Create a new version of paste:

CEC@user:~$ sudo bash
[sudo] password for CEC: 
root@user:/home/CEC# sqlplus sys as sysdba
 
SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 17 09:18:15 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> create table exp6(Eid UNIQUE varchar(2), FName varchar(10), LName varchar(10),Email varchar(20), Phone int, Hdate DATE, JId varchar(2),DId varchar(2));
create table exp6(Eid UNIQUE varchar(2), FName varchar(10), LName varchar(10),Email varchar(20), Phone int, Hdate DATE, JId varchar(2),DId varchar(2))
                             *
ERROR at line 1:
ORA-00907: missing right parenthesis
 
 
SQL> create table exp6(Eid varchar(2), FName varchar(10), LName varchar(10),Email varchar(20), Phone int, Hdate DATE, JId varchar(2),DId varchar(2));
 
Table created.
 
SQL> desc exp6
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EID                            VARCHAR2(2)
 FNAME                          VARCHAR2(10)
 LNAME                          VARCHAR2(10)
 EMAIL                          VARCHAR2(20)
 PHONE                          NUMBER(38)
 HDATE                          DATE
 JID                            VARCHAR2(2)
 DID                            VARCHAR2(2)
 
SQL> create table exp6dept as select DId from exp6;
 
Table created.
 
SQL> alter table exp6dept add(DName varchar(15), LId varchar(3));
 
Table altered.
 
SQL> desc exp6dept;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DID                            VARCHAR2(2)
 DNAME                          VARCHAR2(15)
 LID                            VARCHAR2(3)
 
SQL> create table exp6job(Eid varchar(2), SDate DATE, EDate DATE, JId varchar(2), DId varchar(2));
 
Table created.
 
SQL> rename table exp6job as exp6jobhist;
rename table exp6job as exp6jobhist
       *
ERROR at line 1:
ORA-00903: invalid table name
 
 
SQL> rename exp6job to exp6jobhist;
 
Table renamed.
 
SQL> create table exp6job(JId varchar(2), Title varchar(20), MinSalary int, MaxSalary int);
 
Table created.
 
SQL> create table region(RId varchar(3), RegionName varchar(10), Country varchar(2));
 
Table created.
 
SQL> rename region to exp6region;
 
Table renamed.
 
SQL> create table exp6loc(LId varchar(2), Laddress varchar(10), Cid varchar(2));
 
Table created.
 
SQL> create table exxp6country(Cid varchar(2), Cname varchar(10));
 
Table created.
 
SQL> insert into exp6 values("E1","Mini","P.K","mini@gmail.com",9955,'12-04-1987',"J1","D1");
insert into exp6 values("E1","Mini","P.K","mini@gmail.com",9955,'12-04-1987',"J1","D1")
                                                                                  *
ERROR at line 1:
ORA-00984: column not allowed here
 
 
SQL> desc exp6;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EID                            VARCHAR2(2)
 FNAME                          VARCHAR2(10)
 LNAME                          VARCHAR2(10)
 EMAIL                          VARCHAR2(20)
 PHONE                          NUMBER(38)
 HDATE                          DATE
 JID                            VARCHAR2(2)
 DID                            VARCHAR2(2)
 
SQL> insert into exp6 values("E1","Mini","P.K","mini@gmail.com",9955,'12-04-1987',"J1","D1");
insert into exp6 values("E1","Mini","P.K","mini@gmail.com",9955,'12-04-1987',"J1","D1")
                                                                                  *
ERROR at line 1:
ORA-00984: column not allowed here
 
 
SQL> insert into exp6 values('E1','Mini','P.K','mini@gmail.com',9955,'12-04-1987','J1','D1');
 
1 row created.
 
SQL> insert into exp6 values('E2','Savanth','Kumar','savanth@gmail.com',9867,'22-07-1990','J2','D2');
 
1 row created.
 
SQL> insert into exp6 values('E3','Jane','Joy','jane@gmail.com',9765,'23-04-1999','J2','D3');
 
1 row created.
 
SQL> insert into exp6 values('E4','Suhail','Haroon','suhail@gmail.com',9234,'01-05-2003','J3','D3');
 
1 row created.
 
SQL> insert into exp6 values('E5','Radha','Soman','radha@gmail.com',9976,'09-02-2011','J2','D1');
 
