My SQL Exercises

There is a database comprising four tables: Student table (Student), Course table (Course), score table (score) and teacher Information sheet (Teacher). The structure of the four tables, as shown in table 1-1 (i) to table (iv), is shown in table 1-2 table (i) ~ table (iv). Create four tables with SQL statements and complete related topics. Table 1-1 database table Structure table (i) Student (student table) attribute name data type can be null meaning Snochar (3) No study number (master code) Snamechar (8) No student name Ssexchar (2) No student sex sbirthday datetime Student's date of birth Classchar (5) Student's class table (ii) Course (curriculum) attribute name data type can be null meaning Cnochar (5) No course number (Master code) Cnamevarchar (10) No Course name Tnochar (3) No Staff number (external Code) Table (iii) score (score table) attribute name data type can be null meaning Snochar (3) No study number (outside code) Cnochar (5) No course number (outside code) degreedecimal (4,1) can score main code: sno+ CNO Table (iv) Teacher (Teacher's Table) property name data type can be null meaning Tnochar (3) No staff number (master code) Tnamechar (4) No Faculty name Tsexchar (2) No faculty sex tbirthday datetime can be Faculty Date of birth Profchar (6) can title Departvarchar (10) No Faculty Department table 1-2 data table in database (i) StudentSnoSnameSsexSbirthdayclass108 Zenghua male 1977-09-0195033105 Kuanming male 1975-10-0295031107 Wang Liju 1976-01-2395033101 Lee Army male 1976-02-2095033109 Wang Fang female 1975-02-1095031103 Contacts men 1974-06-0395031 Table (ii) coursecnocname Introduction to TNO3-105 computer 8253-245 operating system 8046-166 Digital circuit 8569-888 Advanced Mathematics 831 Table (iii) scoresnocnodegree1033-245861053-245751093-245681033-105921053-105 881093-105761013-105641073-105911083-105781016-166851076-166791086-16681 Table (iv) TeacherTnoTnameTsexTbirthdayProfDepart804 Li Chengnan 1958-12-02 Associate Professor Computer Department 856 Zhang Xunan 1969-03-12 lecturer Electronic Engineering Department 825 Wang Ping female 1972-05-05 teaching assistant Computer department 831 Liu Bing 1977-08-14 teaching assistant Electronic Engineering Department 1, query the student table for all records sname, Ssex, and class columns. Select Sname,ssex,class from Student2, query teachers all units that are not duplicated depart columns. Select distinct depart from TEACHER.3, querying all records of the student table. SELECT * FROM Student 4, querying all records in the score table for scores between 60 and 80. SELECT * from score where degree between (where degree>=60 && degree <=80) 5, Query score table with scores of 85, 86 or 88 Recorded. SELECT * from score where degree==85| | degree==86| | Degree==88 (where degree in (85,86,88)) 6, Query student table "95031" class or sex for "female" students record. SELECT * from studnt where class= "95031" or ssex= "female" 7, querying all records of the student table in descending order of Class. SELECT * FROM Student ORDER by Class Desc8, CNO Ascending, Degree descending all records of the score table. SELECT * FROM Score ORDER by Cno ASC, Degree desc9, check the number of students in "95031" class. Select COUNT (*) from student where class= "95031" 10, query the student number and course number of the highest score in the score table. (sub-query or sort) sort: Select Sno,cno from score order BY degree desc LIMIT 0, 1 subquery: Select Sno,cno from Score wherE degree= (select Max (degree) from score) 11, check the average score for each course. According to the course Grouping (group by), the average score for each course select AVG (degree) from score Group by Cno12, query the score table for at least 5 students to take the course and start with 3. Query for courses starting with 3 Select Cno from score where Cno like "3%" queries at least 5 students take the course number select Cno from Score GROUP by Cno have count (*) ; =5 Select AVG (degree), Cno from score where Cno in (Selet Cno from score where Cno like ' 3% ' GROUP by Cno have Count (*) & gt;=5) 13, the query score is greater than 70, less than 90 of the SNO column. Select Sno from score where degree>70 and degree <9014, querying the sname, CNO, and degree columns of all students. Select (select Sname from student where student. Sno=score. Sno), Cno, degree from score Select Sname, cno,degree from Score,student where score. Sno=student. Sno Select Sname, Cno, degree from score joins student on score. Sno=student. SNO15, queries all students for SNO, CNAME, and degree columns. Select Sno, (select Cname from course where score. Cno=course. Cno), degree from score Select Sno,cname,degree from Score,course where score. Cno=course. CNO16, queries all students for sname, CNAME, and degree columns. Select Sname,cname,degree from ScorE,course,student where score. Cno=course. Cno and student. Sno=score. Sno17, query "95033" class student's average score. Select AVG (degree) from score where Sno in (select Sno from student where class= ' 95033 ') 18, assume that a grade table is created using the following command: Create Ta BLE grade (low int (3), Upp int (3), rank char (1)) insert into grade values (90,100, ' A ') insert into grade values (80,89, ' B ') Insert into grade values (70,79, ' C ') inserts into grade values (60,69, ' D ') inserts into grade values (0,59, ' E ') now queries all students for SNO, CNO and Rank columns. Select Sno,cno,degree from the score join grade on Score.degree between Upp and low19, query for elective "3-105" course results and above the "109" student scores of all the students record. SELECT * FROM score where cno= ' 3-105 ' and degree> (select degree from score where sno= ' 109 ' and cno= ' 3-105 ') 20, query score Select A record of a non-maximum score for a student in a multi-course class SELECT * from score a where Sno in (select Sno from Score GROUP by Sno have Count (*) >1) and deg Ree < (select Max (degree) from score b where b.cno= A.cno) 21, the query score is higher than the number of "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 study number 108 of all students born in the same year Sno, Sname and Sbirthday column. Select Sno, Sname, sbirthday from student where year (sbirthday) = (select year (sbirthday) from student where sno= ' 108 ') 23, query "Zhang Xu" the students ' performance in the classroom. SELECT * FROM score where Cno in (select Cno from Course where tno= (select Tno from teacher where Tname= ' Zhang Xu ')) 24, query for a course of the same The name of a teacher with more than 5 students. Select *from Teacher where Tno in (select Tno from Course where Cno in (select Cno from Score GROUP by Cno have count (*) &GT;5) 25, check the records of 95033 classes and 95031 classes of all students. SELECT * from student the Where class in (' 95033 ', ' 95031 ') 26, query the existence of more than 85 points of the course CNO. Select Cno from Score Group by Cno had Max (degree) >8527, and queried the "computer department" of the Teacher's transcript of the teaching course. SELECT * FROM score where Cno in (select Cno from Course where Tno in (select Tno from teacher where depart = ' computer system ') 28, query " Tname and prof of teachers with different titles in computer department and Electronic Engineering department. SELECT * FROM teacher where Prof isn't in (select Prof from teacher where depart = ' computer system ' and Prof in (select Prof from Teach Er where depart= ' electronic Engineering Department ') SELECT * from teacher where Prof in (select PROF from teacher where depart = ' computer system ' and Prof not in (select Prof from teacher where depart= ' electronic Engineering Department ')) union SELECT * FROM Teacher where Prof in (select Prof from teacher where depart = ' Electronic Engineering ' and Prof not in (select Prof from teacher where DEP art= ' Computer Department ') 29, query elective number for the "3-105" course and the result is at least higher than the elective number "3-245" of the students CNO, Sno and degree, and by degree from high to low order. SELECT * FROM score where cno= ' 3-105 ' and degree> any (select degree from score where cno= ' 3-245 ') Order by degree DESC3 0, inquires the elective number is "3-105" and the result is higher than the elective number is "3-245" the course schoolmate's CNO, Sno and degree. SELECT * FROM score where cno= ' 3-105 ' and degree> all (select degree from score where cno= ' 3-245 ') 31, query all teachers and classmates name, SE X and birthday. Select Sname, Ssex, sbirthday from student Union select Tname, Tsex, tbirthday from Teacher32, querying the name, sex, and bir of all "female" and "female" classmates Thday. Select Sname, Ssex, sbirthday from student where ssex= ' women ' Union select Tname, Tsex, tbirthday from teacher where tsex= ' female ' 3 3. Check the scores of students who have a lower average score than the course. SELECT * FROM Score a WHERE degree< (select AVG (degree) from score b where a.cNO=B.CNO) 34, query all the teachers tname and depart. Select Tname, depart from teacher where Tno in (select Tno from Course where Cno in (select distinct Cno from score)) 35, Check the Tname and depart of all teachers who have not lectured. Select Tname, depart from teacher where Tno isn't in (select Tno from Course where Cno on (select distinct Cno from score)) 3 6. Check the class number of at least 2 boys. Select class from student where ssex= ' man ' GROUP by Class has count (Ssex) >=237, query student table with no surname "Wang" of the classmate record. SELECT * FROM student where Sname don't like ' Wang% ' 38, query the name and age of each student in the student table. Select Sname, Year (now ())-year (Sbirthday) as the age from Student39, querying the student table for the maximum and minimum sbirthday date values. Select Max (sbirthday), Min (sbirthday) from STUDENT40, check all records in the student table in the Order of class and age from large to small. Select Class,year (now ())-year (Sbirthday) as the age from student order by Class DESC, age41, query "male" teachers and their courses. Select Cname from Course where Tno in (Select Tno from teacher where tsex= ' man ') 42, query the highest scores of students Sno, CNO and degree columns. SELECT * FROM score ORDER BY degree desc limit0,1 select * FROM score where degree = (select Max (degree) from SCOre) 43, inquiries and "Li June" with the gender of all students sname. Select Sname from student where ssex= (Select Ssex from student where Sname= ' Li June ') 44, query 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 whe Re Sname= ' Li June ') 45, check all the students who have elective "Introduction to Computer" in the "male" grade table. SELECT * FROM score where Sno in (select Sno from student where ssex= ' man ') and Cno in (select Cno from course where cname= ' Introduction to Computers ')

My SQL Exercises