sql創(chuàng)建查詢視圖語句

這篇具有很好參考價值的文章主要介紹了sql創(chuàng)建查詢視圖語句。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點擊"舉報違法"按鈕提交疑問。

學生表

c表

sql創(chuàng)建查詢視圖語句

s表

sql創(chuàng)建查詢視圖語句

sc表

sql創(chuàng)建查詢視圖語句

?文章來源地址http://www.zghlxwxcb.cn/news/detail-450779.html

--1.查詢?nèi)w學生的學號，姓名與年齡
SELECT sno,sname,age=year(getdate())-year(sbirth)
FROM S;

--2.查詢計算機系（CS）的學生學號，姓名
SELECT sno,sname
FROM S
WHERE sdept='計算機';

--3.查詢計算機系所有的女生姓名和年齡
SELECT sname,age=(year(getdate())-year(sbirth))
FROM S
WHERE sdept='計算機' and ssex='0';

--4.查詢選修了課程的學生學號（只保留不重復的學號）
SELECT DISTINCT sno
FROM SC
WHERE cno IS NOT NULL;

--5.查詢所有年齡在20歲以下的學生姓名及其年齡**
SELECT sname,age=(year(getdate())-year(sbirth) )
FROM S
WHERE (year(getdate())-year(sbirth))<20;

--6.查詢考試有不及格的學生的學號
SELECT DISTINCT sno
FROM SC
WHERE grade<60 or grade is NULL;

--7.查詢年齡在18-21歲之間（包含18和21歲）的學生的姓名和年齡**
SELECT sname,2022-year(sbirth) as age
FROM S
WHERE 2022-year(sbirth) BETWEEN? 18 AND 21;

SELECT sname,age=(year(getdate())-year(sbirth))
FROM S
WHERE (year(getdate())-year(sbirth))<=21
and (year(getdate())-year(sbirth))>=18;

--8.查詢計算機，數(shù)學系和信息系學生的姓名
SELECT sname
FROM S
WHERE sdept in('計算機','數(shù)學','信息工程');

--9.查詢不是計算機和信息系的學生姓名
SELECT sname
FROM S
WHERE sdept not in('計算機','信息工程');

--10.查詢所有姓劉的學生姓名，學號
SELECT sname,sno
FROM S
WHERE sname like '劉%';

--11.查詢與數(shù)據(jù)庫相關課程的課程號和名稱
SELECT cno,cname
FROM C
WHERE cname like '數(shù)據(jù)庫%';

--12.查詢未錄入成績的學生的學號和相應的課程號
SELECT sno,cno
FROM SC
WHERE grade IS NULL;

--13.查詢計算機系年齡在20歲以下的學生姓名**
SELECT sname
FROM? S
WHERE sdept='計算機' and 2022-year(sbirth)<20;

--14.查詢選修了0102號課程的學生的學號及其成績，查詢結果按照學號升序排列
SELECT sno,grade
FROM? SC
WHERE cno='0102'
ORDER BY sno;

--15.查詢選修了0101號課程的學生的學號及其成績，查詢結果按照分數(shù)降序排列
SELECT sno,grade
FROM SC
WHERE cno='0101'
ORDER BY grade DESC;

--16.查詢學生總?cè)藬?shù)
SELECT COUNT(distinct sno) as 學生總?cè)藬?shù)
FROM S;

--17.查詢選修了課程的學生人數(shù)
SELECT COUNT(DISTINCT sno) as 學生人數(shù)
FROM SC;

--18.計算0101號課程的平均成績
SELECT AVG(grade) as 平均成績
FROM SC
WHERE? cno='0101';

--19.計算計算機系學生的平均年齡
SELECT AVG(2022-year(sbirth)) as 平均年齡
FROM S
WHERE sdept='計算機';

--20.查詢選修了0102號課程的學生最低分數(shù)
SELECT MIN(grade) as 最低分
FROM SC
WHERE cno='0102';

--21.查詢學生001108選修課程的總學分數(shù)
SELECT SUM(ccredit ) as 總學分
FROM SC,C
WHERE sno='001108' and SC.cno=C.cno ;

--22.查詢每門課程號及其對應的選課人數(shù)
SELECT cno,COUNT(sno) as 選課人數(shù)
FROM SC
GROUP BY cno;

--23.計算每門課程及其平均分
SELECT cno,AVG(grade) as 平均分
FROM SC
GROUP BY cno;

--24.計算選修了3門課程以上的學生學號
SELECT sno
FROM SC
GROUP BY sno
HAVING COUNT(*)>3;

