Lab 1 relational database standard language SQL

[PURPOSE]

In the Microsoft SQL server environment of the given relational database management system, lab students can:

1, ms SQL Server understanding and environment familiar, understand the T-SQL of standard SQL expansion.

2. Master the data definition in the T-SQL environment, including database, basic table, view, index definition.

3, master the T-SQL environment data control including data insertion, deletion, modification.

4. Master the data query and various changes in the T-SQL environment.

5. Understand Data Control in T-SQL environment.

[Lab nature]

Validation experiment

[Experiment content]

Create Database Qixin

Use Qixin

Create Table s
(
Sno char (2 ),
Sname varchar (6) Not null,
Status int,
City varchar (6 ),
Primary Key (SNO)
)

Create Table P
(
PNO char (2 ),
Pname varchar (6) Not null,
Color char (2 ),
Weight int,
Primary Key (PNO)
)

Create Table J
(
Jno char (2 ),
Jname varchar (8) Not null,
City varchar (6 ),
Primary Key (jno)
)

Create Table SPJ
(
Sno char (2 ),
PNO char (2 ),
Jno char (2 ),
Qty int,
Primary Key (SNO, PNO, jno ),
Foreign key (SNO) References S (SNO ),
Foreign key (PNO) References P (PNO ),
Foreign key (jno) References J (jno)
)

/* Create an Index */
Create unique index s_sname on S (sname)
Drop index S. s_sname

Insert into S values ('s1', ' ', 20, 'tianjin ')
Insert into S values ('s2', 'shengsi', 10, 'beijing ')
Insert into S values ('s3', 'Eastern red', 30, 'beijing ')
Insert into S values ('s4 ', 'fengtai sheng', 20, 'tianjin ')
Insert into S values ('s5 ', 'weimin', 30, 'shanghai ')
Go
Insert into P values ('p1', 'Nut ', 'red', 12)
Insert into P values ('p2 ', 'bolt', 'green', 17)
Insert into P values ('p3 ', 'screwdrivers', 'lan', 14)
Insert into P values ('p4 ', 'screwdrivers', 'red', 14)
Insert into P values ('p5 ', 'cam', 'lan', 40)
Insert into P values ('p6 ', 'gears', 'red', 30)
Go
Insert into J values ('j1', 'sanjie', 'beijing ')
Insert into J values ('j2 ', 'faw', 'changchun ')
Insert into J values ('j3', 'spring factory ', 'tianjin ')
Insert into J values ('j4', 'shipyard ', 'tianjin ')
Insert into J values ('j5', 'locomotive factory ', 'tangshan ')
Insert into J values ('j6', 'Radio factory ', 'changzhou ')
Insert into J values ('j7', 'half-guide factory ', 'nanjing ')
Go
Insert into SPJ values ('s1', 'p1', 'j1', 200)
Insert into SPJ values ('s1', 'p1', 'j3', 100)
Insert into SPJ values ('s1', 'p1', 'j4', 700)
Insert into SPJ values ('s1', 'p2', 'j1', 100)
Insert into SPJ values ('s2', 'p3', 'j1', 400)
Insert into SPJ values ('s2', 'p3', 'j1', 200)
Insert into SPJ values ('s2', 'p3', 'j4', 500)
Insert into SPJ values ('s2', 'p3', 'j5', 400)
Insert into SPJ values ('s2 ', 'p5', 'j1', 400)
Insert into SPJ values ('s2 ', 'p5', 'j2 ', 100)
Insert into SPJ values ('s3', 'p1', 'j1', 200)
Insert into SPJ values ('s3', 'p3', 'j1', 200)
Insert into SPJ values ('s4 ', 'p5', 'j1', 100)
Insert into SPJ values ('s4 ', 'p6', 'j3', 300)
Insert into SPJ values ('s4 ', 'p6', 'j4', 200)
Insert into SPJ values ('s5', 'p2', 'j4', 100)
Insert into SPJ values ('s5', 'p3', 'j1', 200)
Insert into SPJ values ('s5', 'p6 ', 'j1', 200)
Insert into SPJ values ('s5', 'p6 ', 'j4', 500)

Create Table student
(
Sno char (9 ),
Sname varchar (10) Not null,
Ssex char (2 ),
Sage int,
Sdept char (2 ),
Primary Key (SNO)
)

Create Table Course
(
CNO char (1 ),
Cname varchar (20) not null,
Cpno char (1 ),
Ccredit smallint,
Primary Key (CNO)
)

Create Table SC
(
Sno char (9 ),
CNO char (1 ),
Grade int,
Primary Key (SNO, CNO)
)

Drop table student
Drop table Course
Drop table SC

Insert into student values ('20170101', 'Li yong', 'male', 20, 'cs ')
Insert into student values ('20170101', 'Liu Chen ', 'female', 19, 'cs ')
Insert into student values ('20170101', 'wang min', 'female ', 18, 'M ')
Insert into student values ('20170101', 'zhang Lil', 'male', 19, 'is ')

Insert into course values ('1', 'database', '5', 4)
Insert into course values ('2', 'mat', '', 2)
Insert into course values ('3', 'information system', '1', 4)
Insert into course values ('4', 'OS', '6', 3)
Insert into course values ('5', 'Data struct', '7', 4)
Insert into course values ('6', 'data', '', 2)
Insert into course values ('7', 'pascal ', '6', 4)

Insert into SC values ('20170101', '1', 92)
Insert into SC values ('20170101', '2', 85)
Insert into SC values ('20170101', '3', 88)
Insert into SC values ('20170101', '2', 90)
Insert into SC values ('20170101', '3', 80)

--- Query the names of students who have selected all courses
Select sname from student where not exists
(Select * from course where not exists
(Select * from SC where SnO = student. SnO and CNO = course. CNO ))
--- Query the student numbers of all courses selected at least 200215122
Select distinct SnO from SC SCX where not exists
(Select * from SC SCY where SCY. Sno = '2013' and not exists
(Select * from SC SCZ where SCZ. Sno = SCX. SnO and SCZ. CNO = SCY. CNO ))

--- Query students of the Computer Science Department and students not older than 19
Select * from student where sdept = 'cs 'intersect select * from student where sage <= 19
--- Query the intersection between students in the Computer Science Department and students not older than 19
Select * from student where sdept = 'cs 'intersect select * from student where sage <= 19
--- Query the difference between students in the Computer Science Department and students not older than 19
Select * from student where sdept = 'cs 'Then T select * from student where sage <= 19

--- Calculate the average score of the students for each department and store the results to the database.
Create Table dept_age (sdept char (15), avg_age smallint)
Insert into dept_age (sdept, avg_age) Select sdept, AVG (SAGE) from student group by sdept
Select * From dept_age

--- Create a view for students who have taken course 1 in the Information Department
Create view is_s1 (SNO, sname, grade)
As
Select student. Sno, sname, grade from student, SC
Where sdept = 'is 'and student. Sno = SC. SnO and SC. CNO = '1'

Create view is_s2
As
Select SnO, sname, grade
From is_s1
Where grade> = 90

-- P127 page 4th
--- (1) Request the supplier number SnO for J1 parts of the project:
Select distinct SnO from SPJ where jno = 'j1'
--- (2) request the supplier number SnO for the J1 part P1 of the project:
Select distinct SnO from SPJ where jno = 'j1' and PNO = 'p1'
--- (3) request the supplier number SnO whose J1 part is Red:
Select distinct SnO from SPJ, P where SPJ. PNO = P. PNO and jno = 'j1' and color = 'red'
Bytes ----------------------------------------------------------------------------------------------------------------
Select distinct SnO from SPJ where jno = 'j1' and PNO in (select PNO from P where color = 'red ')
Bytes ----------------------------------------------------------------------------------------------------------------
Select distinct SnO from SPJ join P on SPJ. PNO = P. PNO where jno = 'j1' and color = 'red'
--- (4) do not use the project No. jno of the red parts produced by the Tianjin Supplier:
Select distinct jno from SPJ where jno not in
(Select jno from SPJ, P, S where SPJ. PNO = P. PNO and SPJ. Sno = S. SnO and city = 'tianjin 'and color = 'red ')
Bytes ----------------------------------------------------------------------------------------------------------------
Select distinct jno from SPJ where jno not in
(Select SPJ. jno from S join SPJ on S. sno = SPJ. sno join P on p. PNO = SPJ. PNO where S. city = 'tianjin 'and P. color = 'red ')
Bytes ----------------------------------------------------------------------------------------------------------------
Select distinct jno from SPJ where jno not in
(Select jno from SPJ where SnO IN (select SnO from s where city = 'tianjin ') and PNO in (select PNO from P where color = 'red '))
Bytes ----------------------------------------------------------------------------------------------------------------
--- The following statement is incorrect. Analyze it yourself:
Select distinct jno from P join SPJ on SPJ. PNO = P. PNO join S on SPJ. Sno = S. Sno where color = 'red' and S. City! = 'Tianjin'
Bytes ----------------------------------------------------------------------------------------------------------------
--- (5) request to use at least the jno of all parts supplied by the supplier S1:
Select distinct jno from SPJ spjx
Where not exists (
Select * From SPJ spjy where SnO = 's1' and not exists (
Select * From SPJ spjz where spjx. jno = spjz. jno and spjz. PNO = spjy. PNO ))
Bytes ----------------------------------------------------------------------------------------------------------------
Select distinct jno from SPJ spjx
Where not exists (
Select * From SPJ where SnO = 's1' and PNO not in (
Select PNO from SPJ spjz where spjz. jno = spjx. jno ))
Bytes ----------------------------------------------------------------------------------------------------------------

-- (1) identify the names and cities of all suppliers.
Select sname, city from S
-- (2) identify the name, color, and weight of all parts.
Select pname, color, weight from P
-- (3) Find the engineering Number of the parts supplied by supplier S1.
Select jno from SPJ where SnO = 's1'
-- (4) identify the names and quantity of various parts used by project J2.
-- Method 1:
Select pname, sum (qty) as sumqty from P join SPJ on p. PNO = SPJ. PNO where jno = 'j1' group by pname
-- Method 2:
Select pname, sum (qty) as sumqty from SPJ, P where SPJ. PNO = P. PNO and jno = 'j2 'group by pname
-- Method 3:
Select pname, sumqty from P,
(Select PNO, sum (qty) as sumqty from SPJ where jno = 'j2 'group by PNO) j2sumqty
Where p. PNO = j2sumqty. PNO
-- (5) Find all part numbers provided by the Shanghai manufacturer.
Select distinct PNO from SPJ where SnO IN (select SnO from s where city = 'shanghai ')
Select distinct PNO from S, SPJ where S. Sno = SPJ. SnO and city = 'shanghai'
Select distinct PNO from S join SPJ on S. Sno = SPJ. Sno where city = 'shanghai'
Select distinct PNO from SPJ where exists (select * from s where SnO = SPJ. SnO and city = 'shanghai ')
-- (6) Find the engineering name of the parts produced in Shanghai.
Select jname from J Where jno in (select jno from SPJ where SnO IN (select SnO from s where city = 'shanghai '))
Select distinct jname from S, J, SPJ where S. Sno = SPJ. SnO and J. jno = SPJ. jno and S. City = 'shanghai'
-- (7) Find the engineering Number of the parts not produced in Tianjin.
Select jno from SPJ where jno not in (select jno from SPJ where SnO IN (select SnO from s where city = 'tianjin '))
Bytes -------------------------------------------------------------------------------------------------------------
Select jno from SPJ spjx where not exists
(Select * from S, SPJ where S. Sno = SPJ. SnO and S. City = 'tianjin 'and SPJ. jno = spjx. jno)
Bytes -------------------------------------------------------------------------------------------------------------
Select jno from SPJ spjx where not exists
(Select * From SPJ spjy where spjy. jno = spjx. jno and SnO IN (select SnO from s where city = 'tianjin '))
Bytes -------------------------------------------------------------------------------------------------------------
-- Error: Select distinct jno from SPJ where SnO not in (select SnO from s where city = 'tianjin ')
-- (8) change the color of all red parts to blue.
Update p set color = 'blue' where color = 'red'
-- (9) switch from S5 to P6 for J4 to S3.
Update SPJ set SnO = 's3' where SnO = 's5 'and PNO = 'p6' and jno = 'j4'
-- (10) Delete the record whose supplier number is S2 from the supplier relationship and delete the corresponding record from the supply relationship.
Delete from SPJ where SnO = 's2'
Delete from s where SnO = 's2'
-- (11) insert (S2, J6, P4, 200) into the supply relationship.
Insert into SPJ values ('s2', 'p4 ', 'j6', 200)

-- 11th questions. Create a supply view for the three projects, including supplier code SnO, part code PNO, and supply quantity qty.
Create view v_sj
Select SnO, PNO, sum (qty) as qty from SPJ where jno in (select jno from J Where jname = 'three') group by SnO, PNO
-- (1) Find out the various part codes used by the three construction projects and their quantity
Select PNO, sum (qty) as qtynum from v_spj group by PNO
-- (2) identify the supply of vendor S1
Select * From v_spj where SnO = 's1'

Bytes ----------------------------------------------------------------------------------------------------------------
/* Lab assignments
(1) query the part numbers, names, and usage of each project.
(2) query the project names of two or more different parts.
(3) query the project number with the largest total number of parts used.
(4) query the project number that uses the most part types.
*/
-- 1. query the part number, name, and usage of each project.
-- Part numbers and usage for each project
Select jno, PNO, sum (qty) as sumqty from SPJ group by jno, PNO
-- Maximum usage of each project
Select jno, max (sumqty) as maxsumqty from (
Select jno, PNO, sum (qty) as sumqty from SPJ group by jno, PNO) spjsumqty2
Group by jno
-- Compare the two
Select jno, P. PNO, pname, maxsumqty from P,
(Select spjsumqty1.jno, PNO, maxsumqty from (
Select jno, PNO, sum (qty) as sumqty from SPJ group by jno, PNO) spjsumqty1,
(Select jno, max (sumqty) as maxsumqty from (
Select jno, PNO, sum (qty) as sumqty from SPJ group by jno, PNO) spjsumqty2
Group by jno) spjmaxsumqty
Where spjsumqty1.jno = spjmaxsumqty. jno and spjsumqty1.sumqty = spjmaxsumqty. maxsumqty) spjpnomaxsumqty
Where p. PNO = spjpnomaxsumqty. PNO
Order by jno, P. PNO
-- 2 query the names of projects with two or more different parts.
Select distinct jname from J Where jno in (
Select jno from SPJ group by jno having count (distinct PNO)> = 2)
Select distinct jname from J,
(Select jno from SPJ group by jno having count (distinct PNO)> = 2) spjcount
Where J. jno = spjcount. jno
-- 3 query the project number with the largest part quantity
-- Total number of parts used in each project
Select jno, sum (qty) as sumqty from SPJ group by jno
-- Maximum number of parts used
Select max (sumqty) from (select jno, sum (qty) as sumqty from SPJ group by jno)
-- Compare the two
Select distinct jno from (
Select jno, sum (qty) as sumqty from SPJ group by jno) spjqty1
Where sumqty = (
Select max (sumqty) from (
Select jno, sum (qty) as sumqty from SPJ group by jno) spjqty2)
-- 4 query the project number with the most part types used
-- Number of parts used in each project
Select jno, count (distinct PNO) as countpno from SPJ group by jno
-- Maximum number of parts used
Select max (countpno) from (select jno, count (distinct PNO) as countpno from SPJ group by jno
-- Compare the two
Select distinct jno from (
Select jno, count (distinct PNO) as countpno from SPJ group by jno) spjcount1
Where countpno = (
Select max (countpno) from (
Select jno, count (distinct PNO) as countpno from SPJ group by jno) spjcount2)