一些SQL的试题

4.1
select stud_id,name ,birthday,gender,mark from stud_info where name like '张%' and len(name)=2
4.2
select stud_id ,name from stud_grade where grade=(select max(grade) from stud_grade
或者select top 1 stud_id ,name from stud_grade order by grade desc

4.3
declare @zhuanye char(10)
select @zhuanye=sunbstring(zhuanye,5,2) from stud_info
select name from stud_info where @zhuanye='01'

4.4
create proc people_num
as
begin
select count(*) from stud_info where gender='男'
end

4.5
alert table stud_info add column money varchar(30)

4.6
alter table stud_info add constraint uniqueindex unique(telcode)
4.7
ALTER TABLE stud_info
ADD
CONSTRAINT pk_level
PRIMARY KEY CLUSTERED (stud_id)
4.8
alter table stud_grade
add constraint stud_info
foreign key (stud_id)
references (stud_id)
4.9
create nonclustered index CourseIndex
on stud_grade(course_id)
4.10
create view stud_view
as
select A.stud_id ,A.name,B.grade from stud_info A,stud_grade B where A.stud_id=B.stud_id and
substring(stud_id,3,2)='01'

第二题
36 select 姓名,年龄 from S order by 年龄 desc
37 select * from S where 姓名 like '王%'
38 select S.姓名,S.年龄,S.性别,S.系别 from S,C,SC where S.学号=SC.学号 and C.课号=SC.课号 and C.课名='数据库原理'
39 select * from S where 系别=(select 系别 from S where 姓名='刘平')

第三题
5. 根据给出的Transcat-SQL语句，说明其功能或结果。（每题4分，共20分）
5.1 语句如下：
USE orders
CREATE TABLE test (column_a int)
功能：在orders 数据库下建立test表
GO
EXEC sp_help test
功能：执行存储过程sp_help查看test表的信息
GO
ALTER TABLE test ADD column_b varchar(30) null
功能：向test表增加一列，类型为字符型，长度为30
GO
EXEC sp_help test
功能：执行存储过程sp_help查看test表的信息
GO
5.2语句如下：
SELECT title_id, price FROM titles WHERE type=’psychlogy’
ORDER BY price DESC, title_id ASC
功能：从titles表中查询type=’psychlogy’的 title_id，price，并按price降序排列，title_id升序排列.
5.3语句如下：
SELECT au_id, phone, address FROM authors WHERE state=’CA’
功能：从authors表中查询state=’CA’的
au_id, phone, address
5.4语句如下：
USE pubs
GO
ALTER VIEW authors_view
AS
SELECT authors.au_id, authors.au_fname
FROM authors
功能：修改视图authors_view
5.5语句如下：
USE pubs
GO
CREATE PROCEDURE books
AS
SELECT * FROM sales WHERE ord_date>'1993-5-29'
功能：创建存储过程books
其功能为从sales表中查询ord_date>'1993-5-29' 的记录
C
B
A
D
A
A
A