1. Recyclable and Low-Fat Products

Retreive product IDs where both low_fats and recyclable are 'Y'.

SELECT product_id
FROM Products
WHERE low_fats = 'Y' AND recyclable = 'Y';

2. Find Customer Referees

Select customers whose referee is not ID 2, including those with no referee (NULL).

SELECT name
FROM customer
WHERE referee_id != 2 OR referee_id IS NULL;

Note: Direct comparison with NULL using = or != always yields unknown; use IS NULL instead.

3. Big Countries

A country is "big" if area ≥ 3,000,000 km² or population ≥ 25,000,000.

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

4. Article Views I

Find authors who viewed their own articles, return distinct author IDs sorted ascending.

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

5. Invalid Tweets

Identify tweets with content longer than 15 characters using CHAR_LENGTH().

SELECT tweet_id
FROM tweets
WHERE CHAR_LENGTH(content) > 15;

Use CHAR_LENGTH for character count; LENGTH returns bytes and may differ for multi-byte characters.

6. Replace Employee ID With Unique Identifier

List all employees with their unique IDs (if available) using a left join.

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

7. Product Sales Analysis I

Report product names along with sales year and price by joining from the Sales table.

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

Starting from Sales ansures only sold products appear in results.

8. Customers Who Visited but Did Not Make Any Transactions

Count visits per customer that had no associated transaction.

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

9. Rising Temperature

Find days where temperature was higher than the previous day using self-join.

SELECT w1.id
FROM Weather w1
JOIN Weather w2
  ON w1.recordDate = DATE_ADD(w2.recordDate, INTERVAL 1 DAY)
WHERE w1.temperature > w2.temperature;

Assumes MySQL’s DATE_ADD; adapt date function per DBMS.

10. Average Time of Process per Machine

Compute average process duration per machine by pairing 'start' and 'end' events.

SELECT
  start.machine_id,
  ROUND(AVG(end.timestamp - start.timestamp), 3) AS processing_time
FROM Activity start
JOIN Activity end
  ON start.machine_id = end.machine_id
  AND start.process_id = end.process_id
  AND start.activity_type = 'start'
  AND end.activity_type = 'end'
GROUP BY start.machine_id;

11. Employee Bonus

Return employees with bonus < 1000 or no bonus at all.

SELECT e.name, b.bonus
FROM Employee e
LEFT JOIN Bonus b ON e.EmpId = b.EmpId
WHERE b.bonus IS NULL OR b.bonus < 1000;

12. Students and Examinations

For every student-subject pair, report exam attendance count (0 if never attended).

SELECT
  s.student_id,
  s.student_name,
  sub.subject_name,
  IFNULL(exam_counts.attended_exams, 0) AS attended_exams
FROM Students s
CROSS JOIN Subjects sub
LEFT JOIN (
  SELECT student_id, subject_name, COUNT(*) AS attended_exams
  FROM Examinations
  GROUP BY student_id, subject_name
) exam_counts
  ON s.student_id = exam_counts.student_id
  AND sub.subject_name = exam_counts.subject_name
ORDER BY s.student_id, sub.subject_name;

13. Managers with at Least 5 Direct Reports

Identify managers having five or more direct reports.

SELECT m.name
FROM Employee m
JOIN Employee r ON m.id = r.managerId
GROUP BY m.id, m.name
HAVING COUNT(r.id) >= 5;

Alternative: First find qualifying manager IDs via grouping on managerId, then join to get names.

14. Confirmation Rate

Calculate confirmation rate per user as ratio of 'confirmed' actions, defaulting to 0.

SELECT
  s.user_id,
  ROUND(IFNULL(AVG(c.action = 'confirmed'), 0), 2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id;

Boolean expression inside AVG yields 1 for true, 0 for false.

15. Not Boring Movies

Select odd-ID movies excluding those with description 'boring', ordered by rating descending.

SELECT *
FROM cinema
WHERE MOD(id, 2) = 1 AND description != 'boring'
ORDER BY rating DESC;

16. Average Selling Price

Compute weighted average price per product based on units sold within valid price periods.

SELECT
  p.product_id,
  IFNULL(ROUND(SUM(p.price * u.units) / SUM(u.units), 2), 0) AS average_price
FROM Prices p
LEFT JOIN UnitsSold u
  ON p.product_id = u.product_id
  AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;

Handles products with no sales via IFNULL.

17. Project Employees I

Report average employee experience per project, rounded to two decimals.

SELECT
  pr.project_id,
  ROUND(AVG(emp.experience_years), 2) AS average_years
FROM Project pr
JOIN Employee emp ON pr.employee_id = emp.employee_id
GROUP BY pr.project_id;

Assumes all projects have assigned employees (inner join sufficient).

18. Percentgae of Users Attended in Each Contest

Compute registration percentage per contest relative to total users.

SELECT
  contest_id,
  ROUND(COUNT(user_id) * 100.0 / (SELECT COUNT(*) FROM Users), 2) AS percentage
FROM Register
GROUP BY contest_id
ORDER BY percentage DESC, contest_id;

Uses scalar subquery to get total user count.

19. Queries Quality and Percentage

For each query name, compute average quality (rating/position) and % of poor ratings (<3).

SELECT
  query_name,
  ROUND(AVG(rating / position), 2) AS quality,
  ROUND(SUM(CASE WHEN rating < 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS poor_query_percentage
FROM Queries
GROUP BY query_name;

CASE or IF can be used; CASE is standard SQL.