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
0 comments:
Post a Comment