2015-12-01 SQL query Statement basics

1. Check the student number and name of all students
Select Sno,sname
from student;
3. Check the details of all students
SELECT *
from student;
4. Query the names of all students and their year of birth
Select Sname,2004-sage
from student;
5. Check the names of all students, year of birth and Department of origin, and require that all names be indicated in lowercase letters
Select Sname, ' Year of birth: ', 2004-sage,lower (sdept)
from student;
6. Check the student number of elective course, distinct remove duplicate line
Select DISTINCT Sno
From SC;

Select Sno
From SC;
Equivalent to
Select All Sno
From SC;
7. Check the list of all students in the computer science department
Select Sname
From student
Where sdept= ' CS ';
8. Check the name and age of all students under 20 years of age
Select Sname,sage
From student
where sage<20;
9. Check the number of students who have failed in the exam results
Select DISTINCT Sno
From SC
where grade<60;
10. Check the name, department and age of students aged between 20~23岁 (including 20 and 23 years old)
Select Sname,sdept,sage
From student
where Sage between and 23;
11 Check the name, department and age of students who are not between 20~23岁
Select Sname,sdept,sage
From student
where sage not between and 23;
12. Query Computer Science Department CS, Mathematics Department MA and Information department is student's name and gender
Select Sname,ssex
From student
where sdept in (' CS ', ' ma ', ' is ');
13. The query is not the computer Science department CS, the Mathematics Department MA and the information department is student's name and the gender
Select Sname,ssex
From student
where sdept not in (' CS ', ' ma ', ' is ');
14. Details of the students who have queried the number 200215121
SELECT *
From student
Where Sno like ' 200215121 ';
Equivalent to
SELECT *
From student
where sno= ' 200215121 ';
15. Check the name, number and gender of all students surnamed Liu
Select Sname,sno,ssex
From student
Where sname like ' Liu ';
16. Check the names, student numbers and gender of all students not surnamed Liu
Select Sname,sno,ssex
From student
Where sname not like ' Liu% ';
17. Check the name of a student surnamed Ouyang with a full name of 3 kanji
Select Sname
From student
Where sname like ' Ouyang _ ';
19. Check the course number and credits for the Db_design course
Select Cno,ccredit
From course
Where cname like ' db\_design ' ESCAPE ' \ ';
ESCAPE ' \ ' means ' \ ' for the code-changing character. The character "_" following the "\" in the matching string no longer has the meaning of a wildcard and is escaped as a normal "_" character
20. Query the details of the course beginning with "Db_" and the 3rd character
SELECT *
From course
Where cname like ' db\_%i__ ' ESCAPE ' \ ';
21. Check the student's number and corresponding course number for the missing grades
Select Sno,cno
From SC
where grade is null;
22. Check the names of students under the age of 20 in the computer science department
Select Sname
From student
Where sdept= ' CS ' and sage<20;
23. Query Computer Science Department CS, Mathematics Department MA and Information department is student's name and gender
Select Sname,ssex
From student
Where sdept= ' cs ' or sdept= ' ma ' or sdept= ' is ';
24. Check the number of students who have enrolled in course 3rd and their scores, and the results are sorted in descending order of fractions
Select Sno,grade
From SC
where cno= ' 3 '
Order by grade DESC;
25. Query all students, query results according to the Department of the line number in ascending order, the students of the same department in descending order by age
SELECT *
From student
Order by Sdept,sage DESC;
26. Check the total number of students
Select COUNT (*)
from student;
27. Check the number of students who have enrolled in the course
Select COUNT (DISTINCT Sno)
from SC;
28. Average scores of students in the 1th course
Select AVG (Grade)
From SC
where cno= ' 1 ';
29. Check the highest score for students taking course number 1th
Select MAX (Grade)
From SC
where cno= ' 1 ';
30. Check the total academic score of 200215012 elective courses for students
Select SUM (Ccredit)
From Sc,course
where sno= ' 200215012 ' and sc.cno=course.cno;
31. Ask for each course number and the corresponding number of courses selected
Select Cno,count (SNO)
From SC
Group by CNO;
32. Enquiry number of students enrolled in more than 3 courses
Select Sno
From SC
GROUP BY Sno
Having Count (*) >3;
33. Inquire about each student and the course of their selection
Select student.*,sc.*
From STUDENT,SC
where Student.sno=sc.sno;
35. To inquire about the indirect first course of each course
Select First.cno=second.cpno
From Course First,course second
where First.cpno =second.cno;
36.
Select Student.sno,sname,ssex,sage,sdept,cno,grade
From student left out join SC on (STUDENT.SNO=SC.SNO);
37. Enquiries for all students who have enrolled in the 2nd course and have achieved a score of 90
Select Student.sno,sname
From STUDENT,SC
where Student.sno=sc.sno and
Sc.cno= ' 2 ' and sc.grade>90;
38. Query each student's number, name, elective course name and grade
Select Student.sno,sname,cname,grade
From Student,sc,course
where Student.sno=sc.sno and Sc.cno=course.cno;
39. Query students who are studying in the same department as Liu Chen
Select Sno,sname,sdept
From student
where Sdept in
(Select Sdept
From student
Where Sname= ' Liu Chen ');
Equivalent to
Select S1.sno,s1.sname,s1.sdept
From student s1,student S2
where S1.sdept=s2.sdept and
S2.sname= ' Liu Chen ';
40. Enquiry the student number and name of the course entitled "Information System"
Select Sno,sname
From student
Where Sno in
(Select Sno
From SC
where CNO in
(Select CNO
From course
Where cname= ' Information system '
)
);
Equivalent to
Select Student.sno,sname
From Student,sc,course
where Student.sno=sc.sno and
Sc.cno=course.cno and
Course.cname= ' Information system ';
41. Find out the course number for each student who exceeds the average grade of his elective course
Select Sno,cno
From SC X
where grade>= (select AVG (Grade)
From SC y
where Y.sno=s.sno
);
42. Check the name and age of a student in another department who is younger than the computer science department
Select Sname,sage
From student
Where Sage<any (select Sage
From student
Where sdept= ' CS '
)
and sdept <> ' CS ';
43. Check the names and ages of students who are younger than all students in the Department of Computer Science
Select Sname,sage
From student
where Sage < All
(Select Sage
From student
Where sdept= ' CS '
)
and sdept <> ' CS ';
44. Check the names of all students who have enrolled in course 1th
Select Sname
From student
where EXISTS
(SELECT *
From SC
where Sno=student.sno and cno= ' 1 ');
If the inner query result is not empty after using the quantifier exists, the outer WHERE clause returns TRUE, otherwise the false value is returned
45. Check the names of students who do not have elective course No. 1th
Select Sname
From student
Where NOT EXISTS
(SELECT *
From SC
where Sno=student.sno and cno= ' 1 ');
46. Check the name of the student who took all the courses
Select Sname
From student
Where NOT EXISTS
(SELECT *
From course
Where NOT EXISTS
(SELECT *
From SC
where Sno=student.sno
and Cno=course.cno));
47. Check the student number of at least 200215122 elective courses
SELECT DISTINCT Sno
From SC Scx
Where NOT EXISTS
(SELECT *
From SC Scy
where scy.sno= ' 200215122 ' and
NOT EXISTS
(SELECT *
From SC Scz where Scz.sno=scx.sno and Scz.cno=scy.cno));
48. Check with students of computer science department and students younger than 19 years old
SELECT *
From student
Where sdept= ' CS '
Union
SELECT *
From student
where sage<=19;
Union automatically removes duplicate tuples, and the union ALL operator can save repeating tuples
49. Check the set of students who have enrolled in Course 1 or Course 2
Select Sno
From SC
where cno= ' 1 '
Union
Select Sno
From SC
where cno= ' 2 ';
50. Query the intersection of students in computer science and students older than 19 years of age
SELECT *
From student
Where sdept= ' CS '
Intersect
SELECT *
From student
where sage<=19;

2015-12-01 SQL query Statement basics