50 classic SQL statements

50 commonly used SQL statements Student (S #, Sname, Sage, Ssex) Student table Course (C #, Cname, T #) curriculum SC (S #, C #, score) exam table Teacher (T #, Tname) Instructor table questions: 1. query the student ID of all the students whose scores are higher than those of the 002 course; selecta. S # from (selects #, scorefromSCwhereC #001)

50 commonly used SQL statements Student (S #, Sname, Sage, Ssex) Student table Course (C #, Cname, T #) curriculum SC (S #, C #, score) example Table Teacher (T #, Tname) Instructor table questions: 1. query the student IDs of all the students whose scores are higher than those of the 002 course; select. S # from (select s #, score from SC where C # = '001 ')

50 commonly used SQL statements

Student (S #, Sname, Sage, Ssex) Student table

Course (C #, Cname, T #) Curriculum

SC (S #, C #, score) Orders table

Teacher (T #, Tname) Instructor table

Problem:

1. query the student ID of all students whose score is higher than that of the "002" course;

Select a. S # from (select s #, score from SC where C # = '001') a, (select s #, score

From SC where C # = '002') B

Where a. score> B. score and a. s # = B. s #;

2. query the student ID and average score of students whose average score is greater than 60;

Select S #, avg (score)

From SC

Group by S # having avg (score)> 60;

3. query the student ID, name, number of course selections, and total score of all students;

Select Student. S #, Student. Sname, count (SC. C #), sum (score)

From Student left Outer join SC on Student. S # = SC. S #

Group by Student. S #, Sname

4. query the number of teachers surnamed "Li;

Select count (distinct (Tname ))

From Teacher

Where Tname like 'Li % ';

5. query the student ID and name of the student who has not learned the course "ye ping;

Select Student. S #, Student. Sname

From Student

Where S # not in (select distinct (SC. S #) from SC, Course, Teacher where SC. C # = Course. C # and Teacher. T # = Course. T # and Teacher. tname = 'peiping ');

6. query the student ID and name of the student who has learned the course 001 and has also learned the course 002;

Select Student. S #, Student. sname from Student, SC where Student. S # = SC. S # and SC. C # = '001' and exists (Select * from SC as SC _2 where SC _2.S # = SC. S # and SC _2.C # = '002 ');

7. query the student ID and name of all students who have learned the course taught by instructor ye Ping;

Select S #, Sname

From Student

Where S # in (select S # from SC, Course, Teacher where SC. C # = Course. C # and Teacher. T # = Course. T # and Teacher. tname = 'peiping 'group by S # having count (SC. C #) = (select count (C #) from Course, Teacher where Teacher. T # = Course. T # and Tname = 'peiping '));

8. query the student ID and name of all students whose score is smaller than the score of course no. "002" than the course No. "001;

Select S #, Sname from (select Student. S #, Student. sname, score, (select score from SC SC _2 where SC _2.S # = Student. S # and SC _2.C # = '002 ') score2

From Student, SC where Student. S # = SC. S # and C # = '001') S_2 where score2

9. query the student ID and name of all students whose course scores are less than 60;

Select S #, Sname

From Student

Where S # not in (select Student. S # from Student, SC where S.S # = SC. S # and score> 60 );

10. query the student ID and name of the student who has not completed all the courses;

Select Student. S #, Student. Sname

From Student, SC

Where Student. S # = SC. S # group by Student. S #, Student. Sname having count (C #) <(select count (C #) from Course );

11. query the student ID and name of at least one course with the student ID "1001;

Select S #, Sname from Student, SC where Student. S # = SC. S # and C # in select C # from SC where S # = '123 ';

12. query the student ID and name of at least one student whose student ID is 001;

Select distinct SC. S #, Sname

From Student, SC

Where Student. S # = SC. S # and C # in (select C # from SC where S # = '001 ');

13. Change the average score of the Course taught by instructor ye Ping in the SC table;

Update SC set score = (select avg (SC _2.score)

From SC SC _2

Where SC _2.C # = SC. C #) from Course, Teacher where Course. C # = SC. C # and Course. T # = Teacher. T # and Teacher. tname = 'peiping ');

14. query the student ID and name of other students whose courses are identical to those of "1002;

Select S # from SC where C # in (select C # from SC where S # = '123 ')

Group by S # having count (*) = (select count (*) from SC where S # = '123 ');

15. Delete the SC table record for learning "ye ping;

Delect SC

From course, Teacher

Where Course. C # = SC. C # and Course. T # = Teacher. T # and Tname = 'peiping ';

16. insert some records into the SC table. These records must meet the following requirements: Student IDs, 2, and,

Average score of course no;

Insert SC select S #, '002 ', (Select avg (score)

From SC where C # = '002') from Student where S # not in (Select S # from SC where C # = '002 ');

17. Show the "Database", "enterprise management", and "English" course scores of all students based on average scores in the following format: Student ID ,, database, enterprise management, English, number of valid courses, average effective score

Select s # as student ID

, (SELECT score from SC where SC. S # = t. S # AND C # = '004 ') AS database

, (SELECT score from SC where SC. S # = t. S # AND C # = '001') AS Enterprise Management

, (SELECT score from SC where SC. S # = t. S # AND C # = '006 ') AS English

, COUNT (*) AS valid course COUNT, AVG (t. score) AS average score

From SC AS t

Group by s #

Order by avg (t. score)

18. query the highest score and lowest score of each subject: displayed as follows: course ID, highest score, lowest score

Select l. C # As course ID, L. score AS highest score, R. score AS lowest score

From SC l, SC AS R

Where l. C # = R.C # and

L. score = (select max (IL. score)

From SC AS IL, Student AS IM

Where l. C # = IL. C # and IM. S # = IL. S #

Group by il. C #)

AND

R. Score = (select min (IR. score)

FROM SC AS IR

WHERE R.C # = IR. C #

Group by ir. C #

);

19. The average score of each subject ranges from high to high and the pass rate.

SELECT t. C # AS course number, max (course. Cname) AS course name, isnull (AVG (score), 0) AS average score

, 100 * SUM (case when isnull (score, 0)> = 60 THEN 1 ELSE 0 END)/COUNT (*) AS pass percentage

From SC T, Course

Where t. C # = course. C #

Group by t.c #

Order by 100 * SUM (case when isnull (score, 0)> = 60 THEN 1 ELSE 0 END)/COUNT (*) DESC

20. query the average score and pass percentage of the following courses (shown in "1 line"): Enterprise Management (001), Marx (002), OO & UML (003 ), database (004)

Select sum (case when c # = '001' THEN score ELSE 0 END)/SUM (case c # WHEN '001' THEN 1 ELSE 0 END) AS average score of Enterprise Management

, 100 * SUM (case when c # = '001' AND score> = 60 THEN 1 ELSE 0 END) /SUM (case when c # = '001' THEN 1 ELSE 0 END) AS enterprise management pass percentage

, SUM (case when c # = '002' THEN score ELSE 0 END)/SUM (case c # WHEN '002' THEN 1 ELSE 0 END) AS Marx average score

, 100 * SUM (case when c # = '002 'AND score> = 60 THEN 1 ELSE 0 END) /SUM (case when c # = '002' THEN 1 ELSE 0 END) AS Marx pass percentage

, SUM (case when c # = '003 'THEN score ELSE 0 END)/SUM (case c # WHEN '003' THEN 1 ELSE 0 END) as uml average score

, 100 * SUM (case when c # = '003 'AND score> = 60 THEN 1 ELSE 0 END) /SUM (case when c # = '003 'THEN 1 ELSE 0 END) as uml pass percentage

, SUM (case when c # = '004 'then score ELSE 0 END)/SUM (case c # WHEN '004 'then 1 ELSE 0 END) AS database average score

, 100 * SUM (case when c # = '004 'AND score> = 60 THEN 1 ELSE 0 END) /SUM (case when c # = '004 'THEN 1 ELSE 0 END) AS database pass percentage

FROM SC

21. query the average scores of different courses taught by different teachers from high to low.

SELECT max (Z. T #) AS instructor ID, MAX (Z. tname) AS instructor name, C. C # AS course ID, MAX (C. cname) AS course name, AVG (Score) AS average Score

From SC AS T, Course AS C, Teacher AS Z

Where T.C # = C. C # and C. T # = Z. T #

Group by c. C #

Order by avg (Score) DESC

22. query the transcript of the following course scores of 3rd to 6th students: Enterprise Management (001), Marx (002), UML (003), Database (004)

[Student ID], [Student name], enterprise management, Marx, UML, database, average score

Select distinct top 3

SC. S # As student ID,

Student. Sname AS Student name,

T1.score AS enterprise management,

T2.score AS Marx,

T3.score as uml,

T4.score AS database,

ISNULL (T1.score, 0) + ISNULL (T2.score, 0) + ISNULL (T3.score, 0) + ISNULL (T4.score, 0) as total score

FROM Student, SC left join SC AS T1

On SC. S # = T1.S # AND T1.C # = '001'

Left join SC AS T2

On SC. S # = T2.S # AND T2.C # = '002'

Left join SC AS T3

On SC. S # = T3.S # AND T3.C # = '003'

Left join SC AS T4

On SC. S # = T4.S # AND T4.C # = '004'

WHERE student. S # = SC. S # and

ISNULL (T1.score, 0) + ISNULL (T2.score, 0) + ISNULL (T3.score, 0) + ISNULL (T4.score, 0)

NOT IN

(SELECT

DISTINCT

TOP 15 WITH TIES

ISNULL (T1.score, 0) + ISNULL (T2.score, 0) + ISNULL (T3.score, 0) + ISNULL (T4.score, 0)

FROM SC

Left join SC AS T1

ON SC. S # = T1.S # AND T1.C # = 'k1'

Left join SC AS T2

ON SC. S # = T2.S # AND T2.C # = 'k2'

Left join SC AS T3

ON SC. S # = T3.S # AND T3.C # = 'k3'

Left join SC AS T4

ON SC. S # = T4.S # AND T4.C # = 'k4'

Order by isnull (T1.score, 0) + ISNULL (T2.score, 0) + ISNULL (T3.score, 0) + ISNULL (T4.score, 0) DESC );

23. Print the score of each subject in Statistics. Number of students in each score segment: course ID, course name, [100-85], [85-70], [70-60], [<60]

Select SC. C # as course ID, Cname as course name

, SUM (case when score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100-85]

, SUM (case when score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85-70]

, SUM (case when score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70-60]

, SUM (case when score <60 THEN 1 ELSE 0 END) AS [60-]

From SC, Course

Where SC. C # = Course. C #

Group by SC. C #, Cname;

24. query the average score and rankings of Students

SELECT 1 + (select count (distinct average score)

FROM (select s #, AVG (score) AS average score

FROM SC

Group by s #

) AS T1

WHERE average score> T2. average score) as ranking,

S # as student ID, average score

FROM (select s #, AVG (score) average score

FROM SC

Group by s #

) AS T2

Order by average score desc;

25. query the top three records of each subject's score (excluding the parallel score)

SELECT t1.S # as student ID, t1.C # as course ID, Score as Score

From SC t1

WHERE score IN (select top 3 score

FROM SC

WHERE t1.C # = C #

Order by score DESC

)

Order by t1.C #;

26. query the number of students selected for each course

Select c #, count (S #) from SC group by C #;

27. Check the student ID and name of all students who have selected only one course.

Select SC. S #, Student. Sname, count (C #) AS Course selections

From SC, Student

Where SC. S # = Student. S # group by SC. S #, Student. Sname having count (C #) = 1;

28. query the number of boys and girls

Select count (Ssex) as boys from Student group by Ssex having Ssex = 'male ';

Select count (Ssex) as number of girls from Student group by Ssex having Ssex = 'female ';

29. query the student list with the last name "Zhang"

SELECT Sname FROM Student WHERE Sname like 'sheet % ';

30. query the list of same-name students and count the number of students with the same name

Select Sname, count (*) from Student group by Sname having count (*)> 1 ;;

31. List of students born on April 9, 1981 (note: the type of the Sage column in Student table is datetime)

Select Sname, CONVERT (char (11), DATEPART (year, Sage) as age

From student

Where CONVERT (char (11), DATEPART (year, Sage) = '123 ';

32. query the average scores of each course. The results are sorted in ascending order based on the average scores. The average scores are the same and the course numbers are sorted in descending order.

Select C #, Avg (score) from SC group by C # order by Avg (score), C # DESC;

33. query the student ID, name, and average score of all students whose average score is greater than 85

Select Sname, SC. S #, avg (score)

From Student, SC

Where Student. S # = SC. S # group by SC. S #, Sname having avg (score)> 85;

34. query the names and scores of students whose course names are "databases" and whose scores are less than 60.

Select Sname, isnull (score, 0)

From Student, SC, Course

Where SC. S # = Student. S # and SC. C # = Course. C # and Course. Cname = 'database' and score <60;

35. query the Course selections of all students;

Select SC. S #, SC. C #, Sname, Cname

From SC, Student, Course

Where SC. S # = Student. S # and SC. C # = Course. C #;

36. query the name, course name, and score of any course whose score is over 70;

SELECT distinct student. S #, student. Sname, SC. C #, SC. score

FROM student, SC

Where SC. score> = 70 and SC. S # = student. S #;

37. query failed courses and arrange them in ascending order by course number

Select c # from SC where scor e <60 order by C #;

38. query the student ID and name of the student whose course number is 003 and whose score is higher than 80;

Select SC. S #, Student. Sname from SC, Student where SC. S # = Student. S # and Score> 80 and C # = '003 ';

39. Number of students selected for the course

Select count (*) from SC;

40. query the names of the students with the highest scores and their scores of the students who take the courses taught by "ye ping ".

Select Student. Sname, score

From Student, SC, Course C, Teacher

Where Student. S # = SC. S # and SC. C # = C. C # and C. T # = Teacher. T # and Teacher. tname = 'peiping 'and SC. score = (select max (score) from SC where C # = C. C #);

41. query each course and the number of optional students

Select count (*) from SC group by C #;

42. query the student's student ID, course number, and student score with the same course score

Select distinct A.S #, B. score from SC a, SC B where A. Score = B. Score and A. C # <> B .C #;

43. query the first two of the best scores for each course

SELECT t1.S # as student ID, t1.C # as course ID, Score as Score

From SC t1

WHERE score IN (select top 2 score

FROM SC

WHERE t1.C # = C #

Order by score DESC

)

Order by t1.C #;

44. count the number of optional students in each course (only when the number of students exceeds 10 ). The course number and number of electives must be output. The query results are sorted in descending order of the number of students. If the number of students is the same, the query results are sorted in ascending order of the course number.

Select C # as course number, count (*) as students

From SC

Group by C #

Order by count (*) desc, c #

45. Search for student IDs that take at least two courses

Select S #

From SC

Group by s #

Having count (*)> = 2

46. query the course number and name of all optional courses for all students

Select C #, Cname

From Course

Where C # in (select c # from SC group by c #)

47. query the names of students who have not completed any course taught by instructor ye Ping.

Select Sname from Student where S # not in (select S # from Course, Teacher, SC where Course. T # = Teacher. T # and SC. C # = course. C # and Tname = 'peiping ');

48. query the student ID and average score of two or more failed courses

Select S #, avg (isnull (score, 0) from SC where S # in (select S # from SC where score <60 group by S # having count (*)> 2) group by S #;

49. Search for students whose scores are less than 60 in the "004" course in descending order.

Select S # from SC where C # = '004 'and score <60 order by score desc;

50. Delete the score of course 001 of "002"

Delete from SC where S # = '001' and C # = '001 ';

Classic SQL statements

I. Basics

1. Description: Create a database

Create database database-name

2. Description: delete a database.

Drop database dbname

3. Description: Back up SQL server

--- Create a device for the backup data

USE master

EXEC sp_addumpdevice 'disk', 'testback', 'c: mssql7backupMyNwind_1.dat'

--- Start backup

Backup database pubs TO testBack

4. Description: Create a new table.

Create table tabname (col1 type1 [not null] [primary key], col2 type2 [not null],...)

Create a new table based on an existing table:

A: create table tab_new like tab_old (use the old table to create A new table)

B: create table tab_new as select col1, col2... From tab_old definition only

5. Description: delete a new table.

Drop table tabname

6. Description: Add a column.

Alter table tabname add column col type

Note: Columns cannot be deleted after they are added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.

7. Description: add a primary key: Alter table tabname add primary key (col)

Delete a primary key: Alter table tabname drop primary key (col)

8. Description: create an index: create [unique] index idxname on tabname (col ....)

Delete index: drop index idxname

Note: The index cannot be changed. To change the index, you must delete it and recreate it.

9. Description: create view viewname as select statement

Delete view: drop view viewname

10. Description: several simple basic SQL statements

Select: select * from table1 where range

Insert: insert into table1 (field1, field2) values (value1, value2)

Delete: delete from table1 where range

Update: update table1 set field1 = value1 where range

Search: select * from table1 where field1 like '% value1 %' --- the like syntax is very subtle, query information!

Sort: select * from table1 order by field1, field2 [desc]

Total: select count as totalcount from table1

Sum: select sum (field1) as sumvalue from table1

Average: select avg (field1) as avgvalue from table1

Max: select max (field1) as maxvalue from table1

Min: select min (field1) as minvalue from table1

11. Description: several advanced query Operators

A: UNION operator