sql 1 ‐‐ ‐‐‐‐‐‐‐‐‐‐‐‐单表2 #1、查找部门30中员工的详细信息。34 #2、找出从事clerk工作的员工的编号、姓名、部门号。56 #3、检索出奖金多于基本工资的员工信息。78 #4、检索出奖金多于基本工资60的员工信息。910 #5、找出获得奖金的员工的工作。1112 #6、找出奖金少于100或者没有获得奖金的员工的信息。1314 #7、找出姓名以a、b、s开始的员工信息。

单表：

SELECT * FROM employees WHERE department_id = 30;
SELECT employee_id, first_name, last_name, department_id FROM employees WHERE job_id = 'CLERK';
SELECT * FROM employees WHERE commission_pct > salary;
SELECT * FROM employees WHERE commission_pct > salary * 0.6;
SELECT DISTINCT job_id FROM employees WHERE commission_pct IS NOT NULL;
SELECT * FROM employees WHERE commission_pct < 100 OR commission_pct IS NULL;
SELECT * FROM employees WHERE first_name LIKE 'a%' OR first_name LIKE 'b%' OR first_name LIKE 's%';
SELECT * FROM employees WHERE LENGTH(first_name) = 6;
SELECT * FROM employees WHERE first_name NOT LIKE '%r%';
SELECT * FROM employees ORDER BY first_name;
SELECT * FROM employees ORDER BY job_id DESC, salary ASC;
SELECT * FROM employees WHERE first_name LIKE '%a%';

多表：

SELECT e.first_name, e.last_name, m.first_name as manager_first_name, m.last_name as manager_last_name FROM employees e INNER JOIN employees m ON e.manager_id = m.employee_id;
SELECT e.first_name, e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;
SELECT e.first_name, e.last_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id WHERE e.job_id = 'CLERK';
SELECT e.first_name, e.last_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id WHERE d.department_name = 'Sales';
SELECT d.department_id, d.department_name, l.city, COUNT(e.employee_id) as employee_count FROM departments d INNER JOIN locations l ON d.location_id = l.location_id INNER JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name, l.city;
SELECT e.first_name, e.last_name, d.department_name, e.salary FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;
SELECT e.*, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;
SELECT job_id, MIN(salary) as min_salary FROM employees GROUP BY job_id;
SELECT first_name, last_name, salary * 12 as annual_salary FROM employees ORDER BY annual_salary;
SELECT first_name, last_name FROM employees WHERE salary BETWEEN 4500 AND 5999