mysql action-CSDN博客

本文链接：https://blog.csdn.net/theomarker/article/details/90414865

task10

mysql> create table if not exists Trips (
    -> Id int not null primary key,
    -> Client_Id int not null,
    -> Driver_Id int not null,
    -> City_Id int not null,
    -> Status VARCHAR(50) not null,
    -> Request_at VARCHAR(50) not null
    -> );
mysql> insert into Trips values(2,2,11,1,'cancelled_by_driver','2013-10-01');
mysql> insert into Trips values(3,3,12,6,'completed','2013-10-01');
mysql> insert into Trips values(4,4,13,6,'cancelled_by_driver','2013-10-01');
mysql> insert into Trips values(5,1,10,1,'completed','2013-10-02');
mysql> insert into Trips values(6,2,11,6,'completed','2013-10-02');
mysql> insert into Trips values(7,3,12,6,'completed','2013-10-02');
mysql> insert into Trips values(8,2,12,12,'completed','2013-10-03');
mysql> insert into Trips values(9,3,10,12,'completed','2013-10-03');
mysql> insert into Trips 
mysql> select * from Trips;
+----+-----------+-----------+---------+---------------------+------------+
| 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_driver | 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 |
+----+-----------+-----------+---------+---------------------+------------+
mysql> create table if not exists Users (
    -> Users_Id int not null primary key,
    -> Banned VARCHAR(50) not null,
    -> Role VARCHAR(50) not null
    -> );
mysql> insert into Users VALUES(1,'No','client'),(2,'Yes','client'),(3,'No','client'),(4,'No','client'),(10,'No','drivers'),(11,'No','drivers'),(12,'No','drivers'),(13,'No','drivers');
mysql> select * from Users;
+----------+--------+---------+
| Users_Id | Banned | Role    |
+----------+--------+---------+
|        1 | No     | client  |
|        2 | Yes    | client  |
|        3 | No     | client  |
|        4 | No     | client  |
|       10 | No     | drivers |
|       11 | No     | drivers |
|       12 | No     | drivers |
|       13 | No     | drivers |
+----------+--------+---------+

task 11

mysql> insert into Employee values(5,'Janet',69000,1),(6,'Randy',85000,1);
mysql> select d.Name as Department, e.Name as Employee, e.Salary
    -> from Department d, Employee e
    -> where e.DepartmentId = d.Id and (select count(distinct Salary) from Employee where DepartmentId=d.Id and Salary > e.Salary)<3
    -> order by Department;
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT         | Max      |  90000 |
| IT         | Randy    |  85000 |
| IT         | Joe      |  70000 |
| Sales      | Henry    |  80000 |
| Sales      | Sam      |  60000 |
+------------+----------+--------+

task 12

mysql> select score, (select count(score)+1 from scores where score>s.score) as r from scores s order by score DESC;
+-------+------+
| score | r    |
+-------+------+
|  4.00 |    1 |
|  4.00 |    1 |
|  3.85 |    3 |
|  3.65 |    4 |
|  3.65 |    4 |
|  3.50 |    6 |
+-------+------+