• insert into student (sno,sname,ssex,sbirthday,class) values (105,'匡明','男','1975-10-02',95031) insert into student(sno,sname,ssex,sbirthday,class) values (107,'王丽','女','1976-01-23',95033) ...
insert into student (sno,sname,ssex,sbirthday,class)
values (105,'匡明','男','1975-10-02',95031)
insert into student(sno,sname,ssex,sbirthday,class)
values (107,'王丽','女','1976-01-23',95033)
insert into student(sno,sname,ssex,sbirthday,class)
values (101,'李军','男','1976-02-20',95033)
insert into student(sno,sname,ssex,sbirthday,class)
values (109,'王芳','女','1975-02-10',95031)
insert into student(sno,sname,ssex,sbirthday,class)
values (103,'陆军','男','1974-06-03',95031)
insert into course (cno,cname,tno) values ('3-105','计算机导论',825)
insert into course (cno,cname,tno) values ('3-245','操作系统',804)
insert into course (cno,cname,tno) values ('6-166','数据电路',856)
insert into course (cno,cname,tno) values ('9-888','高等数学',831)

insert into score (sno,cno,degree) values(103,'3-245',86)
insert into score (sno,cno,degree) values(105,'3-245',75)
insert into score (sno,cno,degree) values(109,'3-245',68)
insert into score (sno,cno,degree) values(103,'3-105',92)
insert into score (sno,cno,degree) values(105,'3-105',88)
insert into score (sno,cno,degree) values(109,'3-105',76)
insert into score (sno,cno,degree) values(101,'3-105',64)
insert into score (sno,cno,degree) values(107,'3-105',91)
insert into score (sno,cno,degree) values(108,'3-166',78)
insert into score (sno,cno,degree) values(101,'3-166',85)
insert into score (sno,cno,degree) values(107,'3-166',79)
insert into score (sno,cno,degree) values(108,'3-166',85)

insert into teacher (tno,tname,tsex,tbirthday,prof,depart)
values(804,'李成','男','1958-12-02','副教授','计算机')
insert into teacher (tno,tname,tsex,tbirthday,prof,depart)
values(856,'张旭','男','1969-03-12','讲师','电子工程')
insert into teacher (tno,tname,tsex,tbirthday,prof,depart)
values(825,'王萍','女','1972-05-05','助教','计算机')
insert into teacher (tno,tname,tsex,tbirthday,prof,depart)
values(831,'刘冰','女','1977-08-14','助教','电子工程')

delete from student
where sname ='李军'
truncate table student
update student set ssex='女'
select * from student where ssex='女'
select* from student where sname like'王%'



展开全文
• create database SS on ( name='SS', filename='C:\SS.mdf', size=3mb, filegrowth=5% ) log on ( name='SS_log', filename='C:\SS_log.ldf', size=3mb, maxsize=20mb, ...use SS...
create database SS on

(

name='SS',

filename='C:\SS.mdf',

size=3mb,

filegrowth=5%

)

log on

(

name='SS_log',

filename='C:\SS_log.ldf',

size=3mb,

maxsize=20mb,

filegrowth=5%

)

use SS --打开数据库SS

create table stu

(

sid int primary key identity(1,1) not null,

name varchar(10) not null unique,

sex char(2) check(sex='男' or sex='女') not null,

age as datediff(yy,birth,getdate()),

birth datetime,

email varchar(20) check(email like '%@%.com'),

stuage int check(stuage between 0 and 100),

marrige bit,

more text

)

--create table stu 创建表stu

--primary key 主键

--identity(1,1) 标识列

--unique 唯一约束

--check 检查约束

---default 默认值

create table course

(

cid int primary key identity(1,1),

cname varchar(10)

)

create table score

(

sid int,

cid int,

sc int,

primary key(sid,cid),

foreign key(sid) references stu(sid),

foreign key(cid) references course(cid)

)

insert into stu values('张翠山','男','1922-2-2','zcs@126.com',5,default,0,NULL)

insert into stu values('赵翠花','女','1962-6-6','zch@126.com',16,'上海',0,NULL)

insert into stu values('赵敏','女','1982-6-6','zm@163.com',26,'天津',1,NULL)

insert into stu values('李市民','男','1955-5-6','lsm@163.com',5,'天津',1,NULL)

insert into stu values('成要紧','男','1955-5-6','cyj@163.com',5,'天津',1,NULL)

insert into stu values('方食欲','男','1955-5-6','fsy@163.com',5,'天津',1,NULL)

insert into course(cname) values('数学')

insert into course(cname)

select '语文' union

select '英语'

insert into score(sid,cid,sc)

select 1,1,100 union

select 1,2,90 union

select 1,3,88 union

select 2,1,80 union

select 2,2,75 union

select 2,3,73 union

select 3,1,70 union

select 3,2,50 union

select 3,3,40

select * from course

select * from score

select * from stu


展开全文
• SQLserver代码练习题SQL语句,可用于初学者在使用SQL Server语句的基本练习
• 一些基本的T-sql查询联系，包涵答案，多表查询之类
• 只是最基本的增删改查的SQL语句，新手教学
• SQL Server 存储过程和触发器sql语句练习
--1.查找18岁以上姓张的年龄最大的前20%
select top 20 percent * from stu
where 年龄>18 and 姓名 like '张_'
go

--2.创建存储过程，在stu表中性别那一列插入“男”或“女”时，在表中记录的是“1”或“0”
create procedure stu_insert  @num int,@name char(5),@sex char(6)
as
if @sex='男'
insert into stu values(@num,@name,1,null)
else
if @sex='女'
insert into stu values(@num,@name,0,null)
go

exec stu_insert 7,'赵四',男
go

--3.创建存储过程，输入学号，输出性别（显示的是“男”或“女”）
create proc stu_out @num int,@sex char(6) output
as
declare @se bit
set @se=(select 性别 from stu where 学号=@num)
if @se=1
set @sex='男'
else if @se=0
set @sex='女'
go

declare @sex char(6)
exec stu_out 2 ,@sex output
select @sex;
go

--新建一个表，在表中创建一个触发器，每次插入操作时，变量@str值会显示。
create table table1(a int)
go

create trigger table1_insert on table1 after insert
as
declare @str char(50)
set @str='trigger is working'
print @str
go

insert into table1 values(1)
select * from table1

create table table2(a int)
go

create trigger table2_insert on table2 instead of insert
as
print 'instead of trigger is working'
go

insert into table2 values(1)
select * from table2

select * from cjb
select * from kcb
select * from xsb
go

--创建触发器，当向成绩表中插入一个学生的成绩时，将学生表中该学生的总学分加上添加的课程分数
create trigger cjb_insert on cjb after insert
as
declare @num char(6),@kc_num char(3),@xf int
select @num=学号,@kc_num=课程号 from inserted
select @xf=学分 from kcb where 课程号=@kc_num
update xsb set 总学分=总学分+@xf where 学号=@num
print '修改成功'
go

注意事项：
1、理解存储过程中的输入参数和输出参数。
2、定义参数的类型

展开全文
• 1、查询“001”课程比“002”课程成绩高的所有学生的学号select sc1.S#,sc1.C# from SC sc1 where sc1.C#='001' and sc1.score < (s...


1、查询“001”课程比“002”课程成绩高的所有学生的学号select sc1.S#,sc1.C# from SC sc1  where sc1.C#='001' and sc1.score < (select sc2.score from SC sc2   where sc2.C#='002' and sc1.S#=sc2.S#) 2.查询平均成绩大于60分的同学的学号和平均成绩select S#, AVG(score) from SC group by S# having AVG(score)>60select S#, AVG(score) from SC group by S# having AVG(score)>60 order by AVG(score) DESC 3、查询所有同学的学号、姓名、选课数、总成绩； select stu.S#, stu.Sname, COUNT(sc.C#),SUM(sc.score) from Student stu,SC sc where stu.S#=sc.S# group by stu.S#,stu.Sname 4、查询姓“李”的老师的个数；select COUNT(*) from Teacher where Tname like '李%' 5、查询没学过“叶平”老师课的同学的学号、姓名； select stu.S#,stu.Sname from SC sc,Student stu  where stu.S#=sc.S# and  C#   in(select C# from Course  where T#    not in (select T# from Teacher where tname='叶平')) 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； select S#,Sname from Student  where S# in (select S# from SC where C# in( '001','002')    group by S# having count(S#)=2) 7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；  select stu.S#,stu.Sname from SC sc,Student stu  where stu.S#=sc.S# and sc.C# in(select c.C# from Course c   where c.T# in (select T# from Teacher where Tname='叶平')) 8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；  select S#,Sname from Student where S#  in(select sc1.S# from SC sc1,SC sc2   where sc1.S#=sc2.S#    and sc1.C#='001' and sc2.C#='002'and sc2.Score 9、查询所有课程成绩小于60分的同学的学号、姓名； select stu.S#,stu.Sname from Student stu,SC sc  where sc.score<60 and stu.S#=sc.S# 30、查询同名同性学生名单，并统计同名人数  Select Sname,Ssex,count(*) as num from Student group by Sname,Ssex having count(*)>1  31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime) select Sname,Sage from Student where Sage=2013-1981Select * from Student Where year(Sage)=1981 32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列  select c.C#, (select AVG(score) from SC where C#= c.C#) as avgValuefrom (select C# from SC group by C#) as c order by avgValue,c.C# desc 33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩   select c.S#, stu.Sname, (select AVG(score) from SC where S#= c.S#) as avgValuefrom (select S#  from SC group by S#) as c inner join Student stu on stu.S#=c.S#where (select AVG(score) from SC where S#= c.S#) > 85 34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数    /*34、查询课程名称为“数据库”，且分数低于的学生姓名和分数*/select Cname from Course where Cname='数据库'select score from SC where score < 60select stu.Sname,sc.score from Student stu,SC sc  where stu.S#=sc.S# and sc.C#=(select top 1 C# from Course where Cname='数据库')  and sc.score <60  select stu.Sname,s.score from Student stuinner join SC s on s.S# = stu.S#inner join Course c on c.C# = s.C# where c.Cname='数据库' and s.score <60 35、查询所有学生的选课情况；    select stu.S#,stu.Sname ,cou.Cname from Student stu,SC SC ,Course cou where stu.S#=SC.S# and SC.C#=cou.C# group by stu.S#,stu.Sname,cou.Cname 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；   select stu.Sname ,cou.Cname,s.score from Student stu,SC s,Course cou  where stu.S#=s.S# and s.C#=cou.C# and s.score>70  group by stu.S#,stu.Sname,cou.Cname,s.score 37、查询不及格的课程，并按课程号从大到小排列    select stu.Sname,cou.C#,cou.Cname,s.score from Student stu,SC s,Course cou  where stu.S#=s.S# and s.C#=cou.C# and s.score<60 order by cou.C# DESC 38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；    select stu.S#,stu.Sname from Student stu,SC s where stu.S#=s.S# and s.C#='003' and s.score>80 39、求选了课程的学生人数 select C#, count(distinct S#) from SC group by C# order by C# select count(*) from (select S# from SC SC1 where SC1.S# in(select S# from SC SC2 group by S#) group by S#)消息 102，级别 15，状态 1，第 2 行')' 附近有语法错误 select count(s3.S#) from (select s1.S# from SC s1 where s1.S# in(select s2.S# from SC s2 group by s2.S#)) as s3group by s3.S# select count(*) as COUNTNUM from (select s1.C# from SC s1 where s1.C# in(select s2.C# from SC s2 group by s2.C#)) as s3group by s3.C# 40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩   /* 40查询选修"叶平"老师所授课程的学生中,成绩最高的学生姓名及其成绩*/select Stu.Sname,SC.score from Student Stuinner join SC on SC.S#=stu.S#inner join (select SC.C#,MAX(score) as mscore from SC inner join Course c on sc.C#=c.C# inner join Teacher t on t.T#=c.T# and Tname='叶平' group by SC.C#) as g on g.mscore=SC.score AND SC.C#=g.C#inner join Course c on sc.C#=c.C# inner join Teacher t on t.T#=c.T# and Tname='叶平' /*查询叶平老师教的每门课程最高成绩是多少*/select SC.C#,MAX(score) as HighScore from SC inner join Course c on SC.C#=c.C# inner join Teacher t on t.T#=c.T# and Tname='叶平' group by SC.C#/*查询叶平老师教的课程最高成绩是多少*/select MAX(score) as HighScore from SC inner join Course c on SC.C#=c.C# inner join Teacher t on t.T#=c.T# and Tname='叶平' /* 40查询选修"叶平"老师所授课程的学生中,学生姓名及其成绩,按照降序排序*/select stu.Sname,s.score from Student stu,SC s where stu.S#=s.S# and s.C# in (select c.C# from Course c where c.T# in   (select distinct t.T# from Teacher t where t.Tname='叶平') )   order by s.score DESC41、查询各个课程及相应的选修人数 /*41、查询各个课程及相应的选修人数*/select s1.C#,cou.Cname,count(s1.C#)from SC s1,Course cou where cou.C#=s1.C# group by s1.C#,cou.Cnameselect cou.Cname,cou.C#,count(cou.C#) as 人数 from Course cou inner join SC s on s.C#=cou.C# group by cou.C#,cou.Cname  42、查询不同课程成绩相同的学生的学号、课程号、学生成绩   /*42、查询不同课程成绩相同的学生的学号、课程号、学生成绩（学号重复了）*/select scr.*,sc.C# from (select st.S#,(select max(score) from SC where S#=st.S#) as scorefrom (select s# from SC group by s#) as st where (select max(score) from SC where S#=st.S#)=(select min(score) from SC where S#=st.S#)) as scrinner join SC on scr.S# = SC.S# AND scr.score = SC.score

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29056818/viewspace-768456/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29056818/viewspace-768456/
展开全文
• /*42、查询不同课程成绩相同的学生的学号、课程号、学生成绩*/select t1.C#, t1.S#, t1.score from SC t1 join SC t2 on t1.C#!=t2.C# an...
• SQL语句创建四个表： create database tongjigouse tongjigocreate table student(Sno varchar(20) not null primary key ,--学号Sname varchar(20) not null,--学生姓名Ssex varchar(20) not null, --学生性别...
• 菜鸟级别入门笔记，以下语句都在sqlserver 2008中调试过,是学习sqlserver的入门\基本语句.当然,如果你的目的仅仅是使用数据库, 而不是维护\管理数据库,我想掌握这些基本也就够了.
• 在products表中查询出厂日期晚于2014年4月的水果信息语句：select * from products where 出厂日期>'2014-04-30' and 种类='水果'2．在products表中分组查询所有水果、蔬菜、坚果的总成本语句：select 种类,SUM...
• sql server 语句语法应用讲解 大量语句练习以及答案 相关数据教程讲解
• 学了不少东西，感觉自己的sql语句还是很不好，从基础学起吧。 来一段sql脚本： create database tongji go use tongji go create table student ( Sno varchar(20) not null primary key ,--学号 Sname ...
• SQL 约束语句 分享一下！ USE test GO CREATE TABLE student (student_number int PRIMARY KEY, student_name char(30)) GO
• 一、删除冗余记录 DELETE [学生表] WHERE id NOT IN (SELECT MIN(id) FROM [学生表] GROUP BY [学号],[姓名],[课程编号],[课程],[分数]) 二、创建触发器 if (object_id('账户插入', 'tr') is not null) ...
• sql语句练习(未完待续,sqlserver暂时没有接触过) 1.mysql 练习1 --建表 --学生表 CREATE TABLE Student( s_id VARCHAR(20), s_name VARCHAR(20) NOT NULL DEFAULT '', s_birth VARCHAR(20) NOT NULL ...
• SQL server (oracle)语句练习案例 ！！！练习希望可以按顺序执行！！！ 建表(一)：create table Teacher( Tno integer , Tname char(6) , Title char(6), Dept char(10)); create table Teacher( Tno integer ...
• SQL语句练习题参考答案 1、 select Sname,Ssex,Class from Student; 2、 select distinct depart from teacher; 3、 select Sno as '学号',Sname as '姓名',Ssex as '性别',Sbirthday as'出生日期',Class as'班号'...
• SQLServer实例语句，偏基础吧，合适小白拿来练习用，就是一些实例的语句哈，需要的可以下载下来练习下，初级面试题
• 最近正在准备，计算机四级 的数据库，做了一些关于SQL语句练习，包括数据库的建立，表的建立，查询插入，触发器的一些练习，只是一些很简单的练习并不深入原理，也不深入展开叙述，仅仅作为一些基础的SQL 命令。...
• SQL server实验练习1 （1）请根据下面描述，使用SQL语句完成数据库的创建 名称：stuDatabase 主数据文件： 逻辑文件名： Data_M 物理文件名：stuDatabase__M.mdf 初始大小：8M 递增：10% 限制文件增长：500M 次数据...
• 前言：前面几篇文章，...本文将从MySQL最基础的语句出发，为你展示出创建及修改不同对象应该使用的标准语句。1.创建数据库创建数据库的官方标准语法为：CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [crea...
• IDENTITY、COLLATE Chinese_PRC_CI_AS是sql server的写法： create Table Singer(SingerID int IDENTITY(1,1) not NULL,SingerName nvarchar(31) COLLATE Chinese_PRC_CI_AS NOT NULL,Birthday datetime not null);...
• SQL Server数据库基础知识 函数的定义 函数是指一段可以直接被另一段程序或代码引用的程序或代码。 SQL函数的基本类型和种类 在 SQL 中，基本的函数类型和种类有若干种。 函数的基本类型： Aggregate 函数（合计函数...
• 练sql语句最佳例题，我学sqlserver,就拿它练的,搜索sql server练习（附答案）可以找到答案，抱歉主要是上传的匆忙，答案免分
• SQL Server数据库基础知识 游标概述 文章目录SQL Server数据库基础知识游标概述...把游标与T-SQL语句的结果集联系起来。 (2) 打开游标。 (3) 使用游标操作数据。 (4) 关闭游标。 (5) 删除/释放游标。 游标使用示例 下

...