Think and solve SQL queries

In This Sheet We Would Cover Few SQL Queries And Just Think About Them

Query 1 Write a solution to find the IDs of the invalid tweets. The tweet is invalid if the number of characters used in the content of the tweet is strictly greater than 15. Return the result table in any order. +----------------+---------+ | Column Name | Type | +----------------+---------+ | tweet_id | int | | content | varchar | +----------------+---------+

Start thinking

1. You have to find id’s - “select statement”
2. You have to find invalid tweets, it needs filter - “where clause”
3. Special thing is you need to count characters (Hint - use sql function ) in the content column - each row would be part of filter
4. Got it ? Lets dive into writing query
5. There is no order on the final result

select tweet_id from tweets where length(content) > 15;

Query2 Customer Who Visited but Did Not Make Any Transactions Write a solution to find the IDs of the users who visited without making any transactions and the number of times they made these types of visits. Return the result table sorted in any order. +-------------+---------+
| Column Name | Type | +-------------+---------+ | visit_id | int | | customer_id | int | +-------------+---------+
+----------------+---------+ | Column Name | Type | +----------------+---------+ | transaction_id | int | | visit_id | int | | amount | int | +----------------+---------+

Start thinking

1. You have to find customers who visited but didnt make any transaction
2. Select id of those customers and number of times they visited
3. Left outer join on left table
4. Why join is on visit_id? Because we need to find those who visited without transaction
5. Group by - Make a point is needed because we want to count(*)
6. Got it ?

select customer_id from customer c left join transaction t on 
c.visit_id = t.visit_id where visit_id IS NULL
group by customer_id

Query 3 Write a solution to find managers with at least five direct reports. Return the result table in any order. +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | department | varchar | | managerId | int | +-------------+---------+ If managerId is null, then the employee does not have a manager. No employee will be the manager of themself

Start Thinking

1. This is a special query which has both manager and employee in the same table
2. This question requires you to find who all employees are manager
3. There is a filter condition
4. Got it?

select name from employee x join employee e ON x.managerId = e.managerId where x.managerId IS NOT NULL 
  group by x.managerId having count(*) >= 5

Query 4 Not Boring Movies Write a solution to report the movies with an odd-numbered ID and a description that is not “boring”.

Start thinking

1. There are 2 filters

select * from cinema
where description != 'boring' and id%2 = 1
order by rating desc