Introduction to database (Experiment 4 1) Data Query

[Purpose of the experiment] 1. Master the application of single table query 2. Master the application of connection query 3. Master nested query and set Query [experiment content] 1. Based on experiment 2, create a SC database (experimental conditions), including student, course, and SC tables 2. Data Query Experiment 1. Single Table query 1) Select the specified column [Example] to query all

[Purpose of the experiment] 1. Master the application of single table query 2. Master the application of connection query 3. Master nested query and set Query [experiment content] 1. Based on experiment 2, create a SC database (experimental conditions), including student, course, and SC tables 2. Data Query Experiment 1. Single Table query 1) Select the specified column [Example] to query all

[Lab Objectives]

1. Master the application of single table Query

2. Familiar with connection query applications

3. Master nested queries and set queries

[Lab content]

1. Create a SC database (Experimental Conditions) based on experiment 2, which contains three tables: student, course, and SC.

Ii. Data Query Experiment

1. Single Table query

1) Select the specified Column

[Example] query the student ID and name of all students.

2) query all columns

[Example] query detailed information of all students

3) Name the specified column after Query

[Example] query the "name" and "Birth Year" columns of all students.

4) Eliminate rows with duplicate values

[Example] query the student ID of an optional course

5) select several tuples in the table (matching)

(I) size comparison

[Example] query the list of all students in the computer department (IS)

[Example] query the names and ages of all students under the age of 20.

(II) Scope of Determination

[Example] query the names, department, and age of all students between the ages of 20 and 23.

(III) in and not in determine the set

[Example] query the names and gender of all students in IS and CS

[Example] query the name and age of a student in the IS or MA series.

(IV) character matching (like % _)

[Example] query the names and gender of all students surnamed Li.

[Example] query the student ID, name, and Department of all students enrolled in the "2002" year.

[Example] query information of all students without the surname "Liu"

[Example] query the course number, course name, and credits whose names contain "data ".

(V) query involving null values (is null)

[Example] query the course No. and Course name that have not been preemptible.

[Example] query the student ID, course number, and score of all students with scores

6) Sort query results (order)

[Example] the student ID and score of course 3 are queried, and the results are sorted in descending order.

Note:

Sort is to sort the query results. So it should be placed at the end.

7) Aggregate functions

Count, sum, avg, max, min

[Example] query the total number of students

[Example] query the total credits of all courses

[Example] query the average age of all students

[Example] query the highest score of course 1

8) group statistics)

[Example] query the number of students for each sex.

[Example] query the course number and average score of each course.

[Example] query the student ID and number of optional courses for more than three courses.

[Example] query the number of optional courses, student ID and number of optional courses that have been selected for more than two courses (including two courses, but not for course 1.

[Example] query the number of students who fail to pass more than two courses.

[Example] query the number and number of electives selected by more than two (including two) students.

Note: 1. Target columns of grouping query can contain grouping columns and other columns contained in clustering functions;

2. The where condition is used for the selection condition before grouping. The having clause is used for the selection condition after grouping.

2. Connection Query

1) equi and non-equi join query

[Example] query the status of each student and their Optional Courses

2) Self-connection

[Example] query indirect electives of each course

3) External Connection

[Example] query the electives of all students (including students without electives)

[Example] query the electives of all students (including students with no electives, attributes include student IDs, names, course names, and scores)

4) Qualified connections

[Example] query the student ID and name of all students who have taken course 2 and whose score is higher than 90

[Example] query the student ID, name, elective course name, and score.

3. nested Query

1) subquery with IN predicates (attribute in (subquery result ))

[Example] query the student information in the same department as Wang Min.

[Example] query student information that is not in the same department as Wang Min.

[Example] query the student ID and name for the course "Information System.

[Example] query the student ID and name that used to attend classes with Liu Chen. (Assume that one course has only one class)

2) subqueries with comparison operators (=, >=, <=, <> or! =)

[Example] query information of all students in the same department as Wang Min (= judgment)

[Example] query the number of courses that each student has exceeded the limit of the course. (Similar courses are not classified)

[Example] query the course number of each student that exceeds the average score of the selected course.

[Example] query the course number of each student that exceeds the average score of the course.

3) subqueries with ANY or ALL predicates

[Example] query the names, gender, age, and Department of a student younger than a computer student in other systems.

[Example] query the names and ages of students younger than all children in other computer systems.

4) subqueries with Exists predicates

[Example] query the names of all students taking course 1.

[Example] query the names of students who have selected all courses.

[Example] query the student ID that takes at least 200215122 of all optional courses.

4. Set Query

1) UNION

[Example] query the detailed information of students in the computer department and students not older than 19 years old.

[Example] query the details of students who have taken course 1 and are not 19 years old.

2) INTERSECT

[Example] query the intersection of details of students not older than 19 years old who have taken course 1.

3) Difference between T

[Example] query the detailed information difference between students of the Computer Science Department and students not older than 19 years old.

[Homework after class]

1. Use the SPJ database to complete the SPJ query exercises after class.

2. Complete the following query using SQL statements based on the SPJ database.

1) query the supplier details of "Tianjin.

2) query the supplier code that is not "Tianjin.

3) query the supplier code for J1 parts of the supplied project.

4) query the product code that has been supplied by S1 of the supplier.

5) query the types of parts that the supplier S1 supplies for project J1.

6) query the part code and quantity of various parts that supplier S1 supplies for project J1.

7) There are several types of parts used in the J1 project.

8) query the various part codes used by the J1 project and their quantity and quantity.

9) query the part code and quantity of parts with the total number of parts used in the J1 project greater than 300.

10) query the parts used in the J1 project. The quantity must at least be greater than the quantity and, part code and quantity of all parts supplied by S2 used by J3.

11) query the various parts used by the J1 project. The quantity must be at least greater than the similar quantity and part code and quantity used by J3.

12) query the project number of a project that uses at least several types of parts used in the J1 project.

13) the query uses at least the engineering numbers of all red parts.

14) query the engineering numbers of all red parts.

15) query the project number and supplier Number of the same city as the supplier that supplies the parts of the project.

16) query the project No. JNO of the red parts not produced by the Tianjin supplier.

17) the query uses at least the project No. JNO of all parts supplied by supplier S1.

18) query the project No. JNO of all parts made in Shanghai.

19) query the project No. JNO of all parts used by J2.

3. Use SQL statements to complete the following tasks based on the SC database.

1) Increase the score of each failed student by 5 points.

2) scores of students lower than the average score of the course are improved by 10%.

3) increase the number of students in the Computer Science Department by one year.

4) Increase the scores of courses with credits of less than 3 points (including 3 points) by 3 points, and those with credits of more than 3 points by 4 points.

5) create a view named "transcript". The attributes include student ID, Student name, course number, course name, and score.

6) Delete course 1.