备案控制台
探索云世界
开发者社区 数据库 文章 正文

C# .NET面试系列十一：数据库SQL查询（附建表语句）

2024-03-12 67
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： #### 第1题用一条 SQL 语句 查询出每门课都大于80 分的学生姓名建表语句：```sqlcreate table tableA( name varchar(10), kecheng varchar(10), fenshu int(11)) DEFAULT CHARSET = 'utf8';```插入数据```sqlinsert into tableA values ('张三', '语文', 81);insert into tableA values ('张三', '数学', 75);insert into tableA values ('李四',

第1题

用一条 SQL 语句 查询出每门课都大于80 分的学生姓名

建表语句：

create table tableA
(
    name varchar(10),
    kecheng varchar(10),
    fenshu int(11)
) DEFAULT CHARSET = 'utf8';

插入数据

insert into tableA values ('张三', '语文', 81);
insert into tableA values ('张三', '数学', 75);
insert into tableA values ('李四', '语文', 76);
insert into tableA values ('李四', '数学', 90);
insert into tableA values ('王五', '语文', 81);
insert into tableA values ('王五', '数学', 100);
insert into tableA values ('王五', '英语', 90);

答案有2种：

-- 法1：使用了GROUP BY和HAVING子句，首先按姓名分组，然后使用HAVING子句过滤出分数最低的分数大于80分的学生姓名。这样也能保证查询出每门课都大于80分的学生姓名。
SELECT name
FROM tableA
GROUP BY name
HAVING MIN(fenshu) > 80;

-- 法2：使用了子查询，首先在子查询中找出所有分数不低于80分的学生姓名，然后在外层查询中找出不在这个子查询结果中的学生姓名。这样就能保证查询出每门课都大于80分的学生姓名。
SELECT DISTINCT NAME 
FROM
    tableA 
WHERE
    NAME NOT IN (
    SELECT DISTINCT NAME 
    FROM
        tableA 
WHERE
    fenshu <= 80)

第2题

一道SQL语句面试题，关于group by表内容：

日期 结果
2005-05-09 胜
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负

如果要生成下列结果, 该如何写sql语句?

建表语句：

create table tableb (
    rq varchar(10),
    shengfu nchar(1)
) DEFAULT CHARSET = 'utf8';

插入数据：

insert into tableb values('2005-05-09', '胜');
insert into tableb values('2005-05-09', '胜');
insert into tableb values('2005-05-09', '负');
insert into tableb values('2005-05-09', '负');
insert into tableb values('2005-05-10', '胜');
insert into tableb values('2005-05-10', '负');
insert into tableb values('2005-05-10', '负')

答案：

SELECT
    rq,
    sum( CASE WHEN shengfu = '胜' THEN 1 ELSE 0 END ) '胜',
    sum( CASE WHEN shengfu = '负' THEN 1 ELSE 0 END ) '负' 
FROM
    tableb 
GROUP BY
    rq

-- 使用 CASE WHEN 来对每个胜负情况进行条件判断，然后将符合条件的结果进行计数。
-- 使用 SUM 对计数结果进行求和。
-- 使用 CONCAT 将胜负的计数结果连接成字符串，形成最终的结果。

第3题

一个叫 team 的表，里面只有一个字段name, 一共有4 条纪录，分别是a,b,c,d, 对应四个球队，现在四个球对进行比赛，用一条sql 语句显示所有可能的比赛组合

team表建表语句:

CREATE TABLE team (
    name varchar(20)
) DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO team VALUES ('a');  
INSERT INTO team VALUES ('b');  
INSERT INTO team VALUES ('c');  
INSERT INTO team VALUES ('d')

答案：

SELECT t1.name AS team1, t2.name AS team2
FROM team t1
JOIN team t2 ON t1.name < t2.name;

-- 这条 SQL 语句使用自联接将 team 表与自身连接，通过比较球队的名称确保每个组合只显示一次。t1.name < t2.name 的条件保证了不会重复显示同一场比赛。

结果如图所示：

第4题

怎么把这样一个表儿

查成这样一个结果

建表语句：

