Database Query instance (contains all where condition examples)

Querying a specified column

[Example 1] All students are queried for their school number and name.

SELECT Sno,sname

From Student;

[Example 2] All students are queried for their name, school number and department.

SELECT sname,sno,sdept

From Student;

[Example 3] A detailed record of all students is queried.

SELECT sno,sname,ssex,sage,sdept

From Student;

Or

SELECT *

From Student;

[Example 4] Check the names of all students and their year of birth.

SELECT Sname,2004-sage/* Assumes the year is 2004 years */from Student;

[Example 5] All students are queried for their names, year of birth and all faculties, requiring all names to be represented in lowercase letters

Select Sname, "Year of Birth: ', 2004-sage,

Islower (sdept)

From Student;

[Example 6] inquiry the student number of the course.

SELECT Sno from SC;

Equivalent to:

SELECT all Sno from SC;

[Example 7] Search the list of all students in computer science department.

SELECT Sname www.111cn.net

From Student

WHERE sdept= "CS?;

[Example 8] The name and age of all students under 20 years of age are queried. SELECT Sname,sage

From Student

WHERE Sage < 20;

[Example 9] The number of students who have failed in the examination results.

SELECT DISTINCT Sno

From SC

WHERE grade<60;

[Example 10] The name, department and age of the student in 20~23岁 (including 20 and 23 years old) are queried

SELECT Sname,sdept,sage

From Student

WHERE Sage between and 23;

[Example 11] Check the name, department, and age of students who are not in the 20~23岁

SELECT Sname,sdept,sage

From Student

WHERE Sage not between and 23;

[Example 12] Inquiry Information Department (IS), the Department of Mathematics (MA) and Computer Science (CS) students ' names and gender.

SELECT Sname,ssex

From Student

WHERE sdept in (' Is ', ' MA ', ' CS ');

[Example 13] The query is neither the information department, the mathematics department, nor the student's name and gender of the computer Science department. SELECT Sname,ssex

From Student

WHERE sdept not in (' is ', ' MA ', ' CS ');

[Example 14] The details of the student who queried the number 200215121.

SELECT *

From Student

WHERE Sno like "200215121′;

Equivalent to:

SELECT *

From Student

WHERE Sno = ' 200215121 ';

[Example 15] query all surname Liu Cosheng's name, school number and gender.

SELECT Sname,sno,ssex

From Student

WHERE Sname like "Liu?;

[Example 16] The name of a student whose surname is "Ouyang" and whose full name is three Chinese characters is queried.

SELECT Sname

From Student

WHERE Sname like ' Ouyang __ ';

[Example 17] The name and student number of the 2nd word "yang" is queried.

SELECT Sname,sno

From Student

WHERE Sname like "_ Yang%?;

[Example 18] The names of all students who are not surnamed Liu are queried.

SELECT Sname,sno,ssex

From Student

WHERE Sname not like ' Liu% ';

[Example 19] Query the course number and credits for the Db_design course.

SELECT Cno,ccredit

From Course

WHERE Cname like ' db_design ' ESCAPE ';

[Example 20] Query the details of a course that begins with "Db_" and the 3rd character is the last. SELECT *

From Course

WHERE Cname like ' db_%i_ _ ' ESCAPE ';

[Example 21] Some students do not take the exam after elective courses, so there is a record of elective courses, but no test results. Check the student's number and the corresponding course number for the missing score. SELECT Sno,cno

From SC

WHERE Grade is NULL

[Example 22] Check all grades of student number and course number.

SELECT Sno,cno

From SC

WHERE Grade is not NULL;

[Example 23] The name of the computer department under the age of 20 years is queried.

SELECT Sname

From Student

WHERE sdept= ' CS ' and sage<20;

Rewriting [Example 12]

[Example 12] Inquiry Information Department (IS), the Department of Mathematics (MA) and Computer Science (CS) students ' names and gender.

SELECT Sname,ssex

From Student

WHERE sdept in (' Is ', ' MA ', ' CS ')

Can be rewritten as:

SELECT Sname,ssex

From Student

WHERE sdept= ' is ' or sdept= ' MA ' or sdept= ' CS ';

[Example 24] Query the number of students taking the 3rd course and their achievements, the results of the query sorted in descending order of fractions.

SELECT Sno,grade

From SC