1 row created.
 
SQL> select * from exp6;
 
EI FNAME      LNAME  EMAIL             PHONE HDATE    JI DI
-- ---------- ---------- -------------------- ---------- -------- -- --
E1 Mini       P.K    mini@gmail.com         9955 12-04-87 J1 D1
E2 Savanth    Kumar  savanth@gmail.com      9867 22-07-90 J2 D2
E3 Jane       Joy    jane@gmail.com         9765 23-04-99 J2 D3
E4 Suhail     Haroon     suhail@gmail.com       9234 01-05-03 J3 D3
E5 Radha      Soman  radha@gmail.com        9976 09-02-11 J2 D1
 
SQL> insert into exp6dept values('D1','Production','L1');
 
1 row created.
 
SQL> insert into exp6dept values('D2','Marketing','L1');
 
1 row created.
 
SQL> insert into exp6dept values('D3','RandD','L1');
 
1 row created.
 
SQL> insert into exp6dept values('D1','Marketing','L2');
 
1 row created.
 
SQL> insert into exp6dept values('D2','HR','L2');
 
1 row created.
 
SQL> insert into exp6dept values('D1','Production','L3');
 
1 row created.
 
SQL> insert into exp6dept values('D2','RandD','L3');
 
1 row created.
 
SQL> insert into exp6dept values('D3','HR','L3');
 
1 row created.
 
SQL> select * from exp6dept;
 
DI DNAME       LID
-- --------------- ---
D1 Production      L1
D2 Marketing       L1
D3 RandD       L1
D1 Marketing       L2
D2 HR          L2
D1 Production      L3
D2 RandD       L3
D3 HR          L3
 
8 rows selected.
 
SQL> insert into exp6jobhist values('E1','12-04-1987','02-05-1993','J1','D1');
 
1 row created.
 
SQL> insert into exp6jobhist values('E1','02-05-1993','30-03-2008','J2','D1');
 
1 row created.
 
SQL> insert into exp6jobhist values('E1','30-03-2008','null','J3','D1');
insert into exp6jobhist values('E1','30-03-2008','null','J3','D1')
                                                 *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
 
 
SQL> insert into exp6jobhist values('E2','22-07-1990','05-06-2001','J7','D2');
 
1 row created.
 
SQL> delete from exp6jobhist where EId=E2;
delete from exp6jobhist where EId=E2
                                  *
ERROR at line 1:
ORA-00904: "E2": invalid identifier
 
 
SQL> delete from exp6jobhist where EId='E2';
 
1 row deleted.
 
SQL> select * from exp6jobhist;
 
EI SDATE    EDATE    JI DI
-- -------- -------- -- --
E1 12-04-87 02-05-93 J1 D1
E1 02-05-93 30-03-08 J2 D1
 
SQL> insert into exp6jobhist values('E1','30-03-2008',null,'J3','D1');
 
1 row created.
 
SQL> insert into exp6jobhist values('E2','22-07-1990','05-06-2001','J7','D2');
 
1 row created.
 
SQL> insert into exp6jobhist values('E2','05-06-2001',null,'J8','D3');
 
1 row created.
 
SQL> insert into exp6jobhist values('E3','23-04-1999','09-11-2004','J5','D1');
 
1 row created.
 
SQL> insert into exp6jobhist values('E3','09-11-2004',null,'J6','D3');
 
1 row created.
 
SQL> insert into exp6jobhist values('E4','01-05-2003',null,'J7','D3');
 
1 row created.
 
SQL> select * from exp6jobhist;
 
EI SDATE    EDATE    JI DI
-- -------- -------- -- --
E1 12-04-87 02-05-93 J1 D1
E1 02-05-93 30-03-08 J2 D1
E1 30-03-08      J3 D1
E2 22-07-90 05-06-01 J7 D2
E2 05-06-01      J8 D3
E3 23-04-99 09-11-04 J5 D1
E3 09-11-04      J6 D3
E4 01-05-03      J7 D3
 
8 rows selected.
 
SQL> insert into exp6job values('J1','Junior Programmer',24000,45000);
 
1 row created.
 
SQL> insert into exp6job values('J2','Senior Programmer',45000,112000);
 
1 row created.
 