CREATE TABLE table1 (
    `year` int (11),
    `month` int (11),
    `amount` DECIMAL
) DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO table1 VALUES (1991, 1, 1.1);  
INSERT INTO table1 VALUES (1991, 2, 1.2); 
INSERT INTO table1 VALUES (1991, 3, 1.3); 
INSERT INTO table1 VALUES (1991, 4, 1.4); 
INSERT INTO table1 VALUES (1992, 1, 2.1);  
INSERT INTO table1 VALUES (1992, 2, 2.2); 
INSERT INTO table1 VALUES (1992, 3, 2.3); 
INSERT INTO table1 VALUES (1992, 4, 2.4);

答案：

SELECT
  year,
  MAX(CASE WHEN month = 1 THEN amount END) AS m1,
  MAX(CASE WHEN month = 2 THEN amount END) AS m2,
  MAX(CASE WHEN month = 3 THEN amount END) AS m3,
  MAX(CASE WHEN month = 4 THEN amount END) AS m4
FROM table1
GROUP BY year;
-- 这条 SQL 语句使用了条件聚合函数 MAX(CASE WHEN ...) 来将每个月份的数据转置成表的列。每个 MAX(CASE WHEN ...) 子句对应一个月份，当月份匹配时，将 amount 的值取出。

第5题

一张部门表，一张员工表

部门表建表语句：

create table dpt
(
    id int(11),
    department varchar(12) 
)DEFAULT CHARSET=utf8;

插入数据：

insert into dpt values(1, '设计');
insert into dpt values(2, '市场');
insert into dpt values(3, '售后')

员工表建表语句：

create table emy
(
    id int(11),
    dptID int(11),
    `name` varchar(12)
) DEFAULT CHARSET=utf8;

插入数据

insert into emy values(1, 1, '张三');
insert into emy values(2, 1, '李四');
insert into emy values(3, 2, '王五');
insert into emy values(4, 3, '彭六');
insert into emy values(5, 4, '陈七')

第6题

删除除了自动编号不同, 其他都相同的学生冗余信息

学生表建表语句：

create table tablestudent
(
    id int(11),
    studentno varchar(10),
    studentname varchar(10),
    classno varchar(10),
    classname varchar (11),
    score int (11)
) DEFAULT CHARSET = 'utf8';

插入数据:

insert into tablestudent values (1, '2005001', '张三', '0001', '数学',69);
insert into tablestudent values (2, '2005002', '李四', '0001', '数学',69);
insert into tablestudent values (3, '2005001', '张三', '0001', '数学',69)

如图所示：

答案：

DELETE ts1
FROM tablestudent ts1
JOIN tablestudent ts2 ON ts1.studentno = ts2.studentno
                      AND ts1.studentname = ts2.studentname
                      AND ts1.classno = ts2.classno
                      AND ts1.classname = ts2.classname
                      AND ts1.score = ts2.score
                      AND ts1.id < ts2.id;
-- 这条 SQL 语句使用了自联接（self-join），将表自身与自身连接，通过比较各个字段来找到除了自动编号不同以外其他字段都相同的冗余信息。ts1.id < ts2.id 保证了只删除自动编号较小的冗余信息。

第7题

关于年度、季度SQL统计查询的面试题

A表 - 销售数据表，其中adddate是销售时间，company是客户公司，money是销售金额，sales是销售员

B表 - 产品销售表，aid对应A表中的id，pid对应是C表中产品表。

C表 - 产品类型表

1、销售数据表建表语句：

CREATE TABLE `a` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `adddate` datetime NOT NULL,
  `company` varchar(10) DEFAULT NULL,
  `money` decimal(7,2) DEFAULT NULL,
  `sales` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

插入数据：

insert  into `a`(`id`,`adddate`,`company`,`money`,`sales`) 
values 
(1, '2011-01-02 00:00:00', 'A', '10000.00', '张三'),
(2, '2011-07-03 00:00:00', 'B', '50000.00', '王五'),
(3, '2011-09-03 00:00:00', 'A', '20000.00', '赵六'),
(4, '2012-07-03 00:00:00', 'A', '10000.00', '李四'),
(5, '2013-10-03 00:00:00', 'B', '5000.00', '麻子'),
(6, '2013-07-04 00:00:00', 'B', '6000.00', '小柒');

