SQL Server Basic statement exercise (i)

Learn a lot of things, feel their own SQL statement is still very bad, from the basic learning.

To a SQL script:

Create DATABASE Tongjigouse tongjigocreate table student (Sno varchar) NOT NULL PRIMARY key,--number sname varchar (a) not n ull,--student name Ssex varchar (NOT NULL)--Student Sex sbirthday datetime,--Student birthdate class varchar (20)-Student Class) Gocreate table teacher--Teacher (Tno varchar) NOT NULL PRIMARY key,--Faculty number (master code) tname varchar () NOT NULL,--faculty name Tsex varchar () NOT NULL,-- Faculty Sex Tbirthday DateTime,--faculty born Prof VarChar (a),--title depart varchar () not null--Faculty Department) gocreate Table course--Course (Cno varchar () Not NULL PRIMARY key,--course number CNAME varchar () NOT NULL,--course name Tno varchar (a) NOT null references teacher (TNO), --Faculty Number (external code)) Gocreate table score--fraction (Sno varchar () NOT NULL references student (SNO),-------------------------(external code) Cno varchar () NOT NULL ref Erences Course (Cno),--Course number (external code) primary key (SNO,CNO), Degree Decimal (4,1),--score)

Start writing the practice SQL statement:

--1. Query 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 in the Depart column. SELECT DISTINCT depart from teacher--3. Queries all records in the score table that have scores from 60 to 80. SELECT * FROM score WHERE degree between and 80--4. Query score table for records with a score of 85, 86, or 88. SELECT * from score where degree in (85,86,88)--5. Query student table in "95031" class or sex for "female" student records. SELECT * from student where [class]= ' 95031 ' and ssex= ' female '--6. Queries all records in the student table in descending order of class. SELECT * FROM student order by class desc--7. Queries all records in score table in CNO Ascending, degree descending order. SELECT * FROM Score ORDER by Cno,degree desc--8. Check the number of students in the "95031" class. Select COUNT (*) from student where class= ' 95031 '--9. Check the student number and course number of the highest score in the score table. (subquery or sort) Select Sno,cno from score where degree= (select MAX (degree) from score)--10. Query the average score for each course. Select Cno,avg (degree) from score Group by cno--11. Query score the average score of at least 5 students enrolled in a course that starts with 3.  Select AVG (degree) from score where CNO like ' 3% ' GROUP by CNO have COUNT (*) >=5select AVG (degree) from score Group BY Cno has COUNT (*) >=5 and Cno like ' 3% '--12. The query score is greater than 70, and the SNO column is less than 90. Select SNO from score where degree>70 and degree<90--13. Query the Sname, CNO, and degree columns for all students. Select Sname,cno,degree from score joins student on score.sno = student.sno--14. Query the SNO, CNAME, and degree columns for all students. Select Sno,cname,degree from score joins course on score.cno=course.cno--15. Queries all students for sname, CNAME, and degree columns. Select Sname,cname,degree from score join student on Student.sno=score.sno join course on SCORE.CNO = course.cnoselect SNA Me,cname,degree from Score,student,course where student.sno=score.sno and score.cno = Course.cno

SQL Server Basic statement exercise (i)