Task06

A.1

DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee(
    Id INTEGER(4) NOT NULL ,
    Name VARCHAR(128) NOT NULL ,
    Salary INTEGER(128) NOT NULL ,
    DepartmentId INTEGER(4) NOT NULL ,
    PRIMARY KEY (Id) );
INSERT INTO Employee VALUES (1, 'Joe', 70000, 1),
                            (2, 'Henry', 80000, 2),
                            (3, 'Sam', 60000, 2),
                            (4, 'Max', 90000, 1);
DROP TABLE IF EXISTS Department;
CREATE TABLE Department(
    id INTEGER(4) NOT NULL ,
    name VARCHAR(128) NOT NULL ,
    PRIMARY KEY (id) );

INSERT INTO Department VALUES (1, 'IT'),
                              (2, 'Sales');

SELECT p1.Name as Department,
       p2.Name as Employee,
       p3.max_salary as Salary
  From Department AS p1,
       Employee AS p2,
       (SELECT MAX(Salary) AS max_salary, DepartmentId
          From Employee
         GROUP BY DepartmentId) AS p3
 WHERE p3.DepartmentId = p1.Id
   AND p2.Salary = p3.max_salary;

A.2

DROP TABLE IF EXISTS seat;
CREATE TABLE seat
(   id INTEGER(4) NOT NULL ,
    student VARCHAR(128) NOT NULL ,
    PRIMARY KEY (id) );
INSERT INTO seat VALUES (1, 'Abbot'),
                        (2, 'Doris'),
                        (3, 'Emerson'),
                        (4, 'Green'),
                        (5, 'Jemes');
SELECT p.id, p.student
  FROM (SELECT id-1 AS id, student
          FROM seat
         WHERE MOD(id,2)=0
         UNION
        SELECT id+1 AS id, student
          FROM seat
         WHERE MOD(id,2)=1 AND id != (SELECT COUNT(*) FROM seat)
         UNION
        SELECT id, student
          FROM seat
         WHERE id = (SELECT COUNT(*) FROM seat)
           ) AS p
 ORDER BY id;

A.3

DROP TABLE IF EXISTS grade;
CREATE TABLE grade
    (class CHAR(1) NOT NULL ,
     score_avg CHAR(2) NOT NULL ,
     PRIMARY KEY (class));
INSERT INTO grade VALUES (1, 93),
                         (2, 93),
                         (3, 93),
                         (4, 91);

SELECT class,
       score_avg,
       RANK() over w AS 'standard_rank',
       DENSE_RANK() OVER w AS 'dense_rank',
       ROW_NUMBER() OVER w AS 'row_number_rank'
  FROM grade
WINDOW w AS (ORDER BY score_avg DESC);

A.4

DROP TABLE IF EXISTS numbers;
CREATE TABLE numbers
    (Id CHAR(1) NOT NULL ,
     Num CHAR(1) NOT NULL ,
     PRIMARY KEY (Id));
INSERT INTO numbers VALUES (1, 1),
                           (2, 1),
                           (3, 1),
                           (4, 2),
                           (5, 1),
                           (6, 2),
                           (7, 2);
SELECT DISTINCT n1.Num AS ConsecutiveNums
  FROM numbers AS n1, numbers AS n2, numbers AS n3
 WHERE n1.Id = n2.Id - 1
   AND n2.Id = n3.Id - 1
   AND n1.Num = n2.Num
   AND n2.Num = n3.Num;

A.5

DROP TABLE IF EXISTS tree;
CREATE TABLE tree
    (id CHAR(1) NOT NULL,
     p_id CHAR(1),
     PRIMARY KEY(id));

INSERT INTO tree VALUES (1,NULL),
                        (2,1),
                        (3,1),
                        (4,2),
                        (5,2);

SELECT id,
       CASE WHEN t.p_id IS NULL THEN 'ROOT'
            WHEN t.p_id IS NOT NULL AND (t.id IN (SELECT p_id FROM tree)) THEN 'INNER'
       ELSE 'LEAF'
       END AS Type
  FROM tree t

A.6

DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
    (Id VARCHAR(6) NOT NULL,
     Name VARCHAR(5) NOT NULL,
     Department CHAR(1) NOT NULL,
     Manager_Id VARCHAR(6),
     PRIMARY KEY (Id));
INSERT INTO Employee 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 IN (SELECT Manager_ID
                FROM (SELECT Id,
                             Manager_Id,
                             COUNT(Id) OVER (PARTITION BY Manager_Id ORDER BY Manager_Id) AS num_same_manager
                      FROM Employee) AS temp
 WHERE num_same_manager >= 5);

A.7

DROP TABLE IF EXISTS survey_log;
CREATE TABLE survey_log
    (uid CHAR(1) NOT NULL,
     action VARCHAR(6),
     question_id CHAR(3) NOT NULL,
     answer_id VARCHAR(12),
     q_num CHAR(1) NOT NULL,
     timestamp CHAR(3));

