Cacher is the code snippet organizer for pro developers

CREATE TABLE PRODUCTS
(
       PRODUCT_ID     INTEGER,
       PRODUCT_NAME   VARCHAR(30)
);
CREATE TABLE SALES
(
       SALE_ID        INTEGER,
       PRODUCT_ID     INTEGER,
       DATE           TIMESTAMP,
       Quantity       INTEGER,
       PRICE          INTEGER
);       
INSERT INTO PRODUCTS VALUES ( 100, 'Nokia');
INSERT INTO PRODUCTS VALUES ( 200, 'IPhone');
INSERT INTO PRODUCTS VALUES ( 300, 'Samsung');
INSERT INTO PRODUCTS VALUES ( 400, 'LG');
INSERT INTO SALES VALUES ( 1, 100, '2015-10-01 00:00:00', 25, 5000);
INSERT INTO SALES VALUES ( 2, 100, '2015-11-01 00:00:00', 16, 5000);
INSERT INTO SALES VALUES ( 3, 100, '2016-12-01 00:00:00', 8,  5000);
INSERT INTO SALES VALUES ( 4, 200, '2016-11-01 00:00:00', 10, 9000);
INSERT INTO SALES VALUES ( 5, 200, '2017-10-01 00:00:00', 15, 9000);
INSERT INTO SALES VALUES ( 6, 200, '2017-11-01 00:00:00', 20, 9000);
INSERT INTO SALES VALUES ( 7, 300, '2015-12-01 00:00:00', 20, 7000);
INSERT INTO SALES VALUES ( 8, 300, '2015-11-01 00:00:00', 18, 7000);
INSERT INTO SALES VALUES ( 9, 300, '2016-10-01 00:00:00', 20, 7000);
COMMIT;
USE dbo;     -- or your DB Name
SELECT * FROM SALES;
SELECT * FROM SALES LIMIT 5;
SELECT Sale_ID, Product_ID, Quantity FROM SALES;
-- Get top 5 details of only Sale_ID, Product, Order_Quantity from Sales 
-- with exactly those field-names in the results:
SELECT
  Sale_ID
  , Product_ID as 'Product'
  , Quantity as 'Order_Quantity'
FROM SALES
LIMIT 5;
SELECT DISTINCT Product_ID FROM SALES;
Inner Join: An inner join is a join that returns only those results from both tables where the join condition is True.
-- Get details of those Products that have made Sales in 2017:

SELECT Sale_ID, Date, Quantity, Price, p.Product_Name
FROM SALES s INNER JOIN PRODUCTS p
ON s.Product_ID=p.Product_ID
AND YEAR(Date) = '2017'

2. Outer Join: An outer join is a join that returns all the results from one or both the tables (depending upon the join type — left, right, or full), and where the rows failing the join condition return NULL values.
-- Get details of the all Products regardless of whether they have made Sales:

SELECT p.Product_Name, Sale_ID, Date, Quantity, Price
FROM PRODUCTS p LEFT JOIN SALES s 
ON p.Product_ID=s.Product_ID
-- Get all Sales details for the Product- 'iPhone' 
-- (Here, we know the Product_ID)

SELECT * FROM SALES
WHERE Product_ID = 200;
-- Get sales of a product that ends with '-Phone' 
-- (Here, we aren't sure of the Product_ID or the text casing)

SELECT s.*, p.* FROM SALES s
INNER JOIN PRODUCTS p ON s.Product_ID = p.Product_ID
WHERE p.Product_Name LIKE '%Phone'
-- get sales details between a date range

SELECT * FROM SALES
WHERE Date >= '2015-11-01' AND Date <= '2017-10-01'
ORDER BY Date;
SELECT * FROM SALES
WHERE Date BETWEEN '2015-11-01' AND '2017-10-01'
ORDER BY Date;
-- get the Sales details for the products —  ‘Nokia’, ‘Samsung’, and ‘LG’
SELECT s.*, p.* FROM SALES s
INNER JOIN PRODUCTS p ON s.Product_ID = p.Product_ID
WHERE p.Product_Name IN ('Nokia','Samsung','LG')
-- Aggregation on all rows
select count(*) Total_Rows, sum(price) Total_Sales, avg(price) Avg_Price
, min(price) Min_Price, max(price) Max_Price
FROM SALES;
-- Get the total sales, average price and total quantity sold for each product in our database

SELECT p.Product_Name,
COALESCE(SUM(price),0) Total_Sales
,COALESCE(AVG(price),0) Avg_Price
,COALESCE(SUM(Quantity),0) Total_Quantity
FROM PRODUCTS p LEFT JOIN SALES s
ON p.Product_ID=s.Product_ID
GROUP BY p.Product_Name;
------
Let’s consider another example — For every product sold from 1st October 2016 onward, get the total sales, average price and total quantity on a yearly basis, and return only those products that have sold at least 10 items, sorted by product name and year.
------
-- Step 1: For every product sold from 1st October 2016 onward

select p.Product_Name
FROM PRODUCTS p LEFT JOIN SALES s
ON p.Product_ID=s.Product_ID
WHERE s.Date >= '2016-10-01'    -- date filter
GROUP BY p.Product_Name;        -- for each product
---------
-- Step 2: get the total sales, average price and total quantity on a yearly basis

-- MySQL
select p.Product_Name
,COALESCE(YEAR(s.Date),'None') Year
,COALESCE(SUM(price),0) Total_Sales
,COALESCE(AVG(price),0) Avg_Price
,COALESCE(SUM(Quantity),0) Total_Quantity
FROM PRODUCTS p LEFT JOIN SALES s
ON p.Product_ID=s.Product_ID
WHERE s.Date >= '2016-10-01'
GROUP BY p.Product_Name, YEAR(s.Date); -- For each product, and each year
------------
-- Step 3: return only those products that have sold at least 10 items, sorted by product name and year.

-- MySQL
select p.Product_Name
,COALESCE(YEAR(s.Date),'None') Year
,COALESCE(SUM(price),0) Total_Sales
,COALESCE(AVG(price),0) Avg_Price
,COALESCE(SUM(Quantity),0) Total_Quantity
FROM PRODUCTS p LEFT JOIN SALES s
ON p.Product_ID=s.Product_ID
WHERE s.Date >= '2016-10-01'
GROUP BY p.Product_Name, YEAR(s.Date)
HAVING Total_Quantity>=10    -- final filter on quantity
ORDER BY Product_Name, Year;
====
-- get Sales details on quarterly basis

-- MySQL
SELECT
    CASE
        WHEN MONTH(Date) BETWEEN 1  AND 3  THEN CONCAT(YEAR(Date),'Q1')
        WHEN MONTH(Date) BETWEEN 4  AND 6  THEN CONCAT(YEAR(Date),'Q2')
        WHEN MONTH(Date) BETWEEN 7  AND 9  THEN CONCAT(YEAR(Date),'Q3')
        WHEN MONTH(Date) BETWEEN 10 AND 12 THEN CONCAT(YEAR(Date),'Q4')
    END AS Quarter
    , CAST(SUM(Price) AS DECIMAL(10,2)) Total_Sales
    , COUNT(1) Num_of_Sales
FROM SALES
GROUP BY Quarter
ORDER BY Quarter
-------
-- get Sales details on monthly basis

-- MySQL
SELECT YEAR(DATE) Year
  , MONTHNAME(DATE) Month
  , CAST(SUM(Price) As Decimal (10,2)) Total_Sales
  , COUNT(1) AS Num_of_Sales
FROM SALES
GROUP BY Year(Date), Month(Date)
ORDER BY Year(Date) DESC, Month(Date) DESC