SQL数据定义与数据查询Word文档下载推荐.docx

SQL数据定义与数据查询Word文档下载推荐.docx

《SQL数据定义与数据查询Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《SQL数据定义与数据查询Word文档下载推荐.docx（24页珍藏版）》请在冰豆网上搜索。

）;

建立Course表：

CREATETABLECourse

（CnoCHAR（4）PRIMARYKEY,

CnameCHAR（40）UNIQUE,

CpnoCHAR（4）,

CreditSMALLINT,

CONSTRAINTFK_CFOREIGNKEY（Cpno）REFERENCESCourse（Cno）

）;

建立Teacher表：

CREATETABLETeacher

（TnoCHAR（4）PRIMARYKEY,

TnameCHAR（40）,

DepartmentCHAR（20）,

EmailCHAR（100）,

SalaryINT

建立SC表：

CREATETABLESC

（SnoCHAR（9）,

CnoCHAR（4）,

GradeSMALLINT,

PRIMARYKEY（Sno,Cno）,

CONSTRAINTFK_SC1FOREIGNKEY（Sno）REFERENCESStudent（Sno）,

CONSTRAINTFK_SC2FOREIGNKEY（Cno）REFERENCESCourse（Cno）

（3）修改数据表：

✓将SC表中Grade的数据类型改为float；

ALTERTABLESCALTERCOLUMNGradeFLOAT;

✓在Student表中增加一个属性Scome记录学生的入学时间，其数据类型为DATETIME；

ALTERTABLEStudentADDScomeDATETIME;

✓删除Student表中新增加Scome属性；

ALTERTABLEStudentDROPCOLUMNScome;

✓删除表Teacher表中的Email属性；

ALTERTABLETeacherDROPCOLUMNEmail;

✓删除Student表中对Sname属性创建的Unique约束。

（无法删除未建立名字的约束）

建立约束语句：

ALTERTABLEStudentADDCONSTRAINTS_CHECKUNIQUE（Sname）;

删除约束语句：

ALTERTABLEStudentDROPS_CHECK;

*（4）用SQL语言分别在3个数据表中插入数据（具体数据可见教材P.82）：

INSERT

INTOStudent

VALUES（'

200215121'

'

李勇'

男'

20,'

CS'

200215122'

刘晨'

女'

19,'

200215123'

王敏'

18,'

MA'

200215125'

张立'

IS'

INTOCourse

1'

数据库'

5'

4）;

2'

数学'

NULL,2）;

3'

信息系统'

4'

操作系统'

6'

3）;

数据结构'

7'

数据处理'

PALCAL语言'

INTOSC

92）;

85）;

88）;

90）;

80）;

（5）创建索引：

✓在Student表上创建按sage降序排列的索引NON_Stusage；

CREATEINDEXNON_StusageONStudent（SageDESC）;

✓在Student表上为Sno列创建一个聚簇索引CLU_Stusno；

CREATECLUSTEREDINDEXCLU_StusnoONStudent（Sno）;

✓在Student表上为Sname列创建一个聚簇索引CLU_Stusname；

（能否创建成功？

为什么？

）不能创建，因为已经存在一个聚簇索引，一个基本表中只能存在一个聚簇索引。

✓在Course表上创建按ccredit升序排列的唯一性索引；

CREATEUNIQUEINDEXUNI_CcreONCourse（Ccedit）;

✓在SC表上按Sno升序和Cno降序建立唯一索引UN_SC。

CREATEUNIQUEINDEXUN_SCONSC（Sno,CnoDESC）;

（6）删除索引CLU_Stusno、UN_SC：

DROPINDEXStudent.CLU_Stusno;

DROPINDEXSC.UN_SC

（7）删除Teacher表

DROPTABLETeacher;

（8）保存SQL文件，文件名：

SC-姓名学号后四位.sql，如SC-xukai3112.sql

（二）使用命令行方式建立SPJ数据库

（1）用SQL语言建立SPJ数据库，数据库命名：

SPJBASE-学号后四位及姓名，如SPJBASE-3112liyan；

CREATEDATABASESPJBASE_1224cxj

（NAME=SPJBASE_1224cxj_dat,

\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\SC_1214fanglingzhi_dat.mdf'

（NAME=SPJBASE_1224cxj_log,

