1. 下面的语句是否可以执行成功
select last_name , job_id , salary as sal from employees;
【成功】
2. 下面的语句是否可以执行成功 select*from employees;
【成功】
3. 找出下面语句中的错误
select employee_id , last_name,salary *12 “ANNUAL SALARY” from employees;
【last_name后的逗号是中文符的逗号,错误;别名的双引号也是中文符的,也是错误的】
4. 显示表 departments 的结构,并查询其中的全部数据
【DESC departments ;SELECT*FROM departments;】
5. 显示出表 employees 中的全部 job_id(不能重复)
【SELECTDISTINCT job_id FROM employees;】
6. 显示出表 employees 的全部列,各个列之间用逗号连接,列头显示成 OUT_PUT
【SELECT CONCAT(employee_id,",",first_name,"-",last_name,",",email,",",phone_number,",",job_id,",",salary,",",IFNULL(commission_pct,0),",",IFNULL(manager_id,0),",",department_id,",",hiredate) OUT_PUT
FROM employees;】
02-过滤数据
1. 查询工资大于 12000 的员工姓名和工资
SELECT first_name,last_name,salary FROM employees;2. 查询员工号为 176 的员工的姓名和部门号和年薪
SELECT first_name,last_name,department_id,salary*12 annul FROM employees WHERE employee_id=176;3. 选择工资不在 5000 到 12000 的员工的姓名和工资
SELECT first_name,last_name,salary FROM employees WHERE salary NOTBETWEEN5000AND12000;4. 选择在 20 或 50 号部门工作的员工姓名和部门号
SELECT first_name,last_name,department_id FROM employees WHERE department_id IN(20,50);5. 选择公司中没有管理者的员工姓名及 job_id
SELECT first_name,last_name,job_id FROM employees WHERE manager_id ISNULL;6. 选择公司中有奖金的员工姓名,工资和奖金级别
SELECT e.first_name,e.last_name,e.salary ,jg.grade_level FROM employees e
INNERJOIN job_grades jg ON e.salary BETWEEN jg.lowest_sal AND jg.highest_sal;7. 选择员工姓名的第三个字母是 a 的员工姓名
SELECT first_name ,last_name FROM employees WHERE first_name LIKE'__a%';8. 选择姓名中有字母 a 和 e 的员工姓名
SELECT first_name,last_name FROM employees WHERE first_name LIKE'%a%'OR'%e%';9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
SELECT*FROM employees WHERE first_name LIKE'%e';10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
SELECT first_name,last_name,job_id ,department_id FROM employees WHERE department_id BETWEEN80AND100;11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
SELECT first_name,last_name,job_id FROM employees e WHERE manager_id IN(100,101,110);
03-排序数据
1. 查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序
SELECT first_name,last_name ,department_id , salary*12 annul FROM employees ORDERBY annul DESC,first_name ASC;2. 选择工资不在 8000 到 17000 的员工的姓名和工资,按工资降序
SELECT first_name,last_name , salary FROM employees WHERE salary NOTBETWEEN8000AND17000ORDERBY salary DESC;3. 查询邮箱中包含 e 的员工信息,并先按邮箱的字节数降序,再按部门号升序
SELECT*FROM employees WHERE email LIKE'%e%'ORDERBY LENGTH(email)DESC,department_id ASC;
04-单行函数
1、显示系统时间(注:日期+时间)SELECTNOW()FROM DUAL;2、查询员工号,姓名,工资,以及工资提高百分之 20%后的结果(new salary)
SELECT employee_id,first_name,last_name,salary ,salary *(1+0.2) bonus FROM employees;3、 将员工的姓名按首字母排序,并写出姓名的长度(length)
SELECT employee_id,first_name,LENGTH(first_name)FROM employees ORDERBY first_name ASC;4、做一个查询,产生下面的结果
/* 提示:<last_name> earns <salary> monthly but wants <salary*3>
效果:
Dream Salary
King earns 24000 monthly but wants 72000
*/SELECT CONCAT(last_name," earns ",salary," monthly but wants ",salary*3)"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,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 JOB_ID END'Grade'FROM employees ;
一、查询编号>3 的女神的男朋友信息,如果有则列出详细,如果没有,用 NULL 填充
SELECT g.id,g.name,b.*FROM beauty g
LEFTOUTERJOIN boys b
ON g.boyfriend_id = b.id
WHERE g.id >3;
二、查询哪个城市没有部门
SELECT l.`location_id`,l.`city`,d.`department_id`,d.`location_id`FROM locations l
LEFTOUTERJOIN departments d
ON l.`location_id`= d.`location_id`WHERE d.`location_id`ISNULL;
三、查询部门名为 SAL 或 IT 的员工信息
SELECT e.*,d.department_name
FROM employees e
INNERJOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name IN('SAL','IT');
08-SQL92语法连接查询
1. 显示所有员工的姓名,部门号和部门名称。
SELECT e.first_name,e.department_id,d.department_name
FROM employees e
INNERJOIN departments d
ON e.department_id = d.department_id;2. 查询 90 号部门员工的 job_id 和 90 号部门的 location_id
SELECT d.department_id,e.job_id,d.location_id
FROM departments d
INNERJOIN employees e
ON d.department_id = e.department_id
WHERE d.department_id =90;3. 选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT e.last_name,e.`commission_pct`,d.department_name,d.location_id,l.city
FROM employees e
INNERJOIN departments d
ON e.department_id = d.department_id
INNERJOIN locations l
ON d.location_id = l.location_id
WHERE e.`commission_pct`ISNOTNULL;4. 选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT e.last_name,e.job_id,e.department_id,d.department_name,l.city
FROM employees e
INNERJOIN departments d
ON e.department_id = d.department_id
INNERJOIN locations l
ON d.location_id = l.location_id
WHERE l.city ='Toronto';5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT d.department_name,e.job_id,MIN(salary)FROM employees e
INNERJOIN departments d
ON e.`department_id`= d.`department_id`INNERJOIN jobs j
ON e.`job_id`= j.`job_id`GROUPBY d.department_id,e.job_id ;6.查询每个国家下的部门个数大于 2 的国家编号
SELECT*FROM city;SELECT*FROM country;SELECT*FROM countrylanguage;7、选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的
格 式 employees Emp# manager Mgr#
kochhar 101 king 100SELECT e.last_name,e.employee_id,m.last_name,m.employee_id
FROM employees e
LEFTOUTERJOIN employees m
ON e.manager_id = m.employee_id;
09-子查询
1. 查询和 Zlotkey 相同部门的员工姓名和工资
SELECT e.last_name,e.salary
FROM employees e
WHERE department_id =(SELECT department_id
FROM employees
WHERE last_name ='Zlotkey')AND last_name <>'Zlotkey';2. 查询工资比公司平均工资高的员工的员工号,姓名和工资。
SELECT e.employee_id,e.last_name,e.salary
FROM employees e
WHERE salary >(SELECTAVG(salary)FROM employees
);3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT e.employee_id,e.last_name,e.salary
FROM employees e
INNERJOIN(SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUPBY department_id
) dep_sal
ON e.department_id = dep_sal.department_id
WHERE e.salary > dep_sal.avg_sal
ORDERBY employee_id DESC;SELECT e1.employee_id,e1.last_name,e1.salary
FROM employees e1
WHERE salary >(SELECTAVG(salary)FROM employees e2
WHERE e1.`department_id`= e2.`department_id`GROUPBY department_id
)ORDERBY employee_id DESC;4. 查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN(SELECT department_id
FROM employees
WHERE last_name LIKE'%u%');5. 查询在部门的 location_id 为 1700 的部门工作的员工的员工号
SELECT e.employee_id
FROM employees e
WHERE department_id IN(SELECT department_id
FROM departments
WHERE location_id =1700);6. 查询管理者是 K_ing 的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id =(SELECT employee_id
FROM employees
WHERE last_name ='K_ing'AND manager_id ISNULL);7. 查询工资最高的员工的姓名,要求 first_name 和 last_name 显示为一列,列名为 姓.名
SELECT CONCAT(first_name,last_name)AS"姓名"FROM employees
WHERE salary =(SELECTMAX(salary)FROM employees
);
10-数据处理
CREATEDATABASEIFNOTEXISTS homeWork;1. 运行以下脚本创建表 my_employees
CREATETABLE my_employees(
Id INT(10),
First_name VARCHAR(10),
Last_name VARCHAR(10),
Userid VARCHAR(10),
Salary DOUBLE(10,2));CREATETABLE users(
id INT,
userid VARCHAR(10),
department_id INT);#2. 显示表 my_employees 的结构 DESC my_employees;3. 向 my_employees 表中插入下列数据
ID FIRST_NAME LAST_NAME USERID SALARY
1 patel Ralph Rpatel 8952 Dancs Betty Bdancs 8603 Biri Ben Bbiri 11004 Newman Chad Cnewman 7505 Ropeburn Audrey Aropebur 1550INSERTINTO my_employees VALUES(1,'patel','Ralph','Rpatel',895),(2,'Dancs','Betty','Bdancs',860),(3,'Biri','Ben','Bbiri',1100),(4,'Newman','Chad','Cnewman',860),(5,'Ropeburn','Audrey','Aropebur',1550);4. 向 users 表中插入数据
1 Rpatel 102,'Bdancs',103,'Bbiri',204,'Cnewman',305,'Aropebur',40INSERTINTO users VALUES(1,'Rpatel',10),(2,'Bdancs',10),(3,'Bbiri',20),(4,'Cnewman',30),(5,'Aropebur',40);5. 将 3 号员工的 last_name 修改为“drelxer”
UPDATE my_employees SET last_name="drelxer"WHERE id =3;6. 将所有工资少于 900 的员工的工资修改为 1000UPDATE my_employees SET salary =1000WHERE salary <900;7. 将 userid 为 Bbiri 的 USER 表和 my_employees 表的记录全部删除
DELETEFROM users WHERE userid ='Bbiri';DELETEFROM my_employees WHERE userid ='Bbiri';8. 删除所有数据
DELETEFROM users;DELETEFROM my_employees;9. 检查所作的修正
SELECT*FROM users;SELECT*FROM my_employees;10. 清空表 my_employees
DELETEFROM my_employees;
11-创建和管理表
1. 创建表 dept1
NAME NULL? TYPE
id INT(7)
NAME VARCHAR(25)CREATETABLE dept1(
id INT(7),`name`VARCHAR(25));2. 将表 departments 中的数据插入新表 dept2 中
CREATETABLE dept2 ASSELECT*FROM departments;3. 创建表 emp5
NAME NULL? TYPE
id INT(7)
First_name VARCHAR(25)
Last_name VARCHAR(25)
Dept_id INT(7)CREATETABLE emp5(
id INT(7),
First_name VARCHAR(25),
Last_name VARCHAR(25),
Dept_id INT(7));4. 将列 Last_name 的长度增加到 50ALTERTABLE emp5 MODIFY last_name VARCHAR(50);5. 根据表 employees 创建 employees2
CREATETABLE employees2 ASSELECT*FROM employees WHERE1=2;DESC employees2;6. 删除表 emp5
DROPTABLE emp5;7. 将表 employees2 重命名为 emp5
RENAMETABLE employees2 TO emp5;ALTERTABLE employees2 RENAMETO emp5;8 在表 dept1 和 emp5 中添加新列 test_column,并检查所作的操作
ALTERTABLE dept1 ADDCOLUMN test_column VARCHAR(32);ALTERTABLE emp5 ADDCOLUMN test_column VARCHAR(32);9.直接删除表 emp5 中的列 department_id
ALTERTABLE emp5 DROPCOLUMN department_id;