Question

Q2. Retrieve the names of all employees from the employee table to produce output on CSV format or delimited format with a common delimeter, rather than separete columns.

Hint:Put the whole row into a string with a semicolon as the seperator(delimeter) between thecolumns: FORMAT:(fname;minit;lname)

Example:

EMPLOYEES

--------------

James;E;Borg

Frank;T;Wong

Q3.

Write a query to show the employees name from the employee table in this format: first letter of the first name, followed by a dot, a blank, and the full last name

Example:

EMPLOYEES

------------------

J. Borg

F. Wong

Q4.

Run a query from the employee table to find the age of the employees and the Day(Ex:Monday) they were born. You may need to update these records if Age is shown as negative.

Example:

NAME BDATE AGE

------------------------------- ------------------ ----------

Ramesh Narayan Monday 09/15/52 61

Jennifer Wallace Saturday 06/20/31 82

Q5.

Find the names of all employees who are directly supervised by 'franklin wong'. Hint: You may need to work with CASE sensitivity.

Q6.

write and test a query to list the name and the total compensation(salary+commission) of all the instructors.Unknown commission should be treated as zero

Q7.

write a query to list superssn,ssn,bdate for employees was born in year 1926 or later.

Sort the rows by asc superssn with NULL at top. For each employees, they should be sorted by desc bdate

I have copied and pasted the following data from the .sql file (create-insert table data) to use to answer these above mentioned questions. Please copy and paste all the data to the oracle system and run them to create table and insert data. Then generate the queries to answer the above mentioned questions.

CREATE TABLE employee (
fname varchar2(15) not null,
minit varchar2(1),
lname varchar2(15) not null,
ssn char(9),
bdate date,
address varchar2(30),
sex char,
salary number(10,2),
superssn char(9),
dno number(4),
primary key (ssn)
);

DROP TABLE works_on CASCADE CONSTRAINTS;
CREATE TABLE works_on (
essn char(9),
pno number(4),
hours number(4,1),
primary key (essn,pno)
);

CREATE TABLE instructor (
   instructor_id number(3) not null,
   instructor_name varchar2(15) not null,
   salary number(6,2) not null,
   commission number(6,2),
   mentor_id number(3),
   date_hired date
);

INSERT INTO employee VALUES ('Alicia', 'J', 'Zelaya',
'999887777', TO_DATE('07-19-1958', 'MM-DD-YYYY'), '3321 Castle, Spring,TX', 'F', 25000,
'987654321', 4);
INSERT INTO employee VALUES ('Jennifer', 'S', 'Wallace',
'987654321', TO_DATE('06-20-1931', 'MM-DD-YYYY'), '291 Berry, Bellaire,TX', 'F', 43000,
'888665555', 4);
INSERT INTO employee VALUES ('Ramesh', 'K', 'Narayan',
'666884444', TO_DATE('09-15-1952', 'MM-DD-YYYY'), '975 Fire Oak, Humble,TX', 'M', 38000,
'333445555', 5);
INSERT INTO employee VALUES ('Joyce', 'A', 'English',
'453453453', TO_DATE('07-31-1962', 'MM-DD-YYYY'), '5631 Rice, Houston, TX', 'F', 25000,
'333445555', 5);
INSERT INTO employee VALUES ('Ahmad', 'V', 'Jabbar',
'987987987', TO_DATE('03-29-1959', 'MM-DD-YYYY'), '980 Dallas, Houston,TX', 'M', 25000,
'987654321', 4);
INSERT INTO employee VALUES ('James', 'E', 'Borg',
'888665555', TO_DATE('11-10-1927', 'MM-DD-YYYY'), '450 Stone, Houston,TX', 'M', 55000,
null, 1);
INSERT INTO employee VALUES ('New', 'V', 'Lost',
'777977977', TO_DATE('03-23-1977', 'MM-DD-YYYY'), 'NY,NY', 'M', 20000, '987654321', 4);
      
INSERT INTO employee VALUES ('Franklin', 'T', 'Wong',
'333445555', TO_DATE('12-08-1945', 'MM-DD-YYYY'), '638 Voss, Houston,TX', 'M', 40000, '888665555', 5);      

