Hour 5, "Manipulating Data"

Quiz Answers

1: Use the EMPLOYEE_TBL with the following structure:
COLUMNDATA TYPE(NOT)NULL
LAST_NAMEVARCHAR2(20)NOT NULL
FIRST_NAMEVARCHAR2(20)NOT NULL
SSNCHAR(9)NOT NULL
PHONENUMBERNULL

LAST_NAMEFIRST_NAMESSNPHONE
SMITHJOHN3124567883174549923
ROBERTSLISA2321188573175452321
SMITHSUE4432219893178398712
PIERCEBILLY310239856317676390

What would happen if the following statements were run:

  1. insert into employee_tbl
('JACKSON', 'STEVE', '313546078', '3178523443'),
										
  2. insert into employee_tbl values
('JACKSON', 'STEVE', '313546078', '3178523443'),
										
  3. insert into employee_tbl values
('MILLER', 'DANIEL', '230980012', NULL);
										
  4. insert into employee_tbl values
('TAYLOR', NULL, '445761212', '3179221331'),
										
  5. delete from employee_tbl;
										
  6. delete from employee_tbl;
where last_name = 'SMITH';
										
  7. delete from employee_tbl;
where last_name = 'SMITH';
and first_name = 'JOHN';
										
  8. update employee_tbl
set last_name = 'CONRAD';
									
  9. update employee_tbl
set last_name = 'CONRAD';
where last_name = 'SMITH';
										
  10. update employee_tbl
set last_name = 'CONRAD';
first_name = 'LARRY';
										
  11. update employee_tbl
set last_name = 'CONRAD';
first_name = 'LARRY';
where ssn = '313546078'
A1:

  1. The INSERT statement would not run because the key word VALUES is missing in the syntax.

  2. One row would be inserted into the EMPLOYEE_TBL.

  3. One row would be inserted into the EMPLOYEE_TBL, with a NULL value in the PHONE column.

  4. The INSERT statement would not process because the FIRST_NAME column is NOT NULL.

  5. All rows in the EMPLOYEE_TBL would be deleted.

  6. All employees with the last name of SMITH would be deleted from the EMPLOYEE_TBL.

  7. Only JOHN SMITH would be deleted from the EMPLOYEE_TBL.

  8. All last names would be changed to CONRAD.

  9. Both JOHN and SUE SMITH would now be JOHN and SUE CONRAD.

  10. All employees are now LARRY CONRAD.

  11. JOHN SMITH is now LARRY CONRAD.

Exercise Answers

2:Using the EMPLOYEE_TBL with the following structure:
COLUMNDATA TYPE(NOT)NULL
LAST_NAMEVARCHAR2(20)NOT NULL
FIRST_NAMEVARCHAR2(20)NOT NULL
SSNCHAR(9)NOT NULL
PHONENUMBER (10)NULL

LAST_NAMEFIRST_NAMESSNPHONE
SMITHJOHN3124567883174549923
ROBERTSLISA2321188573175452321
SMITHSUE4432219893178398712
PIERCEBILLY3102398563176763990

Write DML to accomplish the following:

  1. Change Billy Pierce's SSN to 310239857.

  2. Add Ben Moore to the EMPLOYEE_TBL, PHONE_NUMBER is 317-564-9880, SSN = 313456789.

  3. John Smith quit; remove his record.

A: 
  1. UPDATE EMPLOYEE_TBL 
SET SSN = '310239857'
WHERE SSN = '310239856';
									
  2. INSERT INTO EMPLOYEE_TBL VALUES 
('MOORE', 'BEN', '313456789', 
'3175649880'),
									
  3. DELETE FROM EMPLOYEE_TBL 
WHERE SSN = '312456788';
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset