SQL Server 学习

今天下午无意间看到一篇有关于sql 的面试题。好久没有用sql 了。今天拿来练练手。

希望能唤醒自己对sql 的记忆。

本篇文章只是自己纯粹的学习sql . 没有任何其他意义。

其中包括 create table, select ,insert,create trigger

 

Create table and Select ,Insert

CREATE TABLE student_info

(

学号 VARCHAR(8) PRIMARY KEY,

姓名 VARCHAR(10),

性别 CHAR(4),

出生年月 VARCHAR(10),

家庭住址 VARCHAR(30),

备注 VARCHAR(30)

)

 

SELECT * FROM student_info

 

INSERT INTO student_info VALUES('0001','zhangsan','male','1981-8-9','beijing',NULL)

 

 

 

CREATE TABLE curriculum

(

课程编号 VARCHAR(8) PRIMARY KEY,

 

课程名称 VARCHAR(20),

 

学分 INT

) 

INSERT INTO curriculum(课程编号,课程名称,学分) VALUES('0001','jisuanjijichu',2)

INSERT INTO curriculum VALUES('0002','Clanguage',2)

 

 

 

 

 

CREATE TABLE grade

(

学号 VARCHAR(8),

 

课程编号 VARCHAR(8),

 

分数 INT

)

 

 

 

INSERT INTO grade(学号,课程编号,分数) VALUES('0001','0001',80)

INSERT INTO grade(学号,课程编号,分数) VALUES('0001','0002',90)

 

 /*题目：

条件查询

*/

 

--1、在GRADE表中查找80-90分的学生学号和分数

 

SELECT 学号,分数

FROM grade

where 分数 between 80 and 90

 

--2、在GRADE 表中查找课程编号为0001学生的平均分

SELECT AVG(分数) as 平均分

FROM grade

WHERE 课程编号='0001'

 

--3、在GRADE 表中查询学习各门课程的人数

SELECT COUNT(学号) 人数,课程编号

FROM grade 

GROUP BY 课程编号

 

--4、查询所有姓张的学生的学号和姓名

SELECT 学号,姓名

FROM student_info

WHERE 姓名 LIKE 'zhang%'

 

/*

   嵌套查询：

*/

--1、 查询和学号’0001’的这位同学性别相同的所有同学的姓名和出生年月

 

SELECT 姓名,出生年月

FROM student_info

WHERE 性别 in (SELECT 性别 FROM student_info WHERE 学号='0001')

 

--2、 查询所有选修课程编号为0001 和0002的学生的学号、姓名和性别

SELECT 学号,姓名,性别

FROM student_info

WHERE 学号 in (SELECT 学号 FROM grade WHERE 课程编号='0001' and 学号 in (SELECT 学号 FROM grade WHERE 课程编号='0002'))

 

--3、 查询出学号为0001的学生的分数比0002号学生最低分高的课程编号和分数

 

SELECT 课程编号, 分数

FROM grade

WHERE 学号='0001'  and 分数 > (select MIN(分数) from grade where 学号='0002')

 

 

/*

  多表查询：

*/

--1、 查询分数在80-90分的学生的学号、姓名、分数

SELECT student_info.学号,student_info.姓名,grade.分数 FROM student_info LEFT JOIN grade ON student_info.学号 = grade.学号WHERE grade.分数 BETWEEN 80 AND 90

select student_info.学号,student_info.姓名,grade.分数 from student_info,grade where grade.分数 between 80 and 90

--2、 查询学习了’C语言’课程的学生学号、姓名和分数

SELECT student_info.学号,姓名,分数 FROM  student_info LEFT JOIN grade ON student_info.学号 = grade.学号 WHERE grade.课程编号 IN (SELECT 课程编号 FROM curriculum WHERE 课程名称='Clanguage')

select student_info.学号,student_info.姓名,grade.分数 from student_info,grade,curriculum where student_info.学号=grade.学号 and grade.课程编号=curriculum.课程编号 and curriculum.课程名称='Clanguage'

--3、 查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选课的学生总成绩为空。

SELECT student_info.学号,姓名,SUM(分数) 总成绩  FROM student_info RIGHT JOIN grade ON student_info.学号 = grade.学号  

group by student_info.学号,姓名 

select grade.学号,student_info.姓名,sum(grade.分数) as 总成绩 from student_info,grade where grade.学号=student_info.学号 group bygrade.学号,student_info.姓名

 

触发器

/*题目、活期存款中，“储户”通过“存取款单”和“储蓄所”发生联系。

    假定储户包括：账号，姓名，电话，地址，存款额；“储蓄所”包括：储蓄所编号，名称，电话，地址（假定一个储户可以在不同得储蓄所存取款）

1、写出设计以上表格的语句（4分）*/

CREATE TABLE 储户

(

账号 int primary key,

姓名 varchar(10),

电话 varchar(13),

地址 varchar(40),

存款额 int

)

 

CREATE TABLE 储蓄所

(

储蓄所编号 int primary key,

名称 varchar(20),

电话 varchar(13),

地址 varchar(40)

)

 

CREATE TABLE 存取款单

(

账号 int,

姓名 varchar(10),

存款额 int,

储蓄所编号 int,

储蓄所名词 varchar(20)

)

 

create table qukuan

(

id int,

yuer int,

flag int

 

)

 

create table cunkuan

(

  id int,

  qian int,

  flag int

)

 

 

/*2、创建一个触发器TR1完成下面内容：

     当向“存取款单”表中插入数据时，

            如果存取标志＝１则应该更改储户表让存款额加上存取金额，

              如果存取标志＝0 则应该更改储户表让存款额减去存取金额,

              如果余额不足显示余额不足错误。*/

CREATE TABLE CREATE TRIGGER tr1 on qukuan after insert

 

AS

 

BEGIN

declare @sid nvarchar(50)

declare @type int

declare @qian int

declare @yuer int

select @sid=sid,@type=[type],@m=m from inserted

select @yuer=yuer from cunkuan

if(@type=1)

begin

update cunkuan set yuer=yuer+@qian

end

else

begin

if(@yuer<@qian)

   begin

     print '余额不足'

   end

else

    begin

     update cunkuan set yuer=yuer-@qian

    end

end

END

GO