sqlserver 期中考试题
create database DB
use DB
--专业表
create table major
(spno char(5) not null primary key,
spname varchar(20) not null,
pno char(2) )
--学生表
create table student
(sno char(7) not null primary key,
sname varchar(20) not null,
ssex char(2) not null,
sage smalldatetime ,
spno char(5) not null foreign key references major(spno),
classid char(5),
Inyear char(4) not null )
select datediff(yyyy,'1981-8-12',getdate())from student
--课程表
create table course
(cno char(10) not null primary key,
cname varchar(20) not null,
credit smallint ,
tno char(3))
--选课表
create table scourse
(sno char(7) not null foreign key references student(sno),
cno char(10) not null foreign key references course(cno),
Gmark numeric(4,1),
primary key(sno,cno))
//第一题
select * from student where datediff(yyyy,sage,getdate()) between 25 and (select datediff(yyyy,sage,getdate()) from student where sname='李勇')
//第二题
select sno,sname from student where sno in ( select sno from scourse where cno=(select cno from course where cname='操作系统'))
//第三题
select sname from student where sno in (select distinct sno from scourse where cno not in('1'))
//第四题
select sname,sno from student where sno in(select sno from scourse group by sno having count(sno)=(select count(*) from course))
select student.sname,student.sno from student where student.sno in(select scourse.sno from scourse group by scourse.sno having count(scourse.sno)=(select count(*) from course))
//第五题
select sname from student where sno in (select sno from student where Inyear='1999') and sno in (select sno from scourse where Gmark is null) and spno in(select spno from major where spname='计算机软件')
select student.sname from student where student.sno in (select student.sno from student where student.Inyear='1999') and student.sno in (select scourse.sno from scourse where
相关文档：
索引的创建及使用(sqlserver 2005)
为指定表或视图创建关系索引，或为指定表创建 XML 索引。可在向表中填入数据前创建索引。可通过指定限定的数据库名称，为另一个数据库中的表或视图创建索引。
Transact-SQL 语法约定
语法
Create Relational Index CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] IN ......
触发器是一种特殊的存储过程，类似于其它编程语言中的事件函数，SQL Server? 允许为 INSERT、UPDATE、DELETE 创建触发器，当在表（视图）中插入、更新、删除记录时，触发一个或一系列 T-SQL 语句。
本专题的目的并非是向您详尽地介绍触发器，而是系统地向您介绍触发器的常用知识与相关应用，并与您� ......
1、创建数据库testdb
2、创建表test
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
[id] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[name] [nchar](10) COLLATE Chinese_PRC_CI_AS NULL,
[sex] [bit] NULL
) ON [PRIMARY]
3、创建数据库db
......
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
public class CommandInfo
{
public string CommandText;
public SqlPa ......
