Write the following queries in SQL uisng the Yelp_db schema The Yelp_db schema c
ID: 3754971 • Letter: W
Question
Write the following queries in SQL uisng the Yelp_db schema
The Yelp_db schema can be found at the following link:
https://www.yelp.com/dataset/documentation/main
4.1 Find business names whose zip code begins with '1' and ends with '1'. You can assume that zip code consists of five digits. The result should be following.
4.2 Find the total number of checkin per business, which is located in 'NY' state. Sort by the total checin number. Note that the total number of checkin must be ZERO if there is no checkin (Hint: check the mysql function 'IFNULL'). The result should be the following:
4.3 Find the total number of restaurants per (city and state). Sort the numbers. It is not necessary to display all results. The results should be the following:
postal_code name Ulta Beautv Nail Secrets Nine Stories 15241 15101 15201 Industrv Public House 15201 15201 15201 15221 15101 15201 Artisan Pizza Comoanv 15241 15201 NTB-National Tire &. 15101 15201 15201 Canterburv Place Healthv Ride Pah New Luckv Alpine Pools 31st Street Pub Paint Monkev Sent business 78 ×Explanation / Answer
If you have any doubts, please give me comment...
-- 4.1)
SELECT name, postal_code
FROM business
WHERE postal_code LIKE '1%1';
-- 4.2)
SELECT name, COUNT(c.business_id) checknum
FROM business b LEFT OUTER JOIN checkin c ON b.business_id = c.business_id
WHERE state='NY'
GROUP BY business_id, name
ORDER BY checknum;
-- 4.3)
SELECT city, state, COUNT(*)
FROM businees
GROUP BY city, state;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.