INSERT INTO survey_log 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),
                              (6,'SHOW',285,NULL,1,123),
                              (6,'SKIP',285,NULL,1,124);
SELECT question_id
  FROM (SELECT question_id,
             SUM(valid_response)/COUNT(uid) AS response_rate
          FROM (SELECT uid,
                       question_id,
                  CASE WHEN action='ANSWER' AND answer_id IS NOT NULL THEN 1
                       WHEN action='SKIP' AND answer_id IS NULL THEN 0
	              ELSE NULL
                   END AS valid_response
                  FROM survey_log
                 WHERE action = 'ANSWER'
                    OR action = 'SKIP') AS survey_log_w_valid_response
         GROUP BY question_id) AS survey_log_w_response_rate
 ORDER BY response_rate DESC
 LIMIT 1;

A.8

INSERT INTO Employee VALUES (5, 'Janet', 69000, 1),
                            (6, 'Randy', 85000, 1);

(SELECT D.Name AS Department,
        E.Name AS Employee,
        E.Salary
   FROM Employee AS E INNER JOIN Department AS D
     ON E.DepartmentId = D.Id
  WHERE D.Name = 'Sales'
  ORDER BY E.Salary DESC
  LIMIT 3)
  UNION
(SELECT D.Name AS Department,
        E.Name AS Employee,
        E.Salary
   FROM Employee AS E INNER JOIN Department AS D
     ON E.DepartmentId = D.Id
  WHERE D.name = 'IT'
  ORDER BY E.Salary DESC
  LIMIT 3);

A.9

DROP TABLE IF EXISTS point_2d;
CREATE TABLE point_2d
    (x FLOAT NOT NULL,
     y FLOAT not NULL,
     PRIMARY KEY(x,y));
INSERT INTO point_2d VALUES (-1,-1),
                            (0,0),
                            (-1,-2);
SELECT ROUND(MIN(SQRT(POWER(p1.x-p2.x,2)+POWER(p1.y-p2.y,2))),2) AS shortest_distance
  FROM point_2d AS p1, point_2d AS p2
 WHERE p1.x!=p2.x OR p1.y!=p2.y
 ORDER BY shortest_distance;

A.10

DROP TABLE IF EXISTS Trips;
CREATE TABLE trips
    (id INTEGER(4) NOT NULL ,
     client_id INTEGER(4) NOT NULL ,
     driver_id INTEGER(4) NOT NULL ,
     city_id INTEGER(4) NOT NULL ,
     STATUS VARCHAR(64) NOT NULL ,
     request_at DATE,
     PRIMARY KEY (id) );
INSERT INTO trips VALUES (1,1,10,1,'complete',20131001),
                         (2,2,11,1,'cancelled_by_driver',20131001),
                         (3,3,12,6,'complete',20131001),
                         (4,4,13,6,'cancelled_by_client',20131001),
                         (5,1,10,1,'complete',20131002),
                         (6,2,11,6,'complete',20131002),
                         (7,3,12,6,'complete',20131002),
                         (8,2,12,12,'complete',20131003),
                         (9,3,10,12,'complete',20131003),
                         (10,4,13,12,'cancelled_by_driver',20131003);
SELECT request_at,
       ROUND((COUNT(IF(p.status='cancelled_by_driver',true,null))+COUNT(IF(p.status='cancelled_by_client',TRUE,NULL)))/COUNT(p.status),2) AS 'Cancellation Rate'
  FROM (SELECT t.status,t.request_at
          FROM trips AS t, (SELECT users_id AS banned_users_id FROM users WHERE banned='Yes') AS u
         WHERE t.client_id != u.banned_users_id
           AND t.driver_id != u.banned_users_id) AS p
 GROUP BY request_at;

B.1

DROP TABLE IF EXISTS student_score;
CREATE TABLE student_score
    (name VARCHAR(4) NOT NULL,
     subject VARCHAR(64) NOT NULL ,
     score INTEGER(4) NOT NULL ,
     PRIMARY KEY (name, subject));
INSERT INTO student_score VALUES ('A','chinese',99),
                                 ('A','math',98),
                                 ('A','english',97),
                                 ('B','chinese',92),
                                 ('B','math',91),
                                 ('B','english',90),
                                 ('C','chinese',88),
                                 ('C','math',87),
                                 ('C','english',85);
SELECT name,
	   SUM(CASE WHEN subject = 'chinese' THEN score
		   ELSE NULL
		   END) AS 'chinese',
	   SUM(CASE WHEN subject = 'math' THEN score
		   ELSE NULL
		   END) AS 'math',
	   SUM(CASE WHEN subject = 'english' THEN score
		   ELSE NULL
           END) AS 'english'
  FROM student_score
 GROUP BY name;
浙ICP备19012682号