SQL基础练习

我不是女神ヾ 2023-06-25 13:29 37阅读 0赞

花了一天时间看了一下尚硅谷的sql视频，自己做了一下老师提供的练习题并进行核对，算是把sql基础复习了一遍。资料放在文末可自取。

## 表结构 ##

### employees员工表 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU1MTAzNg_size_16_color_FFFFFF_t_70]

## 一、 基本的语句 ##

### 题目 ###

1.  下面的语句是否可以执行成功

select last_name , job_id , salary as sal
    from employees;

1.  下面的语句是否可以执行成功

select * from employees;

1.  找出下面语句中的错误

select employee_id , last_name，
    salary * 12 “ANNUAL SALARY”
    from employees;

1.  显示表 `departments` 的结构，并查询其中的全部数据
2.  显示出表 `employees` 中的全部 job\_id（不能重复）
3.  显示出表 `employees` 的全部列，各个列之间用逗号连接，列头显示成 OUT\_PUT

### 练习 ###

#1.	下面的语句是否可以执行成功  
    SELECT last_name , job_id , salary AS sal
    FROM employees; 
    
    #2.下面的语句是否可以执行成功  
    SELECT  *  FROM employees; 
    
    #3.找出下面语句中的错误 
    SELECT employee_id , last_name,
    salary * 12 AS "ANNUAL  SALARY"
    FROM employees;
    
    #4.显示表departments的结构，并查询其中的全部数据
    SELECT * FROM `departments`;
    
    #5.显示出表employees中的全部job_id（不能重复）
    SELECT DISTINCT job_id FROM employees;
    
    #6.显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT
    SELECT 
    	IFNULL(commission_pct,0) AS 奖金率,
    	commission_pct
    FROM 
    	employees;	
    #-------------------------------------------
    SELECT
    	CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0)) AS out_put
    FROM
    	employees;

## 二、条件查询（P267） ##

### 题目 ###

1.  查询工资大于 12000 的员工姓名和工资
2.  查询员工号为 176 的员工的姓名和部门号和年薪
3.  选择工资不在 5000 到 12000 的员工的姓名和工资
4.  选择在 20 或 50 号部门工作的员工姓名和部门号
5.  选择公司中没有管理者的员工姓名及 `job_id`
6.  选择公司中有奖金的员工姓名，工资和奖金级别
7.  选择员工姓名的第三个字母是 a 的员工姓名
8.  选择姓名中有字母 a 和 e 的员工姓名
9.  显示出表 `employees` 表中 `first_name` 以 'e’结尾的员工信息
10. 显示出表 `employees` 部门编号在 80-100 之间 的姓名、职位
11. 显示出表 `employees` 的 `manager_id` 是 100,101,110 的员工姓名、职位

### 练习 ###

# 1. 查询工资大于 12000 的员工姓名和工资
    SELECT last_name,salary
    from employees
    where salary>12000
    
    
    
    # 2. 查询员工号为 176 的员工的姓名和部门号和年薪
    SELECT last_name, department_id,salary*12*(1+IFNULL(commission_pct,0)) as 年薪
    from employees
    where employee_id = 176
    
    
    # 3. 选择工资不在 5000 到 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
    -- 
    
    # 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%"
    -- 
    
    # 9. 显示出表 employees 表中 first_name 以 'e'结尾的员工信息
    SELECT *
    from employees
    where first_name like "%e"
    -- 
    
    # 10. 显示出表 employees 部门编号在 80-100 之间 的姓名、职位
    select last_name, job_id
    from employees
    where department_id BETWEEN 80 and 100
    -- 
    
    # 11. 显示出表 employees 的 manager_id 是 100,101,110 的员工姓名、职位
    select last_name, job_id
    from employees
    where manager_id in (100,101,110)

## 二、排序（P270） ##

### 题目 ###

1.  查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
2.  选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
3.  查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序

### 练习 ###

# 1. 查询员工的姓名和部门号和年薪，按年薪降序 按姓名升序
    SELECT last_name, department_id,salary*12*(1+IFNULL(commission_pct,0)) as 年薪
    from employees
    ORDER BY 年薪 DESC,last_name ASC
    
    # 2. 选择工资不在 8000 到 17000 的员工的姓名和工资，按工资降序
    select last_name,salary
    from employees
    WHERE salary not BETWEEN 8000 and 17000
    ORDER BY salary DESC
    
    # 3. 查询邮箱中包含 e 的员工信息，并先按邮箱的字节数降序，再按部门号升序
    SELECT *
    from employees
    where email LIKE "%e%"
    ORDER BY LENGTH(email) DESC, department_id ASC

## 三、常见函数（P277） ##

### 题目 ###

1.  显示系统时间(注：日期+时间)
2.  查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
3.  将员工的姓名按首字母排序，并写出姓名的长度（length）
4.  做一个查询，产生下面的结果  
    <last\_name> earns monthly but wants <salary\*3>

<table> 
 <thead> 
  <tr> 
   <th>Dream Salary</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>King earns 24000 monthly but wants 72000</td> 
  </tr> 
 </tbody> 
</table>

1.  使用 case-when，按照下面的条件：

<table> 
 <thead> 
  <tr> 
   <th>job</th> 
   <th>grade</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>AD_PRES</td> 
   <td>A</td> 
  </tr> 
  <tr> 
   <td>ST_MAN</td> 
   <td>B</td> 
  </tr> 
  <tr> 
   <td>IT_PROG</td> 
   <td>C</td> 
  </tr> 
  <tr> 
   <td>SA_REP</td> 
   <td>D</td> 
  </tr> 
  <tr> 
   <td>ST_CLERK</td> 
   <td>E</td> 
  </tr> 
 </tbody> 
</table>

产生下面的结果:

<table> 
 <thead> 
  <tr> 
   <th>Last_name</th> 
   <th>Job_id</th> 
   <th>Grade</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>king</td> 
   <td>AD_PRES</td> 
   <td>A</td> 
  </tr> 
 </tbody> 
</table>

### 练习 ###

# 1. 显示系统时间(注：日期+时间)
    SELECT NOW()
    
    # 2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果
    SELECT employee_id, last_name, salary, salary*1.2 as "New salary"
    from employees
    
    # 3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
    SELECT last_name, LENGTH(last_name)
    from employees
    ORDER BY SUBSTR(last_name,1,1)
    
    # 4. 做一个查询，产生下面的结果
    # <last_name> earns <salary> monthly but wants <salary*3>
    SELECT CONCAT(last_name,' earns ',salary,' monthly but he wants ',salary*3) as "Dream Salary"
    from employees
    
    # 5. 使用 case-when，按照下面的条件：
    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'
    END as Grade
    
    from employees

## 四、测试题3（P284） ##

### 题目 ###

1.  查询员工姓名、入职日期并按入职日期升序
2.  将当前日期显示成xxxx年xx月xx日

### 练习 ###

# 1. 查询员工姓名、入职日期并按入职日期升序
    SELECT last_name,hiredate
    from employees
    ORDER BY YEAR(hiredate) ASC
    
    # 2. 将当前日期显示成xxxx年xx月xx日
    SELECT DATE_FORMAT(now(),"%Y年%m月%d日")

## 五、外连接查询（P288） ##

### 题目 ###

1.  查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 null 填充
2.  查询哪个城市没有部门
3.  查询部门名为 SAL 或 IT 的员工信息

### 练习 ###

# 1. 查询编号>3 的女神的男朋友信息，如果有则列出详细，如果没有，用 null 填充
    select b.name,bo.*
    from beauty b
    left join boys bo
    on b.boyfriend_id=bo.id
    where b.id>3
    
    # 2. 查询哪个城市没有部门
    select city
    from departments d
    RIGHT JOIN locations l
    on d.location_id=l.location_id
    where d.department_id is null
    
    # 3. 查询部门名为 SAL 或 IT 的员工信息
    SELECT d.*,e.*
    from departments d
    left join employees e
    on d.department_id = e.department_id
    where department_name in ('SAL','IT')

## 六、子查询（P290+P303） ##

### 题目 ###

1.  查询和 Zlotkey 相同部门的员工姓名和工资
2.  查询工资比公司平均工资高的员工的员工号，姓名和工资。
3.  查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
4.  查询和姓名中包含字母 u 的员工在相同部门的员工的员工号和姓名
5.  查询在部门的 location\_id 为 1700 的部门工作的员工的员工号
6.  查询管理者是 King 的员工姓名和工资
7.  查询工资最高的员工的姓名，要求 first\_name 和 last\_name 显示为一列，列名为 姓.名

### 练习 ###

# 1. 查询和 Zlotkey 相同部门的员工姓名和工资
    SELECT last_name,salary
    from employees
    WHERE department_id in (
    	SELECT department_id
    	from employees
    	where last_name='Zlotkey'
    )
    # 2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。
    SELECT employee_id,last_name,salary
    from employees
    where salary > (
    	SELECT AVG(salary)
    	FROM employees
    )
    # 3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资（！）
    SELECT employee_id,last_name,salary
    from employees e
    JOIN (
    	SELECT department_id, avg(salary) as ag
    	from employees
    	GROUP BY department_id
    )as dep_ag
    on e.department_id = dep_ag.department_id
    WHERE e.salary> dep_ag.ag
    
    # 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 employee_id
    FROM employees e
    JOIN departments d
    ON e.department_id = d.department_id
    WHERE d.location_id=1700
    
    # 6. 查询管理者是 King 的员工姓名和工资
    SELECT last_name,salary
    from employees
    WHERE manager_id in (
    	SELECT employee_id
    	from employees
    	WHERE last_name = "K_ing"
    )
    
    # 7. 查询工资最高的员工的姓名，要求 first_name 和 last_name 显示为一列，列名为 姓.名
    SELECT CONCAT(first_name,'.',last_name) as "姓.名"
    from employees
    where salary in (
    	SELECT max(salary)
    	from employees
    )

### 相关资料 ###

尚硅谷的sql教程[https://www.bilibili.com/video/av49181542?p=267][https_www.bilibili.com_video_av49181542_p_267]

尚硅谷mysql教程配套资料，  
链接: [https://pan.baidu.com/s/1thTHme-QtXPuowX5fgGNnQ][https_pan.baidu.com_s_1thTHme-QtXPuowX5fgGNnQ] 提取码: 1ykn

这里有测试数据 [https://blog.csdn.net/GongmissYan/article/details/102937816][https_blog.csdn.net_GongmissYan_article_details_102937816]

某位大神根据视频整理的笔记[https://cloudlandboy.github.io/myNote/\#/backend/mysql/dbandsql][https_cloudlandboy.github.io_myNote_backend_mysql_dbandsql]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU1MTAzNg_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191229144049673.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzU1MTAzNg==,size_16,color_FFFFFF,t_70
[https_www.bilibili.com_video_av49181542_p_267]: https://www.bilibili.com/video/av49181542?p=267
[https_pan.baidu.com_s_1thTHme-QtXPuowX5fgGNnQ]: https://pan.baidu.com/s/1thTHme-QtXPuowX5fgGNnQ
[https_blog.csdn.net_GongmissYan_article_details_102937816]: https://blog.csdn.net/GongmissYan/article/details/102937816
[https_cloudlandboy.github.io_myNote_backend_mysql_dbandsql]: https://cloudlandboy.github.io/myNote/#/backend/mysql/dbandsql