SQL Query Statement x45

Record the problem, although later encountered different, but the use is the same

There are some ways to use it.

COUNT (*) counts

GROUP BY group

Join connection is followed by an on join condition

Between and what what what between

In No in subquery

Like fuzzy query% means other content

--------------------------------------------------------------------------------------------------------------- -------

--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.
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.
SELECT * FROM score where degree>=60 and degree<=80

--5, query the records in the score table for grades 85, 86, or 88.
SELECT * FROM score where degree=85 or degree=86 or degree=88

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

--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 ASC, Degree desc

--9, check the number of students in "95031" class.
Select COUNT (1) from student where class = 95031

--10, check the student number and course number of the highest score in the score table. (sub-query or sort)
Select Sno,cno from score where degree on (select MAX (degree) from score)

--11, check the average score for each course.
Select Cno,avg (degree) from score Group by Cno

--12, query the average score of a course that has at least 5 students enrolled in the score table and starts with 3.
Select Cno,avg (degree) from score Group by CNO have Count (CNO) >=5 and cno like ' 3% '

--13, the query score is greater than 70, and the SNO column is less than 90.
Select Sno from score where degree>70 and degree<90

--14, Sname, CNO, and degree columns for all students are queried.
Select A.sname,b.cno,b.degree from Student a joins score B on A.sno=b.sno

--15, queries all students for SNO, CNAME, and degree columns.
Select A.sno,b.cname,a.degree from score a joins Course B on A.cno=b.cno

--16, queries all students for sname, CNAME, and degree columns.
Select A.sname,c.cname,b.degree from Student a joins score B on A.sno=b.sno
Join Course C on B.cno=c.cno

--17, query "95033" class student's average score.
Select AVG (degree) from Student a joins score B on A.sno=b.sno where Class = 95033

--18, assume that a grade table is created with the following command:
CREATE table grade (Low Int,upp Int,rank char (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 ')
SELECT * FROM Grade
--Sno, CNO and rank columns are now available for all students.
Select A.sno,b.cno,c.[rank] From Student a joins score B on A.sno=b.sno
Join grade C on b.degree between C.low and C.upp

--19, query Elective "3-105" course performance is higher than the "109" student scores of all the students record.
SELECT * 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.
SELECT * from score where degree not in (select MAX (degree) from score Group by Sno)

--21, the query score is higher than the school number is "109", the course number is "3-105" all records of the results.
SELECT * FROM score where degree>
(select degree from score where sno=109 and cno= ' 3-105 ')

--22, inquiry, and student number 108 are the SNO, sname and Sbirthday of all students born in the same year.
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.
Select degree from score a joins Course B on A.cno=b.cno
Join Teacher C on B.tno=c.tno where c.tno=856

--24, the name of the teacher who has more than 5 students who have enrolled in a course.
Select distinct C.tname from score a joins Course B on A.cno=b.cno joins Teacher C on
B.tno=c.tno where A.cno in (select CNO from Score Group by CNO have COUNT (CNO) >5)

--25, check the records of 95033 classes and 95031 classes of all students.
SELECT * FROM Student a joins score B on A.sno=b.sno joins Course C on B.cno=c.cno
Join Teacher D on C.tno=d.tno

--26, the inquiry existence has 85 points above the course CNO.
Select distinct CNO from score where degree>85

--27, the results of the "computer department" teacher's teaching schedule.
Select A.degree from score a join Course B on A.cno=b.cno join
Teacher c on B.tno=c.tno where depart= ' computer system '

--28, inquires into the tname and prof of teachers with different titles of "computer department" and "Electronic Engineering department".
Select Tname,prof from Teacher where Prof not in
(select Prof from Teacher where depart= ' computer system ') or Prof not in
(select Prof from Teacher where depart= ' electronic Engineering Department ')

--29, inquires the elective number is "3-105" the course and the result is at least higher than the elective number "3-245" Schoolmate's CNO, Sno and degree, and presses degree from high to low order.
Select Cno,sno,degree from score where cno= ' 3-105 ' and
Degree > (select MAX (degree) from score where cno= ' 3-245 ') Order BY degree DESC

--30, inquires the elective number is "3-105" and the result is higher than the elective number "3-245" the classmate's CNO, Sno and degree.
Select Cno,sno,degree from score a where cno= ' 3-105 ' and degree >
(select degree from score b where cno= ' 3-245 ' and A.sno=b.sno)

--31, check the name, sex and birthday of all teachers and classmates.
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.
Select Sname,ssex,sbirthday from student where ssex= ' woman '
Union select Tname,tsex,tbirthday from teacher where tsex= ' woman '

--33,   query scores of students who have a lower average score than the course.
Select * from score a where degree <
(select AVG (degree) from score B group by Cno have A.cno=b.cno)

--34, querying Tname and depart of all teachers in the classroom.
SELECT distinct Tname,depart from score a joins Course B on A.cno=b.cno joins
Teacher C on B.tno=c.tno where a.cno i N (b.cno)

--35 , querying the tname and depart of all teachers who have not lectured.
Select Tname,depart from Teacher where Tno isn't in
(select C.tno from score a joins Course B on A.cno=b.cno join
Tea Cher c on B.tno=c.tno where A.cno in (B.CNO))

--36, query the class number of at least 2 boys.
Select class from student where ssex= ' Man ' GROUP by class has count (Class) >=2

--37, query student table with no surname "Wang" of the classmate record.
Select * from Student where Sname isn't like ' king% '

--38, Query Student table for each student's name and age.
Select Sname,2017-year (sbirthday) from student

--39, the maximum and minimum student date values in the Query Sbirthday table.
Select MAX (sbirthday), MIN (sbirthday) from student

--40, queries all records in the student table in the Order of class and age from largest to youngest.
SELECT * from Student ORDER by Class desc,sbirthday ASC

--41, query "male" teachers and their courses.
Select A.cname,b.tname from Course a joins teacher B on A.tno=b.tno where tsex= ' man '

--42, Sno, CNO, and degree columns for the highest scores of students.
Select A.sno,b.cno,b.degree from Student a join score B to a.sno=b.sno where degree in
(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 in (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 in (select Ssex from Student where Sname= ' Li June ')
and Class in (select Class from Student where Sname= ' Li June ')

--45, query all of the "male" students who took the "Introduction to Computer" course.
SELECT * FROM Student a joins score B on A.sno=b.sno joins Course C on B.cno=c.cno
where C.cname = ' Introduction to computer ' and a.ssex= ' man '

SQL Query Statement x45