Please look at document need done by 2moro at 10.pm est
Perform the following tasks. Put your answer to each question into a word file.
Notes: you are free to choose either SQLPlus or SQL Developer to do this assignment. If you use SQLPlus, you have to spool out the results as you did in Assignment 1. If necessary, edit your spooled file so that your answers are nicely numbered as the questions appear below.
If you use SQL Developer, you can first copy and paste your statement into your word file, and then copy and paste the results into your word file. In order to include the column headers into your copied results, after you select the results, you hit SHIFT-CTRL-C to copy and CRTL-V to paste.
· List the distinct location_id from the DEPARTMENTS table.
· List first_name, last_name and hire_date of the employees whose manager’s id is 120. Please sort the output in a descending order of last_name. (EMPLOYEES table)
· List first_name, last_name and salary for the employees whose salary is between 9000 and 12000. Please add first_name and last_name together (make sure the results are readable) and assign an alias employee_name to the added column in the query. (EMPLOYEES table)
· List the department_id and department_name from the departments table. However, the query should only list the department_id is not in the set (30, 50, 60, 80, 90,100). (DEPARTMENTS table)
· List job_title and max_salary from the JOBS table. Also list salary_category which is categorized as low if max_salary is lower than 10000, as middle if max_salary is lower than 20000 but higher than 10000, and as high if max_salary is higher than or equal to 20000. Hint: use case expression (see Slide 25 in Ch 1)
· Search for country names in the countries table. Regardless cases, the first two characters of the country name should be equal to ‘un’. (COUNTRIES table)
Hint: use lower() and substr() functions and use them in a nested way like lower(substr())
· Write a SELECT statement to accept country name at runtime. List the search results by showing country_id and country_name from the countries table. (Countries table)
Hint: use ampersand (&), see slide 26 in Ch 1
· Write another SELECT statement based on the statement you write for Question 7. This time the query should be case insensitive, which means the name you type can be upper, lower, or mix cases.
Hint: use lower() function; the & part will be an parameter of lower() function
· List the name, hire_date and years of employment based on the employees table. Round years of employment to two decimal places and only show employees whose first name ends with two characters, ‘en’. (EMPLOYEES table)
Hint: use months_between() and round() functions. See slide 18 in Ch 2
· List the name, salary, and compensation based on the employees table. The salary should be greater than 12000 and the manager id is 100. Compensation=salary +salary*commistion_pct+200. If commission _pct is null, replace it with 0.1. (EMPLOYEES table)
Hit: see slide 24 in Ch2.
· List the name, salary, and compensation based on the employees table. The salary should be greater than 12000 and the manager id is 100. Compensation=salary +salary*commistion_pct+200. If commission _pct is null, replace it with 0.1. Format the compensation using the first format model on Table 2.10 in the textbook. (EMPLOYEES table)
Note: this is a bonus question. You can get additional 4 points as bonus if you do this question correctly. No partial points will be given, which means you either get 4 points if your answer is 100% correct, or zero.