OU All In One

DBMS and SQL questions

Keys, normalisation and transactions, plus SQL queries on sample tables: joins, subqueries, GROUP BY/HAVING, UNION and window functions.

1. DBMS stands for:

  1. Database Management System
  2. Digital Base Memory System
  3. Data Backup Management Software
  4. Database Machine Server
Answer

A. Database Management System Software that stores and manages data.

2. A primary key:

  1. Links to another table only
  2. Uniquely identifies each row and cannot be NULL
  3. Is always a text column
  4. Can repeat across rows
Answer

B. Uniquely identifies each row and cannot be NULL Primary keys are unique and not null.

3. A foreign key:

  1. Is the same as an index
  2. Must be unique in its own table
  3. Refers to the primary key of another table
  4. Cannot be NULL ever
Answer

C. Refers to the primary key of another table It enforces relationships between tables.

4. Which statement can remove only selected rows using a WHERE clause?

  1. DROP
  2. TRUNCATE
  3. DELETE
  4. ALTER
Answer

C. DELETE DELETE supports WHERE; TRUNCATE always empties the whole table and DROP removes the table.

5. Which clause filters groups after GROUP BY?

  1. LIMIT
  2. WHERE
  3. HAVING
  4. ORDER BY
Answer

C. HAVING WHERE filters rows before grouping; HAVING filters groups.

6. Which join returns only rows with matching values in both tables?

  1. INNER JOIN
  2. FULL OUTER JOIN
  3. CROSS JOIN
  4. LEFT JOIN
Answer

A. INNER JOIN INNER JOIN keeps matches only.

7. A LEFT JOIN returns:

  1. Only unmatched rows
  2. Only matching rows
  3. All rows from the right table
  4. All rows from the left table and matching rows from the right
Answer

D. All rows from the left table and matching rows from the right Unmatched right-side columns become NULL.

8. Which SQL keyword removes duplicate rows from a result?

  1. DISTINCT
  2. SINGLE
  3. UNIQUE
  4. DIFFERENT
Answer

A. DISTINCT SELECT DISTINCT removes duplicates.

9. Normalisation is done to:

  1. Add duplicate data
  2. Encrypt the database
  3. Make queries slower
  4. Reduce data redundancy and update anomalies
Answer

D. Reduce data redundancy and update anomalies It organises tables to avoid repeated data.

10. A table is in 1NF when:

  1. It has no primary key
  2. It has no foreign keys
  3. It has only one column
  4. Every column holds atomic (indivisible) values
Answer

D. Every column holds atomic (indivisible) values 1NF forbids repeating groups and multi-valued cells.

11. ACID properties of transactions are:

  1. Access, Control, Index, Data
  2. Accuracy, Clarity, Integrity, Design
  3. Atomicity, Consistency, Isolation, Durability
  4. Atomicity, Concurrency, Indexing, Durability
Answer

C. Atomicity, Consistency, Isolation, Durability ACID guarantees reliable transactions.

12. "Atomicity" means a transaction:

  1. Uses one table only
  2. Runs very fast
  3. Is visible to all users at once
  4. Happens completely or not at all
Answer

D. Happens completely or not at all Partial transactions are rolled back.

13. Which command saves the changes of a transaction permanently?

  1. GRANT
  2. ROLLBACK
  3. SAVE
  4. COMMIT
Answer

D. COMMIT COMMIT makes changes permanent.

14. An index on a column mainly helps to:

  1. Encrypt the data
  2. Reduce the table size
  3. Prevent all duplicates
  4. Speed up searches on that column
Answer

D. Speed up searches on that column Indexes make lookups faster at the cost of extra storage and slower writes.

15. Which SQL function counts rows?

  1. NUMBER()
  2. TOTAL()
  3. COUNT()
  4. SUM()
Answer

C. COUNT() COUNT(*) counts rows.

16. Which is a DDL command?

  1. UPDATE
  2. CREATE
  3. SELECT
  4. INSERT
Answer

B. CREATE DDL defines structure: CREATE, ALTER, DROP.

17. Which is a DML command?

  1. DROP
  2. ALTER
  3. UPDATE
  4. CREATE
Answer

C. UPDATE DML changes data: INSERT, UPDATE, DELETE.

18. Table Emp has salaries 30, 50, 50, 70 (in thousands). What does SELECT COUNT(DISTINCT salary) FROM Emp return?

  1. 200
  2. 4
  3. 3
  4. 2
Answer

C. 3 Distinct salaries are 30, 50 and 70.

19. Table Marks has values 40, 60, 80. What does SELECT AVG(score) FROM Marks return?

  1. 60
  2. 80
  3. 40
  4. 180
Answer

A. 60 (40 + 60 + 80)/3 = 60.

