SQL练习

175.组合两个表

• 查询所有A表中成员的名称、城市和国家信息

  select firstName, lastName, city, state
  from Person left join Address`
  on Person.personId=Address.personId;

• 注意：
  • inner join：两个表中值都存在的情况
  • outer join：附表中值可能存在null情况
  • A inner join B：取交集
  • A left join B：取A的全部，B中没有对应的值，则为null
  • A right join B：取B的全部，A中没有对应的值，则为null
  • A full join B：取并集，彼此没有对应的值时为null

176.第二高的薪水

• 获取所有员工薪水中第二高的薪水（注意去重）

  select (select distinct(salary)
  from Employee
  order by salary desc
  limit 1, 1)
  as SecondHighestSalary;

• 注意：
  • 为了获取第二高的薪水，可以采用limit分页查询的方式，将数据分为每页一条数据，然后直接取第二页
  • 有可能出现没有第二高的数据的情况，需要在外边再套一层select查询解决null问题
  • 因为需要对薪水去重，在select的值外边套了一层distinct(salary)

177.第N高的薪水

• 和上一题类似，只不过是查询第N高的薪水，会涉及到sql函数的传值问题（目前还有问题没有解决）

178.分数排名

• 对于所有的分数按照从大到小进行排名，相同分数名词相同，不会出现跳过名次的现象

  select score, (select count(distinct score) from Scores where score >= s.score) as 'Rank'
  from Scores
  order by score desc

• 注意：
  • 去重是需要的distinct
  • 计算出结果进行展示Rank
  • 通过查询所有比当前分数大的个数来判断当前分数的排名score >= s.score

180.连续出现的数字

• 统计所有num中连续出现三次及以上的数字

  select distinct Num as ConsecutiveNums
  from Logs
  where (id+1, Num) in (select * from Logs)
  and (id+2, Num) in (select * from Logs);

或者使用这种三表联查的方式

  select distinct a.Num as ConsecutiveNums
  from Logs a, Logs b, Logs c
  where a.Num=b.Num and b.Num=c.Num and a.id+1=b.id and b.id+1=c.id;

• 注意：
  • 这道题需要查询出现三个连续相同的值
  • 采用第一种方式是比较友好的，判断数据在表中

181.超过经理收入的员工

• 计算员工比对应经理收入高的员工名称

  select a.name as "Employee"
  from Employee a, Employee b
  where a.managerId=b.id and a.salary>b.salary;

182.查找重复的电子邮箱

• 查询出现多次的电子邮箱

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

• 注意：
  • 正确使用group by分组并使用having进行过滤条件

183.从不订购的客户

• 查询没有订购过商品的用户

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

81.顾客登录名

• 查询顾客ID、顾客名称、登录名，其中登录名全部大写，有顾客名称前两个字符和其所在城市前三个字符组成

  select cust_id, cust_name,
  upper(concat(substring(cust_name, 1, 2), substring(cust_city, 1, 3))) as user_login
  from Customers;

• 注意：
  • 字母全大写函数：upper()
  • 字符串拼接函数：concat()
  • 字符串截取函数：substring()

82.返回2020年1月的所有订单信息

• 返回2020年1月的所有订单信息，按照订单日期升序排序

  select order_num, order_date
  from Orders
  where month(order_date)='01' and year(order_date)='2020'
  order by order_date;
  或者
  select order_num, order_date
  from Orders
  where substring(order_date, 1, 7)='2020-01'
  order by order_date;

• 注意：
  • 日期格式：datetime
  • 获取年月日信息：year、month、day
  • 也可以使用字符串截取的方式获取前七位的子串进行判断

95.检索所有产品名称以及对应的销售总数

• 从Products表中检索所有的产品名称，以及所售产品的总数

  select prod_name, (
    select sum(quantity)
    from OrderItems
    where OrderItems.prod_id=Products.prod_id) as quant_sold
  from Products;
  或者
  select prod_name, sum(quantity) as quant_sold
  from Products p, OrderItems oi
  where p.prod_id=oi.prod_id
  group by p.prod_name;

• 注意：
  • 子查询的位置，可以放在select的语句当中直接将结果作为当前查询的结果
  • 分组使用的是prod_name，而不是prod_id

97.顾客名称和订单及订单总价

• 返回Customers表中的顾客名称和Orders表中的相关订单号，以及每个订单的总价

  select c.cust_name, o.order_num, sum(quantity * item_price) as OrderTotal
  from Customers c inner join Orders o on c.cust_id=o.cust_id
  inner join OrderItems oi on o.order_num=oi.order_num
  group by cust_name, o.order_num
  order by c.cust_name, o.order_num;
  或者
  select c.cust_name, o.order_num, (quantity * item_price) as OrderTotal
  from Customers c, Orders o, OrderItems oi
  where c.cust_id=o.cust_id and o.order_num=oi.order_num
  order by cust_name, order_num;

• 注意：
  • 如果使用聚合函数，就需要在Customers、Orders两张表上的对应属性都是用group by进行分组
  • 整型和浮点型之间的类型之间存在一些问题

99.购买prod_id为BR01的产品的顾客电子邮箱

• 联表查询，重点是内连接、外连接的使用

  select cust_email
  from Customers inner join Orders using(cust_id)
  inner join OrderItems using(order_num)
  where prod_id='BR01';

• 注意：
  • 连接时的判断条件有两种（on a.id=b.id或者使用using(id))