高階查詢
select * from c, t where c. Tid= t. Tid and t. name like '李%'
select s. Sid, sname, cname, score from s, C, SC where s. Sid= SC. Sid and score> 80
select t. name, C. cname from C, t where c. Tid= t. Tid and department= '計算機系' and name like '[張,李]%'
select sex, AVG ( score) from s, SC where S. Sid= sc. sid and sex= '男' group by sex
select cname, COUNT ( * ) as sum_s, MAX ( score) , avg ( score) , MIN ( sc. score) from C, SC where c. cid= sc. cid group by cname order by COUNT ( * ) desc
select * from t where department in ( select department from t where name= '張爽' )
select t1. * from t t1, t t2 where t1. department= t2. department and t2. name= '張爽'
select s. Sid, sname from s, SC where s. Sid= sc. Sid group by s. Sid, sname having COUNT ( * ) >= 2
select C. Cid, c. cname, COUNT ( sid) as '人數' from C, SC where c. Cid = sc. Cid group by c. cname, c. Cid
資料表
create database jxgl
go
use jxgl
go
create table T(
Tid nchar ( 5 ) primary key ,
name nchar ( 10 ) not null ,
sex nchar ( 2 ) default '男' ,
workingtime datetime ,
plandscape nchar ( 20 ) ,
degree nchar ( 10 ) ,
title nvarchar( 6 ) ,
department nvarchar( 20 ) ,
tel nchar ( 11 )
)
create table S(
Sid nchar ( 10 ) primary key ,
sname nchar ( 10 ) ,
sex nchar ( 2 ) default '男' ,
birthday Date ,
age as datediff( yy, birthday, getdate( ) ) ,
entrydate date ,
plandscape nchar ( 10 ) default '團員' ,
nativeplace nvarchar( 20 ) ,
class as substring( Sid, 7 , 2 )
)
create table C(
Cid nchar ( 3 ) primary key ,
cname nvarchar( 20 ) Not null ,
category nchar ( 4 ) Not null check ( category in ( '考試' , '考查' ) ) default '考試' ,
credit int ,
Tid nchar ( 5 ) foreign key references T( Tid) on update cascade
)
create table SC(
Sid nchar ( 10 ) foreign key references S( Sid) on update cascade on delete cascade ,
Cid nchar ( 3 ) ,
score int check ( score between 0 and 100 ) ,
primary key ( Sid, Cid) ,
foreign key ( Cid) references C( Cid) on update cascade on delete cascade
)
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95011' , N'趙西明 ' , N'男 ' , CAST( 0x0000768300000000 AS DateTime ) , N'群眾 ' , N'碩士 ' , N'副教授' , N'軟體' , N'13733152369' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95012' , N'李小平 ' , N'男 ' , CAST( 0x0000775E00000000 AS DateTime ) , N'黨員 ' , N'碩士 ' , N'教授' , N'計算機系' , N'13733152370' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95013' , N'張爽 ' , N'男 ' , CAST( 0x00007BC700000000 AS DateTime ) , N'群眾 ' , N'本科 ' , N'副教授' , N'計算機系' , N'13733152371' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95014' , N'李麗寧 ' , N'女 ' , CAST( 0x00007BC700000000 AS DateTime ) , N'黨員 ' , N'碩士 ' , N'副教授' , N'計算機系' , N'13733152372' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95015' , N'張金明 ' , N'男 ' , CAST( 0x0000812C00000000 AS DateTime ) , N'群眾 ' , N'碩士 ' , N'講師' , N'計算機系' , N'13733152373' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95016' , N'李燕 ' , N'女 ' , CAST( 0x000082C800000000 AS DateTime ) , N'黨員 ' , N'碩士 ' , N'講師' , N'計算機系' , N'13733152374' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95017' , N'宛平 ' , N'女 ' , CAST( 0x0000840B00000000 AS DateTime ) , N'群眾 ' , N'博士 ' , N'副教授' , N'軟體' , N'13733152375' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95018' , N'陳江川 ' , N'男 ' , CAST( 0x0000883900000000 AS DateTime ) , N'群眾 ' , N'博士 ' , N'講師' , N'軟體' , N'13733152376' )
INSERT [ dbo] . [ T] ( [ Tid] , [ name] , [ sex] , [ workingtime] , [ plandscape] , [ degree] , [ title] , [ department] , [ tel] ) VALUES ( N'95019' , N'郭新 ' , N'男 ' , CAST( 0x00008CAA00000000 AS DateTime ) , N'黨員 ' , N'博士 ' , N'講師' , N'軟體' , N'13733152377' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071101' , N'張佳 ' , N'女 ' , CAST( 0x3F1D0B00 AS Date ) , CAST( 0x3C3D0B00 AS Date ) , N'團員 ' , N'江西南昌' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071102' , N'好生 ' , N'男 ' , CAST( 0xCD1E0B00 AS Date ) , CAST( 0x3C3D0B00 AS Date ) , N'團員 ' , N'廣州順德' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071203' , N'徐克 ' , N'男 ' , CAST( 0xBF210B00 AS Date ) , CAST( 0x3C3D0B00 AS Date ) , N'團員 ' , N'江西南昌' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071204' , N'葉飛 ' , N'女 ' , CAST( 0xD1210B00 AS Date ) , CAST( 0x3C3D0B00 AS Date ) , N'黨員 ' , N'上海' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071205' , N'任偉 ' , N'男 ' , CAST( 0x7B220B00 AS Date ) , CAST( 0x3C3D0B00 AS Date ) , N'團員 ' , N'北京順義' )
INSERT [ dbo] . [ S] ( [ Sid] , [ sname] , [ sex] , [ birthday] , [ entrydate] , [ plandscape] , [ nativeplace] ) VALUES ( N'2017071206' , N'江賀 ' , N'男 ' , CAST( 0x02200B00 AS Date ) , CAST( 0x3D3D0B00 AS Date ) , N'黨員 ' , N'福建廈門' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'101' , N'計算機基礎' , N'考試 ' , 2 , N'95011' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'102' , N'C語言' , N'考試 ' , 3 , N'95012' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'103' , N'計算機組成原理' , N'考試 ' , 3 , N'95012' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'104' , N'組合語言' , N'考試 ' , 3 , N'95014' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'105' , N'Java語言' , N'考查 ' , 2 , N'95015' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'106' , N'作業系統' , N'考試 ' , 3 , N'95016' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'107' , N'資料結構' , N'考試 ' , 3 , N'95017' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'108' , N'編譯原理' , N'考試 ' , 3 , N'95017' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'109' , N'網路基礎' , N'考試 ' , 3 , N'95017' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'110' , N'資料庫原理' , N'考試 ' , 3 , N'95017' )
INSERT [ dbo] . [ C] ( [ Cid] , [ cname] , [ category] , [ credit] , [ Tid] ) VALUES ( N'120' , N'SQLServer' , N'考查 ' , 2 , N'95018' )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071101' , N'101' , 90 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071101' , N'102' , 70 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071101' , N'103' , 48 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071101' , N'105' , 80 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'102' , 90 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'104' , 77 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'106' , 68 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'107' , 85 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'109' , 77 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'110' , 65 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071102' , N'120' , 48 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'102' , 65 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'104' , 82 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'105' , 79 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'107' , 55 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'110' , 77 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071203' , N'120' , 67 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'101' , 86 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'102' , 86 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'104' , 77 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'105' , 84 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'106' , 95 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'108' , 91 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071204' , N'110' , 82 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071205' , N'101' , 63 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071205' , N'102' , 84 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071205' , N'103' , 77 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071206' , N'107' , 58 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071206' , N'108' , 74 )
INSERT [ dbo] . [ SC] ( [ Sid] , [ Cid] , [ score] ) VALUES ( N'2017071206' , N'109' , 74 )