Comprehensive SQL application (1. Create a temporary table for subsequent queries) instance _ (student, course list, and optional table)

I. you need to analyze and query the number of students and passing students of each course in each class. You need to display the query results in the same table, including the class, course number, number of students and number of passing students. implementation steps -- 1. create a temporary table # Number of Students createtable # Number of students (class char (20), course number char (3), number of students int) -- 2. query each class

I. you need to analyze and query the number of students and passing students of each course in each class. You need to display the query results in the same table, including the class, course number, number of students and number of passing students. implementation steps -- 1. create temporary table "# exam count" create table # exam count (class char (20), course number char (3), exam count int) -- 2. query each class

I. analysis required

Query the number of students and passing students of each course. The query results must be displayed in the same table, including the class, course number, number of students and passing students.

II. Implementation steps

-- 1. Create a temporary table "# Number of Students"
Create table # Number of students (
Class char (20 ),
Course No. char (3 ),
Exam count int
)
-- 2. query the number of students of each course in each class and save it in the temporary table "# Number of Students"
Insert into # Number of Students
Select class, course No., COUNT (electives. Student No.) as students
From student inner join electives on student. Student ID = electives. Student ID
Group by class, course No.
-- 3. Create a temporary table "# Pass count"
Create table # Pass count (
Class char (20 ),
Course No. char (3 ),
Pass count int
)
-- 4. query the number of passing students in each course and save them in the temporary table "# Number of passing students"
Insert into # Pass count
Select class, course No., COUNT (electives. Student No.) as pass COUNT
From student inner join electives on student. Student ID = electives. Student ID
Where score> 60
Group by class, course No.
-- 5. connect two temporary tables to the left to obtain the final query result.
Select # Number of students. Class, # Number of students. Course number, number of students, and number of passing students
From # exam Count left join # Pass count on # exam count. Class = # Pass count. Class and # exam count. course No. = # Pass count. course No.

Iii. query results

, Record-free space, virtual host, US space