首页 > 数据库 >SQL Server内查询、联合查询、外查询

SQL Server内查询、联合查询、外查询

时间：2024-11-15 11:45:24浏览次数：3  
标签：Degree Sno Server Score Student SQL Cno 查询 SELECT

SQL Server内查询、联合查询、外查询

# 题目

14、查询所有学生的Sname、Cno和Degree列。

15、查询所有学生的Sno、Cname和Degree列。

17、查询“95033”班学生的平均分。

18、现查询所有同学的Sno、Cno、Degree和rank列（其中rank为成绩的等级，成绩转换成为等级的规则是：大于等于90分为A、小于90且大于等于80分为B、小于80且大于等于70分为C、小于70且大于等于60为D，小于60分为E）。

23、查询“张旭”老师听上课程的学生成绩。

29、查询选修编号为3-105课程，且成绩至少高于选修编号为3-245课程的同学的Cno、Sno、Degree，并按Degree从高到低次序排序。

41、查询“男”教师及其所上的课程。

43、查询和“李军”同性别的所有同学的Sname。

44、查询和“李军”同性别并同班的同学Sname。

45、查询所有选修“计算机导论”课程的“男”同学的成绩表。

# 表结构如下



Student（学生表）结构
属性名数据类型可否为空含义
SnoChar (3)否学号（主码）
SnameChar (8)否学生姓名
SsexChar (2)否学生性别
Sbirthdaydatetime可学生出生年月
ClassChar (5)可学生所在班级


Course（课程表） 
属性名数据类型可否为空含义
CnoChar (5)否课程号（主键）
CnameVarchar(10)否课程名称
TnoChar (3)否教工编号（外码）


Score（成绩表）
属性名数据类型可否为空含义
SnoChar (3)否学号（外码）
CnoChar (5)否课程号（外码）
DegreeDecimal(4,1)可成绩


Teacher（教师表） 
属性名数据类型可否为空含义
TnoChar (3)否教工编号（主码）
TnameChar (4)否教工姓名
TsexChar (2)否教工性别
Thirthdaydatetime可教工出生年月
ProfChar (6)可职称
DepartVarchar (10)否教工所在部门






# 创建数据库


请看上一章：SQLServer-创建表、查询、条件查询语句


# 创建数据表


CREATE TABLE Student (  
    Sno CHAR(3) NOT NULL PRIMARY KEY,  
    Sname CHAR(8) NOT NULL,  
    Ssex CHAR(2) NOT NULL,  
    Sbirthday DATETIME,  
    Class CHAR(5)  
);    

CREATE TABLE Teacher (  
    Tno CHAR(3) NOT NULL PRIMARY KEY,  
    Tname CHAR(4) NOT NULL,  
    Tsex CHAR(2) NOT NULL,  
    Tbirthday DATETIME,  
    Prof CHAR(6),  
    Depart VARCHAR(10) NOT NULL  
);
  
CREATE TABLE Course (  
    Cno CHAR(5) NOT NULL PRIMARY KEY,  
    Cname VARCHAR(10) NOT NULL,  
    Tno CHAR(3) NOT NULL,  
    FOREIGN KEY (Tno) REFERENCES Teacher(Tno)  
);  
  
CREATE TABLE Score (  
    Sno CHAR(3) NOT NULL,  
    Cno CHAR(5) NOT NULL,  
    Degree DECIMAL(4,1),  
    PRIMARY KEY (Sno, Cno),  
    FOREIGN KEY (Sno) REFERENCES Student(Sno),  
    FOREIGN KEY (Cno) REFERENCES Course(Cno)  
);  






#写入数据


INSERT INTO Student (Sno, Sname, Ssex, Sbirthday, Class) VALUES  
('101', '王一', '男', '1997-01-01', '95031'),  
('102', '李军', '男', '1997-10-01', '95032'),  
('103', '陈帆', '女', '1997-12-12', '95033'),  
('104', '吴地', '男', '1996-11-15', '95034'),  
('105', '张天天', '女', '1997-12-10', '95035'),  
('106', '杨虹', '女', '1996-05-12', '95036'),  
('107', '罗硅', '男', '1998-01-02', '95031'),  
('108', '曾国', '男', '1997-10-12', '95033');



INSERT INTO Teacher (Tno, Tname, Tsex, Tbirthday, Prof, Depart) VALUES  
('A01', '张旭', '男', '1977-12-01', '教授', '计算机系'),  
('A02', '韩趣', '女', '1980-01-01', '副教授', '计算机系'),  
('A03', '杨辉', '男', '1985-01-02', '讲师', '电子工程系'),  
('A04', '辛全', '男', '1986-11-12', '助教', '电子工程系'),  
('A05', '唐艳', '女', '1988-09-03', '助教', '信息管理系');


