Database Management Concepts

In a school, Dia is analyzing the test scores of her classmates. She wants to find out the average score of all students in her class. What is an aggregate function in SQL that she can use to achieve this?

• An aggregate function in SQL is a type of data type used for storing values.

• An aggregate function in SQL returns multiple results from a set of values.

• An aggregate function in SQL is used to modify individual records.

• An aggregate function in SQL is a function that computes a single result from a set of values.

Kiara is working on a project that requires her to analyze sales data from multiple regions. She needs to retrieve specific information about sales trends within those regions. Explain the purpose of a nested query in this context.

• To create a backup of the main query results.

• To simplify data retrieval by reducing the number of queries needed.

• To improve database performance by eliminating the need for joins.

• The purpose of a nested query is to enable complex data retrieval by allowing a query to be executed within another query.

Saisha is trying to understand the difference between a subquery and a join in SQL. She learns that a subquery is nested within another query, while a join combines rows from multiple tables. Can you help her confirm this understanding?

• A subquery retrieves data from a single table, while a join retrieves data from a single row.

• A subquery can only be used in SELECT statements, while a join can be used in any SQL command.

• A subquery is nested within another query, while a join combines rows from multiple tables.

• A subquery is used for data aggregation, while a join is used for filtering data.

Anika wants to analyze the sales data from two different stores. How does she perform an inner join in SQL to combine the sales records from both stores based on a related column?

• Use INNER JOIN to combine rows from two tables based on a related column.

• Use OUTER JOIN to include unmatched rows from both tables.

• Use LEFT JOIN to combine all rows from the first table.

• Use CROSS JOIN to combine rows without any condition.

In a school, Saisha is analyzing the test scores of her classmates. She wants to find out the average score for each subject. What is the role of the GROUP BY clause in aggregate functions for her analysis?

• The GROUP BY clause combines all rows into a single result without grouping.

• The GROUP BY clause groups rows that have the same values in specified columns, allowing aggregate functions to compute results for each group.

• The GROUP BY clause sorts the results in ascending order.

• The GROUP BY clause filters out rows that do not meet certain criteria.

Define a left join and provide an example using a scenario where students and their enrolled courses are listed.

• SELECT A.id, A.name FROM Students A INNER JOIN Courses B ON A.id = B.student_id;

• SELECT A.id, A.name, B.course_id FROM Students A RIGHT JOIN Courses B ON A.id = B.student_id;

• SELECT A.id, A.name, B.course_id FROM Students A JOIN Courses B ON A.id = B.student_id;

• Example: SELECT A.id, A.name, B.course_id FROM Students A LEFT JOIN Courses B ON A.id = B.student_id; This returns all students and their enrolled courses, if any.

Kavya is analyzing her grades in different subjects. She wants to find out which subjects have an average score that is higher than her score in Mathematics. To do this, she uses a correlated subquery that depends on her Mathematics score for its values.

• A correlated subquery is a subquery that depends on the outer query for its values.

• A correlated subquery is a type of join operation between two tables.

• A correlated subquery is a subquery that returns a fixed value.

• A correlated subquery is a subquery that can run independently of the outer query.

Aashi is analyzing the data from her school's student database. She wants to know how many students are enrolled in different courses. How can she use the COUNT() function in SQL?

• You can use the COUNT() function to calculate the average of a column.

• You can use the COUNT() function in SQL to count rows in a table or the number of rows that meet specific conditions.

• The COUNT() function is used to delete rows from a table.

• COUNT() can only be used with numeric data types.

In a school, Arnav is analyzing the exam results of students. He wants to find out which subjects have an average score below 50. What is the significance of the HAVING clause in his SQL query?

• The HAVING clause is used to join tables in SQL queries.

• The HAVING clause filters aggregated data in SQL queries.

• The HAVING clause is used to create new tables in SQL queries.

• The HAVING clause defines the order of results in SQL queries.

Naira and Kiara are working on a project where they need to combine data from two different surveys. Explain the concept of a Cartesian product in relational operations as it applies to their project.

• The Cartesian product combines two relations by pairing every row from the first with every row from the second, resulting in a new relation.

• The Cartesian product merges two relations into a single row.

• The Cartesian product only combines rows with matching values.

• The Cartesian product is a method for filtering rows in a relation.