Database query exercise arrangement (II.)

--26, the inquiry existence has 85 points above the course CNO. Need to remove weight with distinct

Select distinct CNO from score where degree>85

--27, the results of the "computer department" teacher's teaching schedule.

The three tables join up and the filter condition depart for the computer system
Select Score.cno,score.sno,score.degree from Score
Join Course on SCORE.CNO=COURSE.CNO
Join teacher on Course.tno=teacher.tno
Where depart like ' computer Department '

--28, inquires into the tname and prof of teachers with different titles of "computer department" and "Electronic Engineering department". Related sub-query

1. The titles of all teachers in the computer department are different from those of the electronic engineering faculty.

2. The professional titles of all the teachers in the department of Electronic engineering are different from those of the teachers in the computer department.

3. Join together
Select *from Teacher T1 where depart= ' computer system ' and not exists
(
Select *from teacher T2 where depart= ' electronic Engineering Department ' and T1.prof=t2.prof
)
Union
Select *from Teacher T1 where depart= ' electronic Engineering Department ' and not exists
(
Select *from teacher T2 where depart= ' computer system ' and t1.prof=t2.prof
)

--29, inquires the elective number "3-105" course and the result is at least higher than the elective number "3-245" classmate's CNO, Sno and degree, and press degree from high to low
--query 3-105 score greater than 3-245 highest score all records of achievement
Select *from Score
where cno= ' 3-105 ' and degree >
(select Max (degree) from score where cno= ' 3-245 ') Order BY degree DESC

CNO, Sno, and degree.//related subquery for--30, query elective number "3-105" and score higher than elective number "3-245" class

Select the sno of the students who are taking both courses

Select the grade of the student who took the 3-325 course

3-105 students who have a higher score than the elective 3-325

Select *from score S1 where Sno in
(
Select Sno from score where CNO on (' 3-105 ', ' 3-245 ') group by SNO have COUNT (*) >1
)
and cno= ' 3-105 ' and degree>
(
Select degree from score S2 where Sno in
(
Select Sno from score where CNO on (' 3-105 ', ' 3-245 ') group by SNO have COUNT (*) >1
) and cno= ' 3-245 ' and S2.sno=s1.sno
)

--31, check the name, sex and birthday of all teachers and classmates. With Union Union
Select Sname,ssex,sbirthday from Student
Union
Select Tname,tsex,tbirthday from teacher

--32 the name, sex and birthday of all "female" teachers and "female" classmates. Combined conditions
Select Sname,ssex,sbirthday from Student
where ssex = ' female '
Union
Select Tname,tsex,tbirthday from teacher
where tsex= ' woman '

--33, the results of students who have a lower average score than the course. Related sub-query
Select *from score S1 where degree<
(
Select AVG (degree) from score S2 where S1.cno=s2.cno GROUP by CNO
)

--34, query all the teachers tname and depart.
Select Tname,depart from teacher

--35 inquires the Tname and depart of all teachers who have not lectured. Encountering null is not used with IS =

The first method uses Join,leftjoin to filter columns that are empty sno
Select Tname,depart from teacher
Left JOIN course on Teacher.tno=course.tno
Left JOIN score on COURSE.CNO=SCORE.CNO
where Score.sno is null
--The second method uses unrelated subqueries:
Select Tname,depart from teacher where TNO in
(
Select TNO from course where CNO not in
(
Select distinct CNO from score
)
)

--36, check the class number of at least 2 boys.
Select class from student where ssex= ' Man ' GROUP by class has COUNT (*) >1

--37, query student table in the name of "Wang" classmate Records.
Select *from Student where sname not like ' King% '

--38, query the name and age of each student in the student table.
Select Sname,year (GETDATE ())-year (sbirthday) from student--get date () is the time to take the present

--39, queries the student table for the maximum and minimum sbirthday date values.

Select MAX (sbirthday), Min (sbirthday) from student

--40, check all records in the student table in order of class and age from large to small.
Select *from Student ORDER by class Desc,sbirthday ASC

--41, query "male" teachers and their courses. Writing course.* After Select is all the columns of the output course
Select Tname,tsex,cname from Course
Join teacher on Course.tno =teacher.tno
Where tsex= ' man '

--42, Sno, CNO, and degree columns for the highest scores of students.
Select Sno,cno,degree from Score
where degree= (select MAX (degree) from score
)

--43, inquiries and "Li June" with the sex of all the students of the sname.
Select Sname from Student
where ssex= (select Ssex from student where Sname= ' Li June '
)

--44, inquiries and "Li June" with the same sex and classmates sname.
Select Sname from Student
where ssex= (select Ssex from student where Sname= ' Li June '
) and class= (select class from student where Sname= ' Li June '
)

--45, query all of the "male" students who took the "Introduction to Computer" course.
Select Student.sno,sname,ssex,cname,degree from Student
Join score on Student.sno=score.sno
Join Course on score.cno= course.cno
where student.ssex = ' man ' and Course.cname= ' Introduction to Computers '

--46, query the information of the students with the highest scores in the score table. Multilayer nesting
Select Student.*from Student
Join score on Student.sno=score.sno
where degree= (select MAX (degree) from score
)

--47, query the average student information in the score table for more than 80 points.

According to Sno Group, find out the sno of students with average value greater than 80 points

Use Sno to filter score

Select *from Student where Sno in (
Select Sno from score Group by Sno have AVG (degree) >80
)

Unrelated subqueries: subqueries can be executed independently, and then taken to the outer layer to use

Correlated subquery: The outer record gets the inner layer for the condition comparison, the eligible query returns, can not be executed independently, the inner layer and the outer layers are interrelated

Database query exercise arrangement (II.)