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