INSERT INTO Course (Cno, Cname, Tno) VALUES  
('3-105', '计算机导论', 'A01'),  
('3-205', '数据结构', 'A02'),  
('3-245', '程序设计', 'A04'),  
('3-305', '数据库', 'A03');


INSERT INTO Score (Sno, Cno, Degree) VALUES  
('101', '3-105', 80.0),  
('101', '3-205', 88.0),  
('101', '3-245', 90.5),  
('102', '3-105', 85.0),  
('102', '3-245', 68.0),  
('103', '3-105', 75.0),  
('103', '3-245', 58.0),  
('103', '3-305', 80.5),  
('104', '3-105', 87.0),  
('104', '3-205', 95.0),  
('104', '3-245', 89.0),  
('105', '3-245', NULL),  
('106', '3-105', NULL),  
('107', '3-305', NULL),  
('108', '3-205', NULL);






# 查询命令




--14、查询所有学生的Sname、Cno和Degree列。
SELECT Sname, Cno, Degree FROM Student JOIN Score ON Student.Sno = Score.Sno;

 --15、查询所有学生的Sno、Cname和Degree列。
SELECT Student.Sno, Course.Cname, Score.Degree FROM Student JOIN Score ON Student.Sno = Score.Sno JOIN Course ON Score.Cno = Course.Cno;

 --17、查询“95033”班学生的平均分。
SELECT AVG(Degree) AS AvgDegree FROM Score JOIN Student ON Score.Sno = Student.Sno WHERE Student.Class = '95033';

 --18、现查询所有同学的Sno、Cno、Degree和rank列（其中rank为成绩的等级，成绩转换成为等级的规则是：大于等于90分为A、小于90且大于等于80分为B、小于80且大于等于70分为C、小于70且大于等于60为D，小于60分为E）。
WITH RankedScores AS ( SELECT Student.Sno, Score.Cno, Score.Degree, CASE WHEN Score.Degree >= 90 THEN 'A' WHEN Score.Degree >= 80 THEN 'B' WHEN Score.Degree >= 70 THEN 'C' WHEN Score.Degree >= 60 THEN 'D' ELSE 'E' END AS rank FROM Student JOIN Score ON Student.Sno = Score.Sno)SELECT * FROM RankedScores;

 --23、查询“张旭”老师听上课程的学生成绩。
SELECT Student.Sno, Score.Degree FROM Student JOIN Score ON Student.Sno = Score.Sno JOIN Course ON Score.Cno = Course.Cno WHERE Course.Tno = (SELECT Tno FROM Teacher WHERE Tname = '张旭');

 --29、查询选修编号为3-105课程，且成绩至少高于选修编号为3-245课程的同学的Cno、Sno、Degree，并按Degree从高到低次序排序。
SELECT s1.Cno, s1.Sno, s1.Degree FROM Score s1 JOIN Score s2 ON s1.Sno = s2.Sno WHERE s1.Cno = '3-105' AND s2.Cno = '3-245' AND s1.Degree > s2.Degree ORDER BY s1.Degree DESC;

 --41、查询“男”教师及其所上的课程。
SELECT Teacher.Tname, Course.Cname FROM Teacher JOIN Course ON Teacher.Tno = Course.Tno WHERE Teacher.Tsex = '男';

 --43、查询和“李军”同性别的所有同学的Sname。
SELECT Sname FROM Student WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname = '李军');

 --44、查询和“李军”同性别并同班的同学Sname。
SELECT Sname FROM Student WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname = '李军') AND Class = (SELECT Class FROM Student WHERE Sname = '李军');

 --45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT Student.Sno, Student.Sname, Score.Degree FROM Student JOIN Score ON Student.Sno = Score.Sno JOIN Course ON Score.Cno = Course.Cno WHERE Course.Cname = '计算机导论' AND Student.Ssex = '男';
  






# 查询命令（直接使用）


--14、查询所有学生的Sname、Cno和Degree列。
SELECT Sname, Cno, Degree
FROM Student
JOIN Score ON Student.Sno = Score.Sno;

--15、查询所有学生的Sno、Cname和Degree列。
SELECT Student.Sno, Course.Cname, Score.Degree
FROM Student
JOIN Score ON Student.Sno = Score.Sno
JOIN Course ON Score.Cno = Course.Cno;

