sql创建查询视图语句

学生表

c表

s表

sc表

 

--1.查询全体学生的学号，姓名与年龄
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.查询计算机，数学系和信息系学生的姓名
SELECT sname
FROM S
WHERE sdept in('计算机','数学','信息工程');

--9.查询不是计算机和信息系的学生姓名
SELECT sname
FROM S
WHERE sdept not in('计算机','信息工程');

--10.查询所有姓刘的学生姓名，学号
SELECT sname,sno
FROM S
WHERE sname like '刘%';

--11.查询与数据库相关课程的课程号和名称
SELECT cno,cname
FROM C
WHERE cname like '数据库%';

--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号课程的学生的学号及其成绩，查询结果按照分数降序排列
SELECT sno,grade
FROM SC
WHERE cno='0101'
ORDER BY grade DESC;

--16.查询学生总人数
SELECT COUNT(distinct sno) as 学生总人数
FROM S;

--17.查询选修了课程的学生人数
SELECT COUNT(DISTINCT sno) as 学生人数
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号课程的学生最低分数
SELECT MIN(grade) as 最低分
FROM SC
WHERE cno='0102';

--21.查询学生001108选修课程的总学分数
SELECT SUM(ccredit ) as 总学分
FROM SC,C
WHERE sno='001108' and SC.cno=C.cno ;

--22.查询每门课程号及其对应的选课人数
SELECT cno,COUNT(sno) as 选课人数
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.查询“计算机”系所有选修了“数据库原理”课程的学生姓名
select sname
from s,c,sc
where s.sno=sc.sno and sc.cno=c.cno and sdept='计算机' and cname='数据库原理'

 

--33.显示至少有5名学生选修的课程的平均分数及其课程名称
select avg(grade) as 平均分数,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.查询每个人选修的课程数，显示其学号及课程数
select sno,count(distinct cno) as 课程数
from sc
group by sno

--35.查询有3门以上课程是70分以上的学生的学号及（70分以上的）课程数
select sno,count(distinct cno) 课程数
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使用的各种零件的号码及其数量
select pno,qty
from spj
where jno='j2'

--4)    找出天津的工程项目使用的各种零件的号码、数量及其对应的工程号
select pno,qty,spj.jno
from spj,j
where spj.jno=j.jno and city='天津'

--5)   找出工程项目J2使用的各种零件的名称及其数量
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)    找出使用上海产的零件的工程名称
select jname
from j,spj,s
where spj.jno=j.jno and s.sno=spj.sno and s.city='上海'  

--嵌套查询：
--1)    显示与“螺母”颜色相同的产品名
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)    找出没有使用天津产的零件的工程号码
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) 求没有使用天津供应商生产的红色零件的工程号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='天津')

--数据更新
--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.    创建一个视图，包含所有的供应商名称，供应的零件名称以及供应的项目号。查询该视图；在视图中添加一条记录看是否能正常添加；在视图中删除一条记录，看能否正常删除
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.    创建一个视图，包含三建项目的供应情况，包括供应商名称，零件名称以及供应数量。针对该试图完成下列查询：
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)    找出三建工程使用的各种零件代号及其数量
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.    建立计算机系学生的视图（创建两个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，‘物理’），观察执行结果,是否能成功添加
INSERT
INTO IS_S1
VALUES('20110212','王宇','1','1993-10-10','物理')
--不能添加成功 视图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一点视图指定了

--IS_S2中添加学生（‘20110213’，‘张丽君’，‘女’，1993-12-10，‘数学’），观察执行结果,是否能成功添加
--执行之后，分别查询IS_S1和IS_S2，查看上述两条记录是否添加成功，两个视图查询结果是否有差异；
INSERT
INTO IS_S2
VALUES('20110213','张丽君','0','1993-12-10','数学')
--能插成功
select *
from IS_S2;

--视图IS_S2看不到张丽君的查询结果

 

--2.    创建一个视图，将学生的学号及他的平均成绩定义为一个视图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分，观察执行结果
UPDATE S_G
SET avggrade=avggrade+5;
--对视图或函数 'S_G' 的更新或插入失败，因其包含派生域或常量域。

--3.    创建一个视图，用于存放计算机系所有学生的学生姓名，所选课程名称及其成绩；
--然后查询该视图；
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
--通过该视图删除当中一条记录，观察执行结果
DELETE
FROM IS_STU
WHERE sname='李方方'
--不可删除 视图或函数'IS_STU'不可跟新，因为修改会影响多个基表

--4.    创建一个视图，用于存放选修了“计算机导论”的学生姓名，学号，班号，其成绩选项为空；
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
--更新该视图中‘韦平平’的成绩，置其成绩为90，观察执行结果
UPDATE IS_STUDENT
SET grade=90
WHERE sname='韦平平'

--注：班号是学号的前四位，可以采用对学号取函数的方法
--substring(S.sno,1,4)

 

 

--1.    用SQL语句创建职工表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中的工资的数据类型由字符型改为整数；
ALTER TABLE WORKS
ALTER COLUMN SALARY INT
--3.    观察每张表格是否有索引产生；
--EMP和COMP有索引

--4.    用SQL语句将下列数据插入表格：
--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,秦淮区瑞金路); (C2,顺丰快递南京江宁分公司1,江宁区禄口镇); (C3,顺丰快递南京江宁分公司2,江宁开发区); (C4,顺丰快递南京秦淮分公司2,秦淮区长乐路);
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','秦淮区瑞金路'),
('C2','顺丰快递南京江宁分公司1','江宁区禄口镇'),
('C3','顺丰快递南京江宁分公司2','江宁开发区'),
('C4','顺丰快递南京秦淮分公司2','秦淮区长乐路');

--5.    完成下列更新（使用SQL语句）
--将所有员工的年龄增加一岁；
UPDATE EMP
SET AGE=AGE+1

--将地址为江宁开发区的公司地址改为“江宁开发区胜太路”；
UPDATE COMP
SET VADDR='江宁开发区胜太路'
WHERE VADDR='江宁开发区'

--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

SELECT *
FROM WORKS X
WHERE SALARY>(
    SELECT AVG(SALARY)
    FROM WORKS Y
    WHERE X.CNO=Y.CNO
    )

 

--    查询工资高于5000元的职工工号和姓名
SELECT WORKS.ENO,ENAME
FROM WORKS,EMP
WHERE WORKS.ENO=EMP.ENO AND SALARY>5000