一些简单的sql练习

• 准备数据

CREATE TABLE students
(sno VARCHAR(3) not null,
sname VARCHAR(3) not null,
ssex VARCHAR(2) not null,
sbirthday datetime,
class VARCHAR(5)
)

CREATE table courses(
cno VARCHAR(5) not null,
cname VARCHAR(10) not null,
tno VARCHAR(10)not null
)

CREATE TABLE scores(
sno VARCHAR(3) not null,
cno VARCHAR(5) not null,
degree NUMERIC(10,1)not null
)

CREATE TABLE teachers(
tno VARCHAR(3) not null,
tname VARCHAR(4) not null,
tsex VARCHAR(2) not null,
tbirthday datetime not null ,
prof varchar(6),
depart VARCHAR(10) not null
)
插入数据
INSERT students (sno,sname,ssex,sbirthday,class)VALUES (108,'曾华','男','1977-09-01',95033);
INSERT students (sno,sname,ssex,sbirthday,class)VALUES (105,'匡明','男','1975-10-02',95031);
INSERT students (sno,sname,ssex,sbirthday,class)VALUES (107,'王丽','女','1976-01-23',95033);
INSERT students (sno,sname,ssex,sbirthday,class)VALUES (101,'李军','男','1977-02-20',95033);
INSERT students (sno,sname,ssex,sbirthday,class)VALUES (109,'王芳','女','1977-02-10',95031);
INSERT students (sno,sname,ssex,sbirthday,class)VALUES (103,'陆君','男','1977-06-03',95031);

INSERT courses(cno,cname,tno)values ('3-105','计算机导论',825);
INSERT courses(cno,cname,tno)values ('3-245','操作系统',804);
INSERT courses(cno,cname,tno)values ('6-166','数据电路',856);
INSERT courses(cno,cname,tno)values ('9-888','高等数学',100);

insert scores(sno,cno,degree)VALUES(103,'3-245',86);
insert scores(sno,cno,degree)VALUES(105,'3-245',75);
insert scores(sno,cno,degree)VALUES(109,'3-245',68);
insert scores(sno,cno,degree)VALUES(103,'3-105',92);
insert scores(sno,cno,degree)VALUES(105,'3-105',88);
insert scores(sno,cno,degree)VALUES(109,'3-105',76);
insert scores(sno,cno,degree)VALUES(101,'3-105',64);
insert scores(sno,cno,degree)VALUES(107,'3-105',91);
insert scores(sno,cno,degree)VALUES(108,'3-105',78);
insert scores(sno,cno,degree)VALUES(101,'6-166',85);
insert scores(sno,cno,degree)VALUES(107,'6-166',79);
insert scores(sno,cno,degree)VALUES(108,'6-166',81);
-- 多行插入
INSERT teachers(tno,tname,tsex,tbirthday,prof,depart)VALUES
(804,'李诚','男','1958-12-02','副教授','计算机系'),
(256,'张旭','男','1969-03-12','讲师','电子工程系'),
(825,'王萍','女','1972-08-14','助教','计算机系'),
(931,'刘冰','女','1977-05-05','助教','电子工程系');

• 题目及答案

1.-- 查询students表中的所有记录sname、ssex和class列。
SELECT sname,ssex,class from students;

2.-- 查询教师所有的单位即不重复的depart列
SELECT distinct depart from teachers;

distinct 不寻常的有区别的
3.-- 查询student表中的所有记录
SELECT *from students;

4.-- 查询score表中成绩在60到80之间的所有记录
SELECT *from scores WHERE degree BETWEEN 60 and 80;

between 在...之间
5.-- 查询score表中成绩为85,86或88的记录
SELECT *FROM scores where degree in (85,86,88);

6.-- 查询student表中'95031'班或性别为'女'的同学记录
SELECT *FROM students WHERE class='95031' or ssex='女';

SELECT * from students WHERE ssex='女' 
UNION
SELECT * from students WHERE class='95031'

7.-- 以class降序查询student表中的所有记录
select *from students ORDER BY class DESC;

order 顺序,次序
8.-- 以cno升序 degree降序查询score表的所有记录
SELECT *from scores ORDER BY cno asc,degree DESC;

9.-- 查询'95031'班的人数
SELECT COUNT(1) as stunum from students where class='95031';

count 数,计数
count（1）计算一共有多少符合条件的行
10.-- 查询score表中的最高分的学生学号和课程号.
SELECT sno,cno from scores ORDER BY degree desc LIMIT 1;

-- 11.查询'3-105'号课程的平均分
SELECT AVG(degree) FROM scores WHERE cno='3-105';

