Oracle Database 10g SQL Fundamentals I - Practice 8

 

Oracle Database 10g SQL Fundamentals I - Practice 8

1.
CREATE TABLE MY_EMPLOYEE
(ID NUMBER(4)
 , LAST_NAME VARCHAR2(25)
 , FIRST_NAME VARCHAR2(25)
 , USER_ID VARCHAR2(8)
 , SALARY NUMBER(9, 2)
)
;

 

 

2.
DESC MY_EMPLOYEE

 

 

3.
INSERT INTO MY_EMPLOYEE(ID
, LAST_NAME
, FIRST_NAME
, USER_ID
, SALARY) VALUES(1, 'Patel', 'Ralph', 'rpatel', 895)
;

 

4.
INSERT INTO MY_EMPLOYEE(ID
, LAST_NAME
, FIRST_NAME
, USER_ID
, SALARY) VALUES(2, 'Dancs', 'Betty', 'bdancs', 860)
;

 

5.
SELECT * FROM MY_EMPLOYEE;

 

6.
INSERT INTO MY_EMPLOYEE
VALUES(&P_ID
, '&&P_LAST_NAME'
, '&&P_FIRST_NAME'
, LOWER(SUBSTR('&P_FIRST_NAME', 1, 1) ||
SUBSTR('&P_LAST_NAME', 1, 7)), &P_SALARY);

 

7.
INSERT INTO MY_EMPLOYEE(ID
, LAST_NAME
, FIRST_NAME
, USER_ID
, SALARY) VALUES(3, 'Biri', 'Ben', 'bbiri', 1100)
;

INSERT INTO MY_EMPLOYEE(ID
, LAST_NAME
, FIRST_NAME
, USER_ID
, SALARY) VALUES(4, 'Newman', 'Chad', 'cnewman', 750)
;

 

8.
SELECT * FROM MY_EMPLOYEE;

 

 

9.
COMMIT

 

 

10.
UPDATE MY_EMPLOYEE
SET LAST_NAME = 'Drexler'
WHERE ID = 3;

 

 

11.
UPDATE MY_EMPLOYEE
SET SALARY = 1000
WHERE SALARY < 900;

 

12.
DELETE FROM MY_EMPLOYEE WHERE LAST_NAME='Dancs';

 

 

15. COMMIT

 

 

17.
INSERT INTO MY_EMPLOYEE(ID
, LAST_NAME
, FIRST_NAME
, USER_ID
, SALARY) VALUES(5, 'Ropeburn', 'Audrey', 'aropebur', 1550)
;

 

18~23
SAVEPOINT STEP_18;
DELETE FROM MY_EMPLOYEE;
SELECT * FROM MY_EMPLOYEE;
ROLLBACK TO STEP_18;
SELECT * FROM EMPLOYEE;
COMMIT;