【数据库】简单&连接&嵌套查询

目录

🎇简单查询

🎇连接查询

🎇嵌套查询

分析&思考 

---

🎇简单查询

--练习简单查询
--select * from classes
--select * from student
--select * from scores
--1.按Schedule表的结构要求用SQL语言创建Schedule表
--字段名	字段描述	数据类型	主键	外键	非空	唯一	自增
--ID	      编号	    int	        是√	否	     是	      是√	是√
--TeacherID 教师号	   char(8)	    否	    是√	 是√	  否	否
--CourseID  课程号	    char(8)	    否	    是	     是	      否	否
--ClassID	  班级号	char(8)	    否	    是	     是	      否	否
--Semester  开课学期	int    	    否	    否	     是	      否	否
--SchoolYear	学年	int        	否	    否	     否	      否	否

--❗
--use student
--go
--create table schedule
--(
--ID int identity primary key,
--TeacherID char(8) not null references Teacher(TeacherID),
--CourseID char(8) not null references Course(CourseID),
--ClassID char(8) not null references Class(ClassID),
--Semester int not null,
--SchoolYear int
--)
--go

--//
--（2）查询teacher表中所有教师的姓名和年龄。
--select teachername 姓名,year(getdate())-year(birth) 年龄
--from teacher
--//

--（2）查询student表中所有学生的姓名和性别。
--select sname 姓名,ssex 性别 from student

--（3）查询所有系的信息。
--select * from classes

--（4）.查询成绩值大于等于60的学生的学号。
--select sno from scores
--where grade>60

--（5）查询软件06101班的女生信息。
--select * from student
--where ssex='女'
--and classno='软件06101'

--（6）查询学生姓名中第2个字为“丽”的学生信息。
--select * from student
--where sname like '_丽%'

--（7）查询student表姓名和性别。
--select sname 姓名,ssex 性别 from student

--（8）查询student表中前5行数据。
--select top 5 * from student

--（9）查询选修了体育课程的学生的学号和成绩，将查询结果按成绩降序排序，成绩相同按学号升序排序。
--select sno 学号,grade 成绩 from scores
--where course='大学英语'
--order by grade desc,sno asc--成绩降序，学号升序

----//（10）查询course表中的最大学分的课程名。(用order by子句)。
--select top 1 with ties coursename
--from course
--order by credit desc//降序
----//

--（11）查询不同系部的班级数。
--select count(classno)
--from classes
--group by dept

----（12）统计各个班的学生人数。
--select classno,count(sno)
--from student
--group by classno

--（13）查询课程Dp030001的最高分、最低分和平均分。
--select max(grade) 最高分,min(grade) 最低分,avg(grade) 平均分
--from scores
--where course='体育'

--（14）查询grade表中选修了3门以上课程的学生学号。
--select studentid
--from grade
--group by studentid
--having count(courseid)>3

----（15）查询不同班级不同性别学生人数。
--select classno,ssex,count(sno) from student
--group by classno,ssex

🎇连接查询

--（1）查询课程名及该课程的得分情况。
--select course 课程名,grade 得分 from scores 

--两张表
--（2）查询学生姓名及其所学生的课程名。
--select sname 姓名,course 课程名 from student join scores on student.sno=scores.sno

------（3）查询和‘程玲’同学同班的学生信息。
--select * from student
--where classno=(select classno from student where sname='程玲')

--一张表
--（4）查询比6320210615 同学成绩高的信息。
--select s1.grade from scores s1 
--join scores s2 on s1.grade>s2.grade
--and s2.sno='6320210615 '

--（5）
--select dept,student.classno,sno,sname,ssex from student join classes on student.classno=classes.classno

--（6）查询既学了了又选修了课程的学生的学号，姓名。
--select student.sno 学号,sname 姓名 from student join scores on student.sno=scores.sno
--where course='大学英语' and course='体育'


--（7） “大学英语（一）”成绩不及格的学生人数是多少？
--select count(student.sno) from student join scores on student.sno=scores.sno
--where course='大学英语' and grade<60

--（8）计算机系的平均成绩为多少？
--select avg(grade) from student 
--join classes on student.classno=classes.classno
--join scores on student.sno=scores.sno
--where dept='计算机系'

--（9）查询全部教师、全部课程的课程安排。

--（10）查询“大学英语”课程前三名的学生学号、姓名和成绩。
--select top 3 with ties 
--student.sno,sname,grade from student join scores on student.sno=scores.sno
--where grade='大学英语'
--order by grade desc--降序

🎇嵌套查询

----（1）嵌套查询 “计算机系”的学生信息。
--select * from student
--where classno in(select classno from classes where dept='计算机系')

--（2）嵌套查询“计算机系”的全部学生信息。
--select * from student
--where classno in(select classno from classes where dept='计算机系')

--（3）嵌套查询xx课程中成绩未达到该门课程平均分的同学信息。
--SELECT *
--FROM grade
--WHERE CourseID = 'Dp010001'
--AND grade <
--	(SELECT AVG(grade)
--	FROM grade
--	WHERE CourseID='Dp010001')
--或者
--select * from student 
--where sno in(select sno from scores where course='大学英语' and
--grade <(select avg(grade) from scores where course='大学英语'
--))

--（4）嵌套查询大学英语课程中最低分的学生信息。
--select * from student where sno in
--(select sno from scores where course='大学英语'
--and grade=(select min(grade) from scores where course='大学英语'))

--（4）嵌套查询Dp010001课程中最低分的学生信息。
--SELECT *
--FROM student
--WHERE StudentID IN
--	(SELECT StudentID
--	FROM grade
--	WHERE CourseID = 'Dp010001'
--		AND grade =
--			(SELECT MIN(grade)
--			FROM grade
--			WHERE CourseID = 'Dp010001'))

--（5）嵌套查询Cs010901班比Cs010902班年龄都大的学生信息。
--SELECT *
--FROM student
--WHERE ClassID='Cs010901'
--AND Birth < ALL
--	(SELECT Birth
--	FROM student
--	WHERE ClassID='Cs010902')

--（6）用带EXISTS子查询选修了Dp010001的学生学号和姓名。
--SELECT StudentID,StudentName
--FROM student
--WHERE EXISTS
--	(SELECT *
--	FROM grade 
--	WHERE CourseID='Dp010001'
--	AND student.StudentID=grade.StudentID)

--（7）查询选修了Dp010001课程而没有选修Dp010002号课程的学生学号。
--SELECT StudentID
--FROM 	grade 
--WHERE CourseID='Dp010001' 
--AND StudentID NOT IN
--	(SELECT StudentID
--	FROM grade
--	WHERE CourseID='Dp010002')
----（8）合并显示教师中的男性教师和有教授职称的教师。
--SELECT *
--FROM Teacher
--WHERE Sex='男'
--UNION
--SELECT *
--FROM Teacher
--WHERE Profession='教授'

--（9）查询student表中女同学的学号，姓名及根据学号前两位判断所处年级（St01为‘大一’02为‘大二’，其他的不清楚）。
--SELECT StudentID,StudentName,
--	CASE
--		WHEN StudentID LIKE 'St01%' THEN '大一'
--		WHEN StudentID LIKE 'St02%' THEN '大二'
--		ELSE '不清楚'
--	END as 年级
--FROM Student
--WHERE Sex='女'

分析&思考