--25.查詢每個學生的學號及課程的平均分
SELECT sno,AVG(grade) 平均分
FROM SC
GROUP BY sno;

--26.查詢所有課程的平均學分
SELECT Cno,AVG(ccredit) as 平均分
FROM C
GROUP BY cno;
--27.查詢與“林一帆”在同一個系學習的學生學號和姓名（不包括林一帆本人
select sno,sname
from s
where sname <> '林一帆' and sdept in
?? ?(select sdept
?? ?from s
?? ?where sname='林一帆'
?? ?)

--28.查詢平均分在75分以下的學生信息
SELECT distinct *
from S
where sno in
?? ?(select SC.sno
?? ?from sc
?? ?group by sc.sno
?? ?HAVING? AVG(Grade)<75)

--29.查詢選修0101號課程且成績在80分以上的所有學生姓名，學號
select sname,s.sno
from s,sc
where s.sno=sc.sno and cno='0101' and sc.grade>80

--30.查詢每個學生的學號，姓名，選修的課程名及其成績
select s.sno,sname,cname,grade
from s,c,sc
where s.sno=sc.sno and sc.cno=c.cno ;

--31.顯示每個系的學生的平均年齡
select sdept,avg(2022-year(sbirth)) as 平均年齡
from s
group by sdept

--32.查詢“計算機”系所有選修了“數(shù)據(jù)庫原理”課程的學生姓名
select sname
from s,c,sc
where s.sno=sc.sno and sc.cno=c.cno and sdept='計算機' and cname='數(shù)據(jù)庫原理'

--33.顯示至少有5名學生選修的課程的平均分數(shù)及其課程名稱
select avg(grade) as 平均分數(shù),cname
from c,sc
where c.cno=sc.cno
group by cname
having count(*)>5

select c.cname,t.avggrade
from c, (
?? ?select cno,avg(grade) as avggrade,count(*) as NUM
?? ?from sc
?? ?group by cno
?? ?)as t
?? ?where NUM>=5 and t.cno=c.cno

--34.查詢每個人選修的課程數(shù)，顯示其學號及課程數(shù)
select sno,count(distinct cno) as 課程數(shù)
from sc
group by sno

--35.查詢有3門以上課程是70分以上的學生的學號及（70分以上的）課程數(shù)
select sno,count(distinct cno) 課程數(shù)
from sc
where sno in(
select sno
from sc
where grade>70
)
group by sno
having count(*)>3

--36.查詢“C++程序設計”這門課程的學生成績
select grade
from c,sc
where c.cno=sc.cno and cname='C++程序設計'

--37.求年齡大于學生平均年齡的男學生姓名和年齡
select sname,age=(year(getdate())-year(sbirth))
from s x
where ssex=1 and year(getdate())-year(sbirth)>
??? (select avg(year(getdate())-year(sbirth))
?? ?from s y
?? ?);

--38.查詢成績高于該門課程平均分的學生學號和課程號
select sno,cno
from sc x
where grade >
?? ?(select avg(grade)
?? ?from sc y
?? ?where y.cno=x.cno
?? ?);

--39.查詢每門課程最高分的學生學號
select cno,sno
from sc x
where grade =
(
select max(grade)
from sc y
where x.cno=y.cno
)

--40.查詢每門課程最低分的學生學號
select cno,sno
from sc x
where grade =
(
select min(grade)
from sc y
where x.cno=y.cno
)

--41.查詢每門課程的最低分及其課程名稱
select cname,x.grade
from c,sc x
where x.cno = c.cno and grade in
(
select min(grade)
from sc y
where x.cno=y.cno
)

--42.查詢所有學生及其選修課程的課程名稱，成績（保留未選課的學生信息）
select s.sno,cname,grade
from s left join sc on s.sno=sc.sno
? ? ? ? ?? left join c on? c.cno=sc.cno

--43.顯示所有課程的成績，按照課程號升序排列，成績降序排列（觀察空值最先顯示還是最后顯示）
select cno,grade
from sc
order by cno asc,grade desc

SPJ表查詢

--連接查詢：
--1)?? ?求供應工程J1零件的供應商號碼SNO
select distinct sno
from SPJ
where jno='J1'

--2)?? ?求使用供應商S1所供應零件的工程號碼
select distinct jno
from spj
where sno='s1'

--3)?? ?找出工程項目J2使用的各種零件的號碼及其數(shù)量
select pno,qty
from spj
where jno='j2'

--4)?? ?找出天津的工程項目使用的各種零件的號碼、數(shù)量及其對應的工程號
select pno,qty,spj.jno
from spj,j
where spj.jno=j.jno and city='天津'

--5)?? 找出工程項目J2使用的各種零件的名稱及其數(shù)量
select pname,qty
from p,spj
where p.pno=spj.pno and jno='J2'

--6)?? ?找出上海廠商供應的所有零件號碼
select distinct pno
from s,spj
where spj.sno=s.sno and city='上海'

--7)?? ?找出使用上海產(chǎn)的零件的工程名稱
select jname
from j,spj,s
where spj.jno=j.jno and s.sno=spj.sno and s.city='上海' ?

--嵌套查詢：
--1)?? ?顯示與“螺母”顏色相同的產(chǎn)品名
select pname
from p
where pname<>'螺母' and color =
?? ?(
?? ?select color
?? ?from p
?? ?where pname='螺母'
?? ?)

--2)?? ?查出工程J1和J3同時使用的零件名稱
select distinct p.pname
from p,spj x,spj y
where p.pno=x.pno and x.pno=y.pno and p.pno=Y.PNO and X.JNO='J1' and Y.JNO='J3';

select PNAME
from spj,P
where spj.pno in (select pno from spj where jno='J1') and jno='J3'and spj.PNO=p.PNO;

--3)?? ?找出沒有使用天津產(chǎn)的零件的工程號碼
select distinct jno
from spj
where sno not in
?? ?(
?? ?select sno
?? ?from s
?? ?where city='天津'
?? ?)

--4）找出北京的任何工程都不購買的零件的零件號
SELECT PNO
FROM P
WHERE NOT EXISTS
?? ?(SELECT *
?? ?FROM SPJ,S
?? ?WHERE SPJ.PNO=P.PNO AND SPJ.SNO=S.SNO AND S.CITY='北京')

--5) 求沒有使用天津供應商生產(chǎn)的紅色零件的工程號JNO
SELECT JNO
FROM J
WHERE NOT EXISTS
?? ?(SELECT *
?? ?FROM S,P,SPJ
?? ?WHERE SPJ.JNO=J.JNO
?? ??? ?and SPJ.SNO=S.SNO
?? ??? ?and SPJ.PNO=P.PNO
?? ??? ?and S.CITY = '天津'
?? ??? ?and P.COLOR = '紅'
?? ?)

select jno
from j
where jno not in(
select spj.jno
from s,spj,p
where s.sno=spj.sno and p.pno=spj.pno and p.color='紅' and s.city='天津')

--數(shù)據(jù)更新
--1)?? ?將全部紅色零件的顏色改為藍色
update p
set color='藍'
where color='紅'

--2)?? ?將S5供給J4的零件P6改為S3供應
update spj
set pno='S3'
where sno='S5' and jno='J4' and pno='P6'
--3)?? ?從供應商關系中刪除S2的記錄，并從供應關系中刪除相應的記錄
DELETE
FROM SPJ
WHERE SNO='S2'

delete
from s
where sno='s2'
--4)?? ?試將（S2,J6,P4,200）插入供應情況關系
insert
into SPJ(SNO,PNO,JNO,QTY)
values('S2','J6','P4',200)
--視圖
--1.?? ?創(chuàng)建一個視圖，包含所有的供應商名稱，供應的零件名稱以及供應的項目號。查詢該視圖；在視圖中添加一條記錄看是否能正常添加；在視圖中刪除一條記錄，看能否正常刪除
create view? IS_SPJ
as
select sname,pname,jno
from spj,p,s
where spj.pno=p.pno and spj.sno=s.sno
--查詢該視圖
select *
from IS_SPJ
--添加
INSERT
INTO IS_SPJ
VALUES('精益','螺母','J8')
--刪除
DELETE
FROM IS_SPJ
WHERE JNO='J1'

--2.?? ?創(chuàng)建一個視圖，包含三建項目的供應情況，包括供應商名稱，零件名稱以及供應數(shù)量。針對該試圖完成下列查詢：
CREATE VIEW IS_SPJ1
AS
SELECT SPJ.SNO,SNAME,SPJ.PNO,PNAME,QTY
FROM S,P,SPJ
WHERE SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO AND JNO IN
(SELECT JNO
FROM J
WHERE JNAME='三建'
)

--1)?? ?找出三建工程使用的各種零件代號及其數(shù)量
SELECT p.pno,sum(qty) sumqty
FROM IS_SPJ1,p
where p.pname=IS_SPJ1.pname
group by p.pno

--2)?? ?找出供應商S1的供應情況
SELECT distinct *
FROM IS_SPJ1
WHERE SNO='S1'

視圖

基于S-C表

