SQL Server Query Exercise

This is the table used in the exercise:

--1.select DNO from Dept where dname= ' mathematics Department '

Select Sno,sname from student where dno= (select DNO from dept where dname= ' Mathematics Department ');

--2. Number of students who have elective courses

SELECT DISTINCT Sno from SC;

--3. The number of students enrolled in the No. No. 01 course and results, the results of the search by grade in ascending order, the same results by the number of descending order
Select Sno,grade from SC where cno= ' down ' ORDER by Grade,sno Desc

--4. Ask for the student's number and score in the No. 01 course with a score of 80-90, and multiply the score by 0.8 output.

Select sno,grade=grade*0.8 from SC where cno= ' grade between and 90

--5. Information about students who are in the Math department or computer department surnamed Zhang
SELECT * FROM student where DNO in (select DNO
From dept where Dname in (' Mathematics department ', ' computer Department ');

--6. View elective courses, but no information on students ' grades.
SELECT * FROM student where Sno in (select Sno
From SC where grade is null)

--7. Query student's school number, grade, name, course name
Select Sc.sno,sname,cname,grade from Sc,student,course
where Sc.sno=student.sno and Sc.cno=course.cno

--8. Cross-linking of students and departments respectively, inner connection, outer connection
--Inner Connection:
SELECT * FROM student, dept where Student.dno=dept.dno;
--External connection:
SELECT * FROM student LEFT OUTER join dept on (STUDENT.DNO=DEPT.DNO);

--9. Take the student's number and name in advanced mathematics
Select Sname, sno from student where Sno in (select Sno from SC
where cno = (select CNO from Course
Where cname= ' mathematics ');

--10. The results of the No. 01 course and the scores and number of students above Zhou Libo

Select Sno,grade from SC where grade > (select grade from
SC where sno= (select Sno from student
Where sname= ' Zhou Libo '));

--11. Students of other faculties who are younger than the maximum age of the computer system

Select Max (sage) from student where dno= (select DNO from dept
where dname= ' computer system ');

Select Sage from student where dno= (select DNO from dept
where dname= ' computer system ');

SELECT * FROM student where Sage < (select Max (sage) from student
where dno= (select DNO from dept
where dname= ' computer system ') and DNO! = (select DNO from dept
where dname= ' computer system ');

--12. Students in other departments who are younger than those in computer science

SELECT * FROM student where Sage < All (select Sage from student
where dno= (select DNO from dept
where dname= ' computer system ') and DNO! = (select DNO from dept
where dname= ' computer system ');

--13. The name of the student who took the No. No. 02 Course
Select Sname from student where Sno in (select Sno from SC where cno= ' 02 ')
--14. The name of the student who did not take the No. No. 01 course
Select Sname from student where Sno not in (select Sno from SC where cno= ' 02 ')

--15. Query the name of the student who took all the courses
Select sname from student where isn't exists (SELECT * FROM Course
Where NOT EXISTS (SELECT * FROM SC
where Sc.sno=student.sno and Course.cno=sc.cno));

--16 the student's number and name of the students enrolled in all the courses taken by the student number ' 2014005 '.
Select Sname, sno from student where NOT EXISTS (SELECT * FROM SC
Where NOT EXISTS (SELECT * FROM
(SELECT * from SC where sno= ' 2014005 ') as NEWTB
where
Newtb.sno=student.sno and Sc.cno=newtb.cno));

SELECT DISTINCT Sno from SC S1
Where NOT EXISTS
(SELECT * from SC S2 where s2.sno= ' 2014005 ' and NOT exists
(SELECT * from SC S3 where S1.sno=s3.sno and S2.cno=s3.cno)
)

Select Sname,sno from student where Sno on (SELECT DISTINCT Sno from SC S1
Where NOT EXISTS
(SELECT * from SC S2 where s2.sno= ' 2014005 ' and NOT exists
(SELECT * from SC S3 where S1.sno=s3.sno and S2.cno=s3.cno)
))

--note the order between tables
Select Sname, sno from student where isn't exists (SELECT * FROM
(select CNO from SC where sno= ' 2014005 ') as NEWTB
Where NOT EXISTS (SELECT * FROM SC
where Sc.sno=student.sno and Sc.cno=newtb.cno));

SQL Server Query Exercise