Using Transact SQL 1. Create a new database called Labs Create table employee with the following columns (enter 10 rows employees with data for each column) empID lastName firstName middleI address city phone email jobTitleCode hireDate salary Create table jobTitle with the following columns (enter 5 rows jobTitles) jobTitleCode jobTitle exempt/nonexempt min max 2. a. query ‘JOIN’ two tables and uses ‘BETWEEN’ to restrict record selection b. JOIN two tables and use ‘LIKE’ to restrict record selection. c. Create new table contractors (add 3 rows data). Use‘UNION’ to create a result set of all employees and contractors include select lists. d. Prepare a report showing all SQL statements and results. 3. Using SQL add 10 more employees to the database. Include one SQL insert statement. a. First, group rows by job title, providing counts of employees for each title. b. Then group rows by city, providing average salary for each city. c. Then group rows by exempt/nonexempt status, providing the highest and lowest employee salary for each group. d. Prepare a report showing SQL statements and results. 4. Using a CREATE statement, generate a “Terminated Employees?? table with the same columns as the employee table. a. add 5 employees to the new table. b. Using a UNION statement, create a result set of both active and terminated employees providing employees names, job titles, salaries, and an indicator of active or terminated. c. Using 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 SQL statements and results. Finally 5. Create a complete SQL Database utilizing JOIN, UNION, BETWEEN, LIKE First Construct a prototype of your project database, load it with several records of data by means of the insert command, and write SQL instructions for simple data retrieval. Prepare a report explaining the SQL statements and their results, demonstrating how data will be processed and accessed in the application. Include at least one UPDATE statement. Submit
1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.
2) Installation package that will install the software (in ready-to-run condition) on the platform(s) specified in this bid request.
3) Exclusive and complete copyrights to all work purchased. (No GPL, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site).
Windows 2000 XP