1 Write a SELECT statement to list employee information base

1. Write a SELECT statement to list employee information based employees table. The statement should be able to accept manager id at runtime. (HR.EMPLOYEES table

2. Produce a query to list city and state_province. The city is in the UK and the city name has only 6 characters. (HR.LOCATIONS table)

3. Produce a query to list customer name and city, both in a mixed case format. City’s third, fourth and fifth characters together should be equal to \'OOK\'. (DEMO.CUSTOMER table)

Hint: use initcap() and substr() functions

4. List the name, hire_date and years of employment based on demo.employee table. Round years of employment to two decimal places. (demo.EMPLOYEE table)

Hint: use round(months_between()).

5. 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+1000. If commission _pct is null, replace it with 0.08. (HR.EMPLOYEES table)

Solution

1) select lastname,firstname,designation, address,sal from HREmPLOYEES where sal = &salary;

2) select city, state_province from HRLOCATIONS where len(city) = 7;

3) select Customername, city from DEMOCUSTOMER where initcap(substring(city,3,5)) = \'ELM\' ;
4) select name, hire_date, months_between(joingdate, releiveddate) from EMPLOYEES ;
5)select name,sal from HREMPLOYEES where sal > 12000 and ManagerID = 100 ;