数据库实验二-三-SQL语句

SQL语句,实验二,实验三,数据库数据定义和查询1、掌握使用SQL语句创建和删除数据表,创建各种完整性约束。2、掌握使用SQL语句修改表的结构。3、掌握查询语句的使用方法,重点掌握连接查询和嵌套查询。数据更新与视图1、掌握数据更新语句的使用;2、掌握视图操作的基本方法和应用,理解基于视图的查询和数据更新操作的过程。

实验二 数据定义和查询

SQL语句：

CREATE DATABASE Lab2

ON

PRIMARY

(

NAME = 'Lab2_data1',

FILENAME = 'D:\data\Lab2_data1.mdf',

SIZE = 20 MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10%

),

(

NAME = 'Lab2_data2',

FILENAME = 'D:\data\Lab2_data2.ndf',

SIZE = 20 MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10%

)

LOG ON

(

NAME = 'Lab2_log1',

FILENAME = 'D:\data\Lab2_log1.ldf',

SIZE = 50 MB,

MAXSIZE = 100 MB,

FILEGROWTH = 10 MB

);

USE Lab2

ALTER DATABASE lab2 REMOVE FILE Lab2_data2;

/*按要求创建四个关系S,P,J,SPJ*/

CREATE TABLE S/*供应商表*/

(

sno varchar(20) primary key,

sname varchar(20) unique,

city varchar(50)

);

SQL语句,实验二,实验三,数据库数据定义和查询1、掌握使用SQL语句创建和删除数据表,创建各种完整性约束。2、掌握使用SQL语句修改表的结构。3、掌握查询语句的使用方法,重点掌握连接查询和嵌套查询。数据更新与视图1、掌握数据更新语句的使用;2、掌握视图操作的基本方法和应用,理解基于视图的查询和数据更新操作的过程。

CREATE TABLE P/*零件表*/

(

pno varchar(20) primary key,

pname varchar(20) ,

color varchar(10) ,

weight int check(weight>=1 and weight <=50),

);

CREATE TABLE J/*工程项目表*/

(

jno varchar(20) primary key,

jname varchar(20) not null unique,

city varchar(50) ,

);

CREATE TABLE SPJ/*供应情况表*/

(

sno varchar(20) references S(sno),

pno varchar(20) references P(pno),

jno varchar(20) references J(jno),

qty int

);

/*给S表增加Sphone和Semail两个属性列，分别用来存放供应商的联系电话和电子信箱。*/

ALTER TABLE S

ADD sphone varchar(20),

semail varchar(20);

/*（2）删除Jname属性列取值唯一的约束。*/

ALTER TABLE J

DROP UQ__J__C1C26BEE0DAF0CB0;

ALTER TABLE SPJ

ALTER COLUMN qty smallint;

ALTER TABLE S

DROP COLUMN semail;

/*（1）查询所有供应商所在的城市。*/

SELECT distinct city

from S

/*（2）查询零件重量在10-20之间（包括10和20）的零件名和颜色。*/

SQL语句,实验二,实验三,数据库数据定义和查询1、掌握使用SQL语句创建和删除数据表,创建各种完整性约束。2、掌握使用SQL语句修改表的结构。3、掌握查询语句的使用方法,重点掌握连接查询和嵌套查询。数据更新与视图1、掌握数据更新语句的使用;2、掌握视图操作的基本方法和应用,理解基于视图的查询和数据更新操作的过程。

SELECT pname ,color

from P

where weight between 10 and 20

/*（3）查询工程项目的总个数。*/

SELECT COUNT(jno)

from J

/*（4）查询所有零件的平均重量。*/

SELECT AVG(weight)

from P

/*（5）查询供应商S3供应的零件号。*/

SELECT pno

from SPJ

where sno='s3'

/*（6）查询各个供应商号及其供应了多少类零件。*/

SELECT sno,COUNT(pno)

from SPJ

GROUP BY sno

/*（7）查询供应了2类以上零件的供应商号。*/

SELECT sno

from SPJ

GROUP BY sno

HAVING COUNT(pno)>=2

/*（8）查询零件名以“螺”字开头的零件信息。*/

SELECT *

FROM P

WHERE pname LIKE '螺%';

/*（9）查询工程项目名中最后一个字为“厂”字的工程项目所在的城市。*/

SELECT city

from J

where jname like '%厂';

SQL语句,实验二,实验三,数据库数据定义和查询1、掌握使用SQL语句创建和删除数据表,创建各种完整性约束。2、掌握使用SQL语句修改表的结构。3、掌握查询语句的使用方法,重点掌握连接查询和嵌套查询。数据更新与视图1、掌握数据更新语句的使用;2、掌握视图操作的基本方法和应用,理解基于视图的查询和数据更新操作的过程。

/*（10）查询给每个工程供应零件的供应商的个数。*/

SELECT jno,COUNT(sno)

from SPJ

GROUP BY jno

/*（11）查询供应数量在1000—2000之间（包括1000和2000）的零件名称。*/ SELECT pname

from P

where pno in

(SELECT pno

from SPJ

group by pno

HAVING sum(QTY) BETWEEN 1000 AND 2000);

/**/

/**/

use lab2;

/*（1）查询计算机专业学生的姓名和年龄。*/

select Sname,datediff(yy,sbirth,getdate())

from S

where major='计算机';

（2）查询成绩在70-80分（包括70和80分）之间的学生的学号、课程号和成绩。 select *

from SC

where grade between 70 and 80;

（3）查询C01号课程的最高成绩。

select max(grade)

from SC

group by Cno

having cno='C01';

（4）统计每个专业的学生人数。

Select major, Count(sno)

From S

Group by Major;

（5）统计每门课程的修课人数和考试最高分。

Select cno,count(sno) 人数,max(grade) 最高分

From SC

group by Cno;

（6）统计每个学生的选课门数和考试总成绩，并按总成绩的递减顺序显示结果。 Select sno,count(cno) 选课门数, sum(grade) 总成绩

From SC

SQL语句,实验二,实验三,数据库数据定义和查询1、掌握使用SQL语句创建和删除数据表,创建各种完整性约束。2、掌握使用SQL语句修改表的结构。3、掌握查询语句的使用方法,重点掌握连接查询和嵌套查询。数据更新与视图1、掌握数据更新语句的使用;2、掌握视图操作的基本方法和应用,理解基于视图的查询和数据更新操作的过程。

group by sno

Order by 总成绩 desc;

（7）查询总成绩超过200分的学生，要求列出学号和总成绩。

Select sno,sum(grade)

From SC

group by sno

having SUM(grade)>=200;

（8）查询姓名为余丹妮的学生所学课程的课程名与学分。

Select cname ,credit

From C

Where cno in(

Select cno

From SC

Where sno in(

Select sno

From S

Where sname='余丹妮'

)

)

（91）查询选修课程号为“C02”或“C07”的学生的学号。

Select distinct s1.Sno

From SC s1,SC s2

Where s1.sno=s2.sno and (o='C02' or o='C07');

（9）查询选修课程号为“C02”或“C07”的学生的学号。

Select distinct sno

From SC

Where cno='C02' or cno='C07';

（10）查询选修了课程号为“C02”和“C07”的学生的学号。

Select s1.Sno

From SC S1,SC S2

Where s1.sno=s2.sno and o='C02' and o='C07';

（11）查询学习全部课程的学生姓名。

Select sname

From S

Where sno in(

Select sno

From SC

Group by sno

Having count(cno)=8

)

（12）查询1992年1月1日以前出生的学生的姓名和专业。

SQL语句,实验二,实验三,数据库数据定义和查询1、掌握使用SQL语句创建和删除数据表,创建各种完整性约束。2、掌握使用SQL语句修改表的结构。3、掌握查询语句的使用方法,重点掌握连接查询和嵌套查询。数据更新与视图1、掌握数据更新语句的使用;2、掌握视图操作的基本方法和应用,理解基于视图的查询和数据更新操作的过程。

Select sname, major

From S

Where datediff(dd,sbirth,'1992-1-1')>0

（13）查询选修了“计算机网络”课程且成绩在90分以上的学生姓名。

Select sname

From S

Where sno in(

Select sno

From SC

Where cno in (

Select cno

From C

Where cname='计算机网络'

)and grade>=90

)

（14）查询选修了5门以上课程的学生学号和姓名。

Select sno ,sname

From S

Where sno in (

Select sno

From SC

Group by sno

having count(cno)>=5

)

（15）查询未选修“离散数学”的学生情况。

SELECT *

FROM S

WHERE NOT EXISTS

(SELECT *

FROM SC

WHERE Sno = S.Sno

AND Cno=(select cno

From C

Where cname='离散数学')

)

（16）统计C01和C06号课程的选课人数及平均成绩。

Select cno ,count(sno) 选课人数,avg(grade) 平均成绩

From sc

Group by cno

having Cno='C01' or Cno='C06';

（17）查询比所有计算机专业学生年龄都大的学生。

Select *

From S

SQL语句,实验二,实验三,数据库数据定义和查询1、掌握使用SQL语句创建和删除数据表,创建各种完整性约束。2、掌握使用SQL语句修改表的结构。3、掌握查询语句的使用方法,重点掌握连接查询和嵌套查询。数据更新与视图1、掌握数据更新语句的使用;2、掌握视图操作的基本方法和应用,理解基于视图的查询和数据更新操作的过程。

Where datediff(yy,sbirth,getdate())>(select max(datediff(yy,sbirth,getdate()))

From S

Where major='计算机')

（18）将网络工程专业的学生按出生时间先后排序。

