SQL query statement exercise

SQL query statement exercise

Create three tables for the student-course database:

Student table: Student (SNO, sname, ssex, sage, sdept) Sno;

Course schedule: Course (CNO, cname, cpno, credeit) CNO;

Student electives: SC (SNO, CNO, grade) SnO, CNO, master code;

Student

Student ID

Sno name

Sname gender

Ssex age

Sage System

Sdept

95001 LI Yong male 20 cs

95002 Liu Chen female 19is

95003 Wang minfemale 18ma

95004 Zhang lilan, 19is

Course No.

Sno Course name

Cname first course

Cpno credits

Credit

1 Database 54

2 mathematics 2

3 Information System 14

4 operating system 63

5. Data Structure 74

6. Data Processing 2

7pascal 64

Course:

SC:

Student ID

Sno course No.

CNO score

Grade

95001192

95001285

95001388

95002290

95002380

I. query columns and rows in a table

1: query the learning and name of all students

Sele SnO, sname from student

2: query the name, student ID, and Department of all students.

Sele SnO, sname, sdept from student

3: Query detailed records of all students

Sele * from student

4: query the names and birthyears of all students.

Sele SnO, sage from student

5: query the names of all students, including the Year of birth and the department where the student is located. Use lowercase letters to indicate the Department name.

6: query the student ID of the Selected Course

Sele SnO, CNO from SC

7: query the names of students who have selected the course

Sele distinct sname from student, SC where student. Sno = SC. SnO

Ii. Conditional query:

Common query Conditions

Query condition predicates

Comparison =, <,>, >=, <= ,! =, <>,!> ,! <;

Not + comparison operator

The value range is between and, not between and,

Determine the set in, not in

Character match like, not like

Null isnull, isnotnull

Multiple conditions and, or

1: query the names of all students in the computer department

Sele sname from student where sdept = "Cs"

2: query the names and ages of all students under the age of 20

Sele sname, sage from student where sage <20

3: Check the student ID for which the exam score fails.

Sele student. Sno from student, SC where student. Sno = SC. SnO and Grade <60

4: query the names, department and age of the students aged between 20 and 23.

Sele sname, sdept, sage from student where sage between 20 and 23

5: query the names, department and age of students between 20 and 23.

Sele sname, sdept, sage from student where Sage not between 20 and 23

6. query the names and gender of students in the Information Department (is), Mathematics Department (MA), and computer department (CS ).

Sele sname, ssex from student where sdept in ("is", "Ma", "CS ")

7. Name and gender of students not in the Information Department (is), Mathematics Department (MA), and computer department (CS)

Sele sname, ssex from student where sdept not in ("is", "Ma", "CS ")

8: query the student details with the student ID "95001"

Sele * from student where SnO = 95001.

9: query the names, student IDs, and gender of all students surnamed Liu (where name like 'Liu % ')

Sele sname, SnO, ssex from student where sname like 'Liu %'

10: query the names of students named "Ouyang" and named as three Chinese characters.

Sele sname from student where sname like 'ouyang _'

11: query the Student name and student ID (where sname like '_ Yang %') whose 2nd words are "yang ')

Sele sname, SnO from student where sname like '_ Yang %'

12: query the names of all students not surnamed Liu.

Sele sname from student where sname not like 'Liu %'

13: query the course number and credits of the db_design course (where cname like 'db _ DESIGN 'escape '')

Sele CNO, gredit from course where cname like 'db _ Design' escape''

14: query the details of a course starting with "DB _" and ending with 3rd characters (where cname like 'db _ % I _ 'escape '')

'Db _ % I _ 'escape '') Sele CNO, gredit from course where cname like 'db _ % I _ 'escape''

15: query the student's student ID and the corresponding course number.

Sele student. Sno, CNO from student, SC where grade is null

16: query the student ID and course number for all scores (where grade is not null)

Sele student. Sno, CNO from student, SC where grade is not null

17: query the names of students under the age of 20 in the computer department
Sele sname from student where sdept = "CS" and sage <20
18: query the student ID and score of the course No. 3, and sort the scores in descending order.
Sele student. Sno, grade from student, SC where student. Sno = SC. SnO and SC. CNO = 3 order by grade DESC

19: query the status of all the students. The results are listed in ascending order of the numbers in the department. Students in the same department are listed in descending order of age.
Sele * from student order by sdept, sage DESC

Iii. Use the Set Function
Count, sum, AVG, Max, Min
1: query the total number of students
2: query the number of students enrolled in the course (select count (distinct SnO ))
3: calculate the average score of Students in course 1
4: query the maximum score of students who take the No. 1 course
5: Find the course number and the number of students (selsect CNO, count (SNO); from SC; group by CNO)
6: query the student ID of more than three courses
Select SnO
From SC
Group by SnO
Having count (*)> 3
4. Connection query:
<1> equi and non-equi connection queries
In the connection query, two conditions are connected, which are called join conditions or join predicates. When the connection operator is "=", the conditions are called equijoin, such as, =, <,>, <=, >= ,! = Non-equivalent connection during connection
1: query the status of each student and their Optional Courses
Select student. *, SC .*
From student, SC
Where student. Sno = SC. SnO
<2> Self-connection
The connection operation queries connections in the same table.
2: query the indirect preference (that is, the preference) of each course)
Select first. CNO, second. CNO
From course first, course second
Where first. CNO = second. CNO
V. Composite condition join
1: Query all students who have taken course 2 and scored more than 90.
Select student, sname
Form student, SC
Where student. Sno = SC. SnO and
SC. CNO = '2' and SC. Grade> 90
6. nested Query
1: subquery with predicate in
<1> query students in the same department as "Liu Chen"
Select SnO, sname, sdept
From student
Where sdept in (
Select sdept
From student
Where sname = "Liu Chen ")
Or: Select s1.sname, s1.sdept
From student S1, student S2
Where s1.dept = s2.dept and s2.name = "Liu Chen"
<2> query the student ID and name for the course "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 ")
Or: Select SnO, sname
From student, SC, Course
Where student. Sno = SC. SnO and
SC. CNO = course. CNO and
Course. cname = 'information system ')
2: subqueries with any or all predicates
<1> query the names and ages of a student younger than a certain student in the information system in other systems.
Select sname, sage
From student
Where sage <Any (select sage
From student
Where sdept = 'is'
And sdept <> 'is'
Or use the set function: Select sname, sage
From student
Where sage <
(Select max (SAGE)
From student
Where sdept = 'is ')
And sdept <> 'is'
<2> query the names and ages of students younger than all students in other systems.
Select sname, sage
From student
Where sage <all
(Select sage
From student
Where sdept = 'is ')
And sdept <> 'is'
3 subqueries with exitst predicates
<1> query the names of all students taking course 1.
Select sname
From student
Where exists
(Select *
From SC
Where SnO = student. SnO and CNO = '1 ')
<2> query the names of students not taking course 1.
Select sname
Form student
Where not exists
(Select *
Form SC
Where SnO = stuedent. SnO and CNO = '1 ')
<2> query the names of all students taking all 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)
<3> The number of the students whose courses are not selected in student 95002 is found.
Select distinct SnO
From SC SCX
Where not exists
(Select *
From SC SCY
Where SCY. Sno = '20140901' and
Not exists
(Select *
From SC SCZ
Where SCZ. Sno = SCX. SnO and
SCZ. CNO = SCY. CNO)