--17、查询“95033”班学生的平均分。
SELECT AVG(Degree) AS AvgDegree
FROM Score
JOIN Student ON Score.Sno = Student.Sno
WHERE Student.Class = '95033';

--18、现查询所有同学的Sno、Cno、Degree和rank列（其中rank为成绩的等级，成绩转换成为等级的规则是：大于等于90分为A、小于90且大于等于80分为B、小于80且大于等于70分为C、小于70且大于等于60为D，小于60分为E）。
WITH RankedScores AS (
    SELECT Student.Sno, Score.Cno, Score.Degree,
           CASE
               WHEN Score.Degree >= 90 THEN 'A'
               WHEN Score.Degree >= 80 THEN 'B'
               WHEN Score.Degree >= 70 THEN 'C'
               WHEN Score.Degree >= 60 THEN 'D'
               ELSE 'E'
           END AS rank
    FROM Student
    JOIN Score ON Student.Sno = Score.Sno
)
SELECT * FROM RankedScores;

--23、查询“张旭”老师听上课程的学生成绩。
SELECT Student.Sno, Score.Degree
FROM Student
JOIN Score ON Student.Sno = Score.Sno
JOIN Course ON Score.Cno = Course.Cno
WHERE Course.Tno = (SELECT Tno FROM Teacher WHERE Tname = '张旭');

--29、查询选修编号为3-105课程，且成绩至少高于选修编号为3-245课程的同学的Cno、Sno、Degree，并按Degree从高到低次序排序。
SELECT s1.Cno, s1.Sno, s1.Degree
FROM Score s1
JOIN Score s2 ON s1.Sno = s2.Sno 
WHERE s1.Cno = '3-105' 
  AND s2.Cno = '3-245' 
  AND s1.Degree > s2.Degree
ORDER BY s1.Degree DESC;

--41、查询“男”教师及其所上的课程。
SELECT Teacher.Tname, Course.Cname
FROM Teacher
JOIN Course ON Teacher.Tno = Course.Tno
WHERE Teacher.Tsex = '男';

--43、查询和“李军”同性别的所有同学的Sname。
SELECT Sname
FROM Student
WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname = '李军');

--44、查询和“李军”同性别并同班的同学Sname。
SELECT Sname
FROM Student
WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname = '李军')
  AND Class = (SELECT Class FROM Student WHERE Sname = '李军');

--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT Student.Sno, Student.Sname, Score.Degree
FROM Student
JOIN Score ON Student.Sno = Score.Sno
JOIN Course ON Score.Cno = Course.Cno
WHERE Course.Cname = '计算机导论'
  AND Student.Ssex = '男';






# 全部命令


CREATE TABLE Student (  
    Sno CHAR(3) NOT NULL PRIMARY KEY,  
    Sname CHAR(8) NOT NULL,  
    Ssex CHAR(2) NOT NULL,  
    Sbirthday DATETIME,  
    Class CHAR(5)  
);    

CREATE TABLE Teacher (  
    Tno CHAR(3) NOT NULL PRIMARY KEY,  
    Tname CHAR(4) NOT NULL,  
    Tsex CHAR(2) NOT NULL,  
    Tbirthday DATETIME,  
    Prof CHAR(6),  
    Depart VARCHAR(10) NOT NULL  
);
  
CREATE TABLE Course (  
    Cno CHAR(5) NOT NULL PRIMARY KEY,  
    Cname VARCHAR(10) NOT NULL,  
    Tno CHAR(3) NOT NULL,  
    FOREIGN KEY (Tno) REFERENCES Teacher(Tno)  
);  
  
CREATE TABLE Score (  
    Sno CHAR(3) NOT NULL,  
    Cno CHAR(5) NOT NULL,  
    Degree DECIMAL(4,1),  
    PRIMARY KEY (Sno, Cno),  
    FOREIGN KEY (Sno) REFERENCES Student(Sno),  
    FOREIGN KEY (Cno) REFERENCES Course(Cno)  
);  

INSERT INTO Student (Sno, Sname, Ssex, Sbirthday, Class) VALUES  
('101', '王一', '男', '1997-01-01', '95031'),  
('102', '李军', '男', '1997-10-01', '95032'),  
('103', '陈帆', '女', '1997-12-12', '95033'),  
('104', '吴地', '男', '1996-11-15', '95034'),  
('105', '张天天', '女', '1997-12-10', '95035'),  
('106', '杨虹', '女', '1996-05-12', '95036'),  
('107', '罗硅', '男', '1998-01-02', '95031'),  
('108', '曾国', '男', '1997-10-12', '95033');



