/*14. 相关表结构：
学生表 tbIStudent(编号 StuId、姓名 StuName、年龄SuAge、性别 Stusex)
课程表 tblCourse(课程编号 CourseId、课程名称 CourseName、教师编号 Teald)
成绩表 tblScore(学生编号 Stuld、课程编号 CourseId、成绩 Score)
教师表 tbITeacher(教师编号 Tenld、姓名 tenName)*/
-- （1）查询没有学全所有课的同学的学号、姓名：
SELECT sid, sname
FROM tb_student
WHERE sid IN
(SELECT stu_id
FROM tb_score
GROUP BY stu_id
HAVING COUNT(1)<
(SELECT COUNT(1) FROM tb_course));
-- （2）查询课程编号为“001的课程比课程编号为“002课程成绩高的所有学生的学号：
SELECT stu_id
FROM tb_score t1
WHERE course_id=1
AND stu_id IN
(SELECT t2.stu_id
FROM tb_score t2
WHERE t2.course_id=2
AND t1.`stu_id`=t2.`stu_id`
AND t1.`scores`>t2.`scores`);
-- （3）查询各科成绩最高和最低的分：以如下形式显示：课程名称，最高分，最低分
SELECT
t2.`cname` 课程名称,
MAX(scores) 最高分,
MIN(scores) 最低分
FROM tb_score t1
JOIN tb_course t2
WHERE t1.`course_id`=t2.`cid`
GROUP BY t1.`course_id`;
/*15. 根据以下两张数据表回答问题:
数据表A
编码(key) 数据日期(ds)
k1 2009-07-07
K2 2009-07-08
K3 2009-07-07
K5 2009-07-07
数据表B
编码(key) 数据日期(ds)
k1 2009-07-07
K2 2009-07-07
K3 2009-07-08
K4 2009-07-07
以下两个查询语句的结果分别是什么?*/
-- 查询1:
/*
SELECT
a.key, a.ds,
b.key, b.ds
FROM a
LEFT OUTER JOIN b
ON(a.key=b.key)
WHERE a.ds='2009-07-07'
AND b.ds= '2009-07-07';*/
/*查询结果：
a.KEY a.ds b.KEY b.ds
k1 2009-07-07 k1 2009-07-07*/
-- 查询2:
/*
SELECT
a.key, a.ds,
b.key, b.ds
FROM a
LEFT OUTER JOIN b
ON(a.key=b.key
AND a.ds='2009-07-07'
AND b.ds='2009-07-07');*/
/*查询结果：
a.KEY a. ds b.KEY b.ds
k1 2009-07-07 k1 2009-07-07
K2 2009-07-08 null null
K3 2009-07-07 null null
K5 2009-07-07 null null*/
/*
16. 产品入库表(ProductList)不同产品的入库时间可能相同。
入库序号(ID) 产品名称(ProductName) 数量(Count) 入库时间(DataTime)
0001 诺基亚N95 20 2009-1-1 00:00:00
0002 诺基亚N75 30 2009-1-1 00:00:00
0003 诺基亚N95 50 2009-1-1 00:10:00
... ... ... ...
用一条语句查询所有产品的最后一次入库记录。*/
SELECT *
FROM ProductList a
WHERE
(SELECT COUNT(1)
FROM ProductList b
WHERE a.ProductName=b.ProductName
AND a.DataTime<b.DataTime)=0;
/*17.使用SQL解决以下问题。
部门表dept:
dept-no 部门编号
dept-name 部门名称
location 部门位置
员工表emp:
emp_no 编号
emp_name 姓名
title 职位
mar 领导编号
hire-date 雇佣日期
month-time 发工资日期
salary 基本工资
bonus 奖金
dept-no 所在部门编号
工资等级表grade:
grade 工资等级
losal 此等级工资下限
hisal 此等级工资上限*/
-- （1）写一条查询语句,返回:部门2018年1-3月实发工资总额, 2018年4-6月实发工资总额,
-- 2018年7-9月实发工资总额
SELECT
SUM(IF(QUARTER(`month-time`)=1, salary, 0)) AS "2018年1-3月实发工资总额",
SUM(IF(QUARTER(`month-time`)=2, salary, 0)) AS "2018年4-6月实发工资总额",
SUM(IF(QUARTER(`month-time`)=3, salary, 0)) AS "2018年7-9月实发工资总额"
FROM emp
WHERE YEAR(`month-time`)=2018
GROUP BY SUBSTRING(`month-time`,1,6);
-- （2）在财务部,人事部,运营部,各随机取5位员工的员工编号,姓名,职位,基本工资(一条语句)
SELECT
t1.emp_no,
t1.emp_name,
t1.title,
t1.salary
FROM emp t1
JOIN
(SELECT empno
FROM emp
WHERE deptno IN
(SELECT id
FROM department
WHERE dep_name ='财务部')
ORDER BY RAND()
LIMIT 5) t2
ON t1.`empno`=t2.empno
JOIN
(SELECT empno
FROM emp
WHERE deptno IN
(SELECT id
FROM department
WHERE dep_name ='人事部')
ORDER BY RAND()
LIMIT 5) t3
ON t1.`empno`=t3.empno
JOIN
(SELECT empno
FROM emp
WHERE deptno IN
(SELECT id
FROM department
WHERE dep_name ='运营部')
ORDER BY RAND()
LIMIT 5) t4
ON t1.`empno`=t4.empno;
-- （3）题目:查询出每个员工的姓名,工资,部门名称,工资在公司的等级及其领导的姓名及其工资所在的等级
SELECT
t1.emp_name,
t1.`salary`,
t2.`dept-name`,
t3.`grade`,
t4.emp_name,
t4.grade
FROM emp t1
JOIN dept t2
ON t1.`dept-no`=t2.`dept-no`
JOIN grade t3
ON t1.`salary` BETWEEN t3.losal AND t3.hisal
LEFT JOIN
(SELECT a.*,b.grade
FROM emp a ,grade b
WHERE a.`salary` BETWEEN b.losal AND b.hisal ) t4
ON t1.mar=t4.emp_no ;
/*
18. 已知
表a: 表b:
id name id age
1 zhao 1 30
2 li 2 28
3 wang 4 21
select a.id,a.name,b.age
from a
( ) b
on a.id=b.id;
分别写出 join, left join, right join在()时,sql语句的执行结果。*/
/* join
a.id a.name b.age
1 zhao 30
2 li 28 */
/* left join
a.id a.name b.age
1 zhao 30
2 li 28
3 wang null*/
/* right join
a.id a.name b.age
1 zhao 30
2 li 28
null null 21*/
/*19.已知表a和表b,编写sql统计ios和 android的用户总点击数 total click
(若一次sq无法完成统计,可以使用临时表c、d、e等暂存中间数据,无需实现建表过程,描述清楚表的字段即可)
表a： 表b:
id device id click
1 ios 1 3
2 android 3 4
3 ios 2 3
1 2
2 5
4 2 */
SELECT a.device,SUM(b.click) total_click
FROM a,b
WHERE a.id=b.id
GROUP BY a.device;
/*20.例:数据表
province city sellCount
北京市 朝阳区 100
北京市 朝阳区 200
北京市 朝阳区 111
北京市 西城区 300
北京市 西城区 789
北京市 西城区 310
北京市 东城区 456
北京市 东城区 789
北京市 东城区 111
需求:编写SQL，取每个 city sellCount的top2结果，如下表所示
朝阳区 200
朝阳区 100
东城区 789
东城区 456
西城区 789
西城区 310*/
SELECT a.city,a.sellCount
FROM `data` a
WHERE
(SELECT COUNT(1)
FROM `data` b
WHERE a.city=b.city
AND a.sellCount<b.sellCount)<2;
/*21.P_ID为产品ID,P_Num为产品库存量S_ID为仓库ID.请用SQL语句实现将上表中的数据合并。
P_ID P_ NUM S_ID
1 10 1
1 12 2
2 8 1
3 11 1
3 8 3
结果如下:S1_ID为仓库1的库存量,S2_ID为仓库2的库存量,S3_ID为仓库3的库存量。
如果该产品在某仓库中无库存量,那么就是0代替。
P_ID S1_ID S2 _ID S3_ID
1 10 12 0
2 8 0 0
3 11 0 8*/
SELECT
P_ID,
SUM(IF(S_ID=1,IFNULL(P_NUM,0),0)) AS S1_ID,
SUM(IF(S_ID=2,IFNULL(P_NUM,0),0)) AS S2_ID,
SUM(IF(S_ID=3,IFNULL(P_NUM,0),0)) AS S3_ID
FROM product
GROUP BY P_ID;
/*22. 用一条SQL语句查询出每门课都大于80分的学生姓名
Name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90*/
-- 第一种含义，每门课指 语文，数学，英语，没考的不满足条件
SELECT t1.`name`
FROM
score t1
WHERE t1.fenshu>80
GROUP BY t1.`name`
HAVING COUNT(t1.kecheng)=
(SELECT COUNT(1)
FROM score
GROUP BY `name`
ORDER BY COUNT(1) DESC
LIMIT 1);
-- 第二种含义，每门课是指每位学生各自有成绩的课程
SELECT t1.`name`
FROM
score t1
WHERE t1.fenshu>80
GROUP BY t1.`name`
HAVING COUNT(t1.kecheng)=
(SELECT COUNT(1)
FROM score t2
WHERE t1.`name`=t2.`name`
GROUP BY `name`);
/*23. 学生表如下
自动编号 学号 姓名 课程编号 课程名称 分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同，其他都相同的学生冗余信息*/
DELETE FROM student a
WHERE '自动编号' IN
(SELECT '自动编号'
FROM student b
GROUP BY b.'学号',b.'姓名',b.'课程编号',b.'课程名称',b.'分数'
HAVING COUNT(1)>1);
/*24. 怎么把这样一个表
Year month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
查成这样一个结果
year m1 m2 m3 m4
1991 1.1 1.2 1.3 1.4*/
SELECT
`Year`,
SUM(IF(`month`=1,amount,0)) m1,
SUM(IF(`month`=2,amount,0)) m2,
SUM(IF(`month`=3,amount,0)) m3,
SUM(IF(`month`=4,amount,0)) m4
FROM amount
WHERE `Year`=1991
GROUP BY `Year`;
-- 25.缩写SQL语句
-- 1）创建一张学生表，包含以下信息，学号，姓名，年龄，性别，家庭住址，联系电话
DROP TABLE IF EXISTS ts_student;
CREATE TABLE ts_student (
stu_id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(16),
age INT,
gender VARCHAR(4),
adress VARCHAR(200),
phone VARCHAR(11) );
-- 2）修改学生表的结构，添加一列信息，学历
ALTER TABLE ts_student ADD COLUMN academic VARCHAR(32);
-- 3）修改学生表的结构，删除一列信息，家庭住址
ALTER TABLE ts_student DROP COLUMN adress;
-- 4）修改学生表的数据，将电话号码以11开头的学员的学历改为“大专”
UPDATE ts_student
SET academic='大专'
WHERE phone LIKE '11%';
-- 26.查询A（ID,Name）表中第31至40条记录，ID作为主键可能是不是连续增长的列，完整的查询语句如下：
SELECT *
FROM A
WHERE ID BETWEEN 31 AND 40;
-- 27.查询表A中存在ID重复三次以上的记录，完整的查询语句如下：
SELECT *
FROM A
GROUP BY ID
HAVING COUNT(1)>3;
-- 36.两个表A和B，均有key和value两个字段，写一个SQL语句，将B表中的value值置成A表中相同key值对应的value值
UPDATE B b
SET b.`value`=
(SELECT a.`value`
FROM A a
WHERE a.key=b.key);
/*56.有一张APP用户操作记录表,有userid (用户ID) ,imit (手机型号), day (日期)
要找出前天和昨天imit不一样的信息
userid imit day
a a-1 10.1
a a-1 10.2
b b-1 10.1
b b-2 10.1
b b-1 10.2
b b-3 10.2*/
SELECT *
FROM app_op a
WHERE a.imit !=
(SELECT b.imit
FROM app_op b
WHERE DATEDIFF(REPLACE(a.day,'.','-'),REPLACE(b.day,'.','-'))=1
AND a.userid=b.userid);
/*57.有一张表,记录用户的登陆信息, userid (用户ID) , imit (手机型号),post,time (时间),
找出昨天每个用户最早一笔和最晚一笔的用户id,时间,手机型号
userid imit post time
a a-1 xxxxx 4.19.30
a a-1 xxxxx 4.21.33
a a-1 xxxxx 4.22.24
b b-2 xxxxx 16.19.20
b b-1 xxxxx 17.20.20
b b-3 xxxxx 20.30.55
b b-4 xxxxx 23.15.13*/
SELECT t1.userid,t1.imit,t1.time
FROM user_login t1
JOIN
(SELECT t2.time
FROM user_login t2
GROUP BY t2.userid
ORDER BY t2.time ASC)
ON t1.time=t2.time
JOIN
(SELECT t3.time
FROM user_login t3
GROUP BY t3.userid
ORDER BY t3.time DESC)
ON t1.time=t3.time;
/*60.查询学生表的数据,用一条SQL语句查询出每门课都大于80分的学生
并将所有年龄小于22岁的,学历为“大专”的学生的姓名和学号显示出来？。
name kecheng fenshu
张三 语文 81
张三 数学 75
李四 语文 76
李四 数学 90
王五 语文 81
王五 数学 100
王五 英语 90*/
SELECT a.name
FROM sudent a
WHERE a.fenshu >80
GROUP BY a.name
AND COUNT(1)=
(SELECT COUNT(1)
FROM sudent b
GROUP BY b.name);
/*61.原表如下:
Day sales
2018-01-10 100
2018-01-11 200
2017-12-10 300
2017-01-10 400
2017-01-11 500
用sql显示如下格式 Day,本期销售额、昨日销售额、上月销售额、上年同期销售额*/
SELECT
a.`day`,
a.`sales` 本期销售额,
(SELECT b.sales FROM sales b WHERE SUBDATE(a.day,1)=b.day) 昨日销售额,
(SELECT SUM(IFNULL(c.sales,0)) FROM sales c WHERE DATE_ADD(a.day,INTERVAL -1 MONTH)=b.day) 上月销售额,
(SELECT SUM(IFNULL(d.sales,0)) FROM sales d DATE_ADD(a.day,INTERVAL -1 YEAR)=b.day) 上年同期销售额
FROM sales a
/*64. 表名: student
name course score
张青 语文 72
王华 数学 72
张华 英语 81
张青 物理 67
李立 化学 98
张燕 物理 70
张青 化学 76
用sql查询出“张”姓学生中平均成绩大于75分的学生信息;*/
SELECT *
FROM student
WHERE `name` LIKE "张%"
GROUP BY `name`
HAVING AVG(score)>75;
/*65. 有如下三张表
a、学生基本信息表(xh学号、xm姓名、xb性别、nl年龄、bj班级)
b、学生课程信息表(xh学号、kch课程编号、kcm课程名称、1s代课老师姓名)
c、学生成绩表 (xh学号、xq学期、kch课程编号,cj成绩
根据以上三张表完成以下SQL语句:*/
-- （1）查询各班级每位学生的所选课程信息,并按班级和课程排序,具体字段如下:
-- 学号,姓名,性别,年龄,班级,课程名称,代课老师姓名
SELECT t1.xh,t1.xm,t1.xb,t1.nl,t1.bj,t2.kcm,t2.ls
FROM studnet t1
JOIN
(SELECT a.xh,b.kcm,b.1s
FROM score a
JOIN course b
ON a.xh=b.xh
AND a.kch=b.kch
GROUP BY a.xh,a.kch) t2
ON t1.xh=t2.xh
ORDER BY t1.bj,t2.kcm;
-- （2）查询二.1班第一学期姓李的且年龄大于13岁的所有同学的课程和成绩信息,具体字段如下:
-- 学号,姓名,性别,年龄,班级, 代课老师姓名,课程名称,成绩
SELECT t1.xh,t1.xm,t1.xb,t1.nl,t1.bj,t2.ls,t2.kcm,t2.cj
FROM student t1
JOIN
(SELECT a.xh,a.cj,b.kcm,b.1s
FROM score a
JOIN course b
ON a.xh=b.xh
AND a.kch=b.kch
GROUP BY a.xh,a.kch) t2
ON t1.xm LIKE "李%"
AND t1.nl>13
AND 1.xh=t2.xh;
-- （3）查询各班各科成绩的及格率,平均分,最高分,最低分,并按班级和及格降序排序,具结果字段如下:
-- 班级、课程名称、及格率、平均分、最高分、最低分
SELECT
t1.bj,t2.kcm,
CONCAT(SUM(IF(score>=60,1,0))*100/COUNT(1),'%') 及格率,
AVG(score) 平均分,
MAX(score) 最高分,
MIN(score) 最低分
FROM student t1
JOIN course t2
ON t1.xh=t2.xh
JOIN score t3
ON t1.xh = t3.xh
AND t2.kch=t3.kch
GROUP BY t1.bj
ORDER BY
班级 DESC,
及格率 DESC;
-- （4）查询所有姓名性别相同且所选课程及总成绩相同的学生信息,具体字段如下:
-- 学号、班级、姓名、性别、课程名称、成绩
SELECT t1.xh,t1.bj,t1.xm,t1.xb,t3.kcm,t2.cj
FROM
(SELECT a.*
FROM student a
JOIN student b
ON a.xm=b.xm
AND a.xb=b.xb) t1
JOIN
(SELECT c.*
FROM score c
JOIN score d
ON c.xh=d.xh
AND c.ckh=d.ckh
GROUP BY c.xh
HAVING SUM(c.cj)=SUM(d.cj)) t2
ON t1.xh=t2.xh
JOIN course t3
ON t2.xh=t3.xh
AND t2.kch=t3.kch;
-- （5）查询每个学生所选的课程数、代课老师+课程名称、总成绩;具体格式如下
-- 学号 姓名 班级 所选课程 所选课程数 总成绩
-- 0001 张三 二.1 李老师:语文, 张老师:数学 2 185
SELECT
a.xh 学号,a.xm 姓名,a.bj 班级,
GROUP_CONCAT(b.ls,':',b.kcm) 所选课程,
COUNT (1) 所选课程数,
SUM(c.cj) 总成绩
FROM student a
JOIN course b
ON a.xh=b.xh
JOIN score c
ON a.xh=c.xh
AND b.kch=c.kch
GROUP BY a.xh;
-- （6）将三.2班所有学生的英语课的老师更新为王老师
UPDATE course a
SET ls="王老师"
WHERE a.xh IN
(SELECT b.xh
FROM student
WHERE b.bj='三.2班');
-- （7）查询各班总成绩排名前10的同学,并按班级和成绩从高到低排序,具体结果字段如下:
-- 班级、姓名、姓名、总成绩、排名
SELECT a.bj,a.xm,SUM(b.cj),@r=@r+1
FROM student a,(SELECT @r=0) c
JOIN score b
ON a.xh=b.xh
GROUP BY a.xh,b.kch
ORDER BY
a.bj DESC,
b.cj DESC
HAVING @r<=10;
/*66.下表用户登记表(TB_USER_REG),每个用户可能有多次登记记录,但是身份证不会变,
希望用SQL一次性查询出所有用户的最新信息
身份证号(ID_NUM） 姓名(NAME) 年龄(ACG) 登记时间(REG_TIME)
12345 张三丰 25 2018-01-01 10:10:20.111
12345 张三疯 22 2015-01-01 0810:20.111
12346 李四 33 2018-01-02 09:10:20.111
12347 王五 34 2018-01-03 11:10:20.111*/
SELECT *
FROM TB_USER_REG
GROUP BY ID_NUM
ORDER BY REG_TIME DESC;
/*67. 下表足球比赛记录表(TB_RECORD),主客场双循环,通过一条SQL统计每个球队的积分情况
(赢一场3分,输一场0分,平一场1分)
主队(HOME_TEAM) 客队(VISIT_TTEAM) 主队进球(HOME_GOAL) 客队进球(VISIT_GOAL)
上海 南京 2 1
南京 上海 2 2
上海 北京 3 1
北京 上海 2 3*/
SELECT
`HOME_TEAM`,
SUM(
(CASE WHEN HOME_GOAL> VISIT_GOAL
THEN 3
WHEN HOME_GOAL= VISIT_GOAL
THEN 1
ELSE 0 END)) 总积分
FROM TB_RECORD
GROUP BY HOME_TEAM ;
1420
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
