小节练习一"/>
MySQL小节练习一
一:选择与过滤联系
SELECT employee_id , last_name,
salary * 12 "ANNUAL SALARY"
FROM employees;
– 去除重复行
SELECT DISTINCT job_id
FROM employees;
– 连接字符串
SELECT CONCAT(employee_id, ',' , last_name , ',', salary) OUT_PUT
FROM employees;
1.查询工资大于12000的员工姓名和工资
SELECT last_name, salary
FROM employees
WHERE salary > 12000;
2.查询员工号为176的员工的姓名和部门号
SELECT last_name, department_id
FROM employees
WHERE employee_id = 176;
3.选择工资不在5000到12000的员工的姓名和工资
SELECT last_name, salary
FROM employees
WHERE salary < 5000 OR salary > 12000;
SELECT last_name, salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;
4.选择在20或50号部门工作的员工姓名和部门号
SELECT last_name, department_id
FROM employees
WHERE department_id = 20 OR department_id = 50;
SELECT last_name, department_id
FROM employees
WHERE department_id IN(20, 50);
5.选择公司中没有管理者的员工姓名及job_id
SELECT last_name, job_id
FROM employees
WHERE manager_id IS NULL;
6.选择公司中有奖金的员工姓名,工资和奖金级别
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
7.选择员工姓名的第三个字母是a的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
8.选择姓名中有字母a和e的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
二:多表查询联系
1.显示所有员工的姓名,部门号和部门名称。
SELECT last_name, e.department_id, department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`;
2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id, location_id
FROM employees e, departments d
WHERE e.`department_id` = d.`department_id`
AND e.`department_id` = 90;
或
SELECT job_id, location_id
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` = 90;
3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT last_name , department_name , d.location_id , city
FROM employees e
LEFT OUTER JOIN departments d
ON e.`department_id` = d.`department_id`
LEFT OUTER JOIN locations l
ON d.`location_id` = l.`location_id`
WHERE commission_pct IS NOT NULL;
4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT last_name , job_id , e.department_id , department_name
FROM employees e, departments d, locations l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`
AND city = 'Toronto';
或
SELECT last_name , job_id , e.department_id , department_name
FROM employees e
JOIN departments d
ON e.`department_id` = d.`department_id`
JOIN locations l
ON l.`location_id` = d.`location_id`
WHERE l.`city` = 'Toronto';
5.选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
employees Emp# manager Mgr#
kochhar 101 king 100
SELECT emp.last_name employees, emp.employee_id "Emp#", mgr.last_name manager, mgr.employee_id "Mgr#"
FROM employees emp
LEFT OUTER JOIN employees mgr
ON emp.manager_id = mgr.employee_id;
三:单行函数练习
1.显示系统时间(注:日期+时间)
SELECT NOW() FROM DUAL;
2.查询员工号,姓名,工资,以及工资提高百分之20%后的结果(new salary)
SELECT employee_id, last_name, salary, salary * 1.2 "new salary"
FROM employees;
3.将员工的姓名按首字母排序,并写出姓名的长度(length)
SELECT last_name, LENGTH(last_name)
FROM employees
ORDER BY last_name DESC;
4.做一个查询,产生下面的结果
– <last_name> earns monthly but wants <salary*3>
– Dream Salary
– King earns 24000 monthly but wants 72000
SELECT CONCAT(last_name, ' earns ', TRUNCATE(salary, 0) , ' monthly but wants ', TRUNCATE(salary * 3, 0)) "Dream Salary"
FROM employees;
5.使用case-when,按照下面的条件:
– job grade
– AD_PRES A
– ST_MAN B
– IT_PROG C
– SA_REP D
– ST_CLERK E
– 产生下面的结果
– Last_name Job_id Grade
– king AD_PRES A
SELECT last_name Last_name, job_id Job_id, CASE job_id WHEN 'AD_PRES' THEN 'A'WHEN 'ST_MAN' THEN 'B'WHEN 'IT_PROG' THEN 'C'WHEN 'SA_REP' THEN 'D'WHEN 'ST_CLERK' THEN 'E'ELSE 'F'END "grade"
FROM employees;
更多推荐
MySQL小节练习一
发布评论