Required : Copy and paste the PL/SQL code on the space provided after each quest
ID: 3850558 • Letter: R
Question
Required:
Copy and paste the PL/SQL code on the space provided after each questions ad explain each number with question that requires you to answer in essay form.
Create a table name as: EMPLOYEE_RECORD with the following column as shown below:
NOT NULL
2. Insert the following Data as shown in the table below:
Lex
De haan
3. Add new column name as ADDRESS data type char size 20.
4. After you add a new column address view now the table? What happen to the addresses of all old employees? Explain the reason why?
5. Add new record as shown below:
105
JULY
SORIANO
MSORIANO
TEACHER
2600
80
BRGY.PAMBUAN GAPAN
Ø Is inserting a new record in No.5 is possible? Why and why not? Explain the reason why?
6. Add again a new record as shown below:
105
ARRIANE
SALAMAT
ASALAMAT
TEACHER
2600
80
BRGY.MANGINO
Ø Is inserting a new record in No.6 is possible? Why and why not? Explain the reason why?
7.. Add again a new record as shown below:
106
RAYCHELOU
VALENCIA
RVALENCIA
DEAN
50000
80
CANIOGAN ST. DR. SIXTO BLDG. PASIG
Ø Is inserting a new record in No. 8 is possible? If not perform some modification on table structure in order to insert the record of Ms. Valencia.
8. All employees that having a salary of 5800 should be assigned in one dept_id 90, update their departments to 90.
9. Trina got married, after her leave Ms. Trina ask you to update her record in the database from RAJS to DE LEON.
10. After serving the company for three year Randal got promoted as the newest ad_pres and with a new salary of 250000. Update his record using one SQL only.
11. Since Mr. Randal replace the position of Mr. Steven, delete the record of Mr. Steven since he is no longer connected to the company.
12 All employees are given an additional 2% increase in their salaries. Create an SQL statement that would change the employees’ salaries with additional 10%. Note: Use rows with the same values in order to limit the number of query to be use.
13 Email address is not being use, and save space you have to delete the said column.
14 Select the table now and draw the final output after you perform the DDL and DML statements.
15. What will happen if the accidentally type DELETE * FROM EMPLOYEES without issuing WHERE condition?
ID FIRSTNAME LASTNAME EMAIL JOB_ID SALARY DEPT_ID Number(6) Varchar(10) Varchar(10) Varchar(15) Varchar15 Number(6) Number(6) PK
NOT NULL
Explanation / Answer
ANSWER FOR 1.
Create table EMPLOYEE_RECORD
( ID INT (6),
FIRSTNAME VARCHAR(10) NOT NULL ,
LASTNAME VARCHAR(10),
EMAIL VARCHAR(15),
JOB_ID VARCHAR(15),
SALARY INT (6),
DEPT_ID INT (6),
PRIMARY KEY (ID)
)
ANSWER FOR 2.
INSERT INTO EMPLOYEE_RECORD (ID, FIRSTNAME, LASTNAME, EMAIL, JOB_ID, SALARY, DEPT_ID)
VALUES (100, 'Steven', 'King', 'sking', 'Ad_press', 240000, 10)
INSERT INTO EMPLOYEE_RECORD (ID, FIRSTNAME, LASTNAME, EMAIL, JOB_ID, SALARY, DEPT_ID)
VALUES (101, 'Nena', 'Kochar','Nkochar', 'Ad_vp', 17000, 20)
INSERT INTO EMPLOYEE_RECORD (ID, FIRSTNAME, LASTNAME, EMAIL, JOB_ID, SALARY, DEPT_ID)
VALUES (102, 'Lex', 'De haan', 'Ldehaan', 'Ad_vp', 17000, 50)
INSERT INTO EMPLOYEE_RECORD (ID, FIRSTNAME, LASTNAME, EMAIL, JOB_ID, SALARY, DEPT_ID)
VALUES (103, 'Alexander', 'Hunold', 'Ahunold', 'It_prog', 9000, 60)
INSERT INTO EMPLOYEE_RECORD (ID, FIRSTNAME, LASTNAME, EMAIL, JOB_ID, SALARY, DEPT_ID)
VALUES (104, 'Bruce', 'Ernst', 'Bernst', 'It_prog', 6000, 80)
INSERT INTO EMPLOYEE_RECORD (ID, FIRSTNAME, LASTNAME, EMAIL, JOB_ID, SALARY, DEPT_ID)
VALUES (107, 'Diana', 'Lorentz', 'Dlorentz', 'St_man', 5800, 90)
INSERT INTO EMPLOYEE_RECORD (ID, FIRSTNAME, LASTNAME, EMAIL, JOB_ID, SALARY, DEPT_ID)
VALUES (124, 'Kevin', 'Mourgos', 'Kmourgos', 'St_clerk', 5800, 110)
INSERT INTO EMPLOYEE_RECORD (ID, FIRSTNAME, LASTNAME, EMAIL, JOB_ID, SALARY, DEPT_ID)
VALUES (141, 'Trina', 'Rajs' , 'Trajs', 'St_clerk', 5800, 190)
INSERT INTO EMPLOYEE_RECORD (ID, FIRSTNAME, LASTNAME, EMAIL, JOB_ID, SALARY, DEPT_ID)
VALUES (142, 'Curtis', 'Davias', 'Cdavias', 'St_clerk', 5800, 191)
INSERT INTO EMPLOYEE_RECORD (ID, FIRSTNAME, LASTNAME, EMAIL, JOB_ID, SALARY, DEPT_ID)
VALUES (143, 'Randal','Matos', 'Rmatos','St_man', 4200, 195)
ANSWER FOR 3.
ALTER TABLE EMPLOYEE_RECORD
ADD ADDRESS VARCHAR(20);
ANSWER FOR 4.
To create a view of the table the query should be :
Select * from EMPLOYEE_RECORD;
Address of all old employees will show Null value .
As there was no data entered for the new column it will shoe null value by default .
ANSWER FOR 5.
INSERT INTO EMPLOYEE_RECORD (ID, FIRSTNAME, LASTNAME, EMAIL, JOB_ID, SALARY, DEPT_ID, ADDRESS)
VALUES (105, 'JULY','SORIANO', 'MSORIANO','TEACHER', 2600, 80,'BRGY.PAMBUAN GAPAN')
Inserting record in the table is possible using Insert command in the table as all the new values are unique in the table and the primary key ID is also unique .
ANSWER FOR 6.
Inserting this record is not possible as the duplicate primary key ID = 105 can not be inserted in a table .
ANSWER FOR 7.
No it is not possible to insert the data , In order to insert have to run the following query
ALTER TABLE EMPLOYEE_RECORD
ALTER COLUMN ADDRESS nvarchar(60);
ANSWER FOR 8.
UPDATE EMPLOYEE_RECORD
SET DEPT_ID = 90
WHERE SALARY = 5800
ANSWER FOR 9.
UPDATE EMPLOYEE_RECORD
SET LASTNAME = 'DE LEON'
WHERE FIRSTNAME = 'Trina'
ANSWER FOR 10.
UPDATE EMPLOYEE_RECORD
SET SALARY = 250000
WHERE FIRSTNAME = 'Randal'
ANSWER FOR 11.
DELETE FROM EMPLOYEE_RECORD
WHERE FIRSTNAME = 'Steven'
ANSWER FOR 12.
UPDATE EMPLOYEE_RECORD
SET SALARY = SALARY * 1.1
ANSWER FOR 13.
ALTER TABLE EMPLOYEE_RECORD
DROP COLUMN EMAIL
ANSWER FOR 14.
After all the ddl and dml statement the final table can be seen by the following query :
SELECT * FROM EMPLOYEE_RECORD
ANSWER FOR 15.
It will delete all the rows from the table .
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.