Table: Products
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| product_id | int |
| low_fats | enum |
| recyclable | enum |
+-------------+---------+
product_id is the primary key (column with unique values) for this table.
low_fats is an ENUM (category) of type ('Y', 'N') where 'Y' means this product is low fat and 'N' means it is
not.
recyclable is an ENUM (category) of types ('Y', 'N') where 'Y' means this product is recyclable and 'N' means
it is not.
Write a solution to find the ids of products that are both low fat and recyclable.
1. select product_id from Products where low_fats = 1 AND recyclable = 1;
2. select name
from customer
where referee_id != 2
or referee_id is null
3.
# Write your MySQL query statement below
select name, population, area from World
where area >=3000000 or population >= 25000000
4.
# Write your MySQL query statement below
select distinct viewer_id as id from Views
where viewer_id = author_id
order by viewer_id
5.
# Write your MySQL query statement below
select tweet_id
from Tweets
where length(content) > 15
order by tweet_id
6.
# Write your MySQL query statement below
select
ifnull(emu.unique_id, null) as unique_id , [Link]
from Employees em left join EmployeeUNI emu
on [Link] = [Link]
7.
# Write your MySQL query statement below
SELECT P.PRODUCT_NAME, [Link], [Link]
FROM SALES S, PRODUCT P
WHERE S.product_id = P.product_id
8.
# Write your MySQL query statement below
SELECT A.CUSTOMER_ID, COUNT(A.CUSTOMER_ID) AS count_no_trans FROM
(SELECT V.CUSTOMER_ID,V.VISIT_ID FROM Visits V
WHERE V.VISIT_ID NOT IN (SELECT T.VISIT_ID
FROM Transactions T)
)A
GROUP BY A.CUSTOMER_ID
9.
SELECT
distinct [Link] as Id
FROM Weather w1
JOIN Weather w2
ON DATEDIFF([Link], [Link])=1
AND [Link] > w1. Temperature
10.
SELECT s.machine_id,
ROUND(AVG([Link] - [Link]), 3) AS processing_time
FROM Activity s, Activity e
WHERE
s.machine_id = e.machine_id
AND s.process_id = e.process_id
AND s.activity_type = 'start'
AND e.activity_type = 'end'
GROUP BY s.machine_id;
11.
# Write your MySQL query statement below
SELECT [Link], [Link]
FROM
Employee A LEFT JOIN Bonus B
ON
[Link] = [Link]
WHERE
1=1
AND [Link] < 1000
OR [Link] NOT IN (SELECT DISTINCT EMPID FROM BONUS)
12.
SELECT student_id, student_name, subject_name, COUNT(e.student_id) AS attended_exams
FROM
Students
JOIN Subjects
LEFT JOIN Examinations AS e USING (student_id, subject_name)
GROUP BY 1, 3
ORDER BY 1, 3;
Or
select a.student_id,a.student_name , a.subject_name, count(e.student_id) as attended_exams
from
(select s2.student_id,s2.student_name, s1.subject_name
from
Subjects s1, Students s2) a left join Examinations e
on (e.student_id = a.student_id and a.subject_name = e.subject_name)
group by a.student_id, a.subject_name
order by a.student_id, a.subject_name
13.
SELECT name
FROM Employee
WHERE id in (
SELECT managerId
FROM Employee
GROUP BY managerId
HAVING COUNT(managerId) >= 5
14.
SELECT
s.user_id,
ROUND((SUM(IF([Link] = "confirmed", 1, 0)) / COUNT(*)), 2) AS confirmation_rate
FROM
Signups s
LEFT JOIN
Confirmations c ON s.user_id = c.user_id
GROUP BY
s.user_id;
15.
select c.*
from Cinema c
where
mod([Link],2) = 1
and [Link] != 'boring'
order by rating desc
16.
select p.product_id,
coalesce(
round((sum(price * units) / sum(units)), 2)
,0
as average_price
from Prices p
left join UnitsSold us on p.product_id = us.product_id and
us.purchase_date between p.start_date and p.end_date
group by p.product_id
17.
select p.project_id, round((sum(e.experience_years)/count(p.employee_id)), 2) average_years
from
Project p inner join Employee e
on e.employee_id = p.employee_id
group by p.project_id