SQL Skills Audio Quiz Quiz

1. Departments with More than 5 Employees

   Which SQL clause is used to filter groups to only show departments with more than 5 employees?

   1. HAVING COUNT(*) u003E 5
   2. WHERE COUNT(*) u003E 5
   3. GROUP BY COUNT(*) u003E 5
   4. ORDER BY COUNT u003E 5
   5. HAVING GROUP u003E 5

2. Finding Employees Earning Above Average

   How would you select the names and salaries of employees who earn more than the average salary in the employees table?

   1. SELECT name, salary FROM employees WHERE salary u003E (SELECT AVG(salary) FROM employees);
   2. SELECT name, salary FROM employees WHERE salary u003E AVG(salary);
   3. SELECT name FROM employees HAVING salary u003E AVG(salary);
   4. SELECT name, salary FROM employees WHERE salary u003E (AVG(salary));
   5. SELECT name, salary FROM employees WHERE salary u003E ALL salaries;

3. Aggregate Functions for Average Salary

   What is the correct SQL to find the average salary per department in an employees table?

   1. SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;
   2. SELECT AVG(salary), department FROM employees WHERE department GROUP BY salary;
   3. SELECT department, salary FROM employees GROUP BY AVG(salary);
   4. SELECT department, AVG(salary) FROM employees;
   5. SELECT department AVG(salary) FROM employees GROUP TO department;

4. INNER JOIN for Order Details

   To retrieve order details along with the customer name for all orders, which SELECT statement is correct?

   1. SELECT orders.order_id, orders.order_date, customers.name AS customer_name FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id;
   2. SELECT order_id, order_date, name FROM orders JOIN customers WHERE orders.customer_id = customers.customer_id;
   3. SELECT order_id, order_date, customer_name FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id;
   4. SELECT * FROM orders CROSS JOIN customers;
   5. SELECT orders.id, customers.name FROM customers INNER JOIN orders ON customer_id = customers.customer_id;

5. IN Clause for Multiple Departments

   Which query selects the names of employees working in either the HR or Finance departments using the IN clause?

   1. SELECT name FROM employees WHERE department IN ('HR', 'Finance');
   2. SELECT name FROM employees WHERE department = 'HR' OR 'Finance';
   3. SELECT name FROM employees WHERE department BETWEEN 'HR' AND 'Finance';
   4. SELECT name FROM employees IN ('HR', 'Finance');
   5. SELECT name FROM employees WHERE department LIKE 'HR|Finance';

6. Self JOIN for Employees in Same Department

   To find all pairs of employees who work in the same department but are not the same individual, what is the correct SQL query?

   1. SELECT e1.name AS employee1, e2.name AS employee2, e1.department FROM employees e1 INNER JOIN employees e2 ON e1.department = e2.department WHERE e1.employee_id u003Cu003E e2.employee_id;
   2. SELECT name, department FROM employees WHERE employee_id != employee_id;
   3. SELECT * FROM employees WHERE department = department;
   4. SELECT e1.name, e2.name, department FROM employees;
   5. SELECT employee1, employee2, department FROM employees e1 JOIN employees e2 ON e1.employee_id u003Cu003E e2.employee_id;

7. Updating Salaries in a Department

   How can you update all salaries by 10% for employees in the Sales department?

   1. UPDATE employees SET salary = salary * 1.10 WHERE department = 'Sales';
   2. UPDATE employees ADD salary = salary * 1.10 WHERE department = 'Sales';
   3. UPDATE employees SET salary = salary + 10% WHERE department = 'Sales';
   4. UPDATE salary FROM employees WHERE department = 'Sales' SET salary = salary * 1.10;
   5. UPDATE employees WHERE department = 'Sales' SET salary = salary * 1.1;

8. CASE Statement for Salary Status

   What SQL can list all employee names along with their salary status marked as 'High' if salary is greater than 50000, else 'Low'?

   1. SELECT name, CASE WHEN salary u003E 50000 THEN 'High' ELSE 'Low' END AS salary_status FROM employees;
   2. SELECT name, IF salary u003E 50000 THEN 'High' ELSE 'Low' AS salary_status FROM employees;
   3. SELECT name, CASE salary u003E 50000 THEN 'High' ELSE 'Low' FROM employees;
   4. SELECT name WITH salary_status CASE WHEN salary u003E 50000 IS 'High' ELSE 'Low';
   5. SELECT name, salary u003E 50000 AS salary_status FROM employees;

9. LEFT JOIN for Customers With No Orders

   Which SQL query lists all customers and their order totals, showing 0 for customers with no orders?

   1. SELECT c.name, COALESCE(SUM(o.amount), 0) AS total_amount FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.name;
   2. SELECT name, SUM(amount) FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id;
   3. SELECT name, COALESCE(amount, 0) FROM customers LEFT JOIN orders ON customer_id = customer_id;
   4. SELECT c.name, ISNULL(SUM(o.amount), 0) AS total_amount FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;
   5. SELECT c.name, SUM(o.amount) FROM customers c RIGHT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.name;

10. Using Subquery in DELETE

    How do you delete all employees who have not placed any orders?

    1. DELETE FROM employees WHERE employee_id NOT IN (SELECT DISTINCT employee_id FROM orders);
    2. DELETE employees WHERE employee_id NOT EXISTS (SELECT employee_id FROM orders);
    3. DELETE FROM employees IF NOT EXISTS (SELECT employee_id FROM orders);
    4. DELETE FROM employees WHERE NOT IN (SELECT employee_id FROM orders);
    5. DELETE FROM employees WHERE employee_id IN (SELECT employee_id FROM orders);