1/11/2022 , 10-12 dbms lab, Experiment 5

CEC@USER:~$ sudo su
[sudo] password FOR CEC:
root@USER:/home/CEC# sqlplus sys AS sysdba

SQL*Plus: Release 11.2.0.2.0 Production ON Tue Nov 1 10:27:39 2022

Copyright (c) 1982, 2011, Oracle.  ALL rights reserved.

Enter password:

Connected TO:
Oracle DATABASE 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> DESC exp5
 Name                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                        VARCHAR2(2)
 NAME                      NOT NULL VARCHAR2(10)
 AGE                            NUMBER(38)
 TITLE                          VARCHAR2(10)
 JOINING_DATE                       DATE
 DEPT                           VARCHAR2(20)

SQL> UPDATE exp5 SET department ='D1' WHERE employee_id IN('e3','e4','e5');
UPDATE exp5 SET department ='D1' WHERE employee_id IN('e3','e4','e5')
                *
ERROR at line 1:
ORA-00904: "DEPARTMENT": invalid identifier


SQL> UPDATE exp5 SET dept ='D1' WHERE employee_id IN('e3','e4','e5');

0 ROWS updated.

SQL> seelct * FROM exp5;
SP2-0734: UNKNOWN command beginning "seelct * f..." - rest OF line ignored.
SQL> SELECT * FROM exp5;

EM NAME          AGE TITLE      JOINING_ DEPT
-- ---------- ---------- ---------- -------- --------------------
E1 Mahesh         50 Manager    20-03-67
E2 Nimitha        35 Programmer 02-08-81
E3 Ashitha        28 Programmer 01-09-88
E4 Ajay           32 Technician 23-02-75
E5 Roy            24 Programmer 05-04-90
E6 Sindhu         33 Technician 09-04-84

6 ROWS selected.

SQL> UPDATE exp5 SET dept ='D1' WHERE employee_id IN('e3','e4','e5');

0 ROWS updated.

SQL> SELECT * FROM exp5;

EM NAME          AGE TITLE      JOINING_ DEPT
-- ---------- ---------- ---------- -------- --------------------
E1 Mahesh         50 Manager    20-03-67
E2 Nimitha        35 Programmer 02-08-81
E3 Ashitha        28 Programmer 01-09-88
E4 Ajay           32 Technician 23-02-75
E5 Roy            24 Programmer 05-04-90
E6 Sindhu         33 Technician 09-04-84

6 ROWS selected.

SQL> UPDATE exp5 SET dept ='D1' WHERE employee_id IN('E3','E4','E5');

3 ROWS updated.

SQL> SELECT * FROM exp5;

EM NAME          AGE TITLE      JOINING_ DEPT
-- ---------- ---------- ---------- -------- --------------------
E1 Mahesh         50 Manager    20-03-67
E2 Nimitha        35 Programmer 02-08-81
E3 Ashitha        28 Programmer 01-09-88 D1
E4 Ajay           32 Technician 23-02-75 D1
E5 Roy            24 Programmer 05-04-90 D1
E6 Sindhu         33 Technician 09-04-84

6 ROWS selected.

SQL> commit;

Commit complete.

SQL> CREATE TABLE emptitle AS SELECT empid, title FROM emp2;
CREATE TABLE emptitle AS SELECT empid, title FROM emp2
                                *
ERROR at line 1:
ORA-00904: "EMPID": invalid identifier


SQL> CREATE TABLE emptitle AS SELECT employee_id, title FROM emp2;

TABLE created.

SQL> DESC emptitle;
 Name                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                        VARCHAR2(2)
 TITLE                          VARCHAR2(10)

SQL> SELECT * FROM emptitle;

EM TITLE
-- ----------
E1 Manager
E2 Programmer
E3 Programmer
E4 Technician
E5 Programmer
E6 Technician

6 ROWS selected.

SQL> CREATE TABLE empsalary AS SELECT employee_id, salary FROM emp2;

TABLE created.

SQL> DESC emp2;
 Name                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                   NOT NULL VARCHAR2(2)
 NAME                      NOT NULL VARCHAR2(10)
 AGE                            NUMBER(38)
 SALARY                         FLOAT(126)
 TITLE                          VARCHAR2(10)
 JOINING_DATE                       DATE

