Database query exercise arrangement (I.)

SELECT * FROM Student
SELECT * FROM teacher
SELECT * FROM Course
SELECT * FROM Score

There are several newly created tables from which to query some data

--1, queries the sname, Ssex, and class columns of all records in the student table.
Select Sname,ssex,class from Student

--2, query teachers all units that are not duplicated depart column. Use distinct to weigh
Select distinct depart from teacher

--3, queries all records of the student table.
Select *from Student

--4, queries all records in the score table for scores from 60 to 80. Using between can also be done with greater than less than
Select *from score where degree between and 80

--5, query the records in the score table for grades 85, 86, or 88. Use in to take discrete values
Select *from score where degree in (85,86,88)

--6, inquires the student table "95031" class or the sex is "female" the classmate record.
Select *from student where class= ' 95031 ' or ssex= ' women '

--7, queries all records of the student table in descending order of class.
Select *from Student ORDER BY class Desc

--8, CNO Ascending, Degree descending queries all records of the score table.
Select *from score ORDER BY cno,degree Desc

--9, check the number of students in "95031" class. COUNT (*) Statistic function statistics have several values
Select COUNT (*) from student where class= ' 95031 '

--10, check the student number and course number of the highest score in the score table.

The first method: Sort by fractions, take top 1 and take the highest
Select top 1 Sno,cno from score order BY degree DESC

--The second method: first check out the highest score, and then query the highest score of the course number
Select Sno,cno from score where degree=
(select Max (degree) from score)

--11, check the average score for the ' 3-105 ' course. Using the AVG statistic function
Select AVG (degree) from score where cno= ' 3-105 '

--12, query the average score of a course that has at least 5 students enrolled in the score table and starts with 3. First, we need to select CNO starting with 3 and then grouping to find the average.
Select AVG (degree) from score where CNO like ' 3% ' GROUP by CNO have Count (*) >=5

--13, the query minimum score is greater than 70, the highest score is less than 90 sno column.
Select Sno from score Group by Sno have MAX (degree) <90 and MIN (degree) >70

--14, Sname, CNO, and degree columns for all students are queried. Join the student table and the score table first, and then query the required columns.
Select Sname,cno,degree from Student
Join score on student.sno= Score.sno

--15, queries all students for SNO, CNAME, and degree columns.
Select Sno,cname,degree from Score
Join Course on SCORE.CNO=COURSE.CNO

--16, queries all students for sname, CNAME, and degree columns. The first step is to write three tables, the second step joins up, and the third step filters the required columns
Select Sname,cname,degree
From student
Join score on score. Sno=student.sno
Join course on score. Cno=course.cno

--17, check the average score of the course selected in "95033" class. Join the score and student two tables first, then filter by the class criteria, and finally average
Select AVG (degree) from score
Join student on student. Sno=score. Sno
where class= ' 95033 '

--You can also do it in groups:
Select Cno,avg (degree) from score where Sno in
(Select Student.sno
From score
Join student on student. Sno=score. Sno
where class= ' 95033 ') GROUP by CNO

--18, assume that a grade table is created with the following command:
SELECT * FROM Grade
CREATE table grade (Low Int,upp int,rank varchar (1))
Insert into grade values (90,100, ' A ')
Insert into grade values (80,89, ' B ')
Insert into grade values (70,79, ' C ')
Insert into grade values (60,69, ' D ')
Insert into grade values (0,59, ' E ')
--Sno, CNO and rank columns are now available for all students.
Select Sno,cno,rank from Score
Join grade on degree between low and UPP

--19, query Elective "3-105" course performance is higher than the "109" student scores of all the students record. Unrelated subqueries

Join the student table and the score table first to inquire about the results of the 109th students taking 3-105 courses and then select the student records of 3-105 students who are more successful than this.
Select *from Student where Sno in (
Select Sno from score where cno= ' 3-105 ' and degree>
(select degree from score where cno= ' 3-105 ' and sno= ' 109 '))

--20, query score the number of students who choose to learn more than the highest score of the record. Related sub-query

The outer condition is taken to the inner layer for comparison, the eligible query returns

Find out the SNO of the students taking many courses; The inner query finds the highest score for each course, the day before the condition is the inner layer of the CNO and the outer cno like

Select *from score a where Sno in
(
Select Sno from score Group by SNO have COUNT (*) >1
)
and degree not in
(
Select MAX (degree) from score b where A.cno=b.cno GROUP by CNO
)

--21, the query score is higher than the school number is "109", the course number is "3-105" all records of the results.

First, check the student number for the 109 course number 3-105 of the students ' scores, and then query score score greater than this student's record
Select *from Score where degree>
(select degree from score where cno= ' 3-105 ' and sno= ' 109 ')

--22, inquiry, and student number 108 are the SNO, sname and Sbirthday of all students born in the same year.

Use the year function to remove the years from the Sbirthday

Check the year of birth of sno=108, and check the record of the year of birth equal to this student
Select Sno,sname,sbirthday from student where year (sbirthday) =
(Select year (sbirthday) from student where sno= ' 108 ')

--23, inquires "Zhang Xu" the student achievement which the teacher teaches. Three tables join up and then satisfy the condition Tname to Zhang Xu
Select *from Score
Join Course on COURSE.CNO=SCORE.CNO
Join teacher on Course.tno=teacher.tno
Where Teacher.tname= ' Zhang Xu '

--24, the name of the teacher who has more than 5 students who have enrolled in a course. Connect teacher and course and query for more than 5 people CNO
Select Tname from teacher
Join Course on Teacher.tno=course.tno
where Course.cno in (
Select CNO from Score Group by CNO have COUNT (*) >5)

--25, check the records of 95033 classes and 95031 classes of all students.
Select *from student where class= ' 95033 ' or class= ' 95031 '

Database query exercise arrangement (I.)