SQL刷题

SELECT
    product_id
FROM
    products
WHERE
    low_fats = 'Y'
    AND recyclable = 'Y'

SELECT
    name
FROM
    Customer
WHERE
    referee_id != 2
    or referee_id is NULL

SELECT
    name,
    population,
    area
FROM
    World
WHERE
    area >= 3000000
    OR population >= 25000000

SELECT
    DISTINCT author_id AS id
FROM
    Views
WHERE
    author_id = viewer_id
ORDER BY
    id ASC

SELECT
    tweet_id
FROM
    Tweets
WHERE
    LENGTH(content) > 15

SELECT
    EmployeeUNI.unique_id,
    Employees.name
FROM
    Employees
    LEFT JOIN EmployeeUNI ON Employees.id = EmployeeUNI.id

SELECT
    product_name,
    year,
    price
FROM
    Product p
    RIGHT JOIN Sales s ON p.product_id = s.product_id

SELECT
    product_name,
    year,
    price
FROM
    Sales s
    LEFT JOIN Product p ON p.product_id = s.product_id

SELECT
    product_name,
    year,
    price
FROM
    Product p
    Join Sales s ON p.product_id = s.product_id

SELECT
    customer_id,
    COUNT(customer_id) AS count_no_trans
FROM
    Visits v
    LEFT JOIN Transactions t ON v.visit_id = t.visit_id
WHERE
    transaction_id IS NULL
GROUP BY
    customer_id

SELECT
    w2.id
FROM
    Weather w1
    JOIN Weather w2 ON DATEDIFF(w1.recordDate, w2.recordDate) = -1
    AND w1.temperature < w2.temperature

SELECT
    w2.id
FROM
    Weather w1,
    Weather w2
WHERE
    DATEDIFF(w1.recordDate, w2.recordDate) = -1
    AND w1.temperature < w2.temperature

SELECT
    a1.machine_id,
    round(
        avg(a2.timestamp - a1.timestamp),
        3
    ) AS processing_time
FROM
    Activity a1
    JOIN Activity a2 ON a1.machine_id = a2.machine_id
    AND a1.process_id = a2.process_id
    AND a1.activity_type = 'start'
    AND a2.activity_type = 'end'
GROUP BY
    a1.machine_id

SELECT
    e.name,
    b.bonus
FROM
    Bonus b
    RIGHT JOIN Employee e ON e.empId = b.empId
WHERE
    b.bonus < 1000
    OR b.bonus IS NULL

SELECT
    stu.student_id,
    stu.student_name,
    sub.subject_name,
    COUNT(exam.subject_name) AS attended_exams
FROM
    Students stu
    CROSS JOIN Subjects sub
    LEFT JOIN Examinations exam ON stu.student_id = exam.student_id
    AND sub.subject_name = exam.subject_name
GROUP BY
    sub.subject_name,
    stu.student_id
ORDER BY
    stu.student_id,
    sub.subject_name

SELECT
    stu.student_id,
    stu.student_name,
    sub.subject_name,
    COALESCE(exam.attended_exams, 0) AS attended_exams
FROM
    Students stu
    CROSS JOIN Subjects sub
    LEFT JOIN(
        SELECT
            student_id,
            subject_name,
            COUNT(*) AS attended_exams
        FROM
            Examinations
        GROUP BY
            subject_name,
            student_id
    ) exam ON stu.student_id = exam.student_id
    AND sub.subject_name = exam.subject_name
ORDER BY
    stu.student_id,
    sub.subject_name

-- testcase 0:
-- | id  | name  | department | managerId |
-- +-----+-------+------------+-----------+
-- | 101 | John  | A          | None      |
-- | 102 | Dan   | A          | 101       |
-- | 103 | James | A          | 101       |
-- | 104 | Amy   | A          | 101       |
-- | 105 | Anne  | A          | 101       |
-- | 106 | Ron   | B          | 101       |
--------------------------------------------
-- +------+
-- | name |
-- +------+
-- | John |
-- +------+
-- testcase 1:
-- | id  | name  | department | managerId |
-- | --- | ----- | ---------- | --------- |
-- | 101 | John  | A          | null      |
-- | 102 | Dan   | A          | 101       |
-- | 103 | James | A          | 101       |
-- | 104 | Amy   | A          | 101       |
-- | 105 | Anne  | A          | 101       |
-- | 106 | Ron   | B          | 101       |
-- | 111 | John  | A          | null      |
-- | 112 | Dan   | A          | 111       |
-- | 113 | James | A          | 111       |
-- | 114 | Amy   | A          | 111       |
-- | 115 | Anne  | A          | 111       |
-- | 116 | Ron   | B          | 111       |
--------------------------------------------
-- | name |
-- | ---- |
-- | John |
-- | John  |
-- testcase 2:
-- | id  | name  | department | managerId |
-- | --- | ----- | ---------- | --------- |
-- | 101 | John  | A          | null      |
-- | 102 | Dan   | A          | 101       |
-- | 103 | James | A          | 101       |
-- | 104 | Amy   | A          | 101       |
-- | 105 | Anne  | A          | 101       |
-- | 106 | Ron   | B          | 101       |
-- | 107 | Tom   | A          | 102       |
-- | 108 | Tommy | A          | 102       |
-- | 109 | Peter | C          | 102       |
-- | 110 | Dong  | A          | 102       |
-- | 111 | DIDI  | D          | 102       |
--------------------------------------------
-- | name |
-- | ---- |
-- | John |
-- | Dan  |
SELECT
    e1.name
FROM
    Employee e1
    JOIN Employee e2 ON e2.managerId = e1.id
GROUP BY
    e2.managerId
HAVING
    COUNT(e2.id) >= 5