Student scores table SQL exercises

CREATE TABLE Student (Sno char (3) Primary Key,sname char (8) Not null,ssex char (2) not Null,sbirthday Datetime,class char (5) CREATE TABLE Course (Cno varchar primary key,cname varchar) not Null,tno char (3) References Teacher (Tno));d ROP tab Le course;create table score (Sno varchar) references Student (Sno), Cno char (5) References Course (Cno), Degree decimal (4 , 1));d ROP Table Score;create table Teacher (Tno char (3) Primary Key,tname char (4) Not null,tsex char (2) Not null,tbirthday Datetime,prof Char (6), Depart varchar (TEN) NOT null);D ROP table Teather;truncate table Student;insert into Student values (1 08, ' Zeng Hua ', ' Male ', ' 1977-09-01 ', 95033); insert into Student values (105, ' Kuanming ', ' Male ', ' 1975/10/02 ', 95031); INSERT INTO Student VALUES (107, ' Wang Li ', ' female ', ' 1976/01/23 ', 95033); insert into Student values (101, ' Li June ', ' male ', ' 1976/02/20 ', 95033); INSERT INTO Student VALUES (109, ' Wang Fang ', ' female ', ' 1975/02/10 ', 95031); insert into Student values (103, ' contacts ', ' Male ', ' 1974/06/03 ', 95031); TRUNCATE TABLE Course;insert into Course values (' 3-105 ', ' computer-directed825) insert into Course values (' 3-245 ', ' operating system ', 804), insert into Course values (' 6-166 ', ' digital circuit ', 856); INSERT INTO Course VALUES (' 9-888 ', ' Advanced Algebra ', 831), insert into score values (103, ' 3-245 ', "n"), insert into score values (105, ' 3-245 ', 75); INSERT into score values (109, ' 3-245 ', "n"), insert into score values (103, ' 3-105 '), insert into score values (105, ' 3-105 ', INSERT into score values (109, ' 3-105 ', +), insert into score values (101, ' 3-105 ', +); INSERT into score values (107, ' INSERT into score values (108, ' 3-105 ', "3-105"), insert into score values (101, ' 6-166 ', +), insert into score values ( 107, ' 6-166 ', +); INSERT into score values (108, ' 6-166 ', bayi); Truncate table Score;insert into Teacher values (804, ' sung ', ' Male ', ' 1958-12-02 ', ' associate Professor ', ' computer Department '; INSERT into Teacher values (856, ' Zhang Xu ', ' Male ', ' 1969-03-12 ', ' lecturer ', ' Electronic Engineering Department '); INSERT INTO Teacher VALUES (825, ' Wang Ping ', ' female ', ' 1972-05-05 ', ' ta ', ' computer Department '), insert into Teacher values (831, ' Liu Bing ', ' female ', ' 1977-08-14 ', ' teaching assistant ', ' Electronic Engineering Department '); --1, queries the sname, Ssex, and class columns of all records in the student table. Select Sname, Ssex,class from StuDent,--2, query teacher all units that are not duplicated depart column. Select distinct depart from Teacher;--3, all records of the query student table. Select *from Student;--4, queries all records in the score table that have scores from 60 to 80. SELECT * from score where degree between and 80;--5, Query score table score 85, 86 or 88 record select * FROM score where degree=85 or Degre e=86 or degree=88;--6, query student table "95031" class or sex for "female" students record. SELECT * from Student where class=95031 or Ssex = ' female ';--7, queries all records of Student table in descending order of Class. Select *from Student ORDER by Class Desc;--8, CNO in ascending order, degree descending query all records of the score table. Select *from Score ORDER by Cno Asc,degree desc;--9, check the number of students in "95031" classes. Select COUNT (*) count from Student where class=95031;--10, query the highest score in the score table for student number and course number. (sub-query or sort) Select Sno,cno from score where degree= (select MAX (degree) from score)- -11. Check the average of the ' 3-105 ' course. Select AVG (degree) from score where cno= ' 3-105 '--12, the query score table has at least 5 students taking the average fraction of the course that starts with 3. Select AVG (degree) from score Group by Cno has count (Cno) >=5 and Cno like ' 3% '--13, query lowest score greater than 70, maximum score less than 90 sno column. Select Sno from score where degree>70 and degree<90;--14, query all students ' sname, CNO, and degree columns. Select Sname,cno,degree from score s1,student S2 where S1. Sno=s2. Sno;--15, Sno, CNAME, and degree columns for all students select Sno, CNAME, degree from score, Course where score.cno=course.cno;--16, Query the sname, CNAME, and degree columns for all students. Select Sname,cname,degree from score s1,course c,student S2 where S1. Sno=s2. Sno and S1. CNO=C.CNO;--17, check the average score of the course selected in "95033" class. Select AVG (degree) from score, Student where student.class= ' 95033 ' and Student.sno =score.sno;select AVG (degree) from Score where Sno in (select Sno from Student where class= ' 95033 ')--18, query Elective "3-105" course scores are higher than the "109" of the student's record of all students. SELECT * FROM score where cno= ' 3-105 ' and degree> (select degree from score where sno=109 and cno= ' 3-105 ')--19, query SCO The number of students in the re-selected courses is a record of non-highest scores. SELECT * FROM score S1 where degree< (select Max (degree) from score S2 where S1. Sno=s2. SNO) SELECT * from score S1 where degree not in (select Max (degree) from score S2 where S1. Cno=s2. Cno) and Sno in (select Sno-Score GROUP by Sno have COUNT (*) >1)--20, 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, query and student number 108 for all students born in the same year SN O, sname and Sbirthday columns. 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 degree from score where score.cno= (select Cno from Course where course.tno= (select Tno from Teacher where Tname= ' Zhang Xu ')--24 the name of a teacher who has more than 5 students in one elective course. Select Tname from Teacher t,course C where T.tno =c.tno and C.cno in (select Cno from Score Group by Cno have COUNT ( *) >5) Select Tname from Teacher inner join Course on Teacher.tno=course.tno and Course.cno = (select Cno from score gr OUP by Cno has COUNT (Cno) >5)--25, inquires the records of 95033 classes and 95031 classes of all students. Select *from Student where class in (95033,95031)--26, query there are 85 + scores of courses cno.select distinct Cno from score where degree > 85--27, the results of the "computer department" teacher's teaching schedule. Select degree from score s,course C where C.tno in (select Tno from Teacher where depart= ' computer system ') and C.cno=s.cno;select degree from score where Cno in (select Cno from Course where Tno in (s Elect Tno from Teacher where depart = ' Computer Department ')--28, query "computer department" and "Electronic Engineering department" different titles of teachers Tname and Prof. Select Tname,prof from Teacher where Prof in (select Prof from Teacher where depart in (' Computer system ', ' Electronic Engineering ') group by Prof) sel ECT tname,prof from Teacher where Prof not in (select Prof from Teacher where depart = ' Electronic Engineering ' and Prof in (select Prof f Rom Teacher where depart = ' Computer Department ') and depart in (' Computer department ', ' Electronic Engineering Department '),--29, query elective number "3-105" course and the result is at least higher than the number of elective "3-245" Students CNO, Sno and degree, and sort by degree from highest to lowest order.  Select Cno,sno,degree from score where cno= ' 3-105 ' and degree> (select min (degree) from score where cno= ' 3-245 ') Order BY Degree Descselect Cno,sno,degree from score where cno= ' 3-105 ' and degree> any (select degree from score where cno= ' 3-24 5 ') Order by degree desc--30, query elective number "3-105" and score higher than the elective number "3-245" course of the students Cno, Sno and Degree.select Cno,sno,degree from score where cno= ' 3-105 ' and degree> (select Max (DegrEE) from score where cno= ' 3-245 ') select Cno,sno,degree from score where cno= ' 3-105 ' and degree> all (select degree from Score where cno= ' 3-245 ')--31, query the name, sex, and birthday of all teachers and classmates. Select Sname name,ssex sex,sbirthday birthday from Studentunion allselect tname, Tsex, tbirthday from teacher--32, query all "female" teaching Division and "female" classmate name, Sex and birthday.select Sname name,ssex sex,sbirthday birthday from Student where ssex= ' women ' Union allselect tname , Tsex, tbirthday from Teacher where tsex= ' female '--33, the results of a student who has a lower average score than the course.  SELECT * FROM score where degree < No (select AVG (degree) from score Group by Cno) select * from score S1 where degree < (select AVG (degree) from score S2 where s1.cno=s2.cno)--34, query all instructor's tname and Depart.select Tname,depart from Teacher T,course c where T.tno=c.tno and C.cno in (select Cno from Score)--35, querying the tname and depart of all teachers who have not lectured. Select Tname,depart from Teacher t,course C where T.tno=c.tno and C.cno not in (select Cno from Score)--36, query the class number of at least 2 boys. Select Class from Student where (select COUNT (*) from Student where ssex= ' man ') >=2 GROUP by class--37, query Student table not named "King" of the classmate record. SELECT * from Student where Sname don't like ' Wang% '--38, query Student table for each student's name and age. Select Sname, (Year (GETDATE ())-year (Sbirthday)) The age from Student--39, the maximum and minimum Student date values in the Query Sbirthday table. Select Max (sbirthday), Min (sbirthday) from Student--40, querying all records in the Student table in order of class and age from large to small. Select *,datediff (year, Sbirthday,getdate ()) age from Student order by Class desc,age Descselect * from Student ORDER by C Lass desc, sbirthday asc--41, query "male" teachers and their courses. Select Tname,cname from Teacher inner joins Course on teacher.tno = Course.tno where tsex= ' man '--42, queries the highest scores of students Sno, CNO and degree columns. SELECT * from score where degree in (select Max (degree) from score)--43, query, and "Li June" all classmates with sex sname.select Sname from Student whe Re Ssex in (select Ssex from Student where Sname = ' Li June ') and Sname! = ' Li June '--44, query and "Li June" with sex and classmates sname.select Sname from Studen t where Ssex in (select Ssex from Student where Sname = ' Li June ') and Class in (select Class from Student where Sname = ' Li June ') and Sname! = 'Li June '--45 and inquires all the "male" students ' grades in the course of "Introduction to Computer". SELECT * FROM score where Sno in (select Sno from Student where ssex= ' man ') and Cno = (select Cno from Course where Cname= ' Introduction to Computers ')

Student scores table SQL exercises