Skills101
← SQL & DBMS — Advanced

SQL & DBMS Advanced — Test 1

15 questions22 minPass: 60%+40 XP on first passFree sample test

15 questions · 22 minutes · advanced level, with an explanation for every answer.

Log in to take the quiz

Free account · see your score, answers and explanations, earn XP

  1. Question 1

    Why can an index on name NOT help the query WHERE LOWER(name) = 'asha'?

  2. Question 2

    What is the output of this SQL query?

    -- employees(id, name, dept, salary, manager_id)
    -- 1 Asha  IT    70000 NULL | 2 Ravi  IT 50000 1 | 3 Neha  HR    45000 NULL
    -- 4 Karan Sales 40000 3    | 5 Meera Sales 55000 3 | 6 Imran IT 50000 1
    -- departments(dept, city): IT Jaipur | HR Pune | Sales Delhi | Admin Noida
    SELECT name, RANK() OVER (ORDER BY salary DESC), DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees ORDER BY salary DESC, id LIMIT 4;
    
  3. Question 3

    What is the output of this SQL query?

    -- employees(id, name, dept, salary, manager_id)
    -- 1 Asha  IT    70000 NULL | 2 Ravi  IT 50000 1 | 3 Neha  HR    45000 NULL
    -- 4 Karan Sales 40000 3    | 5 Meera Sales 55000 3 | 6 Imran IT 50000 1
    -- departments(dept, city): IT Jaipur | HR Pune | Sales Delhi | Admin Noida
    SELECT dept, ROUND(AVG(salary)), ROUND(100.0 * SUM(salary) / (SELECT SUM(salary) FROM employees)) FROM employees GROUP BY dept ORDER BY 3 DESC LIMIT 1;
    
  4. Question 4

    Transaction T1 reads a row, T2 updates and commits it, T1 reads again and sees a different value. What is this?

  5. Question 5

    What is the output of this SQL query?

    -- employees(id, name, dept, salary, manager_id)
    -- 1 Asha  IT    70000 NULL | 2 Ravi  IT 50000 1 | 3 Neha  HR    45000 NULL
    -- 4 Karan Sales 40000 3    | 5 Meera Sales 55000 3 | 6 Imran IT 50000 1
    -- departments(dept, city): IT Jaipur | HR Pune | Sales Delhi | Admin Noida
    SELECT name, salary - LAG(salary) OVER (ORDER BY id) FROM employees WHERE dept = 'IT' ORDER BY id;
    
  6. Question 6

    What is the output of this SQL query?

    -- employees(id, name, dept, salary, manager_id)
    -- 1 Asha  IT    70000 NULL | 2 Ravi  IT 50000 1 | 3 Neha  HR    45000 NULL
    -- 4 Karan Sales 40000 3    | 5 Meera Sales 55000 3 | 6 Imran IT 50000 1
    -- departments(dept, city): IT Jaipur | HR Pune | Sales Delhi | Admin Noida
    WITH RECURSIVE chain(id, name, lvl) AS (
      SELECT id, name, 0 FROM employees WHERE id = 3
      UNION ALL
      SELECT e.id, e.name, c.lvl + 1 FROM employees e JOIN chain c ON e.manager_id = c.id
    )
    SELECT COUNT(*), MAX(lvl) FROM chain;
    
  7. Question 7

    What is the output of this SQL query?

    -- employees(id, name, dept, salary, manager_id)
    -- 1 Asha  IT    70000 NULL | 2 Ravi  IT 50000 1 | 3 Neha  HR    45000 NULL
    -- 4 Karan Sales 40000 3    | 5 Meera Sales 55000 3 | 6 Imran IT 50000 1
    -- departments(dept, city): IT Jaipur | HR Pune | Sales Delhi | Admin Noida
    SELECT dept, name FROM (SELECT dept, name, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, id) AS rn FROM employees) WHERE rn = 1 ORDER BY dept;
    
  8. Question 8

    Which data structure do most databases use for indexes that support range queries?

  9. Question 9

    What is the output of this SQL query?

    -- employees(id, name, dept, salary, manager_id)
    -- 1 Asha  IT    70000 NULL | 2 Ravi  IT 50000 1 | 3 Neha  HR    45000 NULL
    -- 4 Karan Sales 40000 3    | 5 Meera Sales 55000 3 | 6 Imran IT 50000 1
    -- departments(dept, city): IT Jaipur | HR Pune | Sales Delhi | Admin Noida
    SELECT d.city FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept = d.dept);
    
  10. Question 10

    Which ACID property makes sure a committed transaction survives a power failure?

  11. Question 11

    A relation R(A, B, C) has A → B and B → A, and the key is (A, C). Is it in BCNF?

  12. Question 12

    What is the output of this SQL query?

    -- employees(id, name, dept, salary, manager_id)
    -- 1 Asha  IT    70000 NULL | 2 Ravi  IT 50000 1 | 3 Neha  HR    45000 NULL
    -- 4 Karan Sales 40000 3    | 5 Meera Sales 55000 3 | 6 Imran IT 50000 1
    -- departments(dept, city): IT Jaipur | HR Pune | Sales Delhi | Admin Noida
    SELECT name, NTILE(3) OVER (ORDER BY salary DESC, id) FROM employees ORDER BY salary DESC, id LIMIT 3;
    
  13. Question 13

    What is the output of this SQL query?

    -- employees(id, name, dept, salary, manager_id)
    -- 1 Asha  IT    70000 NULL | 2 Ravi  IT 50000 1 | 3 Neha  HR    45000 NULL
    -- 4 Karan Sales 40000 3    | 5 Meera Sales 55000 3 | 6 Imran IT 50000 1
    -- departments(dept, city): IT Jaipur | HR Pune | Sales Delhi | Admin Noida
    SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.id WHERE e.salary > m.salary;
    
  14. Question 14

    What is the output of this SQL query?

    -- employees(id, name, dept, salary, manager_id)
    -- 1 Asha  IT    70000 NULL | 2 Ravi  IT 50000 1 | 3 Neha  HR    45000 NULL
    -- 4 Karan Sales 40000 3    | 5 Meera Sales 55000 3 | 6 Imran IT 50000 1
    -- departments(dept, city): IT Jaipur | HR Pune | Sales Delhi | Admin Noida
    SELECT COUNT(*) FROM employees WHERE manager_id NOT IN (1, NULL);
    
  15. Question 15

    What is the output of this SQL query?

    -- employees(id, name, dept, salary, manager_id)
    -- 1 Asha  IT    70000 NULL | 2 Ravi  IT 50000 1 | 3 Neha  HR    45000 NULL
    -- 4 Karan Sales 40000 3    | 5 Meera Sales 55000 3 | 6 Imran IT 50000 1
    -- departments(dept, city): IT Jaipur | HR Pune | Sales Delhi | Admin Noida
    SELECT name, SUM(salary) OVER (ORDER BY id) FROM employees WHERE id <= 3;
    
🎯 Today’s placement challenge — 10 questions, no login. Can you score 10/10?Take today’s challenge →