数据库sql语句题目

1、如何将变量count值赋值为1？

DECLARE @count SELECT @count=1

@表示声明一个局部变量，@@表示声明一个全局变量（比如已经定义好的系统变量）
申明局部变量语法：declare @变量名 数据类型；例如：declare @num int；
赋值：有两种方法式（@num为变量名，value为值） set @num=value;
或 select @num=value;

2、有一个学生表，有三个字段： name 、 course 、 score ，每一个学生都有三门课程，比如数学、语文、英语，写 sql 语句，查找出三门课程的成绩都大于 80 的学生。

//方法1
select distinct name from student_grade
group by name
having MIN(score)>80

//方法2
select distinct name
from student_grade
where score>80
group by name
having COUNT(distinct(course))=3

//查询每个学生＞80的课程数目
SELECT name,COUNT(*) as count
from student_grade 
where score > 80 
GROUP BY name

以下题目基于表：

Student(S#,Sname,Sage,Ssex)学生表
S#：学号 ，Sname：学生姓名 ，Sage：学生年龄 ，Ssex：学生性别
Course(C#,Cname,T#)课程表
C#：课程编号， Cname：课程名称， T#：教师编号
SC(S#,C#,score)成绩表
S#：学号 ，C#：课程编号 ，score：成绩
Teacher(T#,Tname)教师表
T#：教师编号 ，Tname：教师名字

3、查询“001”课程比“002”课程成绩高的所有学生的学号

select a.S# from
(select S#,score from SC where C#=1)a,
(select S#,score from SC where C#=2)b
where a.score>b.score and a.S#=b.S#

4、查询平均成绩大于60分的同学的学号和平均成绩

select AVG(score) as avgScore,S#
from SC
group by S#
having AVG(score)>60

5、查询所有同学的学号、姓名、选课数、总成绩

left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的. 换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).B表记录不足的地方均为NULL.

INNER JOIN 产生的结果是AB的交集
FULL [OUTER] JOIN 产生A和B的并集。对于没有匹配的记录，则会以null做为值。

select student.S#,student.Sname,COUNT(SC.C#),SUM(score)
from student left join SC on student.S#=SC.S#
group by student.S#,Sname

6、查询姓‘李’的老师的个数

select COUNT(distinct(Tname))
from Teacher
where Tname like '李%'

7、查询没有学过“叶平”老师可的同学的学号、姓名

select student.S#,student.Sname
from student
where S# not in
(select distinct(SC.S#) from SC,Course,Teacher
where sc.C#=course.C# and Teacher.T#=course.T# 
    and Teacher.Tname='叶平')

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名

select student.S#,student.Sname
from student
where S# in

(select S# from SC,Teacher,Course
where SC.C#=Course.C# and Teacher.T#=course.T# 
    and Teacher.Tname='叶平' 
group by S# 
having COUNT(sc.C#)=(select COUNT(C#) from Course,Teacher
where Teacher.T#=Course.T# and Tname='叶平'))

9、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名

select student.S#,student.Sname
from student
where S# in

(select S# from SC,Course
where SC.C#=Course.C# and SC.C#=1)
 and S# in
 (select S# from SC,Course
where SC.C#=Course.C# and SC.C#=2)

10、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名

Select S#,Sname 
from (select Student.S#,Student.Sname,score ,
(select score from SC SC_2 
where SC_2.S#=Student.S# and SC_2.C#='2') score2    
from Student,SC 
where Student.S#=SC.S# and C#='1') S_2 
where score2 <score; 

11、查询课程成绩小于90的同学的学号、姓名

select S#, sname 
from student 
where s# not in 
(select student.s# from student, sc where student.s# = sc.s# and score>90)

12、创建病人表R（住院号，姓名，性别，科室号，病房，家庭住址）

create table R(
住院号 char(8) primary key,
姓名 char(10),
性别 char(1) check(性别 in ('M','F')),
科室号 char(4),
病房 char(4),
家庭住址 char(30),
foreign key (科室号) references D(科室号)
)

13、收回用户ZHAO对学生表（STUD）中学号（XH）的修改权

--revoke <权限> on <对象> from <用户>
revoke update(XH) on STUD from ZHAO

14、某大型集团公司的数据库的部分关系模式如下：
员工表：EMP(Eno,Ename,Age,Sex,Title)，各属性分别表示员工工号、姓名、年龄、性别和职称级别，其中性别取值为“男”“女”；
公司表：COMPANY(Cno,Cname,City)，各属性分别表示公司编号、名称和所在城市；
工作表：WORKS(Eno,Cno,Salary)，各属性分别表示职工工号、工作的公司编号和工资。
有关关系模式的属性及相关说明如下：
（1）允许一个员工在多家公司工作，使用身份证号作为工号值。
（2）工资不能低于1500元。
根据以上描述，回答下列问题：
【问题1】（4分）
创建工作关系，指定主码、外码，以及工资不能低于1500元的约束。

create table WORKS(
Eno char(10) References EMP(Eno),
Cno char(10) References COMPANY(Cno),
Salary int check(Salary>=150),
Primary key(Eno,Cno)
)

【问题2】（6分）
（1）创建女员工信息的视图FemaleEMP，属性有Eno、Ename、Cno、Cname和Salary，请将下面SQL语句的空缺部分补充完整。

create view FemaleEMP(Eno,Ename,Cno,Cname
    ,Salary)
as
select EMP.Eno,Ename,COMPANY.Cno,Cname,Salary
from EMP,COMPANY,WORKS
where WORKS.Eno=Emp.Eno and WORKS.Cno=
    COMPANY.Cno and Sex='女'

（2）员工的工资由职称级别的修改自动调整，需要用触发器来实现员工工资的自动维护，函数float Salary_value(char(10) Eno)依据员工号计算员工新的工资。请将下面SQL语句的空缺部分补充完整。

create trigger Salary_TRG on EMP after update 
--oracle关键字，sql server无法识别
referencing new row as nrow 
for each row
begin
    update WORKS
    set Salary=Salary_value(nrow.Eno)
    where nrow.Eno=WORKS.Eno
end

【问题3】（5分）
请将下面SQL语句的空缺部分补充完整。
（1）查询员工最多的公司编号和公司名称。

select COMPANY.Cno,Cname
from COMPANY,WORKS
where COMPANY.Cno=WORKS.Cno
group by COMPANY.Cno,Cname
having COUNT(*)>=ALL(
    select COUNT(*) from WORKS Group by Cno)

（2）查询所有不在“中国银行北京分行”工作的员工工号和姓名。

select Eno,Ename
from EMP
where Eno not in(
select Eno from WORKS,COMPANY
where WORKS.Cno=COMPANY.Cno 
    and Cname='中国银行北京分行')

15、补充定义员工关系模式的实体完整性约束和参照完整性约束

alter table 员工 add constraint PK_员工 
    primary key(员工编号)
alter table 员工 add constraint FK_员工
    foreign key(部门) references 部门(部门编号)

16、查询存在两门（包含两门）以上课程不及格的学生姓名

select Sname from student
where S# in(
select S# from SC where score<60 
group by S#
having COUNT(C#)>=2)

17、查询两门以上不及格课程的同学的学号及其平均成绩

select S#,AVG(score)
from SC
where S# in(
select S#
from SC where score<60 
group by S#
having COUNT(C#)>=2)
group by S#

18、查询没学过”叶平”老师讲授的任一门课程的学生姓名

select Sname
from student 
where S# not in 
(select S# from Course,Teacher,SC 
where Course.t#=Teacher.t# and SC.c#=Course.c# 
and Tname='叶平')

19、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序

select c# as 课程号,count(*) as 人数
from sc 
group by c#
having count(*)>=5
order by count(*) desc,C# asc 

20、查询每门课程成绩最好的前两名

select t1.s# as 学生号, t1.c# as 课程号,
    t1.score as 分数
from SC t1
where score in(
    select top 2 score from SC
    where sc.C#=t1.C# 
    order by score desc)
order by t1.C#,score desc

21、查询不同课程成绩相同的学生和学号、课程号、学生成绩

select distinct a.s#,a.c#,a.score,b.s#,b.c#
from SC a,SC b
where a.score=b.score and a.C#<>b.C# 
    and a.S#<>b.s#

注：存在重复语句，如 1 1 67 5 6 和 5 6 67 1 1

22、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select student.Sname,score
from student,SC,Course c,Teacher t
where student.S#=sc.S# and sc.C#=C.C# 
    and t.T#=c.T# and Tname='叶平'
    and score=(
        select MAX(score) from SC where C#=c.C#)

23、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select sname 姓名,sc.S# 学号,AVG(score)平均成绩
from student,SC
where student.S#=sc.S#
group by sc.s#,Sname
having AVG(score)>85

24、创建账户关系的SQL语句

create table 账户(
账户号 char(19) primary key,
客户号 char(19) foreign key(客户号) 
    references 客户(客户号),
开户支行号 char(6) not null,
余额 decimal check(余额>1.00),
状态 varchar(6) check (状态 in('待处理','审理中')) default '待处理'
)

25、为账户关系增加属性“账户标记”，缺省值为0，取值类型整数

alter table 账户 add 账户标记 int default 0

26、将表的查询权限授予用户U1和U2，并允许其将此权限授予他人

grant select on table student to U1,U2 
    with grant option

27、删除表上的约束

alter table <table_name> drop <constrant_name>

28、查询搭乘时期在2012年，且合计购票金额不少于10000元的旅客身份证、姓名、购票金额总和，并按总和降序输出。

select 旅客.身份证号,姓名,SUM(购票金额)
from 旅客,购票
where 旅客.身份证号=购票.身份证号 and 搭乘日期 
    between '2012/1/1' and '2012/12/31'
group by 旅客.身份证号,姓名
having SUM(购票金额)>=10000
order by SUM(购票金额) desc

29、增加“最小库存”属性，存储器：修改库存后（<=最小库存值），向采购表增信息，数量=最小库存*2+10

alter table 商品 add 最小库存 int
create trigger 采购_trigger on 商品 after update
referencing new row as nrow
for each row
when nrow.库存<=最小库存
begin
    insert into 采购
    values(nrow.商品号，nrow.最小库存*2+10)
end

30、info 表
date result
2005-05-09 win
2005-05-09 lose
2005-05-09 lose
2005-05-09 lose
2005-05-10 win
2005-05-10 lose
2005-05-10 lose
如果要生成下列结果, 该如何写sql语句?
　　 win lose
2005-05-09 2 2
2005-05-10 1 2

select date1,SUM(case when result='win' 
    then 1 else 0 end) as win,
SUM(case when result='lose' 
    then 1 else 0 end) as lose
from info
group by date1

31、表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列

select 
(case when a>b then a else b end),
(case when b>c then b else c) 
from table

32、选取前5行数据。选取3-7行数据。

--选取前5行数据
①SET ROWCOUNT 5
   select * from info
②select top 5* from info

--选取3-7行数据
--原理：
--先按date1进行排序，排序完后，给每条数据进行编号。
with t as 
    (select row_number() 
        over(order by date1) as row_no,* 
    from info)
select *
from t
where row_no >3 and row_no<7

33、选取总成绩排名前十的学生

select top 10 name as 姓名,SUM(score) as 总分
from student_grade 
group by name
order by 总分 desc