SQL> DESC exp5;
 Name                      NULL?    TYPE
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                        VARCHAR2(2)
 NAME                      NOT NULL VARCHAR2(10)
 AGE                            NUMBER(38)
 TITLE                          VARCHAR2(10)
 JOINING_DATE                       DATE
 DEPT                           VARCHAR2(20)

SQL> CREATE TABLE depttable AS SELECT dept AS dept_id FROM exp5;

TABLE created.

SQL> SELECT * FROM depttable;

DEPT_ID
--------------------


D1
D1
D1


6 ROWS selected.

SQL> DROP TABLE depttable;

TABLE dropped.

SQL> CREATE TABLE depttable AS SELECT DISTINCT dept AS dept_id FROM exp5;

TABLE created.

SQL> SELECT * FROM depttable;

DEPT_ID
--------------------

D1

SQL> DROP TABLE depttable;

TABLE dropped.

SQL> UPDATE exp5 SET dept ='D3' WHERE employee_id IN('E1');

1 ROW updated.

SQL> UPDATE exp5 SET dept ='D4' WHERE employee_id IN('E2');

1 ROW updated.

SQL> UPDATE exp5 SET dept ='D2' WHERE employee_id IN('E6');

1 ROW updated.

SQL> CREATE TABLE depttable AS SELECT DISTINCT dept AS dept_id FROM exp5;

TABLE created.

SQL> SELECT * FROM depttable;

DEPT_ID
--------------------
D1
D3
D2
D4

SQL> ALTER TABLE depttable ADD department;
ALTER TABLE depttable ADD department
                          *
ERROR at line 1:
ORA-02263: need TO specify the datatype FOR this COLUMN


SQL> ALTER TABLE depttable ADD department VARCHAR(20);

TABLE altered.

SQL> SELECT * FROM depttable orderby dept_id;
SELECT * FROM depttable orderby dept_id
                                *
ERROR at line 1:
ORA-00933: SQL command NOT properly ended


SQL> SELECT * FROM depttable ORDER BY dept_id;

DEPT_ID          DEPARTMENT
-------------------- --------------------
D1
D2
D3
D4

SQL> SELECT * FROM depttable;

DEPT_ID          DEPARTMENT
-------------------- --------------------
D1
D3
D2
D4

SQL> SELECT * FROM exp5;

EM NAME          AGE TITLE      JOINING_ DEPT
-- ---------- ---------- ---------- -------- --------------------
E1 Mahesh         50 Manager    20-03-67 D3
E2 Nimitha        35 Programmer 02-08-81 D4
E3 Ashitha        28 Programmer 01-09-88 D1
E4 Ajay           32 Technician 23-02-75 D1
E5 Roy            24 Programmer 05-04-90 D1
E6 Sindhu         33 Technician 09-04-84 D2

6 ROWS selected.

SQL> UPDATE exp5 SET dept='D4' WHERE empid='E1';
UPDATE exp5 SET dept='D4' WHERE empid='E1'
                                *
ERROR at line 1:
ORA-00904: "EMPID": invalid identifier


SQL> UPDATE exp5 SET dept='D4' WHERE employee_id='E1';

1 ROW updated.

SQL> UPDATE exp5 SET dept='D2' WHERE employee_id='E2';

1 ROW updated.

SQL> SELECT * FROM exp5;

EM NAME          AGE TITLE      JOINING_ DEPT
-- ---------- ---------- ---------- -------- --------------------
E1 Mahesh         50 Manager    20-03-67 D4
E2 Nimitha        35 Programmer 02-08-81 D2
E3 Ashitha        28 Programmer 01-09-88 D1
E4 Ajay           32 Technician 23-02-75 D1
E5 Roy            24 Programmer 05-04-90 D1
E6 Sindhu         33 Technician 09-04-84 D2

6 ROWS selected.

SQL> UPDATE depttable SET department ='Production' WHERE dept_id='D1';

1 ROW updated.

SQL> UPDATE depttable SET department ='R and D' WHERE dept_id='D2';

1 ROW updated.

SQL> UPDATE depttable SET department ='Purchasing' WHERE dept_id='D3';

1 ROW updated.

SQL> UPDATE depttable SET department ='Marketing' WHERE dept_id='D4';

1 ROW updated.

SQL> SELECT * FROM depttable;

DEPT_ID          DEPARTMENT
-------------------- --------------------
D1           Production
D3           Purchasing
D2           R AND D
D4           Marketing

 DATE
 DATE : 1/11/2022 , 10-12 dbms lab, Experiment 5 ;