Question

• As a suggestion, you might want to accomplish this SQL exercise before you accomplish the primary assignment for this week.
• If you have not already done so, use MSSQLS Management Studio to create a new database named ch07_ConstructCo. Use the default settings. When the database has been created, run the Ch07_ConstructCo_SQL.txt script linked above to create and load the database tables and data.  
• Write a T-SQL query for the constructco tables to list the employee last name, first name, job code, and hire date from the employee table.
• Write a T-SQL query for the constructco tables to list the employee last name, first name, and hire date where the hire date is greater than 2000-01-01.
• Write a T-SQL query for the constructco tables to list the employee last name, project number, assigned job from the employee, and assignment tables where the employee table employee number is equal to the assignment table employee number.
• Ensure that you have copied and pasted all of the T-SQL statements and verification results into your assignment submission.

Once you have accomplished that action, paste the script into your assignment submission document for this part of your assignment.

Answer 1

Dear Student,

You will need to create the necessary and instructed databbase and tables as per the given question. You can copy paste the commands of the queries that are attached in the code snippet below. Do note that I have used the following terms:

constructco_employee - table that holds the employee data

attributes of the table: e_no = employee number (primary key), fname = first name, lname = last name, j_code = job code, hire_date = date of hire.

constructco_assignment - table that holds the data of the assigned projects to employees

attributes of the table: emp_no = employee number (foreign key), proj_no = project number, job = assigned job.

SELECT LNAME, FNAME, HIRE_DATE FROM constructco_employee;


SELECT LNAME, FNAME, HIRE_DATE 
    FROM constructco_employee  
    WHERE HIRE_DATE > 2000-01-01;


SELECT LNAME, PROJ_NO, JOB 
   FROM constructco_employee, constructco_assignment 
   WHERE  constructco_employee.E_NO = constructco_assignment.EMP_NO;

Note: make necessary changes in the names of the table and attributes. Table names and attributes are CASE SENSITIVE!