[go: up one dir, main page]
Scribd
Upload
7 views5 pages

Practical 12 CC

The document outlines a practical task involving a customer_sales database schema for a general store, including tables for Customer, Item, and Sales with specified attributes. It details tasks such as creating tables with integrity constraints, inserting records, and querying for sales details, customer purchases, and bills. The solution includes commands for database creation, data insertion, and various select queries to retrieve specific information.

Uploaded by

amanjeenwal04
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
7 views5 pages

Practical 12 CC

The document outlines a practical task involving a customer_sales database schema for a general store, including tables for Customer, Item, and Sales with specified attributes. It details tasks such as creating tables with integrity constraints, inserting records, and querying for sales details, customer purchases, and bills. The solution includes commands for database creation, data insertion, and various select queries to retrieve specific information.

Uploaded by

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

PRACTICAL 12

QUESTION:- Consider the following database schema for a customer_sales scenario of a


general store:
• Customer
o customer_id (INT)
o customer_name (VARCHAR)
• Item
o item_id (INT)
o name (VARCHAR)
o price (FLOAT)
• Sales
o bill_no (INT)
o bill_date (DATE)
o customer_id (INT)
o item_id (INT)
o quantity_sold (INT)

And perform following Tasks:


1. Create the tables with appropriate integrity constraints (such as primary keys and
foreign keys).
2. Insert 10 records into each of the three tables (Customer, Item, and Sales).
3. List the total bill details showing:
i. Quantity sold
ii. Price of each item
iii. Final amount (quantity × price)
4. List the details of customers who have purchased items priced greater than 200.
5. Count how many products have been bought by each customer.
6. List all products bought by the customer with customer_id = 5.
7. List all bills for the current date, including:
i. Customer names
ii. Item numbers

SOLUTION:-
1. Create the tables with appropriate integrity constraints (such as primary keys and
foreign keys).

i. Create database customer_sales and run USE command to use customer_sales


database.
ii. Create tables using CREATE command.

2. Insert 10 records into each of the three tables (Customer, Item, and Sales).

iii. Insert data in tables.


iv. Run select command to see table.

➔ customer table

➔ item table

➔ sales table
3. List the total bill details showing:
i. Quantity sold
ii. Price of each item
iii. Final amount (quantity × price)

4. List the details of customers who have purchased items priced greater than 200.
5. Count how many products have been bought by each customer.

6. List all products bought by the customer with customer_id = 5.

7. List all bills for the current date, including:


a. Customer names
b. Item numbers

You might also like