MBA - MIS Concentration Student Page

This page provides answers for the SQL Problem sets.

SQL Exercise Set #5

1. Display department locations with initial letters capitalized.

SELECT Left([Loc],1) & Right(LCase([loc]),Len([loc])-1) AS FormettedLoc FROM Dept;

2. For each employee, calculate the number of months between today and the date the employee was hired. Order the result by the number of months employed. Round the number of months up to the closest whole number.

SELECT round(DateDiff("m",[HireDate],Now()),0) AS monthsEmployed, Emp.Ename, Emp.EmpNo
FROM Emp
ORDER BY round(DateDiff("m",[HireDate],Now()),0), monthsEmployed;

3. Display the employee name, total compensation, and hire date for all employees whose salary is greater than twice their commission. Format the hire date to look like "3rd of December, 1982."

First, two tables were created to translate integers for day and month into day ordinals and month text. (Since there was an error with formatting the textual month in the QBE grid using the datepart function, it was only possible to get an interger for the month value. As a result, I just created a month lookup table to solve the problem.)

Secondly, two queries were created to translate the day and month into the format required. The text for the query was as follows:

QUERY 1:qryDayHired
SELECT Emp.EmpNo, DatePart("d",[HireDate]) AS hiredOn, DatePart("m",[hiredate]) AS MonthHired FROM Emp;

QUERY 2:qryOrdinalDayHired
SELECT qryDayHired.EmpNo, tblOrdinals.ordinal, tblMonths.MonthName FROM tblMonths INNER JOIN (qryDayHired INNER JOIN tblOrdinals ON qryDayHired.hiredOn = tblOrdinals.number) ON tblMonths.MonthNumber = qryDayHired.MonthHired;

Lastly, the final query was created with the criteria and format specified in the question:

SELECT Emp.Ename, ([sal]+nz([comm],0)) AS TotalCompensation, [qryOrdinalDayHired].[ordinal] & " of " & [qryOrdinalDayHired].[MonthName] & ", " & DatePart("yyyy",[emp].[HireDate]) AS DayHiredOn, Emp.Sal, Emp.Comm
FROM Emp INNER JOIN qryOrdinalDayHired ON Emp.EmpNo = qryOrdinalDayHired.EmpNo
WHERE (((Emp.Sal)>nz([comm],0)*2));

Results are:

4. Display the employee name in lowercase and salary rounded to hundreds for all employees.

First, the following function was created in Module1:

Option Compare Database
Option Explicit

Function RoundToNearest(dblNumber As Double, varRoundAmount As Double, _
   Optional varUp As Variant) As Double

   Dim dblTemp As Double
   Dim lngTemp As Long

   dblTemp = dblNumber / varRoundAmount
   lngTemp = CLng(dblTemp)

   If lngTemp = dblTemp Then
		 RoundToNearest = dblNumber
   Else
	  If IsMissing(varUp) Then
		 ' round down
		 dblTemp = lngTemp
	  Else
		 ' round up
		 dblTemp = lngTemp + 1
	  End If
	  RoundToNearest = dblTemp * varRoundAmount
   End If
End Function

Next, the following query was added to the database:
SELECT LCase([Ename]) AS EmployeeName, RoundToNearest([Sal],100) AS SalaryInHundreds FROM Emp;

The results:

5. Display the employee name and the decoded department number as follows: 10-Accounting, 20-Research, 30-Sales, 40-Operations.

SELECT Emp.Ename, [dept].[deptNo] & " - " & Left(UCase([dept].[Dname]),1) & Right(LCase([dept].[dname]),Len([dept].[dname])-1) AS Department
FROM Dept INNER JOIN Emp ON Dept.DeptNo = Emp.DeptNo
ORDER BY Emp.Ename;

Results are:

6. Display employee name, department number, current monthly salary, and the adjusted monthly salary to satisfy the following pay increase scenario: All employees in department 10 will receive a 5% increase, department 20 a 7% increase, and all others will remain the same. The proposed monthly salary column should read PROPOSED SALARY.

SELECT Emp.Ename, Emp.DeptNo, Emp.Sal, IIf([DeptNo]=10,[sal]*1.05,IIf([deptno]=20,[sal]*1.07,[sal])) AS [PROPOSED SALARY]
FROM Emp;

Results:

7. Write a query that produces the following for each employee: ALLEN EARNS 1600 PER MONTH BUT WANTS 4800

SELECT UCase([ename] & " earns " & [sal] & " per month but wants 4800") AS Message1 FROM Emp ORDER BY emp.ename;

Results:

SQL Problem Sets