SQL Server 数据库——第三章课后题

2023-11-03

习题

3.SQL表达式
4.SQL语句建立第2章习题6中4个表
5.针对习题4中的4个表试用SQL完成以下各项操作
9.请为三建工程项目建立一个供应情况的视图。
心得

3.SQL表达式

在这里插入图片描述

SELECT * FROM S WHERE A=10;

在这里插入图片描述

SELECT A,B FROM S

在这里插入图片描述

SELECT A,B,S.C,S.D,E,F
FROM S,C
WHERE S.C=T.C,S.D=T.D

在这里插入图片描述

SELECT *
FROM S,C
WHERE S.C=T.C

在这里插入图片描述

SELECT *
FROM S,C
WHERE A<E

在这里插入图片描述

SELECT S.C,S.D,T.C,T.D,T.E,T.F
FROM S,T

4.SQL语句建立第2章习题6中4个表

DROP TABLE IF EXISTS S
DROP TABLE IF EXISTS P
DROP TABLE IF EXISTS J
DROP TABLE IF EXISTS SPJ
CREATE TABLE S        
 (	
 SNO CHAR(10),                  
 SNAME CHAR(10),
 STATUS INT,
 CITY CHAR(10)
 ); 
CREATE TABLE P
 (	
 PNO CHAR(10),
 PNAME CHAR(10),
 COLOR CHAR(10),
 WEIGHT INT
 ); 
 CREATE TABLE J
 (	
 JNO CHAR(10),
 JNAME CHAR(10),
 CITY CHAR(10)
 ); 
  CREATE TABLE SPJ
 (	
 SNO CHAR(10),
 PNO CHAR(10),
 JNO CHAR(10),
 QTY INT
 );

插入数据

