– Take screen shots and/or cut and paste command and answer inside of midterm exam.
– Upload midterm exam to, week5: Midterm Exam
– Choose 20 of 27 test questions
– Each question is worth 1 points for total of 20.
- Show the structure of the EMPLOYEES table.
- Select all data from the EMPLOYEES table.
- Select the first and last column of the EMPLOYEES table.
- Create a query to display the JOB_ID, JOB_TITLEand MIN_SALARY with a MIN_SALARY greater than $4000.00.
- Create a query to display the JOB_ID, JOB_TITLEand MIN_SALARY for one specific employee.
- Write a query to display your name and the current date on a single line of output.
- Display using the employees table the EMPLOYEE_ID, FIRST_NAME, LAST_NAME and HIRE_DATEof every employee who was hired after to 1 January, 1995.
- Writea query to modify the previous query and display the HIRE_DATE’s that are not between 1995 and 1996.
- Write a query using the JOBS table to determine the what the MAX_SALARY would be if the employees were given a5% raise. Label the column New Max Salary. Display four columns from the employees table including the salary and the New Max Salary column.
10.Using the employees table display the employee numbers, department id, and hire date, which is the first Thursday three months from the hire date. Format the dates to appear in the format similar to “Thursday the Fifteenth of July, 2001”
11.For each Employee in the employees table display the employee_id, job_id, department_id and calculate the number of months between the hire_date and today’s date. Label the column MONTHS FROM HIRING. Round the number of months up to the closest whole number.
12.Write a query using the EMPLOYEES table that produces the following output concatenated. “Mr.” <first_name> “,” <last_name> “can be contacted at the following phone number ” <phone> “ or at his email address of “ <email>. Provide a column alias for each column.
13.Write a query using the employees table to display the last name, first name and hire date for three specific employees without using the ‘or’ operator.
14.Write a query using the regions table to display the REGION_ID REGION_NAME for the regions whose name contains the string East.
15.Write a query using the employees table to display the LAST_NAME, JOB_ID, and HIRE_DATE for employees whose last name begins with a G.
16.Write a query using the departments table that displays the department number, department name, manager id and location id where the manager id or location id is not null.
17.Create a query that will display the EMPLOYEE ID, LAST_NAME and MANAGER_ID of employees in the employee table. If the employee does not have a manger put “No Manger is Listed”. Create an alias for the column.
18.Write a query using the employees table to display the employee id, hire date , and salary for all employees with an hire date of December 19, 1999.
19.Display the average max_salary of all employees in the jobs table. Label the column “Average Max Salary”. Truncate your results to the nearest whole number.
- Display the last name, first_name, hire date and end date of employees using the employees table and the job_history table.
- Modify #20 so that it only lists employees with a department id of 20, 50 or 90.
22.Write a query to display a unique listing of the number of job id’s .
23.Determine the number of job id’s without listing them. Label the column Number of Jobs.
24.Write a query that will display the difference between the highest hire date and lowest end date in the employee and job_history tables. Label the column “Highest and Lowest”.
25.Write a query using the job_history table to display the job id , department id, and job title and months between the END_DATE and START_DATE.
26.Write a query to display the employee last name, email, phone_number, and start_date of all employees who earn a commission.
- Create a query that displays an evaluation of the employee’s salary where the salary is between the minimum salary and the maximum salary in the jobs table.Display the last names, department numbers, the minimum salary and the maximum salary. Give each column an appropriate label.