Ross Larson - GBAPS code sample - SQL

GBAPS.sql

-- Question 1 - List Names, Birthdates.  Sort by last name, first name
Select Distinct firstName, lastName, birthDate
From Employee
Order By lastName, firstName;
/*
firstName	lastName	birthDate
Christine	Morgan	1974-05-04 00:00:00
Jill	Page	1980-10-21 00:00:00
Bill	Simons	1968-02-14 00:00:00
Joe	Smith	1960-01-16 00:00:00
Kyle	Williams	1975-06-20 00:00:00
*/

-- Question 2 - Count Employees with listed genders
Select COUNT(employeeID)
From Employee
Where gender IS NOT NULL;
-- Result is 5

--Question 3 - List Dept. Name and First/Last Name of dept. chair.
Select d.departmentName, e.FirstName, e.lastName
From Department d
Left Join Employee e
	On d.departmentChairEmployeeID = e.employeeID;
/*
departmentName	FirstName	lastName
Science	Joe	Smith
Math	Bill	Simons
*/

--Question 4 - List Name of each active employee and their current department(s)
Select e.firstName, e.lastName, d.departmentName
From Employee e
Left Join EmployeeDepartment ed
	On e.employeeID = ed.employeeID
Left Join Department d
	On ed.departmentID = d.departmentID
Where ed.endDate IS NULL
And d.departmentID IS NOT NULL;

/*
firstName	lastName	departmentName
Joe	Smith	Science
Bill	Simons	Math
Jill	Page	Science
*/

--Question 5 - List all employees who started in 2001
Select Distinct e.firstName, e.lastName
From Employee e
Left Join EmployeeDepartment ed
	On e.employeeID = ed.employeeID
Where ed.startDate >= '01-01-2001'
And ed.startDate <= '12-31-2001';

/*
firstName	lastName
Jill	Page
Joe	Smith
Kyle	Williams
*/

--Question 6 - Find duplicate employees (same first/last name, birthday and gender)
Select firstName, lastName, birthDate, gender, count(*)
From Employee
Group By firstName, lastName, birthDate, gender
Having count(*) > 1
/*
firstName	lastName	birthDate	gender	(No column name)
Jill	Page	1980-10-21 00:00:00	F	2
*/

--Question 7 - List Employee and either current department or NONE if they are not an active member of a department.
Select 	e.firstName, 
		e.lastName,
		ISNULL(d.departmentName, 'None') As CurrentDepartment
From Employee e
Left Join EmployeeDepartment ed
	On e.employeeID = ed.employeeID
Left Join Department d
	On ed.departmentID = d.departmentID
Where endDate IS NULL;

/*
firstName	lastName	CurrentDepartment
Joe	Smith	Science
Jill	Page	Science
Christine	Morgan	None
Bill	Simons	Math
Jill	Page	None
*/

-- Question 8 - Add Christine Morgran to Math dept. effective 05-01-2015.
Insert Into EmployeeDepartment
Values (
	(Select employeeID From Employee Where firstName = 'Christine' And lastName = 'Morgan'),
	(Select departmentID From Department Where departmentName = 'Math'),
	'2015-05-01',
	NULL
);
/*(1 row(s) affected)*/

--Question 9 - End Bill Simon's tenure in Math dept. effective 04-30-2015
Update EmployeeDepartment ed
Set ed.EndDate = '2015-05-01'
Where employeeID = (Select e.employeeID From Employee e Where e.firstName = 'Bill' And e.lastName = 'Simons')
And departmentID = (Select d.departmentID From Department d Where d.departmentName = 'Math');
/* (1 row(s) affected) */

--Question 10 -  Someone asks you to make a stored procedure to update employee info.  What are your questions?
/**
1. What information do you need to change?
2. Do you need any default information inserted if none is available?
3. How do we identify the employee to modify?
4. Should updating their info affect being a member of or a chair of a department?
5. Why does it have to be a stored procedure?
**/