Sql

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.

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.

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.

Select *

From Customers;

Select Items_in_cart

From Customers

Where Customer_Name = "John Smith";

Select SUM(Cash_spent_to_Date) as SUM_CASH

From Customers;

Select count(1) as Number_of_People_w_items

From Customers

where Items_in_cart > 0;

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

Select c.Customer_Name, o.Item

From Customers c

Left Join Orders o

On c.Customer_ID = o.Customer_ID;

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.

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.