Expert answer:2 Files are attached:1. emp.sql — run this script to create a table called EMPLOYEES_SQL – you will need it for the SQL Worksheet 2.2. SQL Worksheet 2RUN the script attached in your command window in SQLDEV (lastname tab) you will have a new table called EMPLOYEES_SQLUse this table for all commands in this lesson.Special Operators: IN, LIKE, BETWEENUsing Logical Operators: AND OR NOT Sorting results: Order by
cis1513_sql_assign2.doc
empsql.zip
Unformatted Attachment Preview
CIS1513 SQL HOMEWORK #2
Be sure to read the PDF files from the CIS 2520 class (in with the PDF files) to answer the last
three questions.
Turn in your work in NOTEPAD with the SQL statement and the results.
1. Select these columns from EMPLOYEES table:
Employee_id, first name, last name, job id, department Id, salary
Only show Employees who make a salary greater than 9000 dollars.
2. Select these columns from EMPLOYEES_SQL table:
last name, job id, salary , department_id
Retrieve only employees who work in department_id 10 or 50 or 60
(Use an IN statement)
Sort the data by Department id in descending order and last name in ascending order.
3. Select these columns from the EMPLOYEEs table showing which employees don’t
receive a salary.
Employee_id – use an aliases of ID, last_name use an aliases of NAME, salary,
(Use IS NULL statement)
4. Select all columns from the departments table that have a budget equal to or greater
than 1000 and less than or equal to 7000
(Use a BETWEEN statement)
5. Select all columns from the jobs table that have the word PROG in the JOB_ID column
(Use a LIKE statement (%))
6. Using SQL with Compound conditions: AND statement
Table: EMPLOYEES.
Display these columns and add the following aliases below:
Employee id needs an alias of ID, last name needs an aliases of NAME , job id needs an
alias of JOB, salary
Conditions: Find employees whose job_id is equal to: SUPER_PROG AND Salary
greater than or equal to 9000.
Sort by:
job_id and salary
Notes: (Use an ORDER BY statement to sort the data, use commas between sort columns)
(Don’t forget SUPER_PROG is character data, you need single quotes around it)
7. Revise question 2 to use the EMPLOYEES_SQL table, remove the job_id criteria in the
where statement, then to NOT display department ids that are equal to: 10 or 50 or
60 (use a NOT IN)
Keep the same sort order. (You will not get any results for this statement.)
8. Revise the query below to use a BETWEEN clause and column aliases. Find employees
who make a salary between 3000 and 9000. Sort data by salary in descending order.
SELECT employee_id, last_name, job_id, salary, department_id
FROM EMPLOYEES
where salary >= 3000 and salary <=9000;
Column aliases to be used:
Employee_id
Job_id
Department id
ID
JOB
DEPT_ID
9. Display department id, and concatenate first_name and last_name together with a
space in between the columns, give the column an alias of NAME.
Sort by department_id in descending order and last_name in ascending order.
10. Display all columns from employees table where the manager id is 100 or the
department id is 60. Sort by Employee_last_name in descending order.
11. Display the average price of the food for all of the items offered. (F_ table)
12. Display the minimum budget of all the departments. (Departments table)
13. Display the maximum hire date for the salaried employees. (Employees table)
...
Purchase answer to see full
attachment
