SQL Server部分应用

1、SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息FirstName, LastName, City, State

表1: Person

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+

| PersonId | int |

| FirstName | varchar |

| LastName | varchar |

+-------------+---------+

PersonId 是上表主键

表2: Address

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+

| AddressId | int |

| PersonId | int |

| City | varchar |

| State | varchar |

+-------------+---------+

AddressId 是上表主键

使用左连接left join on完成查询语句：

select p.FirstName,p.LastName,a.City,a.State

from

person p left join address a

p.personid=a.personid

2、编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。

+----+--------+

| Id | Salary |

+----+--------+

| 1 | 100 |

| 2 | 200 |

| 3 | 300 |

+----+--------+

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+---------------------+

| SecondHighestSalary |

+---------------------+

| 200 |

+---------------------+

语句：

select max(Salary) SecondHighestSalary from Employee where Salary<(select max(Salary) from Employee)

要点：

（1）去重，所以使用group by进行分组

（2）由于要得到第二大工资，所以需要进行排序，

（3）max得到的最大值，但是要得到第二个最大值，所以要使用limit 1,1

limit start,len

start 表示页数，0,1,2,3....

len表示页的记录数

（4）由于答案的列名是SecondHighestSalary，所以最后要使用as 重命名

（5）需要使用两个select，第二个select用于重建列和列值。因为second highest salary 如果不存在的话，那么内部的select返回值是null，不符合答案。因此需要对内部select进行重建列和列值。

3、Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。

+----+-------+--------+-----------+

+----+-------+--------+-----------+

| 1 | Joe | 70000 | 3 |

| 2 | Henry | 80000 | 4 |

| 3 | Sam | 60000 | NULL |

| 4 | Max | 90000 | NULL |

+----+-------+--------+-----------+

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。

语句：

select a1.name as Employee from Employee a1,Employee a2 where a1.ManagerId=a2.Id and a1.Salary>a2.Salary

4、编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。

示例：

+----+---------+

| Id | Email |

+----+---------+

| 1 | a@b.com |

| 2 | c@d.com |

| 3 | a@b.com |

+----+---------+

根据以上输入，你的查询应返回以下结果：

+---------+

| Email |

+---------+

| a@b.com |

+---------+

语句：

select Email from Person group by Email having count(Email)>1

5、某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

Customers 表：

+----+-------+

| Id | Name |

+----+-------+

| 1 | Joe |

| 2 | Henry |

| 3 | Sam |

| 4 | Max |

+----+-------+

Orders 表：

+----+------------+

| Id | CustomerId |

+----+------------+

| 1 | 3 |

| 2 | 1 |

+----+------------+

语句：

select c.name as Customers from Customers c left join Orders o on o.CustomerId=c.Id where o.id is null

6、编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小的那个。

+----+------------------+

| Id | Email |

+----+------------------+

| 1 | john@example.com |

| 2 | bob@example.com |

| 3 | john@example.com |

+----+------------------+

Id 是这个表的主键。

例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:

+----+------------------+

| Id | Email |

+----+------------------+

| 1 | john@example.com |

| 2 | bob@example.com |

+----+------------------+

语句：

delete p1 from Person p1,Person p2 where p1.email=p2.email and p1.id>p2.id

7、编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 id 。

返回结果不要求顺序。

查询结果格式如下例：

Weather

+----+------------+-------------+

| id | recordDate | Temperature |

+----+------------+-------------+

| 1 | 2015-01-01 | 10 |

| 2 | 2015-01-02 | 25 |

| 3 | 2015-01-03 | 20 |

| 4 | 2015-01-04 | 30 |

+----+------------+-------------+

Result table:

+----+

| id |

+----+

| 2 |

| 4 |

+----+

2015-01-02 的温度比前一天高（10 -> 25）

2015-01-04 的温度比前一天高（20 -> 30）

语句：

select w1.id from Weather w1,Weather w2 where w1.Temperature<w2.Temperature and datediff(w1.recordDate,w2.recordDate)=1

8、这里有张 World 表

+-----------------+------------+------------+--------------+---------------+

