Write SQL queries to answer the following questions:

Write a query to list the employees first, and last name as well as their hire date and commission$\_$pct$.$ If the commission$\_$pct is empty report $0$ instead. Order by Last name alphabetically in ascending order.

Now repeat the previous query but report only the employees with

a$.$ Employee$\_$id larger than $150$

b$.$ First name that starts with the letter D

c$.$ First name that includes the letter in any case and position

d$.$ Last names alphabetically after $$Matos$$

e$.$ Hire date after $01-$MAR$-06($the correct query will return $48$ records$)$

f$.$ salary between $10000$ and $12000$

g$.$ job$\_$id FI$\_$ACCOUNT or SA$\_$MAN

h$.515$ area code $($the first three digits of their phone number$)$ using the extract function

i$.$ with an empty department$\_$id

Find the average salary of employees

Assuming that the city tax for each employee is $3\mathrm{.}8398$ percent of the salary, calculate this tax for each employee. Then round this value to two decimals and name it Rounded$\_$City$\_$Tax. Order by Rounded$\_$City$\_$Tax descending

Count all the departments and also count all the departments without a manager.

Using the Job$\_$History table report the job history of every employee in the form:

Employee$\_$Id $$ Job$\_$Id $$ FROM:Year$\_$of$\_$Start$\_$Date $$ TO: Year$\_$of$\_$End$\_$Date

Hint: You need to extract the year of each of the dates and use a concatenation function to merge all the columns in the form presented above.

Find the employees who have neither commission$\_$pct nor manager

Find the employees who either don$$t have a commission$\_$pct or don$$t have a manager

Return the employees that are either IT$\_$PROG with salary $$8000$ or more, or are FI$\_$ACCOUNT. Write the query without using parenthesis $($but use parenthesis to confirm the result$).$

B$.$ Return the employees that are either IT$\_$PROG or FI$\_$ACCOUNT and in both cases have salary $$8000$ or more.

For each employee, list the manager$\_$id$,$ job$\_$title, and salary. Order the results:

In ascending order of manager$\_$id and then ascending order of salary

In ascending order of manager$\_$id and then descending order of salary

In descending order of manager$\_$id and then ascending order of salary

How many locations overall? How many locations in each country $($report the country$\_$ids and a count of locations with each country$\_$id$)?$

Which country$\_$ids have more than $2$ locations associated with them?

How many employees in each department? Order by the number of employees to find the department with the most employees.

How many employees were hired each year?

B$.$ How many employees were hired each year, if we only take into accounts hires of March? Is this requirement placed in the WHERE clause, the HAVING clause or both?

C$.$ How many employees were hired each year, if we only take into accounts the years $2006$ and later? Is this requirement placed in the WHERE clause, the HAVING clause or both?

D$.$ How many employees were hired each year, if we only take into account the years with more than $2$ hires? Is this requirement placed in the WHERE clause, the HAVING clause or both?

E$.$ How many employees were hired each year in each department?

Extra Credit:

Can you use SQL to do basic arithmetic such as $2*3$ and $5+7?$ Which table are you selecting from? Hint: find out what is DUAL pseudotable

Choose a submission type

Drag a file here, or click to select a file to upload

Drag a file here, or

Choose a file to upload

No file chosen

or

I agree to the tool's End$-$User License Agreement

This assignment submission is my own, original work