Sql作业

表：

Create table Student(

id int,

name varchar(100),

subjectId int,

CreateDate datetime

)

Create table Subject(

id int,

name varchar(100)

)

1:写一个包含连接和分组的sql

select a.id,b.name from Student as a inner join Subject as b on a.subjectId=b.id Group by b.name
2:写一个包含连接和case when的sql

insert into Subject(name) case name when '1' then '语文‘
3:写一个包含连接和Cast()函数的sql

select cast('a.id' As int),b.name from Student as a inner join Subject as b on a.SubjectId=b.id 
4:写一个包含连接和Convert()函数的sql

select Convert(varchar(100),a.CreateDate,0),b.name from Student as a inner join Subject as b on a.SubjectId=b.id 
5:写一个包含子查询的sql

Select id from Student where SubjectId=(Select id from Subject where name='语文')
6:写一个视图的sql

Create view[view_test] as select * from Student
7:创建索引的sql

Create[UNIQUE] Index test on Student with FILLFACTOR=30 Go
8:分页存储过程sql

Create procudure pagelistproc(

@pageIndex int,

@pagesize int,

@tablename varchar(200),

@columnName varchar(200),

@orderby varchar(50),

@sort varchar(50),

@where varchar(500))

as

begin

decore @sql nvarchar(200);

set @sql='select '+@columnName+' from (select'+@columnName+',ROW_NUMBER()over(order by '+@orderby+')    as number from '+@tableName+ ' where '+@where+')t where t.number between '+cast((@pageIndex-1)*@pageSize as varchar(200))+' and '+cast(@pageIndex*@pageSize as varchar(200))+' order by '+@orderby+' '+@sort+'';    exec(@sql);
9:写一个触发器sql

CREATE TRIGGER 触发器名称

ON 表名

FOR INSERT

AS

 Insert into Student(CreateTime) values DateTime.Now

10:写一个存储过程包含事务的sql

1 ALTER proc [dbo].[Proc_InsertStudent] 

@stuName nvarchar(50),@stuClassId int,@stuAge int 

as 

begin 

set nocount on --on表示不返回计数

set xact_abort on --当执行事务时，如果出错,会将transcation设置为uncommittable状态 

 begin try

declare @stuCountByName int;

select @stuCountByName=count(*) from Students where Name=@stuName;

if(isnull(@stuName,'')='')

begin

print('名字不能为空');

return;

end

 if(@stuCountByName>0)

begin

print('名字重复');

return

end

begin tran --开启事务

insert into Students(Name,ClassId,Age) values(@stuName,@stuClassId,@stuAge)

commit tran --提交事务 

end try

 begin catch

if xact_state()=-1

rollback tran; --回滚事务

select ERROR_NUMBER() as ErrorNumber;

select ERROR_MESSAGE() as ErrorMsg;

end catch

set xact_abort off;

end 
11:如何一次性往一张表【插入/更新】10条数据,存储过程实现。

DELIMTER$$

Create procudure pro_copy()

begin

set@i=1

while@i<=10

DO

insert into Student values("id",@i))

set @i=@i+1

end while

end $$

DELIMITER
12:分页的实现方式？至少写3种

select top 10*from AdminInfo where Id not in(select top 10 Id from AdminInfo);

select top 10*from AdminInfo where Id>(select MAX(Id)from AdminInfo where Id in (select top 10 Id from AdminInfo));

select* from AdminInfo where Id between 11 and 20;
13:写一个包含连接和分组,并且根据某个字段拼接的sql
14:写一个包含having写法的sql

Select * from Student Group by id having ID<20;
15:写一个包含连接和分组,排序的sql。

Select a.id,b.name from Student as a inner join Subject as b on a.Subjectid=b.id group by b.name order by COUNT(a.id)