Sql
Work through every question currently mapped to this canonical topic.
- Comparisons vs. Functions
- Improve the following query
SELECT COUNT(purchased_at) FROM shawarma_purchases WHERE purchased_at BETWEEN '2017-01-01' AND '2017-12-31';Answer
SELECT count(*) FROM shawarma_purchases WHERE purchased_at >= '2017-01-01' AND purchased_at <= '2017-31-12'
SQL Self Assessment 11 questions
- What is SQL?
Answer
SQL (Structured Query Language) is a standard language for relational databases (like MySQL, MariaDB, ...).
It's used for reading, updating, removing and creating data in a relational database.
- How is SQL Different from NoSQL
Answer
The main difference is that SQL databases are structured (data is stored in the form of tables with rows and columns - like an excel spreadsheet table) while NoSQL is unstructured, and the data storage can vary depending on how the NoSQL DB is set up, such as key-value pair, document-oriented, etc.
- When is it best to use SQL? NoSQL?
Answer
SQL - Best used when data integrity is crucial. SQL is typically implemented with many businesses and areas within the finance field due to it's ACID compliance.
NoSQL - Great if you need to scale things quickly. NoSQL was designed with web applications in mind, so it works great if you need to quickly spread the same information around to multiple servers
Additionally, since NoSQL does not adhere to the strict table with columns and rows structure that Relational Databases require, you can store different data types together.
- How would I select all fields from this table?
Answer
Select *
From Customers;
- How many items are in John's cart?
Answer
Select Items_in_cart
From Customers
Where Customer_Name = "John Smith";
- What is the sum of all the cash spent across all customers?
Answer
Select SUM(Cash_spent_to_Date) as SUM_CASH
From Customers;
- How many people have items in their cart?
Answer
Select count(1) as Number_of_People_w_items
From Customers
where Items_in_cart > 0;
- How would you join the customer table to the order table?
Answer
You would join them on the unique key. In this case, the unique key is Customer_ID in both the Customers table and Orders table
- How would you show which customer ordered which items?
Answer
Select c.Customer_Name, o.Item
From Customers c
Left Join Orders o
On c.Customer_ID = o.Customer_ID;
- Using a with statement, how would you show who ordered cat food, and the total amount of money spent?
Answer
with cat_food as (
Select Customer_ID, SUM(Price) as TOTAL_PRICE
From Orders
Where Item like "%Cat Food%"
Group by Customer_ID
)
Select Customer_name, TOTAL_PRICE
From Customers c
Inner JOIN cat_food f
ON c.Customer_ID = f.Customer_ID
where c.Customer_ID in (Select Customer_ID from cat_food);
Although this was a simple statement, the "with" clause really shines when a complex query needs to be run on a table before joining to another. With statements are nice, because you create a pseudo temp when running your query, instead of creating a whole new table.
The Sum of all the purchases of cat food weren't readily available, so we used a with statement to create the pseudo table to retrieve the sum of the prices spent by each customer, then join the table normally.
- Which of the following queries would you use?
SELECT count(*) SELECT count(*) FROM shawarma_purchases FROM shawarma_purchases WHERE vs. WHERE YEAR(purchased_at) == '2017' purchased_at >= '2017-01-01' AND purchased_at <= '2017-31-12'Answer
SELECT count(*) FROM shawarma_purchases WHERE purchased_at >= '2017-01-01' AND purchased_at <= '2017-31-12'When you use a function (
YEAR(purchased_at)) it has to scan the whole database as opposed to using indexes and basically the column as it is, in its natural state.