Required:

Use the Employees table (copy and paste the code in SQL command line)

Write the PL/SQL based on the requirement in each number.

Note: Sample output is just a guide, it does not necessarily that the value to your output should be equal to the sample output.

CREATE TABLE EMPLOYEES(

EMPLOYEE_ID NUMBER(3) PRIMARY KEY,

FIRSTNAME VARCHAR(10),

LASTNAME VARCHAR(10)NOT NULL,

JOB_ID VARCHAR(10),

SALARY NUMBER(8,2),

MANAGER_ID NUMBER(6),

DEPARTMENT_ID NUMBER(4));

INSERT INTO EMPLOYEES VALUES (100,'STEVEN','KING','AD_PRES',24000,NULL,90);

INSERT INTO EMPLOYEES VALUES (101,'NENA','KOCHAR','AD_VP',17000,100,90);

INSERT INTO EMPLOYEES VALUES (102,'LEX','DE HAAN','AD_VP',17000,100,90);

INSERT INTO EMPLOYEES VALUES (103,'ALEXANDER','HUNOLD','IT_PROG',NULL,101,60);

INSERT INTO EMPLOYEES VALUES (104,'BRUCE','ERNST','IT_PROG',6000,102,60);

INSERT INTO EMPLOYEES VALUES (107,'DIANA','LORENTZ','IT_PROG',4200,103,60);

INSERT INTO EMPLOYEES VALUES (124,'KEVIN','MOURGOS','ST_MAN',5800,100,50);

INSERT INTO EMPLOYEES VALUES (141,'TRINA','RAJS','ST_CLERK',3500,124,50);

INSERT INTO EMPLOYEES VALUES (142,'CURTIS','DAVIES','ST_CLERK',3100,124,50);

INSERT INTO EMPLOYEES VALUES (143,'RANDALL','MATOS','ST_CLERK',2600,124,50);

INSERT INTO EMPLOYEES VALUES (144,'PETER','VARGAS','ST_CLERK',2500,124,50);

INSERT INTO EMPLOYEES VALUES (149,'ELENI','ZLOTKEY','SA_MAN',10500,100,80);

INSERT INTO EMPLOYEES VALUES (174,'ELLEN','ABEL','SA_REP',11000,149,50);

INSERT INTO EMPLOYEES VALUES (176,'JONATHAN','TAYLOR','SA_REP',8600,149,80);

INSERT INTO EMPLOYEES VALUES (178,'KIMBERLEY','GRANT','SA_REP',7000,149,NULL);

INSERT INTO EMPLOYEES VALUES (200, 'JENNIFER','WHALEN','AD_ASST',4400,101,10);

INSERT INTO EMPLOYEES VALUES (201,'MICHAEL','HARTSTEIN','MK_MAN',13000,100,20);

INSERT INTO EMPLOYEES VALUES (202,'PAT','FAY','MK_REP',6000,201,20);

INSERT INTO EMPLOYEES VALUES (205,'SHELLEY','HIGGINS','AC_MGR',12000,101,110);

INSERT INTO EMPLOYEES VALUES (206,'WILLIAM','GIETZ','AC_ACCOUNT',8300,205,110);

1. Write a query that display the employees lastname concatenated with firstname and put a , (comma) in between. Rename the column as Complete Name. Note all values in Complete Name column should be in lowercase plus display the length of employees lastname for all employees whose lastname starts with letter M sort the lastname in its default order.

Sample output:

Complete Name LENGTH(LASTNAME)

mourgos, anna 7

2. Write a query that display the Firstname concatenated to employees original salary plus concatenate again a new column salary that multiplies the original salary into three. Rename the column as Dream Salaries.Note sort the salary in descending order.

Sample output:

Dream Salaries

King earns 24000 monthly but wants 72000

3. Write a query that display the lastname and salary of all employees whose department_id = 60 or job_id like _T%. Format the salary to be 15 character long, left padded with $ as special character. Label the column Salary.

Sample output:

Lastname salary

King $$$$$$$$$240000

4. Write a query that display the employees lastname concatenated to salary. Format the salary column to 6 character long left padded with * as special character for all employees whose manager_id is null or salary between 4000 and 6000 Rename the column as employees and their Salaries.

Sample output:

Employees and their Salaries

5. Write a query that display the firstname in capitalized format rename the column as pangalan whose job_id is equal to SA_REP.

6. Write a query that display the firstname and length of firstname rename the column length of firstname as Number of Character of all employees whose salary is between 4400 and 8300.

7. Write a query that display the firstname concatenated to salary with additional column salary that provides a computation of salary * 2. Rename the column as Increase of all employees whose lastname ends with N.

Sample output:

Jennifer salary is 4400 if multiply by two then he/she got a new salary of 8800

8. Write a query that displays the salary leftpadded with 15 character long and $ as special character and another column salary right padded with 10 character long with @ as special character used of all employees in 201, 176 and 144.

9. Write a query using SUBSTR function that returns the job_id = REP.

Sample output:

Job_id

SA_REP