20. Table T has rows with dept values A, A, B, C, C, C. What does SELECT dept, COUNT(*) FROM T GROUP BY dept HAVING COUNT(*) > 1 return?

  1. A 2 and C 3
  2. C 3 only
  3. A 2, B 1 and C 3
  4. B 1 only
Answer

A. A 2 and C 3 Only groups with more than one row remain.

21. Which query returns the second highest salary from Emp(salary)?

  1. SELECT MAX(salary) - 1 FROM Emp
  2. SELECT MIN(salary) FROM Emp
  3. SELECT salary FROM Emp ORDER BY salary
  4. SELECT MAX(salary) FROM Emp WHERE salary < (SELECT MAX(salary) FROM Emp)
Answer

D. SELECT MAX(salary) FROM Emp WHERE salary < (SELECT MAX(salary) FROM Emp) The highest salary below the overall maximum is the second highest.

22. A view in SQL is:

  1. A backup of a table
  2. A virtual table based on a query
  3. An index
  4. A user account
Answer

B. A virtual table based on a query Views store a query, not the data itself (unless materialised).

23. What does the LIKE 'A%' condition match?

  1. Values ending with A
  2. Exactly the value "A%"
  3. Values containing only A
  4. Values starting with A
Answer

D. Values starting with A % matches any sequence of characters.

24. Which key can be chosen as the primary key from several unique candidates?

  1. Foreign key
  2. Candidate key
  3. Composite index
  4. Super index
Answer

B. Candidate key Each candidate key could serve as the primary key.

25. An ER diagram represents:

  1. Network routes
  2. Entities, their attributes and relationships
  3. CPU scheduling
  4. Program flow
Answer

B. Entities, their attributes and relationships Entity-Relationship diagrams model data.

26. Which SQL statement adds a new column to an existing table?

  1. INSERT COLUMN
  2. ALTER TABLE … ADD
  3. MODIFY TABLE … NEW
  4. UPDATE TABLE … ADD
Answer

B. ALTER TABLE … ADD ALTER TABLE changes structure.

27. A deadlock occurs when:

  1. Two transactions wait for each other's locks forever
  2. An index is missing
  3. The database is full
  4. A query has a syntax error
Answer

A. Two transactions wait for each other's locks forever Each holds a lock the other needs.

28. NoSQL databases such as MongoDB typically store data as:

  1. Spreadsheets
  2. Documents (e.g. JSON-like)
  3. Binary trees on paper
  4. Only fixed-schema tables
Answer

B. Documents (e.g. JSON-like) Many NoSQL stores are document- or key-value-based.

29. Which ordering does ORDER BY use by default?

  1. Random
  2. Insertion order
  3. Descending
  4. Ascending
Answer

D. Ascending ASC is the default.

30. Which of these removes a table completely, including its structure?

  1. CLEAR TABLE
  2. DROP TABLE
  3. DELETE FROM
  4. TRUNCATE TABLE
Answer

B. DROP TABLE DROP deletes the table definition and data.

31. How many rows does this INNER JOIN return?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

-- departments
-- id | dname
-- 10 | HR
-- 20 | IT
-- 40 | Sales

SELECT COUNT(*)
FROM employees e
JOIN departments d ON e.dept_id = d.id;
  1. 4
  2. 3
  3. 5
  4. 2
Answer

B. 3 Only Asha, Ravi and Neha have a dept_id that exists in departments (10, 20). Kiran (30) and Imran (NULL) have no match.

32. How many rows does this LEFT JOIN return?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

-- departments
-- id | dname
-- 10 | HR
-- 20 | IT
-- 40 | Sales

SELECT COUNT(*)
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
  1. 5
  2. 6
  3. 4
  4. 3
Answer

A. 5 A LEFT JOIN keeps every employee (5). Unmatched ones get NULL department columns.

33. What does this query return?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

-- departments
-- id | dname
-- 10 | HR
-- 20 | IT
-- 40 | Sales

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL
ORDER BY e.name;
  1. Kiran
  2. Imran, Kiran
  3. Sales
  4. Imran
Answer

B. Imran, Kiran LEFT JOIN + "IS NULL" finds employees with no matching department: Kiran (dept 30 does not exist) and Imran (NULL).

34. What does this RIGHT JOIN return?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

-- departments
-- id | dname
-- 10 | HR
-- 20 | IT
-- 40 | Sales

SELECT d.dname
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id
WHERE e.id IS NULL;
  1. Sales
  2. HR
  3. IT
  4. NULL
Answer

A. Sales A RIGHT JOIN keeps every department. Only Sales (40) has no employee, so e.id is NULL for it.

35. What is the output?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

-- departments
-- id | dname
-- 10 | HR
-- 20 | IT
-- 40 | Sales

SELECT d.dname, COUNT(e.id)
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.dname
ORDER BY d.dname;
  1. HR 2, IT 1
  2. HR 2, IT 1, Sales 0
  3. HR 2, IT 1, Sales 1
  4. HR 1, IT 1, Sales 0
