SQL Coding Flashcards

1
Q

Write an SQL query that retrieves the product names and their average price from a database containing two tables:

products:
product_id (INT, primary key)
product_name (VARCHAR(255))
category_id (INT, foreign key referencing categories.category_id)

prices:
price_id (INT, primary key)
product_id (INT, foreign key referencing products.product_id)
price (DECIMAL(10,2))

A

SELECT p.product_name, AVG(pr.price) AS average_price
FROM products p
INNER JOIN prices pr ON p.product_id = pr.product_id
GROUP BY p.product_id, p.product_name;

Explanation
Explanation - Super Simple Style
Imagine you’re at a huge party where products and prices are people who need to find their dance partners.

Finding the Dance Partners (JOIN): First, we need to make sure each product finds its matching prices. The INNER JOIN is like a dance instructor who ensures each product (from the products table) pairs up with its price (from the prices table) by matching their product_id. Only pairs with matching IDs get to dance.

Dancing in Groups (GROUP BY): Now, imagine each product can have multiple prices (like a product being on sale at different times for different prices). We want to know the average price for each product’s dance routine. To do this, we group all the prices for each product together using GROUP BY. It’s like grouping all the dance moves of a product into one performance to evaluate.

Calculating the Score (AVG()): With all the prices (dance moves) grouped by product, we then calculate the average score (price) of their performance using AVG(). This tells us how well the product performed on average, price-wise.

Announcing the Names (SELECT): Finally, we want to announce who’s dancing (the product_name) and their average score (the average_price). The SELECT statement lets us pick this information to be displayed.

Summary
In essence, this SQL query is like organizing a dance competition where products and their prices pair up, perform in groups, and then we calculate and announce the average score of their performances. By joining the two tables, grouping by product, and averaging the prices, we get a clear picture of each product’s average price. SQL lets us do this in a neat, efficient way, turning complex relationships into simple, understandable results.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

CASE: Create a SQL query where we are selecting a list of their names and their species group in two groups: 1) HUMAN and 2) ALIEN, how would you use the CASE statement to do it?

A

SELECT name, CASE WHEN species = ‘Human’ THEN ‘HUMAN’ ELSE ‘ALIEN’ END FROM characters;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What is involved with CASE

A

SELECT COLUM CASE WHEN THEN ELSE END FROM TABLE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is involved with GROUP BY

A

SELECT group_column, COUNT(col) FROM table GROUP BY group_columns;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

How does COALESCE WORK in this query? SELECT
CustomerName,
COALESCE(HomePhone, MobilePhone, BusinessPhone) as PhoneNumber

FROM Customers;

A

The COALESCE function then checks each of the given columns in turn and returns the first one that has a non-NULL value as “PhoneNumber”. If none do then it returns NULL.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What does COALESCE do

A

Returns first column with non-null value and can set a default value in the COALESCE function (like 0). Useful if we are doing averages.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

INNER JOIN: What does it do?

A

Combines rows from two or more tables based on a related column between them AND returns their columns FROM BOTH

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

HOW DO YOU MAKE THE INNER JOIN QUERY?

A

SELECT <> FROM <> JOIN <> ON

SELECT * FROM movies INNER JOIN actors ON movies.lead_actor_id = actors.actor_id

SELECT table1.col_interest, table2.col_interest
FROM table1
JOIN table2 ON table1.primary_key = table2.primary_key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

What is the left join?

A

Lets us join tables but insists on including all rows from the first table, EVEN if there is no match in the second table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

When would we use a left join?

A

You may not have ALL the values existing in the left table on the right but we still want to see all of the values on the left. E.G. SOME VALUES ARE GON BE NULL for example MARIA may not have an email address but we want to see CUSTNAME = MARIA EMAIL ADDRESS = NULL

E.g. Customers table on the left side and an emails table on the right. E.g. we dont have emails for all customers but we still want to see all of the customers in the output.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

How do you write a left join

A

SELECT * FROM LEFT_TABLE LEFT JOIN RIGHT_TABLE ON LEFT_TABLE.COL=RIGHT_TABLE.COL

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

MULTIPLE JOINS WITH INNER JOIN

A

SELECT
Customers.CustomerName,
Accounts.AccountID,
Accounts.AccountType

FROM Customers

INNER JOIN CustAccConn
ON Customers.CustomerID = CustAccConn.CustomerID

INNER JOIN Accounts
ON CustAccConn.AccountID = Accounts.AccountID;

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

num_offset

A

Where to begin counting the number of rows from

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Database normalization

A

Database normalization reduces data duplication within tables and facilitates independent data growth, improving database organization. However, it complicates queries and can introduce performance issues when handling multiple large tables, necessitating mastery in writing queries to efficiently retrieve comprehensive data across tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Do you need to explicitly say SELECT title.t1, col.t2, col.t2 for a join?

A

NOPE not if we are using a join (inner join) because the results will be there. Hell yeah!

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

When do we opt to not use an inner join?

A

When the resulting table we want may not have data in one of the other tables. Inner join only returns data that exists in BOTH tables. For example if customers table has all customers but email table only has SOME emails for the customers then inner join will only return the data that exists. See image

17
Q

What is the overall format of an INNER/LEFT/RIGHT/FULL join?

A

SELECT column, another_column, …
FROM mytable
INNER/LEFT/RIGHT/FULL JOIN another_table
ON mytable.id = another_table.matching_id
WHERE condition(s)
ORDER BY column, … ASC/DESC
LIMIT num_limit OFFSET num_offset;

18
Q

Why does this line of SQL work SELECT DISTINCT building_name, role but not SELECT building_name, DISTINCT(role)

A

The SQL syntax SELECT DISTINCT building_name, role works because DISTINCT applies to the entire set of columns listed after the SELECT keyword, treating the combination of building_name and role as a unique entity. It ensures that each combination of values in the building_name and role columns appears only once in the result set, effectively removing duplicate rows where both building_name and role are the same.

However, SELECT building_name, DISTINCT(role) is incorrect syntax in SQL because DISTINCT cannot be applied to individual columns in a comma-separated list following the SELECT keyword. SQL does not allow for the DISTINCT keyword to be used in the way that it’s applied to a single column while selecting other columns without it. The DISTINCT operation is intended to be applied to all columns being selected as a collective group to find unique rows, not to individual columns within a select statement.

19
Q
A