SQL statement Little Practice One

recently in the study of SQL Server, the teacher laid out some small exercises. To tell the truth, these exercises can find the answer on the Internet, but what is the meaning of direct copy of the answer? It's a waste of life!

The following attached to the original question and the individual SQL statements, such as the online statement of the same, can only be said to be identical. Because I do not like to copy something directly, study must have a serious attitude. The original question and answer are as follows: Create DATABASE Aaagouse aaagocreate table student (   sno varchar (3), sname varchar (4) NOT NULL,-- Sex ssex varchar (2) NOT null,--date of birth sbirthday datetime,--class class varchar (5)) go create table teacher (TNO varchar (3) Not NULL PRIMARY key,--faculty name Tname varchar (4) NOT null,--faculty sex tsex varchar (2) NOT NULL,--faculty Birth date tbirthday datetime,--title Prof V Archar (6),--Department depart varchar ( ) Go create Table Course (--Course number CNO varchar (5) NOT null primary key,-- Course Name CNAME varchar (+) NOT NULL,--faculty number TNO varchar (3)   ) go create Table score (--study number SNO varchar (3),-- Course number CNO varchar (5),--score degree decimal (4,1)) Goinsert into student values (' 108 ', ' 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 ') 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 ') insert INTO teacher VALUES (' 825 ', ' Wang Ping ', ' female ', ' 1972-05-05 ', ' ta ', ' computer Department ') insert into teacher values (' 831 ', ' Liu Bing ', ' female ', ' 1958-08-14 ', ' ta ', ' the Department of Electronic Engineering Insert into course values (' 3-105 ', ' Introduction to Computer ', ' 825 ') insert into course values (' 3-245 ', ' OS ', ' 804 ') insert INTO course VALUES (' 6-166 ', ' digital circuit ', ' 856 ') insert into course values (' 9-888 ', ' Advanced math ', ' 831 ') insert into score values (' 103 ', ' 3-245 ', ' *) insert INTO score values (' ', ' 3-245 ', ' ' "') ' INSERT into score values (' 109 ', ' 3-245 ', '" ') ' INSERT into score values (' 103 ', ' 3-105 ', ' (') ') insert into score values (' + ', ' 3-105 ', ' (') ') insert into score values (' 109 ', ' 3-105 ', ' + ') insert INTO Score values (' 101 ', ' 3-105 ', ' + ') insert into score values (' 107 ', ' 3-105 ', ' ' "') ' INSERT into score values (' 108 ', ' 3-105 ', ' INSERT into score values (' 101 ', ' 6-166 ', ' I ') insert into score values (' 107 ', ' 6-166 ', ' + ') insert into score values (' 108 ', ' 6-166 ', ' Bayi ') SELECT * from Studentselect * from Teacherselect * FROM course SELECT * from Score --1, querying the sname, Ssex, and class columns of all records in the student table. Select Sname from Studentselect ssex to Studentselect class from Student--2, query teacher all units that are not duplicated depart column. 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 >=60.0 and degree <=80.0--5, query score table records with scores of 85, 86, or 88. SELECT * from score where degree in (85,86,88)--6, Query student table "95031" class or sex for "female" students record. 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 Sno Asc,degree Desc--9, check the number of students in "95031" class. Select COUNT (*) 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 scorewhere cno= ' 3-105 '--12, check the average score of at least 5 students in the score table and start with 3 courses. Select AVG (degree) from the score where CNO like ' 3% ' GROUP by CNO has the COUNT (*) &GT;=5--13, the query has a minimum score greater than 70, and the highest score is less than 90 of the SNO column. Select Sno from score where degree in (select degree from score Group by degree have MIN (degree) >70 and MAX (degree) & LT;90)--14, queries all students for sname, CNO, and degree columns. Select Sname,cno,degree from student inner joins score on student.sno = score.sno--15, queries all students for SNO, CNAME, and degree columns. Select Sno,cname,degree from score inner joins course on score.cno = course.cno--16, queries all students for sname, CNAME, and degree columns. Select Sname,cname,degree from student inner joins score on student.sno = Score.snoinner joins course on score.cno = course. CNO--17, check the average score of the course selected in "95033" class. Select AVG (degree) from score inner joins student on student.class = ' 95033 '--18, assuming that a grade table is established using the following command: Create TABLE grade (Low Int,upp Int,rank varchar (1)) insert into grade values (90,100, ' A ') insert to grade values (80,89, ' B ') insert INTO grade Valu ES (70,79, ' C ') insert into grade values (60,69, ' D ') insert into GRade values (0,59, ' E ')-now queries all students for SNO, CNO, and rank columns. Select sno,cno,degree,  (select rank from grade where degree > Low and Degree < UPP) as Rank from Score&nbs P; from Scoreselect * from grade  --19, query elective "3-105" course performance is higher than the "109" student scores of all the students record. Unrelated subquery select * from score where degree > (select degree from score where sno= ' 109 ' and cno= ' 3-105 ') and CNO = ' 3-105 '-- 20, the inquiry score to choose to learn many courses of the students score is the record of non-highest scores. SELECT * FROM score S1 where  degree < (select MAX (degree) from score s2 where S1.sno = S2.sno GROUP BY S2.sno Havi Ng COUNT (*) >1)    --main query one row per query, and then take the query out of this line sno as a parameter, put into the subquery as a condition, in the subquery    --the root of this condition to filter out the specified Sno, Then the group, to obtain the maximum achievement of the SNO, get the maximum score after    -compared with the main query results, if the main query performance is small, to meet the results of the main query output  --22, inquiry and study number for 108 of 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 = ' ') an D sno!= ' 105 '--108th the birth date of the students is the only,--23, query "Zhang Xu" teachers teaching students results. Select Sno,cno,degree from score where cno = (SelecT CNO from course inner JOIN teacher on course.tno = Teacher.tno and Teacher.tname = ' Zhang Xu ')  --24, query the name of a teacher who has more than 5 students in a course 。 Select Tname from teacher where TNO in (select Tno from Course where cno = (select CNO from Score GROUP by CNO have COUNT (*) >5)--25, check the records of Class 95033 and all 95031 students. SELECT * FROM student where class = ' 95033 ' or class = ' 95031 ' ORDER by class--26, query for courses with 85 + scores cno.select distinct Cno F ROM score where degree >85--27, query out the "computer department" Teacher's transcript of the teaching course. Select Cno,degree from score where CNO in (select CNO from Course where TNO in (select TNO from teacher where depart = ' calculation Machine system ')--28, query "computer department" and "Electronic Engineering department" different titles of teachers Tname and Prof.  select  tname, Prof from teacher where Prof is in  (select prof from teacher where depart= ' electronic Engineering Department ') and DEP art= ' computer system ' union--this thing merges the results of two queries into one, the results of two queries must be consistent in number, the data types are consistent select  tname, prof from teacher where Prof not in  ( Select prof from teacher where depart= ' computer Department ') and depart= ' Electronic Engineering Department '  select Tname, prof from teacher t1   where Not exIsts (SELECT * from teacher T2 where t1.prof=t2.prof and T1.tname <>t2.tname)    --every row of records in the main query, holding this record Prof To the subquery is to see if there is no existing and the same prof, and    --(and its own name, if it does not exist to print out the main query out of this line of records)  --29, the query elective number "3-105" course and the result is at least higher than the elective number " 3-245 "The CNO, Sno and degree of the classmates, and sorted by degree from high to low order. Select Cno,sno,degree from score where degree > (select MAX (degree) from score where cno = ' 3-245 ') and CNO = ' 3-105 ' or Der by degree desc--30, query elective number "3-105" and score higher than elective number "3-245" course of students CNO, Sno and Degree.select Cno,sno,degree from score where Degree > (select MAX (degree) from score where cno = ' 3-245 ') and CNO = ' 3-105 '--31, query all teachers and classmates for name, sex and Birthday.select SN Ame,ssex,sbirthday from student union select 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= ' women ' Union select Tname,tsex,tbirthday from teacher where tsex= ' Female '--33, the results of students who have a lower average score than the course.       Select Sno,cno,degree from score S1 where S1.degree < (select AVG (degree) from score S2 WHere S1.cno = S2.cno GROUP by s2.cno)             --a. Get each class result of each student and compare it with the average score of the course          --b. Get average results          --c. Compare        select sno,s1. Cno,degree from score S1 where degree <          (select AVG (degree) from score S2 where s2.cno =S1.CNO GROUP by S2.cno)              --because CNO is generated in both the main query and the subquery, the distinction is made between              --so we give a name to the table in the main query S1, then the main query produced by CNO called s1.cno              & nbsp  --subquery in the table gave a name S2, sub-query cno is called s2.cno  --34, query all teachers tname and Depart.select Tname,depart from teacher Inner JOIN course on teacher.tno = course.tno--35 query all non-lecturing teachers tname and depart. select Tname,depart from teacher t where n OT exists (SELECT * from course C where C.tno =t.tno)--36, query the class number of at least 2 boys. Select class from student where ssex= ' Man ' GROUP by class has COUNT (*) >=2--37, inquires the student table the surname "the king" 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,datediff (Year,sbirthday,getdate ()) from student--39, querying the maximum and minimum sbirthday date values in the student table. Select Max (Year (sbirthday)) as ' Maximum Date ', Min (year (sbirthday)) as ' minimum date ' from student--40, check all records in student table in order and age from largest to smallest. SELECT * FROM student Order by class Desc,datediff (Year,sbirthday,getdate ()) desc--41, query "male" teachers and their courses. Select Tname,cname from teacher inner joins course on teacher.tno = course.tno where tsex = ' male '--42, query highest scores of students 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 whe Re ssex = (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 = (select Ssex from student where sname = ' li June ') Andclass = (select class from student where sname = ' Li June ') and Sname!= ' Li June '--45 and inquires all the "male" students who have enrolled in the "Introduction to Computer" course. Select Sname,ssex,cname,degree from course inner JOIN SCORE on course.cno = Score.cnoinner Join student on score.sno = STUDENT.SNO where cname = ' computer Introduction ' and Ssex = ' male '--46, query score table Students with the highest score. Multilayer nested Select Student.*,degree from student inner joins score on degree = (select MAX (degree) from score) and Student.sno = score.sno--47, query the average student information in the score table for more than 80 points. Related queries. Unrelated query-related subquery: SELECT * FROM Student St where exists (SELECT * FROM Score sc where St.sno = Sc.sno GROUP by Sc.sno have AVG (sc.degree) >80)--Unrelated subquery: Select *from student where Sno in (select Sno from Score Group by Sno have AVG (degree) >80)

SQL statement little practice one