Question

SQL queries and procedures

TABLE: Employees

Business Rules:

EmployeeID is defined as the primary key.

Address has been denormalized to include City and State for performance reasons.

DeptNbr is a foreign key that references DeptNbr in the Department table Manager is a foreign key that references EmployeeID in the Employees table

Data Structure:

(EmployeeId, LastName, FirstName, Street, City, State, Zip, DateOfHire, DateOfBirth, JobTitle, Salary, DeptNbr(fk), Manager(fk))

TABLE: Department

Business Rules:

DeptNbr is defined as the primary key.

Data Structure:

(DeptNbr, DeptName, DeptFax, DeptPhone)

TABLE: Customer

Business Rules:

CustID is defined as the primary key.

Address has been denormalized to include City and State for performance reasons.

Data Structure:

(CustId , CustLastName, CustFirstName, Street, City, State, Zip, CreditLimit, CurrentBalance)

TABLE: Product

Business Rules:

ProductNbr is defined as the primary key.

Data Structure:

(ProductNbr, ProductName, ProductLocation, QtyOnHand,ReorderQty, Cost, MSRP)

TABLE: Calls

Business Rules:

CallID is defined as the primary key.

CustID is a foreign key that references CustID in the Customer table.

ProductNbr is a foreign key that references ProductNbr in the Product table

EmployeeID is a foreign key that references EmployeeID in the Employee table

Data Structure:

(CallID, Date, ProblemDescr, Resolution, CustID(fk), ProductNbr(fk), EmployeeID(fk))

TABLE: Sales

Business Rules:

SalesOrderNbr is defined as the primary key.

CustID is a foreign key that references CustID in the Customer table.

EmployeeID is a foreign key that references EmployeeID in the Employee table.

Data Structure:

(SalesOrderNbr, CustID(fk),EmployeeID(fk),Date)

TABLE: SaleProduct

Business Rules:

SalesOrderNbr + ProductNbr is defined as a composite primary key.

SalesOrderNbr is a foreign key that references SalesOrderNbr in the Sales table.

ProductNbr is a foreign key that references ProductNbr in the Product table.

Data Structure: (SalesOrderNbr(fk), ProductNbr(fk),Qty,UnitPrice)

REQUIRED QUERIES:

Create each of the following queries. After the query has been written and tested, create a stored procedure for the query. Name of query/stored procedure is given in ( ) at beginning of query.

1. (EmpList) For each employee, list the name (first and last), job title . Sort the employees in order by last name.

2. (CustomerCredit) List all customers who have a balance greater than their credit limit. For each customer, list customer ID, last name, and amount over credit limit (use an alias for this column)

3. (TopProduct) Using the Sale_Product table, list the Product Number of the best selling product. "Best selling" refers to the product that has the most total quantity sold.

4. (SaleData) For each Sale, list the sale number, sale date and customer last name. (Inner Join)

5. (DeptEmployees) For each department, list the department name, and the names of employees in that department. List ALL departments, even if the department does not have any employees. (Outer Join)

Answer 1

1. Create Procedure EmpList
AS
Select FirstName, Lastname, JobTitle  From Employees order By LastName;

2. Create Procedure CustomerCredit
as
Select CustID, CustLastName, CurrentBalance - CreditLimit as " Amount Over Credit Limit" from Customer
where currentbalance > creditlimit

3. Create Procedure TopProduct
as
Select ProductNbr from SaleProduct order by Qty desc

4. Create Procedure SaleData
as

Select SalesOrderNbr, Date, CustLastName from sale Inner Join customer on sale.custID=Customer.CustID

5. Create Procedure DeptEmployees
as
select d.DeptName, e.FirstName, e.LastName
from Department d join Employees e
on Department.DeptNbr(+) = Employees.DeptNbr