SQL> insert into exp6job values('J3','Project Manager',67000,135000);
 
1 row created.
 
SQL> insert into exp6job values('J4','Junior Researcher',35000,67000);
 
1 row created.
 
SQL> insert into exp6job values('J5','Senior Researcher',65000,132000);
 
1 row created.
 
SQL> insert into exp6job values('J6','Scientist',78000,178000);
 
1 row created.
 
SQL> insert into exp6job values('J7','Manager',32000,56000);
 
1 row created.
 
SQL> insert into exp6job values('J8','Marketing Head',21000,45000);insert into exp6job values('J8','Marketing Head',21000,45000);
insert into exp6job values('J8','Marketing Head',21000,45000);insert into exp6job values('J8','Marketing Head',21000,45000)
                                                             *
ERROR at line 1:
ORA-00911: invalid character
 
 
SQL> insert into exp6job values('J8','Marketing Head',21000,45000);
 
1 row created.
 
SQL> insert into exp6job values('J9','HR Head',34000,45000);
 
1 row created.
 
SQL> select * from exp6job;
 
JI TITLE         MINSALARY  MAXSALARY
-- -------------------- ---------- ----------
J1 Junior Programmer         24000  45000
J2 Senior Programmer         45000     112000
J3 Project Manager       67000     135000
J4 Junior Researcher         35000  67000
J5 Senior Researcher         65000     132000
J6 Scientist             78000     178000
J7 Manager           32000  56000
J8 Marketing Head        21000  45000
J9 HR Head           34000  45000
 
9 rows selected.
 
SQL> insert into exp6region values('R1','North East','C1');
 
1 row created.
 
SQL> insert into exp6region values('R2','North India','C2');
insert into exp6region values('R2','North India','C2')
                                   *
ERROR at line 1:
ORA-12899: value too large for column "SYS"."EXP6REGION"."REGIONNAME" (actual:
11, maximum: 10)
 
 
SQL> desc exp6region;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RID                            VARCHAR2(3)
 REGIONNAME                     VARCHAR2(10)
 COUNTRY                        VARCHAR2(2)
 
SQL> alter table exp6region MODIFY regionname varchar(20);
 
Table altered.
 
SQL> desc exp6region
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RID                            VARCHAR2(3)
 REGIONNAME                     VARCHAR2(20)
 COUNTRY                        VARCHAR2(2)
 
SQL> insert into exp6region values('R2','North India','C2');
 
1 row created.
 
SQL> insert into exp6region values('R33','South India','C2');
 
1 row created.
 
SQL> select * from exp6region;
 
RID REGIONNAME       CO
--- -------------------- --
R1  North East       C1
R2  North India      C2
R33 South India      C2
 
SQL> insert into exp6loc values('L1','New York','C1');
 
1 row created.
 
SQL> insert into exp6loc values('L2','Delhi','C2');
 
1 row created.
 
SQL> insert into exp6loc values('L3','Kochi','C2');
 
1 row created.
 
SQL> select * from exp6loc;
 
LI LADDRESS   CI
-- ---------- --
L1 New York   C1
L2 Delhi      C2
L3 Kochi      C2
 
SQL> insert into exp6loc values('C1','India');
insert into exp6loc values('C1','India')
            *
ERROR at line 1:
ORA-00947: not enough values
 
 
SQL> insert into exp6country values('C1','India');
insert into exp6country values('C1','India')
            *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
SQL> rename exxp6country to exp6country;
 
Table renamed.
 
SQL> insert into exp6country values('C1','India');
 
1 row created.
 
SQL> insert into exp6country values('C2','USA');
 
1 row created.
 
SQL> select * from exp6country;
 
CI CNAME
-- ----------
C1 India
C2 USA

alter table exp6 add primary key(eid);
alter table exp6dept add primary key(did,lid);
alter table exp6job add primary key(jid);
alter table exp6region add primary key(rid);
alter table exp6loc add primary key(lid);
alter table exp6country add primary key(cid);
alter table exp6jobhist add foreign key(eid) references exp6(eid);
alter table exp6dept add foreign key(lid) references exp6loc(lid);
alter table exp6loc add foreign key(cid) references exp6country(cid);
alter table exp6region add foreign key(cid) references exp6country(cid);
 
SQL> DBMS lab nov 17 2022