SQL综合练习

最新推荐文章于 2022-09-22 23:00:03 发布

miaochangq

最新推荐文章于 2022-09-22 23:00:03 发布

阅读量208

点赞数

本文链接：https://blog.csdn.net/miaochangq/article/details/111928181

版权

题目

题目链接

练习一

CREATE TABLE employee(id INTEGER NOT NULL,
                      `name` VARCHAR(20) NOT NULL,
                      salary INTEGER NOT NULL,
                      departmentid INTEGER NOT NULL,
                      PRIMARY KEY (id));
INSERT INTO employee(id,name,salary,departmentid) VALUES(
                    1,'Joe',70000,1);
INSERT INTO employee(id,name,salary,departmentid) VALUES(
                    2,'Henry',80000,2)
                    (3,'Sam',60000,2)
                    (4,'Max',90000,1);
CREATE TABLE department(id INTEGER NOT NULL,name VARCHAR(20) NOT
                       NULL,PRIMARY KEY(id));
INSERT INTO department(id,name) VALUES 
                      (1,'IT'), 
                      (2,Sales);
SELECT 
    department
    ,employee
    ,salary
FROM(
    SELECT
        d.name AS department,
        e.name AS employee,
        e.salary,
        rank() OVER(PARTITION BY department ORDER BY salary DESC) AS
    ranks
    FROM employee e
    JOIN department d
    ON e.departmentid = d.id)a
WHERE ranks=1;

练习二

CREATE TABLE seat(id INTEGER NOT NULL,student VARCHAR(20),PRIMARY KEY(id));
INSERT INTO seat(id,student) VALUES
                 (1,'Abbot'),
                 (2,'Doris'),
                 (3,'Emerson'),
                 (4,'Green'),
                 (5,'Jeames');
SELECT
    ROW_NUMBER() OVER(ORDER BY `groups`) AS id
    ,student
FROM(
    SELECT
        student
        ,groups
    FROM(
        SELECT
            *
            ,RANK() OVER(PARTITION BY `groups`
                        ORDER BY id DESC) AS ranks
        FROM(
            SELECT
                *
                ,ROUND(id/2) AS groups
            FROM
                seat
        )a
    )b
)c;

练习三

CREATE TABLE scores(id INTEGER NOT NULL,
                   score FLOAT NOT NULL,
                   PRIMARY KEY(id));
INSERT INTO scores(id,score)VALUES
                  (1,3.50),
                  (2,3.65),
                  (3,4.00),
                  (4,3.85),
                  (5,4.00),
                  (6,3.65);
SELECT
    score
    ,DENSE_RANK() OVER(ORDER BY score desc) as ranks
FROM
    scores;

练习四

CREATE TABLE numbers(id INTEGER NOT NULL,
                     num INTEGER NOT NULL,
                     PRIMARY KEY(id));
INSERT INTO numbers(id,num)VALUES(1,1),
                    (2,1),
                    (3,1),
                    (4,2),
                    (5,1),
                    (6,2),
                    (7,2);              
SELECT
    n1.num AS ConsecutiveNums
FROM
    numbers n1,
    numbers n2,
    numbers n3
WHERE
    n1.id=n2.id-1
    AND n2.id = n3.id-1
    AND n1.num=n2.num
    AND n2.num=n3.num;

练习五

CREATE TABLE tree(id INTEGER NOT NULL,
                  p_id INTEGER,
                  PRIMARY KEY(id));
INSERT INTO tree(id,p_id)
VALUES(1,null),
      (2,1),
      (3,1),
      (4,1),
      (5,2);
SELECT
    id
    ,CASE WHEN p_id IS NULL THEN 'Root'
          WHEN id IN (SELECT DISTINCT p_id FROM tree) THEN 'Inner'
          ELSE 'Leaf' 
    END AS Type
FROM
    tree;

练习六

