1.设有下列四个关系模式:
S(SNO,SNAME,CITY)
P(PNO,PNAME,COLOR,WEIGHT)
J(JNO,JNAME,CITY)
SPJ(SNO,PNO,JNO,QTY)
其中,供应商表S由供应商号(SNO)、供应商姓名(SNAME)、供应商所在城市(CITY)组成,记录各个供应商的情况;零件表P由零件号(PNO)、零件名称(PNAME)、零件颜色(COLOR)、零件重量(WEIGHT)组成,记录各种零件的情况;工程项目表J由项目号(JNO)、项目名(JNAME)、项目所在城市(CITY)组成,记录各个工程项目的情况;供应情况表SPJ由供应商号(SNO)、零件号(PNO)、项目号(JNO)、供应数量(QTY)组成,记录各供应商供应各种零件给各工程项目的数量。分别用关系代数和SQL语言完成下列查询:
1)求供应工程项目号为J1工程零件的供应商号SNO
∏SNO(σJNO=‘J1’(SPJ))
SELECT SNO FROM SPJ WHERE JNO=‘J1’;
2)求供应工程项目号为J1工程零件号为P1的供应商号SNO
∏SNO(σJNO=‘J1’∧PNO=‘P1’(SPJ))
SELECT SNO FROM SPJ WHERE JNO=‘J1’ AND PNO=‘P1’;
3)求供应工程项目号为J1工程红色零件的供应商号SNO
∏SNO(σJNO=‘J1’∧COLOR=‘红’(SPJ∞P))
SELECT SNO FROM SPJ,P WHERE SPJ.PNO=P.PNO AND P.COLOR=‘红’AND JNO=‘J1’;
4)求至少使用天津供应商生产的红色零件的工程号JNO
∏JNO(SPJ)-∏JNO(σcity=‘天津’∧COLOR=‘红’(SPJ∞P∞S))
SELECT JNO FROM SPJ,P,S
WHERE S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND CITY<>‘天津’AND COLOR<>‘红’;
5)求至少用了S1供应商所供应的全部零件的工程号JNO(本题不需要用SQL语言完成)
∏JNO,PNO(SPJ)÷∏PNO(σSNO=‘S1’(SPJ))
2.现有如下关系模式:
雇员(员工姓名,居住城市,居住街道) 工作(员工姓名,公司名,工资)
公司(公司名,公司所在城市) 主管(员工姓名,主管姓名)
用关系代数完成下列查询:
1)找出所有在公司名为“firstbank”的公司工作的员工,显示员工姓名。
∏员工姓名(σ公司名=‘firstbank’(工作))
2)显示为“firstbank”公司工作的员工姓名和居住城市。
∏员工姓名,居住城市(σ公司名=‘firstbank’(雇员∞工作))
3)找出所有为“firstbank”公司工作且工资在1000元以上的员工,显示员工姓名和工资。
∏员工姓名,工资(σ公司名=‘firstbank’∧工资>1000(工作))
4)找出每个员工工资都在1000元以上的公司,显示公司名。
∏公司名(工作)-∏公司名(σ工资<1000(工作))
5)找出主管人员Smith领导的员工姓名及员工居住的城市。
∏员工姓名,居住城市(σ主管姓名=‘Smith’(雇员∞主管))
3.设有下列关系模式:
STUDENT(NO,NAME,SEX,BIRTHDAY,CLASS)
TEACHER(NO,NAME,SEX,BIRTHDAY,PROF,DEPART)
COURSE(CNO,CNAME,TNO)
SCORE(NO,CNO,DEGREE)
其中,学生信息表STUDENT由学号(NO)、学生姓名(NAME)、性别(SEX)、出生年月日(BIRTHDAY)、班级(CLASS)组成,记录学生的情况;教师信息表TEACHER由教师号(NO)、教师姓名(NAME)、性别(SEX)、出生年月日(BIRTHDAY)、职称(FROF)、教师所在系(DEPART)组成,记录教师的情况;课程表COURSE由课程号(CNO)、课程名称(CNAME)、任课教师号(TNO)组成,记录所开课程及任课教师情况;成绩表SCORE由学生学号(NO)、课程号(CNO)、成绩(DEGREE)组成,记录学生选课情况及相应的成绩。用SQL语句实现以下操作
1)显示STUDENT表中每个学生的姓名和出生日期。
SELECT NAME,BIRTHDAY FROM STUDENT;
2)显示STUDENT表中所有姓“王”的学生记录。
SELECT * FROM STUDENT WHERE NAME LIKE ‘王%’;
3)显示成绩表中成绩在60分到80分之间的所有记录。
SELECT * FROM SCORE WHERE DEGREE BETWEEN 60 AND 80;
4)显示“男”教师及其所上的课程。(显示该教师的姓名和所上的课程名)
SELECT TEACHER.NAME,COURSE.CNAME FROM TEACHER,COURSE
WHERE TEACHER.NO=COURSE.TNO AND SEX=‘男’;
5)选出和“李军”同学同性别并同班的学生姓名。
SELECT NAME FROM STUDENT
WHERE SEX=(SELECT SEX FROM STUDENT WHERE NAME=‘李军’)AND
CLASS=(SELECT CLASS FROM STUDENT WHERE NAME=‘李军’);
6)向STUDENT表中插入一条学生记录。(学号:999,姓名:程功,性别:男,出生年月日:10/01/80,班级:95035)
INSERT INTO STYDENT VALUES(‘999’,‘程功’,‘男’,‘10/01/80’,‘95035’);
7)在STUDENT表中将学号为“999”的学生的班号改为“95031”
UPDATE STUDENT SET=‘95031’WHERE NO=‘999’;
8)在STUDENT表中删除学号为“999”的学生记录。
DELETE FROM STUDENT WHERE NO=‘999’;
4.设有下列四个关系模式:
S(SNO,SNAME,CITY)
P(PNO,PNAME,COLOR,WEIGHT)
J(JNO,JNAME,CITY)
SPJ(SNO,PNO,JNO,QTY)
其中,供应商表S由供应商号(SNO)、供应商姓名(SNAME)、供应商所在城市(CITY)组成,记录各个供应商的情况;零件表P由零件号(PNO)、零件名称(PNAME)、零件颜色(COLOR)、零件重量(WEIGHT)组成,记录各种零件的情况;工程项目表J由项目号(JNO)、项目名(JNAME)、项目所在城市(CITY)组成,记录各个工程项目的情况;供应情况表SPJ由供应商号(SNO)、零件号(PNO)、项目号(JNO)、供应数量(QTY)组成,记录各供应商供应各种零件给各工程项目的数量。 用SQL语言完成下列操作:
①找出工程项目J2使用的各种零件的名称及其数量
SELECT PNAME,QTY FROM SPJ, P WHERE SPJ.PNO=P.PNO AND JNO=‘J2’;
②找出所有零件的名称、颜色、重量
SELECT PNAME, COLOR, WEIGHT FROM P;
③找出上海厂商供应的所有零件号码
SELECT DISTINCT PNO FROM SPJ
WHERE SNO IN ( SELECT SNO FROM S WHERE CITY=’上海’);
④从供应商关系中删除S2(供应商号)的记录,并从供应情况关系中删除相应的记录
DELETE FROM S WHERE SNO=‘S2’;
DELETE FROM SPJ WHERE SNO=‘S2’;
⑤请将(S2,P4,J6,200)插入供应情况关系
INSERT INTO SPJ VALUS(‘S2’,‘P4’,‘J6’,200);
5.设有3个关系:
S(S#,SNAME,AGE,SEX)
SC(S#,C#,CNAME)
C(C#,CNAME,TEACHER)
试用关系代数表达式表示下列查询语句:
1)检索LIU老师所授课程的课程号和课程名。
∏C#,CNAME(σTEACHER=‘LIU’(C))
2)检索年龄大于23岁的男学生的学号和姓名。
∏S#,SNAME(σAGE>‘23’∧SEX=‘M’(S))
3)检索学号为S3学生所学课程的课程名与任课教师名。
∏CNAME,TEACHER(σS#=‘S3’(SC∞C))
4)检索至少选修LIU老师所授课程中一门课的女学生姓名。
∏SNAME(σSEX=‘F’∧TEACHER=‘LIU’(S∞SC∞C))
5)检索WANG同学不学的课程的课程名。
∏C#(C)-ΠC#(σSNAME=‘WANG’(S∞SC)
6)检索全部学生都选修的课程的课程号与课程名。
∏C#,CNAME(C∞(πS#,C#(SC) ÷ πS#(S)))
7)检索选修课程包含LIU老师所授全部课程的学生学号。
∏S#,C#(SC)÷∏C#(σTEACHER=‘LIU’(C))
6.对于教学数据库的3个基本表
S(S#,SNAME,AGE,SEX)
SC(S#,C#,CNAME)
C(C#,CNAME,TEACHER)试用关系代数表达式表示下列查询语句:
1 ) 检索LIU老师所授课程的课程号和课程名。
SELECT C#,CNAME FROM C WHERE TEACHER= ‘LIU’;
2)检索年龄大于23岁的男学生的学号和姓名。
SELECT S#,SNAME FROM S WHERE AGE>23 AND SEX=‘M’;
3)检索学号为S3学生所学课程的课程名与任课教师名。
SELECT CNAME,TEACHER FROM SC,C WHERE SC.C# =C.C# AND S#=‘S3’;
4)检索至少选修LIU老师所授课程中一门课的女学生姓名。
连接查询方式
SELECT SNAME FORM S,SC,C
WHERE S.S# =SC.S# AND SC.C# =C.C# AND SEX=‘F’AND TEACHER=‘LIU’;
嵌套查询方式
SELECT SNAME FORM S WHERE SEX =‘F’AND S# IN
(SELECT S# FORM SC WHERE C# IN
(SELECT C# FORM C WHERE TEACHER =‘LIU’));
存在量词方式
SELECT SNAME FORM S WHERE SEX=‘F’AND EXISTS
(SELECT * FORM SC WHERE SC.S# =S.S# AND EXISTS
(SELECT * FORM C WHERE C.C# =SC.C# AND TEACHER=‘LIU’));
5)检索WANG同学不学的课程的课程名。
SELECT C# FOME C WHERE NOT EXISTS
(SELECT * FORM S,SC WHERE S.S# =SC.S# AND SC.C# =C.C# AND SNAME=‘WANG’);
6)检索至少选修两门课的学生学号。
SELECT DISTINCT X.S# FROM SC X, SC Y WHERE X.S#=Y.S# AND X.C#!=Y.C#;
7)检索全部学生都选修的课程的课程号与课程名。
SELECT C#,CNAME FROM C WHERE NOT EXISTS
(SELECT * FROM S WHERE NOT EXISTS
(SELECT * FROM SC WHERE S#=S.S# AND C#=C.C#));
用集合包含
SELECT C#,CNAME FROM C WHERE (SELECT S# FROM SC WHERE C# =C.C# )
CONTAINS
SELECT S# FROM S;
或
SELECT C#,CNAME FROM C WHERE NOT EXISTS
((SELECT S# FROM S) EXCEPT
(SELECT S# FROM SC WHERE C# =C.C#));
8)检索选修课程包含LIU老师所授课程的学生学号。
SELECT DISTINCT S# FROM SC X WHERE NOT EXISTS
(SELECT * FROM C WHERE TEACHER=‘LIU’AND NOT EXISTS
(SELECT * FROM SC Y WHERE Y.S# =X.S# AND X.C#=C.C#));
或:SELECT DISTINCT S# FROM SC X WHERE NOT EXISTS
((SELECT C# FROM C WHERE TRACHER=‘LIU’) EXCEPT
(SELECT C# FROM SC Y WHERE Y.S# =X.S#));
7.试用SQL查询语句表达下列对上题中3个基本表S、SC、C的查询:
1)统计有学生选修的课程门数。
SELECT COUNT (DISTINCT C#) FROM SC;
2)求选修C4课程的女学生的平均年龄。
SELECT AVG(AGE) FROM S,SC WHERE S.S#=SC.S# AND C# =‘C’AND SEX=‘F’;
3)求LIU老师所授课程的每门课程的平均成绩。
SELECT C.C#,AVG(GRADE) FROM SC,C WHERE SC.C# =C.C# AND TEACHER=‘LIU’
GROUP BY C.C#;
4)统计每门课程的学生选修人数(超过10人的课程才统计)。要求显示课程号和人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
SELECT C# ,COUNT(S#) FROM SC
GROUP BY C# HAVING COUNT(*)>10
ORDER BY 2 DESC,1;
5)检索学号比WANG同学大,而年龄比他小的学生姓名。
SELECT SNAME FROM S WHERE S#>ALL (SELECT S# S WHERE SNAME=‘WANG’)
AND AGE<ALL (SELECT AGE FROM S WHERE SNAME=‘WANG’);
6)在表SC中检索成绩为空值的学生学号和课程号。
SELECT S#,C# FROM SC WHERE GRADE IS NULL;
7)检索姓名以L打头的所有学生的姓名和年龄。
SELECT SNAME,AGE FROM S WHERE SNAME LIKE ‘L%’;
8)求年龄大于女同学平均年龄的男学生姓名和年龄。
SELECT SNAME,AGE FROM S WHERE SEX=‘M’AND
AGE >(SELECT AVG(AGE) FROM S WHERE SEX=‘F’);
9)求年龄大于所有女同学年龄的男学生姓名和年龄。
SELECT SNAME,AGE FROM S WHERE SEX=‘M’AND
AGE >ALL(SELECT AGE FROM S WHERE SEX=‘F’);
8.用SQL更新语句表达对上题教学数据库中关系S、SC、C作如下更新操作:
1)往关系C中插一个课程元组。
INSERT INTO C VALUES(‘C8’,‘VC++’,‘BAO’);
2)SC中删除尚无成绩的选课元组。
DELETE FROM SC WHERE GRADE IS NULL;
3)把选修LIU老师课程的女同学选课元组全部删去。
DELETE FROM SC WHERE S# IN (SELECT S# FROM S WHERE SEX =‘F’)
AND C# IN(SELECT C# FROM C WHERE TEACHER=‘LIU’);
4)把MATHS课不及格的成绩全改为60分。
UPDATE SC SET GRADE = 60
WHERE GRADE<60 AND C# IN (SELECT C# FROM C WHERE CNAME=‘MATHS’);
5)把低于所有课程总平均成绩的女同学成绩提高5%。
UPDATE SC SET GRADE = GRADE *1.05
WHERE S# IN(SELECT S# FROM S WHERE SEX=‘F’)AND GRADE<(SELECT AVG(GRADE) FROM SC);
6)在表SC中修改C4课程的成绩,若成绩小于等于70分提高5%,若成绩大于70分时提高4%(用两种方法实现,一种方法是用两个UPDATE语句实现,另一种方法是用带CASE操作的一个UPDATE语句实现)。
UPDATE SC SET GRADE=GRADE * 1.04
WHERE C# ‘C4’AND GRADE>70;
UPDATE SC SET GRADE =GRADE * 1.05
WHERE C # =’C4’AND GRADE<=70;
7)在表SC中,当某个成绩低于全部课程的平均成绩时,提高5%。
UPDATE SC SET GRADE = GRADE * 1.05
WHERE GRADE<(SELECT AVG(GRADE) FROM SC);
9.设数据库中有3个关系:
职工表EMP(E#,ENAME,AGE,SEX,ECITY),其属性分别表示职工工号、姓名、年龄、性别和籍贯。
工作表WORKS(E#,C#,SALARY),其属性分别表示职工工号、工作的公司编号和工资。
公司表 COMP(C#,CNAME,CITY),其属性分别表示公司编号、公司名称和公司所在城市。
试用SQL语言写出下列操作:
1)检索超过50岁的男职工的工号和姓名。
SELECT E#,ENAME FROM EMP WHERE AGE>50 AND SEX=‘M’;
2)假设每个职工只能在一个公司工作,检索工资超过1000元的男性职工工号和姓名。
SELECT EMP.E#,ENAME FROM EMP,WORKS
WHERE EMP.E#=WORKS.E# AND SALARY>1000;
3)假设每个职工可在多个公司工作,检索在编号为C4和C8公司兼职的职工工号和姓名。
SELECT A.E#,A.ENAME FROM EMP A,WORKS B,WORKS C
WHERE A.E#=B.E# AND B.E#=C.E# AND B.C#=‘C4’AND C.C#=‘C8’;
4)检索在“联华公司”工作、工资超过1000元的男性职工的工号和姓名。
SELECT A.E#,A.ENAME FROM EMP A,WORKS B,COMP C
WHERE A.E#=B.E# AND B.C#=C.C# AND CNAME=‘联华公司’
AND SALARY>1000 AND SEX=‘M’;
5)假设每个职工可在多个公司工作,检索每个职工的兼职公司数目和工资总数,显示(E#,NUM,SUM_SALARY),分别表示工号、公司数目和工资总数。
SELECT E#,COUNT(C#) AS NUM,SUM(SALARY) AS SUM_SALARY
FROM WORKS GROUP BY E#;
6)工号为E6的职工在多个公司工作,试检索至少在E6职工兼职的所以公司工作的职工工号。
SELECT X.E# FROM WORKS X WHERE NOT EXISTS
(SELECT * FROM WORKS Y WHERE E#=‘E6’AND NOT EXISTS
(SELECT * FROM WORKS Z WHERE Z.E#=X.E# AND Z.C#=Y.C#));
7)检索联华公司中低于本公司平均工资的职工工号和姓名。
SELECT A.E#,A.ENAME FROM EMP A,WORKS B,COMP C
WHERE A.E#=B.E# AND B.C#=C.C# AND CNAME =’联华公司’AND SALARY<
(SELECT AVG(SALARY) FROM WORKS,COMP WHERE WORKS.C#=COMP.C# AND CNAME =‘联华公司’);8)在每个公司中为50岁以上职工加薪100元(若职工为多个公司工作,可重复加)。
UPDATE WORKS SET SALARY =SALARY+100
WHERE E# IN (SELECT E# FROM EMP WHERE AGE>50);
10)在EMP表和WORKS表中删除年龄大于60岁的职工有关元组。
DELETE FROM WORKS WHERE E# IN (SELECT E# FROM EMP WHERE AGE>60);
DELETE FROM EMP WHERE AGE >60;
10.设有关糸模式:SB(SN,SNAME,CITY)其中,SB表示供应商,SN为供应商代号,SNAME为供应商名字,CITY为供应商所在城市,主关键字为SN。
PB(PN,PNAME,COLOR,WEIGHT) 其中PB表示零件,PN为零件代号,PNAME为零件名字,COLOR为零件颜色,WEIGHT为零件重量,主要字键为PN。
JB(JN,JNAME,CITY)其中,JB表示工程,JN为工程编号,JNAME为工程名字,CITY为工程所在城市,主关键字为JN。
SPJB(SN,PN,CITY)其中,SPJB表示供应关糸,SN是为指定工程提供零件的供应商代号,PN为所提供的零代号,JN为工程编号,OTY表示提供的零件数量,主关键字为SN,PN,JN,外关键字为SN,PN,JN。
如图所示表示供应商(S)-零件(P)-工程(J)数据库表,写出实现以下各题功能的SQL语句:
本文链接:https://cloud.tencent.com/developer/article/1557979
本文采用CC BY-NC-SA 3.0 Unported协议进行许可,转载请保留此文章链接