Answer

B. HR 2, IT 1, Sales 0 The LEFT JOIN keeps Sales with a NULL employee; COUNT(e.id) skips NULLs, so Sales shows 0. COUNT(*) would have shown 1.

36. How many rows does this FULL OUTER JOIN return?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

-- departments
-- id | dname
-- 10 | HR
-- 20 | IT
-- 40 | Sales

SELECT COUNT(*)
FROM employees e
FULL JOIN departments d ON e.dept_id = d.id;
  1. 5
  2. 8
  3. 6
  4. 7
Answer

C. 6 3 matched rows + 2 employees with no department (Kiran, Imran) + 1 department with no employee (Sales) = 6.

37. How many rows does this CROSS JOIN return?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

-- departments
-- id | dname
-- 10 | HR
-- 20 | IT
-- 40 | Sales

SELECT COUNT(*)
FROM employees
CROSS JOIN departments;
  1. 15
  2. 8
  3. 3
  4. 5
Answer

A. 15 A cross join pairs every row with every row: 5 × 3 = 15.

38. Which employees does this self join return?

-- employees
-- id | name  | dept_id | salary | manager_id
-- 1  | Asha  | 10      | 50000  | NULL
-- 2  | Ravi  | 20      | 40000  | 1
-- 3  | Neha  | 10      | 60000  | 1
-- 4  | Kiran | 30      | 35000  | 3
-- 5  | Imran | NULL    | 45000  | 3

SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE m.name = 'Neha'
ORDER BY e.name;
  1. Neha
  2. Asha, Ravi
  3. Imran, Kiran
  4. Ravi, Neha
Answer

C. Imran, Kiran Neha has id 3. Kiran and Imran have manager_id 3.

39. What is the output?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

-- departments
-- id | dname
-- 10 | HR
-- 20 | IT
-- 40 | Sales

SELECT e.name, d.dname
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 45000
ORDER BY e.name;
  1. Neha HR
  2. Asha HR, Ravi IT, Neha HR
  3. Asha HR, Neha HR, Imran NULL
  4. Asha HR, Neha HR
Answer

D. Asha HR, Neha HR Joined rows are Asha, Ravi, Neha; of these only Asha (50000) and Neha (60000) earn more than 45000.

40. What does this self join return?

-- employees
-- id | name  | dept_id | salary | manager_id
-- 1  | Asha  | 10      | 50000  | NULL
-- 2  | Ravi  | 20      | 40000  | 1
-- 3  | Neha  | 10      | 60000  | 1
-- 4  | Kiran | 30      | 35000  | 3
-- 5  | Imran | NULL    | 45000  | 3

SELECT e.name, m.name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
WHERE e.salary < 45000
ORDER BY e.name;
  1. Kiran Asha, Ravi Neha
  2. Ravi Asha
  3. Kiran Neha, Ravi Asha
  4. Kiran NULL, Ravi Asha
Answer

C. Kiran Neha, Ravi Asha Ravi (40000) reports to id 1 (Asha); Kiran (35000) reports to id 3 (Neha).

41. Which departments have no employees?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

-- departments
-- id | dname
-- 10 | HR
-- 20 | IT
-- 40 | Sales

SELECT dname
FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.dept_id = d.id
);
  1. Sales
  2. IT
  3. HR
  4. HR, IT
Answer

A. Sales NOT EXISTS keeps a department only when no employee row matches it.

42. What is the output?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

-- departments
-- id | dname
-- 10 | HR
-- 20 | IT
-- 40 | Sales

SELECT name
FROM employees
WHERE dept_id IN (
  SELECT id FROM departments WHERE dname IN ('HR', 'IT')
)
ORDER BY name;
  1. Asha, Neha, Ravi, Kiran
  2. Asha, Neha, Ravi
  3. Asha, Neha
  4. Ravi
Answer

B. Asha, Neha, Ravi The subquery gives ids 10 and 20; employees in those are Asha, Neha (10) and Ravi (20).

43. What does this query return?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

SELECT name
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
  1. Asha
  2. 60000
  3. Imran
  4. Neha
Answer

D. Neha The highest salary is 60000, which is Neha's.

44. What is the second highest salary returned here?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
  1. 60000
  2. 50000
  3. 40000
  4. 45000
Answer

B. 50000 Excluding the top salary (60000), the highest remaining is 50000. A classic interview query.

45. Who earns more than the average salary?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY name;
  1. Neha
  2. Asha, Imran, Neha
  3. Asha, Neha
  4. Imran, Kiran, Ravi
Answer

C. Asha, Neha Average = 230000 / 5 = 46000. Only Asha (50000) and Neha (60000) are above it.

46. What is the output?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

SELECT dept_id, SUM(salary)
FROM employees
WHERE dept_id IS NOT NULL
GROUP BY dept_id
ORDER BY dept_id;
  1. 10 110000, 20 40000, 30 35000
  2. 10 50000, 20 40000, 30 35000
  3. 10 55000, 20 40000, 30 35000
  4. 10 110000, 20 40000, 30 35000, NULL 45000
Answer

A. 10 110000, 20 40000, 30 35000 Dept 10 has Asha + Neha = 110000. Imran (NULL) is filtered out by the WHERE clause.

47. What is the output?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

SELECT COUNT(dept_id)
FROM employees;
  1. 3
  2. 4
  3. 0
  4. 5
Answer

B. 4 COUNT(column) skips NULLs. Imran's dept_id is NULL, so 4. COUNT(*) would give 5.

48. What is the output?

-- employees
-- id | name  | dept_id | salary | manager_id
-- 1  | Asha  | 10      | 50000  | NULL
-- 2  | Ravi  | 20      | 40000  | 1
-- 3  | Neha  | 10      | 60000  | 1
-- 4  | Kiran | 30      | 35000  | 3
-- 5  | Imran | NULL    | 45000  | 3

SELECT COUNT(DISTINCT manager_id)
FROM employees;
  1. 4
  2. 5
  3. 3
  4. 2
Answer

D. 2 manager_id values are NULL, 1, 1, 3, 3. Distinct non-NULL values: 1 and 3.

49. What does this query return?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

SELECT COUNT(*)
FROM employees
WHERE dept_id = NULL;
  1. 0
  2. 5
  3. 4
  4. 1
Answer

A. 0 Any comparison with NULL using "=" is unknown, so no row matches. Use "dept_id IS NULL" instead.

50. What does this return?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

SELECT name
FROM employees
ORDER BY salary DESC
LIMIT 2;
  1. Asha, Neha
  2. Kiran, Ravi
  3. Neha, Imran
  4. Neha, Asha
Answer

D. Neha, Asha Sorted high to low: Neha 60000, Asha 50000, … LIMIT 2 keeps the first two.

51. What does this return?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

SELECT name
FROM employees
ORDER BY salary
LIMIT 1 OFFSET 1;
  1. Asha
  2. Kiran
  3. Imran
  4. Ravi
Answer

D. Ravi Ascending: Kiran 35000, Ravi 40000, … OFFSET 1 skips Kiran, LIMIT 1 takes Ravi.

52. What is the output after these statements?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

UPDATE employees
SET salary = salary + 5000
WHERE dept_id = 10;

SELECT SUM(salary) FROM employees;
  1. 230000
  2. 235000
  3. 240000
  4. 255000
Answer

C. 240000 The total starts at 230000. Two employees in dept 10 get +5000 each: 240000.

53. What is the output after these statements?

-- employees
-- id | name  | dept_id | salary
-- 1  | Asha  | 10      | 50000
-- 2  | Ravi  | 20      | 40000
-- 3  | Neha  | 10      | 60000
-- 4  | Kiran | 30      | 35000
-- 5  | Imran | NULL    | 45000

DELETE FROM employees
WHERE salary < 45000;

SELECT COUNT(*) FROM employees;
  1. 5
  2. 3
  3. 2
  4. 4
Answer

B. 3 Ravi (40000) and Kiran (35000) are deleted; 3 rows remain. Imran (45000) is not less than 45000.

54. What is the output?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT name
FROM students
WHERE city = 'Hyd' AND marks > 70;
  1. Ali
  2. Ali, Sana
  3. Ravi
  4. Ali, Ravi
Answer

A. Ali Hyderabad students are Ali (78) and Ravi (65); only Ali has more than 70.

55. What is the output?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT name
FROM students
ORDER BY marks DESC, name
LIMIT 2;
  1. Meera, Ali
  2. Sana, Meera
  3. John, Meera
  4. Meera, Sana
Answer

D. Meera, Sana Meera and Sana tie on 92, so the second sort key (name) puts Meera first. In SQLite, NULL sorts last in DESC order.

56. What is the output?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT COUNT(DISTINCT city)
FROM students;
  1. 5
  2. 4
  3. 2
  4. 3
Answer

D. 3 Cities: Hyd, Pune, Delhi.

57. What is the output?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT AVG(marks)
FROM students;
  1. 92
  2. 78
  3. 81.75
  4. 65.4
Answer

C. 81.75 AVG ignores NULL: (78 + 92 + 65 + 92) / 4 = 81.75, not divided by 5.

58. What is the output?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT city, COUNT(*)
FROM students
GROUP BY city
ORDER BY city;
  1. Delhi 1, Hyd 2, Pune 1
  2. Delhi 1, Hyd 2, Pune 2
  3. Hyd 2, Pune 2
  4. Delhi 1, Hyd 1, Pune 2
Answer

B. Delhi 1, Hyd 2, Pune 2 COUNT(*) counts rows, including John whose marks are NULL.

59. Which names does this LIKE pattern match?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT name
FROM students
WHERE name LIKE '%a'
ORDER BY name;
  1. Meera, Ravi, Sana
  2. Ali, Sana
  3. Sana
  4. Meera, Sana
Answer

D. Meera, Sana "%a" means any characters followed by a final "a": Meera and Sana.

60. What is the output?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT COUNT(*)
FROM students
WHERE marks BETWEEN 65 AND 78;
  1. 0
  2. 2
  3. 3
  4. 1
Answer

B. 2 BETWEEN includes both ends: Ravi (65) and Ali (78).

61. What is the output?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT name
FROM students
WHERE marks IS NULL;
  1. No rows
  2. John, Sana
  3. NULL
  4. John
Answer

D. John Only John has no marks.

62. What is the output?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT COUNT(*)
FROM students
WHERE marks = (SELECT MAX(marks) FROM students);
  1. 1
  2. 92
  3. 2
  4. 5
Answer

C. 2 The maximum is 92 and two students (Sana, Meera) have it.

63. What is the output?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT name, COALESCE(marks, 0)
FROM students
WHERE city = 'Pune'
ORDER BY name;
  1. John 92, Sana 0
  2. John NULL, Sana 92
  3. John 0, Sana 92
  4. Sana 92
Answer

C. John 0, Sana 92 COALESCE returns the first non-NULL value, so John's NULL becomes 0.

64. Who is marked "Fail" by this CASE expression?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT name
FROM students
WHERE CASE WHEN marks >= 75 THEN 'Pass'
           ELSE 'Fail' END = 'Fail'
ORDER BY name;
  1. Ravi
  2. Ali, Ravi
  3. John, Ravi
  4. John
Answer

C. John, Ravi Ravi (65) fails. For John, NULL >= 75 is not true, so CASE falls to ELSE and he is also "Fail".

65. What is the output of this window function?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT name, RANK() OVER (ORDER BY marks DESC)
FROM students
WHERE marks IS NOT NULL
ORDER BY 2, name;
  1. Meera 1, Sana 1, Ali 2, Ravi 3
  2. Meera 1, Sana 2, Ali 3, Ravi 4
  3. Ali 1, Meera 2, Ravi 3, Sana 4
  4. Meera 1, Sana 1, Ali 3, Ravi 4
Answer

D. Meera 1, Sana 1, Ali 3, Ravi 4 RANK gives ties the same rank and then skips: 1, 1, 3, 4.

66. What is the output of this window function?

-- students
-- id | name  | marks | city
-- 1  | Ali   | 78    | Hyd
-- 2  | Sana  | 92    | Pune
-- 3  | Ravi  | 65    | Hyd
-- 4  | Meera | 92    | Delhi
-- 5  | John  | NULL  | Pune

SELECT name, DENSE_RANK() OVER (ORDER BY marks DESC)
FROM students
WHERE marks IS NOT NULL
ORDER BY 2, name;
  1. Meera 1, Sana 1, Ali 3, Ravi 4
  2. Meera 1, Sana 1, Ali 2, Ravi 3
  3. Meera 1, Sana 1, Ali 2, Ravi 4
  4. Meera 1, Sana 2, Ali 3, Ravi 4
Answer

B. Meera 1, Sana 1, Ali 2, Ravi 3 DENSE_RANK gives ties the same rank without gaps: 1, 1, 2, 3.

67. Which customers does this return?

-- orders
-- id | customer | amount
-- 1  | Amit     | 500
-- 2  | Bina     | 300
-- 3  | Amit     | 700
-- 4  | Chen     | 200
-- 5  | Bina     | 400
-- 6  | Amit     | 100

SELECT customer
FROM orders
GROUP BY customer
HAVING SUM(amount) > 600
ORDER BY customer;
  1. Bina
  2. Amit, Bina, Chen
  3. Amit
  4. Amit, Bina
Answer

D. Amit, Bina Totals: Amit 1300, Bina 700, Chen 200. HAVING keeps groups above 600.

68. What is the output?

-- orders
-- id | customer | amount
-- 1  | Amit     | 500
-- 2  | Bina     | 300
-- 3  | Amit     | 700
-- 4  | Chen     | 200
-- 5  | Bina     | 400
-- 6  | Amit     | 100

SELECT customer, COUNT(*)
FROM orders
GROUP BY customer
HAVING COUNT(*) >= 2
ORDER BY customer;
  1. Amit 2, Bina 2
  2. Amit 3
  3. Amit 3, Bina 2
  4. Amit 3, Bina 2, Chen 1
Answer

C. Amit 3, Bina 2 Amit has 3 orders, Bina 2, Chen 1 (filtered out).

69. What is the output? (Note: WHERE runs before GROUP BY.)

-- orders
-- id | customer | amount
-- 1  | Amit     | 500
-- 2  | Bina     | 300
-- 3  | Amit     | 700
-- 4  | Chen     | 200
-- 5  | Bina     | 400
-- 6  | Amit     | 100

SELECT customer, SUM(amount)
FROM orders
WHERE amount > 200
GROUP BY customer
ORDER BY customer;
  1. Amit 1300, Bina 700, Chen 200
  2. Amit 1200, Bina 700, Chen 0
  3. Amit 1300, Bina 700
  4. Amit 1200, Bina 700
Answer

D. Amit 1200, Bina 700 WHERE removes the 200 and 100 orders first. Amit: 500 + 700, Bina: 300 + 400, and Chen has no rows left.

70. What is the output?

-- orders
-- id | customer | amount
-- 1  | Amit     | 500
-- 2  | Bina     | 300
-- 3  | Amit     | 700
-- 4  | Chen     | 200
-- 5  | Bina     | 400
-- 6  | Amit     | 100

SELECT customer, MAX(amount)
FROM orders
GROUP BY customer
ORDER BY customer;
  1. Amit 500, Bina 300, Chen 200
  2. Amit 700
  3. Amit 700, Bina 400, Chen 200
  4. Amit 1300, Bina 700, Chen 200
Answer

C. Amit 700, Bina 400, Chen 200 MAX is taken within each customer's group.

71. Which customer does this return?

-- orders
-- id | customer | amount
-- 1  | Amit     | 500
-- 2  | Bina     | 300
-- 3  | Amit     | 700
-- 4  | Chen     | 200
-- 5  | Bina     | 400
-- 6  | Amit     | 100

SELECT customer
FROM orders
GROUP BY customer
ORDER BY SUM(amount) DESC
LIMIT 1;
  1. Chen
  2. Amit, Bina
  3. Amit
  4. Bina
Answer

C. Amit Amit has the largest total (1300).

72. What is the output?

-- orders
-- id | customer | amount
-- 1  | Amit     | 500
-- 2  | Bina     | 300
-- 3  | Amit     | 700
-- 4  | Chen     | 200
-- 5  | Bina     | 400
-- 6  | Amit     | 100

SELECT customer FROM orders WHERE amount > 400
UNION
SELECT customer FROM orders WHERE amount < 300
ORDER BY customer;
  1. Chen
  2. Amit, Bina, Chen
  3. Amit, Chen
  4. Amit, Amit, Amit, Chen
Answer

C. Amit, Chen > 400 gives Amit twice; < 300 gives Chen and Amit. UNION removes duplicates: Amit, Chen.

73. How many rows does UNION ALL give here?

-- orders
-- id | customer | amount
-- 1  | Amit     | 500
-- 2  | Bina     | 300
-- 3  | Amit     | 700
-- 4  | Chen     | 200
-- 5  | Bina     | 400
-- 6  | Amit     | 100

SELECT COUNT(*) FROM (
  SELECT customer FROM orders WHERE amount > 400
  UNION ALL
  SELECT customer FROM orders WHERE amount < 300
);
  1. 3
  2. 4
  3. 2
  4. 6
Answer

B. 4 UNION ALL keeps duplicates: 2 rows (> 400) + 2 rows (< 300) = 4.

74. What does this return?

-- orders
-- id | customer | amount
-- 1  | Amit     | 500
-- 2  | Bina     | 300
-- 3  | Amit     | 700
-- 4  | Chen     | 200
-- 5  | Bina     | 400
-- 6  | Amit     | 100

SELECT customer FROM orders WHERE amount >= 400
INTERSECT
SELECT customer FROM orders WHERE amount <= 300
ORDER BY customer;
  1. Amit
  2. Bina, Chen
  3. Amit, Bina, Chen
  4. Amit, Bina
Answer

D. Amit, Bina Customers with an order ≥ 400: Amit, Bina. With an order ≤ 300: Bina, Chen, Amit. Both: Amit, Bina.

75. What does this return?

-- orders
-- id | customer | amount
-- 1  | Amit     | 500
-- 2  | Bina     | 300
-- 3  | Amit     | 700
-- 4  | Chen     | 200
-- 5  | Bina     | 400
-- 6  | Amit     | 100

SELECT customer FROM orders
EXCEPT
SELECT customer FROM orders WHERE amount > 350;
  1. No rows
  2. Bina
  3. Chen
  4. Amit, Chen
Answer

C. Chen Customers with an order above 350 are Amit and Bina; removing them leaves Chen.

76. Which join returns only the rows that have matching values in both tables?

  1. INNER JOIN
  2. LEFT JOIN
  3. FULL OUTER JOIN
  4. CROSS JOIN
Answer

A. INNER JOIN An inner join keeps matching rows only.

77. Which join returns all rows from the left table and the matching rows from the right table?

  1. INNER JOIN
  2. LEFT JOIN
  3. SELF JOIN
  4. RIGHT JOIN
Answer

B. LEFT JOIN Unmatched left rows get NULLs for the right table's columns.

78. In a LEFT JOIN, what appears in the right table's columns when there is no match?

  1. The previous row's value
  2. NULL
  3. An empty string
  4. 0
Answer

B. NULL Missing values are filled with NULL.

79. Joining a table to itself, for example to pair employees with their managers, is called a:

  1. Natural join
  2. Self join
  3. Outer join
  4. Cross join
Answer

B. Self join The same table is used twice with different aliases.

80. A CROSS JOIN between a table of 4 rows and a table of 6 rows returns:

  1. 4 rows
  2. 24 rows
  3. 10 rows
  4. 6 rows
Answer

B. 24 rows A cross join returns the Cartesian product: 4 × 6.

81. A NATURAL JOIN joins two tables on:

  1. Every possible pair of rows
  2. Columns chosen at random
  3. The primary key only
  4. All columns with the same name in both tables
Answer

D. All columns with the same name in both tables It matches on all same-named columns automatically.

82. What is the logical order in which these clauses are evaluated?

  1. FROM → GROUP BY → WHERE → SELECT → HAVING → ORDER BY
  2. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
  3. SELECT → WHERE → FROM → ORDER BY → GROUP BY → HAVING
  4. SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY
Answer

B. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY This is why a column alias from SELECT cannot be used in WHERE.

83. Which statement about WHERE and HAVING is correct?

  1. They are exactly the same
  2. HAVING runs before WHERE
  3. WHERE filters rows before grouping; HAVING filters groups after grouping
  4. WHERE can use aggregate functions like SUM directly
Answer

C. WHERE filters rows before grouping; HAVING filters groups after grouping Aggregates such as SUM(amount) > 600 belong in HAVING.

84. What is the difference between UNION and UNION ALL?

  1. UNION joins columns side by side
  2. There is no difference
  3. UNION ALL removes duplicates; UNION keeps them
  4. UNION removes duplicate rows; UNION ALL keeps them
Answer

D. UNION removes duplicate rows; UNION ALL keeps them UNION ALL is faster because it skips duplicate removal.

85. A subquery that refers to a column of the outer query and runs once for each outer row is a:

  1. Common table expression
  2. Correlated subquery
  3. Scalar subquery
  4. Derived table
Answer

B. Correlated subquery Example: WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id).

86. Which query correctly finds employees whose department does not exist in the departments table?

  1. INNER JOIN departments, then WHERE departments.id IS NULL
  2. WHERE dept_id = NULL
  3. LEFT JOIN departments, then WHERE departments.id IS NULL
  4. CROSS JOIN departments
Answer

C. LEFT JOIN departments, then WHERE departments.id IS NULL An inner join already drops unmatched rows, and "= NULL" never matches.

87. Which aggregate counts every row, including rows with NULL values?

  1. COUNT(column)
  2. SUM(column)
  3. AVG(column)
  4. COUNT(*)
Answer

D. COUNT(*) COUNT(column) and the other aggregates skip NULLs.

88. Which keyword removes duplicate rows from a SELECT result?

  1. GROUP
  2. UNIQUE
  3. DISTINCT
  4. DIFFERENT
Answer

C. DISTINCT SELECT DISTINCT city FROM students;

89. Which statement is used to remove all rows from a table quickly while keeping its structure?

  1. TRUNCATE TABLE
  2. DROP TABLE
  3. DELETE DATABASE
  4. REMOVE TABLE
Answer

A. TRUNCATE TABLE DROP removes the table itself; TRUNCATE empties it.

90. Which SQL clause names a temporary result set that can be used in the main query, like a named subquery?

  1. INTO
  2. HAVING
  3. WITH (a common table expression)
  4. GROUP BY
Answer

C. WITH (a common table expression) WITH high AS (SELECT …) SELECT * FROM high;

91. In DBMS & SQL, which description best fits “primary key”?

  1. filter groups after aggregation
  2. transaction guarantees of atomicity consistency isolation durability
  3. keep all rows from left table with matched right rows
  4. uniquely identify a row
Answer

D. uniquely identify a row primary key is used to uniquely identify a row.

92. In DBMS & SQL, which description best fits “foreign key”?

  1. keep all rows from left table with matched right rows
  2. reference a key in another table
  3. reduce redundancy through schema design
  4. accelerate selected lookup operations
Answer

B. reference a key in another table foreign key is used to reference a key in another table.

93. In DBMS & SQL, which description best fits “INNER JOIN”?

  1. uniquely identify a row
  2. return matching rows from both tables
  3. reduce redundancy through schema design
  4. reference a key in another table
Answer

B. return matching rows from both tables INNER JOIN is used to return matching rows from both tables.

94. In DBMS & SQL, which description best fits “LEFT JOIN”?

  1. filter groups after aggregation
  2. keep all rows from left table with matched right rows
  3. return matching rows from both tables
  4. reduce redundancy through schema design
Answer

B. keep all rows from left table with matched right rows LEFT JOIN is used to keep all rows from left table with matched right rows.

95. In DBMS & SQL, which description best fits “GROUP BY”?

  1. filter groups after aggregation
  2. keep all rows from left table with matched right rows
  3. form groups for aggregation
  4. reference a key in another table
Answer

C. form groups for aggregation GROUP BY is used to form groups for aggregation.

96. In DBMS & SQL, which description best fits “HAVING”?

  1. filter groups after aggregation
  2. keep all rows from left table with matched right rows
  3. reduce redundancy through schema design
  4. group operations into a logical unit
Answer

A. filter groups after aggregation HAVING is used to filter groups after aggregation.

97. In DBMS & SQL, which description best fits “index”?

  1. form groups for aggregation
  2. reference a key in another table
  3. accelerate selected lookup operations
  4. reduce redundancy through schema design
Answer

C. accelerate selected lookup operations index is used to accelerate selected lookup operations.

98. In DBMS & SQL, which description best fits “transaction”?

  1. reference a key in another table
  2. accelerate selected lookup operations
  3. form groups for aggregation
  4. group operations into a logical unit
Answer

D. group operations into a logical unit transaction is used to group operations into a logical unit.

99. In DBMS & SQL, which description best fits “normalization”?

  1. return matching rows from both tables
  2. keep all rows from left table with matched right rows
  3. reduce redundancy through schema design
  4. group operations into a logical unit
Answer

C. reduce redundancy through schema design normalization is used to reduce redundancy through schema design.

100. In DBMS & SQL, which description best fits “ACID”?

  1. group operations into a logical unit
  2. transaction guarantees of atomicity consistency isolation durability
  3. reduce redundancy through schema design
  4. keep all rows from left table with matched right rows
Answer

B. transaction guarantees of atomicity consistency isolation durability ACID is used to transaction guarantees of atomicity consistency isolation durability.

101. Which DBMS & SQL concept matches this description: “uniquely identify a row”?

  1. foreign key
  2. HAVING
  3. primary key
  4. ACID
Answer

C. primary key The definition describes primary key.

102. Which DBMS & SQL concept matches this description: “reference a key in another table”?

  1. INNER JOIN
  2. GROUP BY
  3. foreign key
  4. LEFT JOIN
Answer

C. foreign key The definition describes foreign key.

103. Which DBMS & SQL concept matches this description: “return matching rows from both tables”?

  1. primary key
  2. INNER JOIN
  3. GROUP BY
  4. ACID
Answer

B. INNER JOIN The definition describes INNER JOIN.

104. Which DBMS & SQL concept matches this description: “keep all rows from left table with matched right rows”?

  1. LEFT JOIN
  2. normalization
  3. foreign key
  4. primary key
Answer

A. LEFT JOIN The definition describes LEFT JOIN.

105. Which DBMS & SQL concept matches this description: “form groups for aggregation”?

  1. normalization
  2. foreign key
  3. GROUP BY
  4. index
Answer

C. GROUP BY The definition describes GROUP BY.

106. Which DBMS & SQL concept matches this description: “filter groups after aggregation”?

  1. HAVING
  2. primary key
  3. GROUP BY
  4. LEFT JOIN
Answer

A. HAVING The definition describes HAVING.

107. Which DBMS & SQL concept matches this description: “accelerate selected lookup operations”?

  1. ACID
  2. index
  3. transaction
  4. LEFT JOIN
Answer

B. index The definition describes index.

108. Which DBMS & SQL concept matches this description: “group operations into a logical unit”?

  1. transaction
  2. foreign key
  3. ACID
  4. INNER JOIN
Answer

A. transaction The definition describes transaction.

109. Which DBMS & SQL concept matches this description: “reduce redundancy through schema design”?

  1. transaction
  2. GROUP BY
  3. LEFT JOIN
  4. normalization
Answer

D. normalization The definition describes normalization.

110. Which DBMS & SQL concept matches this description: “transaction guarantees of atomicity consistency isolation durability”?

  1. primary key
  2. normalization
  3. transaction
  4. ACID
Answer

D. ACID The definition describes ACID.

111. A table has 15 rows. An INSERT adds 5 rows and no rows are deleted. How many rows remain?

  1. 0
  2. 50
  3. 20
  4. 19
Answer

C. 20 The new row count is 15+5=20.

Mock test

More Technical MCQs topics