SQL基础练习

create database stu_db --创建数据库Students
on primary --默认就属于Primary 主文件组
(
Name = ‘stu_db’, --主数据库文件的逻辑名
Filename = ‘H:\SQL\stu_db.mdf’, --主数据库文件的物理名
Size = 5MB, --主数据库文件的初始大小
MaxSize = 50MB, --主数据库文件的增长最大值
FileGrowth = 10% --主数据库文件的增长率

)log on
(
–日志文件的具体描述，个参数含义同上
Name = ‘stu_db_log’,
FileName = ‘H:\SQL\stu_db_log.ldf’,
Size = 1MB,
FileGrowth = 1MB

)
GO

use stu_db
GO
drop table course
drop table Score
drop table student
drop table teacher

create table student
(
sno int primary key,
sname char(40) not null,
ssex char(2) not null ,
sbirthday datetime,
class int

)
create table teacher
(
tno int primary key,
tname char(20),
tsex char(2),
tbirthday datetime,
prof char(10),
depart char(20)
)

create table course
(
cno char(10) primary key,
cname char(20) not null ,
tno int foreign key(tno) references teacher(tno)
)

create table Score
(
sno int foreign key (sno) references student(sno) ,
cno char(10) foreign key(cno) references course(cno),
score int not null,
primary key(sno,cno)
)

insert into student values (108,‘曾华’,‘男’,‘1905-05-22’,95033)
insert into student values (105,‘匡明’,‘男’,‘1905-05-18’,95031)
insert into student values (107,‘王丽’,‘女’,‘1905-05-7’,95033)
insert into student values (101,‘李军’,‘男’,‘1905-05-9’,95033)
insert into student values (109,‘王芳’,‘女’,‘1905-05-18’,95031)
insert into student values (103,‘陆君’,‘男’,‘1905-05-20’,95031)
–insert into student values (108,‘曾华’,‘男’,‘1905-05-18’,95033)

insert into teacher values(804,‘李成’,‘男’,‘1958-12-2’,‘副教授’,‘计算机系’)
insert into teacher values(856,‘张旭’,‘男’,‘1969-03-12’,‘讲师’,‘电子工程系’)
insert into teacher values(825,‘王萍’,‘女’,‘1972-05-05’,‘助教’,‘计算机系’)
insert into teacher values(831,‘刘冰’,‘女’,‘1972-8-14’,‘助教’,‘电子工程系’)
insert into teacher values(100,‘刘欢’,‘女’,‘1958-12-2’,‘助教’,‘电子工程系’)

insert into course values(‘3-105’,‘计算机导论’,825)
insert into course values(‘3-245’,‘操作系统’,804)
insert into course values(‘6-166’,‘数据电路’,856)
insert into course values(‘9-888’,‘高等数学’,100)

insert into Score values(103,‘3-245’,86)
insert into Score values(105,‘3-245’,75)
insert into Score values(109,‘3-245’,68)
insert into Score values(103,‘3-105’,92)
insert into Score values(105,‘3-105’,88)
insert into Score values(109,‘3-105’,76)
insert into Score values(101,‘3-105’,64)
insert into Score values(107,‘3-105’,91)
insert into Score values(108,‘3-105’,78)
insert into Score values(101,‘6-166’,85)
insert into Score values(107,‘6-106’,79)

–1、查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname,Ssex,Class from student

–2、查询教师所有的单位即不重复的Depart列。
select distinct Depart from teacher

–3、查询Student表的所有记录。
select * from Student

–4、查询Score表中成绩在60到80之间的所有记录。
select * from score where score between 60 and 80

–5、查询Score表中成绩为85，86或88的记录。
select * from Score where score in(85,86,88)

–6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from Student where Class = 95031 or SSex = ‘女’

–7、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select sno from Score where cno=‘3-105’ and score>(
select score from Score where sno=‘109’ and cno=‘3-105’)

–8、查询出学生信息表里的前5条数据
select top 5 * from student

–9、查询出学生成绩表里面的所有记录，给原字段设置字段别名
select sno 学号,cno 选修课,score 成绩 from Score

–10、查询出教师信息表里面的姓名，职位，院系，以“姓名-职位-院系”显示查询结果
select tname+’-’+prof+’-’+depart as ‘姓名-职位-院系’ from teacher