Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

(a) Consider a 'Customer Order processing' database with three tables as given below 1. Customer - captures customer details cust_no | cust_name 0001 John C002

image text in transcribed
image text in transcribed
(a) Consider a 'Customer Order processing' database with three tables as given below 1. Customer - captures customer details cust_no | cust_name 0001 John C002 Joseph C003 Peter C004 Pan 2. Item - captures item details item_no item_name 1 item_price 10001 Shoes 120.00 10002 Umbrella 100.50 10003 Table 50.00 10004 TV 1000.80 10005 Fan 501.10 3. Orders - captures order details of the items bought by customer order_no order_date cust_no item_nono_of_units_bought 1001 2019-09-08 C002 10001 1002 2019-01-09 COO2 10003 1003 2019-01-09 C002 10004 1004 2019-01-09 C003 10004 1005 2019-05-02 c003 10001 1006 2019-01-03 C003 10003 1007 2019-01-01 C004 10001 1008 2019-11-11 C004 10002 1009 2019-11-11 C004 10004 1010 2019-01-09 0004 10003 Based on the tables given above, answer the following questions i) Write SQL syntax to 1) Create table "Orders' with all necessary constraints. 2) To make a copy of the 'Orders' table. ii) Write an SQL syntax to 1) Modify the table 'customer' to add an attribute 'cust_contact_number'. 2) Also write an SQL syntax to rename the attribute 'cust_contact_number' to 'cust_cnt' for the customer' table. 3) Write an SQL statement to update the field cust_cnt with appropriate date for customer 'Peter'. iii) Write an SQL statement which shows the items bought by customer Joseph' [item name and the ordered date) as given below ITEMS | DATEOFORDER Shoes 2019-09-08 Table 2019-01-09 TV 2019-01-09 iv) Write a Stored Procedure to show the details of the item not bought by any customer (use the item and orders table] V) Write an SQL statement that shows the customer names and the respective items bought by them as given below Name Joseph Peter Pan Items Shoes , Table, TV Shoes, Table, TV Table, shoes, Umbrella, TV vi) Write an SQL statement to show the customer names of the customers who bought 'shoes' in a horizontal manner. vii) Write an SQL statement to 1) create a view to show total number of customers. 2) find the total number of items bought by each customer as shown below Customer | Unitsbought Joseph 8 Peter 8 Pan 10 (7*3=21 Marks) Identify the 'surrogate key in the table 'orders' and define it. (4 Marks) (b)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Machine Learning And Knowledge Discovery In Databases European Conference Ecml Pkdd 2018 Dublin Ireland September 10 14 2018 Proceedings Part 1 Lnai 11051

Authors: Michele Berlingerio ,Francesco Bonchi ,Thomas Gartner ,Neil Hurley ,Georgiana Ifrim

1st Edition

3030109240, 978-3030109240

More Books

Students also viewed these Databases questions

Question

consider the following unrestricted grammar G

Answered: 1 week ago

Question

13-4 What are alternative methods for building information systems?

Answered: 1 week ago