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) 最高分