2、产品销售表建表语句:

DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product` varchar(20) DEFAULT NULL,
  `pid` int(11) DEFAULT NULL,
  `price` decimal(7,2) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

插入数据：

insert  into `b`(`id`,`product`,`pid`,`price`,`number`) 
values 
(1, 'AC1100', 1, '10000.00', 2),
(2, 'AC1200', 1, '20000.00', 3),
(3, 'SSL1100', 2, '10000.00', 1),
(4, 'WOC1100', 3, '10000.00', 1);

3、产品类型表建表语句:

CREATE TABLE `c` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

插入数据：

insert  into `c`(`id`,`type`) 
values
(1, 'AC'),
(2, 'SSL'),
(3, 'WOC');

问题1：请使用SQL语句统计2011年至2013年三年中每个季度的销售金额?

SELECT
  YEAR(adddate) AS year,
  QUARTER(adddate) AS quarter,
  SUM(money) AS total_sales
FROM
  a
WHERE
  YEAR(adddate) BETWEEN 2011 AND 2013
GROUP BY
  YEAR(adddate),
  QUARTER(adddate)
ORDER BY
  YEAR(adddate),
  QUARTER(adddate);

问题2：请使用SQL语句按如下格式统计2013年销售金额前5位的销售员

SELECT
  sales,
  SUM(money) AS total_sales
FROM
  a
WHERE
  YEAR(adddate) = 2013
GROUP BY
  sales
ORDER BY
  total_sales DESC
LIMIT 5;

问题3：请用SQL语句统计出哪个产品线的销售金额最多?

SELECT c.type, SUM(a.money) AS total_sales
FROM a
JOIN b ON a.id = b.pid
JOIN c ON b.pid = c.id
GROUP BY c.type
ORDER BY total_sales DESC
LIMIT 1;

第8题

数据库的表设计如下：

部门表：部门编号，部门名称，地址；

员工表：员工编号，员工名字，职务，管理编号，入职日期，薪资，奖金，部门编号；

部门表建表语句：

CREATE TABLE dept(
    deptno INT PRIMARY KEY,
    dname VARCHAR(20) NOT NULL,
    loc VARCHAR(20) NOT NULL
) DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEWTORK');  
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');  
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');  
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');

如图所示：

员工表建表语句：

CREATE TABLE emp(
empno INT PRIMARY KEY,
ename VARCHAR(20) NOT NULL,    
job VARCHAR(20) CHECK (job IN('CLERK','SALESMAN','MANAGER','ANALYST')),
mgp INT,  
hiredate DATE ,
sal DECIMAL(10,2),
comm DECIMAL(10,2),  
deptno INT,  
CONSTRAINT pk_we FOREIGN KEY (deptno) REFERENCES dept (deptno)  
)DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO emp VALUES
(7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 1640, Null, 20),
(7499, 'ALLEN', 'SALESMAN',7698, '1981-02-20', 11400, 300, 30),
(7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 5200, 500, 30),
(7566, 'JOENS', 'MANAGER', 7839, '1981-04-02', 7015, Null, 20),
(7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 5200, 1400, 30),
(7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 5900, Null, 30),
(7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2470, Null, 10),
(7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3040, Null, 20)

如图所示：

问题如下：

1、列出至少有一个雇员的所有部门

SELECT DISTINCT d.deptno, d.dname, d.loc
FROM dept d
JOIN emp e ON d.deptno = e.deptno;

2、列出薪金比"SMITH"多的所有雇员

SELECT *
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

3、列出入职日期早于其直接上级的所有雇员

SELECT e1.*
FROM emp e1
INNER JOIN emp e2 ON e1.mgp = e2.empno
WHERE e1.hiredate < e2.hiredate;

4、找员工姓名和直接上级的名字

SELECT e1.ename AS emp_name, e2.ename AS manager_name
FROM emp e1
LEFT JOIN emp e2 ON e1.mgp = e2.empno;

5、显示部门名称和人数

SELECT d.dname AS department_name, COUNT(e.empno) AS num_of_employees
FROM dept d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.dname;

6、显示每个部门的最高工资的员工

SELECT e1.*
FROM emp e1
INNER JOIN (
    SELECT deptno, MAX(sal) AS max_sal
    FROM emp
    GROUP BY deptno
) e2 ON e1.deptno = e2.deptno AND e1.sal = e2.max_sal;

7、显示每个部门的工资前2名的员工

SELECT *
FROM (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS row_num
    FROM emp
) ranked
WHERE row_num <= 2;


-- 这段代码使用了 SQL 中的窗口函数 ROW_NUMBER()，这个函数主要用于给查询结果的行加上一个序号，这个序号根据指定的排序规则来确定。让我来解释一下这句代码的具体含义：
-- ROW_NUMBER(): 这是窗口函数的调用，它会为每一行结果分配一个唯一的行号。
-- OVER (PARTITION BY deptno ORDER BY sal DESC): 这部分是窗口函数的 OVER 子句，指定了窗口函数的分区和排序方式。具体来说：
-- PARTITION BY deptno: 意味着窗口函数会根据 deptno 列的值进行分区，也就是说，每个不同的 deptno 值会形成一个分区。
-- ORDER BY sal DESC: 指定了在每个分区内按照 sal 列的值降序排序，也就是工资高的排在前面。
-- AS row_num: 这部分是将窗口函数的结果命名为 row_num，作为一个新的列名。
-- 因此，整个语句的作用是为每个部门内的员工按照工资从高到低进行排序，并为每个排名结果分配一个行号。

SELECT e1.*
FROM emp e1
WHERE (
    SELECT COUNT(DISTINCT e2.sal)
    FROM emp e2
    WHERE e2.deptno = e1.deptno AND e2.sal >= e1.sal
) <= 2;
本系列文章题目摘自网络，答案重新梳理
文章标签：
SQL
数据库
C#
关键词：
SQL数据库
数据库sql
SQL查询
数据库查询
面试数据库
GoodTime
目录
相关文章
游客ks4qonorzqbea
|
4天前
|
SQL Oracle 关系型数据库
sql语句创建数据库
在创建数据库之前，请确保你有足够的权限，并且已经考虑了数据库的安全性和性能需求。此外，不同的DBMS可能有特定的最佳实践和配置要求，因此建议查阅相关DBMS的官方文档以获取更详细和准确的信息。
游客ks4qonorzqbea
23 4
SarPro
|
16天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库：第二章 关系数据库
【SQL server】玩转SQL server数据库：第二章 关系数据库
SarPro
52 10
追逐时光者
|
18天前
|
数据可视化 网络协议 C#
C#/.NET/.NET Core优秀项目和框架2024年3月简报
公众号每月定期推广和分享的C#/.NET/.NET Core优秀项目和框架（每周至少会推荐两个优秀的项目和框架当然节假日除外），公众号推文中有项目和框架的介绍、功能特点、使用方式以及部分功能截图等（打不开或者打开GitHub很慢的同学可以优先查看公众号推文，文末一定会附带项目和框架源码地址）。注意：排名不分先后，都是十分优秀的开源项目和框架，每周定期更新分享（欢迎关注公众号：追逐时光者，第一时间获取每周精选分享资讯🔔）。
追逐时光者
49 1
泡沫o0
|
1月前
|
SQL 存储 BI
【软件设计师备考 专题 】数据库语言（SQL）
【软件设计师备考 专题 】数据库语言（SQL）
泡沫o0
91 0
穆雄雄.
|
1月前
|
SQL 数据库
sql server中创建数据库和表的语法
sql server中创建数据库和表的语法
穆雄雄.
18 1
SarPro
|
16天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
SarPro
88 6
L&ecirc;v&iuml;
|
3天前
|
SQL Java 数据库连接
Java从入门到精通：2.3.2数据库编程——了解SQL语言，编写基本查询语句
Java从入门到精通：2.3.2数据库编程——了解SQL语言，编写基本查询语句
L&ecirc;v&iuml;
12 0
游客rtyzfbpgovwfg
|
4天前
|
SQL 缓存 数据库
sql 数据库优化
SQL数据库优化是一个复杂且关键的过程，涉及多个层面的技术和策略。以下是一些主要的优化建议： 查询语句优化： 避免全表扫描：在查询时，尽量使用索引来减少全表扫描，提高查询速度。 使用合适的子查询方式：子查询可能降低查询效率，但可以通过优化子查询的结构或使用连接（JOIN）替代子查询来提高性能。 简化查询语句：避免不必要的复杂查询，尽量使SQL语句简单明了。 使用EXISTS替代IN：在查询数据是否存在时，使用EXISTS通常比IN更快。 索引优化： 建立合适的索引：对于经常查询的列，如主键和外键，应创建相应的索引。同时，考虑使用覆盖索引来进一步提高性能。 避免过多的索引：虽然索引可以提高查询
游客rtyzfbpgovwfg
40 2
游客rtyzfbpgovwfg
|
4天前
|
SQL XML 数据库
sql导入数据库命令
在SQL Server中，数据库导入可通过多种方式实现：1) 使用SSMS的“导入数据”向导从各种源（如Excel、CSV）导入；2) BULK INSERT语句适用于导入文本文件；3) bcp命令行工具进行批量数据交换；4) OPENROWSET函数直接从外部数据源（如Excel）插入数据。在操作前，请记得备份数据库，并可能需对数据进行预处理以符合SQL Server要求。注意不同方法可能依版本和配置而异。
游客rtyzfbpgovwfg
12 2
长梦
|
7天前
|
NoSQL MongoDB Redis
Python与NoSQL数据库（MongoDB、Redis等）面试问答
【4月更文挑战第16天】本文探讨了Python与NoSQL数据库（如MongoDB、Redis）在面试中的常见问题，包括连接与操作数据库、错误处理、高级特性和缓存策略。重点介绍了使用`pymongo`和`redis`库进行CRUD操作、异常捕获以及数据一致性管理。通过理解这些问题、易错点及避免策略，并结合代码示例，开发者能在面试中展现其技术实力和实践经验。
长梦
129 8
Python与NoSQL数据库（MongoDB、Redis等）面试问答

热门文章

最新文章

  • 1
    一文搞懂SQL优化——如何高效添加数据
  • 2
    SQL常见面试题总结2
  • 3
    NL2SQL实践系列(2)：2024最新模型实战效果(Chat2DB-GLM、书生·浦语2、InternLM2-SQL等)以及工业级案例教学
  • 4
    NL2SQL进阶系列(5)：论文解读业界前沿方案（DIN-SQL、C3-SQL、DAIL-SQL）、新一代数据集BIRD-SQL解读
  • 5
    【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
  • 6
    大模型与数据分析：探索Text-to-SQL（上）
  • 7
    SQL，Group By 真扎心，原来是这样
  • 8
    大模型与数据分析：探索Text-to-SQL（中）
  • 9
    大模型与数据分析：探索Text-to-SQL（下）
  • 10
    NL2SQL进阶系列(4)：ConvAI、DIN-SQL等16个业界开源应用实践详解[Text2SQL]
  • 1
    vue面试题目汇总
    37
  • 2
    首次面试经历（忘指导）当我在简历上写了苍穹外卖，瑞吉外卖时……
    219
  • 3
    【springboot原理篇】Bean的加载方式，面试必看
    52
  • 4
    【Kafka面试演练】那Kafka消费者手动提交、自动提交有什么区别？
    64
  • 5
    【数组相关面试题】LeetCode试题
    17
  • 6
    网安面试指南——（渗透，攻击，防御）
    95
  • 7
    全球AI人才报告：硅谷40万人大裁员，码农地狱级面试12场！
    30
  • 8
    Java开发面试题 | 2023
    53
  • 9
    【怒怼大厂面试官】你先说说知道哪些MySQL的高级特性
    45
  • 10
    LeetCode刷题--- 面试题 01.07. 旋转矩阵（原地旋转+翻转替旋转）
    33

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • Excel文件转存到RDS数据库
  • 快速体验PolarDB开源数据库
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）