Solved question oracle practice 09.

1.

SQL> CREATE TABLE my_employee

  2  (id NUMBER(4) CONSTRAINT my_employee_id_nn NOT NULL,

  3  last_name VARCHAR2(25),

  4  first_name VARCHAR2(25),

  5  userid VARCHAR2(8),

  6  salary NUMBER(9,2));

Table created.

2.

SQL> DESC MY_EMPLOYEE;

 Name                                      Null?    Type

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

 ID                                        NOT NULL NUMBER(4)

 LAST_NAME                                          VARCHAR2(25)

 FIRST_NAME                                         VARCHAR2(25)

 USERID                                             VARCHAR2(8)

 SALARY                                             NUMBER(9,2)

3.

SQL> INSERT INTO my_employee

  2  VALUES (2, 'dancs', 'betty', 'bdancs', 860);

1 row created.

SQL> INSERT INTO my_employee

  2  VALUES (3, 'biri', 'ben', 'bbiri', 1100);

1 row created.

SQL> INSERT INTO my_employee

  2  VALUES (4, 'newman', 'chad', 'cnewman', 750);

1 row created.

SQL> INSERT INTO my_employee

  2  VALUES (3, 'ropeburn', 'audrey', 'aropebur', 1550);

1 row created.

4.

SQL> INSERT INTO my_employee (id, last_name, first_name,

  2  userid, salary)

  3  VALUES (2, 'Dancs', 'Betty', 'bdancs', 860);

1 row created.

5.

SQL> SELECT *

  2  FROM my_employee;

6.

SQL> SET ECHO OFF

SQL> SET VERIFY OFF

SQL> INSERT INTO my_employee

  2  VALUES (&p_id, '&p_last_name', '&p_first_name',

  3  lower(substr('&p_first_name', 1, 1) ||

  4  substr('&p_last_name', 1, 7)), &p_salary);

Enter value for p_id: SET VERIFY ON

Enter value for p_last_name: SET ECHO ON

Enter value for p_first_name:

7 . 

SQL> SET ECHO OFF

SQL> SET VERIFY OFF

SQL> INSERT INTO my_employee

  2  VALUES (&p_id, '&p_last_name', '&p_first_name',

  3  lower(substr('&p_first_name', 1, 1) ||

  4  substr('&p_last_name', 1, 7)), &p_salary);

Enter value for p_id: SET VERIFY ON

Enter value for p_last_name: SET ECHO ON

Enter value for p_first_name:

8.

SQL> SELECT *

  2  FROM my_employee;

9.

SQL> COMMIT;

Commit complete.

10.

SQL> UPDATE my_employee

  2  SET last_name = 'Drexler'

  3  WHERE id = 3;

11.

SQL> UPDATE MY_EMPLOYEE SET SALARY=1000 WHERE SALARY<900;

3 rows updated.

12.

SQL> SELECT last_name, salary

  2  FROM my_employee;

13.

SQL> DELETE MY_EMPLOYEE WHERE LAST_NAME='dancs' AND FIRST_NAME='betty';

1 row deleted.

14.

SQL> SELECT *

  2  FROM my_employee;

15.

SQL>COMMIT;

16.

SQL> SET ECHO OFF

SQL> SET VERIFY OFF

SQL> INSERT INTO my_employee

  2  VALUES (&p_id, '&p_last_name', '&p_first_name',

  3  lower(substr('&p_first_name', 1, 1) ||

  4  substr('&p_last_name', 1, 7)), &p_salary);

Enter value for p_id: SET VERIFY ON

Enter value for p_last_name: SET ECHO ON

17.

SQL> SELECT *

  2  FROM my_employee;

18.

SQL> SAVEPOINT step_18;

Savepoint created.

  

19

SQL> DELETE MY_EMPLOYEE;

4 rows deleted.

20.

SQL> SELECT *

  2  FROM my_employee;

21.

SQL> ROLLBACK TO step_18;

Rollback complete.

22.

SQL> SELECT *

  2  FROM my_employee;

23.

SQL>COMMIT;

Thanks To Mr:- Marcosoft 

For anything ask,just whatsapp contact me via my email 

marcosoft41@gmail.com

+255764292989