数据库实例

创建如下三个基本表，表结构如下：
BORROWER：
借书证号　　　姓名　　系名　　班级
03001　　　　赵垒　　信管系　　X01
03002　　　　刘诚　　信管系　　X01
03003　　　　陈林　　计算机　　J03
03004 王敏 外语系 W02
……
LOANS:
借书证号　　图书登记号　　借书日期
03001　　　　T00101　　　　2004-04-01
03001 T00302 2005-03-04
03002　　　　T00102　　　　2004-05-01
03002 T00101 2005-01-01
03002 T00301 2005-02-05
03003　　　　T00201　　　　2004-05-31
03004 T00101 2005-05-20
…….
BOOKS:
　　书名　　　　作者　　　图书登记号　　　
　数据结构　 　 李卫　　　T00101　　　　　　　
　数据结构概论 王海　　　T00102　　　　　　　
　数据结构导论　李卫　　 T00201　　　　　　
　数据通信　　　杨志　　 T00301　　　　　　　
C语言 陈成 T00302
英语题训 林明 T00401
　
A、查找“信管系”的学生
B、查找“刘诚”所借的书的书名，作者和借书日期
C、查找借了“数据结构概论”的学生借书证号 名字和系别
D、检索借了3本书以上的学生的借书证号，姓名，系名和借书数量。
E、检索借书和赵垒同学所借书中的任意一本相同的学生的姓名，系名，图书登记号，借书日期。

1、创建数据库

use master gocreate database lab1_libraryon(name= lab1_library, filename='d:\lab1_library.mdf', size =10, maxsize=50, filegrowth=5)log on( name = 'library_log', filename = 'D:\lab1_library_log.ldf', size=5mb, maxsize=25mb, filegrowth=5mb)go

2、创建表

create table borrowed(Scard_no varchar(20) not null primary key,Sname varchar(20),Depts varchar(50),geade varchar(20))create table books(book_name varchar(20),book_author varchar(20),book_no varchar(20) not null primary key)create table loans(Scard_no varchar(20),book_no varchar(20),borrow_date varchar(50),primary key(Scard_no,book_no),constraint borrowed_loans foreign key(Scard_no) references borrowed,constraint loans_books foreign key(book_no) references books)

A、查找“信管系”的学生

select * from borrowed where Depts='信管系'

B、查找“刘诚”所借的书的书名，作者和借书日期

select b.book_name,b.book_author,l.borrow_date from books b,borrowed bd,loans l whereb.book_no=l.book_no and bd.Scard_no=l.Scard_no and bd.Sname='刘诚'

C、查找借了“数据结构概论”的学生借书证号 名字和系别

select bd.Scard_no,bd.Sname,bd.Deptsfrom books b,borrowed bd,loans lwhere l.Scard_no=bd.Scard_noand b.book_no=l.book_noand b.book_name='数据结构概论'

D、检索借了3本书以上的学生的借书证号，姓名，系名和借书数量。

select l.Scard_no,Sname,Depts,count(*) as 借书数量from loans l,borrowed bdwhere l.Scard_no=bd.Scard_nogroup by l.Scard_no,Sname,Depts having count(*)>=3

E、检索借书和赵垒同学所借书中的任意一本相同的学生的姓名，系名，图书登记号，借书日期。

select Sname,Depts,book_no,borrow_datefrom borrowed,loanswhere borrowed.Scard_no=loans.Scard_no andborrowed.Sname<>'赵垒' andbook_no in(select loans.book_no from borrowed,loanswhere loans.Scard_no=borrowed.Scard_noand borrowed.Sname='赵垒')

