Using the General Hardware Corp. relational database to generate a batch of SQL Server scripts

6. Consider the General Hardware Corp. relational database and the data stored in it, as shown in Figure
5.14. create a single SQL Server script (a batch) combining answers to the sections b, d, e, f. Your script
should start with the correct USE command followed by a series of queries. Beware that you should take
care to not include more info in the output than was specified in those four sections, e.g. there should be
no additional. In production environment, such “extras” may disclose confidential information or extend
the output beyond the portable device’s screen.
b. Select rows from the CUSTOMER EMPLOYEE relation in which Customer Number = 2198. Project
Employee Number and Employee Name over that result.
d. Select rows from the PRODUCT relation in which Product Number = 21765. Project Unit Price over
that result.
e. Join the SALESPERSON and CUSTOMER relations using the Salesperson Number attribute of each
as the join fields. Select rows from that result in which Salesperson Name = Baker. Project Customer
Name over that result.
f. Join the PRODUCT relation and the SALES relation using the Product Number attribute of each as the
join fields. Select rows in which Product Name = Pliers. Project Salesperson Number and Quantity over
that result.
Solution:
Script:
use GeneralHardware_00252034;
SELECT EMPNUM, EMPNAME FROM CUSTOMEREMPLOYEE WHERE CUSTNUM=2198;
SELECT UNITPRICE FROM PRODUCT WHERE PRODNUM=21765;
SELECT CUSTNAME FROM SALESPERSON INNER JOIN CUSTOMER ON SALESPERSON.SPNUM =
CUSTOMER.SPNUM WHERE SPNAME='Baker';
Select SPNUM,QUANTITY from PRODUCT INNER JOIN SALES on PRODUCT.PRODNUM = SALES.PRODNUM
where PRODNAME = 'Pliers';
Output:

Task 2
Often, database analysts and designers deal with existing databases and have to make their best guesses
about the intent of the original database creators and plan how to adapt them to reflect the business needs
of the existing owners. Northwind diagram should be created on the NHCC server, as directed in the
beginning of this Unit. Analyze it and, if necessary, for understanding, right-click on table names and
Select Top 1000 Rows. Give an example where it would be the best choice when deleting/updating a
record belonging to one of the Northwind tables. Total three examples:
(a) No Action;
(b) Set Default OR Set Null;
(c) Cascade.
Present each example in the format: • the table name on “one” side or relation; • the foreign key
involved; • business justification of using this particular rule.
As is often the case, there may be different possible answers. I will read your justifications attentively.
For example, Restrict Rule justification may be needed if we want to keep records not just for an ongoing
operation but also for a possible audit of the past transactions.