Scribd
Upload
508 views

Assignment 06

Uploaded by

Ritika Awasthi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
508 views

Assignment 06

Uploaded by

Ritika Awasthi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 2

Assignment:- 05

Joins

Instructions:
Please share your answers filled in line in the Word document. Submit
code separately wherever applicable.

Please ensure you update all the details:


Name: _____________ Batch ID: ___________
Topic: Introduction to Database

Joins
1.Run the below query to create the datasets.
a. /*retrieve sales table from the supermart_db (sales dataset contains
multiple years data)*/

b. /* Counting the number of distinct customer_id values in sales table */

c. /* Customers with ages between 20 and 60 */


● create table customer_20_60 as select * from customers where
ages between 20 and 60;
● select count (*) from customer_20_60;

2.Find the total sales that are done in every state for customer_20_60 and the
sales table
Hint: Use Joins and Group By command

3.Get data containing Product_id, Product name, category, total sales value of
that product, and total quantity sold. (Use sales and product tables)

1)
a)
SELECT * FROM Supermart_DB.sales;

b)

© 360DigiTMG. All Rights Reserved.


Assignment:- 05
Joins
SELECT COUNT(DISTINCT customer_id) AS num_customers FROM
Supermart_DB.sales;

c)
CREATE TABLE customer_20_60 AS
SELECT * FROM Supermart_DB.customers
WHERE age BETWEEN 20 AND 60;
SELECT COUNT(*) FROM customer_20_60;

2)
SELECT s.state, SUM(s.sales) AS total_sales
FROM Supermart_DB.sales s
INNER JOIN customer_20_60 c ON s.customer_id = c.customer_id
GROUP BY s.state;

3)
SELECT
p.product_id,
p.product_name,
p.category,
SUM(s.sales) AS total_sales,
SUM(s.quantity) AS total_quantity_sold
FROM
Supermart_DB.sales s
INNER JOIN
Supermart_DB.products p ON s.product_id = p.product_id
GROUP BY
p.product_id, p.product_name, p.category;

© 360DigiTMG. All Rights Reserved.

You might also like