Select *

From S

where Major='网络工程'

Order by sbirth asc

实验三 数据更新与视图

SQL语句：

//在实验一创建的数据库STUDENT中使用SQL语句完成以下操作：

//（1）学生佟双要退学，请删除该学生的所有记录。

alter table SC

add constraint FK_SC_S foreign key (Sno) references S(Sno)

on delete cascade;

delete

from S

where sname='佟双'

（2）将姓名为“刘忠琦”同学的专业改为“软件工程”，备注改为“转专业学习”。 update S

set major='软件工程',

note='转专业学习'

where sname='刘忠琦'

（3）删除选修了“计算机网络”但没有及格的选课记录。

delete

from sc

where cno =(select cno from C where cname='计算机网络') and grade <60

（4）将（101131，路前元，男，1993-11-8，网络工程）插入学生关系。

insert into S(Sno,Sname,Ssex,Sbirth,Major)

values('101131','路前元','男',1993-11-8,'网络工程')

（5）在C表中插入以下元组：

“C09,数据库原理，4，48，4”，

“C10,JAVA语言程序设计，3，72，6”

SQL语句,实验二,实验三,数据库数据定义和查询1、掌握使用SQL语句创建和删除数据表,创建各种完整性约束。2、掌握使用SQL语句修改表的结构。3、掌握查询语句的使用方法,重点掌握连接查询和嵌套查询。数据更新与视图1、掌握数据更新语句的使用;2、掌握视图操作的基本方法和应用,理解基于视图的查询和数据更新操作的过程。

insert into C (Cno,Cname,Term,Period,Credit)

values('C09','数据库原理',4,48,4),('C10','JAVA语言程序设计',3,72,6)

（6）创建视图JSJ_VIEW，包括计算机专业学生的学号，姓名，其选修的课程名及成绩属性列。

create view JSJ_VIEW (sno,sname,cname,grade)

as

select S.sno,sname,cname,grade

from S,C,SC

where S.major='计算机'

and S.sno=SC.sno

and o=o

（7）创建查询每个学生的修课学分的视图XF_VIEW，要求列出学生学号及总学分。 create view XF_VIEW (sno,总学分)

as

select sno,SUM(credit) 总学分

from SC,C

where o=o

group by sno

（8）在创建的视图中查询任静凯和张华所修的学分数。

select 总学分

from XF_VIEW

where sno in (select sno from S where sname ='任静凯' or sname='张华')

use lab2

2．在实验二创建的Project数据库中使用SQL语句完成以下操作：

（1）现有一供应商，代码为S9、姓名为英特尔、所在城市西安，供应情况如下：供应零件P5给工程J7数

量为600，供应零件P4给工程J4数量为500，请将此供应商的信息和供应信息插入数据库。 insert into S (sno,sname,city)

values ('S9','英特尔','西安')

insert into SPJ values ('S9','P5','J7',600),('S9','P4','J4',500);

（2）请将北京供应商的供应数量加150。

update SPJ

set QTY=QTY+150

where sno in (select sno from S where city='北京')

（3）把全部红色零件的颜色改为黑色。

SQL语句,实验二,实验三,数据库数据定义和查询1、掌握使用SQL语句创建和删除数据表,创建各种完整性约束。2、掌握使用SQL语句修改表的结构。3、掌握查询语句的使用方法,重点掌握连接查询和嵌套查询。数据更新与视图1、掌握数据更新语句的使用;2、掌握视图操作的基本方法和应用,理解基于视图的查询和数据更新操作的过程。

update P

set color='黑'

where color='红'

（4）由S5供给J4的零件P6改为由S1供应。

update SPJ

set sno='s1'

where jno='J4' and pno='P6' and sno='s5'

（5）请将（S2,J7,P4,510）插入供应情况关系。

insert into SPJ values ('S2','P4','J7',510)

（6）零件P3已经停产，请将P3的相关信息从数据库中删除。

alter table SPJ

add constraint FK_spj_p1 foreign key (Pno) references P(Pno)

on delete cascade

delete

from P

where Pno='p3'

（7）创建零件名为螺丝刀的供应情况的视图P_ls，包括供应商名（Sname），零件名（Pname），零件重量（Weight），工程项目代码（Jno），供应数量（QTY）。

create view P_ls(sname,pname,pweight,jno,qty)

as

select sname,pname,pweight,jno,qty

from S,P,SPJ

where S.sno=SPJ.sno

and P.pno=SPJ.pno

（8）在视图P_ls中查询供应数量为500的供应商姓名。

select sname

from P_ls

where qty >=500

（9）请为三建工程项目建立一个供应情况的视图SJ_View，包括供应商编号，零件编号，供应数量。

create view SJ_View (sno,pno,qty)

as

select sno,pno,qty

from SPJ

本文来源：https://www.bwwdw.com/article/oq1m.html