数据库代码

四、SQL语言
（一）设学生-课程数据库有如下关系表，请用SQL完成下面的查询及数据操纵
STUDENT（SNO，SNAME，SEX，BIRTHDAY，ADDRESS）；
//学生（学生号，学生名，性别，出生日期）
COURSE（CNO，CNAME，LHOUR，CREDIT）；
//课程（课程号，课程名，学时，学分）
SC_score（SNO，CNO，GRADE）；
//学生选课（学生号，课程号，成绩）

1、查询学生学号、姓名、性别、出生日期；（学生表的投影）
select SNO,SNAME,SEX,BIRTHDAY  from STUDENT;  
2、查询所有女生的学号,姓名；（学生表的选择和投影）
SELECT  SNO,SNAME,ADDRESS  FROM STUDENT  WHERE SEX='女'; 
3、查询1980年以前出生的学生学号、姓名、性别、出生日期；（学生表的选择与投影）
SELECT  SNO, SNAME, SEX, BIRTHDAY  FROM STUDENT  WHERE BIRTHDAY<'1980-01-01';
或
SELECT  SNO, SNAME, SEX, BIRTHDAY  FROM STUDENT  WHERE  Year(BIRTHDAY)<1980;
4、查询1979年到1981年出生的学生姓名,性别及出生日期;
SELECT SNAME,SEX,BIRTHDAY  FROM STUDENT  WHERE BIRTHDAY BETWEEN '1979-1-1' AND '1981-12-31';
或
SELECT SNAME,SEX,BIRTHDAY FROM STUDENT WHERE Year(BIRTHDAY) BETWEEN 1979 AND 1981;
5、查询所有姓李学生的学号,姓名,住址,电话;（模糊查询）
SELECT SNO,SNAME,ADDRESS,TELEPHONE  FROM STUDENT  WHERE SNAME LIKE '李%';
6、查询所有不姓李学生的学号,姓名,住址,电话; （模糊查询）
SELECT SNO,SNAME,ADDRESS,TELEPHONE FROM STUDENT  WHERE SNAME  Not LIKE '李%';
7、查询计算机系所开课程（课程号以CS开头）的课程号、课程名和学分数；（模糊查询）
SELECT  cno,cname,credit  FROM course  WHERE  cno  like 'CS%';
8、查询至少选修一门计算机系课程的女生的姓名；
SELECT  DISTINCT  Sname  from  Student, SC_Score  
WHERE  STUDENT.Sno=SC_Score.Sno  and  Cno like 'CS%' AND SEX='女';
9、查询学生选修课程的成绩：学生名,课程名,成绩; （连接查询）
——学生名--STUDENT表中;课程名_COURSE;成绩:SC_SCORE;——涉及了多张表,表的连接
SELECT SNAME, CNAME, SCORE  FROM STUDENT,COURSE,SC_SCORE  
WHERE  STUDENT.SNO=SC_SCORE.SNO AND SC_SCORE.CNO=COURSE.CNO;
10、查询学生选修课程总分_SUM(SCORE)和课程数:COUNT(*)（统计查询）
——统计:聚集函数;  GROUP BY SNO；——成绩表:SC_SCORE
SELECT SNO,SUM(SCORE),COUNT(*)  FROM SC_SCORE GROUP BY SNO;
11、分别统计男、女生的人数；（统计查询）
使用聚集函数就要用GROUP BY;  检索的结果列只能出现聚集列与GROUP BY 后出现的列；
select  sex,count(*)  from student  group by sex;
12、按课程统计每门课程的平均分、总分、最高分、最低分（统计查询）
SELECT  CNO,AVG(SCORE),SUM(SCORE),MAX(SCORE),MIN(SCORE)  FROM SC_SCORE GROUP BY  CNO;
13.统计每门课程的选课人数、最高分、最低分和总分。（统计查询）
SELECT  CNO , COUNT(CNO),MAX(SCORE),MIN(SCORE),SUM(SCORE)  FROM SC_SCORE GROUP BY  CNO;
14、统计学生表中的最大出生日期，最小出生日期。（统计查询）
SELECT MAX(BIRTHDAY),MIN(BIRTHDAY)  FROM STUDENT;  
15、查询李民选修课程的总成绩；（统计查询）
Select  sname,sum(score)  from student,sc_score  
where  student.sno=sc_score.sno  and sname='李民'
group by sname;
16、查询李民选修课程的总成绩、平均成绩、选修的课程数（统计查询）
select sname,sum(score),avg(score),count(*)
from student,sc_score
where student.sno=sc_score.sno   and  student.sname='李民'
group by sname;
17、查询所有课程成绩均在80分以上的学生的姓名、学号；（嵌套查询）
SELECT SNAME ,SNO  FROM STUDENT
WHERE  SNO IN  ( SELECT SNO  FROM  SC_SCORE  GROUP BY SNO HAVING MIN(SCORE) >=80 );  
19、向sta表插入新生记录（'20010649'，'张明'，'男'，’汉族’,'1982-10-8'）；（插入操作）
INSERT INTO  sta ( sno, sname, sex, birthday )  
VALUES ( '20010649',  '张明',  '男',’汉族’,  '1982-10-8' ) ;
20、将学生表中所有女生的出生日期加上一年；（更新操作）
update student
set birthday=birthday+365;
21、将所有选修"计算机基础"课程的学生成绩下降20%，再另增加10分；（带子查询的更新操作）
update sc_score
set score=score-score*0.2+10
where  cno in 
( select  cno
  from  course
  where cname='计算机基础' ); 
22、删除学号为20010649的学生信息；（删除操作）
DELETE FROM STUDENT  
WHERE SNO='20010649';
23、删除无人选修的课程。（带子查询删除操作）
delete from course
where cno not In ( select cno from sc_score );

（二）设学生课程数据库有如下关系表，请用SQL语言完成下列要求的查询及数据操纵
STUDENT（SNO，SNAME，SEX，BDATE，HEIGHT）；
//学生（学生号，学生名，性别，出生日期，身高）
COURSE（CNO，CNAME，LHOUR，CREDIT，SEMESTER）；
//课程（课程号，课程名，学时，学分，学期）
SC（SNO，CNO，GRADE）；
//选课（学生号，课程号，成绩）

1、查询身高大于1.80米的男生的学号和姓名；
SELECT SNO,SNAME    FROM STUDENT    WHERE HEIGHT>1.8 AND SEX=’男’;
2、查询计算机系所开课程（课程号以CS开头）的课程号和学分数；
SELECT CNO,CREDIT   FROM COURSE     WHERE CNO LIKE ‘CS%’;
3、查询至少选修一门计算机系课程的女生的姓名；
SELECT  SNAME   FROM STUDENT,SC 
    WHERE (STUDENT.SNO=SC.SNO) AND (SEX=’女’) AND CNO LIKE ‘CS%’;
4、查询每门课程选课的学生人数、最高成绩、最低成绩和平均成绩；
SELECT COUNT(SNO),MAX(GRADE),MIN(GRADE),AVG(GRADE)
    FROM  SC    GROUP BY CNO;
5、查询所有课程的成绩都在80分以上的学生的姓名、学号，并按学号升序排列；
SELECT SNAME, SNO   FROM STUDENT
    WHERE SNO IN  ( SELECT SNO  FROM SC GROUP BY SNO HAVING GRADE>=80 )     ORDER BY SNO;
6、查询李芳选修课程的总成绩；
SELECT SNAME,SUM(GRADE)     FROM STUDENT,SC 
    WHERE (STUDENT.SNO=SC.SNO) AND SNAME=’李芳’   GROUP BY SNAME;
