数据库练习（三(作业））

数据库练习（一）

不看题目不看表了，上来就做题吧
(注释：本文存在大量不确定性，我才刚学sql，这些题是老师留的作业，有的挺难的，我也是写一点查一点，请各位谨慎观看，酌情采用）

36.查询至少有2名男生的班号。

我的答案，感觉不太对劲

#36.查询至少有2名男生的班号。
select class
from students
where ssex = '男'
group by class
having count(ssex) >= 2;

count(1) or count(*)

高性能MySQL——Count(1) OR Count(*)？

37、查询Student表中不姓“王”的同学记录。

easy

#37、查询Student表中不姓“王”的同学记录。
select * 
from students
where sno not in
(select sno from students
where sname LIKE '王%');

38.查询Student表中每个学生的姓名和年龄。

select sname,(year(now()) - year(sbirthday)) as age
from students;

😰这个年龄有毒吧，吓得我查了一下

没问题了，打扰了。

sql标量函数

SQL 标量函数-----日期函数 day() 、month()、year()

39.查询Student表中最大和最小的Sbirthday日期值。

#39.查询Student表中最大和最小的Sbirthday日期值。
select max(sbirthday), min(sbirthday)
from students;

40.以班号和年龄从大到小的顺序查询Student表中的全部记录。

#40.以班号和年龄从大到小的顺序查询Student表中的全部记录。
select *, (year(now()) - year(sbirthday)) as age
from students
order by class, age desc;

41.查询“男”教师及其所上的课程。

#41.查询“男”教师及其所上的课程。
select tname, depart
from teachers
where tsex='男';

42.查询最高分同学的Sno、Cno和Degree列。

方法一

#42.查询最高分同学的Sno、Cno和Degree列。
select sno, cno, degree
from scores
where degree = (select max(degree)
from  scores);

方法二

SELECT *
FROM Scores
GROUP BY Cno
HAVING Degree=Max(Degree);

43.查询和“李军”同性别的所有同学的Sname.

方法一

select stu1.sname
from students as stu1, (select * from students as stu
where stu.sname = '李军') as stu2
where stu1.ssex = stu2.ssex and stu1.sname != '李军';

方法二

SELECT s1.Sname
FROM Students AS s1 INNER JOIN Students AS s2
ON(s1.Ssex=s2.Ssex)
WHERE s2.Sname='李军';

45.查询所有选修“计算机导论”课程的“男”同学的成绩表

方法一

我写的套娃，一直套娃

• 从课程表中找到‘计算机导论’的课程号
• 用这个课程号找到成绩表中选‘计算机导论’的学生记录（只要sno和degree）作为sc临时表
• 用sc临时表与students表外联，并限制性别

select students.sno, sname, degree
from students inner join
(select sno, degree
	from scores 
	where cno = (select cno
				from courses
				where cname = '计算机导论')) as sc
on ( students.sno = sc.sno)
where ssex = '男';

方法二

SELECT *
FROM Scores
WHERE Sno IN (
    SELECT Sno
    FROM Students
    WHERE Ssex='男') AND
    Cno IN (
    SELECT Cno
    FROM Courses
    WHERE Cname='计算机导论');

19.查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

数据库练习（二）里面有

extral 1 最高分低于90分，最低分大于70分的学号

select sno
from scores
group by sno
having max(degree) < 90 and min(degree) > 70;

32. 查询所有女学生和所有女老师的姓名、性别、和生日

UNION

select distinct Sname as name,Ssex as sex,Sbirthday as birthday from students where Ssex='女'
union
select distinct Tname as name,Tsex as sex,Tbirthday as birthday from Teachers where Tsex='女'

我刚一开始不知道union函数，写出来是这个

select sname as e_name, tname as e_name, sbirthday as e_day, tbirthday as e_day
from students, teachers
where ssex = '女' and tsex = '女';

28.查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

SELECT Tname,Prof
FROM Teachers
WHERE Depart='计算机系' AND Prof NOT IN(
    SELECT DISTINCT Prof
    FROM Teachers
    WHERE Depart='电子工程系');