Oct 9, 2020

SQL Queries From Beginner's To Advanced

BASIC

Question 1: Write SQL queries for the following: Create a table with top 5 cards for each day based on the amount spent (use the table provided as reference)

Top 5 cards for on the amount spent
Top cards on ‘16-09-2018’
Top 5 cards for each day based on the amount spent

(Note: All parts of the above question need to be done using windowing functions)

Question 2: Provide the outputs of LEFT (Keep table X on Left side), INNER & FULL OUTER joins for the following two tables

(NOTE:The outputs should have all three columns; Col1, Col2 & Col3)?

Question 3:

What will be the output of following Query?

SELECT CASE WHEN null=null THEN ‘Milk’ Else ‘Egg’ END from DUAL;

Question 4 : Consider the single column table below.

Answer the following questions:

1. What is the possible data type of the column ‘COL1’?

2. What will the output of the following SQL statements be?

‘SELECT COUNT(*) AS ENTRIES FROM TABLE;’
‘SELECT COUNT(COL1) AS ENTRIES FROM TABLE;’
‘SELECT COUNT(DISTINCT COL1) AS ENTRIES FROM TABLE;’

Write a query to fetch rows with values ranging from 0 to 2 (assuming the data type to be same as mentioned above)

Question 5: Consider the below table:

1. Find out the number of orders booked and their total purchased amount for each day. The output should be in the below format:

"For 2001-10-10 there are 15 orders and total amount is Rs.100"

2. Find the total purchase amount at a customer salesman level and rank the salesmen based on the total sales done

3. Find the orders with all the field in such a manner that, the oldest order date will come first and the highest purchase amount of same day will come first.

Question 6: Write a query in SQL to obtain the name of the physicians who are the head of each department. Use the below two tables.

Sample table: department

INTERMEDIATE

Question 1: Write a SQL query to convert Table T1 into Table T2

Question 2: Write a query to find cumulative sum of amount for every customer, without using windowing function

Question 3:

Using the data available below, write a SQL query to calculate the balance in a card as of the end of each fiscal quarter (FQ)? In case the card has not done any transaction in a particular quarter, the balance on the card is the same as the balance in the previous quarter.

Data available

Question 4:

Write a SQL query to find the products that contribute to the TOP 80% of the sales.

Tip: In the example below, if by combining say 4 products we achieve 78% of the sales and by including the next one we reach 82%, in that case we would want to include the 5th product so that we cross the 80% cut off. (Please write a generic query, not specific to this particular example).

Part 3 Advanced Level

Question 1:

Below is a sample dataset showing TV sessions of each TV set of each household. The timestamps have been converted to integer values for ease of operation.

Household “111” switch on their TV “1” at 500 and switch it off at 570. However, this has been captured in the data as 2 separate rows. You will have to write a query to convert this into a single row. Similar modification needs to be made to all other subsequent occurrences. Please note that a single valid TV session can be split into more than 2 rows as well (As shown by rows 5-8).

Also note that this is just a sample data.

Question 2:

For every customer, calculate the average visit frequency and average basket size - 1 month after registration, 2 months after registration, 3 months after registration and so on for a maximum of 24

months of tenure. Visit frequency at every monthly checkpoint to be calculated based on past 3 months of data, and basket size at every monthly checkpoint to be calculated based on past 3 purchases.

Question 3:

Input:

As you can see, there are duplicate combinations, ie AB and BA. We need to remove these duplicates and retain only one combination, ie either AB or BA.

Doesn’t matter which one you choose to retain. The output should look something like this: