The original SQL statement was not well written. In recent years, the database has been used less, and the SQL statement has become obsolete. I recently reviewed SQL statements. There are many online examples, but I only provided SQL examples, no matching data for self-practice and debugging
The original SQL statement was not well written. In recent years, the database has been used less, and the SQL statement has become obsolete. Recently, I have reviewed SQL statements. There are many examples on the Internet, but I only provide SQL examples, no matching data for self-practice and debugging
I think that I have tried to write SQL, and debugging is still helpful. When I read the SQL examples, I seem to have understood it. If I write it myself, I don't have to think about it correctly. The debugging works and the writing is concise.
I followed the example of the popular online course selection table for students to review:
This text has been reprinted on the Internet, and some SQL statements are suitable for application systems. Some "reports" are more important, mainly to review the former. The first 20 items are generally quite good, and the last 30 items are obviously biased towards the report style. In addition, the following examples are mixed and 12 examples are selected for exercises (in fact, many syntax, case, any/all, union, and so on are not included. Use the mysql database and share the data created by yourself. For these 12 SQL statements, the original article has been corrected.
SQL is a basic skill. If you can write well, you can continue to practice it. We do not advocate efforts to write complex SQL statements to solve business problems. If complicated SQL statements exist in the application system, the business logic is often exposed to the SQL layer, which is not conducive to maintenance and expansion. Although this can often improve the running efficiency. You can choose the specific situation.
The following examples are all common SQL statements. In fact, you need to learn a lot about specific databases. For example, the decode function, rowid, rownum, and connect by of oracle db are not universal, but it is very practical.
Data can be downloaded here, but it is used as an exercise without any foreign key Association:
The compiled SQL statement is as follows:
Student (S #, Sname, Sage, Ssex) Student table
Course (C #, Cname, T #) Curriculum
SC (S #, C #, score) Orders table
Teacher (T #, Tname) Instructor table
1. Select the student ID that passes each course
Select distinct's # 'from SC where's #' not in (select's # 'from SC where score <60)
2. query the student IDs of all students whose scores are higher than those of the "1" course;
SELECT c01.'s # 'from (select's #', 'score 'from SC where 'C #' = 1) c01,
(Select's # ', 'score' from SC where 'C # '= 2) c02
Where c01.'s # '= c02.'s #' and c01.score> c02.score
3. 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
4. query the student ID, name, number of course selections, and total score of all students;
Select student.'s # ', student. 'sname', count ('C # '), sum (score) from student left outer join SC on student.'s # '= SC.'s # 'group by student.'s # ', SC.'s #'
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 student.'s # 'not in (select distinct (SC.'s # ') from teacher, course, SC where Tname = 'peiping' and teacher. 't#' = course. 't#' and SC. 'C # '= course. 'C #')
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 # '= 1 and exists (select * from SC SC _2 where SC _2. 'C # '= 2 and SC.'s # '= SC _2.'s #')
7. query the student ID and name (clever) of all students who have learned the courses taught by instructor ye Ping)
Select's # ', sname from student where's #' in
(Select's # 'from SC, teacher, course where tname = 'peiping' and teacher. 't#' = course. 't#' and course. 'C # '= SC. 'C # 'group by's # 'having count (SC. 'C # ') =
(Select count ('C # ') from teacher, course where tname = 'Ye ping' and teacher. 't#' = course.'t #'))
8. query the student ID and name (Representative) of all students whose score is lower than the course number 001)
Select's # ', sname from (select student.'s # ', student. sname, score, (select score from SC SC _2 where student.'s # '= SC _2.'s # 'and SC _2. 'C # '= 2) score2 from student, SC where
SC.'s # '= student.'s #' and SC. 'C # '= 1) s_2 where score2 <score
9. query the student ID and name of the student who has not completed all the courses
Select student.'s # ', Sname from student, SC where student.'s # '= SC.'s # 'group by's # ', sname having count ('C #') <(select count ('C # ') from course)
10. query the student ID and name of at least one course with the student ID "002;
Select distinct (SC.'s # '), sname from student, SC where student.'s # '= SC.'s # 'and 'C #' in (select 'C # 'from SC where's #' = 002)
11. Change the average score of the Course taught by instructor ye Ping in the SC table;
Update SC inner join
(Select sc2. 'C # ', avg (sc2.score) score from SC sc2, teacher, course where
Sc2. 'C # '= course. 'C # 'and tname = 'peiping' and teacher. 't#' = course. 't#' and course. 'C # '= sc2. 'C #' group by course. 'C # ') sc3 on SC. 'C # '= sc3. 'C #' set SC. score = sc3.score
12. query the student ID of all the students who have learned the course No. 2. (Note: the first condition of the where statement filters out records that do not meet c #, and then group,)
Select's # 'from SC where 'C #' in (select 'C # 'from SC where's #' = 2)
Group by's # 'having count (*) = (select count (*) from SC where's # '= 2 );
Author in jianghu
