Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

1. Consider the following tables in a relational database. Provide the appropriate SELECT SQL statement necessary to answer the queries that follow. Primary keys

1. Consider the following tables in a relational database. Provide the appropriate SELECT SQL statement necessary to answer2. Find all violations of entity and referential integrities. CUSTOMERS NAME CUSTOMER ADDRESSI ADDRESS2 STATE STR BALANCE 145INVOICE-ITEMS INVOICE# ITEM# DESC PRICE QTY (1001 121 Widget 2.25 145 1001 540 Bolt 10.40 125 (1002 1211 Gear 13.70 10 1003 1INVOICES INVOICE# DATE CUSTOMER AMOUNT 1001 11-1-95 1456 450.75 1002 11-2-95 1457 560.25 11-2-95 1460 300.10 1003 11-2-95 145

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

Step by Step Solution

3.36 Rating (152 Votes )

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

Step: 3

blur-text-image

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

Accounting Information System

Authors: James A. Hall

7th Edition

978-1439078570, 1439078572

More Books

Students also viewed these Programming questions

Question

Explain how linkages between relational tables are accomplished.

Answered: 1 week ago