7、将所有选修“操作系统”课程的学生成绩下降20%，再另增加10分；
UPDATE SC   SET  GRADE=GRADE-GRADE*0.2+10
    WHERE CNO IN (SELECT CNO    FROM COURSE WHERE CNAME=’操作系统’);
8、新生张明（982049，‘张明’，‘男’，19，1.78）选修了数据结构课程（CNO为CS-110）成绩78分；
INSERT INTO STUDET(SNO,SNAME,SEX,AGE,HEIGHT)    VALUES (982049,’张明’,’男’,19,1.78);
INSERT INTO SC(SNO,CNO,GRADE)   VALUES (982049,’CS-110’,78);

（三）设零件供应数据库的关系模式如下，请用SQL语言完成下列要求的查询及数据操纵
S（SNO，SNAME，STATUS，CITY）；//供应商
P（PNO，PNAME，COLOR，WEIGHT）；//零件
J（JNO，JNAME，CITY）；//工程
SPJ（SNO，PNO，JNO，QTY）；//供应关系
请完成下列查询：

1) 找出所有供应商的姓名和所在城市。
SELECT SNAME,CITY   FROM  S; 
2) 找出所有零件的名称、颜色、重量。
SELECT PNAME,COLOR,WEIGHT   FROM  P; 
3) 找出使用供应商S1所供应零件的工程号。
SELECT JNO
FROM  SPJ
WHERE SNO=‘S1’;
4) 找出工程项目J2使用的各种零件的名称及其数量。
SELECT PNAME,QTY
FROM P,SPJ
WHERE P.PNO=SPJ.PNO AND JNO=‘J2’; 
5) 找出上海厂商供应的所有零件号码。
SELECT PNO
FROM S，SPJ
WHERE S.SNO=SPJ.SNO AND CITY=‘上海’; 
6) 找出使用上海产的零件的工程名称。
SELECT JNAME
FROM S, SPJ, J
WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND CITY=‘上海’; 
7) 找出没有使用天津产的零件的工程号。
SELECT JNO
    FROM J
WHERE NOT EXISTS
( SELECT *
FROM SPJ 
WHERE SPJ.JNO=J.JNO AND SNO IN 
        ( SELECT SNO
FROM S
WHERE CITY=‘天津’)); 
8) 把全部红色零件的颜色改成蓝色。
UPDATE  P
SET COLOR=‘蓝色’
WHERE  COLOR=‘红色’
 9) 由S5供应给J4的零件P6改为由S3供应，请做必要的修改。
UPDATE SPJ
SET SNO=‘S3’
    WHERE  JNO=‘J4’ AND PNO=‘P6’ AND SNO=‘S5’
10) 从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录。
DELETE  FROM SPJ 
WHERE  SNO=‘S2’ ; 
DELETE FROM  S
WHERE  SNO=‘S2’; 
11) 请将（S2，J6，P4，200）插入供应商情况表。
INSERT INTO SPJ  VALUES(‘S2’, ‘J6’, ‘P4’,200);
12. 请为三建工程项目建立一个供应情况的视图，包括供应商代码（SNO）、零件代码（PNO）、供应数量（QTY）。
CREATE VIEW GYQK_VIEW
AS 
SELECT SNO,PNO,QTY
FROM SPJ,J
WHERE SPJ.JNO=J.JNO AND JNAME=‘三建’; 
（1）找出三建工程项目使用的各种零件代码及其数量
SELECT PNO,QTY
FROM GYQK_VIEW; 
（2）找出供应商S1的供应情况。
SELECT *
FROM GYQK_VIEW
WHERE SNO=‘S1’; 
12. 针对习题3建立的表，用SQL语言完成下列各题：
（1）把对表S的INSERT权限授予用户张勇，并允许他再将此权限授予其他用户。
GRANT INSERT ON TABLE S TO 张勇
WITH GRANT OPTION;
（2）把查询SPJ表和修改QTY属性的权限授予用户李天明。
GRANT SELECT，UPDATE(QTY) ON TABLE SPJ TO 李天明；