CREATE TABLE Employee

(

EmployeeIDintIDENTITY(1,1) NOT NULL PRIMARY KEY,

LastNamevarchar(25) NOT NULL,

FisrtNamevarchar(25) NOT NULL,

Address varchar(75) NOT NULL,

City varchar(50) NOT NULL,

State varchar(2) NOT NULL,

Phone varchar(14) NOT NULL,

HireDate date NOT NULL,

Salary money NOT NULL,

Gender varchar(1) NOT NULL,

Age int(2) NOT NULL,

JobTitleIDint NOT NULL,

JobTitleIDint FOREIGN KEY REFERENCES JobTitle(JobTitleID)

)

GO

CREATE TABLE JobTitle

(

JobTitleIDint NOT NULL PRIMARY KEY,

EEOClassificationvarchar(25),

JobTitlevarchar(25) NOT NULL,

JobDescription text NOT NULL,

ExemptStatusvarchar(10) NOT NULL,

)

GO

INSERT INTO Employee (LastName, FirstName, Address, City, State, Phone, HireDate, Salary, Gender, Age)

VALUES ('Edelman', 'Glenn', '175 Bishops Lane', 'La Jolla', 'CA', '(619) 555-0199', '10/7/2003', '11.25', 'M', '64')

VALUES ('McMullen', 'Eric', '763 Church Street', 'Lemon Grove', 'CA', '(619) 555-0133', '11/1/2002', '7.00', 'M', '20')

VALUES ('Slentz', 'Raj', '123 Torrey Drive', 'North Clairmont', 'CA', '(619) 555-0123', '6/1/2000', '50000', 'M', '37')

VALUES ('Broun', 'Erin', '2045 Parkway - APT 2B', 'Encinitas', 'CA', '(760) 555-0100', '3/12/2003', '7.25', 'F', '24')

VALUES ('Carpenter', 'Donald', '927 Second Street', 'Encinitas', 'CA', '(619) 555-0154', '11/1/2003', '7.75', 'M', '18')

VALUES ('Esquivez', 'David', '10983 North Coast Hwy APT 902', 'Encinitas', 'CA', '(760) 555-0108', '7/25/2003', '9.75', 'M', '25')

VALUES ('Sharp', 'Nancy', '10793 Montecino Road', 'Romona', 'CA', '(858) 555-0135', '7/12/2003', '10.50', 'F', '24')

INSERT INTO JobTitle (EEOClassification, JobTitle, JobDescription, ExemptStatus)

VALUES (Office/Clerical', 'Accounting clerk', 'Computes, classifies, records, and verifies numerical data for use in maintaining accounting records', 'No')

VALUES ('Officials & Managers', 'Assistant manager', 'Supervises and coordinates activities of workers in department of food store. Assists store manager in daily operations of store.', 'Yes')

VALUES ('Sales Workers', 'Bagger', 'Places customer orders in bags. Performs carryout duties for customers.', 'No')

VALUES ('Sales Workers', 'Cashier', 'Operates cash register to itemize and total customer’s purchases in grocery store.', 'No')

VALUES ('Technician', 'Computer support specialist', 'Installs, modifies, and makes minor repairs to personal computer hardware and software systems, and provides technical assistance and training to system users.', 'No')

VALUES ('Officials & Managers', 'Director of finance and accounting', 'Plans and directs the finance and accounting activities for Kudler Fine Foods.', 'Yes')

VALUES ('Craft Workers (Skilled)', 'Retail assistant bakery and pastry', 'Obtains or prepares food items requested by customers in retail food store.', 'No')

VALUES ('Operatives (Semi skilled)', 'Retail assistant butchers and seafood specialists', 'Obtains or prepares food items requested by customers in retail food store.', 'No')

VALUES ('Office/Clerical', 'Stocker', 'Stores, prices and restocks merchandise displays in store.', 'No')

GO

SELECT EmpoyeeID, LastName, FirstName, Address, City, State, Phone, HireDate, Salary, Gender, Age FROM Employee

UNION

SELECT EEOClassification, JobTitle, JobDescription, ExemptStatus FROM JobTitle

WHERE Salary

BETWEEN '7.00' AND '50000'

GO

SELECT EmpoyeeID, LastName, FirstName, Address, City, State, Phone, HireDate, Salary, Gender, Age FROM Employee

UNION

SELECT EEOClassification, JobTitle, JobDescription, ExemptStatus FROM JobTitle

WHERE HireDate

BETWEEN '6/1/2000' AND '11/1/2003'

GO

SELECT EmpoyeeID, LastName, FirstName, Address, City, State, Phone, HireDate, Salary, Gender, Age FROM Employee

UNION

SELECT EEOClassification, JobTitle, JobDescription, ExemptStatus FROM JobTitle

WHERE Phone

LIKE '(760)%'

GO

SELECT EmpoyeeID, LastName, FirstName, Address, City, State, Phone, HireDate, Salary, Gender, Age FROM Employee

UNION

SELECT EEOClassification, JobTitle, JobDescription, ExemptStatus FROM JobTitle

WHERE Age

IN '24, 25'

GO

SELECT EmpoyeeID, FirstName + ' ' + LastName + ',' Age FROM Employee

UNION

SELECT JobTitleID, JobDescription,

ORDER BY FirstName ASC, LastName ASC

GO

SELECT LastName FROM Employee

UNION

SELECT EEOClassification FROM JobTitle

GROUP BY EEOClassification

GO

SELECT LastName, Salary FROM Employee

GROUP BY Salary

GO

SELECT FirstName, LastName, City FROM Employee

(SELECT JobTitle FROM JobTitle)

GROUP BY LastName

GO

UPDATE Employee

SET Salary=Salary*10

WHERE EEOClassification='Technician'

GO

DELETE FROM Employee

WHERE Last_Name='Edelman' AND First_Name='Glenn'

GO

CREATE PROC SalaryCheck

@JobTitleID

AS

SELECT JobTitleID, Salary