Query all records from 60 to 80 in the score table select * FROM score WHERE grate between and 80
Query the student record for all student in the table select * FROM student WHERE sname like ' King% '
Query for records in score table with scores of 85, 86, or 88 select * FROM score WHERE grate in (85,86,88)
Query the student record for "95931" or "female" in student table select Sno,sname,class from student WHERE class= ' 95931 ' or ssex= ' women '
Query all male records of the student table and sort in descending order of class select Sno,sname,class from student WHERE ssex= ' Man ' Order by class Desc
Query CNO Ascending, Degree descending display score table all records select * FROM score ORDER by cno,degree Desc
Check the number of students in ' 95931 ' class select COUNT (*) as number of from student WHERE class= ' 95931 '
Query the average of the "3-105" course in the score table select AVG (degree) as ' course average ' from score WHERE cno= ' 3-105 '
Check the average score of the course number with more than 5 students enrolled in the score table and start with 3, SELECT Cno,avg (degree) as ' average ' from score WHERE cno like ' 3% ' GROUP by CNO have COUNT ( *) >5
Query the score table for the lowest score greater than 70, the highest score is less than 90 Sno column Select Sno from the score GROUP by Sno have MIN (degree) >70 and MAX (degree) <90
Query the score table with the highest score and the lowest point difference greater than 12 of the course number Select CNO as ' Course Number ', max (degree) as ' highest score ', MIN (degree) as ' min ' from score GROUP by CNO have MA X (degree)-min (degree) >12
Query all students for sname, CNAME, degree example select S.sname,c.cname,sc.degree from student s,course C,score SC WHERE S.sno=sc.sno and Sc.con=c.cno
Sno, sname, sbirthday column Select Sno,sname,year (sbirthday) as ' year of birth ' from student WHERE student for all students born in the same year as the student number "105" in the inquiry form Year (Sbirthday) = (SELECT year (sbirthday) from student WHERE sno= ' 105 ')
The name of the student who queried the highest score select sname from student where sno= (select Sno from score where degree= (select MAX (degree) from score))
Search for a course with more than 5 students the name of the teacher select Tname from teacher WHERE TNO in (SELECT x.tno from course x,score y WHERE x.cno=y.cno GROUP B Y X.tno having COUNT (X.TNO) >5)
Check the course number above 85 points Cnoselect DISTINCT CNO from score where degree in (SELECT degree from score WHERE degree>85)
Query "computer system" and "electronic Engineering" teachers of different titles Tname and Profselect tname,prof from teacher WHERE depart= ' computer system ' and Prof not in (SELECT prof from tea Cher WHERE depart= ' Electronic Engineering Department ')
Query the elective number "3-105" course with a minimum score higher than the Cno,sno and degree of the elective number "3-245" course, and select Cno,sno,degree from score WHERE cno= ' in descending order of degree 3-105 ' and degree > any (SELECT degree from score WHERE cno= ' 3-245 ') ORDER by degree desc
Cno,sno and Degreeselect Cno,sno,degree from score WHERE cno= ' 3-105 ' and degree for students with elective numbers "3-105" and higher scores than elective "3-245" courses > All (SELECT degree from score WHERE cno= ' 3-245 ')
Student score Table Select Sno,cno,degree from score a where degree< (select AVG (degree) from score b WHERE a.cno=b.cno)
Query all instructor's tname and Departselect Tname,depart from teacher a where EXISTS (SELECT * from course b WHERE a.tno=b.tno)
Tname and Departselect of all non-instructor Tname,depart from teacher a WHERE not EXISTS (SELECT * from course b WHERE a.tno=b.tno)
All teachers and students are queried for the name, gender and year of birth select Tname as ' name ', tsex as ' gender ', year (tbirthday) as ' birthdate ' from Teacherunionselect tname as ' name ', Tsex As ' Gender ', year (tbirthday) as ' birthdate ' from student
Check the name, gender, and year of birth of all teachers and students, and sort by name Select Tname as ' name ', tsex as ' gender ', year (tbirthday) as ' birthdate ' from Teacherunionselect Tname as ' Name ', tsex as ' gender ', year (tbirthday) as ' birthdate ' from Studentorder by tname,sname
Find all students older than "Wang Hua" name, Age and sex select Sn,age,sex from S where age> (select age from S where sn= ' Wang Hua ')
Search for elective courses "C2" Students of the highest achievers of the student number SELECT s# from SC where c#= ' C2 ' and grade >=all (SELECT grade from SC where c#= ' C2 ')
Check the student's name and the course number and grade of the selected course select S.sn,sc.c#,sc.grade from S,SC WHERE s.s#=sc.s#
Check the total scores of students taking more than 4 courses (not counting the failed courses) and ask for a sequence of the total scores select S#,sum (grade) from SC WHERE grade >=60 GROUP by s# have COUNT (*) >= 4 ORDER by 2 DESC
Assuming the student relationship is S (s#,sname,sex,age), C (C#,cname,teacher), the student elective relationship is SC (s#,c#,grade). To find the name of the "female" student taking the "computer" course. Select sname from S,c,sc WHERE sc.c#=c.c# and c.cname= "Computer" and s.s#=sc.s# and s.sex= ' women '
Bookstore (bookstore number, bookstore name, address) Books (ISBN, title, pricing) Library (Hall number, library name, city, telephone) book issue (Hall number, ISBN, bookstore number, quantity)
(1) Inquire about the most expensive and cheapest titles and pricing SELECT books in the published books. Book titles, books. Pricing from book Where pricing = (SELECT MAX (pricing) from book, book Release WHERE book. ISBN = Book release, ISBN) Unionsele CT Books-titles, books. Pricing from book Where pricing = (SELECT MAX (pricing) from book, book Release WHERE book. ISBN = Book release. ISBN)
(2) query has the published "Database System Fundamentals" book Library name SELECT museum name from library where museum name in (select Library name from book Release WHERE ISBN in (select ISBN from book W Here title = ' Database system basics ')
People (Pno employee, pname name, sex gender, job occupation, wage salary, Dptno Department number) (1) inquire about all staff members whose wages are higher than the average wage in their department select * from people x WHERE wage > ( SELECT AVG (wage) from people y WHERE X.dptno=y.dptno)
(2) Find all employee information for wages greater than "géotechnique" salary select * from people where wage > (SELECT wage from people where pname= "Géotechnique")
A (a# Shop code, Aname store name, number of Wqty, city) B (b# product number, bname trade name, price) AB (a# store code, b# product number, QTY Item quantity)
(1) Check the number of staff not exceeding 100 persons or the store code and store name of all stores in Changsha select A#,aname from A WHERE wqty<=100 or city= ' Changsha '
(2) inquire about the store name (2 practices) of the supply bag 1, SELECT a.aname from A,b,ab WHERE a.a#=ab. a# and B.b#=ab. b# and B.bname= ' schoolbag ' 2, select ANAME from A where a# in (select a# from AB WHERE b# in (select b# from B WH ERE bname= ' schoolbag '))
(3) The shop name and the city select A.aname,a.city from A,ab where A.a#=ab is available for all goods supplied by the store with a minimum code of 256. a# and AB. b# in (SELECT AB. b# rfom AB WHERE a#= ' 256 ')
From for notes (Wiz)
Day05_ Daily SQL Exercise (i)