CREATE TABLE Employee(id INTEGER NOT NULL,
                      name VARCHAR(20) NOT NULL,
                      department VARCHAR(5) NOT NULL,
                      managerid INTEGER,
                      PRIMARY KEY(id));
INSERT INTO Employee(id,name,department,managerid)
            VALUES(101,'John','A',NULL),
                  (102,'Dan','A',101),
                  (103,'James','A',101),
                  (104,'Amy','A',101),
                  (105,'Anne','A',101),
                  (106,'Ron','B',101);

SELECT
    name
FROM
    `Employee`
WHERE id = (SELECT
                managerid
            FROM
                (SELECT
                    managerid
                    ,COUNT(DISTINCT id) AS counts
                FROM
                    `Employee`
                GROUP BY managerid)a
            WHERE counts=5);

练习七

SELECT
    score
    ,rank
FROM(
    SELECT
        *
        ,RANK() OVER(ORDER BY score DESC) AS rank
        ,DENSE_RANK() OVER(ORDER BY score DESC) AS rank1
    FROM
        scores
    )a;

练习八

CREATE TABLE survey_log(
    uid INTEGER NOT NULL,
    action VARCHAR(20) NOT NULL,
    question_id INTEGER NOT NULL,
    answer_id INTEGER,
    q_num INTEGER NOT NULL,
    `timestamp` INTEGER NOT NULL,
    PRIMARY KEY(timestamp)
);
INSERT INTO survey_log(uid,action,question_id,answer_id,q_num,`timestamp`)
VALUES(5,'show',285,NULL,1,123),
      (5,'answer',285,124124,1,124),
      (5,'show',369,NULL,2,125),
      (5,'skip',369,NULL,2,126);
SELECT
    question_id as survey_log
FROM(
    SELECT
        question_id
        ,SUM(huida) / SUM(chakan) as huidalv
    FROM(
        SELECT
            *
            ,CASE WHEN action = 'show' THEN 1 ELSE 0 END AS chakan
            ,CASE WHEN action = 'answer' THEN 1 ELSE 0 END AS huida
            ,CASE WHEN action = 'skip' THEN 1 ELSE 0 END AS tiaoguo
        FROM
            survey_log)a
    GROUP BY question_id
    ORDER BY huidalv DESC)b
LIMIT 1;

练习九

DROP TABLE employee;
CREATE TABLE employee(id INTEGER NOT NULL,
                      `name` VARCHAR(20) NOT NULL,
                      salary INTEGER NOT NULL,
                      departmentid INTEGER NOT NULL,
                      PRIMARY KEY (id));
INSERT INTO TABLE employee(id,nam,salary,departmentid)
VALUES(1,'Joe',70000,1),
      (2,'Henry',80000,2),
      (3,'Sam',60000,2),
      (4,'Max',90000,1),
      (6,'Janet',69000,1),
      (6,'Randy',85000,1);
SELECT
    department
    ,name AS employee
    ,salary
FROM(
    SELECT
        *
        ,RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS ranks
    FROM(
        SELECT 
            e.*
            ,d.name AS department
        FROM
            employee e
        JOIN
            department d
        on e.departmentid = d.id)a)b
WHERE ranks<=3;

练习十

CREATE TABLE point_2d(x INTEGER NOT NULL,y INTEGER NOT NULL);
INSERT INTO point_2d(x,y)
VALUES(-1,-1),
      (0,0),
      (-1,-2);
SELECT
    MIN(juli) as shortest
FROM(
    SELECT 
        p1.x
        ,p1.y
        ,p2.x AS x_2
        ,p2.y AS y_2
        ,abs (p1.x-p2.x)+abs(p1.y-p2.y)AS juli 
    FROM 
        point_2d p1
        ,point_2d p2
    WHERE abs (p1.x-p2.x)+abs(p1.y-p2.y) !=0)a;

练习十一

CREATE TABLE trips(
    id INTEGER NOT NULL
    ,client_id INTEGER NOT NULL
    ,driver_id INTEGER NOT NULL
    ,city_id INTEGER NOT NULL
    ,status VARCHAR(50) NOT NULL
    ,request_at VARCHAR(50)
    ,PRIMARY KEY(id)
);
INSERT INTO trips(id,client_id,driver_id,city_id,status,request_at)
VALUES(1,1,10,1,'completed','2013-10-1'),
      (2,2,11,1,'cancelled_by_driver','2013-10-1'),
      (3,3,12,6,'completed','2013-10-1'),
      (4,4,13,6,'cancelled_by_client','2013-10-1'),
      (5,1,10,1,'completed','2013-10-2'),
      (6,2,11,6,'completed','2013-10-2'),
      (7,3,12,6,'completed','2013-10-2'),
      (8,2,12,12,'completed','2013-10-3'),
      (9,3,10,12,'completed','2013-10-3'),
      (10,4,13,12,'cancelled_by_driver','2013-10-3')
;
CREATE TABLE users(
    users_id INTEGER NOT NULL
    ,banned VARCHAR(5) NOT NULL
    ,role VARCHAR(10) NOT NULL
    ,PRIMARY KEY (users_id)
);
INSERT INTO users(users_id,banned,role)
VALUES(1,'No','client'),
      (2,'Yes','client'),
      (3,'No','client'),
      (4,'No','client'),
      (10,'No','driver'),
      (11,'No','driver'),
      (12,'No','driver'),
      (13,'No','driver');

SELECT
    request_at
    ,SUM(cancel_trips) / SUM(total_trips) AS CancellationRate
FROM
    (SELECT
        id
        ,request_at
        ,1 as total_trips
        ,CASE WHEN status IN ('cancelled_by_client','cancelled_by_driver')
                THEN 1 ELSE 0 END AS cancel_trips
    FROM
        (SELECT
            s.*
            ,u1.banned AS driver_baned
        FROM
            (SELECT
                t.*
                ,u.banned AS client_baned
            FROM
                trips t
            LEFT JOIN
                users u
            ON t.client_id =u.users_id)s
            LEFT JOIN
                users u1
            ON s.driver_id =u1.users_id
        WHERE client_baned !='Yes')t)m
GROUP BY request_at;

/*
+----+-----------+-----------+---------+---------------------+------------+--------------+--------------+
| id | client_id | driver_id | city_id | status              | request_at | client_baned | driver_baned |
+----+-----------+-----------+---------+---------------------+------------+--------------+--------------+
| 1  | 1         | 10        | 1       | completed           | 2013-10-1  | No           | No           |
| 2  | 2         | 11        | 1       | cancelled_by_driver | 2013-10-1  | Yes          | No           |
| 3  | 3         | 12        | 6       | completed           | 2013-10-1  | No           | No           |
| 4  | 4         | 13        | 6       | cancelled_by_client | 2013-10-1  | No           | No           |
| 5  | 1         | 10        | 1       | completed           | 2013-10-2  | No           | No           |
| 6  | 2         | 11        | 6       | completed           | 2013-10-2  | Yes          | No           |
| 7  | 3         | 12        | 6       | completed           | 2013-10-2  | No           | No           |
| 8  | 2         | 12        | 12      | completed           | 2013-10-3  | Yes          | No           |
| 9  | 3         | 10        | 12      | completed           | 2013-10-3  | No           | No           |
| 10 | 4         | 13        | 12      | cancelled_by_driver | 2013-10-3  | No           | No           |
+----+-----------+-----------+---------+---------------------+------------+--------------+--------------+
*/

miaochangq

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL综合练习

题目题目链接练习一CREATE TABLE employee(id INTEGER NOT NULL, `name` VARCHAR(20) NOT NULL, salary INTEGER NOT NULL, departmentid INTEGER NOT NULL, PRIMARY KEY (id));INSERT IN
复制链接

扫一扫