INSERT INTO Teacher (Tno, Tname, Tsex, Tbirthday, Prof, Depart) VALUES  
('A01', '张旭', '男', '1977-12-01', '教授', '计算机系'),  
('A02', '韩趣', '女', '1980-01-01', '副教授', '计算机系'),  
('A03', '杨辉', '男', '1985-01-02', '讲师', '电子工程系'),  
('A04', '辛全', '男', '1986-11-12', '助教', '电子工程系'),  
('A05', '唐艳', '女', '1988-09-03', '助教', '信息管理系');


INSERT INTO Course (Cno, Cname, Tno) VALUES  
('3-105', '计算机导论', 'A01'),  
('3-205', '数据结构', 'A02'),  
('3-245', '程序设计', 'A04'),  
('3-305', '数据库', 'A03');


INSERT INTO Score (Sno, Cno, Degree) VALUES  
('101', '3-105', 80.0),  
('101', '3-205', 88.0),  
('101', '3-245', 90.5),  
('102', '3-105', 85.0),  
('102', '3-245', 68.0),  
('103', '3-105', 75.0),  
('103', '3-245', 58.0),  
('103', '3-305', 80.5),  
('104', '3-105', 87.0),  
('104', '3-205', 95.0),  
('104', '3-245', 89.0),  
('105', '3-245', NULL),  
('106', '3-105', NULL),  
('107', '3-305', NULL),  
('108', '3-205', NULL);


--14、查询所有学生的Sname、Cno和Degree列。
SELECT Sname, Cno, Degree
FROM Student
JOIN Score ON Student.Sno = Score.Sno;

--15、查询所有学生的Sno、Cname和Degree列。
SELECT Student.Sno, Course.Cname, Score.Degree
FROM Student
JOIN Score ON Student.Sno = Score.Sno
JOIN Course ON Score.Cno = Course.Cno;

--17、查询“95033”班学生的平均分。
SELECT AVG(Degree) AS AvgDegree
FROM Score
JOIN Student ON Score.Sno = Student.Sno
WHERE Student.Class = '95033';

--18、现查询所有同学的Sno、Cno、Degree和rank列（其中rank为成绩的等级，成绩转换成为等级的规则是：大于等于90分为A、小于90且大于等于80分为B、小于80且大于等于70分为C、小于70且大于等于60为D，小于60分为E）。
WITH RankedScores AS (
    SELECT Student.Sno, Score.Cno, Score.Degree,
           CASE
               WHEN Score.Degree >= 90 THEN 'A'
               WHEN Score.Degree >= 80 THEN 'B'
               WHEN Score.Degree >= 70 THEN 'C'
               WHEN Score.Degree >= 60 THEN 'D'
               ELSE 'E'
           END AS rank
    FROM Student
    JOIN Score ON Student.Sno = Score.Sno
)
SELECT * FROM RankedScores;

--23、查询“张旭”老师听上课程的学生成绩。
SELECT Student.Sno, Score.Degree
FROM Student
JOIN Score ON Student.Sno = Score.Sno
JOIN Course ON Score.Cno = Course.Cno
WHERE Course.Tno = (SELECT Tno FROM Teacher WHERE Tname = '张旭');

--29、查询选修编号为3-105课程，且成绩至少高于选修编号为3-245课程的同学的Cno、Sno、Degree，并按Degree从高到低次序排序。
SELECT s1.Cno, s1.Sno, s1.Degree
FROM Score s1
JOIN Score s2 ON s1.Sno = s2.Sno 
WHERE s1.Cno = '3-105' 
  AND s2.Cno = '3-245' 
  AND s1.Degree > s2.Degree
ORDER BY s1.Degree DESC;

--41、查询“男”教师及其所上的课程。
SELECT Teacher.Tname, Course.Cname
FROM Teacher
JOIN Course ON Teacher.Tno = Course.Tno
WHERE Teacher.Tsex = '男';

--43、查询和“李军”同性别的所有同学的Sname。
SELECT Sname
FROM Student
WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname = '李军');

--44、查询和“李军”同性别并同班的同学Sname。
SELECT Sname
FROM Student
WHERE Ssex = (SELECT Ssex FROM Student WHERE Sname = '李军')
  AND Class = (SELECT Class FROM Student WHERE Sname = '李军');

--45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT Student.Sno, Student.Sname, Score.Degree
FROM Student
JOIN Score ON Student.Sno = Score.Sno
JOIN Course ON Score.Cno = Course.Cno
WHERE Course.Cname = '计算机导论'
  AND Student.Ssex = '男';







# 记得备份.SQL