WHERE cno= ' 3 '

ORDER by Grade DESC;

[Example 25] query all students, the results of the query according to the Department of the line number in ascending order, the students in the same department in descending order by age.

SELECT *

From Student

ORDER by Sdept,sage DESC;

[Example 26] Check the total number of students.

SELECT COUNT (*)

From Student;

[Example 27] The number of students enrolled in the course was queried.

SELECT COUNT (DISTINCT Sno)

From SC;

[Example 28] calculates the average student score for course 1th.

SELECT AVG (Grade)

From SC

WHERE cno= ' 1 ';

[Example 29] Check the highest score for students taking the 1th course.

SELECT MAX (Grade)

From SC

Wher cno= "1?;

[Example 30] Check the total academic score of 200215012 elective courses for students.

SELECT SUM (Ccredit)

From SC, Course

Wher sno= ' 200215012′and SC. Cno=course.cno;

[Example 31] for each course number and the corresponding number of elective courses.

SELECT Cno,count (Sno)

From SC

GROUP by Cno;

[Example 32] The enquiry number of students enrolled in more than 3 courses.

SELECT Sno

From SC

GROUP by Sno

Having COUNT (*) >3;

[Example 33] inquire about each student and its elective course

SELECT student.*,sc.*

From STUDENT,SC

WHERE Student.sno = SC. Sno;

[Example 34] for [Example 33] with a natural connection to complete.

SELECT Student.sno,sname,ssex,sage,sdept,cno,grade from STUDENT,SC

WHERE Student.sno = SC. Sno;

[Example 35] inquiring into the indirect first course of each course (i.e., first course)

SELECT first. Cno,second. Cpno

From Course First,course SECOND

WHERE first. Cpno = SECOND. Cno;

[Example 36] rewriting [Example 33]

SELECT Student.sno,sname,ssex,sage,sdept,cno,grade from Student left off JOIN SC on (STUDENT.SNO=SC. Sno);

[Example 37] All students who have enrolled in course 2nd and have scored above 90 points

SELECT Student.sno, Sname

From Student, SC

WHERE Student.sno = SC. Sno and

/* Connection Predicate */

Sc. Cno= "2? and SC. Grade > 90; * * Other qualifying conditions */

[Example 38] query each student's number, name, elective course name and grade

SELECT Student.sno,sname,cname,grade

From Student,sc,course/* Multi-table connection */

WHERE Student.sno = SC. Sno

and SC. Cno = Course (www.111cn.net). Cno;

[Example 39] query with "Liu Chen" students in the same department of Learning.

This query requires steps to complete

① determine the name of "Liu Chen"

SELECT sdept

From Student WHERE sname= ' Liu Chen ';

The result is: CS

② Find all students studying in the IS Department.

SELECT sno,sname,sdept

From Student

WHERE sdept= ' CS ';

Embed the first step query in the condition of the second step query

SELECT sno,sname,sdept

From Student

WHERE sdept in

