SQL Coding Flashcards
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))
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.
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?
SELECT name, CASE WHEN species = ‘Human’ THEN ‘HUMAN’ ELSE ‘ALIEN’ END FROM characters;
What is involved with CASE
SELECT COLUM CASE WHEN THEN ELSE END FROM TABLE
What is involved with GROUP BY
SELECT group_column, COUNT(col) FROM table GROUP BY group_columns;
How does COALESCE WORK in this query? SELECT
CustomerName,
COALESCE(HomePhone, MobilePhone, BusinessPhone) as PhoneNumber
FROM Customers;
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.
What does COALESCE do
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.
INNER JOIN: What does it do?
Combines rows from two or more tables based on a related column between them AND returns their columns FROM BOTH
HOW DO YOU MAKE THE INNER JOIN QUERY?
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
What is the left join?
Lets us join tables but insists on including all rows from the first table, EVEN if there is no match in the second table.
When would we use a left join?
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 do you write a left join
SELECT * FROM LEFT_TABLE LEFT JOIN RIGHT_TABLE ON LEFT_TABLE.COL=RIGHT_TABLE.COL
MULTIPLE JOINS WITH INNER JOIN
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;
num_offset
Where to begin counting the number of rows from
Database normalization
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.
Do you need to explicitly say SELECT title.t1, col.t2, col.t2 for a join?
NOPE not if we are using a join (inner join) because the results will be there. Hell yeah!
When do we opt to not use an inner join?
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
