1. Write a database description for each of the relations shown, using SQL DDL. 2. Write SQL data definition commands for each of the following queries: a) How would you add an attribute, country code, to the customer table? b) How would you remove the class and room attribute from the customer table? c) How would you change the Address1 field from 25 characters to 40 characters? 3. Write SQL commands for the following: a) Create two different forms of the INSERT command to add a customer with a customerID of 65798 and last name Lopez to the Customer table. b) Now write a command that will remove Lopez from the Student table.

c) Create an SQL command that will modify the name of product XYZ 5555 from Database to Introduction to Relational Databases. 4. Write SQL queries to answer the following questions: a) Which customer have an ID number that is less than 12345? b) What is the name of the product whose ID is 4756? c) Display the list of products whose price is less than 500? d) Find the average price of the products? e) List the product who belongs to the same category. f) Display the list of products of same category but the number of products less than 10. 5. Write SQL queries to answer the following questions: a) How many products are defined against the category ID 2714? b) Display the detail of the order ID 2346. c) List the Product ID, category, and SKU.

DO Category 1 CategoryID CategoryName Description Picture Active Suppliers 8 SupplierID CompanyName Contact Name ContactName ContactTitle Address1 Address2 City State PostalCode Country Phone Fax Email URL PaymentMethods DiscountType TypeGoods Notes DiscountAvailable CurrentOrder Logo CustomerID SizeURL Products * 8 ProductID SKU IDSKU Vendor ProductID Product Name ProductDescription SupplierID CategoryID QuantityPerUnit UnitPrice MSRP AvailableSize AvailableColors Size Color Discount UnitWeight UnitsInStock UnitsOnOrder ReorderLevel ProductAvailable DiscountAvailable CurrentOrder Picture Ranking Note Shippers 8 ShipperID CompanyName Phone Orders * OrderID CustomerID Order Number PaymentID Order Date ShipDate RequiredDate ShipperID Freight Sales Tax Timestamp TransactStatus ErrLoc ErrMsg Fulfilled Deleted Paid PaymentDate Customers * Y CustomerID FirstName Last Name Class Room Building Address1 Address2 City State PostalCode Country Phone Email VoiceMail Password Creditcard CreditcardTypeID CardExpMo CardExpYr BillingAddress BillingCity BillingRegion BillingPostalCode Billing Country ShipAddress ShipCity ShipRegion ShipPostalCode ShipCountry DateEntered OrderDetails * OrderID ProductID OrderNumber Price Quantity Discount Total IDSKU Size Color Fulfilled ShipDate 8 Order DetailID BillDate Payment 8 PaymentID PaymentType Allowed