Database Queries and Normalization
Added on 2019-09-23
8 Pages834 Words188 Views
|
|
|
Part A: Entity Relationship Diagram:Part B: Normalisation:We insert the data in undeclared sequence then we apply the normalization on table and we got the result with sequence.
Part C: Relational schema and MySQL database:Part D: SQL1)List the full name of Patrons, phone number and address in the descending order of customer number. The full name is comprised of first and last name joined with single space.Use the alias Customer full name for the composed column heading.Answer: selectPatrons.Customer_Name,Patrons.Customer_Mobile,Patrons.Customer_addressfrom PatronsorderbyCustomer_numberdescCustomer_NameCustomer_MobileCustomer_addressMili7894654235rd stage NY 54893arhayn9870564562rd stage NY 54894Shaym4569879873rd stage NY 54895
2)List all the products having price less than $100Answer:select*from SaleswhereProduct_Price< 100Product_numberProfuct_nameProduct_brandProduct_descriptionProduct_PriceProduct_Store_number9875dressramnondGood Quality dress9913)List the product number, product title, quantity ordered, unit price and total amount per product (unit price * quantity ordered) for order having order number 1005.Answer:selectsales.Product_number,Sales.Profuct_name,orders.Quantity,Sales.Product_Price,Sales.Product_Price*orders.Quantityas [Total amount per product] fromordersleftjoin Sales onorders.Product_number=Sales.Product_numberProduct_numberProfuct_nameQuantityProduct_PriceTotal amount per product9877makeup31504509875dress199999876Watch51999959874Iphone 6s2299598
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents