Home work project that is to be done in 5 steps with full documentation including design and internal comments on each of the 5 steps. The project starts small and keeps building upon it until all the requirements are met. This is for a beginning T-Sql class so it should be fairly simple.
Create a T-SQL database that has 2 tables;
a. One table is employee information and contains the
Title code,Hire Date, Salary
b. Second table is job title information and contains job
title code,job title,exempt/non-exempt status,minimum
salary, maximum salary
c. Enter 10 employees and 5 job titles into the tables
d. Use CREATE TABLE and CREATE DATABASE commands
e. write a brief summary describing the business
requirements for this project
Write 3 T-SQL queries
a. The first query joins the two tables in the database
and uses BETWEEN to restrict record selection
b. The second query joins the two tables in the database
and uses LIKE to restrict record selection
c. The third query uses UNION of the two tables to
produce a result set. Creating a table similar to the
Employee Table, but which contains contractors.
Add 3rows to the new table make sure the SSN
numbers are different than those in the Employee table.
Use the UNION to create a result set of all employees
and contractors include the SELECT lists, a literal
identifying the individual as an employee or
d. Prepare a report showing the the T-SQL statements
and the results
e. Prepare a brief summary describing the project design
and ER diagram
Using T-SQL add 10 more employees to the database,
include one T-SQL INSERT statement in the code.
a. First, group rows by job title, providing counts of
employees for each title.
b. Then group rows by city, providing average salary for
c. Then group rows by exempt/non-exempt status,
providing the highest and lowest employee salary
for each group.
d. Use functions and sub queries
e. Prepare a report showing T-SQL statements and
f. Prepare a brief summary describing the attendant data
retrieval required. Including possible T-SQL instuctions
that may be used and why you would consider them.
Using a CREATE statement, generate a "Terminated
Employees" table having the same columns as the
a. Add 5 employees to the new table
b. Using a UNION statement, create a result set of both
of both active and terminated employees, providing
names, job titles, salaries, and an indicator of active
c. Prepare a SELECT statement with a sub-select (rather
than a JOIN) to calculate the average salary for
exempt employees and provide an additional column
containing the literal "Exempt"
d. Prepare a report showing the T-SQL statements and
Construct a prototype of the project database, load it with
several records demonstrating how data will be processed
and accessed in the application. Include at least one
Create a complete T-SQL database combining all the
previous steps and commands
a. add stored procedures
b. use CURSORS
c. use TRIGGERS
Windows 2000 and XP