sql-server相關子查詢

一個勤勤懇懇的q大炮發表於2020-12-29

相關子查詢
--1．	查詢選修了107號課程的學生的學號和姓名。
select Sid,sname
from S
where exists(
select * from SC where Sid=S.Sid and Cid='107')
--2．	查詢沒有選修Java語言的學生姓名。
select sname
from S
where  exists(
 select * from C
  where cname='Java語言' and not exists(
select * from SC where Sid=S.Sid and Cid=C.Cid))
--3．	查詢每個學生超過他選修課程平均成績的學號和課程號。
select Sid,Cid
from SC
where SC.score>(
select AVG(score) from SC y where y.Sid=SC.Sid)
--4．	查詢與張爽老師在同一個系工作的老師。
select * from T
where name!='張爽' and exists(
select * from T y where y.name='張爽' and y.department=T.department)
--5．	查詢選修了所有課程的學生編號和姓名
select Sid,sname
from S
where not exists(
  select * from C where not exists(
     select * from SC where SC.Sid=S.Sid and SC.Cid=C.Cid)
)
--6．	查詢張佳同學沒有選的課程編號
select Cid
from C
where exists(
  select * from S where S.sname='張佳' and not exists(
    select * from SC where SC.Sid=S.Sid and SC.Cid=C.Cid)
)
--7．	查詢至少有兩位同學都選的課程號
select distinct Cid
from SC
where exists(
  select * from SC y where y.Cid=SC.Cid and y.Sid<>SC.Sid)

資料表
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)

sql-server不相關子查詢
2020-12-29
SQLServer
相關子查詢&非相關子查詢概念
2011-09-08
MySQL 相關子查詢
2022-12-26
MySql
sql語法相關子查詢與非相關子查詢
2013-10-05
SQL
什麼是SQL 語句中相關子查詢與非相關子查詢
2024-04-28
SQL
sql-server高階查詢
2020-12-29
SQLServer
區分關聯子查詢和非關聯子查詢
2019-05-21
exist-in和關聯子查詢-非關聯子查詢
2011-11-09
UNDO相關查詢
2014-03-16
CURL查詢ES相關
2021-02-05
關聯查詢子查詢效率簡單比照
2014-04-08
子查詢-表子查詢
2017-11-14
MySQL 之慢查詢相關操作
2021-01-20
MySql
表空間相關查詢
2015-03-23
關聯子查詢 Correlated Subqueries
2011-04-25
Oracle許可權相關查詢
2017-10-11
Oracle
[鎖機制] 鎖相關查詢
2013-09-18
鎖表的相關資訊查詢
2011-03-14
關聯子查詢的用處
2010-08-30
複雜查詢—子查詢
2020-11-16
Linux 查詢日誌相關命令
2020-01-08
Linux
Oracle 表空間查詢相關sql
2017-11-27
OracleSQL
查詢基表的相關檢視
2011-06-27
關於mysql 子查詢中使用 limit
2017-07-18
MySqlMIT
SQL查詢的：子查詢和多表查詢
2020-11-18
SQL
NOT IN、NOT EXISTS的相關子查詢改用LEFT JOIN--sql2000效能優化
2016-07-08
SQL優化
PostgreSQL並行查詢相關配置引數
2021-07-24
SQL並行
MySQL慢查詢日誌相關設定
2021-11-26
MySql
MS SQL SERVER索引優化相關查詢
2014-01-26
SQLServer索引優化
MySQL子查詢
2019-11-29
MySql
子串查詢
2020-11-04
使用子查詢
2017-09-27
MYsql 子查詢
2018-05-15
MySql
oracle子查詢
2014-02-18
Oracle
查詢子串
2015-03-23
子查詢分解
2008-05-20
11子查詢
2011-01-11
sql子查詢
2007-04-02
SQL

sql-server相關子查詢

相關文章