SQL Database Practice

Create DATABASE Mydb3gouse mydb3 CREATE TABLE student (--Study number SNO varchar (3) NOT NULL primary key,--name Sname VA Rchar (4) Not NULL,--Gender ssex varchar (2) NOT NULL,--Date of birth sbirthday datetime,--Class varchar (5)) CRE    Ate table teacher (--Staff number TNO varchar (3) NOT NULL primary key,--staff name Tname varchar (4) NOT NULL,--Faculty sex Tsex varchar (2) NOT NULL,--faculty date of birth tbirthday datetime,--title Prof varchar (6),--Department depart varchar () c    reate Table Course (--Course number CNO varchar (5) NOT NULL primary key,--course name CNAME varchar (TEN) NOT NULL,--faculty number    TNO varchar (3) References teacher (TNO)) CREATE TABLE score (--study number SNO varchar (3) NOT NULL references student (SNO), --Course Number CNO varchar (5) NOT NULL references course (CNO),--Score degree decimal (4,1)) insert into studentvalues (' 108 ', ' Zenghua ', ' Male ', ' 1977-09-01 ', ' 95033 ') insert INTO studentvalues (' 105 ', ' Kuanming ', ' Male ', ' 1975-10-02 ', ' 95031 ') insert INTO Studentvalues (' 107 ', ' Wang Li ', ' female ', ' 1976-01-23 ', '95033 ') insert into studentvalues (' 101 ', ' Li June ', ' male ', ' 1976-02-20 ', ' 95033 ') insert into studentvalues (' 109 ', ' Wang Fang ', ' Female ', ' 1975-02-10 ', ' 95031 ') insert into studentvalues (' 103 ', ' contacts ', ' Male ', ' 1974-06-03 ', ' 95031 ') insert into teachervalues (' 804 ', ' Sung ', ' Male ', ' 1958-12-02 ', ' associate Professor ', ' computer Department ' insert into Teachervalues (' 856 ', ' Zhang Xu ', ' Male ', ' 1969-03-12 ', ' lecturer ', ' Electronic Engineering ') insert INTO Teachervalues (' 825 ', ' Wang Ping ', ' female ', ' 1972-05-05 ', ' ta ', ' computer Department ') insert into teachervalues (' 831 ', ' Liu Bing ', ' female ', ' 1958-08-14 ', ' ta ') , ' Electronic Engineering ') insert INTO coursevalues (' 3-105 ', ' Introduction to Computer ', ' 825 ') insert into coursevalues (' 3-245 ', ' OS ', ' 804 ') insert into Coursevalues (' 6-166 ', ' digital circuit ', ' 856 ') insert into coursevalues (' 9-888 ', ' Advanced math ', ' 831 ') insert into scorevalues (' 103 ', ' Insert into Scorevalues (' 109 ', ' 3-245 ', ' 3-245 ') inserts into scorevalues (' + ', ' 3-245 ', ' [') ') insert INTO Scorevalues (' 103 ', ' 3-105 ', ' a ') insert into scorevalues (' + ', ' 3-105 ', ' "') ' INSERT INTO scorevalues (' 109 ', ' 3-105 ', ' Insert INTO Scorevalues (' 101 ', ' 3-105 ', ' + ') insert into scorevalues (' 107 ', ' 3-105 ', ' " Scorevalues (' 108 ', ' 3-105 ', ' + ') insert into scorevalues (' 101 ', ' 6-166 ', ' ") insert into scorevalues (' 107 ', ' 6-166 ', '  + ') insert into scorevalues (' 108 ', ' 6-166 ', ' Bayi ') SELECT * from Studentselect * from Teacherselect * from Courseselect * FROM Score--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 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 from 60 to 80. SELECT * from score where degree between and 80--5, query score table with scores of 85, 86 or 88 records. SELECT * from score where degree in (85,86,88)--6, Query student table ' 95031 ' class or sex for ' female ' student records. SELECT * FROM student where class= ' 95031 ' or ssex= ' female '--7, querying all records of student table in descending class. SELECT * FROM student order by class Desc--8, CNO Ascending, Degree descending all records of the score table. SELECT * FROM Score ORDER by Cno,degree Desc--9, check the number of students in ' 95031 ' class. Select COUNT (*) ' number ' from student where class= ' 95031 '--10, query the highest score in the score table for student number and course number. Select Sno,cno from score where degree= (select MAX (degree) from score)--11, query ' 3-105 ' for the average score of the 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 where cno= (select CNO from score where CNO like ' 3% ' GROUP by CNO Having COUNT (*) >5)--13, the query has a minimum score greater than 70, and the highest score is less than the Sno column of 90. Select Sno from score Group by Sno have MIN (degree) >70 and MAX (degree) <90--14, querying the sname, CNO, and degree columns of all students. Select Student.sname,score.cno,score. Degree from score joins student on STUDENT.SNO=SCORE.SNO--15, queries all students for SNO, CNAME, and degree columns. Select Score.sno,course.cname,score. Degree from score joins course on COURSE.CNO=SCORE.CNO--16, queries all students for sname, CNAME, and degree columns. Select Student.sname,course.cname,score. Degree from score joins student on Student.sno=score.sno joins course on COURSE.CNO=SCORE.CNO--17, queries ' 95033 ' for the average score of selected courses. Select AVG (degree) from score where Sno in (select Sno from student where class= ' 95033 ') g Roup by CNO--18, assuming the following command is used to establish aGrade table: 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 to grade values (60,69, ' D ') insert into grade values (0,59, ' E ')-- The SNO, CNO and rank columns of all students are now queried. Select distinct Sno,cno,rank from score joins grade on Score.degree between Low and upp--19, query elective ' 3-105 ' course scores higher than ' 109 ' students The records of all the classmates.        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 a record of the scores of the students who have chosen to learn more than the highest score. SELECT * FROM score where CNO not in (select CNO from score where degree= (select MAX (Degr     EE) from score)) and CNO in (select CNO from Score Group by CNO have COUNT (CNO) >1 --21, the results of the query is higher than the number of ' 109 ', the course number is ' 3-105 ' of all records. SELECT * FROM score where cno= ' 3-105 ' and degree> (select DegrEE from score where cno= ' 3-105 ' and sno= ' 109 ')--22, query and student number 108 for all students born in the same year Sno, 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 ' teacher's student performance. Select score.* from score join course on SCORE.CNO=COURSE.CNO join teacher on Course.tno=teacher.tno where tname= ' Zhang Xu ' select score.* from Score,course,teacher where Score.cno=course.cno and Course.tno=teacher.tno and Tname= ' Zhang Xu ' --24, the name of the teacher who has more than 5 students who have enrolled in a course.  Select distinct tname from teacher joins course on Course.tno=teacher.tno joins score on SCORE.CNO=COURSE.CNO where score.cno= (select CNO from Score Group by CNO have COUNT (CNO) >5)--25, check A record of 95033 classes and 95031 students are consulted. SELECT * from student where class in (95033,95031)--26, query there are 85 + scores of courses cno.select distinct CNO from score where degre E>85--27, check out the "computer department" teacher taught the results table. Select distinct score.* from score join coursE on Course.cno=score.cno join teacher on Teacher.tno=course.tno where teacher.depart= ' computer Department '--28, query ' computer Department ' and ' Electronic Engineering department ' different jobs The teacher's tname and Prof.  SELECT * from teacher t1 where depart= ' computer system ' and NOT EXISTS (SELECT * from teacher t2 where depart = ' Electronic Engineering Department ' and T1.prof =  T2.PROF) Unionselect * from teacher T1 where depart= ' electronic engineering ' and NOT EXISTS (SELECT * from teacher t2 where depart = ' computer system ') and t1.prof = t2.prof)--29, query elective numbered ' 3-105 ' course with a score higher than the CNO, Sno, and degree of the students with elective number ' 3-245 ', and sorted by degree from highest to lowest order. SELECT * FROM score where cno = ' 3-105 ' and degree > (select MAX (degree) from score where cno = ' 3-245 ') Order by degree DESC--30, the query elective number is ' 3-105 ' and the score is higher than the electives numbered ' 3-245 ' class CNO, Sno and Degreeselect * from score S1 where Sno in (select Sno from score whe Re cno in (' 3-105 ', ' 3-245 ') group by SNO have COUNT (*) > 1) and CNO = ' 3-105 ' and degree > (select degree from Scor E S2 where Sno in (select Sno from score where CNO in (' 3-105 ', ' 3-245 ') group by SNO have COUNT (*) > 1) and CNO = ' 3-2 S1.sno = S2.sno)--31, queryThere are teachers and classmates name, sex and Birthday.select sname,ssex,sbirthday from Studentunionselect tname,tsex,tbirthday from Teacher--32, Query all ' female ' teachers and ' female ' classmates ' name, Sex and Birthday.select sname,ssex,sbirthday from student where ssex= ' female ' unionselect tname,tsex, Tbirthday from teacher where tsex= ' female '--33, the score table for students who have a lower average score than the course.  --average score for each course--score below average select * from score S1 where degree < (select Cno,avg (degree) from score s2 where s1.cno = S2.cno Group by CNO)--34, query all instructor's tname and Depart.select distinct tname,depart from teacher--35 to inquire about the tname and depart of all teachers who have not lectured. Select Tname, depart from teacher left joins course on teacher. TNO =course. TNO left JOIN score on course. CNO = SCO Re.cno where score. Sno is a null select Tname,depart from teacher where TNO in (SELECT * from course where CNO No T in (select distinct CNO from score))--36, query the class number of at least 2 boys. Select class from student where ssex= ' Man ' GROUP by class has COUNT (*) >1--37, query student table with no surname ' King ' of the classmate record. SELECT * FROM student where sname not like ' Wang% '--38, query the name and age of each student in the student table. Select Sname,year (GETDATE ())-year (Sbirthday) from student--39, query student the maximum and minimum sbirthday date values in the table. Select MAX (Sbirthday), MIN (Sbirthday) from student--40, check all records in the student table in the Order of class and age from large to small. SELECT * FROM student order by class Desc,sbirthday asc--41, query ' male ' teachers and their courses. Select course.* from teacher joins course on Teacher.tno=course.tno where tsex= ' man '--42, queries the highest scores of classmates Sno, CNO and degree columns.  SELECT * FROM score where degree= (select MAX (degree) from score)--43, query and ' Li June ' all classmates with sex sname.select sname From student where ssex= (select Ssex from student where Sname= ' Li June ')--44, Query and ' Li June ' with sex and classmates Sname.selec T sname from student where ssex= (select Ssex from student where Sname= ' Li June ') and class= (SE Lect class from student where Sname= ' Li June ')--45 and inquires all the ' male ' students ' grades in the ' Introduction to Computer ' course. Select student.* from score join course on COURSE.CNO=SCORE.CNO join student on Student.sno=score.sno where cours E.cname= ' Introduction to computers ' andSsex= ' man '--46, query the score table for the students with the highest scores. SELECT * FROM student where sno= (select Sno from score where degree= (select MAX (degree) From score)--47, query the score table for students with an average score of more than 80 points.  SELECT * FROM Score SELECT * FROM student select * from student where Sno in (select Sno from Score Group by Sno have AVG (degree) >80)

　　

SQL Database Practice