标签：Degree,Sno,Server,Score,Student,SQL,Cno,查询,SELECT	

From： https://blog.csdn.net/jbywapp/article/details/143792990
      

      




      

      

        
相关文章

        

          
    
 
  • Unable to load io.netty.resolver.dns.macos.MacOSDnsServerAddressStreamProvider, 
     macm1启动项目，报错，“Unabletoloadio.netty.resolver.dns.macos.MacOSDnsServerAddressStreamProvider,fallbacktosystemdefaults.ThismayresultinincorrectDNSresolutionsonMacOS.”，出现这个问题是因为使用了spring-cloud-starter-gateway依赖，这需要额外安装......
     
  • 【GreatSQL优化器-02】索引和Sargable谓词
    【GreatSQL优化器-02】索引和Sargable谓词一、Sargable谓词介绍GreatSQL的优化器在有过滤条件的时候，需要先把条件按照是否有索引来进行区分，可以用索引来加速查询的条件称为Sargable，其中arge来源于SearchArgument（搜索参数）的首字母拼成的"SARG"。GreatSQL用keyuse_array索引数......
     
  • 听说EI要换届了？最好用的SCI/SSCI期刊查询利器：影响因子和分区情况一站式查询！
    EI收录期刊重大变革，业内人士预测——EI目录要换届了[人心惶惶][人心惶惶]！那么，如何关注EI换届情况呢？再往外延伸，如何查询关注SCI/SSCI的变更呢？EI目录下载与查询EI官网期刊目录下载链接：https://www.elsevier.com/products/engineering-village/databases/compendex点击图片......
     
  • mysql基础函数
    函数是一段可以直接调用的程序字符串函数|CONCAT(S1,S2,S3）|字符串拼接将s1，s2s3拼接成一个字符串||LOWER(str)|将字符串str全部转为小写||UPPER(str)|将字符串str全部转为大写||LPAD(str,n,pad)|左填充，用字符串Pad对str的左边......
     
  • 分布式管理进阶：HarmonyOS Next 中的设备信息查询与状态监听
    本文旨在深入探讨华为鸿蒙HarmonyOSNext系统（截止目前API12）的技术细节，基于实际开发实践进行总结。主要作为技术分享与交流载体，难免错漏，欢迎各位同仁提出宝贵意见和问题，以便共同进步。本文为原创内容，任何形式的转载必须注明出处及原作者。在探索HarmonyOSNext的分布式管理服务时......
     
  • JDBC中数据库的连接与查询
    让我们仔细看看是怎么访问数据库的packagesql;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.SQLException;publicclassConn{//创建类ConnConnectioncon;//声明Connection对象publicstaticStringuser;publicstat......
     
  • xshell上实现：MongoDB文档查询
    实验内容概述    本实验旨在了解MongoDB文档数据库的基本操作，包括创建集合、插入文档数据和执行文档查询操作。实验环境：Linux（Centos7）xshell7Mongodb实验目的理解MongoDB文档数据库的基本概念和特性。掌握在MongoDB中创建集合和插入文档数据的方法。学习使用Mongo......
     
  • 【原创】java+ssm+mysql物流信息网系统设计与实现
    个人主页：程序猿小小杨个人简介：从事开发多年，Java、Php、Python、前端开发均有涉猎博客内容：Java项目实战、项目演示、技术分享文末有作者名片，希望和大家一起共同进步，你只管努力，剩下的交给天意。前言：随着全球经济一体化的不断推进，物流业作为支撑企业运营的重要环节，其管理和......
     
  • ABAP开发学习——ST05 ABAP SQL跟踪工具
    操作步骤第一步使用ST05之前，将要查的程序停留想要看的操作的前一步，这里想看到取数操作，所以停留在选择界面第二步进入ST05选择SQLTrace然后激活第三步去执行程序第四步ST05取消激活第五步查看操作选完时间直接执行......
     
  • 【原创】java+ssm+mysql商品库存管理系统（进销存）设计与实现
    个人主页：程序猿小小杨个人简介：从事开发多年，Java、Php、Python、前端开发均有涉猎博客内容：Java项目实战、项目演示、技术分享文末有作者名片，希望和大家一起共同进步，你只管努力，剩下的交给天意。前言：随着市场竞争的日益激烈，企业面临着巨大的竞争压力。为了在市场中立于不败......
     
    •            
          

        

      

    

    

      
赞助商

      




      

      
阅读排行

      

        
      

    

  





  
 
    
网站主页关于我们联系我们网站地图 

    
本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。 

    
Copyright © 2020-2023  IPS99  版权所有 IPS99