数据库技术及应用必考源码

创建数据库

Create database stud

On

Primary

( name=stud,

Filename=’d:\stud.maf’, Size=100mb,

Maxsize=200,

Filegrowth=20)

Log on

(name=stud_log, Filename=’d:\stud_log.ldf’, Size=100mb,

Maxsize=200,

Filegrowth=20)

创建表

Use stud

Create teble student (stuid char(13) primary key, Stuname nvarchar(8), Stusex nvarchar(2),

Stubir samlldatetime, Stuaddr nvarchar(30), Claid char(10))

表中增加备注列

Use stud

Alter table student

Alter column stuid char(8) 插入

Use stud

Insert into

student(stuid,stuname,claid) Values(‘054201’,’小名’,’210’) 查询博士信息资料

Use stud

Select * into dr_tea from teacher

Where teadeg=’博士’

Go

Select * from dr_tea

查询女教师信息

Use stud

Select * from teacher Where teasex=’女’第四位是4的系部信息

Use stud

Select * from department

Where depid like’___4%’

出生日期先后女同学

Use stud

Select stuname,stubir

From student

Where stusex=’女’

Order by stubir

成绩学号升序

Use stud

Select * from result

Order by stuid,result desc

学分总和

Use stud

Select sun(coucre)

From course

每个专业班级数

Use stud

Select subject as 专业名称,

Count(claname) as 班级数

From class

Group by subject

除计科外专业班级数

Use stud

Select subject as 专业名称,

Count(claname) as 班级数

From class

Where subject<>’计科’

Group by subject

男生超过5人

Use stud

Select claid from student

Where stusex=’男’

Group by claid

Having count(*)>=5

所有学生平均成绩

Ust stud

Select * from result

Compute avg(result)

每个学生所选课程成绩

Ust stud

Select

student,stuid,student.stuna

me,result.couid,result.result

From student,result

Where

student.stuid=result.stuid

查询系部和班级信息

Ust stud

Sekect department.*,

Class.claid,class.claname

From department inter join

class

On

department.depid=class.dep

id

右外

Use stud

Select

result.stuid,course.couid,

Course.couname,result.resul

t

From course

Right join

Result on

course.couid=result.couid

超过平均学分的课程

Ues stud

Select couname,coucre

From course

Where coucre>(select

avg(coucre)from course)

学分超过4的成绩

Use stud

Select stuid,result,couid

From result

Where result.couid

In(select couid

From course

Where coucre>4)