1) What query calculates the cumulative sum of sales within each category? a) SELECT category_id, sales_amount, SUM(sales_amount) OVER (PARTITION BY category_id ORDER BY order_date) AS cumulative_sales FROM sales; b) SELECT SUM(sales_amount) FROM sales GROUP BY category_id; c) SELECT sales_amount FROM sales WHERE category_id IS NOT NULL; d) SELECT SUM(sales_amount) FROM sales; 2) How do you retrieve the difference in salary between each employee and their department's average? a) SELECT * FROM employees WHERE salary = AVG(salary); b) SELECT DISTINCT department_id, salary FROM employees; c) SELECT salary FROM employees ORDER BY salary; d) SELECT employee_id, salary, salary - AVG(salary) OVER (PARTITION BY department_iAS salary_diff FROM employees; 3) How do you find all customers who have placed at least three orders? a) SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(order_i>= 3; b) SELECT DISTINCT customer_id FROM orders WHERE COUNT(order_i>= 3; c) SELECT customer_id FROM orders WHERE order_id >= 3; d) SELECT * FROM customers WHERE orders >= 3; 4) What query finds customers who have never placed an order? a) SELECT customer_id FROM customers WHERE orders = 0; b) SELECT * FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders); c) SELECT DISTINCT customer_id FROM orders; d) SELECT customer_id FROM orders WHERE order_id IS NULL; 5) How do you find the median salary from an employees table? a) SELECT AVG(salary) FROM employees; b) SELECT salary FROM ( SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS row_num, COUNT(*) OVER() AS total_rows FROM employees) ranked WHERE row_num = total_rows / 2 OR row_num = (total_rows / 2) + 1; c) SELECT MEDIAN(salary) FROM employees; d) SELECT salary FROM employees WHERE salary > 0; 6) How do you retrieve the last three orders placed by each customer? a) SELECT employee_id FROM employees WHERE salary = salary; b) SELECT DISTINCT employee_id FROM employees; c) SELECT employee_id FROM employees ORDER BY salary; d) SELECT e1.* FROM employees e1 JOIN employees e2 ON e1.department_id = e2.department_id AND e1.salary = e2.salary AND e1.employee_id <> e2.employee_id; 7) How do you find employees who have the same salary as another employee in the same department? a) SELECT DISTINCT employee_id FROM employees; b) SELECT e1.* FROM employees e1 JOIN employees e2 ON e1.department_id = e2.department_id AND e1.salary = e2.salary AND e1.employee_id <> e2.employee_id; c) SELECT employee_id FROM employees WHERE salary = salary; d) SELECT employee_id FROM employees ORDER BY salary; 8) What query retrieves employees who have a direct manager? a) SELECT e1.employee_id, e1.name, e2.name AS manager_name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.employee_id; b) SELECT * FROM employees WHERE manager_id IS NOT NULL; c) SELECT DISTINCT employee_id FROM employees; d) SELECT employee_id FROM employees ORDER BY manager_id; 9) How do you find the percentage contribution of each product to total sales? a) SELECT product_id, SUM(sales_amount) AS product_sales, SUM(sales_amount) * 100.0 / SUM(SUM(sales_amount)) OVER () AS percentage_contribution FROM sales GROUP BY product_id; b) SELECT DISTINCT product_id, SUM(sales_amount) FROM sales; c) SELECT product_id FROM sales ORDER BY sales_amount; d) SELECT * FROM sales WHERE sales_amount > 0; 10) How do you calculate the year-over-year growth in sales? a) SELECT DISTINCT year, sales_amount FROM sales; b) SELECT year, sales_amount, sales_amount - LAG(sales_amount) OVER (ORDER BY year) AS yoy_growth FROM sales; c) SELECT year, SUM(sales_amount) FROM sales GROUP BY year; d) SELECT year FROM sales ORDER BY sales_amount;

SQL Query-Based Quiz 3

列印
嵌入

排行榜

視覺風格

選項

切換範本

恢復自動保存: ?