1. Consider the following tables in a relational database. Provide the appropriate SELECT SQL statement necessary...

Transcribed Image Text:

1. Consider the following tables in a relational database. Provide the appropriate "SELECT" SQL statement necessary to answer the queries that follow. Primary keys are underlined and foreign key fields have an asterisk at the end of the field. CUSTOMERS (CUST-NO, C-NAME, C-ADDRESS, BALANCE) SALESPERSONS (SP-NO, S-NAME, DATE-EMPLOYED, SALARY) SALES (INVOICE-NO, DATE, CUST-NO*, SP-NO*) a) List the salesperson name and salary for all sales to customers whose balance outstanding is greater than 20000. b) List the names and addresses of all customers who have been sold merchandise by salespersons employed before 1/196. 2. Find all violations of entity and referential integrities. CUSTOMERS CUSTOMER NAME ADDRESSI ADDRESS2 STATE STR BALANCE 456 ABC Corp. 111 Any St. Houston TX 6.25 34560.65 457 DEF Corp 222 Anywhere Dr. New York NY 6.50 2145.90 458 GHI Corp s Someplace Ct. Miani 6.45 45670.75 459 UKL Corp 56 Some Dr. Bryan TX 6.25 21009.50 460 MNO Corp Noplace Cir San Diego CA s s0 4561.00 INVOICE-ITEMS INVOICE# TEM# DESC PRICE QTY 1001 121 Widget 2.25 45 1001 $40 Bolt 0.40 25 1002 211 Gear 3.70 10 1003 121 Widget 2.25 15 INVOICE-ITEMS INVOICE# ITEM# DESC PRICE QTY 1001 121 Widget 2.25 45 1001 540 Bolt 0.40 25 1002 211 Gear 3.70 10 1003 121 Widget 2.25 15 1003 121 Widget 2.25 10 1006 348 Nut 0.25 INVOICES INVOICE# DATE CUSTOMER# AMOUNT 1001 11-1-95 456 450.75 1002 11-2-95 457 560.25 11-2-95 160 300 10 INVOICES INVOICE# DATE CUSTOMER# AMOUNT 1001 11-1-95 456 450.75 1002 11-2-95 457 560.25 11-2-95 460 300.10 1003 11-2-95 459 890.25 1004 11-3-95 450 425.50 1. Consider the following tables in a relational database. Provide the appropriate "SELECT" SQL statement necessary to answer the queries that follow. Primary keys are underlined and foreign key fields have an asterisk at the end of the field. CUSTOMERS (CUST-NO, C-NAME, C-ADDRESS, BALANCE) SALESPERSONS (SP-NO, S-NAME, DATE-EMPLOYED, SALARY) SALES (INVOICE-NO, DATE, CUST-NO*, SP-NO*) a) List the salesperson name and salary for all sales to customers whose balance outstanding is greater than 20000. b) List the names and addresses of all customers who have been sold merchandise by salespersons employed before 1/196. 2. Find all violations of entity and referential integrities. CUSTOMERS CUSTOMER NAME ADDRESSI ADDRESS2 STATE STR BALANCE 456 ABC Corp. 111 Any St. Houston TX 6.25 34560.65 457 DEF Corp 222 Anywhere Dr. New York NY 6.50 2145.90 458 GHI Corp s Someplace Ct. Miani 6.45 45670.75 459 UKL Corp 56 Some Dr. Bryan TX 6.25 21009.50 460 MNO Corp Noplace Cir San Diego CA s s0 4561.00 INVOICE-ITEMS INVOICE# TEM# DESC PRICE QTY 1001 121 Widget 2.25 45 1001 $40 Bolt 0.40 25 1002 211 Gear 3.70 10 1003 121 Widget 2.25 15 INVOICE-ITEMS INVOICE# ITEM# DESC PRICE QTY 1001 121 Widget 2.25 45 1001 540 Bolt 0.40 25 1002 211 Gear 3.70 10 1003 121 Widget 2.25 15 1003 121 Widget 2.25 10 1006 348 Nut 0.25 INVOICES INVOICE# DATE CUSTOMER# AMOUNT 1001 11-1-95 456 450.75 1002 11-2-95 457 560.25 11-2-95 160 300 10 INVOICES INVOICE# DATE CUSTOMER# AMOUNT 1001 11-1-95 456 450.75 1002 11-2-95 457 560.25 11-2-95 460 300.10 1003 11-2-95 459 890.25 1004 11-3-95 450 425.50