DB --> LEC 7 --> MCQ & WITHOUT ANS

SQL Clauses: Concepts and Usage

ORDER BY Clause

1. What does the ORDER BY clause do in SQL?

   • A) Filters rows
   • B) Sorts the result set
   • C) Groups the data
   • D) Joins tables

2. What is the default sorting order of ORDER BY?

   • A) DESC
   • B) RANDOM
   • C) ASC
   • D) NONE

3. Which clause is usually used after ORDER BY?

   • A) SELECT
   • B) GROUP BY
   • C) LIMIT
   • D) WHERE

4. Which keyword explicitly defines ascending order in sorting?

   • A) ASC
   • B) DESC
   • C) INCR
   • D) UP

5. What does ORDER BY 1 refer to?

   • A) First row
   • B) Primary key
   • C) First column in SELECT
   • D) Row with value 1

---

ASC and DESC

1. ORDER BY price DESC sorts:

   • A) Low to high
   • B) Alphabetically
   • C) High to low
   • D) Randomly

2. In string sorting, what does ASC result in?

   • A) Z to A
   • B) Length of string
   • C) A to Z
   • D) No order

3. Which is true about DESC order?

   • A) It’s the default
   • B) Sorts nulls first
   • C) Sorts from smallest to largest
   • D) Sorts from largest to smallest

---

ORDER BY Multiple Columns

1. What happens when sorting by multiple columns?

   • A) The second column is ignored
   • B) MySQL gives an error
   • C) Rows are sorted by the first column, then second
   • D) Only the last column is used

2. What does ORDER BY department ASC, salary DESC do?

   • A) Sorts both in ascending order
   • B) Sorts by department then salary (reverse)
   • C) Sorts salary then department
   • D) Sorts randomly

---

ORDER BY with WHERE

1. What is the purpose of the WHERE clause?

   • A) Sort rows
   • B) Filter rows
   • C) Join tables
   • D) Display NULLs

2. In which order is a query evaluated?

   • A) ORDER BY → WHERE
   • B) GROUP BY → ORDER BY
   • C) WHERE → ORDER BY
   • D) HAVING → WHERE

3. Which of the following is valid?

   • A) SELECT * FROM table ORDER BY WHERE name = 'Ali'
   • B) SELECT name FROM employees WHERE department='IT' ORDER BY salary DESC
   • C) SELECT * ORDER BY name WHERE age > 30
   • D) SELECT FROM table ORDER BY age

---

GROUP BY Clause

1. What does GROUP BY do?

   • A) Sorts data
   • B) Joins tables
   • C) Aggregates data into groups
   • D) Filters rows

2. Which function is commonly used with GROUP BY?

   • A) LOWER()
   • B) COUNT()
   • C) CONCAT()
   • D) LENGTH()

3. In MySQL, which is TRUE about GROUP BY?

   • A) It’s used without SELECT
   • B) It must include all selected columns
   • C) It must be after ORDER BY
   • D) It filters data

4. Which clause comes after GROUP BY if sorting is required?

   • A) WHERE
   • B) SELECT
   • C) HAVING
   • D) ORDER BY

---

HAVING Clause

1. What is the purpose of the HAVING clause?

   • A) Filters individual rows
   • B) Filters grouped data
   • C) Joins tables
   • D) Sorts rows

2. Which clause supports aggregate functions directly?

   • A) WHERE
   • B) ORDER BY
   • C) HAVING
   • D) FROM

3. What is the result of this query?

   SELECT department, AVG(salary)
   FROM employees
   GROUP BY department
   HAVING AVG(salary) > 70000;
   

   • A) Departments with salaries over 70,000
   • B) Employees with salary > 70,000
   • C) All departments
   • D) None

4. Which comes first in execution: HAVING or GROUP BY?

   • A) HAVING
   • B) GROUP BY

5. Can HAVING be used without GROUP BY?

   • A) No
   • B) Only with COUNT
   • C) Yes, but not recommended
   • D) Only with SELECT *

---

HAVING vs WHERE

1. Which is evaluated first in query execution?

   • A) HAVING
   • B) WHERE
   • C) SELECT
   • D) ORDER BY

2. What happens if you use aggregate functions in WHERE?

   • A) Error
   • B) Filters by group
   • C) Valid syntax
   • D) Summarizes rows

3. Which is true?

   • A) HAVING filters before GROUP BY
   • B) WHERE filters after GROUP BY
   • C) HAVING filters after GROUP BY
   • D) HAVING and WHERE are the same

4. In MySQL, HAVING COUNT(*) > 2 means:

   • A) Only groups with more than 2 members
   • B) Each row must appear 3 times
   • C) Filters values > 2
   • D) None

---

Advanced ORDER BY and Usage

1. What is the effect of ORDER BY column_name DESC LIMIT 5?

   • A) Returns the last 5 rows in the table
   • B) Returns the first 5 rows in alphabetical order
   • C) Returns the top 5 highest values in that column
   • D) Returns 5 random rows

