Lab 3 - SQL joins

Today’s lab will provide practice working with SQL clauses in DBeaver. Don’t forget the importance of the order of the SQL clauses.

The goals for lab 3 include:

• additional working with clauses in SQL.
• understanding each of SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT.
• practicing running SQL queries in a SQL client, DBeaver.
• JOINing tables

Advice for turning in the assignment

• Be sure to indicate (in the .sql file) which problem is being answered with which SQL code. Use the following syntax to comment within a .sql file: /* here is where comments go */. Indeed, feel free to copy the question into the .sql file so that you have it for your own records.

• save the .Rproj file somewhere you can find it. Don’t keep everything in your downloads folder. Maybe make a folder called SDS261 or something. That folder could live on your Desktop. Or maybe in your Dropbox.

• The SQL document should be saved in the R Project as lab3-sds261-yourlastname-yourfirstname.sql. You will have to navigate to the R Project to save the DBeaver file in the correct place.

• Connect to the yelp database, which contains the businesses, reviews, and users tables. See README file for connection details.

SHOW TABLES;

3 records

Tables_in_yelp
businesses
reviews
users

Assignment

1. For each of the three tables in the yelp database, identify the number of records and the variables. (That means, write at least a sentence for each table.)

2. Find the user with the most reviews. What is the person’s name, and how many reviews did they make?

3. What cities are represented in the businesses table? Find out by querying the number of businesses per city. Use LIMIT to start, just in case there are hundreds of cities. Write down a few of the city names. Do you notice anything interesting?

4. Find the businesses in “Carefree” (a city in Arizona). Write down the names of a few of the businesses.

5. Count the number of records resulting when the query connects the users with the reviews using a JOIN. How many records are there? What does that tell you about the two tables?

6. How many users wrote reviews in the reviews table but do not exist in the users table. Hint: use a RIGHT JOIN to keep all the reviews, and then look for the rows where the user_id from the users table IS NULL.

7. Write a query to ask a question that you think is interesting and uses at least one join. Provide both the question (in words) as well as the SQL code and results.

Reuse