Open woneuy01 opened 4 years ago
Product table: +------------+--------------+------------+ | product_id | product_name | unit_price | +------------+--------------+------------+ | 1 | S8 | 1000 | | 2 | G4 | 800 | | 3 | iPhone | 1400 | +------------+--------------+------------+
Sales table: +-----------+------------+----------+------------+----------+-------+ | seller_id | product_id | buyer_id | sale_date | quantity | price | +-----------+------------+----------+------------+----------+-------+ | 1 | 1 | 1 | 2019-01-21 | 2 | 2000 | | 1 | 2 | 2 | 2019-02-17 | 1 | 800 | | 2 | 2 | 3 | 2019-06-02 | 1 | 800 | | 3 | 3 | 4 | 2019-05-13 | 2 | 2800 | +-----------+------------+----------+------------+----------+-------+
Result table: +-------------+ | seller_id | +-------------+ | 1 | | 3 | +-------------+
with tot as ( select seller_id, sum(price) as total from Sales group by 1 order by total desc ) select seller_id from tot where tot.total = (select max(tot.total) from tot )
Write a SQL query for a report that provides the pairs (actor_id, director_id) where the actor have cooperated with the director at least 3 times.
Example:
ActorDirector table: +-------------+-------------+-------------+ | actor_id | director_id | timestamp | +-------------+-------------+-------------+ | 1 | 1 | 0 | | 1 | 1 | 1 | | 1 | 1 | 2 | | 1 | 2 | 3 | | 1 | 2 | 4 | | 2 | 1 | 5 | | 2 | 1 | 6 | +-------------+-------------+-------------+
Result table: +-------------+-------------+ | actor_id | director_id | +-------------+-------------+ | 1 | 1 | +-------------+-------------+ The only pair is (1, 1) where they cooperated exactly 3 times.
SELECT actor_id, director_id FROM ActorDirector GROUP BY actor_id, director_id Having count(distinct timestamp) >= 3
Write an SQL query to find the users who have valid emails.
A valid e-mail has a prefix name and a domain where:
The prefix name is a string that may contain letters (upper or lower case), digits, underscore '_', period '.' and/or dash '-'. The prefix name must start with a letter. The domain is '@leetcode.com'. Return the result table in any order.
The query result format is in the following example.
Users +---------+-----------+-------------------------+ | user_id | name | mail | +---------+-----------+-------------------------+ | 1 | Winston | winston@leetcode.com | | 2 | Jonathan | jonathanisgreat | | 3 | Annabelle | bella-@leetcode.com | | 4 | Sally | sally.come@leetcode.com | | 5 | Marwan | quarz#2020@leetcode.com | | 6 | David | david69@gmail.com | | 7 | Shapiro | .shapo@leetcode.com | +---------+-----------+-------------------------+
Result table: +---------+-----------+-------------------------+ | user_id | name | mail | +---------+-----------+-------------------------+ | 1 | Winston | winston@leetcode.com | | 3 | Annabelle | bella-@leetcode.com | | 4 | Sally | sally.come@leetcode.com | +---------+-----------+-------------------------+ The mail of user 2 doesn't have a domain. The mail of user 5 has # sign which is not allowed. The mail of user 6 doesn't have leetcode domain. The mail of user 7 starts with a period.
select from users where mail regexp '^[A-Za-z][A-Za-z0-9_.-]@leetcode.com$'
Select all employee's name and bonus whose bonus is < 1000.
Table:Employee
+-------+--------+-----------+--------+ | empId | name | supervisor| salary | +-------+--------+-----------+--------+ | 1 | John | 3 | 1000 | | 2 | Dan | 3 | 2000 | | 3 | Brad | null | 4000 | | 4 | Thomas | 3 | 4000 | +-------+--------+-----------+--------+ empId is the primary key column for this table. Table: Bonus
+-------+-------+ | empId | bonus | +-------+-------+ | 2 | 500 | | 4 | 2000 | +-------+-------+ empId is the primary key column for this table. Example ouput:
+-------+-------+ | name | bonus | +-------+-------+ | John | null | | Dan | 500 | | Brad | null | +-------+-------+
select name, bonus from employee e left join bonus using(empid) where coalesce(bonus,0) <1000
Could you help Tim by writing a query to judge whether these three sides can form a triangle, assuming table triangle holds the length of the three sides x, y and z.
x | y | z |
---|---|---|
13 | 15 | 30 |
10 | 20 | 15 |
For the sample data above, your query should return the follow result:
x | y | z | triangle |
---|---|---|---|
13 | 15 | 30 | No |
10 | 20 | 15 | Yes |
select x, y, z, case when (x+y>z and y+z>x and z+x>y) then 'Yes' else 'No' end as triangle from triangle
Students table: +------------+--------------+ | student_id | student_name | +------------+--------------+ | 1 | Alice | | 2 | Bob | | 13 | John | | 6 | Alex | +------------+--------------+ Subjects table: +--------------+ | subject_name | +--------------+ | Math | | Physics | | Programming | +--------------+ Examinations table: +------------+--------------+ | student_id | subject_name | +------------+--------------+ | 1 | Math | | 1 | Physics | | 1 | Programming | | 2 | Programming | | 1 | Physics | | 1 | Math | | 13 | Math | | 13 | Programming | | 13 | Physics | | 2 | Math | | 1 | Math | +------------+--------------+ Result table: +------------+--------------+--------------+----------------+ | student_id | student_name | subject_name | attended_exams | +------------+--------------+--------------+----------------+ | 1 | Alice | Math | 3 | | 1 | Alice | Physics | 2 | | 1 | Alice | Programming | 1 | | 2 | Bob | Math | 1 | | 2 | Bob | Physics | 0 | | 2 | Bob | Programming | 1 | | 6 | Alex | Math | 0 | | 6 | Alex | Physics | 0 | | 6 | Alex | Programming | 0 | | 13 | John | Math | 1 | | 13 | John | Physics | 1 | | 13 | John | Programming | 1 | +------------+--------------+--------------+----------------+ The result table should contain all students and all subjects. Alice attended Math exam 3 times, Physics exam 2 times and Programming exam 1 time. Bob attended Math exam 1 time, Programming exam 1 time and didn't attend the Physics exam. Alex didn't attend any exam. John attended Math exam 1 time, Physics exam 1 time and Programming exam 1 time.
Write your MySQL query statement below
with ex as( select student_id, subject_name, count(subject_name) attended_exams from Examinations group by 1,2)
select s.student_id, s.student_name, ss.subject_name, coalesce(ex.attended_exams,0) attended_exams from students s cross join subjects ss left join ex on s.student_id = ex.student_id and ss.subject_name=ex.subject_name order by s.student_id, s.student_name, ss.subject_name