学通4中数据库SQL教程练习和答案

oracleace 2023-05-07

508

“ 这个讲义如果有错误请联系号主。

B站上热播的SQL教程练习和答案，零基础学通4种数据库SQL语言（MySQL、Oracle、SQL Server和PostgreSQL），点击文章下面的“原文链接”可观看B站上的课程录像。 ”

关于号主，姚远：

Oracle ACE（Oracle和MySQL数据库方向）。
Oracle MAA 大师。
华为云MVP。
《MySQL 8.0运维与优化》的作者。
拥有 Oracle 10g和12c OCM等数十项数据库认证。
曾任IBM公司数据库部门经理
20年DBA工作经验，服务2万+客户。

SELECT字段的别名练习（答案）

编写一个SQL语句，输出下面的结果

mysql> select empno 员工号,salary 月薪, salary*14 14薪 from employees;
+-----------+----------+-----------+
| 员工号    | 月薪     | 14薪      |
+-----------+----------+-----------+
|         1 | 20000.00 | 280000.00 |
|         2 | 19100.00 | 267400.00 |
|         3 | 23900.00 | 334600.00 |
|         4 | 15000.00 | 210000.00 |
|         5 | 14200.00 | 198800.00 |
|         6 |  9700.00 | 135800.00 |
|         7 |  8900.00 | 124600.00 |
|         8 | 14900.00 | 208600.00 |
|         9 | 15000.00 | 210000.00 |
+-----------+----------+-----------+
9 rows in set (0.00 sec)

WHERE练习答案

mysql> select name,salary,salary*1.1 "updated salary",hire_date from employees  where hire_date<'2010-01-01';
+-----------+----------+----------------+------------+
| name      | salary   | updated salary | hire_date  |
+-----------+----------+----------------+------------+
| 周福生    | 20000.00 |      22000.000 | 2009-12-02 |
| 赵卫华    | 15000.00 |      16500.000 | 2009-11-12 |
+-----------+----------+----------------+------------+
2 rows in set (0.00 sec)

AND、OR 和NOT运算符练习

mysql>  select * from employees where deptno<>3 and salary>15000;




mysql>  select * from employees where not (deptno=3 or salary<=15000);

BETWEEN练习的答案

mysql> select name,salary from employees where salary between 10000 and 15000;
+-----------+----------+
| name      | salary   |
+-----------+----------+
| 赵六      | 15000.00 |
| 李明      | 14200.00 |
| 程娟      | 14900.00 |
| 赵卫华    | 15000.00 |
+-----------+----------+
4 rows in set (0.00 sec)

LIKE练习的答案

mysql> select email from employees where email like '_h%@gmail.com';
+----------------------+
| email                |
+----------------------+
| zhou@gmail.com       |
| chengjuan@gmail.com  |
| zhaoweihua@gmail.com |
+----------------------+
3 rows in set (0.00 sec)

ORDER BY练习答案

mysql> select name,deptno,hire_date from employees order by deptno,hire_date desc;
+-----------+--------+------------+
| name      | deptno | hire_date  |
+-----------+--------+------------+
| 赵六      |      1 | 2019-12-01 |
| 王五      |      1 | 2013-01-03 |
| 赵卫华    |      1 | 2009-11-12 |
| 孙军      |      2 | 2022-05-22 |
| 李四      |      2 | 2011-02-10 |
| 周福生    |      2 | 2009-12-02 |
| 李明      |      3 | 2021-09-11 |
| 钱杰      |      3 | 2019-06-12 |
| 程娟      |      3 | 2013-07-22 |
+-----------+--------+------------+
9 rows in set (0.00 sec)

NOT IN的坑练习答案

mysql> select deptno,dname from dept d
where d.deptno not in (select e.deptno from emp e where e.deptno is not null);
+--------+-----------+
| deptno | dname     |
+--------+-----------+
|      5 | Operation |
+--------+-----------+
1 row in set (0.00 sec)


mysql> select deptno,dname from dept d 
where not exists (select 1 from emp e where d.deptno=e.deptno);
+--------+-----------+
| deptno | dname     |
+--------+-----------+
|      5 | Operation |
+--------+-----------+
1 row in set (0.00 sec)

INSERT练习答案

insert into employees(empno,name,deptno,salary) values(17,'张小英',1,DEFAULT);

UPDATE练习答案

update employees set salary=salary*1.1 
where empno in 
(select managerno from departments);

DELETE练习答案

delete from employees where deptno=(select deptno from departments where managerno=2);

INNER JION内连接练习答案

select j.*,e.name,d.dname from job_history j join 
employees e on j.empno=e.empno join departments d 
on e.deptno=d.deptno;

自连接练习答案

select j1.empno from
(select empno from job_history j1 where deptno=2) j1
join
(select empno from job_history j2 where deptno=3) j2
on j1.empno=j2.empno;

外连接练习答案

select e.empno,name,start_date,j.deptno
 from job_history j right join employees e
 on
 j.empno=e.empno;

Union练习的答案

select name,hire_date,'创始人' 资深程度 from employees where hire_date <'2010-01-01'
union
select name,hire_date,'老员工' 资深程度 from employees where hire_date between '2010-01-01' and '2019-12-31'
union
select name,hire_date,'新员工' 资深程度 from employees where hire_date >'2019-12-31';

HAVING练习

 select empno,count(*) from job_history group by empno having count(*)>1;

子查询练习答案

select name,salary
from employees
where salary>(
            select avg(salary)
            from employees
  6              );




NAME           SALARY
---------- ----------
周福生          20000
王五            19100
李四            23900

IN运算符中的子查询练习答案

select name from employees
where empno not in (
                     select distinct empno
                     from job_history
                    );

子查询和连接的练习答案

 select name  from employees left join job_history using (empno) where start_date is null;

exists的练习

select name from employees 
where not exists 
  (select 1 from job_history where employees.empno=job_history.empno);

SELECT子句中的子查询练习答案

select dname,(select sum(salary) from employees where deptno=d.deptno) 部门工资总和  from departments d;

PARTITION BY的练习答案

select e2.*
from
(
   select e1.* ,
   rank() over (partition by deptno order by hiredate) as rank_date
   from employees e1
)  e2
where e2.rank_date=2;

CASE表达式练习的答案

select name, hiredate,
case 
when hiredate<'2010-01-01' then '创始人' 
when hiredate between '2010-01-01' and '2019-12-31' then '老员工'
else '新员工' end 资深程度
from employees
where hiredate is not null;

CTE练习答案

with em_ch as
( select * from employees 
where empno in 
       (select distinct empno from job_history)
)
select * from em_ch;

视图练习的答案

create view emp_qq as
select empno,name,salary,hiredate,email from employees 
where email like '%@qq.com'
order by hiredate
with check option;

欢迎加我的微信，拉你进数据库微信群👇

推荐文章👇

试看《MySQL 8.0运维与优化》（清华大学出版社）

Oracle和MySQL课程录像

托业890分的Oracle ACE为您翻译国际大佬的雄文（合集）

文章转载自oracleace，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

学通4中数据库SQL教程练习和答案

评论