175 组合两个表 combine-two-tables
表1: Person
列名 | 类型 |
---|---|
PersonId | int |
FirstName | varchar |
LastName | varchar |
PersonId 是上表主键
表2: Address
列名 | 类型 |
---|---|
AddressId | int |
PersonId | int |
City | varchar |
State | varchar |
AddressId 是上表主键
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State
解:
SELECT FirstName, LastName, City, State FROM Person left join Address on Person.PersonId = Address.PersonId;
176 第二高的薪水 second-highest-salary
编写一个 SQL 查询,获取 Employee 表中第二高的薪水(Salary) 。
Id | Salary |
---|---|
1 | 100 |
2 | 200 |
3 | 300 |
例如上述 Employee 表,SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null。
SecondHighestSalary |
---|
200 |
解:
SELECT MAX(Salary) AS SecondHighestSalary FROM Employee WHERE Salary NOT IN ( SELECT MAX(Salary) FROM Employee)S;
或
SELECT (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1) AS SecondHighestSalary;
177 第N高的薪水 nth-highest-salary
编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)
Id | Salary |
---|---|
1 | 100 |
2 | 200 |
3 | 300 |
例如上述 Employee 表,n = 2 时,应返回第二高的薪水 200。如果不存在第 n 高的薪水,那么查询应返回 null。
getNthHighestSalary(2) |
---|
200 |
解:
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N = N-1;
RETURN (
# Write your MySQL query statement below.
SELECT (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT N,1) AS SecondHighestSalary
);
END
178 分数排名 rank-scores
编写一个 SQL 查询来实现分数排名。如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”
Id | Score |
---|---|
1 | 3.50 |
2 | 3.65 |
3 | 4.00 |
4 | 3.85 |
5 | 4.00 |
6 | 3.65 |
例如,根据上述给定的 Scores 表,你的查询应该返回(按分数从高到低排列):
Score | Rank |
---|---|
4.00 | 1 |
4.00 | 1 |
3.85 | 2 |
3.65 | 3 |
3.65 | 3 |
3.50 | 4 |
解:
SELECT Score, (SELECT count(DISTINCT score) FROM Scores WHERE score >= s.score) AS Rank FROM Scores s ORDER BY Score DESC ;
180 连续出现的数字 consecutive-numbers
编写一个 SQL 查询,查找所有至少连续出现三次的数字。
Id | Num |
---|---|
1 | 1 |
2 | 1 |
3 | 1 |
4 | 2 |
5 | 1 |
6 | 2 |
7 | 2 |
例如,给定上面的 Logs 表, 1 是唯一连续出现至少三次的数字。
ConsecutiveNums |
---|
1 |
#select distinct l1.Num as ConsecutiveNums from Logs as l1 inner join Logs l2 on l2.Id = l1.Id + 1 and l2.Num = l1.Num inner join Logs as l3 on l3.Id = l2.Id + 1 and l3.Num = l2.Num
或
select distinct l1.Num as ConsecutiveNums from Logs as l1,Logs as l2,Logs as l3 where l2.Id = l1.Id + 1 and l2.Num = l1.Num and l3.Id = l2.Id + 1 and l3.Num = l2.Num
181 超过经理收入的员工 employees-earning-more-than-their-managers
Employee 表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id
Id | Name | Salary | ManagerId |
---|---|---|---|
1 | Joe | 70000 | 3 |
2 | Henry | 80000 | 4 |
3 | Sam | 60000 | NULL |
4 | Max | 90000 | NULL |
给定 Employee 表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。
Employee |
---|
Joe |
解:
select e1.Name as Employee from Employee as e1,Employee as e2 where e1.ManagerId = e2.Id and e1.Salary > e2.Salary
182 查找重复的电子邮件 duplicate-emails
编写一个 SQL 查询,查找 Person 表中所有重复的电子邮箱。
示例:
Id | |
---|---|
1 | a@b.com |
2 | c@d.com |
3 | a@b.com |
根据以上输入,你的查询应返回以下结果:
a@b.com |
说明:所有电子邮箱都是小写字母。
解:
select Email from Person group by Email having count(Email) > 1
183 从不订购的客户 customers-who-never-order
某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户
Customers 表:
Id | Name |
---|---|
1 | Joe |
2 | Henry |
3 | Sam |
4 | Max |
Orders 表:
Id | CustomerId |
---|---|
1 | 3 |
2 | 1 |
例如给定上述表格,你的查询应返回:
Customers |
---|
Henry |
Max |
解:
select Name as Customers from Customers where Id not in (select CustomerId from Orders)
或
select Name as Customers from Customers left join Orders ON Customers.Id = Orders.CustomerId WHERE CustomerId IS NULL;
184 部门工资最高的员工 department-highest-salary
Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
Id | Name | Salary | DepartmentId |
---|---|---|---|
1 | Joe | 70000 | 1 |
2 | Henry | 80000 | 2 |
3 | Sam | 60000 | 2 |
4 | Max | 90000 | 1 |
Department 表包含公司所有部门的信息。
Id | Name |
---|---|
1 | IT |
2 | Sales |
编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
Department | Employee | Salary |
---|---|---|
IT | Max | 90000 |
Sales | Henry | 80000 |
解:
select Department.Name as Department,Employee.Name as Employee,Salary from Employee,Department where Employee.DepartmentId = Department.Id and (Employee.Salary,Employee.DepartmentId) in (select max(Salary),DepartmentId from Employee group by DepartmentId);
185 部门工资前三高的员工 department-top-three-salaries
Employee 表包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id 。
Id | Name | Salary | DepartmentId |
---|---|---|---|
1 | Joe | 70000 | 1 |
2 | Henry | 80000 | 2 |
3 | Sam | 60000 | 2 |
4 | Max | 90000 | 1 |
5 | Janet | 69000 | 1 |
6 | Randy | 85000 | 1 |
Department 表包含公司所有部门的信息。
Id | Name |
---|---|
1 | IT |
2 | Sales |
编写一个 SQL 查询,找出每个部门工资前三高的员工。例如,根据上述给定的表格,查询结果应返回:
Department | Employee | Salary |
---|---|---|
IT | Max | 90000 |
IT | Randy | 85000 |
IT | Joe | 70000 |
Sales | Henry | 80000 |
Sales | Sam | 60000 |
解:
select Department.Name as Department,Employee.Name as Employee,Employee.Salary from Employee join Department on Employee.DepartmentId = Department.Id where (select count(distinct e1.Salary) from Employee e1 where e1.DepartmentId = Employee.DepartmentId and Employee.Salary < e1.Salary) < 3 order by Department.Name, Employee.Salary desc
196 删除重复的电子邮箱 delete-duplicate-emails
编写一个 SQL 查询,来删除 Person 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
Id | |
---|---|
1 | john@example.com |
2 | bob@example.com |
3 | john@example.com |
Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 Person 表应返回以下几行:
Id | |
---|---|
1 | john@example.com |
2 | bob@example.com |
解:
DELETE FROM Person WHERE Id NOT IN (SELECT Id from (SELECT MIN(Id) AS id FROM Person GROUP BY Email) as MinId)
197 上升的温度 rising-temperature
给定一个 Weather 表,编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。
Id(INT) | RecordDate(DATE) | Temperature(INT) |
---|---|---|
1 | 2015-01-01 | 10 |
2 | 2015-01-02 | 25 |
3 | 2015-01-03 | 20 |
4 | 2015-01-04 | 30 |
例如,根据上述给定的 Weather 表格,返回如下 Id:
Id |
---|
2 |
4 |
解:
select w1.Id from Weather as w1 join Weather as w2 where w1.RecordDate = date_add(w2.RecordDate, interval 1 day) and w1.Temperature > w2.Temperature
#可以用datediff(day,w1.RecordDate,w2.RecordDate)
595 大的国家 big-countries
这里有张 World 表
name | continent | area | population | gdp |
---|---|---|---|---|
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 area > 3000000 or population > 25000000
596 超过5名学生的课 classes-more-than-5-students
有一个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(distinct student) > 4
620 有趣的电影 not-boring-movies
某城市开了一家新的电影院,吸引了很多人过来看电影。该电影院特别注意用户体验,专门有个 LED显示板做电影推荐,上面公布着影评和相关电影描述。
作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating 排列。
例如,下表 cinema:
id | movie | description | rating |
---|---|---|---|
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 |
对于上面的例子,则正确的输出是为:
id | movie | description | rating |
---|---|---|---|
5 | House card | Interesting | 9.1 |
1 | War | great 3D | 8.9 |
解:
select id,movie,description,rating from cinema where description != 'boring' and id%2 = 1 order by rating desc
627 交换工资 swap-salary
给定一个 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 sex when 'm' then 'f' when 'f' then 'm' end
或
update salary set sex = if(sex = 'm','f','m')
262 行程和用户 Trips and Users
Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id,Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型,枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
Id | Client_Id | Driver_Id | City_Id | Status | Request_at |
---|---|---|---|---|---|
1 | 1 | 10 | 1 | completed | 2013-10-01 |
2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 |
3 | 3 | 12 | 6 | completed | 2013-10-01 |
4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 |
5 | 1 | 10 | 1 | completed | 2013-10-02 |
6 | 2 | 11 | 6 | completed | 2013-10-02 |
7 | 3 | 12 | 6 | completed | 2013-10-02 |
8 | 2 | 12 | 12 | completed | 2013-10-03 |
9 | 3 | 10 | 12 | completed | 2013-10-03 |
10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 |
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止,Role 则是一个表示(‘client’, ‘driver’, ‘partner’)的枚举类型。
Users_Id | Banned | Role |
---|---|---|
1 | No | client |
2 | Yes | client |
3 | No | client |
4 | No | client |
10 | No | driver |
11 | No | driver |
12 | No | driver |
13 | No | driver |
写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。基于上表,你的 SQL 语句应返回如下结果,取消率(Cancellation Rate)保留两位小数。
Day | Cancellation Rate |
---|---|
2013-10-01 | 0.33 |
2013-10-02 | 0.00 |
2013-10-03 | 0.50 |
解:
select request_at as Day,
round((sum(case when status = 'cancelled_by_driver' or status = 'cancelled_by_client' then 1 else 0 end)) / count(status) ,2) as 'Cancellation Rate'
from Trips where
Client_Id in (select Users_Id from Users where Banned = 'No')
and Request_at >= '2013-10-01' and Request_at <= '2013-10-03'
group by request_at
626 换座位 Exchange Seats
小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。
其中纵列的 id 是连续递增的
小美想改变相邻俩学生的座位。
你能不能帮她写一个 SQL query 来输出小美想要的结果呢?
示例:
id | student |
---|---|
1 | Abbot |
2 | Doris |
3 | Emerson |
4 | Green |
5 | Jeames |
假如数据输入的是上表,则输出结果如下:
id | student |
---|---|
1 | Doris |
2 | Abbot |
3 | Green |
4 | Emerson |
5 | Jeames |
注意:
如果学生人数是奇数,则不需要改变最后一个同学的座位。
解:
select (case
when id%2=1 and id!=(select count(*) from seat) then id+1
when id%2=0 then id-1
else id
end) id, student
from seat
order by id asc
601 体育馆的人流量 Human Traffic of Stadium
X 市建了一个新的体育馆,每日人流量信息被记录在这三列信息中:序号 (id)、日期 (date)、 人流量 (people)。
请编写一个查询语句,找出高峰期时段,要求连续三天及以上,并且每天人流量均不少于100。
例如,表 stadium:
id | date | people |
---|---|---|
1 | 2017-01-01 | 10 |
2 | 2017-01-02 | 109 |
3 | 2017-01-03 | 150 |
4 | 2017-01-04 | 99 |
5 | 2017-01-05 | 145 |
6 | 2017-01-06 | 1455 |
7 | 2017-01-07 | 199 |
8 | 2017-01-08 | 188 |
对于上面的示例数据,输出为:
id | date | people |
---|---|---|
5 | 2017-01-05 | 145 |
6 | 2017-01-06 | 1455 |
7 | 2017-01-07 | 199 |
8 | 2017-01-08 | 188 |
Note:
每天只有一行记录,日期随着 id 的增加而增加。
解:
select distinct s4.id,s4.visit_date,s4.people from stadium s1,stadium s2,stadium s3,stadium s4
where s1.id = s2.id + 1
and s2.id = s3.id + 1
and s1.people >= 100
and s2.people >= 100
and s3.people >= 100
and s4.id in (s1.id,s2.id,s3.id)
书上习题
create table Student(
s_no char(6) primary key,
class_no char(6) not null,
s_name varchar(10) not null,
s_sex char(2) check(s_sex in ('男','女')),
s_birthday datetime
);
create table Class(
class_no char(6) primary key,
class_name char(20) not null,
class_special varchar(20),
class_dept char(20)
);
create table Course(
course_no char(5) primary key,
course_name char(20) not null,
course_score numeric(6,2)
);
create table Choise(
s_no char(6),
course_no char(5),
score numeric(6,1)
);
create table Teacher(
t_no char(6) primary key,
t_name varchar(10) not null,
t_sex char(2) check(s_sex in ('男','女')),
t_birthday datetime,
t_title char(10)
);
create table Teaching(
course_no char(5),
t_no char(6)
);
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('991101','js9901','张彬','男','1981-10-1');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('991102','js9901','王蕾','女','1980-8-8');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('991103','js9901','李建国','男','1981-4-5');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('991104','js9901','李平方','男','1981-5-12');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('991201','js9902','陈东辉','男','1980-2-8');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('991202','js9902','葛鹏','男','1979-12-23');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('991203','js9902','潘桃枝','女','1980-2-6');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('991204','js9902','姚一峰','男','1981-5-7');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('001101','js0001','宋大芳','男','1980-4-9');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('001102','js0001','许辉','女','1978-8-1');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('001201','js0002','王一山','男','1980-12-4');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('001202','js0002','牛莉','女','1981-6-9');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('002101','xx0001','李丽丽','女','1981-9-19');
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values('002102','xx0001','李王','男','1980-9-23');
insert into Class(class_no,class_name,class_special,class_dept) values('js9901','计算机99-1','计算机','计算机系');
insert into Class(class_no,class_name,class_special,class_dept) values('js9902','计算机99-2','计算机','计算机系');
insert into Class(class_no,class_name,class_special,class_dept) values('js0001','计算机00-1','计算机','计算机系');
insert into Class(class_no,class_name,class_special,class_dept) values('js0002','计算机00-2','计算机','计算机系');
insert into Class(class_no,class_name,class_special,class_dept) values('xx0001','信息00-1','信息','信息系');
insert into Class(class_no,class_name,class_special,class_dept) values('xx0002','信息00-2','信息','信息系');
insert into Course(course_no,course_name,course_score) values('01001','计算机基础',3);
insert into Course(course_no,course_name,course_score) values('01002','程序设计语言',5);
insert into Course(course_no,course_name,course_score) values('01003','数据结构',6);
insert into Course(course_no,course_name,course_score) values('02001','数据库原理与应用',6);
insert into Course(course_no,course_name,course_score) values('02002','计算机网络',6);
insert into Course(course_no,course_name,course_score) values('02003','微机原理与应用',8);
insert into Choise(s_no,course_no,score) values('991101','01001',88.0);
insert into Choise(s_no,course_no,score) values('991102','01001',);
insert into Choise(s_no,course_no) values('991103','01001',91.0);
insert into Choise(s_no,course_no,score) values('991104','01001',78.0);
insert into Choise(s_no,course_no,score) values('991201','01001',67.0);
insert into Choise(s_no,course_no,score) values('991101','01002',90.0);
insert into Choise(s_no,course_no,score) values('991102','01002',58.0);
insert into Choise(s_no,course_no,score) values('991103','01002',71.0);
insert into Choise(s_no,course_no,score) values('991104','01002',85.0);
insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title) values('000001','李英','女','1964-11-3','讲师');
insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title) values('000002','王大山','男','1955-3-7','副教授');
insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title) values('000003','张朋','男','1960-10-5','讲师');
insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title) values('000004','陈为军','男','1970-3-2','助教');
insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title) values('000005','宋浩然','男','1966-12-4','讲师');
insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title) values('000006','徐红霞','女','1951-5-8','副教授');
insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title) values('000007','徐永军','男','1948-4-8','教授');
insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title) values('000008','李桂清','女','1940-11-3','教授');
insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title) values('000009','王一帆','女','1962-5-9','讲师');
insert into Teacher(t_no,t_name,t_sex,t_birthday,t_title) values('000010','田丰','男','1972-11-5','助教');
insert into Teaching(course_no,t_no) values('01001','000001');
insert into Teaching(course_no,t_no) values('01002','000002');
insert into Teaching(course_no,t_no) values('01003','000002');
insert into Teaching(course_no,t_no) values('02001','000003');
insert into Teaching(course_no,t_no) values('02002','000004');
insert into Teaching(course_no,t_no) values('01001','000005');
insert into Teaching(course_no,t_no) values('01002','000006');
insert into Teaching(course_no,t_no) values('01003','000007');
insert into Teaching(course_no,t_no) values('02001','000007');
insert into Teaching(course_no,t_no) values('02002','000008');
#查询所有学生的基本信息
select s_no,class_no,s_name,s_sex,s_birthday from Student;
#查询所有学生 显示学号姓名
select s_no,s_name from Student;
#查询男同学 显示学号姓名出生日期
select s_no,s_name,s_birthday from Student where s_sex = '男';
#查询出生日期在80-1-1前女学生
select s_no,class_no,s_name,s_sex,s_birthday from Student where s_sex='女' and s_birthday<'1980-01-01';
#查询李姓男学生
select s_no,class_no,s_name,s_sex,s_birthday from Student where s_sex='男' and s_name like "李%";
#查询名字含一
select s_no,class_no,s_name,s_sex,s_birthday from Student where s_name like "%一%";
#查询职称不是讲师
select t_no,t_name,t_sex,t_birthday,t_title from Teacher where t_title != '讲师';
#查询未参加考试的学生
select s_no,course_no,score from Choise where score is NULL;
#查询考试不及格的学生并按成绩排序
select s_no,course_no,score from Choise where score < 60 ORDER BY score ASC;
#查询课程号为01001 02001 02003所有课程
select course_no,course_name,course_score from Course where course_no in ('01001','02001','02003');
#查询1970年出生的讲师
select t_no,t_name,t_sex,t_birthday,t_title from Teacher where t_birthday < '1970';
#查询各课程号选修人数
select course_no,count(course_no) from Choise group by course_no ;
#查询教授两门课程以上的讲师
select t_no,count(course_no) from Teaching group by t_no having count(course_no)=>2 ;
#查询01001平均分最低分最高分
select avg(score),min(score),max(score) from Choise where course_no = '01001';
#查询1960年后出生职称为讲师并按出生日期升序
select t_no,t_name,t_sex,t_birthday,t_title from Teacher where t_birthday > '1960' and t_title = '讲师' ORDER BY t_birthday ASC;
#查询所有学生选课和成绩s_no s_name course_no score
select Student.s_no,s_name,course_no,score from Student join Choise on Student.s_no = Choise.s_no;
#查询计算机99-1同学的选课及成绩s_no s_name course_no course_name score
select Student.s_no,s_name,Course.course_no,course_name,score from Student join Choise,Course,Class where Student.s_no = Choise.s_no and Course.course_no = Choise.course_no and Student.class_no = Class.class_no and Class.class_name = '计算机99-1';
#查询所有学生及格科目
select Student.s_no,s_name,Course.course_no,course_name,score from Student join Choise,Course where Student.s_no = Choise.s_no and Course.course_no = Choise.course_no and Choise.score >= 60;
#查询所有学生平均成绩和所选科目数
select Student.s_no,s_name,avg(score),count(score) from Choise join Student where Student.s_no = Choise.s_no group by s_no;
#查询未参加考试的学生和课程
select Student.s_no,s_name,Course.course_no,course_name,score from Choise join Student,Course where Course.course_no = Choise.course_no and Student.s_no = Choise.s_no and score is NULL;
#查询不及格的学生和课程
select Student.s_no,s_name,Course.course_no,course_name,score from Choise join Student,Course where Course.course_no = Choise.course_no and Student.s_no = Choise.s_no and score < 60;
#查询"程序设计语言"的所有同学和成绩 ANY
select s_name,score from Student,Choise where course_no = any(select course_no from Course where course_name = '程序设计语言') and Student.s_no = Choise.s_no;
#查询所有教师的任课
select t_name,course_name from Teaching,Teacher,Course where Teacher.t_no = Teaching.t_no and Teaching.course_no = Course.course_no;
#查询李建国的同学 子查询
select s_name from Student where class_no = (select class_no from Student where s_name = '李建国') and s_name != '李建国';
#查询没有选修计算机基础NOTEXISTS
select s_name from Student,Choise where NOT EXISTS (select course_no from Course where course_name = '计算机基础' and course_no = Choise.course_no) and Student.s_no = Choise.s_no;
#查询主讲数据库原理和应用和主讲数据结构的教师姓名 UNION
select t_name from Teacher,Teaching,Course where Teacher.t_no = Teaching.t_no and Teaching.course_no = Course.course_no and Course.course_name = '数据库原理与应用' UNION select t_name from Teacher,Teaching,Course where Teacher.t_no = Teaching.t_no and Teaching.course_no = Course.course_no and Course.course_name = '数据结构';
#插入学生的存储过程
delimiter $$
create procedure insertStudent(
in s_no char(6),
in class_no char(6),
in s_name varchar(10),
in s_sex char(2),
in s_birthday datetime
)
begin
insert into Student(s_no,class_no,s_name,s_sex,s_birthday) values(s_no,class_no,s_name,s_sex,s_birthday);
end$$
delimiter ;
#插入课程 学分默认2
#delimiter $$
#create procedure insertCourse(
#in course_no char(5),
#in course_name char(20),
#in course_score numeric(6,2) DEFAULT 2
#)
#begin
#insert into Course(course_no,course_name,course_score) values(course_no,course_name,course_score);
#end$$
#delimiter ;
#根据姓名查询学生信息
delimiter $$
create procedure queryStudent(
in name varchar(10)
)
begin
select s_no,s_name from Student where s_name = name;
end$$
delimiter ;
#触发器
drop trigger StudentTrigger;
delimiter $$
create trigger StudentTrigger
AFTER INSERT ON Student FOR EACH ROW
begin
select NEW.s_no into @arg1;
select NEW.s_name into @arg2;
end$$
delimiter ;