+-----------------+------------+------------+--------------+---------------+

| Afghanistan | Asia | 652230 | 25500100 | 20343000 |

| Albania | Europe | 28748 | 2831741 | 12960000 |

| Algeria | Africa | 2381741 | 37100000 | 188681000 |

| Andorra | Europe | 468 | 78115 | 3712000 |

| Angola | Africa | 1246700 | 20609294 | 100990000 |

+-----------------+------------+------------+--------------+---------------+

如果一个国家的面积超过 300 万平方公里，或者人口超过 2500 万，那么这个国家就是大国家。

编写一个 SQL 查询，输出表中所有大国家的名称、人口和面积。

例如，根据上表，我们应该输出:

+--------------+-------------+--------------+

| name | population | area |

+--------------+-------------+--------------+

| Afghanistan | 25500100 | 652230 |

| Algeria | 37100000 | 2381741 |

+--------------+-------------+--------------+

语句：

select name,population,area from World where population>25000000 or area>3000000

9、有一个courses 表，有: student (学生) 和 class (课程)。

请列出所有超过或等于5名学生的课。

例如，表：

+---------+------------+

| student | class |

+---------+------------+

| A | Math |

| B | English |

| C | Math |

| D | Biology |

| E | Math |

| F | Computer |

| G | Math |

| H | Math |

| I | Math |

+---------+------------+

应该输出:

+---------+

| class |

+---------+

| Math |

+---------+

语句：

select class from courses group by class having count(class)>=5

10、作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数的影片，结果请按等级 rating 排列。

例如，下表 cinema:

+---------+-----------+--------------+-----------+

+---------+-----------+--------------+-----------+

| 1 | War | great 3D | 8.9 |

| 2 | Science | fiction | 8.5 |

| 3 | irish | boring | 6.2 |

| 4 | Ice song | Fantacy | 8.6 |

| 5 | House card| Interesting| 9.1 |

+---------+-----------+--------------+-----------+

对于上面的例子，则正确的输出是为：

+---------+-----------+--------------+-----------+

+---------+-----------+--------------+-----------+

| 5 | House card| Interesting| 9.1 |

| 1 | War | great 3D | 8.9 |

+---------+-----------+--------------+-----------+

语句：

select c.id,c.movie,c.description,c.rating from cinema c where c.description != 'boring' and c.id%2=1 order by rating desc

备注：where c.id%2=1显示的数据id为奇数，where c.id%2=0显示的数据id为偶数

11、给定一个 salary 表，如下所示，有 m = 男性和 f = 女性的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。

注意：您必只能写一个 Update 语句，请不要编写任何 Select 语句。

例如：

| id | name | sex | salary |

|----|------|-----|--------|

| 1 | A | m | 2500 |

| 2 | B | f | 1500 |

| 3 | C | m | 5500 |

| 4 | D | f | 500 |

运行你所编写的更新语句之后，将会得到以下表:

| id | name | sex | salary |

|----|------|-----|--------|

| 1 | A | f | 2500 |

| 2 | B | m | 1500 |

| 3 | C | f | 5500 |

| 4 | D | m | 500 |

语句：

update salary set sex = (case when sex = 'm' then 'f' else 'm' end)

重点：

case when 条件 then 符合条件变更结果 else 不符合条件变更结果 end结束

case后的数据和when后的数据进行比对，符合就使用then后的数据，不符合就使用else后的数据

场景1：有分数score，score<60返回不及格，score>=60返回及格，score>=80返回优秀

SELECT

STUDENT_NAME,

(CASE WHEN score < 60 THEN '不及格'

WHEN score >= 60 AND score < 80 THEN '及格'

WHEN score >= 80 THEN '优秀'

ELSE '异常' END) AS REMARK

FROM

TABLE

注意：如果你想判断score是否null的情况，WHEN score = null THEN '缺席考试'，这是一种错误的写法，正确的写法应为：

CASE WHEN score IS NULL THEN '缺席考试' ELSE '正常' END

posted @ 2021-08-10 16:41 码·蚁阅读(47) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

码·蚁

SQL Server部分应用

公告