(SELECT sdept

From Student

WHERE sname= "Liu Chen?) ；

This query is not related to subqueries.

Use your own connection to complete [example 39] Query requirements

SELECT S1. Sno,s1. Sname,s1. Sdept

From Student s1,student S2

WHERE S1. Sdept = S2. Sdept and

S2. Sname = ' Liu Chen ';

[Example 40] The inquiry took the course name "Information System" student number and name select Sno,sname③ finally in Student relationship from Student remove Sno and Sname WHERE Sno in

(select Sno② then finds the student number in the SC relationship of the selected from SC with course No. 3rd where Cno in

(select Cno① first finds the course number of the from Course "Information System" in the Course relationship, which is number 3rd where cname= "information system?"

)

);

Implementation with connection query [example 40]

SELECT Sno,sname

From Student,sc,course

WHERE Student.sno = SC. Sno and

Sc. Cno = Course.cno and

Course.cname= "Information system?;

Example: Suppose that a student can only study in a department and must belong to a department, then in [Example 39] may be substituted with = in:

SELECT sno,sname,sdept

From Student

WHERE sdept =

(SELECT sdept

From Student

WHERE sname= "Liu Chen?) ；

[Example 41] Find out the average student's course in excess of his elective course

No.

SELECT Sno, Cno

From SC X

WHERE Grade >= (SELECT AVG (Grade)

From SC y

WHERE Y.sno=x.sno);

[Example 42] inquire about the age of a student in other departments than computer science

Small student's name and age

SELECT Sname,sage

From Student

WHERE Sage < any (SELECT Sage

From Student WHERE sdept= ' cs ') and sdept <> "CS"; /* Conditions in the parent query block */

Using aggregation functions for [example 42]

SELECT Sname,sage

From Student

WHERE Sage <

(SELECT MAX (Sage)

From Student www.111cn.net

WHERE sdept= "CS")

and sdept <> ' CS?;

[Example 43] inquire about the names and ages of students in other departments who are younger than all students of computer science department.

Method One: Use the ALL predicate

SELECT Sname,sage

From Student

WHERE Sage < All

(SELECT Sage

From Student

WHERE sdept= ' CS ')

and sdept <> ' CS?;

Method two: Using Aggregation functions

SELECT Sname,sage

From Student

WHERE Sage <

(SELECT MIN (Sage)

From Student

WHERE sdept= ' CS ')

and sdept <> ' CS?;

[Example 44] Check the names of all students who have elective course No. 1th.

Using nested queries

SELECT Sname

From Student

WHERE EXISTS

(SELECT *

From SC

WHERE Sno=student.sno and cno= ' 1 ');

Using the JOIN operation

SELECT Sname

From Student, SC

WHERE STUDENT.SNO=SC. Sno and SC. Cno= ' 1′;

[Example 45] The name of the student who did not take course No. 1th was queried.

SELECT Sname

From Student

WHERE not EXISTS

(SELECT *

From SC

WHERE Sno = Student.sno and cno= ' 1′);

[Example 39] query with "Liu Chen" students in the same department of Learning.

Can be replaced with a subquery with the EXISTS predicate:

SELECT sno,sname,sdept

From Student S1

WHERE EXISTS

(SELECT *

From Student S2

WHERE S2. Sdept = S1. Sdept and

S2. Sname = "Liu Chen?) ；

[Example 46] The name of the student who took all the courses was queried.

SELECT Sname

From Student

WHERE not EXISTS

(SELECT *

From Course

WHERE not EXISTS

(SELECT *

From SC

WHERE sno= Student.sno and cno= course.cno)

）；

[Example 47] The inquiry took at least 200215122 of the students ' elective courses

Health number.

Denoted by the NOT EXISTS predicate:

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));

[Example 48] The students of Computer science department and students younger than 19 years old are queried.

Method One:

? SELECT *

? From Student

? WHERE sdept= ' CS '

? UNION

? SELECT *

? From Student

? WHERE sage<=19;

Method Two:

SELECT DISTINCT *

From Student

WHERE sdept= ' CS ' OR sage<=19;

[Example 49] The query took the course 1 or took the course 2 of the students.

SELECT Sno

From SC

WHERE cno= ' 1 '

UNION

SELECT Sno

From SC

WHERE cno= ' 2 ';

[Example 50] The students of Computer science department are not older than 19 years old

The intersection of the students

SELECT *

From Student

WHERE sdept= ' CS '

INTERSECT

SELECT *

From Student

WHERE sage<=19

Note: SQL Server does not have a cross-operation

[Example 50] is actually querying the computer science department of a small age

Students at the age of 19

SELECT *

From Student

WHERE sdept= "CS? and sage<=19;

[Example 51] The student collection of elective course 1 and elective course 2

The intersection of the Fit

SELECT Sno

From SC

WHERE cno= ' 1 '

INTERSECT

SELECT Sno

From SC

WHERE cno= ' 2 ';

[Example 51] is actually a query that has both elective course 1 and elective course 2

of students

SELECT Sno

From SC

WHERE cno= ' 1 ' and Sno in

(SELECT Sno from SC

WHERE cno= ' 2 ');

[Example 52] The students of Computer science department and the age of not more than 19 years of study

The difference set of the birth.

SELECT *

From Student

WHERE sdept= ' CS '

EXCEPT

SELECT *

From Student

WHERE Sage <=19;

Note: SQL Server error-free operation

[Example 52] is actually querying the computer science department for older than 19 years

of students

SELECT *

From Student

WHERE sdept= ' CS ' and sage>19;
From:http://www.111cn.net/database/mssqlserver/59038.htm

Database Query instance (contains all where condition examples)