SQL 题目

SQL33 创建一个actor表，包含如下列信息

CREATE TABLE actor( 
actor_id smallint(5) NOT NULL PRIMARY KEY, ## 主键
first_name varchar(45) NOT NULL, ## 按顺序是名称，类型（长度），是否可为null
last_name varchar(45) NOT NULL,
last_updata date NOT NULL 
)

SQL1 查找最晚入职员工的所有信息

SELECT * FROM employees #表的名字是employees
order by hire_data DESC # 根据hire_date降序排列，升序是ASC
limit 0,1 # 输出第0-1行的数据

SQL2 查找入职员工时间排名倒数第三的员工所有信息

select * from employees
order by hire_date desc
limit 1 OFFSET 2 ## 删除2两条，输出第三条 

SQL3 查找当前薪水详情以及部门编号dept_no

select salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_manager.dept_no  ## 输出时要求dept_no放最后
from dept_manager, salaries 
where dept_manager.emp_no=salaries.emp_no ## 用where连接两个表
order by salaries.emp_no;

SQL7 查找薪水记录超过15次的员工号emp_no以及其对应的记录次数

SELECT emp_no, COUNT(emp_no) AS t ## COUNT（）计数
FROM salaries
GROUP BY emp_no ## 分组
HAVING t > 15; ## 分组条件

leetcode

变更性别

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

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

连续出现的数字

查找所有至少连续出现三次的数字。
返回的结果表中的数据可以按 任意顺序 排列。

select distinct l1.Num AS ConsecutiveNums
from Logs l1, Logs l2, Logs l3
where l1.Id = l2.Id - 1
    and l2.Id = l3.Id-1
    and l1.Num = l2.Num
    and l2.Num = l3.Num

删除重复的电子邮箱

来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个

delete p1
from Person AS p1,Person AS p2
where p1.Email = p2.Email
      and p1.Id > p2.Id

大的国家

如果一个国家的面积超过 300 万平方公里，或者人口超过 2500 万，那么这个国家就是大国家。
编写一个 SQL 查询，输出表中所有大国家的名称、人口和面积。

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

组合两个表

编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
FirstName, LastName, City, State
要用左连接

select p.FirstName, p.LastName, a.City, a.State
from Person p Left Outer Join Address a
on p.PersonId = a.PersonId

换座位

两两交换，如果有最后一个不变

select s1.id-1 as id, s1.student
from seat s1
where s1.id mod 2 = 0
union
select s2.id+1 as id, s2.student
from seat s2
where s2.id mod 2 = 1
    and s2.id != (select max(s3.id) from seat s3)
union
select s4.id as id, s4.student as student
from seat s4
where s4.id mod 2 = 1
    and s4.id = (select max(s5.id) from seat s5)
order by id

查找重复的电子邮箱

select Email
from Person
group by Email
having count(*)>= 2

有趣的电影

select * from cinema
where id mod 2 = 1
    and description != "boring"
order by rating desc

从不订购的客户

select C.Name as Customers
from 
    Customers as C 
    LEFT JOIN Orders as O
    on C.Id = O.CustomerId
where O.CustomerId IS NULL

部门工资最高的员工

SELECT
    d.Name AS Department, 
    e.Name AS Employee, 
    e.Salary AS Salary
FROM 
    Department AS d, 
    Employee AS e,
    (SELECT DepartmentId, max(Salary) AS Salary FROM Employee GROUP BY DepartmentId) AS m
WHERE
    d.Id = e.DepartmentId
    AND e.DepartmentId = m.DepartmentId
    AND e.Salary = m. Salary
 

分数排名

select 
    s.Score As Score,
    (select count(distinct b.Score) from Scores AS b where b.Score >= s.Score ) AS 'Rank'
from 
    Scores AS s
order by 
    s.Score desc