--S表
INSERT INTO S VALUES('S1','精益',20,'天津') ;
INSERT INTO S VALUES('S2','盛锡',10,'北京') ;
INSERT INTO S VALUES('S3','东方红',30,'北京') ;
INSERT INTO S VALUES('S4','丰泰盛',20,'天津') ;
INSERT INTO S VALUES('S5','为民',20,'上海') ;
--P表
INSERT INTO P VALUES('P1','螺母','红',12) ;
INSERT INTO P VALUES('P2','螺栓','绿',17) ;
INSERT INTO P VALUES('P3','螺丝刀','蓝',14) ;
INSERT INTO P VALUES('P4','螺丝刀','红',14) ;
INSERT INTO P VALUES('P5','凸轮','蓝',40) 
INSERT INTO P VALUES('P6','齿轮','红',30) ;
--J表
INSERT INTO J VALUES('J1','三建','北京') ;
INSERT INTO J VALUES('J2','一汽','长春') ;
INSERT INTO J VALUES('J3','弹簧厂','天津') ;
INSERT INTO J VALUES('J4','造船厂','天津') ;
INSERT INTO J VALUES('J5','机车厂','唐山') ;
INSERT INTO J VALUES('J6','无线电厂','常州') ;
INSERT INTO J VALUES('J7','半导体厂','南京') ;
--SPJ
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','J2',100) ;
INSERT INTO SPJ VALUES('S2','P3','J1',400) ;
INSERT INTO SPJ VALUES('S2','P3','J2',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','J2',200) ;
INSERT INTO SPJ VALUES('S5','P6','J4',500) ;

（1）求供应工程J1零件的供应商号码SNO

SELECT SNO
FROM SPJ
WHERE JNO='J1'

在这里插入图片描述
（2）求供应工程J1零件P1的供应商号码

SELECT SNO
FROM SPJ
WHERE JNO='J1'AND PNO='P1'

在这里插入图片描述
（3）求供应工程J1零件为红色的供应商号码SNO

SELECT SNO
FROM SPJ,P
WHERE SPJ.PNO=P.PNO AND JNO='J1'AND COLOR='红'

在这里插入图片描述
（4）求没有使用天津供应商生产的红色零件色工程号JNO

SELECT JNO 
FROM J
WHERE JNO NOT IN(
	SELECT JNO
	FROM SPJ,P,S
	WHERE SPJ.PNO=P.PNO AND COLOR='红' 
	AND SPJ.SNO=S.SNO AND S.CITY='天津');

（5）求至少用了供应商S1所供应的全部零件的工程号JNO

-- ①查询s1供应商的零件号
SELECT DISTINCT PNO
FROM SPJ
WHERE SNO='S1';
-- ②查询哪一工程既使用S1又使用S2
SELECT JNO
FROM SPJ
WHERE PNO='P1' AND JNO IN(
						  SELECT JNO
						  FROM SPJ
						  WHERE PNO='P2');

在这里插入图片描述

5.针对习题4中的4个表试用SQL完成以下各项操作

（1）找出所有供应商姓名和所在城市

SELECT SNAME,CITY FROM S

在这里插入图片描述
（2）找出所有零件的，名称、颜色、重量

SELECT PNAME,COLOR,WEIGHT FROM P

在这里插入图片描述

（3）找出使用供应商S1所供应零件的工程号码

SELECT JNO
FROM J
WHERE JNO IN(
			SELECT JNO
			FROM SPJ
			WHERE SNO='S1')

在这里插入图片描述
（4）找出工程项目J2所使用的各种零件的名称及质量

SELECT PNAME,WEIGHT
FROM P
WHERE PNO IN(
			SELECT PNO
			FROM SPJ
			WHERE JNO='J2')

在这里插入图片描述
（5）找出上海厂商供应的所有零件号码

SELECT PNO
FROM P
WHERE PNO IN(
			SELECT PNO
			FROM SPJ,S
			WHERE SPJ.SNO=S.SNO AND CITY='上海')

在这里插入图片描述
（6）找出使用上海厂商的零件的工程名称

SELECT JNAME
FROM J
WHERE JNO IN(
			SELECT JNO
			FROM SPJ,S
			WHERE SPJ.SNO=S.SNO AND CITY='上海')

在这里插入图片描述
（7）找出没有使用天津产的零件的工程号码

SELECT JNO
FROM J
WHERE JNO NOT IN(
			SELECT JNO
			FROM SPJ,S
			WHERE SPJ.SNO=S.SNO AND CITY='天津')

在这里插入图片描述
（8）把全部红色零件的颜色改成蓝色

SELECT * FROM P
UPDATE P
SET COLOR='蓝'
WHERE COLOR='红'
SELECT * FROM P;

在这里插入图片描述
（9）由S5供给J4的零件P6改为由S3供应，请坐必要的修改

SELECT *FROM SPJ
WHERE JNO='J4';

UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND JNO='J4' AND PNO='P6'

SELECT *FROM SPJ
WHERE JNO='J4';

在这里插入图片描述
（10）从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录

DELETE FROM S WHERE SNO='S2'
DELETE FROM SPJ WHERE SNO='S2'

（11）请将（S2，J6，P4，200）插入供应请情况关系。

INSERT 
INTO SPJ
VALUES('S2','J6','P4',200)

在这里插入图片描述

9.请为三建工程项目建立一个供应情况的视图。

包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）

CREATE VIEW V_SPJ
AS
SELECT SNO,PNO,QTY
FROM SPJ
WHERE JNO IN(
			SELECT JNO
			FROM J
			WHERE JNAME='三建');

在这里插入图片描述
基于上面的视图完成以下查询：
(1）找出三建工程项目使用的各种零件代码及其数量

SELECT PNO,QTY
FROM V_SPJ

在这里插入图片描述
(2）找出供应商S1的供应情况

SELECT *
FROM V_SPJ
WHERE SNO='S1'

在这里插入图片描述

心得

第四题最后一问百度的，我觉得我依然不能很好的用SQL语句表达除以。
最近作业多，课也多，还有竞赛，都没时间静下心来好好钻研。。。。每天只是机械的完成任务，没有什么乐趣。有时间一定要回来思考思考

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

数据库实验课报告