1번
SELECT name FROM doctors WHERE major = '성형외과';
2번
SELECT major, COUNT(*) FROM doctors GROUP BY major;
3번
SELECT COUNT(*) FROM doctors WHERE DATEDIFF(y, CURDATE(), hire_date) >= 5;
4번
SELECT name, DATEDIFF(d, CURDATE(), hire_date) FROM doctors;
1번
SELECT gender, COUNT(*) FROM patients GROUP BY gender;
2번
SELECT COUNT(*) FROM patients WHERE DATEDIFF(y, CURDATE(), birth_date) >= 40;
3번
SELECT * FROM patients WHERE DATEDIFF(y, CURDATE(), last_visit_date) >= 1;
4번
SELECT COUNT(*) FROM patients WHERE YEAR(birth_date) = 1980;
1번
SELECT COUNT(*) FROM departments;
2번
SELECT employees.name, departments.name
FROM employees JOIN departments ON employees.department_id = departments.id;
3번
SELECT employees.name
FROM employees JOIN departments ON employees.department_id = departments.id
WHERE departments.name = '기술팀';
4번
SELECT departments.name, COUNT(*)
FROM employees JOIN departments ON employees.department_id = departments.id
GROUP BY departments.name;
5번
SELECT departments.name
FROM departments LEFT JOIN employees ON departments.id = employees.department_id
WHERE employees.name is NULL;
6번
SELECT employees.name
FROM employees JOIN departments ON employees.department_id = departments.id
WHERE departments.name = '마케팅팀';
1번
SELECT orders.id, products.name
FROM orders JOIN products ON orders.product_id = products.id;
2번
SELECT products.id, SUM(products.price * orders.quantity)
FROM orders JOIN products ON orders.product_id = products.id
GROUP BY products.id
ORDER BY SUM(price * quantity) DESC
LIMIT 1;
3번
SELECT products.id, SUM(quantity)
FROM orders
GROUP BY products.id;
4번
SELECT products.name
FROM orders JOIN products ON orders.product_id = products.id
WHERE DATE(order_date) >= DATE('2023-03-03');
5번
SELECT products.name
FROM orders JOIN products ON orders.product_id = products.id
GROUP BY products.id
ORDER BY orders.quantity DESC
LIMIT 1;
6번
SELECT product_id, AVG(quantity)
FROM orders
GROUP BY product_id;
7번
SELECT products.id, products.name
FROM products LEFT JOIN orders ON products.id = orders.product_id
WHERE orders.quantity IS NULL
GROUP BY products.id;