INSERT INTO employee(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO)
VALUES ('John', 'B', 'Smith',
'123456789', TO_DATE('01-09-1955', 'MM-DD-YYYY'), '731 Fondren, Houston,TX', 'M', 30000,
'333445555', 5);
      
INSERT INTO works_on VALUES ('123456789', 1, 32.5);
INSERT INTO works_on VALUES ('123456789', 2, 7.5);
INSERT INTO works_on VALUES ('666884444', 3, 40.0);
INSERT INTO works_on VALUES ('453453453', 1, 20.0);
INSERT INTO works_on VALUES ('453453453', 2, 20.0);
INSERT INTO works_on VALUES ('333445555', 2, 10.0);
INSERT INTO works_on VALUES ('333445555', 3, 10.0);
INSERT INTO works_on VALUES ('333445555', 10, 10.0);
INSERT INTO works_on VALUES ('333445555', 20, 10.0);
INSERT INTO works_on VALUES ('999887777', 30, 30.0);
INSERT INTO works_on VALUES ('999887777', 10, 10.0);
INSERT INTO works_on VALUES ('987987987', 10, 35.0);
INSERT INTO works_on VALUES ('987987987', 30, 5.0);
INSERT INTO works_on VALUES ('987654321', 30, 20.0);
INSERT INTO works_on VALUES ('987654321', 20, 15.0);
INSERT INTO works_on VALUES ('888665555', 20, null);

insert into instructor values (
835, 'SPARKS', 4000 , 200, 978, TO_DATE('16-Dec-1984', 'DD-MON-YYYY'));

insert into instructor values (
978, 'STEEL', 5000 , 250, 222, TO_DATE('16-Jan-1980', 'DD-MON-YYYY'));

insert into instructor values (
222, 'CAINE', 5500 , 350, NULL, TO_DATE('02-Nov-1976', 'DD-MON-YYYY'));

insert into instructor values (
243, 'TUCKER', 2000 , NULL, 835, TO_DATE('18-Dec-1990', 'DD-MON-YYYY'));

insert into instructor values (
263, 'JOHNSON', 4000 , NULL, 835, TO_DATE('18-Jul-1992', 'DD-MON-YYYY'));

insert into instructor values (
515, 'SHELLEY', 3500 , 200, 700, TO_DATE('20-Jan-1991', 'DD-MON-YYYY'));

insert into instructor values (
453, 'LODGE', 2500 , 100, 835, TO_DATE('14-Sep-1988', 'DD-MON-YYYY'));

insert into instructor values (
700, 'WAYNE', 4500 , 300, NULL, TO_DATE('16-May-1981', 'DD-MON-YYYY'));

insert into instructor values (
628, 'MONROE', 3000 , NULL, 700, TO_DATE('16-Jul-1984', 'DD-MON-YYYY'));

insert into instructor values (
790, 'NEWMAN', 3100 , 300, 700, TO_DATE('16-Dec-1982', 'DD-MON-YYYY'));

insert into instructor values (
795, 'BOGART', 3200 , NULL, 700, TO_DATE('16-Dec-1983', 'DD-MON-YYYY'));

insert into instructor values (
560, 'LAUREL', 5200 , 200, 978, TO_DATE('16-Dec-1983', 'DD-MON-YYYY'));

insert into instructor values (
123, 'FLINTSTONE,FRED', 1000 , NULL, 978, TO_DATE('16-Jun-1989', 'DD-MON-YYYY'));

select * from employee;
select * from works_on;
select * from instructor;

Answer 1

ANSWER:--

GIVEN THAT:--

• 2.Select CONCAT(fname,':',minit,':',lname) as Employees from Employee;

  3.Select CONCAT(SUBSTRING(fname,1,1), '.' ,' ' ,lname) )as Employees from Employee;

  4.Select fname||' '|| lname , DATENAME(dw,bdate), FLOOR(DATEDIFF(DAY, bdate, getDate()) / 365.25) as Age from Employee;

  5.Select e1.fname, e1.minit, e1.lname from employee e1,employee e2 where e1.superssn = e2.ssn and e2.fname = 'Franklin' and lname = 'Wong';

  6.Select Name , Salary + NVL(Commission,0) as Compensation from Instructor;

  7.Select superssn,ssn,bdate from Employees where Year(bdate) >= 1926 order by superssn NULLS First , bdate desc;