Database Lab 04





Download All  Questions And SQL Files Import SQL To Your Database

Download Sql

Problem title: Design database schema and populate the database with appropriate datasets.

Believe you have got many resources and confident about the SQL and database management system already. Please provide enough efforts for the solution of the following problems.

Customer(Cust id : integer, cust_name: string)
Item(item_id: integer, item_name: string, price: integer)
Sale(bill_no: integer, bill_date: date, qty_sold: integer)

For the above schema, perform the following—
1. Create the tables with the appropriate integrity constraints, insert around 10 records in each of the tables
2. Design the relationship set table to associate the relationship among the given entity set.
3. Give a count of how many products have been bought by each customer.
4. List the total Bill details with the quantity sold, price of the item and the final amount
5. List Customer's Final Item Quantity And Final Bill.


Query 3:

SELECT customer.cust_id, cust_name, SUM(qt) AS total FROM customer,relationship
WHERE
customer.cust_id=relationship.cust_id

group by relationship.cust_id



Query 4:

SELECT  item_name,item.item_id, price, SUM(qt) AS Total_qt, SUM((qt)*price) AS FINAL FROM customer,item,relationship WHERE
customer.cust_id=relationship.cust_id AND
item.item_id=relationship.item_id

group by relationship.item_id

Query 5:

SELECT cust_name, SUM(qt) As Quantity, SUM((qt)*price) AS Final_Bill FROM customer,item,relationship WHERE
customer.cust_id=relationship.cust_id AND
item.item_id=relationship.item_id 

GROUP BY relationship.cust_id





Download Coding Interview Book and Get More Tutorials for Coding and Interview Solution: Click Here

Download System Design Interview Book and Get More Tutorials and Interview Solution: Click Here

Do you need more Guidance or Help? Then Book 1:1 Quick Call with Me: Click Here

Share on Google Plus

About Ashadullah Shawon

I am Ashadullah Shawon. I am a Software Engineer. I studied Computer Science and Engineering (CSE) at RUET. I Like To Share Knowledge. Learn More: Click Here
    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment