Part 1 (3 Points): Creating the database: Create the following tables. The under

Question

Part 1 (3 Points): Creating the database: Create the following tables. The underlined column names are the primary keys. Make sure to specify the primary and foreign key constraints in your create table statements. 1. customer: (cus_code:integer, cus_lname:varchar(20), cus_fname:varchar(20), cus_initial:char, cus_areacode:integer,cus_phone:integer). 2. invoice: (inv_number:integer, cus_code:integer, inv_date:date, foreign key cus_code references customer(cus_code)) 3. line: (inv_number:integer, prod_code:integer ,line_units:integer, foreign key (inv_number) references Invoice(inv_number), foreign key (prod_code) references Product (prod_code) ) 4. product:(prod_code:integer, prod_desc:varchar(50), prod_price:integer, prod_quant:integer,vend_code:integer, foreign key (vend_code) referenecs Vendor(vend_code)) 5. vendor:(vend_code:integer,vend_name:varchar(30),vend_contact:varchar(30 ),vend_areacode:integer,vend_phone:integer) Part 2 (3 Points): Inserting data: Insert the following data in the tables using insert into statements: 1. customer: 10010, Ramas, Alfred, A, 615, 8442573 10011, Dunne, Leona, K, 713, 8941238 10012, Smith, Kathy, W, 615, 8942285 10013, Olowski, Paul,F, 615, 2221672 10014, Orlando, Myron, NULL, 615, 2971228 2. invoice: 1001, 10011, 2008-08-03 1002, 10014, 2008-08-04 1003, 10012, 2008-03-20 1004, 10011, 2008-09-23 3. line: 1001, 12321, 1 1001, 65781, 3 1002, 34256, 6 1003, 12321, 5 1002, 12333, 6 4. product: 12321, hammer, 189 ,20, 232 65781, chain, 12, 45, 235 34256, tape, 35, 60, 235 12333, hanger, 200 ,10, 232 5. vendor: 232, Bryson, Smith, 615, 2233234 235, SuperLoo, Anderson, 615, 2158995 Part 3 (9 Points): SQL Queries Write SQL statements to answer each of the following questions. 1. List the First name, middle initial, last name, and area code for all customers. 2. List the invoice number and invoice date for all invoices of customer number 10011 3. List the product number and product quantity for products in invoice number 1001 4. List all product description and product price supplied by vendor whose vendor contact name is Smith 5. Produce a list of product description, vendor name, and vendor phone for all products with price less than 50. 6. For each product bought by a customer, list product description and customer’s first name and last name. Part 4 (5 Points): Checking entity and referential integrity: Write an SQL statement to do each of the following tasks in the given order (try 1, 2, 3, 4, then 5). Explain whether and why the statement is correctly executed or not 1. Insert the following entry in CUSTOMER 10012, Juan, Rodriguez, J, 612, 7788776 2. Insert the following entry in INVOICE 1005, 10017, 2008-11-30 3. Insert the following entry in PRODUCT 12322, hammer, 189, 20, 231 4. Insert the following entry to the VENDOR table 231,Adam, Eric, 615, 2158995 5. Insert the following entry in PRODUCT 12322, coil, 189, 20, 231

Explanation / Answer

/* 1 List the First name, middle initial, last name, and area code for all customers. */ SELECT customer.cus_fname, customer.cus_lname, customer.cus_areacode FROM customer ORDER BY customer.cus_areacode; /* 2 List the invoice number and invoice date for all invoices of customer number 10011. */ SELECT invoice.inv_number, invoice.inv_date FROM invoice WHERE invoice.cus_code = 10011 ORDER BY invoice.inv_number; /* 3 List the product number and product quantity for products in invoice number 1001 */ SELECT product.prod_code, product.prod_quant FROM product JOIN line ON line.prod_code = product.prod_code JOIN invoice ON line.inv_number = invoice.inv_number WHERE invoice.inv_number = 1001 ORDER BY prod_code; /* 4 List all product description and product price supplied by vendor whose vendor contact name is Smith */ SELECT product.prod_desc, product.prod_price FROM product JOIN vendor ON product.vend_code = vendor.vend_code WHERE vendor.vend_contact = "Smith" ORDER BY prod_price; /* 5 Produce a list of product description, vendor name, and vendor phone for all products with price less than 50. */ SELECT product.prod_desc, vendor.vend_name, vendor.vend_phone FROM product JOIN vendor ON vendor.vend_code = product.vend_code WHERE product.prod_price < 50 ORDER BY vend_name; /* 6 For each product bought by a customer, list product description and customer’s first name and last name. */ SELECT prod_desc, cus_fname, cus_lname FROM customer JOIN invoice ON customer.cus_code = invoice.cus_code JOIN line ON line.inv_number = invoice.inv_number JOIN product ON product.prod_code = line.prod_code ORDER BY cus_lname;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---