SQL面试题
一、从不订购的客户
题目描述:
某网站包含两个表,Customers
表和 Orders
表。编写一个 SQL 查询,找出所有从不订购任何东西的客户
例如给定上述表格,你的查询应返回:
select Name as 'Customers' from Customers where Id not in (select CustomerId from Orders);
二、超过经理收入的员工
题目描述:
Employee
表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id
给定 Employee
表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工
select e1.Name as Employee from Employee as e1, Employee as e2 where e1.ManagerId = e2.Id And e1.Salary > e2.Salary;
或者采用自连接查询:
select e1.name as Employee from Employee as e1 inner join Employee as e2 on e1.ManagerId = e2.Id And e1.Salary > e2.Salary;
三、查询出科目成绩都大于80分的学生的名字
drop table if EXISTS tmp_1; create table tmp_1 (`Id` INT NOT NULL AUTO_INCREMENT, name varchar(10), subject varchar(10), score int, PRIMARY KEY(`Id`)); insert into tmp_1 (name,subject,score) values ('李云龙','语文',79); insert into tmp_1 (name,subject,score) values ('李云龙','数学',81); insert into tmp_1 (name,subject,score) values ('楚云飞','语文',81); insert into tmp_1 (name,subject,score) values ('楚云飞','数学',89); insert into tmp_1 (name,subject,score) values ('张大彪','语文',79); insert into tmp_1 (name,subject,score) values ('张大彪','数学',90); #虽然瞟一眼就知道答案是楚云飞,但是我们要通过sql求出结果 select name from tmp_1 group by name having MIN(socre) > 80; #或者选出score < 80的name,再从表中排除掉这些名字 select distinct(tt.name) from tmp_1 tt where tt.name not in (select t.name from tmp_1 t where t.score <80);