Database machine test (III)

This is the third time on the machine content and results. This program is based on the table created during the second on the machine. All the complete code is stored in the code library of our space and can be directly run. Experiment 5 Use of SELECT statements (ii) I. Practice objective to master the nested use method of SELECT statements, which is suitable for SQL Server to perform complex queries on tables. II,

This is the third time on the machine content and results. This program is based on the table created during the second on the machine. All the complete code is stored in the code library of our space and can be directly run. Experiment 5 Use of SELECT statements (ii) I. Practice objective to master the nested use method of SELECT statements, which can be applied to SQL Server to perform complex queries on tables. II,

This is the third time on the machine content and results. This program is based on the table created during the second on the machine. All the complete code is stored in the code library of our space and can be directly run.

Use of the SELECT statement in Experiment 5 (2)

I. Internship Purpose

Familiar with the nested usage of SELECT statements, and suitable for SQL Server to perform complex queries on tables.

Ii. Preparation for internship

1. Review the usage of SELECT statements in more advanced formats.

2. Understand the rules used by the set function in grouping query.

Iii. experiment content

Use nested queries and table connection queries to complete the following query statements respectively:

(1) Find out the student information in the same class as LI Yong.

(2) find information about all students having the same electives as LI Yong.

(3) Find the student information between the ages of LI Yong and 25.

(4) Find the student ID and name that has taken the course operating system.

(5) find out the names of all students not taking course 1.

(6) find out the names of students who have chosen all courses.

Complete the following query:

(1) query the student ID and score of course 3, and sort them in descending order.

(2) query information of all students. The query results must be sorted in ascending order by class number, and students in the same class are sorted by age in descending order.

(3) calculate the number of students corresponding to each course number.

(4) query the student ID that has taken more than three courses.

Iv. Content of the internship report

1. Write the SQL statement for the preceding operation.

2. When the nested query with the [NOT] EXISTS keyword is used, when will the WHERE condition of the outer query be true and when is false?

3. Which of the following statements can be used for connection query and nested query? Why?

Machine lab 6 SQL storage operations

I. Internship Purpose

Master the use of interactive SQL statements to store existing basic tables: Modify, delete, insert, and deepen understanding of data integrity.

Ii. Preparation for internship

1. Review data integrity. ensure data consistency during data modification, deletion, and insertion.

2. Review the use of UPDATE, DELETE, INSERT statements and subqueries.

Iii. Internship content

Complete the following query statements:

(1) calculate the average age of the students in each class and store the results in the database.

(2) set the score of all the students in the 01311 class to zero.

(3) Delete Course Selection records for all students in the 2001-level computer software class.

(4) The student LI Yong dropped out of school and deleted his records from the database.

Iv. Content of the internship report

1. Write the SQL statement for the preceding operation.

2. What are the essential differences between the DROP and DELETE commands?

Run the Code:

*********************** */
/** Find the student information of the same class as LI Yong **/
SELECT Sno 'student ID ', sname' name', Ssex 'gender ', Sage 'age', clno' class'
FROM Student
WHERE Clno =
(SELECT Clno
FROM Student
WHERE Sname = 'Li Yong ');

/** Find out all student information that has the same electives as LI Yong **/
SELECT *
FROM Student
WHERE Sno IN
(SELECT Sno
FROM Grade
WHERE Cno IN
(SELECT Cno
FROM Grade
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Sname = 'Li Yong ')));

/** Find the student information between the ages of LI Yong and 25 **/
SELECT *
FROM Student
WHERE Sage
(SELECT Sage
FROM Student
WHERE Sname = 'Li Yong ') AND 25;


/** Find out the names of all students not taking course 1 **/
SELECT Sname 'name'
FROM Student
WHERE Sno NOT IN
(SELECT Sno
FROM Grade
WHERE Cno = '1 ');

/** Find out the names of students who have selected all courses **/
SELECT Sname
FROM Student
Where not exists (SELECT *
FROM Course
Where not exists (SELECT *
FROM Grade
WHERE Student. Sno = Grade. Sno and Course. Cno = Grade. Cno ));

/** Query the student ID and score of course 3 selected, and sort them in descending order **/
SELECT Sno 'student ID ', gmark' score'
FROM Grade
WHERE Cno = '3' order by Gmark DESC;


/** Query the information of all students. The query results must be sorted by class number in ascending order, and students in the same class are sorted by age in descending order **/
SELECT *
FROM Student
Order by Clno, Sage DESC;


/** Calculate the number of students corresponding to each course number **/
SELECT Cno 'course No. ', COUNT (*) 'course selections'
FROM Grade
Group by Cno;


/** Query the student ID that has taken more than three courses **/
SELECT Sno
FROM Grade
Group by Sno having count (*)> 3;


***************/
/** Calculate the average age of the students in each class and store the results to the database **/
/* Method 1 create a new database */
Drop table Savage;
Create table Savage (
Clno Char (5) not null,
Avage Smallint
);
Insert into Savage (Clno, Avage)
SELECT Clno, AVG (Sage)
FROM Student
Group by Clno;
/* The second method creates an attribute column in the Class table * // * always returns an error, where is the error... */
/* Alter table Class add Avage Smallint;
UPDATE Class
SET Avage = AVG (Sage)
FROM Class, Student
Group by Clno HAVING Student. Clno = Class. Clno ;*/

/** Set the score of all students in the 01311 class to zero **/
UPDATE Grade
SET Gmark = 0
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Clno = '000000 ');


/** Delete Course Selection records for all students in the 2001-level computer software class **/
DELETE
FROM Grade
WHERE Sno IN
(SELECT Sno
FROM Student
WHERE Clno = (
SELECT Clno
FROM Class
WHERE Speciality = 'computer soft' AND Inyear = '20140901 '));


/** The student LI Yong has dropped out of school and deleted his records from the database **/
/* The affected table is Class Grade Student (the newly created Savage table is not considered )*/
UPDATE Class
SET Number = Number-1
WHERE Clno =
(SELECT Clno
FROM Student
WHERE Sname = 'Li Yong ');
DELETE
FROM Grade
WHERE Sno =
(SELECT Sno
FROM Student
WHERE Sname = 'Li Yong ');
DELETE
FROM Student
WHERE Sname = 'Li yong ';