在SQLserver中创建数据库和表的实例及相关的SQL查询语句.docx
- 文档编号:9289832
- 上传时间:2023-02-04
- 格式:DOCX
- 页数:11
- 大小:16.87KB
在SQLserver中创建数据库和表的实例及相关的SQL查询语句.docx
《在SQLserver中创建数据库和表的实例及相关的SQL查询语句.docx》由会员分享,可在线阅读,更多相关《在SQLserver中创建数据库和表的实例及相关的SQL查询语句.docx(11页珍藏版)》请在冰豆网上搜索。
在SQLserver中创建数据库和表的实例及相关的SQL查询语句
在SQLserver2000中创建数据库和表的实例及相关的SQL查询语句.txt一个人一盒烟一台电脑过一天一个人一瓶酒一盘蚕豆过一宿。
永远扛不住女人的小脾气,女人永远抵不住男人的花言巧语。
CREATEDATABASE库存管理
go
USE库存管理
GO
CREATETABLE仓库
(仓库号CHAR(5)PRIMARYKEY,
城市CHAR(10),
面积INTCHECK(面积>0))
GO
CREATETABLE职工(
仓库号CHAR(5)FOREIGNKEYREFERENCES仓库,
职工号CHAR(5)PRIMARYKEY,
工资INTCHECK(工资>=1000AND工资<=5000)DEFAULT1200)
GO
CREATETABLE供应商(
供应商号CHAR(5)PRIMARYKEY,
供应商名CHAR(20),
地址CHAR(20))
GO
CREATETABLE订购单(
职工号CHAR(5)NOTNULLFOREIGNKEYREFERENCES职工,
供应商号CHAR(5)NULLFOREIGNKEYREFERENCES供应商,
订购单号CHAR(5)PRIMARYKEY,
订购日期DATETIMEDEFAULTGetDate())
GO
INSERTINTO仓库VALUES('WH1','北京',370)
INSERTINTO仓库VALUES('WH2','上海',500)
INSERTINTO仓库VALUES('WH3','广州',200)
INSERTINTO仓库VALUES('WH4','武汉',400)
GO
INSERTINTO职工VALUES('WH2','E1',1220)
INSERTINTO职工VALUES('WH1','E3',1210)
INSERTINTO职工VALUES('WH2','E4',1250)
INSERTINTO职工VALUES('WH3','E6',1230)
INSERTINTO职工VALUES('WH1','E7',1250)
GO
INSERTINTO供应商VALUES('S3','振华电子厂','西安')
INSERTINTO供应商VALUES('S4','华通电子公司','北京')
INSERTINTO供应商VALUES('S6','607厂','郑州')
INSERTINTO供应商VALUES('S7','爱华电子厂','北京')
GO
INSERTINTO订购单VALUES('E3','S7','OR67','2002-06-23')
INSERTINTO订购单VALUES('E1','S4','OR73','2002-07-28')
INSERTINTO订购单VALUES('E7','S4','OR76','2002-05-25')
INSERTINTO订购单VALUES('E6',NULL,'OR77',NULL)
INSERTINTO订购单VALUES('E3','S4','OR79','2002-06-13')
INSERTINTO订购单VALUES('E1',NULL,'OR80',NULL)
INSERTINTO订购单VALUES('E3',NULL,'OR90',NULL)
INSERTINTO订购单VALUES('E3','S3','OR91','2002-07-13')
GO
select*from仓库
select*from职工
select*from订购单
select*from供应商
select供应商名from供应商where地址='北京'
select订购单号from订购单where供应商号='S6'
select*from订购单where职工号='E6'and供应商号='S6'
select职工号,仓库号from职工where职工号in(
select职工号from订购单where供应商号='S3')
select*from职工where职工号notin(
select职工号from订购单where供应商号='S3')
select*from供应商where供应商号notin(
select供应商号from订购单where供应商号isnotnull)
select*from供应商where地址='北京'and供应商号in(
select供应商号from订购单where职工号='E1')and供应商号in(
select供应商号from订购单where职工号='E3')
select工资from职工where职工号in(
select职工号from订购单Awhere供应商号in(
select供应商号from供应商where供应商名='华通电子公司'))
select供应商名from供应商where供应商号notin(
select供应商号from订购单where供应商号isnotnulland职工号in(
select职工号from职工where工资<1220))
select城市from仓库where仓库号in(
select仓库号from职工where职工号in(
select职工号from订购单where供应商号='S4'))
select职工号from职工where仓库号in(
select仓库号from仓库where城市='上海')and职工号in(
select职工号from订购单where供应商号='S6')
select职工号from职工where仓库号in(
select仓库号from仓库where城市='广州')and职工号in(
select职工号from订购单where供应商号='S6')and职工号notin(
select职工号from订购单where供应商号!
='S6')
select职工号from职工where仓库号in(
select仓库号from仓库where城市='北京')andexists(
select*from订购单where供应商号='S4'and职工号=职工.职工号)andnotexists(
select*from订购单where供应商号!
='S4'and职工号=职工.职工号)
select职工号from职工where仓库号in(
select仓库号from仓库where城市='北京')and职工号in(
select职工号from订购单where供应商号='S4')and职工号notin(
select职工号from订购单where供应商号!
='S4')
select订购单号from订购单where供应商号in(
select供应商号from供应商where地址='北京')and职工号in(
select职工号from职工where工资>1230)
selectcount(distinct仓库号)from仓库
select*from仓库where面积=(select(max)面积from仓库)
selectavg(面积)平均面积from仓库
selectavg(面积)from仓库where仓库号in(
select仓库号from职工where职工号in(
select职工号from订购单where供应商号='s4'))
select地址,count(distinct供应商号)from供应商groupby地址
select仓库号,count(distinct职工号)from职工where工资>1220groupby仓库号
selectcount(distinct供应商号)from订购单where职工号in(
select职工号from职工where仓库号in(
select仓库号from仓库where面积=(
selectmin(面积)from仓库)))
selectcount(distinct供应商号)from订购单where职工号in(
select职工号from职工where仓库号in(
select仓库号from仓库where面积<=all(
select面积from仓库)))
select*from职工Xwhere工资<(
selectAVG(工资)from职工YwhereX.仓库号=Y.仓库号)
createdatabase教学管理
GO
USE教学管理
GO
CREATETABLES(
[S#]varchar(20)primarykey,
SNamevarchar(20)notnull,
Sexvarchar
(2)default'男',
Ageint)
GO
CREATETABLEC(
[C#]varchar(20)primarykey,
CNamevarchar(20)notnullunique,
Teachervarchar(20))
GO
CREATETABLESC(
[S#]varchar(20)notnull,
[C#]varchar(20)notnull,
Gradeintdefault0check(gradeisnull
orGradebetween0and100),
primarykey(S#,C#),
constraintFK_SC_Sforeignkey([S#])
referencesS([S#])ondeletecascade
onupdatecascade,
constraintFK_SC_Cforeignkey([C#])
referencesC([C#])ondeletecascade
onupdatecascade)
GO
INSERTINTOS([S#],SName,Age,Sex)
VALUES('S1','李强',23,'男')
INSERTINTOS([S#],SName,Age,Sex)
VALUES('S2','刘丽',22,'女')
INSERTINTOS([S#],SName,Age,Sex)
VALUES('S3','张友',22,'男')
INSERTINTOS([S#],SName,Age,Sex)
VALUES('S4','王芳',21,'女')
INSERTINTOS([S#],SName,Age,Sex)
VALUES('S5','王海',21,'男')
INSERTINTOC([C#],CName,Teacher)
VALUES('C1','C语言','王华')
INSERTINTOC([C#],CName,Teacher)
VALUES('C2','数据库原理','程军')
INSERTINTOC([C#],CName,Teacher)
VALUES('C3','编译原理','程军')
INSERTINTOSC([S#],[C#],Grade)
VALUES('S1','C1',83)
INSERTINTOSC([S#],[C#],Grade)
VALUES('S1','C2',70)
INSERTINTOSC([S#],[C#],Grade)
VALUES('S1','C3',90)
INSERTINTOSC([S#],[C#],Grade)
VALUES('S2','C1',85)
INSERTINTOSC([S#],[C#],Grade)
VALUES('S2','C2',84)
INSERTINTOSC([S#],[C#],Grade)
VALUES('S3','C1',82)
INSERTINTOSC([S#],[C#],Grade)
VALUES('S3','C2',90)
INSERTINTOSC([S#],[C#],Grade)
VALUES('S3','C3',95)
INSERTINTOSC([S#],[C#],Grade)
VALUES('S5','C1',85)
INSERTINTOSC([S#],[C#],Grade)
VALUES('S5','C3',84)
select*fromS
select*fromc
select*fromsc
selectcnamefromcwherec#notin(
selectc#fromscwheres#in(
selects#fromswheresname='王海'))
selects#,snamefromswherenotexists(
select*fromcwhereteacher='程军'andnotexists(
select*fromscwheres.s#=sc.s#andsc.c#=c.c#))
selects#,snamefromswheresex='女'ands#in(
selects#fromscwherec#in(
selectc#fromcwherecname='c语言')andc#notin(
selectc#fromcwherecname!
='c语言'))
selectc#,cnamefromcwhereteacher='程军'
selects#,snamefromswheresex='男'andage>21
selectsnamefromswherenotexists(
select*fromcwhereteacher='程军'andnotexists(
select*fromscwheres.s#=sc.s#andsc.c#=c.c#))
selectc#fromcwherec#notin(
selectc#fromscwheres#in(
selects#fromswheresname='李强'))
selectc#fromcwherenotexists(
select*fromscwherec.c#=sc.c#ands#in(
selects#fromswheresname='李强'))
selectdistinctx.s#fromscx,scywherex.s#=y.s#andx.c#!
=y.c#
selects#fromscgroupbys#havingcount(*)>=2
selectc#,cnamefromcwherenotexists(
select*fromswherenotexists(
select*fromscwheres.s#=sc.s#andc.c#=sc.c#))
selectdistincts#fromscwherec#in(
selectc#fromcwhereteacher='程军')
selectx.s#fromscx,scywherex.s#=y.s#andx.c#='c1'andy.c#='c5'
selects#fromswheres#in(
selects#fromscwherec#='c1')ands#in(
selects#fromscwherec#='c5')
selectsnamefromswherenotexists(
select*fromcwherenotexists(
select*fromscwheresc.c#=c.c#andsc.s#=s.s#))
selects#fromswherenotexists(
select*fromscxwheres#='s2'andnotexists(
select*fromscywheres.s#=y.s#andx.c#=y.c#))
selects#,snamefromswheres#in(
selects#fromscwherec#in(
selectc#fromcwherecname='c语言'))
selects#,snamefromswheres#notin(
selects#fromscwherec#in(
selectc#fromcwherecname='c语言'))
selects#,count(distinctc#)as选课门数,avg(grade)as平均分,max(grade)as最高分,min(grade)as最低分fromscgroupbys#
selectcnamefromcwherenotexists(
select*fromswheresex='男'andnotexists(
select*fromscwheres.s#=sc.s#andc.c#=sc.c#))
selects#,snamefromswhereexists(
selects#fromscxwheres.s#=x.s#andexists(
selects#,min(grade)fromscywherex.s#=y.s#groupbys#havingmin(grade)>80))
selects#,snamefromswheres#notin(
selects#fromscwheres.s#=sc.s#andgrade<=80)ands#in(selects#fromsc)
selects#,snamefromswhereexists(
selects#fromscxwheres.s#=x.s#andexists(
selects#,count(distinctc#)fromscywherex.s#=y.s#groupbys#havingcount(distinctc#)>=all(
selectcount(distinctc#)fromscgroupbys#)))
selects#,snamefromswheres#notin(
selects#fromscxwheregrade<(
selectavg(grade)fromscywherex.c#=y.c#groupbyc#))
ands#in(selects#fromsc)
selects#fromswhereexists(
selects#,count(distinctc#)fromscwheres.s#=sc.s#andgrade>60groupbys#havingcount(distinctc#)>=2)
selectcname,teacherfromsc,cwheresc.c#=c.c#ands#='s1'
selectsnamefroms,sc,cwheresc.c#=c.c#andsc.s#=s.s#andsex='女'andteacherlike'王%'
selectsnamefromswheresex='女'ands#in(
selects#fromscwherec#in(
selectc#fromcwhereteacherlike'王%'))
selectsnamefromswheresex='女'andexists(
select*fromscwheres.s#=sc.s#andexists(
select*fromcwheresc.c#=c.c#andteacherlike'王%'))
selectcnamefromcwherec#notin(
selectc#froms,scwheres.s#=sc.s#andsc.c#=c.c#andsnamelike'王%')
selectcnamefromcwherenotexists(
select*froms,scwheres.s#=sc.s#andsc.c#=c.c#andsnamelike'王%')
selectdistincts#fromscxwherenotexists(
select*fromcwhereteacherlike'程%'andnotexists(
select*fromscywherey.s#=x.s#andy.c#=c.c#))
selects#,snamefromswherenotexists(
select*fromcwhereteacher='程军'andnotexists(
select*fromscwheres.s#=sc.s#andsc.c#=c.c#))
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQLserver 创建 数据库 实例 相关 SQL 查询 语句
![提示](https://static.bdocx.com/images/bang_tan.gif)