作业5 SQL查询（1）

1.设有四个关系（只示意性给出一条记录）：

 

S（SNO，SNAME， ADDRESS，TEL）其中，SNO：供应商代码 SNAME：姓名 ADDRESS：地址 TEL：电话；

J（JNO，JNAME，LEADER，BG）其中，JNO：工程代码 JNAME：工程名 LEADER：负责人 BG：预算；

P（PNO，PNAME，SPEC，CITY，COLOR）其中，PNO：零件代码 PNAME：零件名 SPEC：规格 CITY：产地 COLOR：颜色；

SPJ（SNO，PNO，JNO，QTY）其中，SNO：供应商代码 JNO：工程代码 PNO：零件代码 QTY：数量；

（1）为每个关系建立相应的表结构，根据示例添加若干记录。

说明：建表时得注明属性列字段名、类型等，同时注明是否允许为空，设置好主键和外键

参考答案：

CREATE TABLE S(SNO VARCHAR(5) NOT NULL PRIMARY KEY,

                 SNAME VARCHAR(20) NOT NULL,

                 ADDRESS NVARCHAR(30),

                 TEL VARCHAR(20));

INSERT INTO S VALUES('S1', 'SN1', '上海南京路', '68564345');

……

CREATE TABLE P(PNO VARCHAR(5) NOT NULL PRIMARY KEY,

                 PNAME VARCHAR(20) NOT NULL,

                 SPEC VARCHAR(20),

                 CITY NVARCHAR(20),

                 COLOR NVARCHAR(2));

INSERT INTO P VALUES('P1', 'PN1', '8x8', '无锡', '红');

……

CREATE TABLE  J(JNO VARCHAR(5) NOT NULL PRIMARY KEY,

                 JNAME VARCHAR(20) NOT NULL,

                 LEADER NVARCHAR(10),

                 BG INT);

INSERT INTO J VALUES('J1', 'JN1', '王总',10);

……

CREATE TABLE  SPJ

 (SNO VARCHAR(5) NOT NULL CONSTRAINT S_F FOREIGN KEY REFERENCES S(SNO),

PNO VARCHAR(5) NOT NULL CONSTRAINT P_F FOREIGN KEY REFERENCES P(PNO),

JNO VARCHAR(5) NOT NULL CONSTRAINT J_F FOREIGN KEY REFERENCES J(JNO),

QTY INT,

CONSTRAINT PR_SPJ PRIMARY KEY(SNO,PNO,JNO));

INSERT INTO SPJ VALUES('S1', 'P1', 'J1', 200);

……

2.

设有学生-课程关系数据库，它由三个关系组成，它们的模式是：学生S（学号SNO，姓名SN，所在系DEPT，年龄AGE）、课程C（课程号CNO，课程名CN，先修课号CPNO）、SC（学号SNO，课程号CNO，成绩G）。

请用SQL语句写出下列查询：

 （1）检索学生的所有情况。

SELECT *
FROM S

 （2）检索学生年龄大于等于20岁的学生姓名。

SELECT SN
FROM S
WHERE AGE>=20

（3）检索先修课号为C2的课程号。

SELECT CNO
FROM C
WHERE CPNO='C2'

（4）检索不选修任何课程的学生的学号。

ELECT SNO
FROM S
except
SELECT DISTINCT SNO  
FROM SC
或者
SELECT SNO
FROM S

3.设有四个关系（只示意性给出一条记录）：

S（SNO，SNAME， ADDRESS，TEL）其中，SNO：供应商代码 SNAME：姓名 ADDRESS：地址 TEL：电话；

J（JNO，JNAME，LEADER，BG）其中，JNO：工程代码 JNAME：工程名 LEADER：负责人 BG：预算；

P（PNO，PNAME，SPEC，CITY，COLOR）其中，PNO：零件代码 PNAME：零件名 SPEC：规格 CITY：产地 COLOR：颜色；

SPJ（SNO，PNO，JNO，QTY）其中，SNO：供应商代码 JNO：工程代码 PNO：零件代码 QTY：数量；

完成如下查询：

（1）找出所有供应商的姓名和地址、电话。

SELECT SNAME,ADDRESS,TEL 
FROM S

（2）找出所有零件的名称、规格、产地。

SELECT PNAME,SPEC,CITY 
FROM P

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

SELECT DISTINCT JNO 
FROM SPJ 
WHERE SNO='S1'

（4）找出产地为上海的所有零件代码和规格.

SELECT PNO,SPEC 
FROM P 
WHERE CITY='上海'

（5）取出为工程J1和J2提供零件的供应商代号

SELECT distinct SNO
FROM SPJ 
WHERE JNO ='J1'
union
SELECT distinct SNO
FROM SPJ 
WHERE JNO ='J2'
--或  SELECT distinct SNO FROM SPJ WHERE JNO IN ('J1','J2');
--或  SELECT distinct SNO FROM SPJ WHERE JNO ='J1'  or JNO ='J2';

4.对于简易教学管理数据库有如下3个基本表：S(SNO,SN,AGE,SEX)、SC(SNO,CNO,SCORE)、C(CNO,CN,TH)，其含义为SNO(学号),SN(姓名),AGE(年龄),SEX(性别)，SCORE(成绩)，CNO(课程号),CN(课程名),TH(教师名)。试用SQL语言表达如下查询及操作：

（1）检索年龄大于16岁的女学生的学号和姓名。

SELECT SNO,SN
FROM S
WHERE AGE>16 AND SEX='女'

--或 SELECT SNO,SN FROM S WHERE SEX='女' INTERSECT SELECT SNO,SN FROM S WHERE AGE>16