2. Which is valid SQL syntax?

   • A) SELECT name ORDER BY name
   • B) SELECT name FROM users ORDER BY 1 DESC
   • C) SELECT name, ORDER BY name DESC
   • D) SELECT name WHERE age > 20 ORDER BY name

3. Which sorts by salary ascending and name descending?

   • A) ORDER BY salary, name DESC
   • B) ORDER BY salary ASC, name ASC
   • C) ORDER BY salary DESC, name ASC
   • D) ORDER BY salary ASC, name DESC

4. What happens if you sort by a column not in the SELECT clause?

   • A) Error
   • B) Works fine in MySQL
   • C) Requires GROUP BY
   • D) Only possible with LIMIT

---

GROUP BY Deep Dive

1. Which clause is mandatory when using aggregate functions without GROUP BY?

   • A) WHERE
   • B) HAVING
   • C) ORDER BY
   • D) None

2. If you use GROUP BY department, what must be true?

   • A) All selected columns are aggregated or grouped
   • B) You must use ORDER BY
   • C) No WHERE clause allowed
   • D) HAVING must follow

3. What is the result of this query?

   SELECT department, MAX(salary)
   FROM employees
   GROUP BY department;
   

   • A) Highest salary in the company
   • B) Highest salary for each department
   • C) All employees
   • D) Employees earning max salary

4. If we GROUP BY a column with NULL values:

   • A) NULLs are excluded
   • B) NULLs cause an error
   • C) NULLs are grouped together
   • D) NULLs are sorted first

5. Which is a valid GROUP BY query?

   • A) SELECT COUNT(*), name GROUP BY name
   • B) SELECT * FROM students GROUP BY name, age
   • C) SELECT COUNT(*) GROUP BY
   • D) SELECT name FROM users GROUP name

---

HAVING vs WHERE Clarification

1. Why use HAVING instead of WHERE?

   • A) WHERE can’t filter strings
   • B) WHERE is slower
   • C) HAVING filters after aggregation
   • D) HAVING runs first

2. Which aggregate function is NOT supported in HAVING?

   • A) COUNT
   • B) MAX
   • C) AVG
   • D) All are supported

3. What is the error in this SQL?

   SELECT department
   FROM employees
   HAVING salary > 50000;
   

   • A) salary must be aggregated
   • B) HAVING used before GROUP BY
   • C) Wrong keyword
   • D) No FROM clause

4. What’s the result of:

   SELECT department, COUNT(*)
   FROM employees
   GROUP BY department
   HAVING COUNT(*) <= 2;
   

   • A) Groups with > 2 rows
   • B) Groups with 2 or fewer rows
   • C) All rows
   • D) Error

5. What is the correct order of clauses in a SQL SELECT query?

   • A) SELECT → WHERE → ORDER BY → GROUP BY → HAVING
   • B) SELECT → ORDER BY → WHERE → GROUP BY → HAVING
   • C) SELECT → WHERE → GROUP BY → HAVING → ORDER BY
   • D) SELECT → GROUP BY → WHERE → HAVING → ORDER BY

---

Execution Order and Logic

1. In MySQL, the evaluation order is:

   • A) WHERE → SELECT → GROUP BY
   • B) SELECT → WHERE → ORDER BY
   • C) FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
   • D) SELECT → FROM → GROUP BY → WHERE → ORDER BY

2. If HAVING is used without GROUP BY, it acts like:

   • A) WHERE
   • B) ORDER BY
   • C) LIMIT
   • D) JOIN

3. Which clause allows filtering by raw column values?

   • A) HAVING
   • B) GROUP BY
   • C) ORDER BY
   • D) WHERE

4. To find departments with more than 5 employees, use:

   • A) WHERE COUNT(*) > 5
   • B) GROUP BY department WHERE COUNT(*) > 5
   • C) GROUP BY department HAVING COUNT(*) > 5
   • D) HAVING department > 5

---

Mixed Concepts and Scenarios

1. How can you get the names of departments with no employees?

   • A) WHERE COUNT(*) = 0
   • B) HAVING COUNT(*) = 0
   • C) Use LEFT JOIN and check for NULLs
   • D) GROUP BY with MAX()

2. Which clause is optional in a SELECT query?

   • A) FROM
   • B) SELECT
   • C) WHERE
   • D) None

3. To filter employees before grouping, use:

   • A) HAVING
   • B) GROUP BY
   • C) WHERE
   • D) LIMIT

4. Which query correctly finds departments with average salary > 60000?

   A) SELECT department FROM employees WHERE AVG(salary) > 60000;
   B) SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 60000;
   C) SELECT department FROM employees GROUP BY AVG(salary) > 60000;
   D) SELECT department HAVING AVG(salary) > 60000;
   

5. Can you use ORDER BY on aggregate values?

   • A) No
   • B) Yes
   • C) Only with LIMIT
   • D) Only for numeric values

6. What does this return?

   SELECT COUNT(*)
   FROM employees
   WHERE department = 'Sales';
   

   • A) Number of departments
   • B) Number of employees in Sales
   • C) Salary sum
   • D) List of employees

Database SQL

You may like these posts

Comments