API tools faq
paste
Advertisement
4doorsmorehories

DBMS lab nov 17

4doorsmorehories
Nov 23rd, 2022
77
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 12.22 KB | Source Code | 0 0
raw download clone embed print report
  1. CEC@user:~$ sudo bash
  2. [sudo] password for CEC:
  3. root@user:/home/CEC# sqlplus sys as sysdba
  4.  
  5. SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 17 09:18:15 2022
  6.  
  7. Copyright (c) 1982, 2011, Oracle.  All rights reserved.
  8.  
  9. Enter password:
  10.  
  11. Connected to:
  12. Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
  13.  
  14. 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));
  15. 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))
  16.                              *
  17. ERROR at line 1:
  18. ORA-00907: missing right parenthesis
  19.  
  20.  
  21. 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));
  22.  
  23. Table created.
  24.  
  25. SQL> desc exp6
  26.  Name                      Null?    Type
  27.  ----------------------------------------- -------- ----------------------------
  28.  EID                            VARCHAR2(2)
  29.  FNAME                          VARCHAR2(10)
  30.  LNAME                          VARCHAR2(10)
  31.  EMAIL                          VARCHAR2(20)
  32.  PHONE                          NUMBER(38)
  33.  HDATE                          DATE
  34.  JID                            VARCHAR2(2)
  35.  DID                            VARCHAR2(2)
  36.  
  37. SQL> create table exp6dept as select DId from exp6;
  38.  
  39. Table created.
  40.  
  41. SQL> alter table exp6dept add(DName varchar(15), LId varchar(3));
  42.  
  43. Table altered.
  44.  
  45. SQL> desc exp6dept;
  46.  Name                      Null?    Type
  47.  ----------------------------------------- -------- ----------------------------
  48.  DID                            VARCHAR2(2)
  49.  DNAME                          VARCHAR2(15)
  50.  LID                            VARCHAR2(3)
  51.  
  52. SQL> create table exp6job(Eid varchar(2), SDate DATE, EDate DATE, JId varchar(2), DId varchar(2));
  53.  
  54. Table created.
  55.  
  56. SQL> rename table exp6job as exp6jobhist;
  57. rename table exp6job as exp6jobhist
  58.        *
  59. ERROR at line 1:
  60. ORA-00903: invalid table name
  61.  
  62.  
  63. SQL> rename exp6job to exp6jobhist;
  64.  
  65. Table renamed.
  66.  
  67. SQL> create table exp6job(JId varchar(2), Title varchar(20), MinSalary int, MaxSalary int);
  68.  
  69. Table created.
  70.  
  71. SQL> create table region(RId varchar(3), RegionName varchar(10), Country varchar(2));
  72.  
  73. Table created.
  74.  
  75. SQL> rename region to exp6region;
  76.  
  77. Table renamed.
  78.  
  79. SQL> create table exp6loc(LId varchar(2), Laddress varchar(10), Cid varchar(2));
  80.  
  81. Table created.
  82.  
  83. SQL> create table exxp6country(Cid varchar(2), Cname varchar(10));
  84.  
  85. Table created.
  86.  
  87. SQL> insert into exp6 values("E1","Mini","P.K","mini@gmail.com",9955,'12-04-1987',"J1","D1");
  88. insert into exp6 values("E1","Mini","P.K","mini@gmail.com",9955,'12-04-1987',"J1","D1")
  89.                                                                                   *
  90. ERROR at line 1:
  91. ORA-00984: column not allowed here
  92.  
  93.  
  94. SQL> desc exp6;
  95.  Name                      Null?    Type
  96.  ----------------------------------------- -------- ----------------------------
  97.  EID                            VARCHAR2(2)
  98.  FNAME                          VARCHAR2(10)
  99.  LNAME                          VARCHAR2(10)
  100.  EMAIL                          VARCHAR2(20)
  101.  PHONE                          NUMBER(38)
  102.  HDATE                          DATE
  103.  JID                            VARCHAR2(2)
  104.  DID                            VARCHAR2(2)
  105.  
  106. SQL> insert into exp6 values("E1","Mini","P.K","mini@gmail.com",9955,'12-04-1987',"J1","D1");
  107. insert into exp6 values("E1","Mini","P.K","mini@gmail.com",9955,'12-04-1987',"J1","D1")
  108.                                                                                   *
  109. ERROR at line 1:
  110. ORA-00984: column not allowed here
  111.  
  112.  
  113. SQL> insert into exp6 values('E1','Mini','P.K','mini@gmail.com',9955,'12-04-1987','J1','D1');
  114.  
  115. 1 row created.
  116.  
  117. SQL> insert into exp6 values('E2','Savanth','Kumar','savanth@gmail.com',9867,'22-07-1990','J2','D2');
  118.  
  119. 1 row created.
  120.  
  121. SQL> insert into exp6 values('E3','Jane','Joy','jane@gmail.com',9765,'23-04-1999','J2','D3');
  122.  
  123. 1 row created.
  124.  
  125. SQL> insert into exp6 values('E4','Suhail','Haroon','suhail@gmail.com',9234,'01-05-2003','J3','D3');
  126.  
  127. 1 row created.
  128.  
  129. SQL> insert into exp6 values('E5','Radha','Soman','radha@gmail.com',9976,'09-02-2011','J2','D1');
  130.  
  131. 1 row created.
  132.  
  133. SQL> select * from exp6;
  134.  
  135. EI FNAME      LNAME  EMAIL             PHONE HDATE    JI DI
  136. -- ---------- ---------- -------------------- ---------- -------- -- --
  137. E1 Mini       P.K    mini@gmail.com         9955 12-04-87 J1 D1
  138. E2 Savanth    Kumar  savanth@gmail.com      9867 22-07-90 J2 D2
  139. E3 Jane       Joy    jane@gmail.com         9765 23-04-99 J2 D3
  140. E4 Suhail     Haroon     suhail@gmail.com       9234 01-05-03 J3 D3
  141. E5 Radha      Soman  radha@gmail.com        9976 09-02-11 J2 D1
  142.  
  143. SQL> insert into exp6dept values('D1','Production','L1');
  144.  
  145. 1 row created.
  146.  
  147. SQL> insert into exp6dept values('D2','Marketing','L1');
  148.  
  149. 1 row created.
  150.  
  151. SQL> insert into exp6dept values('D3','RandD','L1');
  152.  
  153. 1 row created.
  154.  
  155. SQL> insert into exp6dept values('D1','Marketing','L2');
  156.  
  157. 1 row created.
  158.  
  159. SQL> insert into exp6dept values('D2','HR','L2');
  160.  
  161. 1 row created.
  162.  
  163. SQL> insert into exp6dept values('D1','Production','L3');
  164.  
  165. 1 row created.
  166.  
  167. SQL> insert into exp6dept values('D2','RandD','L3');
  168.  
  169. 1 row created.
  170.  
  171. SQL> insert into exp6dept values('D3','HR','L3');
  172.  
  173. 1 row created.
  174.  
  175. SQL> select * from exp6dept;
  176.  
  177. DI DNAME       LID
  178. -- --------------- ---
  179. D1 Production      L1
  180. D2 Marketing       L1
  181. D3 RandD       L1
  182. D1 Marketing       L2
  183. D2 HR          L2
  184. D1 Production      L3
  185. D2 RandD       L3
  186. D3 HR          L3
  187.  
  188. 8 rows selected.
  189.  
  190. SQL> insert into exp6jobhist values('E1','12-04-1987','02-05-1993','J1','D1');
  191.  
  192. 1 row created.
  193.  
  194. SQL> insert into exp6jobhist values('E1','02-05-1993','30-03-2008','J2','D1');
  195.  
  196. 1 row created.
  197.  
  198. SQL> insert into exp6jobhist values('E1','30-03-2008','null','J3','D1');
  199. insert into exp6jobhist values('E1','30-03-2008','null','J3','D1')
  200.                                                  *
  201. ERROR at line 1:
  202. ORA-01858: a non-numeric character was found where a numeric was expected
  203.  
  204.  
  205. SQL> insert into exp6jobhist values('E2','22-07-1990','05-06-2001','J7','D2');
  206.  
  207. 1 row created.
  208.  
  209. SQL> delete from exp6jobhist where EId=E2;
  210. delete from exp6jobhist where EId=E2
  211.                                   *
  212. ERROR at line 1:
  213. ORA-00904: "E2": invalid identifier
  214.  
  215.  
  216. SQL> delete from exp6jobhist where EId='E2';
  217.  
  218. 1 row deleted.
  219.  
  220. SQL> select * from exp6jobhist;
  221.  
  222. EI SDATE    EDATE    JI DI
  223. -- -------- -------- -- --
  224. E1 12-04-87 02-05-93 J1 D1
  225. E1 02-05-93 30-03-08 J2 D1
  226.  
  227. SQL> insert into exp6jobhist values('E1','30-03-2008',null,'J3','D1');
  228.  
  229. 1 row created.
  230.  
  231. SQL> insert into exp6jobhist values('E2','22-07-1990','05-06-2001','J7','D2');
  232.  
  233. 1 row created.
  234.  
  235. SQL> insert into exp6jobhist values('E2','05-06-2001',null,'J8','D3');
  236.  
  237. 1 row created.
  238.  
  239. SQL> insert into exp6jobhist values('E3','23-04-1999','09-11-2004','J5','D1');
  240.  
  241. 1 row created.
  242.  
  243. SQL> insert into exp6jobhist values('E3','09-11-2004',null,'J6','D3');
  244.  
  245. 1 row created.
  246.  
  247. SQL> insert into exp6jobhist values('E4','01-05-2003',null,'J7','D3');
  248.  
  249. 1 row created.
  250.  
  251. SQL> select * from exp6jobhist;
  252.  
  253. EI SDATE    EDATE    JI DI
  254. -- -------- -------- -- --
  255. E1 12-04-87 02-05-93 J1 D1
  256. E1 02-05-93 30-03-08 J2 D1
  257. E1 30-03-08      J3 D1
  258. E2 22-07-90 05-06-01 J7 D2
  259. E2 05-06-01      J8 D3
  260. E3 23-04-99 09-11-04 J5 D1
  261. E3 09-11-04      J6 D3
  262. E4 01-05-03      J7 D3
  263.  
  264. 8 rows selected.
  265.  
  266. SQL> insert into exp6job values('J1','Junior Programmer',24000,45000);
  267.  
  268. 1 row created.
  269.  
  270. SQL> insert into exp6job values('J2','Senior Programmer',45000,112000);
  271.  
  272. 1 row created.
  273.  
  274. SQL> insert into exp6job values('J3','Project Manager',67000,135000);
  275.  
  276. 1 row created.
  277.  
  278. SQL> insert into exp6job values('J4','Junior Researcher',35000,67000);
  279.  
  280. 1 row created.
  281.  
  282. SQL> insert into exp6job values('J5','Senior Researcher',65000,132000);
  283.  
  284. 1 row created.
  285.  
  286. SQL> insert into exp6job values('J6','Scientist',78000,178000);
  287.  
  288. 1 row created.
  289.  
  290. SQL> insert into exp6job values('J7','Manager',32000,56000);
  291.  
  292. 1 row created.
  293.  
  294. SQL> insert into exp6job values('J8','Marketing Head',21000,45000);insert into exp6job values('J8','Marketing Head',21000,45000);
  295. insert into exp6job values('J8','Marketing Head',21000,45000);insert into exp6job values('J8','Marketing Head',21000,45000)
  296.                                                              *
  297. ERROR at line 1:
  298. ORA-00911: invalid character
  299.  
  300.  
  301. SQL> insert into exp6job values('J8','Marketing Head',21000,45000);
  302.  
  303. 1 row created.
  304.  
  305. SQL> insert into exp6job values('J9','HR Head',34000,45000);
  306.  
  307. 1 row created.
  308.  
  309. SQL> select * from exp6job;
  310.  
  311. JI TITLE         MINSALARY  MAXSALARY
  312. -- -------------------- ---------- ----------
  313. J1 Junior Programmer         24000  45000
  314. J2 Senior Programmer         45000     112000
  315. J3 Project Manager       67000     135000
  316. J4 Junior Researcher         35000  67000
  317. J5 Senior Researcher         65000     132000
  318. J6 Scientist             78000     178000
  319. J7 Manager           32000  56000
  320. J8 Marketing Head        21000  45000
  321. J9 HR Head           34000  45000
  322.  
  323. 9 rows selected.
  324.  
  325. SQL> insert into exp6region values('R1','North East','C1');
  326.  
  327. 1 row created.
  328.  
  329. SQL> insert into exp6region values('R2','North India','C2');
  330. insert into exp6region values('R2','North India','C2')
  331.                                    *
  332. ERROR at line 1:
  333. ORA-12899: value too large for column "SYS"."EXP6REGION"."REGIONNAME" (actual:
  334. 11, maximum: 10)
  335.  
  336.  
  337. SQL> desc exp6region;
  338.  Name                      Null?    Type
  339.  ----------------------------------------- -------- ----------------------------
  340.  RID                            VARCHAR2(3)
  341.  REGIONNAME                     VARCHAR2(10)
  342.  COUNTRY                        VARCHAR2(2)
  343.  
  344. SQL> alter table exp6region MODIFY regionname varchar(20);
  345.  
  346. Table altered.
  347.  
  348. SQL> desc exp6region
  349.  Name                      Null?    Type
  350.  ----------------------------------------- -------- ----------------------------
  351.  RID                            VARCHAR2(3)
  352.  REGIONNAME                     VARCHAR2(20)
  353.  COUNTRY                        VARCHAR2(2)
  354.  
  355. SQL> insert into exp6region values('R2','North India','C2');
  356.  
  357. 1 row created.
  358.  
  359. SQL> insert into exp6region values('R33','South India','C2');
  360.  
  361. 1 row created.
  362.  
  363. SQL> select * from exp6region;
  364.  
  365. RID REGIONNAME       CO
  366. --- -------------------- --
  367. R1  North East       C1
  368. R2  North India      C2
  369. R33 South India      C2
  370.  
  371. SQL> insert into exp6loc values('L1','New York','C1');
  372.  
  373. 1 row created.
  374.  
  375. SQL> insert into exp6loc values('L2','Delhi','C2');
  376.  
  377. 1 row created.
  378.  
  379. SQL> insert into exp6loc values('L3','Kochi','C2');
  380.  
  381. 1 row created.
  382.  
  383. SQL> select * from exp6loc;
  384.  
  385. LI LADDRESS   CI
  386. -- ---------- --
  387. L1 New York   C1
  388. L2 Delhi      C2
  389. L3 Kochi      C2
  390.  
  391. SQL> insert into exp6loc values('C1','India');
  392. insert into exp6loc values('C1','India')
  393.             *
  394. ERROR at line 1:
  395. ORA-00947: not enough values
  396.  
  397.  
  398. SQL> insert into exp6country values('C1','India');
  399. insert into exp6country values('C1','India')
  400.             *
  401. ERROR at line 1:
  402. ORA-00942: table or view does not exist
  403.  
  404.  
  405. SQL> rename exxp6country to exp6country;
  406.  
  407. Table renamed.
  408.  
  409. SQL> insert into exp6country values('C1','India');
  410.  
  411. 1 row created.
  412.  
  413. SQL> insert into exp6country values('C2','USA');
  414.  
  415. 1 row created.
  416.  
  417. SQL> select * from exp6country;
  418.  
  419. CI CNAME
  420. -- ----------
  421. C1 India
  422. C2 USA
  423.  
  424. alter table exp6 add primary key(eid);
  425. alter table exp6dept add primary key(did,lid);
  426. alter table exp6job add primary key(jid);
  427. alter table exp6region add primary key(rid);
  428. alter table exp6loc add primary key(lid);
  429. alter table exp6country add primary key(cid);
  430. alter table exp6jobhist add foreign key(eid) references exp6(eid);
  431. alter table exp6dept add foreign key(lid) references exp6loc(lid);
  432. alter table exp6loc add foreign key(cid) references exp6country(cid);
  433. alter table exp6region add foreign key(cid) references exp6country(cid);
  434.  
  435. SQL> DBMS lab nov 17 2022
Tags: dbmslab
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!