\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\SC_1214zhuyameng_log.ldf'

（2）用SQL语言在其中建立S表、P表、J表和SPJ表（要求进行完整性定义），表结构如下；

厂家S（SNO，SNAME，STATUS，CITY），其中STATUS（状态）为整型，其他均为字符型

产品P（PNO，PNAME，COLOR，WEIGHT），其中WEIGHT（重量）为浮点型，其他均为字符型

工程J（JNO，JNAME，CITY），其中各属性均为字符型

供应SPJ（SNO，PNO，JNO，QTY），其中QTY（供应量）为整型，其他均为字符型

建立S表：

CREATETABLESs

（SNOCHAR（9）PRIMARYKEY,

SNAMECHAR（40）,

STATUSINT,

CITYCHAR（20）

建立P表：

CREATETABLEP

（PNOCHAR（9）PRIMARYKEY,

PNAMECHAR（40）,

COLORCHAR（20）,

WEIGHTFLOAT

建立J表：

CREATETABLEJ

（JNOCHAR（9）PRIMARYKEY,

JNAMECHAR（40）,

建立SPJ表：

CREATETABLESPJ

（SNOCHAR（9）,

PNOCHAR（9）,

JNOCHAR（9）,

QTYINT,

PRIMARYKEY（SNO,PNO,JNO）,

CONSTRAINTFK_SPJ1FOREIGNKEY（SNO）REFERENCESS（SNO）,

CONSTRAINTFK_SPJ2FOREIGNKEY（PNO）REFERENCESP（PNO）,

CONSTRAINTFK_SPJ3FOREIGNKEY（JNO）REFERENCESJ（JNO）

*（3）用SQL语言分别在4个数据表中输入一定的记录；

省略

（4）保存SQL文件，文件名：

SPJ-姓名学号后四位.sql，如SPJ-xukai3112.sql

（三）针对学生课程数据库查询

（1）查询全体学生的学号与姓名。

SELECTSno,Sname

FROMStudent;

（2）查询全体学生的姓名、学号、所在系。

SELECTSname,Sno,Sdept

（3）查询全体学生的详细记录。

SELECT*

（4）查全体学生的姓名及其出生年份。

SELECTSname,Sage

（5）查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。

SELECTSname,Sage,LOWER（Sdept）

（6）使用列别名改变查询结果的列标题。

SELECTSnameNAME,'

YearofBirth:

'

BIRTH,2000-SageBIRTHDAY,LOWER（Sdept）DEPARTMENT

（7）查询选修了课程的学生学号。

SELECTDISTINCTSno

FROMSC;

（8）查询所有年龄在20岁以下的学生姓名及其年龄。

SELECTSname,Sage

FROMStudent

WHERESage<

20;

（9）查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

SELECTSname,Sdept,Sage

FROMStudent

WHERESageBETWEEN20AND23;

（10）查询年龄不在20~23岁之间的学生姓名、系别和年龄。

WHERESageNOTBETWEEN20AND23;

（11）查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECTSname,Ssex

WHERESdeptIN（'

（12）查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

WHERESdeptNOTIN（'

（13）查询所有姓刘学生的姓名、学号和性别。

SELECTSname,Sno,Ssex

WHERESnameLIKE'

刘%'

;

（14）查询学号为20080711的学生的详细情况。

（具体的学号值根据表中数据确定）

SELECT*

WHERESno='

20060701'

（15）查询姓“欧阳”且全名为三个汉字的学生姓名。

SELECTSname

欧阳__'

（16）查询名字中第2个字为“晨”字的学生的姓名和学号。

SELECTSname,Sno

__晨%'

（17）查询所有不姓刘的学生姓名。

WHERESnameNOTLIKE'

（18）查询DB_Design课程的课程号和学分。

SELECTCno,Ccredit

FROMCourse

WHERECnameLIKE'

DB\_Design'

ESCAPE'

\'

（19）查询以"

DB_"

开头，且倒数第3个字符为i的课程的详细情况。

DB\_%i__'

（20）查询缺少成绩的学生的学号和相应的课程号。

SELECTSno,Cno

FROMSC

WHEREGradeISNULL;

（21）查所有有成绩的学生学号和课程号。

WHEREGradeISNOTNULL;

（22）查询计算机系年龄在20岁以下的学生姓名。

WHERESdept='

ANDSage<

（23）查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

（使用多个条件表达式）

ORSdept='

（24）查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

WHERESage>

=20ANDSage<

=23;

（25）查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

SELECTSno,Grade

WHERECno='

ORDERBYGradeDESC;

（26）查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

ORDERBYSdept,SageDESC;

（27）查询年龄最大的前三个学生的姓名。

SELECTTop3Sname

OrderbySageDESC;

（28）查询学生总人数。

SELECTCOUNT（*）

（29）查询选修了课程的学生人数。

SELECTCOUNT（DISTINCTSno）

（30）计算1号课程的学生平均成绩。

SELECTAVG（Grade）

（31）查询选修1号课程的学生最高分数。

SELECTMAX（Grade）

（32）求各个课程号及相应的选课人数。

SELECTCno,COUNT（Sno）

GROUPBYCno;

（33）查询选修了3门以上课程的学生学号。

SELECTSno

GROUPBYSno

HAVINGCOUNT（*）>

3;

（34）查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数。

SELECTSno,COUNT（*）

WHEREGrade>

=90

HAVINGCOUNT（*）>

=3;

（35）查询学生20060711选修课程的总学分。

SELECTSum（Ccredit）

FROMSC,Course

20060711'

AND

SC.Cno=Course.Cno;

（36）查询每个学生选修课程的总学分。

WHERESC.Cno=Course.Cno

GROUPBYSno;

（37）查询每个学生及其选修课程的情况。

SELECTStudent.*,SC.*

FROMStudent,SC

WHEREStudent.Sno=SC.Sno;

（38）查询每一门课的间接先修课（即先修课的先修课）。

SELECTFIRST.Cno,SECOND.Cpno

FROMCourseFIRST,CourseSECOND

WHEREFIRST.Cpno=SECOND.Cno;

（39）查询每个学生及其选修课程的情况包括没有选修课程的学生（用外连接）。

SELECTStudent.*,SC.*

FROMStudentjoinSConStudent.Sno=SC.Sno;

（40）查询选修2号课程且成绩在90分以上的所有学生的学号、姓名。

SELECTStudent.Sno,Student.Sname

FROMStudent,SC

WHEREStudent.Sno=SC.SnoANDSC.Cno='

ANDSC.Grade>

90;

（41）查询每个学生的学号、姓名、选修的课程名及成绩。

SELECTStudent.Sno,Sname,Cname,Grade

FROMStudent,SC,Course

WHEREStudent.Sno=SC.SnoAND

（42）查询与“刘晨”在同一个系学习的学生（分别用嵌套查询和连接查询）。

嵌套查询：

SELECTSno,Sname,Sdept

WHERESdept=

（SELECTSdept

FROMStudent

WHERESname='

连接查询：

FROMStudentS1,StudentS2

WHERES1.Sdept=S2.SdeptANDS2.Sname='

（43）查询选修了课程名为“信息系统”的学生学号和姓名。

SELECTS1.Sno,S1.Sname,S1.Sdept

SELECTSno,Sname

WHERESnoIN

（SELECTSno

FROMSC

WHERECnoIN

（SELECTCno

FROMCourse

WHERECname='

））;

（44）查询其他系中比信息系任意一个（其中某一个）学生年龄小的学生姓名和年龄。

SELECTSname,Sage

ANY

（SELECTSage

FROMStudent

WHERESdept='

ANDSdept<

>

（45）查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。

分别用ALL谓词和集函数。

ALL谓词：

ALL（SELECTSage

WHERESdept='

集函数：

（SELECTMIN（Sage）

ANDSdept<

（46）查询所有选修了1号课程的学生姓名。

（分别用嵌套查询和连查询）

SELECTSname

WHERECno='

SELECTStudent.Sname

WHEREStudent.Sno=SC.SnoANDC.Cno='

（47）查询没有选修1号课程的学生姓名。

WHERENOTEXISTS

（SELECT*

FROMSC

WHERESno=Student.SnoAND

Cno='

（48）查询选修了全部课程的学生姓名。

FROMCourse

WHERENOTEXISTS

（SELECT*

FROMSC

WHERESC.Sno=Student.SnoAND

Cno=Course.Cno））;

（49）查询至少选修了学生95002选修的全部课程的学生号码。

SELECTDISTINCT