SQL | Basics, but tricky scenarios, to refresh your knowledge.

 If you have lost SQL touch, considering the continuous transformation that is happening across software industry (Data Warehouse and Business Intelligence) with the introduction of self service BI tools and advanced ETL tools, then here are some basic SQL scenarios that will definitely brush-up your SQL. Consider answering them on your own before you look at the solution. You may definitely have a better approach for the solutions provided, I'm just sharing my solutions.

Disclaimer : Below questions are not my own creations but are the ones asked to me by SQL experts. Solutions are mine.

1. Consider below table, can you get the percentage of products that are both low fat and recyclable ?

/*
CREATE TABLE products (	product_id INT,
brand_name VARCHAR(100),
product_name VARCHAR (100),
is_low_fat_flg BIT,
is_recyclabl e_flg BIT
)


INSERT INTO products VALUES
 (1,NULL,'A',1,1),
 (1,NULL,'B',1,1),
 (1,NULL,'C',1,1),
 (1,NULL,'D',1,0),
 (1,NULL,'E',1,1),
 (1,NULL,'F',0,1),
 (1,NULL,'G',1,1),
 (1,NULL,'H',1,0),
 (1,NULL,'I',1,1),
 (1,NULL,'J',0,1),
 (1,NULL,'K',1,1),
 (1,NULL,'L',1,1),
 (1,NULL,'M',0,0),
 (1,NULL,'N',1,1),
 (1,NULL,'O',1,1),
 (1,NULL,'P',0,1),
 (1,NULL,'Q',1,1),
 (1,NULL,'R',1,1),
 (1,NULL,'S',1,0),
 (1,NULL,'T',0,1),
 (1,NULL,'U',1,1),
 (1,NULL,'V',1,1),
 (1,NULL,'W',1,1),
 (1,NULL,'X',0,0),
 (1,NULL,'Y',1,1),
 (1,NULL,'Z',1,1)
 */
--  SQL
 SELECT CAST(SUM(IIF(is_low_fat_flg = 1 and is_recyclable_flg = 1,1,NULL)) AS FLOAT) / COUNT(*)
 FROM products

2. Consider below the table, get the difference of sales of apple and orange at date level.

 /*
 CREATE TABLE fruit_sales (dateid DATE, fruit VARCHAR(50), sold DECIMAL)

 INSERT INTO fruit_sales VALUES
 ('2015-01-01'  ,'Apple' ,31 ),
 ('2015-01-01'  ,'Orange'  ,  19 ),
 ('2015-01-02'  ,'Apple'   ,  37 ),
 ('2015-01-02'  ,'Orange'  ,  26 ),
 ('2015-01-03'  ,'Apple'   ,  21 ),
 ('2015-01-03'  ,'Orange'  ,  21 ),
 ('2015-01-04'  ,'Apple'  ,  35 ), 
 ('2015-01-04'  ,'Orange'  ,  27)

 */

-- SQL

 SELECT dateid, ABS(SUM(IIF(fruit = 'Apple',sold,NULL)) - SUM(IIF(fruit = 'Orange',sold,NULL)))
 FROM fruit_sales
 GROUP BY dateid

3. Consider below the table, write a query that shows the US and France sales, by date. Grain is 1 row/day.

 /*
 CREATE TABLE country_sales(dateid   DATE, country VARCHAR(2), amt DECIMAL )
 INSERT INTO country_sales VALUES
( '2013-01-01' , 'US' ,         5 ) ,
( '2013-01-02', 'US' ,        8  ),
( '2013-01-03' , 'US' ,        10  ),
( '2013-01-01' , 'FR' ,         6  ),
( '2013-01-02' , 'FR' ,         6  ),
( '2013-01-05' , 'FR' ,         7  ),
( '2013-01-08' , 'GB' ,        10  ),
( '2013-01-01' , 'FB' ,         5  ),
( '2013-01-06' , 'JP' ,         8  ),
( '2013-01-07' , 'JP' ,         8 )
*/

