不相關子查詢
select * from T where T. name!= '張爽' and T. department= (
select T. department from T where T. name= '張爽' )
select Sid, sname from S where Sid in (
select Sid from SC where SC. Cid= (
select Cid from C where C. cname= '資料結構' ) )
select sname from S where Sid in (
select Sid from SC where SC. Cid= (
select Cid from C where C. cname= 'Java語言' ) )
select AVG ( score) from SC where Sid in (
select Sid from S where S. nativeplace like '江西%' )
select * from S where S. Sid= (
select Sid from SC where SC. Cid= '103' and score> (
select AVG ( score) from SC where SC. Cid= '103' ) )
select * from S where Sid not in (
select Sid from SC where SC. Cid= (
select Cid from C where C. cname= 'Java語言' ) )
select * from S
except
select * from S where Sid in (
select Sid from SC where SC. Cid= (
select Cid from C where C. cname= 'Java語言' ) )
select * from S where S. nativeplace not like '江西%' and age< all (
select age from S where S. nativeplace like '江西%' )
select * from S where S. nativeplace not like '江西%' and age< (
select min ( age) from S where S. nativeplace like '江西%' )
資料表
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 )