Question

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/1/96.

1. Find all violations of entity and referential integrities.

Customers

customer #	name	address1	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.	5 SOMEPLACE CT	MIAMI	FL	6.45	45670.75
459	JKL CORP.	56 SOME DR.	BRYAN	TX	6.25	21009.50
460	MNO CORP.	7 NOPLACE CIR.	SAN DIEGO	CA	5.50	4561.00

INVOICE-ITEMS

INVOICE #	ITEM#	DESC	PRICE	QTY
1001	121	WIDGET	2.25	45
1001	540	BOLT	.40	25
1002	211	GEAR	3.70	10
1003	121	WIDET	2.25	15
1003	121	WIDGET	2.25	10
1006	348	NUT	0.25	5

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

Answer 1

The SQL statements are provided below :

a) SELECT s.S-NAME, s.SALARY

FROM CUSTOMERS c, SALESPERSONS sp, SALES s

WHERE s.SP-NO=sa.SP-NO AND c.CUST-NO=s.CUST-NO AND BALANCE >20000

b) SELECT c.C-NAME, c.C-ADDRESS

FROM CUSTOMERS c, SALESPERSONS sp, SALES s

WHERE s.SP-NO=sa.SP-NO AND c.CUST-NO=s.CUST-NO WHERE DATE-EMPLOYED <'01/01/96'

If you have any doubt/query regarding the above solution, then let me know in the comment. If the solution helps, do give an upVote to this answer.