数据库sql语句例题及答案，SQL数据库笔试题及答案
很多朋友对于数据库sql语句例题及答案和SQL数据库笔试题及答案不太懂，今天就由小编来为大家分享，希望可以帮助到大家，下面一起来看看吧！
有关数据库的作业·~~SQL语句题
所有的
1．select X.商品代号，分类名，数量，品牌 from商品表1 X，商品表2 Y where X．商品代号=Y．商品代号
找出商品库里面所有的商品信息
2．select专业，count(*) as专业人数 from学生 group by专业 order by专业人数 desc
找出每个专业的专业人数，并且降序排列
3．select课程．课程号，课程．课程名，count(*) as选课人数 from课程，选课 where课程．课程号一选课．课程号 group by课程．课程号，课程．课程名
找出每门课程的选修人数
4.
SELECT商品代号 from商品表1 group by商品代号 HAVING SUM(数量)> 10
5.
SELECT* FROM商品表1 WHERE单价>(SELECT AVG(单价) FROM商品表1)
SQL数据库试题求解
------------------------------------------------------
create table students(st_id varchar(20),st_name varchar(50),sex varchar(10))
insert into students(st_id,st_name,sex)
select'st001','张杰','男' union all
select'st002','公孙燕飞','男' union all
select'st003','王楠','女' union all
select'st004','王伟','男' union all
select'st005','李燕纹','女' union all
select'st006','孙武','男'
select*
from students
create table teachers(t_id varchar(20),t_name varchar(50),t_lesson varchar(50))
insert into teachers
select't001','张老师','数学' union all
select't002','李老师','英语'
delete from results
create table results(r_id varchar(20),r_fenshu int,r_stid varchar(50),r_tid varchar(50))
insert into results
select'r001','90','st001','t002' union all
select'r002','68','st005','t001' union all
select'r003','92','st003','t001' union all
select'r004','82','st006','t002' union all
select'r005','70','st002','t002' union all
select'r006','86','st002','t001' union all
select'r007','57','st003','t002' union all
select'r008','76','st006','t001' union all
select'r009','55','st001','t001' union all
select'r010','77','st004','t002' union all
select'r011','58','st005','t002'
----------------------------------------------------------
1.
select st_id
from students
where st_name='王伟'
2.select st_id,st_name
from students
where st_name like'__燕%'
3 select st_name,len(st_name) as名字长度
from students
where sex='男'
4 select min(r_fenshu) as最低分数
from teachers t inner join results r on t.t_id=r.r_tid
where t_lesson='数学'--这个是不考虑成绩中有null值的
5 select s.st_id as学生编号,r_fenshu as分数,r_tid as课目号
from students s inner join results r on s.st_id=r.r_stid
where s.sex='女'
--如果还要课目的名称的话请用下面的
select s.st_id as学生编号,r.r_fenshu as分数,r.r_tid as课目号,t.t_lesson as课目名称
from students s inner join results r on s.st_id=r.r_stid
inner join teachers t on r.r_tid= t.t_id
where s.sex='女'
6 select avg(r.r_fenshu)
from results r inner join teachers t on r.r_tid= t.t_id
where t.t_lesson='英语'
7.select*
from students s inner join results r on s.st_id=r.r_stid
inner join teachers t on r.r_tid= t.t_id
where s.st_id in(select top 2 st_id from students order by st_id desc)
order by s.st_id desc
8 select sum(r.r_fenshu) as总分
from results r inner join students s on r.r_stid=s.st_id
where s.st_name='王楠'
9.select distinct s.st_id,s.st_name
from students s inner join results r on s.st_id= r.r_stid
where st_id not in(select r_stid from results where r_fenshu<60) and st_id not in(select r_stid from results where r_fenshu>=90)
10 update results
set r_fenshu= r_fenshu+ 10
--如果分数不可能大于100请用这句 set r_fenshu= case when r_fenshu+ 10<=100 then r_fenshu+ 10 else 100 end
where r_stid in(select st_id from students where sex='女')
1进阶题
select t.t_name,count(*)
from students s,teachers t,results r
where r.r_tid= t.t_id
and s.st_id=r.r_stid
and r.r_fenshu>= 60
and t.t_id in(select t_id from teachers where t_lesson='数学')
--and t_lesson='数学'
group by t.t_name
2
select top 1 sum(r_fenshu) as总分,t.t_lesson,t_id,t_name
from results r,teachers t
where r.r_tid= t.t_id
group by t.t_lesson,t_id,t_name
order by总分 desc
3. delete from results where r_stid in(select r_stid from results group by r_stid having count(r_tid)= 1)
1选做题
select d.name from sysobjects d where d.xtype='U'
2.select top 5* from students order by newid()
sql创建数据库语句
创建数据库的SQL语句：
create database stuDB
on primary--默认就属于primary文件组,可省略
(
/*--数据文件的具体描述--*/
name='stuDB_data',--主数据文件的逻辑名称
filename='D:\stuDB_data.mdf',--主数据文件的物理名称
size=5mb,--主数据文件的初始大小
maxsize=100mb,--主数据文件增长的最大值
filegrowth=15%--主数据文件的增长率
)
log on
(
/*--日志文件的具体描述,各参数含义同上--*/
name='stuDB_log',
filename='D:\stuDB_log.ldf',
size=2mb,
filegrowth=1mb
)
创建表和删除表的SQL语句如下：
use StuDB
go
if exists(select* from sysobjects where name='stuMarks')
drop table stuMarks
create table stuMarks
(
ExamNo int identity(1,1) primary key,
stuNo char(6) not null,
writtenExam int not null,
LabExam int not null
)
go
--其中，列属性"identity(起始值,递增量)"表示"ExamNo"列为自动编号,也称为标识列
alter table表名
add constraint约束名约束类型具体的约束说明
alter table表名
drop constraint约束名
alter table stuMarks
add constraint UQ_stuNo Unique(stuNo)
alter table stuMarks
drop constraint UQ_stuNo
/*--添加SQL登录账户--*/
exec sp_addlogin'xie','123456'--账户名为xie,密码为123456
--删除xie账户名
exec sp_droplogin'xie'
/*--在stuDB数据库中添加两个用户(必须存在)--*/
use stuDB
go
exec sp_grantdbaccess'xie','123456'
go
--提示：SQL Server中的dbo用户是具有在数据库中执行所有活动权限的用户,表示数据库的所有者(owner),一般来说，
--如果创建了某个数据库,就是该数据库的所有者,即dbo用户,dbo用户是一个比较特殊的数据库用户,无法删除,且此用
--户始终出现在每个数据库中
/*--给数据库用户授权--*/
--授权的语法如下
-- grant权限 [on表名] to数据库用户
use stuDB
go
grant select,update,insert on stuMarks to xie
grant create table to xie
go
关于数据库sql语句例题及答案，SQL数据库笔试题及答案的介绍到此结束，希望对大家有所帮助。