-- SQL
SELECT dateid,country,SUM(amt)
FROM country_sales
WHERE country IN ('US','FR')
GROUP BY dateid, country

-- check what happens if you use the below code

SELECT dateid,SUM(IIF(country = 'US',amt,NULL)),SUM(IIF(country = 'FR',amt,NULL))
FROM country_sales
WHERE country IN ('US','FR')
GROUP BY dateid

4. Consider below the table, write a query to get the students who have borrowed at least 5 books.

/*
CREATE TABLE Students(Stid INT, Sname VARCHAR(50))
CREATE TABLE BooksBorrowed(Bid INT,Stid INT, BookName VARCHAR(100))

INSERT INTO Students VALUES
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'E'),
(6,'F'),
(7,'G'),
(8,'H'),
(9,'I'),
(10,'J'),
(11,'K'),
(12,'L'),
(13,'M'),
(14,'N'),
(15,'O'),
(16,'P'),
(17,'Q'),
(18,'R'),
(19,'S'),
(20,'T'),
(21,'U'),
(22,'V'),
(23,'W'),
(24,'X'),
(25,'Y'),
(26,'Z')



INSERT INTO BooksBorrowed VALUES
(1,1,'Book1'),
(2,3,'Book2'),
(3,5,'Book3'),
(4,7,'Book4'),
(5,9,'Book5'),
(6,11,'Book6'),
(7,1,'Book7'),
(8,3,'Book8'),
(9,7,'Book9'),
(10,9,'Book9'),
(11,11,'Book8'),
(12,2,'Book7'),
(13,4,'Book6'),
(14,6,'Book5'),
(15,1,'Book4'),
(16,1,'Book3'),
(17,3,'Book2'),
(18,3,'Book1'),
(19,3,'Book5'),
(20,9,'Book6'),
(21,9,'Book7'),
(22,9,'Book8'),
(23,12,'Book9'),
(24,14,'Book1'),
(25,16,'Book2'),
(26,18,'Book3'),
(27,18,'Book4'),
(28,12,'Book6'),
(29,14,'Book8'),
(30,14,'Book4'),
(31,16,'Book3'),
(32,18,'Book7'),
(33,14,'Book3'),
(34,12,'Book6'),
(35,12,'Book7'),
(36,16,'Book8'),
(37,16,'Book2'),
(38,16,'Book1')
*/

-- SQL

SELECT S.Sname,B.*
FROM Students S INNER JOIN
(SELECT Stid,count(bid) TotalBooksBorowed
FROM BooksBorrowed
GROUP BY Stid
HAVING COUNT(*)>=5) B
	ON S.Stid = B.Stid

5. Use the above schema and get the students who have barrowed no books

SELECT S.stid,S.Sname,B.stid
FROM Students S 
LEFT JOIN BooksBorrowed B
ON S.Stid = B.Stid
WHERE B.Stid IS NULL

6. Use the above code and get the students who have borrowed least number of books. I have not provided solution for this question.

7. Consider the below code and Get the average sales by given month

/*
CREATE TABLE Items (ItemId VARCHAR(10),StartDate DATE,EndDate DATE,Price DECIMAL)
INSERT INTO Items VALUES
('mobile','2020-01-01','2020-01-15',100),
('mobile','2020-01-16','2020-01-20',150)

CREATE TABLE Sales(Saleid INT,ItemId VARCHAR(10),PurchaseDate DATE,Quantity INT)
INSERT INTO Sales VALUES
(100,'mobile','2020-01-05',100),
(101,'mobile','2020-01-17',200)

SELECT * FROM Items
SELECT * FROM Sales
*/

--SQL
--  Get the average sales by given month

SELECT MONTH(PurchaseDate), SUM(S.quantity * I.Price)/COUNT(*) TotalAmount
FROM Sales S
INNER JOIN Items I 
	ON S.ItemId = I.ItemId AND PurchaseDate BETWEEN I.StartDate AND I.EndDate
GROUP BY MONTH(PurchaseDate)

Please do leave comments below, if you see this post as helpful or if there are any issues with the solutions provided.