-- 12.查询score表中至少有5名学生选修的并以3
开头的课程的平均数
SELECT cno,avg(degree) 
FROM scores 
WHERE cno LIKE '3%' 
GROUP BY cno 
HAVING COUNT(*)>=5;

-- 13.查询最低分大于70,最高分小于90的sno列
SELECT sno 
FROM scores 
GROUP BY sno 
HAVING MAX(degree)<90 AND min(degree)>70;

-- 14.查询所有学生的sname.cno和degree列
 select sname,cno,degree 
 from students join scores 
 on students.sno=scores.sno 
 ORDER BY sname;

-- 15.查询所有学生的sno.cname和degree列
SELECT sno,cname,degree 
FROM scores JOIN courses 
on scores.cno=courses.cno 
ORDER BY sno;

-- 16.查询所有的学生的sname.cname和degree列
SELECT sname,cname,degree
FROM scores JOIN courses 
ON scores.cno=courses.cno JOIN students 
ON students.sno=scores.sno 
ORDER BY sname;

-- 17.查询'95033'班的所选课程的平均分
SELECT  cname,AVG(degree)
FROM scores JOIN students     #scores和students 建立连接
ON scores.sno=students.sno      #条件 学生学号相同
JOIN courses            #再与courses建立连接
ON scores.cno=courses.cno        #课程的编号
WHERE class='95033'            #条件student 95033 班的学生学号建立连接获取成绩参与平均分的算法,学生scores中课程编号与课程中编号对应的课程名字返回
GROUP BY courses.cno    #以95033班所选的课程分组
ORDER BY cname;

-- 18

-- 19.查询选修'3-105'课程的成绩高于109号同学成绩的所有记录
SELECT s1.sno,s1.degree
FROM scores s1 INNER JOIN scores s2
on s1.cno =s2.cno AND s1.degree>s2.degree
WHERE s1.cno='3-105' AND s2.sno='109'
ORDER BY s1.sno

-- 20.查询score中选学一门以上课程同学中分数为非最高分成绩的记录
select *
FROM scores
GROUP BY sno
HAVING COUNT(cno)>1 AND degree!=MAX(degree)

-- 21.查询成绩高于学号为109 课程号为3-105的成绩的所有记录
SELECT s1.sno,s1.degree
FROM scores s1 JOIN scores s2
on s1.cno=s2.cno AND s1.degree>s2.degree
WHERE s1.cno='3-105' AND s2.sno='109'
ORDER BY s1.sno

-- 22.查询和学号为108的同学同年同月出售的所有学生的
-- sno.sname和sbirthday列
SELECT s1.sno,s1.sname,s1.sbirthday
FROM students s1 JOIN students s2
on YEAR(s1.sbirthday)=YEAR(s2.sbirthday)
WHERE s2.sno='108'

-- 23.查询'王萍'教师任课的学生成绩
SELECT sno,degree
FROM scores inner JOIN courses
on scores.cno=courses.cno 
INNER JOIN teachers
on courses.tno=teachers.tno
WHERE teachers.tname='王萍'
-----另一种写法
SELECT sno,degree
FROM teachers ,courses,scores
WHERE teachers.tname='王萍'

-- 24.查询选修某课程的同学人数多于5人的教师姓名
SELECT DISTINCT tname
FROM scores INNER JOIN courses
on scores.cno=courses.cno 
JOIN teachers
on courses.tno=teachers.tno
WHERE courses.cno in (SELECT cno FROM scores GROUP BY cno HAVING COUNT(sno)>5)
------
SELECT DISTINCT tname
FROM teachers,courses,scores
WHERE courses.cno=scores.cno AND courses.tno=teachers.tno AND
courses.cno = (SELECT cno FROM scores GROUP BY cno HAVING COUNT(*)>5)
======
SELECT DISTINCT tname ,cname
FROM scores JOIN courses
on scores.cno=courses.cno
JOIN teachers
on courses.tno=teachers.tno
GROUP BY courses.cname 
HAVING COUNT(sno)>5

-- 25.查询95033班和95031班全体学生的记录
SELECT *
FROM students
WHERE class in ('95033','95031')
ORDER BY class

-- 26.查询存在有85分以上成绩的课程cno
SELECT DISTINCT cno
FROM scores
WHERE degree>85
----
SELECT DISTINCT courses.cname
FROM scores ,courses
WHERE scores.cno=courses.cno AND degree>85

-- 27.查询出计算机系教师所教课程的成绩表
SELECT tname,cname,sname,degree
FROM teachers JOIN courses
on teachers.tno=courses.tno 
JOIN scores
on courses.cno=scores.cno 
JOIN students
on scores.sno=students.sno
WHERE teachers.depart='计算机系'
ORDER BY tname,cname,degree DESC

