请选择 进入手机版 | 继续访问电脑版

SQL综合练习草稿

[复制链接]
滚雪球少年 发表于 2020-12-31 18:57:31 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
  1. -- 练习一DROP TABLE Employee;CREATE TABLE If Not Exists Employee(id INT, name VARCHAR(255),salary INT,departmentid INT);INSERT INTO Employee(id,name,salary,departmentid) VALUES (1,&#39;Joe&#39;,70000,1);INSERT INTO Employee(id,name,salary,departmentid) VALUES (2,&#39;Henry&#39;,80000,2);INSERT INTO Employee(id,name,salary,departmentid) VALUES (3,&#39;Sam&#39;,60000,2);INSERT INTO Employee(id,name,salary,departmentid) VALUES (4,&#39;Max&#39;,90000,1);DROP TABLE Department;Create table If Not Exists Department (id INT, name VARCHAR(255));INSERT INTO Department (Id, name) VALUES (&#39;1&#39;, &#39;IT&#39;);INSERT INTO Department (Id, name) VALUES (&#39;2&#39;, &#39;Sales&#39;);SELECT d.name AS &#39;Department&#39;,sa.Employee AS &#39;Employee&#39;,sa.Salary AS &#39;Salary&#39;FROM  -- 自链接找出个部门工资的最大值,并以工资金额作为毗连条件(SELECT e.departmentid AS &#39;Department&#39;, e.name AS &#39;Employee&#39;,m.s AS &#39;Salary&#39;FROM Employee AS eINNER JOIN (SELECT *,max(salary) AS  &#39;s&#39;FROM Employee AS eGROUP BY e.departmentid)AS mON e.salary = m.s) AS sa-- 再把部门表里的部门名字毗连进来INNER JOIN Department AS dON sa.Department = d.id;-- 练习二Create table If Not Exists seat(id int NOT NULL PRIMARY KEY AUTO_INCREMENT, student varchar(255));insert into seat (id, student) values (&#39;1&#39;, &#39;Abbot&#39;);insert into seat (id, student) values (&#39;2&#39;, &#39;Doris&#39;);insert into seat (id, student) values (&#39;3&#39;, &#39;Emerson&#39;);insert into seat (id, student) values (&#39;4&#39;, &#39;Green&#39;);insert into seat (id, student) values (&#39;5&#39;, &#39;Jeames&#39;);-- 条件语句,如果是因为最后一位是5(单数),所以若余数为1且ID不为5,则退一位,余数为零则进一位,余数为一且id=5则不动。SELECT (CASE WHEN mod(id,2) = 1 AND id = max(id) THEN id                                                        WHEN mod(id,2) = 0 THEN id-1                                                        ELSE id +1 END) AS &#39;id&#39;, studentFROM seatORDER BY id;-- 练习三DROP TABLE score;CREATE table if not EXISTS score(id int not null primary key auto_increment,                                                                                                                                Score DECIMAL(11,2));insert into score (id, Score) values (&#39;1&#39;, 3.50);insert into score (id, Score) values (&#39;2&#39;, 3.65);insert into score (id, Score) values (&#39;3&#39;, 4.00);insert into score (id, Score) values (&#39;4&#39;, 3.85);insert into score (id, Score) values (&#39;5&#39;, 4.00);insert into score (id, Score) values (&#39;6&#39;, 3.65);SELECT Score ,DENSE_RANK() OVER (ORDER BY Score DESC) AS &#39;RANK&#39;FROM score;-- 练习七SELECT Score ,RANK() OVER (ORDER BY Score DESC) AS &#39;RANK&#39;FROM score;SELECT count(id) FROM seat;-- 练习四CREATE TABLE if not EXISTS Log(id int not null primary key auto_increment,                                                                                                                                num int);INSERT INTO Log(id, num) VALUES (1,1);INSERT INTO Log(id, num) VALUES (2,1);INSERT INTO Log(id, num) VALUES (3,1);INSERT INTO Log(id, num) VALUES (4,2);INSERT INTO Log(id, num) VALUES (5,1);INSERT INTO Log(id, num) VALUES (6,2);INSERT INTO Log(id, num) VALUES (7,2);SELECT l.num as &#39;ConsecutiveNums &#39; FROM-- 找出出现过的数字(SELECT num FROM LogGROUP BY num) AS lINNER JOIN -- 数一下出现过的数字分别出现过反复(SELECT num, COUNT(*) AS timesFROM LogGROUP BY num) AS c-- 毗连条件为出现过数字,且出现次数大于3次的ON l.num = c.numWHERE c.times >3;-- 练习六CREATE TABLE if not EXISTS emp_manag(id int not null PRIMARY KEY,                                                                                                                        name VARCHAR(255),                                                                                                                        department VARCHAR(255) not null,                                                                                                                        managerid int DEFAULT NULL);INSERT INTO emp_manag(id, name, department, managerid) VALUES (101,&#39;John&#39;,&#39;A&#39;,NULL);INSERT INTO emp_manag(id, name, department, managerid) VALUES (102,&#39;Dan&#39;,&#39;A&#39;,101);INSERT INTO emp_manag(id, name, department, managerid) VALUES (103,&#39;James&#39;,&#39;A&#39;,101);INSERT INTO emp_manag(id, name, department, managerid) VALUES (104,&#39;Amy&#39;,&#39;A&#39;,101);INSERT INTO emp_manag(id, name, department, managerid) VALUES (105,&#39;Anne&#39;,&#39;A&#39;,101);INSERT INTO emp_manag(id, name, department, managerid) VALUES (106,&#39;Ron&#39;,&#39;B&#39;,101);SELECT em.name FROM emp_manag AS emINNER JOIN-- 选出带五个员工的管理者(SELECT id, name, managerid,count(managerid) AS workersFROM emp_managGROUP BY manageridHAVING workers =5) AS l-- 毗连管理员名字与idON em.id = l.managerid;SELECT id, name FROM emp_manag;-- 练习九INSERT INTO Employee(id,name,salary,departmentid) VALUES (5,&#39;Janet&#39;,69000,1);INSERT INTO Employee(id,name,salary,departmentid) VALUES (6,&#39;Randy&#39;,85000,1);SELECT department.name AS &#39;Department&#39;,d.r_name AS &#39;Employee&#39;, d.r_salary AS &#39;Salary&#39;FROM departmentINNER JOIN -- 毗连部门名称表(SELECT r.departmentid AS r_id ,r.name AS r_name, r.salary AS r_salaryFROM-- 用窗口函数举行分组排序(SELECT id,name, salary,departmentid, RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC) AS rankingFROM employee) AS r-- 把每组前三选出WHERE r.ranking < 4) AS dON d.r_id = department.id;-- 练习十CREATE TABLE if not EXISTS shortest(x int,                                                                                                                        y int);INSERT INTO shortest (x,y) VALUES (-1,-1);INSERT INTO shortest (x,y) VALUES (0,0);        INSERT INTO shortest (x,y) VALUES (-1,-2);        SELECT MIN(            ROUND(                        sqrt(                                    power((p1.x-p2.x),2)+power((p1.y-p2.y),2)                        )                        ,2)            ) AS shortestFROM shortest as p1INNER JOIN shortest as p2on (p1.x,p1.y)(p2.x,p2.y);-- 练习十一-- 暂未写完,背面再补
复制代码
 

来源:https://blog.csdn.net/weixin_41814086/article/details/111877934
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

发布主题

专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )