SQL作业二

1
--查询 订单号为 10306 订单产品明细 ，要求排列格式为
订单号 产品1 产品2 产品3
10306 黄鱼 盐水鸭 鸡肉

非排列
一、select a.订单号码,b.产品 from 订货明细 as a,产品资料 as b where a.订单号码=10306 and a.产品编号=b.产品编号

非排列
二、select a.订单号码,b.产品 from 订货明细 as a inner join
产品资料 as b on a.订单号码=10306 and a.产品编号=b.产品编号

2 查询 订单日期 96 年内的，每个月的订单数
日期 订单数
1996-01 24
1996-02 85
SELECT CONVERT(VARCHAR(7),A.订单日期,120)AS 日期, SUM(数量) AS 订单数
FROM 订货主档 AS A INNER JOIN 订货明细 AS B
ON A.订单号码=B.订单号码 AND CONVERT(VARCHAR(4),A.订单日期,120)='1996'
GROUP BY CONVERT(VARCHAR(7),A.订单日期,120)

3 查询 每年的订单数
日期 订单数
1996 5482
1997 1125
SELECT CONVERT(VARCHAR(4),A.订单日期,120)AS 日期, SUM(数量) AS 订单数
FROM 订货主档 AS A INNER JOIN 订货明细 AS B
ON A.订单号码=B.订单号码
GROUP BY CONVERT(VARCHAR(4),A.订单日期,120)

4 查询96年中每月，每样产品的销售数量
日期 销售数量
1996-01 24

5 查询每年，每样产品的销售数量
日期 销售数量
1996 1024

select CONVERT(VARCHAR(4),b.订单日期,120)AS 日期,
sum(数量) as 销售数量 from
订货明细 as a,订货主档 as b where a.订单号码=b.订单号码
group by CONVERT(VARCHAR(4),b.订单日期,120)

6 编写SQL语句
创建学生表 student
学生编号 主键
身份证号码 唯一不重复
学生姓名
学生出生年月日 1980 - 2000 之间
所属班级 外键，关联班级编号
电话号码 数字约束

CREATE TABLE [dbo].[student](
[学生编号] [int] NOT NULL,
[身份证号码] [char](23) COLLATE Chinese_PRC_CI_AS NOT NULL,
[学生姓名] [nvarchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[学生出生年月日] [datetime] NOT NULL,
[所属班级] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[电话号码] [int] NOT NULL,
CONSTRAINT [PK__student__0425A276] PRIMARY KEY CLUSTERED
(
[学生编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[student] WITH CHECK ADD CONSTRAINT [CK_student] CHECK (([学生出生年月日]>='1980-1-1' AND [学生出生年月日]<='2000-12-31'))
GO
ALTER TABLE [dbo].[student] CHECK CONSTRAINT [CK_student]
GO
ALTER TABLE [dbo].[student] WITH CHECK ADD CONSTRAINT [CK_student_shuzi] CHECK (([电话号码]>=(0) AND [电话号码]<=(999999999)))
GO
ALTER TABLE [dbo].[student] CHECK CONSTRAINT [CK_student_shuzi]

创建班级表 SchoolClass
班级编号 主键
班级名称 如：2008计算2班
班级辅导员 外键，关联辅导员名字
所属系别 外键，关联系别编号

CREATE TABLE [dbo].[SchoolClass](
[班级编号] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[班级名称] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_SchoolClass_班级名称] DEFAULT (N'2008计算2班'),
[班级辅导员] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[所属系别] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_班级表] PRIMARY KEY CLUSTERED
(
[班级编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

创建系别表 DEPARTMENT
系别编号 主键
系别名称
CREATE TABLE [dbo].[DEPARTMENT](
[系别编号] [nchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
[系别名称] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
CONSTRAINT [PK_DEPARTMENT] PRIMARY KEY CLUSTERED
(
[系别编号] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

二、
create table DEPARTMENT
(
系别编号 nchar(10) primary key,
系别名称 nvarchar(20)
)

创建辅导员表 Counselor
辅导员编号 主键
辅导员名称
辅导员电话号码

create table Counselor
(
辅导员编号 nchar(10) primary key,
辅导员名称 nvarchar(50) not null,
辅导员电话号码 int
)

创建成绩表 score
编号 主键
学生编号 外键关联学生编号
课程编号 外键 关联课程表
成绩 约束，数字，范围 0-150
考试时间 时间

if EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME ='score')
DROP TABLE score
create table score
(
编号 nchar(10) primary key,
学生编号 nchar(10) foreign key references student(学生编号),

课程编号 nchar(10) foreign key references schedule(课程编号),

成绩 int
check(150> 成绩 and 成绩>0)

创建课程表 schedule
课程编号 主键
课程名称
create table schedule
(
课程编号 nchar(10) primary key,
课程名称 nvarchar(20) not null
)

7 编写插入语句，为上述表插入数据

insert into schedule values('001','数学')
其它表的插入方法类似

8 尝试编写存储过程，为上述表查询统计学生成绩

create procedure chengji
as
select b.成绩 from student as a,score as b
where a.学生编号=b.学生编号