USE [S-C]
--1.?? ?建立計算機系學生的視圖（創(chuàng)建兩個IS_S1和IS_S2，一個定義中帶有with check option，另一個不帶）；
--然后分別查詢兩個視圖；
CREATE VIEW IS_S1
AS
SELECT *
FROM S
WHERE sdept='計算機'
WITH CHECK OPTION;

CREATE VIEW IS_S2
AS
SELECT *
FROM S
WHERE sdept='計算機';

SELECT * FROM IS_S1
SELECT * FROM IS_S2
--分別在兩個視圖中添加如下信息：
--IS_S1中添加學生（‘20110212’，‘王宇’，‘男’，1993-10-10，‘物理’），觀察執(zhí)行結果,是否能成功添加
INSERT
INTO IS_S1
VALUES('20110212','王宇','1','1993-10-10','物理')
--不能添加成功視圖進行的插入或更新已失敗，原因是目標視圖或者目標視圖所跨越的某一點視圖指定了

--IS_S2中添加學生（‘20110213’，‘張麗君’，‘女’，1993-12-10，‘數(shù)學’），觀察執(zhí)行結果,是否能成功添加
--執(zhí)行之后，分別查詢IS_S1和IS_S2，查看上述兩條記錄是否添加成功，兩個視圖查詢結果是否有差異；
INSERT
INTO IS_S2
VALUES('20110213','張麗君','0','1993-12-10','數(shù)學')
--能插成功
select *
from IS_S2;

--視圖IS_S2看不到張麗君的查詢結果

--2.?? ?創(chuàng)建一個視圖，將學生的學號及他的平均成績定義為一個視圖S_G；
CREATE VIEW S_G
AS
SELECT sno,avg(grade)avggrade
FROM sc
GROUP BY sno
--然后查詢該視圖；
select * from S_G
--通過該視圖查詢平均成績大于75分的學生學號；
select sno
from S_G
where avggrade>75
--將該視圖中所有學生的平均成績加5分，觀察執(zhí)行結果
UPDATE S_G
SET avggrade=avggrade+5;
--對視圖或函數(shù) 'S_G' 的更新或插入失敗，因其包含派生域或常量域。

--3.?? ?創(chuàng)建一個視圖，用于存放計算機系所有學生的學生姓名，所選課程名稱及其成績；
--然后查詢該視圖；
CREATE VIEW IS_STU
AS
SELECT sname,cname,grade
FROM s,c,sc
WHERE s.sno=sc.sno and c.cno=sc.cno and s.sdept='計算機'

select * from IS_STU
--通過該視圖刪除當中一條記錄，觀察執(zhí)行結果
DELETE
FROM IS_STU
WHERE sname='李方方'
--不可刪除視圖或函數(shù)'IS_STU'不可跟新，因為修改會影響多個基表

--4.?? ?創(chuàng)建一個視圖，用于存放選修了“計算機導論”的學生姓名，學號，班號，其成績選項為空；
CREATE VIEW IS_STUDENT
AS
SELECT SNAME,S.SNO,substring(S.sno,1,4)sub,grade=NULL
FROM SC,S,C
WHERE SC.SNO=S.SNO and SC.CNO=C.CNO and C.CNAME='計算機導論'

--然后查詢該視圖；
select * from IS_STUDENT
--更新該視圖中‘韋平平’的成績，置其成績?yōu)?0，觀察執(zhí)行結果
UPDATE IS_STUDENT
SET grade=90
WHERE sname='韋平平'

--注：班號是學號的前四位，可以采用對學號取函數(shù)的方法
--substring(S.sno,1,4)

--1.?? ?用SQL語句創(chuàng)建職工表EMP(ENO,ENAME,AGE,SEX) ，其屬性分別為職工號，姓名，年齡和性別；工作表：WORKS(ENO,CNO,SALARY)，
--其屬性分別為職工號，工作的公司編號和工資（字符型）；公司表：COMP(CNO,CNAME,VADDR)，其屬性分別為公司編號，公司名和地址。
--指定每張表的主鍵和外鍵，是否為空等約束條件。
use [ewc]
CREATE TABLE EMP
?? ?(ENO VARCHAR(20) PRIMARY KEY,
?? ?ENAME NVARCHAR(20) NOT NULL,
?? ?AGE SMALLINT,
?? ?SEX CHAR(2),
?? ?);

CREATE TABLE COMP
?? ?(CNO VARCHAR(2) PRIMARY KEY,
?? ?CNAME CHAR(50),
?? ?VADDR CHAR(50),
?? ?);

CREATE TABLE WORKS
?? ?(ENO VARCHAR(20),
?? ?CNO CHAR(2),
?? ?SALARY CHAR(20),
?? ?PRIMARY KEY(ENO,CNO),
?? ?FOREIGN KEY(ENO) REFERENCES EMP(ENO),
?? ?FOREIGN KEY(CNO) REFERENCES COMP(CNO)
?? ?);

--2.?? ?將WORKS中的工資的數(shù)據(jù)類型由字符型改為整數(shù)；
ALTER TABLE WORKS
ALTER COLUMN SALARY INT
--3.?? ?觀察每張表格是否有索引產(chǎn)生；
--EMP和COMP有索引

--4.?? ?用SQL語句將下列數(shù)據(jù)插入表格：
--EMP：(E1,劉晨,25,男); (E2,董方,23,男); (E3,李逸,22,女); (E4,陸麗,25,女); (E5,陳東,27,男); (E6,劉暢,25,女); (E7,張帆,25,男);
--WORKS：(E1,C1,3000); (E2,C1,4000); (E3,C1,3000); (E4,C1,8000); (E5,C2,7000); (E6,C2,3000); (E7,C3,3000); (E5,C4,3000); (E7,C4,3000);
--COMP：(C1,順豐快遞南京秦淮分公司1,秦淮區(qū)瑞金路); (C2,順豐快遞南京江寧分公司1,江寧區(qū)祿口鎮(zhèn)); (C3,順豐快遞南京江寧分公司2,江寧開發(fā)區(qū)); (C4,順豐快遞南京秦淮分公司2,秦淮區(qū)長樂路);
INSERT
INTO EMP
VALUES('E1','劉晨',25,'男'),
('E2','董方',23,'男'),
('E3','李逸',22,'女'),
('E4','陸麗',25,'女'),
('E5','陳東',27,'男'),
('E6','劉暢',25,'女'),
('E7','張帆',25,'男');

INSERT
INTO WORKS
VALUES('E1','C1','3000'),
('E2','C1','4000'),
('E3','C1','3000'),
('E4','C1','8000'),
('E5','C2','7000'),
('E6','C2','3000'),
('E7','C3','3000'),
('E5','C4','3000'),
('E7','C4','3000');

INSERT
INTO COMP
VALUES('C1','順豐快遞南京秦淮分公司1','秦淮區(qū)瑞金路'),
('C2','順豐快遞南京江寧分公司1','江寧區(qū)祿口鎮(zhèn)'),
('C3','順豐快遞南京江寧分公司2','江寧開發(fā)區(qū)'),
('C4','順豐快遞南京秦淮分公司2','秦淮區(qū)長樂路');

--5.?? ?完成下列更新（使用SQL語句）
--將所有員工的年齡增加一歲；
UPDATE EMP
SET AGE=AGE+1

--將地址為江寧開發(fā)區(qū)的公司地址改為“江寧開發(fā)區(qū)勝太路”；
UPDATE COMP
SET VADDR='江寧開發(fā)區(qū)勝太路'
WHERE VADDR='江寧開發(fā)區(qū)'

--6.?? ?完成下列查詢
--??? ?查詢在“順豐快遞南京秦淮分公司1”工作的職工工號
SELECT WORKS.ENO
FROM COMP,WORKS
WHERE WORKS.CNO=COMP.CNO AND CNAME='順豐快遞南京秦淮分公司1'

--??? ?查詢有兼職的員工工號
SELECT ENO
FROM WORKS
GROUP BY ENO
HAVING COUNT(*)>1

--??? ?假設每個職工可在多個公司工作，查詢在編號為C4和C3的公司兼職的職工工號
SELECT A.ENO
FROM WORKS A,WORKS B
WHERE A.ENO=B.ENO and A.cno='c4' and B.cno='c3'

--??? ?查詢不在“順豐快遞南京秦淮分公司1”工作的職工姓名
SELECT DISTINCT ENAME
FROM EMP
WHERE ENO NOT IN(
?? ?SELECT ENO
?? ?FROM WORKS
?? ?WHERE CNO IN(
?? ??? ?SELECT CNO
?? ??? ?FROM COMP
?? ??? ?WHERE CNAME='順豐快遞南京秦淮分公司1'
?? ??? ?)
?? ?)
--??? ?查詢工資高于其所在公司員工平均工資的所有員工
SELECT ENO
from WORKS,
(SELECT CNO,AVG(SALARY) AVG_SALARY
FROM WORKS
GROUP BY CNO
) B
WHERE WORKS.CNO=B.CNO AND SALARY>B.AVG_SALARY