Quiz - 2 ( DQL, DDL, DML, Joins and Set

1. Consider the following SQL statement:

SELECT COUNT(*) FROM student WHERE age > (SELECT AVG(age) FROM student);

• The query returns the count of students older than the average age.

• The query returns an error because subqueries cannot be used in the WHERE clause.

• The query returns the average age of students.

• The query fails because COUNT(*) cannot be used with subqueries.

What will be the output of the following SQL query?

SELECT 10 / 0 FROM dual;

A) 0
B) NULL
C) Error: Division by zero
D) Infinity

• 0

• NULL

• Error: Division by zero

• INFINITY

3. Which of the following constraints ensures that a column in a table cannot have NULL values?

• UNIQUE

• CHECK

• NOT NULL

• FOREIGN KEY

4. Consider the relational schema:

Employee (EmpID, Name, Age, Department, Salary, ManagerID)
If we need to retrieve the names of employees who earn more than their respective managers, which SQL query is CORRECT?

• SELECT e.Name FROM Employee e, Employee m WHERE e.ManagerID = m.EmpID AND e.Salary > m.Salary;

• SELECT Name FROM Employee WHERE Salary > ManagerID;

• SELECT e.Name FROM Employee e JOIN Employee m ON e.ManagerID = m.EmpID WHERE e.Salary < m.Salary;

• SELECT Name FROM Employee WHERE ManagerID > Salary;

5. Given the relation schema:

Flights (FlightNo, Source, Destination, Fare, AirlineID)
Which of the following SQL statements is incorrect?

• SELECT DISTINCT Source FROM Flights;

• SELECT * FROM Flights WHERE Fare = NULL;

• SELECT * FROM Flights WHERE Fare IS NULL;

• SELECT Source, Destination FROM Flights;

Which of the following is NOT a DML statement?

• INSERT
  

• 
  UPDATE
  

• DELETE
  

• CREATE

7. The difference between UNION and UNION ALL is that:

• UNION does not remove duplicates, while UNION ALL does

• UNION removes duplicates, while UNION ALL does not

• UNION is faster than UNION ALL

• Both give the same result in all cases

8. What will be the output of the following SQL statement?

SELECT ROUND(12.6789, 2);

• 12.67

• 12.68

• 12.7

• 13

9. A table without any constraints allows:

• NULL value

• 
  Duplicate values
  

• 
  Inconsistent data
  

• 
  All of the above

The default sorting order in an ORDER BY clause is:

• Ascending
  

• 
  Descending

• 
  Unsorted

• 
  Depends on the DBMS