SQL Basics Quiz: Writing and Optimizing CRUD Queries Quiz

1. Using SELECT with WHERE and Indexes

   Suppose a table Employees has columns id (primary key), name, and department. Which SQL query will efficiently return all employees in the 'Finance' department if there is an index on the department column?

   1. SELECT * FROM Employees WHERE department = 'Finance';
   2. SELECT * FROM Employees WHERE name = 'Finance';
   3. SELEC * FROM Employees WHERE department = 'Finance';
   4. SELECT * FORM Employees WHERE department = 'Finance';
   5. SELECT * FROM Employees WHEN department = 'Finance';

2. Inserting New Records

   To add a new employee with id 5, name 'Dana', and department 'HR' to the Employees table, which SQL statement is correct?

   1. INSERT INTO Employees (id, name, department) VALUES (5, 'Dana', 'HR');
   2. ADD INTO Employees (id, name, department) VALUES (5, 'Dana', 'HR');
   3. INSERT Employees (id, name, department) VALUES (5, 'Dana', 'HR');
   4. INSERT INTO Employees VALUES (id = 5, name = 'Dana', department = 'HR');
   5. INSERT Employees VALUES (5, 'Dana', 'HR');

3. Updating Single Row

   How would you change the department of the employee with id 3 to 'Marketing' in the Employees table?

   1. UPDATE Employees SET department = 'Marketing' WHERE id = 3;
   2. MODIFY Employees SET department = 'Marketing' WHERE id = 3;
   3. UPDATE Employees department = 'Marketing' WHERE id = 3;
   4. UPDATE Employees SET department := 'Marketing' WHERE id = 3;
   5. UPDATE Employees SET department = 'Marketing';

4. Deleting Rows

   What SQL command properly removes all employees from the Employees table whose department is 'Temp'?

   1. DELETE FROM Employees WHERE department = 'Temp';
   2. REMOVE FROM Employees WHERE department = 'Temp';
   3. DROP FROM Employees WHERE department = 'Temp';
   4. DELETE Employees WHERE department = 'Temp';
   5. DELETE * FROM Employees WHERE department = 'Temp';

5. Retrieving Unique Values Efficiently

   Which SQL query retrieves a unique, sorted list of departments from the Employees table, and would benefit from an index on department?

   1. SELECT DISTINCT department FROM Employees ORDER BY department;
   2. SELECT department FROM Employees ORDER FROM department;
   3. SELECT UNIQUE department FROM Employees SORT BY department;
   4. SELECT ALL department FROM Employees ORDER department;
   5. SELECT department FROM Employees GROUP BY department ASC;

6. Aggregating Data

   How would you get the count of employees in each department from the Employees table?

   1. SELECT department, COUNT(*) FROM Employees GROUP BY department;
   2. SELECT department, COUNT(*) FROM Employees ORDER BY department;
   3. SELECT COUNT(department) FROM Employees;
   4. COUNT BY department FROM Employees;
   5. SELECT department, TOTAL() FROM Employees GROUP BY department;

7. Efficient Range Query

   You want to find all employees with IDs between 10 and 20, inclusive, using an efficient index scan. What is the correct SQL?

   1. SELECT * FROM Employees WHERE id BETWEEN 10 AND 20;
   2. SELECT * FROM Employees WHERE id u003E= 10 u0026 id u003C= 20;
   3. SELECT * FROM Employees WHEN id BETWEEN 10 AND 20;
   4. SELECT * FROM Employees WHERE id FROM 10 TO 20;
   5. SELECT * FROM Employees WHERE id IN (10, 20);

8. Deleting All Records

   Which statement will delete all records from the Employees table but leave the table structure in place?

   1. DELETE FROM Employees;
   2. DROP TABLE Employees;
   3. REMOVE Employees;
   4. DELETE * FROM Employees;
   5. TRUNCATE Employees;

9. Optimizing an Expensive Calculation

   For a table StaffHours recording entry and exit times, you need to calculate the duration between each 'Enter' and the following 'Exit' event for each staff member. Which optimized SQL approach, available in SQL databases supporting analytic functions, performs this in a single table scan?

   1. Use LAG and LEAD analytic functions to fetch previous and next event times, computing the difference per row in a single query.
   2. Write a series of nested queries using self-joins over five Common Table Expressions (CTEs).
   3. Loop over each row with a cursor and calculate duration one at a time.
   4. Run a query for each staff member separately and join results manually.
   5. Use a view and update the view after every INSERT or DELETE on the table.

10. Improving SELECT Query Performance

    If a SELECT query filtering on a particular column is identified as slow due to full table scans, what is a standard SQL method for optimization?

    1. Create an index on the column used in the WHERE clause.
    2. Increase the query timeout value.
    3. Rewrite the query with SELECT DISTINCT.
    4. Group results by the slow column.
    5. Add more columns to the SELECT statement.