学生表
c表
s表
sc表
文章来源地址https://www.toymoban.com/news/detail-450779.html
--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;
文章来源:https://www.toymoban.com/news/detail-450779.html
--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
到了这里,关于sql创建查询视图语句的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!