二、.
1、现要为学校建立一个数据库，学校院系机构设置如下：
一个系有若干个专业，每个专业一年只招生一个班，
每个班有若干个学生，要求建立一个关于系，学生，班级的数据库，
关系模式如下：
学生STUDENT（学号，姓名，年龄，班号）
班CLASS（班号，专业名，系号，入学年份）
系DEPARTMENT（系号，系名）
请用SQL语句完成以下功能：
1、用查询分析器建表，要求声明每个表的主外码。
2、插入如下数据：
Student(
8101，张三，18,101；　　8102，钱四，16,121；
　8103，王玲，17,131；　　8105，李飞，19,102；
　8109，赵四，18,141；　8110，李可，20,111；
　
CLASS（
101，软件，002，1995;
102 微电子，002，1996;
111，无机化学，003，1995；　
121，统计数学，001，1995；
131，现代语言，004，1996；
　　141，国际贸易，005，1997；　
）
DEPARTMENT（001，数学；　002，计算机；
　　　　　　003，化学；　004，中文；
　　　　　　005，经济）

3、完成以下查询功能：
（1）、找出学号为“8110”学生所在的系名字

（2）找出所有姓李的学生，并按其年龄由小到大排序。

（3）、列出所有开设超过两个专业的系的名字。　

（4）、学生张三转到化学系111班，请更新相关的表。
（5）查询年龄最大的学生所在的系名

1、创建数据库

use mastergocreate database Lab2on(name = Lab2,filename = 'F:\数据库\作业2\2\Lab2.mdf',size=10,maxsize=50,filegrowth=5)log on(name='Lab2_log',filename='F:\数据库\作业2\2\Lab2_log',size=5mb,maxsize=25mb,filegrowth=5mb)go

2、创建表

create table department(Dept_no varchar(20) primary key,Dept_name varchar(20))create table class(class_no varchar(20) primary key,major_name varchar(50),Dept_no varchar(20),entrance_year varchar(20),constraint class_department foreign key(Dept_no) references department)create table Student(Sno varchar(20) primary key,Sname varchar(20),age int,class_no varchar(20),constraint Student_class foreign key(class_no) references class)

（1）、找出学号为“8110”学生所在的系名字

select Dept_namefrom Student,department,classwhere Student.class_no=class.class_noand department.dept_no=class.dept_noand Sno='8110'

（2）找出所有姓李的学生，并按其年龄由小到大排序。

select * from Studentwhere Sname like '李%'order by age

where Sname not like ‘李%’ —>不姓李的学生名字
下划线“_”代表一个字符.

（3）、列出所有开设超过两个专业的系的名字。　

select Dept_name from departmentwheredept_no in(select dept_nofrom classgroup by dept_no having count(*)>=2)

（4）、学生张三转到化学系111班，请更新相关的表。

update Studentset class_no='111'where sname='张三'

5）查询年龄最大的学生所在的系名

select Dept_namefrom departmentwhereDept_no=    (     select Dept_no from class     where class_no=        (            select class_no            from Student             where age=(select max(age) from Student )         )    )

三、、现有一个学生选修课程的数据库，其中存放以下三个表：
学生（学号，姓名，性别，年龄，系别）
课程（课程号，课程名，任课教师）；
选修（学号，课程号，分数）；请用SQL完成以下功能：
（1）、建表，在定义中要求声明：
　A、每个表的主外码。
（2）、插入如下数据：
　学生（101，张三，男，16，数学
　102，李四，男，18，计算机
　103，王玲，女，17，中文
　105，李飞，男，19，计算机
　109，赵四，女，18，历史
　110，李平，男，20，化学）
课程（203，操作系统，程羽
　　　279，高等数学，王备
　　　210，现代文学，王林
　　　243，有机化学，沈同
　　　204，数据结构，张青)
选修（101,203,82
　　　105,203,59
　　　102,279,90
　　　101,279,88
　　　105,279,82
　　　110,279,68
　　　109,210,72
　　　103,210,90
　　　110,243,92
　　　101,204,85
　　　105,204,91
　　　102,204,56）
（3）、用SQL语然完成下列查询：
　A、列出张三同学选修的所有课程的名称和成绩
　B、列出所有课程都及格了的同学的名字
　C、列出在选修张青老师所教授课程的学生中，成绩最高的学生姓名和成绩
（4）、删除成绩不及格的学生记录
（5）、将105号同学选修203号课程的成绩改为所有课程的平均成绩