-- 28.查出计算机系与电子工程系不同职称的教师的tname和prof
SELECT tname,prof
FROM teachers
WHERE depart='计算机系' AND prof NOT in(
    SELECT DISTINCT prof
    FROM teachers
    WHERE depart='电子工程系')

-- 29.查询选修编号为3-105 课程且成绩至少高于任意选修编号为3-245的同学的成绩的cno.sno和degree,并按degree从高到低次序排序
SELECT cno,sno,degree
FROM scores
WHERE cno='3-105' and degree >any(
    SELECT degree
    FROM scores
    WHERE cno='3-245')
    ORDER BY degree DESC
 ----写完自己就看不懂了的写法
 SELECT DISTINCT s1.cno,s1.sno,s1.degree
FROM scores s1 JOIN scores s2
on s1.degree>s2.degree and
s1.cno='3-105' and s2.cno='3-245'
ORDER BY degree desc 

--     30.查询选修编号为3-105 且成绩高于所有选修编号为3-245课程的同学的cno,sno和degree
SELECT cno,sno,degree
FROM scores
WHERE cno='3-105' AND degree>ALL(
SELECT degree
FROM scores
WHERE cno='3-245')
ORDER BY degree desc
any关键词可以理解为“对于子查询返回的列中的任一数值，如果比较结果为true，则返回true”。
all的意思是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”

--     31.查询所有教师和同学的name sex 和birthday
SELECT sname ,ssex ,sbirthday
FROM students
UNION
SELECT tname,tsex,tbirthday
FROM teachers

-- 32.查询所有女教师和女同学的name.sex和birthday
SELECT sname,ssex,sbirthday
FROM students
WHERE ssex='女'
UNION
SELECT tname,tsex,tbirthday
FROM teachers
WHERE tsex='女'

-- 33查询成绩比该课程平均成绩低的同学的成绩表
SELECT s1.*
FROM scores as s1 inner JOIN(
    SELECT cno,AVG(degree) as adegree
    FROM scores
    GROUP BY cno) s2
    on (s1.cno=s2.cno and s1.degree<s2.adegree)

--     34查询所有任课教师的tname和depart
SELECT tname,depart
FROM teachers 
WHERE tno in (
    SELECT tno
    FROM courses
)

--     35查询所有未讲课的教师的tname和depart
SELECT tname,depart
FROM teachers
WHERE tno NOT in(
    SELECT tno
    FROM courses)

--     36查询至少有2名男生的班号
SELECT class,COUNT(1) as boyCount
FROM students
WHERE ssex='男'
GROUP BY class
HAVING boyCount>=2

-- 37.查询student表中不姓王的同学的记录
SELECT *
FROM students
WHERE sname not LIKE '王%'

-- 38.查询student表中每个学生的姓名和年龄
SELECT sname,YEAR(NOW())-YEAR(sbirthday) as sage
FROM students

-- 39.查询student表中最大和最小的sbirthday日期值
SELECT min(sbirthday),max(sbirthday)
FROM students

-- 40.以班号和年龄从大到小的顺序查询student表中的全部记录
SELECT *
FROM students
ORDER BY class DESC ,sbirthday ASC;

-- 41.查询男教师及其所上的课程
SELECT teachers.tname,courses.cname
FROM teachers JOIN courses
ON teachers.tno=courses.tno
WHERE teachers.tsex='男'

-- 42.查询最高分同学的sno.cno和degree列
SELECT sno,cno,degree
FROM scores
GROUP BY cno
HAVING degree=max(degree)

-- 43.查询和李军同性别的所有同学的sname
SELECT s1.sname 
FROM students s1 JOIN students s2
on s1.ssex=s2.ssex
WHERE s2.sname='李军'

-- 44.查询和李军同性别并同班的同学sname
SELECT s1.sname
FROM students as s1 JOIN students as s2
on s1.ssex=s2.ssex AND s1.class=s2.class
WHERE s2.sname='李军'

-- 45.查询所有选项计算机导论课程的男同学的成绩表
SELECT *
FROM scores
WHERE sno in(
    SELECT sno
    FROM students
    WHERE ssex='男') AND
    cno in(
    SELECT cno
    FROM courses
    WHERE cname='计算机导论')
 ====other
 SELECT students.sno,students.sname,scores.degree,courses.cname,students.class
from students JOIN scores
on students.sno=scores.sno
JOIN courses
on scores.cno=courses.cno
WHERE courses.cname='计算机导论' and students.ssex='男'
ORDER BY students.sno