sqlserver实操题，sql server数据库实验报告

sqlserver加分编程题

----table 为你的表名，通过循环语句实现更新，@coun为更新次数

declare @cou int,@count int

set @cou=0

set @count=0

select top 0 * into #t1 from table

select @cou =COUNT (*) from table where [分数]90

while @cou0

begin

set @count=@count+@cou

insert into #t1

select * from table where [分数]90

update a

set a.[分数]= case when [分数] 60 then [分数]+5

when [分数] 70 then [分数]+4

when [分数] 80 then [分数]+3

when [分数] 90 then [分数]+2

else [分数]

end

from table a

inner join #t1 b on a.[姓名]=b.[姓名] and a.[课程]=b.[课程]

delete from #t1

select @cou =COUNT (*) from table where [分数]90

end

print @count

相关问答

Q1: 谁会做啊，SQLserver的题！！！求帮忙

1、检索图书表中图书类别为中国历史的记录，并表示记录的全部属性。

2、检索图书表中书名包含SQL Server的记录，并表示记录的书号、书名、作者、价格属性。

3、检索图书表中所有记录并按照图书类别分组，表示记录的图书类别和对应的数量。

六、1、

create table Teacher(

tid char(5) primary key not null,

tname char(10) not null,

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

birthday smalldatetime,

title char(10),

depart char(20)

);

comment on column Teacher.tid is '教工号';

comment on column Teacher.tname is '教工姓名';

comment on column Teacher.sex is '性别';

comment on column Teacher.birthday is '出生日期';

comment on column Teacher.title is '职称';

comment on column Teacher.depart is '所属系部';

2、

alter table Teacher add tel char(15);

3、

alter table Teacher add salary numeric(8,2);

4、

alter table Teacher add constraint ck_title check (title in ('教授','副教授','讲师','助讲'));

5、

insert into Teacher values('10001','李芳','女',CONVERT(datetime,'3-14-1975',101),'副教授','计算机','83118147',4582.5);

6、

update Teacher set tel='18653155668' where tname='李芳';

7、

select tname as "姓名",(GETDATE()-birthday) as "年龄",salary as "工资",title as "职称"

from Teacher

where depart='计算机';

8、

create view view1

as

select tid,tname,depart,tel

from Teacher

order by depart;

9、

select title,count(tid)

from Teacher

group by title;

10、

select depart,min(salary),max(salary),avg(salary)

from Teacher

group by depart;

Q2: 求解答数据库基础SQLserver2/3/4题

SQL代码如下：

1.

CREATE TABLE Course

(

Cno VARCHAR(3) PRIMARY KEY,

Cname VARCHAR(30),

Cpno VARCHAR(3)

)

CREATE TABLE Student

(

Sno VARCHAR(7) PRIMARY KEY,

Sname VARCHAR(20),

Ssex CHAR(2),

birthday DATE,

Sdept VARCHAR(15)

)

CREATE TABLE SC

(

Sno VARCHAR(7),

Cno VARCHAR(3),

Grade INT,

CONSTRAINT pk_SC_Sno_Cno PRIMARY KEY(Sno, Cno)

)

2.

SELECT (SELECT COUNT(*)

FROM Student)

AS '学生总人数',

(SELECT COUNT(*)

FROM Student

WHERE Sno IN (

SELECT DISTINCT Sno

WHERE SC

))

AS '选修总人数'

3.

SELECT Sno, Sname, Cno, Cname, Grade

FROM Student s

JOIN SC

ON s.Sno = SC.Sno

JOIN Course c

ON c.Cno = SC.Cno

4.

SELECT AVG(Grade)

FROM SC

WHERE Cno = '001'

Q3: sqlserver填空题求助

top 3,desc

declare

--,；

exec sp_rename

identity(1,1)，not null

select , set

update,6

ORC

数据库文件的初始大小

DECLARE CURSOR

原子性，隔离性

显示事务，自动提交事务

go

实体型，联系

实体完整性，参照完整性

Q4: 求大佬帮忙解决这套题 sqlserver

通过下列SQL语句关联这四个表

select * from goods_sell a

inner join goods_detai1 b on a.sell_id=b.sel1_id

inner join goods c on c.goods_id=b.goods_id

inner join Account_user d on a.user_ id=d.user_ id

然后通过group by 开始汇总，order by 来排序你所要的结果

关于sqlserver实操题和sql server数据库实验报告的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。