1、创建数据库（略，同上）
2、创建表

create table Student(Sno varchar(20) primary key,Sname varchar(20),sex varchar(5),age int,dept varchar(20))create table course(Cno varchar(20) primary key,Cname varchar(20),Cteacher varchar(20))create table choiceCourse(Sno varchar(20),Cno varchar(20),primary key(Sno,Cno),grade int,constraint Student_cC foreign key(Sno) references Student,constraint course_cC foreign key(Cno) references course)

A、列出张三同学选修的所有课程的名称和成绩

select c.Cname,cC.gradefrom choiceCourse cC,Student s,course cwhere cC.Sno=s.Sno and c.Cno=cC.Cnoand Sname='张三'

*　B、列出所有课程都及格了的同学的名字 *

select Sname from Studentwhere Sno  not in(select Sno from choiceCoursewhere grade<60)

**C、列出在选修张青老师所教授课程的学生中，成绩最高的学生姓名和成绩 **select Sname,gradefrom Student s,choiceCourse cCwheres.Sno=cC.Snoand grade=(select max(grade) from ChoiceCourse where Cno=        (select Cno from Course where Cteacher='张青'))

（4）、删除成绩不及格的学生记录

delete from choiceCoursewhere grade<60

（5）、将105号同学选修203号课程的成绩改为所有课程的平均成绩

update choiceCourse set grade =(select avg(grade) from choiceCourse)where Sno='105'and Cno='203'

四、现有图书管理数据库的三个关系模式：
图书（总编号，书名，出版单位，单价）
读者 （借书证号，单位，姓名，性别，）
借阅 （借书证号，总编号，借书日期）
1． 创建图书管理库和图书、读者和借阅三个基本表的表结构：
2． 分别插入以下所给元组：
图书：
总编号 书名 出版单位 单价
445501 数据库导论 科学出版社 17.90
445503 数据库导论 科学出版社 17.90
112266 FoxBASE 科学出版社 23.60
665544 高等数学 高等教育出版社 20.00
446603 数据库基础 电子工业出版社 22.50
449901 FoxPro教程 高等教育出版社 32.70
449902 FoxPro大全 北京大学出版社 32.70
118801 计算机网络 清华大学出版社 21.80

读者：
借书证号 单位 姓名 性别
111 信息系 王维利 女
112 财会系 李 立 男
113 经济系 张 三 男
114 信息系 周华发 男
115 信息系 赵正义 男
116 信息系 李 明 男
117 计算机系 李小峰 男
118 计算机系 许鹏飞 男
119 计算机系 刘大龙 男

借阅：
借书证号 总编号 借书日期
112 445501 1997-3-19
111 445503 1997-8-21
112 112266 1997-3-14
114 665544 1997-10-21
119 446603 1997-12-12
112 449901 1997-10-23
115 449902 1997-8-21
118 118801 1997-9-10

用SQL完成如下查询：
1)找出姓李的读者姓名和所在单位。
2) 查找价格介于20元和30元之间的图书种类，结果按单价升序排序。
3)查找书名以“计算机“打头的所有图书和作者。
4) 检索同时借阅了总编号为112266和449901两本书的借书证号。
5)找出借阅了FoxPro大全一书的读者信息。

创建数据库和表同上，略
1)找出姓李的读者姓名和所在单位。

select Sname,deptfrom readerwhere Sname like '李%'

2) 查找价格介于20元和30元之间的图书种类，结果按单价升序排序。

select book_name,pricefrom bookwhere price between 20 and 30 order by price 

3)查找书名以“计算机“打头的所有图书和作者。

select book_name,book_pressfrom bookwhere book_name like '计算机%'

4) 检索同时借阅了总编号为112266和449901两本书的借书证号。

自身连接：

select A.Scard_nofrom borrow A,borrow Bwhere A.Scard_no=B.Scard_no and A.book_no ='112266' andB.book_no= '449901

5)找出借阅了FoxPro大全一书的读者信息。

select reader.Scard_no,Sname,dept,sex from reader,borrow,book where reader.Scard_no=borrow.Scard_no and borrow.book_no=book.book_no and book.book_name='FoxPro大全'

五、已知一个关系数据库的模式如下：
职工EMP（职工号，姓名，工资，所在部门）
部门DEPT（部门号，部门名，部门经理的职工号）
（1）、使用查询分析器建表，要求如下：
　　A、每个表的主外码。
　　B、职工姓名和部门名不能为空。
　　C、工资取800到5000这间整数。
（2）、插入如下数据：
职工（E01，汤姆，4000，D02
　 　E02，丹尼，3000，D02
　　E03，约韩，1000，D01
　　E04，迪克，5000,D01
E05,比尔，3500，D03
E06,米妮，4500 ,D04
E07，威克，4200，D02
E08，希拉，4300，D02
E09，亚瑟，3500，D02
E10，迪尼，3000，D02
E11,艾米，4200，D01
E12，汤尼，3500,D01
E13,迪尔，3800，D03
E14,比尔，4400，D03
E15,菲亚，4600 ,D04
E16,安西，4700 ,D04）
部门（D01，人事，E04
　　　D02，信息，E01
　　　D03，销售，E05
　　　D04，财务，E06）
（3）查询信息部门的职工工资为3500到4000的职工号与名字
（4）查询名字以“迪”字为首字的所有职工所有信息以及在的部门名字。
（5）查询每个部门号的职工人数，并按人数降序排列，人数相同的，按部门号升序排列。
（6）、查询：找出那些工资高于其所在部门经理工资的职工的姓名，部门名称，和工资。
（7）、用SQL完成：将人事部门经理的工资改为该部门所有职工的平均工资。
（8）、新增加一个广告部门，编号为D06。

建表

create table DEPT(Dno varchar(20) primary key,Dname varchar(20),Eno varchar(20))create table EMP(Eno varchar(20) primary key,Ename varchar(20),Ewage float,Dno varchar(20),constraint DEPT_EMP foreign key(Dno) references DEPT)

（3）查询信息部门的职工工资为3500到4000的职工号与名字

select Eno,Ename from EMPwhere Ewage between 3500 and 4000

（4）查询名字以“迪”字为首字的所有职工所有信息以及在的部门名字。

select EMP.Eno,Ename,Ewage,Dnamefrom EMP,DEPTwhere EMP.Dno=DEPT.Dno andEname like '迪%'

（5）查询每个部门号的职工人数，并按人数降序排列，人数相同的，按部门号升序排列。

select EMP.Dno,count(*) as 职工人数from EMP,DEPTwhere EMP.Dno=DEPT.Dnogroup by EMP.Dno,EMP.Dno order by count(*) desc

（6）找出每个部门工资最高的员工姓名和工资

select Ename,Ewage ,Dno from EMP ewhere Ewage=(select max(Ewage) from EMP where Dno=e.Dno) order by Dno

（7）、查询：找出那些工资高于其所在部门经理工资的职工的姓名，部门名称，和工资。

（8）、用SQL完成：将人事部门经理的工资改为该部门所有职工的平均工资。

update EMP set Ewage=(select avg(Ewage) as 平均工资 from EMPwhere Dno=(select Dno from DEPT where Eno='E04'))where Eno='E04'

（9）、新增加一个广告部门，编号为D06。

insert into DEPT values('D06','广告','')

groupby的使用：(http://www.cnblogs.com/rainman/archive/2013/05/01/3053703.html)

groupby和having的用法解析：http://www.cnblogs.com/wang-123/archive/2012/01/05/2312676.html