DpEjnnYa

1DEPARTMENT OF COMPUTER SCIENCE AND
2ENGINEERING
3LAB MANUAL
4Academic Year: 2018-19 ODD SEMESTERS
5
6Program (UG/PG)		: UG
7Semester			: V
8Course Code			: 15IT302J
9Course Title			: DATABASE MANAGEMENT SYSTEM
10
11Prepared By
12R.SUBASH,
13Assistant Professor (O.G),
14Department of Computer Science and Engineering.
15
16
17
18
19
20FACULTY OF ENGINEERING AND TECHNOLOGY
21SRM UNIVERSITY
22(Under section 3 of UGC Act, 1956)
23
24SRM Nagar, Kattankulathur- 603203
25Kancheepuram District
26
27
28
29
30
31
32LIST OF EXPERIMENTS& SCHEDULE
33Course Code: 15IT302J
34
35Course Title: Database Management System 
36
37Exp. No.	Title	Week No.
381	Creating database table	1
392	Working with Data Manipulation commands	2
403	Basic SELECT statements	3
414	Advanced SELECT statements	4
425	Integrity and Constraints	5
436	Joining Tables	6
447	SQL functions	7
458	Sub queries	8
469	Views	9
4710	Basics of PL/SQL	10,11
4811	Design and Develop applications	12,13
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63HARDWARE AND SOFTWARE REQUIREMENTS
64
65HARDWARE REQUIREMENTS: 
66
67INTEL PENTIUM 915 GV 
6880GB HDD
69 512MB DDR 
70
71SOFTWARE REQUIREMENTS:
72
73 ORACLE 8i, 9i. 
74MY SQL,
75 DB2
76
77
78
79
80
81
82
83
84
85
86
87
88INTERNAL ASSESSMENT MARK SPLIT UP
89
90Observation: 30 Marks + Mini Project with the Report: 10 Marks	: 40 Marks 
91Model Exam	: 10 Marks 
92Record		: 5 Marks
93 					         MCQ / Quiz / Viva Voice	:  5Marks
94TOTAL MARKS	: 60 Marks
95
96
97
98EXTERNAL ASSESSMENT MARK				: 40 Marks
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113EX.NO:1	CREATING DATABASE TABLE
114
115AIM:
116
117To create a DDL to perform creation of table, alter, modify and drop column.
118
119
120
121DDL COMMANDS
122
1231.	The Create Table Command: - it defines each column of the table uniquely. Each column has minimum of three attributes, a name , data type and size.
124
125Syntax:
126
127Create table <table name> (<col1> <datatype>(<size>),<col2> <datatype><size>)); Ex:create table emp(empno number(4) primary key, ename char(10));
128
129
1302.	Modifying the structure of tables. a) Add new columns
131
132Syntax:
133
134Alter table <tablename> add(<new col><datatype(size),<new col>datatype(size)); Ex:alter table emp add(sal number(7,2));
135
136
1373.	Dropping a column from a table.
138
139Syntax:
140
141Alter table <tablename> drop column <col>;
142
143Ex:alter table emp drop column sal;
144
145
146
1474. Modifying existing columns.
148
149Syntax:
150
151Alter table <tablename> modify(<col><newdatatype>(<newsize>));
152
153Ex:alter table emp modify(ename varchar2(15));
154
1555. Renaming the tables
156
157Syntax:
158
159Rename <oldtable> to <new table>;
160
161Ex:rename emp to emp1;
162
163
164
1656. truncating the tables.
166
167Syntax:
168
169Truncate table <tablename>;
170
171Ex:trunc table emp1;
172
173
174
1757. Destroying tables.
176
177Syntax:
178
179Drop table <tablename>;
180
181Ex:drop table emp;
182
183
184CREATION OF TABLE:
185
186SYNTAX:
187
188create table<tablename>(column1 datatype,column2 datatype...);
189
190EXAMPLE:
191
192SQL>create table std(sno number(5),sname varchar(20),age number(5),sdob date,sm1 number(4,2),sm2 number(4,2),sm3 number(4,4)); Table created.
193
194SQL>insert into std values(101,‟AAA‟,16,‟03-jul-88‟,80,90,98); 1 row created.
195
196SQL>insert into std values(102,‟BBB‟,18,‟04-aug-89‟,88,98,90); 1 row created.
197
198OUTPUT:				
199Select * from std;			
200SNO	SNAME	AGE	SDOBSM1	SM2	SM3
201101	AAA	16	03-jul-88   80	90	98
202102	BBB	18	04-aug-89 88	98	90
203
204
205ALTER TABLE WITH ADD:
206
207SQL>create table student(id number(5),name varchar(10),game varchar(20)); Table created.
208
209SQL>insert into student values(1,‟mercy‟,‟cricket‟); 1 row created.
210
211SYNTAX:
212
213alter table<tablename>add(col1 datatype,col2 datatype..);
214
215EXAMPLE:
216
217SQL>alter table student add(age number(4));
218
219SQL>insert into student values(2,‟sharmi‟,‟tennis‟,19);
220
221
222OUTPUT:
223
224ALTER: select * from student;
225
226ID NAME GAME
227
2281	Mercy Cricket
229
230
231
232ADD: select * from student;
233
234ID NAME GAME AGE
235
2361	Mercy cricket
237
2382	Sharmi Tennis 19
239
240
241
242ALTER TABLE WITH MODIFY:
243
244SYNTAX:
245
246Alter table<tablename>modify(col1 datatype,col2 datatype..);
247
248EXAMPLE:
249
250SQL>alter table student modify(id number(6),game varchar(25));
251
252
253
254OUTPUT:
255
256MODIFY
257
258desc student;
259
260NAME NULL? TYPE
261
262Id	Number(6)
263
264Name Varchar(20)
265
266Game Varchar(25)
267
268Age	Number(4)
269
270DROP:
271
272SYNTAX: drop table<tablename>;
273
274EXAMPLE:
275
276SQL>drop table student;
277
278SQL>Table dropped.
279
280
281
282TRUNCATE TABLE
283
284SYNTAX: TRUNCATE TABLE <TABLE NAME>;
285
286Example: Truncate table stud;
287
288
289
290DESC
291
292Example: desc emp;
293
294Name Null? Type
295
296--------------------------------- --------
297
298EmpNo NOT NULL number(5)
299
300EName VarChar(15)
301
302Job NOT NULL Char(10)
303
304DeptNo NOT NULL number(3)
305
306PHONE_NO number (10)
307
308
309
310Queries:
311
312Q1. Create a table called EMP with the following structure.
313
314Name Type
315
316---------- ----------------------
317
318EMPNO NUMBER(6)
319
320ENAME VARCHAR2(20)
321
322JOB VARCHAR2(10)
323
324DEPTNO NUMBER(3)
325
326SAL NUMBER(7,2)
327
328Allow NULL for all columns except ename and job.
329
330Solution:
331
3321.	Understand create table syntax.
333
3342.	Use the create table syntax to create the said tables.
335
3363.	Create primary key constraint for each table as understand from logical table structure. Ans:
337
338SQL> create table emp(empno number(6),ename varchar2(20)not null,job varchar2(10) not null, deptno number(3),sal number(7,2));
339
340Table created.
341Q2: Add a column experience to the emp table.
342
343experience numeric null allowed.
344
345Solution:
346
3471.	Learn alter table syntax.
348
3492.	Define the new column and its data type.
350
3513.	Use the alter table syntax.
352
353Ans: SQL> alter table emp add(experience number(2)); Table altered.
354
355Q3: Modify the column width of the job field of emp table.
356
357Solution:
358
3591.	Use the alter table syntax.
360
3612.	Modify the column width and its data type.
362
363Ans: SQL> alter table emp modify(job varchar2(12)); Table altered.
364
365SQL> alter table emp modify(job varchar(13)); Table altered.
366
367Q4: Create dept table with the following structure.
368
369Name Type
370
371------------ ---------------------
372
373DEPTNO NUMBER(2)
374
375DNAME VARCHAR2(10)
376
377LOC VARCHAR2(10)
378
379Deptno as the primarykey
380
381Solution:
382
3831.	Understand create table syntax.
384
3852.	Decide the name of the table.
386
3873.	Decide the name of each column and its data type.
388
3894.	Use the create table syntax to create the said tables.
390
3915.	Create primary key constraint for each table as understand from logical table structure. Ans:
392
393SQL> create table dept(deptno number(2) primary key,dname varchar2(10),loc varchar2(10));
394
395Table created.
396
397Q5: create the emp1 table with ename and empno, add constraints to check the empno value while entering (i.e) empno > 100.
398
399Solution:
400
4011.	Learn alter table syntax.
402
4032.	Define the new constraint [columns name type]
404
4053.	Use the alter table syntax for adding constraints. Ans:
406SQL> create table emp1(ename varchar2(10),empno number(6) constraint
407
408check(empno>100));
409
410Table created.
411
412Q6: drop a column experience to the emp table.
413
414Solution:
415
4161.	Learn alter table syntax. Use the alter table syntax to drop the column.
417
418Ans:
419
420SQL> alter table emp drop column experience; Table altered.
421
422Q7: Truncate the emp table and drop the dept table
423
424Solution:
425
4261. Learn drop, truncate table syntax.
427
428Ans: SQL> truncate table emp; Table truncated.
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445RESULT:
446Thus the DDL commands have been executed successfully.
447
448EX.NO:2	WORKING WITH DATA MANIPULATION COMMANDS
449
450
451AIM
452
453To study the various DML commands and implement them on the database.
454
455DML COMMANDS
456
457DML commands are the most frequently used SQL commands and is used to query and manipulate the existing database objects. Some of the commands are Insert, Select, Update, Delete.
458
459Insert Command This is used to add one or more rows to a table. The values are separated by commas and the data types char and date are enclosed in apostrophes. The values must be entered in the same order as they are defined.
460
461Select Commands It is used to retrieve information from the table. It is generally referred to as querying the table. We can either display all columns in a table or only specify column from the table.
462
463Update Command It is used to alter the column values in a table. A single column may be updated or more than one column could be updated.
464
465Delete command After inserting row in a table we can also delete them if required. The delete command consists of a from clause followed by an optional where clause.
466
467
468Q1: Insert a single record into dept table.
469
470Ans: SQL> insert into dept values (1,'IT','Tholudur');
471
4721 row created.
473
474Q2: Insert more than a record into emp table using a single insert command.
475
476Ans: SQL> insert into emp values(&empno,'&ename','&job',&deptno,&sal);
477
478Enter value for empno: 1
479
480Enter value for ename: Mathi
481
482Enter value for job: AP
483
484Enter value for deptno: 1
485
486Enter value for sal: 10000
487
488old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)
489new 1: insert into emp values(1,'Mathi','AP',1,10000)
490
4911 row created.
492
493SQL> / Enter value for empno: 2
494
495Enter value for ename: Arjun
496
497Enter value for job: ASP
498
499Enter value for deptno: 2
500
501Enter value for sal: 12000
502
503old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)
504
505new 1: insert into emp values(2,'Arjun','ASP',2,12000)
506
5071 row created.
508
509
510
511SQL> / Enter value for empno: 3
512
513Enter value for ename: Gugan
514
515Enter value for job: ASP
516
517Enter value for deptno: 1
518
519Enter value for sal: 12000
520
521old 1: insert into emp values(&empno,'&ename','&job',&deptno,&sal)
522
523new 1: insert into emp values(3,'Gugan','ASP',1,12000)
524
5251 row created.
526
527Q3: Update the emp table to set the salary of all employees to Rs15000/- who are working as ASP
528
529Ans: SQL> select * from emp;
530
531EMPNO ENAME JOB DEPTNO SAL
532
533---------- -------------------- ------------- ---------- ----------	
5341 Mathi AP 1	10000		
5352 Arjun ASP	2 12000		
5363 Gugan ASP 1 12000		
537SQL> update emp set sal=15000 where job='ASP'; 2 rows updated.
538
539SQL> select * from emp;
540
541EMPNO ENAME JOB DEPTNO SAL
542
543---------- -------------------- ------------- ---------- ----------
544
5451 Mathi AP 1 10000
546
5472 Arjun ASP 2 15000
548
5493 Gugan ASP 1 15000
550
551Q4: Create a pseudo table employee with the same structure as the table emp and insert rows into the table using select clauses.
552
553Ans: SQL> create table employee as select * from emp;
554
555Table created.
556
557SQL> desc employee;
558
559Name Null? Type
560
561----------------------------------------- -------- ----------------------------
562
563EMPNO NUMBER(6)
564
565ENAME NOT NULL VARCHAR2(20)
566
567JOB NOT NULL VARCHAR2(13)
568
569DEPTNO NUMBER(3)
570
571SAL NUMBER(7,2)
572
573Q5: select employee name, job from the emp table
574
575Ans: SQL> select ename, job from emp;
576
577ENAME JOB
578
579-------------------- -------------
580
581Mathi AP
582
583Arjun ASP
584
585Gugan ASP
586
587Karthik Prof
588
589Akalya AP
590
591suresh lect
592
5936 rows selected.
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612RESULT:
613Thus the DML commands have been executed successfully.
614
615EX.NO:3			BASIC SELECT STATEMENTS
616
617AIM
618
619To study the various Basic Select statement on the database.
620
621
622Q1: Delete only those who are working as lecturer
623
624Ans: SQL> select * from emp;
625
626EMPNO ENAME JOB DEPTNO SAL
627
628---------- -------------------- ------------- ---------- ----------
629
6301 Mathi AP 1 10000
631
6322 Arjun ASP 2 15000
633
6343 Gugan ASP 1 15000
635
6364 Karthik Prof 2 30000
637
6385 Akalya AP 1 10000
639
6406 suresh lect 1 8000
641
6426 rows selected.
643
644SQL> delete from emp where job='lect';
645
6461 row deleted.
647
648SQL> select * from emp;
649
650EMPNO ENAME JOB DEPTNO SAL
651
652-	--------- -------------------- ------------- ---------- ----------
653
6541 Mathi AP 1 10000
655
6562 Arjun ASP 2 15000
657
6583 Gugan ASP 1 15000
659
6604 Karthik Prof 2 30000
661
6625 Akalya AP 1 10000
663
664
665Q2: List the records in the emp table orderby salary in ascending order.
666
667Ans: SQL> select * from emp order by sal;
668
669EMPNO ENAME JOB DEPTNO SAL
670
671---------- -------------------- ------------- ---------- ----------
672
6731 Mathi AP 1 10000
674
6755 Akalya AP 1 10000
676
6772 Arjun ASP 2 15000
678
6793 Gugan ASP 1 15000
680
6814 Karthik Prof 2 30000
682
683
684
685Q3: List the records in the emp table orderby salary in descending order.
686
687Ans: SQL> select * from emp order by sal desc;
688
689EMPNO ENAME JOB DEPTNO SAL
690
691---------- -------------------- ------------- ---------- ----------
692
6934 Karthik Prof 2 30000
694
6952 Arjun ASP 2 15000
696
6973 Gugan ASP 1 15000
698
6991 Mathi AP 1 10000
700
7015 Akalya AP 1 10000
702
703
704Q4: Display only those employees whose deptno is 30.
705
706Solution: Use SELECT FROM WHERE syntax.
707
708Ans: SQL> select * from emp where deptno=1;
709
710EMPNO ENAME JOB DEPTNO SAL
711
712---------- -------------------- ------------- ---------- ----------
713
7141 Mathi AP 1 10000
715
7163 Gugan ASP 1 15000
717
7185 Akalya AP 1 10000
719
720
721Q5: Display deptno from the table employee avoiding the duplicated values.
722
723Solution:
724
7251. Use SELECT FROM syntax.
726
7272.Select should include distinct clause for the deptno.
728
729Ans: SQL> select distinct deptno from emp;
730
731DEPTNO
732
733----------
734
7351
736
7372
738
7396. To select all stores with sales above $1,000 in Table Store_Information
740
741SQL>SELECT Store_Name FROM Store_Information WHERE Sales > 1000;
742
743Store_Name
744Los Angeles
745
7467. To select all distinct stores in Table Store_Information, we key in,
747
748SQL>SELECT DISTINCT Store_Name FROM Store_Information;
749
750Result:
751Store_Name
752
753Los Angeles
754San Diego
755Boston
756
757
7588. If we want to select all stores with sales greater than $1,000 or all stores with sales less than $500 but greater than $275 in Table Store_Information, we key in,
759
760SQL>SELECT Store_Name FROM Store_Information WHERE Sales > 1000 OR (Sales < 500 AND Sales > 275);
761
762Result:
763Store_Name
764Los Angeles
765San Francisco
766
7679.  To select all records for the Los Angeles and the San Diego stores in Table Store_Information, we key in,
768
769SQL>SELECT * FROM Store_Information WHERE Store_Name IN ('Los Angeles', 'San Diego');
770
771Result:
772Store_Name	Sales	Txn_Date
773Los Angeles	1500	Jan-05-1999
774San Diego	250	Jan-07-1999
775
776
777
77810. To select view all sales information between January 6, 1999, and January 10, 1999, we key in,
779
780SQL>SELECT * FROM Store_Information WHERE Txn_Date BETWEEN 'Jan-06-1999' AND 'Jan-10-1999';
781
782Note that date may be stored in different formats in different databases. This tutorial simply choose one of the formats.
783
784Result:
785
786Store_Name	Sales	Txn_Date
787San Diego	250	Jan-07-1999
788San Francisco	300	Jan-08-1999
789Boston		700	Jan-08-1999
790
79111. We want to find all stores whose name contains 'AN'. To do so, we key in,
792
793SQL>SELECT * FROM Store_Information WHERE Store_Name LIKE '%AN%';
794
795Result:
796Store_Name	Sales	Txn_Date
797LOS ANGELES	1500	Jan-05-1999
798SAN DIEGO	250	Jan-07-1999
799SAN FRANCISCO	300	Jan-08-1999
800
80112. To list the contents of Table Store_Information by Sales in descending order, we key in,
802
803SQL> SELECT Store_Name, Sales, Txn_Date FROM Store_Information ORDER BY Sales DESC;
804
805Result:
806Store_Name	Sales	Txn_Date
807Los Angeles	1500	Jan-05-1999
808Boston	700	Jan-08-1999
809San Francisco	300	Jan-08-1999
810San Diego	250	Jan-07-1999
811
81213. To see only the stores with sales over $1,500, we would type,
813
814SELECT Store_Name, SUM(Sales)
815FROM Store_Information GROUP BY Store_Name HAVING SUM(Sales) > 1500;
816
817Result:
818Store_Name	SUM(Sales)
819Los Angeles	1800
820
821
822RESULT:
823Thus the Basic select commands have been executed successfully.
824
825
826
827
828EX.NO:4		ADVANCED SELECT STATEMENTS
829
830AIM
831
832To study the various Advanced Select statement on the database.
833
834
8351. The LIMIT clause restricts the number of results returned from a SQL statement. It is available in MySQL.
836
837Syntax
838The syntax for LIMIT is as follows:
839
840[SQL Statement 1]
841LIMIT [N];
842
843To retrieve the two highest sales amounts in Table Store_Information, we key in:
844
845SELECT Store_Name, Sales, Txn_Date FROM Store_Information
846ORDER BY Sales DESC
847LIMIT 2;
848
849Store_Name	Sales	Txn_Date
850Los Angeles	1500	Jan-05-1999
851Boston	700	Jan-08-1999
852
8532. The TOP keyword restricts the number of results returned from a SQL statement in Microsoft SQL Server.
854Syntax
855
856The syntax for TOP is as follows:
857
858SELECT TOP [TOP argument] "column_name"
859FROM "table_name";
860where [TOP argument] can be one of two possible types:
861
8621. [N]: The first N records are returned.
863
8642. [M] PERCENT: The number of records corresponding to M% of all qualifying records are returned.
865
866Examples
867
868We use the following table for our examples.
869
870Table Store_Information
871Store_Name	Sales	Txn_Date
872Los Angeles	1500	Jan-05-1999
873San Diego	250	Jan-07-1999
874San Francisco	300	Jan-08-1999
875Boston	700	Jan-08-1999
876[TOP argument] is an integer
877
878To show the two highest sales amounts in Table Store_Information, we key in,
879
880SELECT TOP 2 Store_Name, Sales, Txn_Date
881FROM Store_Information
882ORDER BY Sales DESC;
883Result:
884
885Store_Name	Sales	Txn_Date
886Los Angeles	1500	Jan-05-1999
887Boston	700	Jan-08-1999
888
889
890TOP argument] is a percentage
891
892To show the top 25% of sales amounts from Table Store_Information, we key in,
893
894SELECT TOP 25 PERCENT Store_Name, Sales, Txn_Date
895FROM Store_Information
896ORDER BY Sales DESC;
897Result:
898
899Store_Name	Sales	Txn_Date
900Los Angeles	1500	Jan-05-1999
901
9023. EXISTS is a Boolean operator used in a subquery to test whether the inner query returns any row. 
903If it does, then the outer query proceeds. If not, 
904the outer query does not execute, and the entire SQL statement returns nothing.
905
906The syntax for EXISTS is:
907
908SELECT "column_name1"
909FROM "table_name1"
910WHERE EXISTS 
911(SELECT * 
912FROM "table_name2"
913WHERE "condition");
914
915The following SQL query,
916
917SELECT SUM(Sales) FROM Store_Information
918WHERE EXISTS
919(SELECT * FROM Geography
920WHERE Region_Name = 'West');
921produces the result below:
922
923SUM(Sales)
9242750
925
9264. CASE is used to provide if-then-else type of logic to SQL. There are two formats: The first is a Simple CASE expression,
927 where we compare an expression to static values. The second is a Searched CASE expression,
928 where we compare an expression to one or more logical conditions.
929Simple CASE Expression Syntax
930
931The syntax for a simple CASE expression is:
932
933SELECT CASE ("column_name") 
934  WHEN "value1" THEN "result1" 
935  WHEN "value2" THEN "result2" 
936  ... 
937  [ELSE "resultN"] 
938  END
939FROM "table_name";
940The ELSE clause is optional.
941SELECT Store_Name, CASE Store_Name
942  WHEN 'Los Angeles' THEN Sales * 2
943  WHEN 'San Diego' THEN Sales * 1.5
944  ELSE Sales
945  END
946"New Sales",
947Txn_Date
948FROM Store_Information;
949
950Store_Name	New Sales	Txn_Date
951Los Angeles	3000	Jan-05-1999
952San Diego	375	Jan-07-1999
953San Francisco	300	Jan-08-1999
954Boston	700	Jan-08-1999
955ELECT CASE
956  WHEN "condition1" THEN "result1" 
957  WHEN "condition2" THEN "result2" 
958  ... 
959  [ELSE "resultN"] 
960  END
961FROM "table_name";
962The ELSE clause is optional. "Condition" can consist of one or more logical statements.
963
964Searched CASE Expression Example
965
966We use the same Store_Information above. If we want to define the status of a store's sale based on the following rules:
967
968If Sales >= 1,000, it's a "Good Day"
969If Sales >= 500 and < 1,000, it's an "OK Day"
970If Sales < 500, it's a "Bad Day"
971We can use the following searched CASE expression:
972
973SELECT Store_Name, Txn_Date, CASE
974  WHEN Sales >= 1000 THEN 'Good Day'
975  WHEN Sales >= 500 THEN 'OK Day'
976  ELSE 'Bad Day'
977  END
978"Sales Status"
979FROM Store_Information;
980Result:
981
982Store_Name	Txn_Date	Sales Status
983Los Angeles	Jan-05-1999	Good Day
984San Diego	Jan-07-1999	Bad Day
985San Francisco	Jan-08-1999	Bad Day
986Boston	Jan-08-1999	OK Day
987Note that a simple CASE expression is a special case of a searched CASE expression.
988
989 As an example, the following two CASE expressions are identical:
990
991Simple CASE Expression:
992
993SELECT Store_Name, CASE Store_Name
994  WHEN 'Los Angeles' THEN Sales * 2
995  WHEN 'San Diego' THEN Sales * 1.5
996  ELSE Sales
997  END
998"New Sales",
999Txn_Date
1000FROM Store_Information;
1001Searched CASE Expression:
1002
1003SELECT Store_Name, CASE
1004  WHEN Store_Name = 'Los Angeles' THEN Sales * 2
1005  WHEN Store_Name = 'San Diego' THEN Sales * 1.5
1006  ELSE Sales
1007  END
1008"New Sales",
1009Txn_Date
1010FROM Store_Information;
1011
10125. AUTO_INCREMENT is used in MySQL to create a numerical primary key value for each additional row of data.5
1013
1014The syntax for AUTO_INCREMENT is as follows:
1015
1016CREATE TABLE TABLE_NAME 
1017(PRIMARY_KEY_COLUMN INT NOT NULL AUTO_INCREMENT 
1018... 
1019PRIMARY KEY (PRIMARY_KEY_COLUMN));
1020
1021CREATE TABLE USER_TABLE 
1022(Userid int NOT NULL AUTO_INCREMENT, 
1023Last_Name varchar(50), 
1024First_Name varchar(50), 
1025PRIMARY KEY (Userid));
1026
1027INSERT INTO USER_TABLE VALUES ('Perry', 'Jonathan');
1028
1029Table USER_TABLE
1030
1031Userid	Last_Name	First_Name
10321	Perry		Jonathan
1033
10346. he purpose of the SQL UNION query is to combine the results of two queries together while removing duplicates. In other words, when using UNION, only unique values are returned (similar to SELECT DISTINCT).
1035
1036Syntax
1037
1038The syntax of UNION in SQL is as follows:
1039
1040[SQL Statement 1]
1041UNION
1042[SQL Statement 2];
1043
1044ELECT Txn_Date FROM Store_Information
1045UNION
1046SELECT Txn_Date FROM Internet_Sales;
1047Result:
1048
1049Txn_Date
1050Jan-05-1999
1051Jan-07-1999
1052Jan-08-1999
1053Jan-10-1999
1054Jan-11-1999
1055Jan-12-1999
1056
1057
10587. The INTERSECT command in SQL combines the results of two SQL statement and returns only data that are present in both SQL statements.
1059
1060INTERSECT can be thought of as an AND operator (value is selected only if it appears in both statements), while UNION and UNION ALL can be thought of as an OR operator (value is selected if it appears in either the first or the second statement).
1061
1062Syntax
1063
1064The syntax for INTERSECT is as follows:
1065
1066[SQL Statement 1]
1067INTERSECT
1068[SQL Statement 2];
1069
1070SELECT Txn_Date FROM Store_Information
1071INTERSECT
1072SELECT Txn_Date FROM Internet_Sales;
1073Result:
1074
1075Txn_Date
1076Jan-07-1999
1077
10788. The MINUS command operates on two SQL statements. It takes all the results from the first SQL statement, and then subtract out the ones that are present in the second SQL statement to get the final result set. If the second SQL statement includes results not present in the first SQL statement, such results are ignored.
1079
1080Syntax
1081
1082The syntax for MINUS is as follows:
1083
1084[SQL Statement 1]
1085MINUS
1086[SQL Statement 2];
1087
1088SELECT Txn_Date FROM Store_Information
1089MINUS
1090SELECT Txn_Date FROM Internet_Sales;
1091Result:
1092
1093Txn_Date
1094Jan-05-1999
1095Jan-08-1999
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118RESULT:
1119
1120Thus the advanced Select Statements have been executed successfully
1121EX.NO:5			INTEGRITY AND CONSTRAINTS
1122
1123AIM
1124
1125To study the various Integrity and Constraints on the database.
1126
1127Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.
1128Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.
1129The following constraints are commonly used in SQL:
1130●	NOT NULL - Ensures that a column cannot have a NULL value
1131●	UNIQUE - Ensures that all values in a column are different
1132●	PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
1133●	FOREIGN KEY - Uniquely identifies a row/record in another table
1134●	CHECK - Ensures that all values in a column satisfies a specific condition
1135●	DEFAULT - Sets a default value for a column when no value is specified
1136●	INDEX - Use to create and retrieve data from the database very quickly
1137
1138Q1. Sql>Create Table Employee(Empno Number(4) 
1139Primary Key, 
1140Ename Varchar2(10), 
1141Job Varchar2(6), 
1142Sal Number(5), 
1143Deptno Number(7));
1144
1145Column Level Constraints Using Primary Key With Naming Convention
1146Q2. Sql>Create Table Employee (Empno Number(4) 
1147Constraint Emp_Empno_Pk Primary Key, 
1148Ename Varchar2(10), 
1149Job Varchar2(6), 
1150Sal Number(5), 
1151Deptno Number(7));
1152Q3: Write a query to create foreign key constraints with Table level with alter command.
1153
1154 Sql>Create Table Dept (Deptno Number (2) Primary Key,  
1155Dname Varchar2 (20), 
1156Location Varchar2 (15)); 
1157	
1158 Sql>Create Table Emp4 (Empno Number (3),
1159 Deptno Number (2) References Dept (Deptno), 
1160Design Varchar2 (10));
1161 Column Level Foreign Key Constraint With Naming Conversions:
1162
1163
1164Q4. Sql>Create Table Dept
1165 (Deptno Number (2) Primary Key,
1166 Dname Varchar2 (20),
1167 Location Varchar2 (15)); 
1168
1169Sql>Create Table Emp5
1170 (Empno Number (3), 
1171Deptno Number (2), 
1172Design Varchar2 (10) Constraint Enp2_Deptno_Fk Foreign Key
1173(Dept No) Referencesdept (Deptno));
1174
1175Q5. Write a query to create Check constraints with table level using alter command
1176
1177 Sql>Create Table Emp7
1178(Empno Number (3), 
1179Ename Varchar2 (20), 
1180Design Varchar2 (15), 
1181Sal Number (5) Constraint Emp7_Sal_Ck Check (Sal>500 And Sal <10001
1182Dweptno Number (2));
1183 
1184Q6.  Write a query to create unique constraints with column level
1185
1186Sql>Create Table Emp10 (Empno Number (3), 
1187Ename Varchar2 (20),
1188 Desgin Varchar2 (15) Constraint Emp10_Design_Uk Unique, 
1189Sal Number (5));
1190
1191Q7. Write a query to create Not Null constraints with column level
1192
1193 Sql>Create Table Emp13
1194 (Empno Number (4),
1195 Ename Varchar2 (20) Constraint Emp13_Ename_Nn Not Null,
1196 Design Varchar2 (20), 
1197Sal Number (3));
1198
1199Q8. Write a query to create Null constraints with column level.
1200
1201 Sql>Create Table Emp13
1202 (Empno Number (4),
1203 Ename Varchar2 (20) Constraint Emp13_Ename_Nn Null, 
1204Design Varchar2 (20), 
1205Sal Number (3));
1206
1207Q9. Write a query to disable the constraints 
1208
1209Sql>Alter Table Emp13 Disable Constraint Emp13_Ename_Nn Null;
1210
1211Q10. Write a query to enable the constraints 
1212
1213 Sql>Alter Table Emp13 Enable Constraint Emp13_Ename_Nn Null;
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238RESULT:
1239
1240Thus the Integrity and Constraints have been executed successfully
1241
1242EX.NO:6			JOINING TABLES
1243
1244AIM
1245
1246To study the various Join operations on the database.
1247
1248SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.
1249
1250The INNER JOIN keyword return rows when there is at least one match in both tables.
1251
1252 The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
1253
1254 The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
1255
1256The FULL JOIN keyword return rows when there is a match in one of the tables.
1257
1258
1259
1260LEFT JOIN or LEFT OUTTER JOIN
1261
1262Table:1 - ORDERS
1263SQL> CREATE table orders(O_Id number(5),
1264Orderno number(5),
1265P_Id number(3));
1266
1267Table created.
1268
1269SQL> DESC orders;
1270 Name Null? Type
1271 --------------------- -------- -----------
1272 O_ID NUMBER(5)
1273 ORDERNO NUMBER(5)
1274 P_ID NUMBER(3)
1275
1276
1277INSERTING VALUES INTO ORDERS
1278
1279SQL> INSERT into orders values(&O_Id,&Orderno,&P_Id);
1280Enter value for o_id: 1
1281Enter value for orderno: 77895
1282Enter value for p_id: 3
1283old 1: INSERT into orders values(&O_Id,&Orderno,&P_Id)
1284new 1: INSERT into orders values(1,77895,3)
1285
12861 row created.
1287
1288SQL> INSERT into orders values(&O_Id,&Orderno,&P_Id);
1289
1290Enter value for o_id: 2
1291Enter value for orderno: 44678
1292Enter value for p_id: 3
1293old 1: INSERT into orders values(&O_Id,&Orderno,&P_Id)
1294new 1: INSERT into orders values(2,44678,3)
12951 row created.
1296
1297SQL> INSERT into orders values(&O_Id,&Orderno,&P_Id);
1298
1299Enter value for o_id: 3
1300Enter value for orderno: 22456
1301Enter value for p_id: 1
1302old 1: INSERT into orders values(&O_Id,&Orderno,&P_Id)
1303new 1: INSERT into orders values(3,22456,1)
1304
13051 row created.
1306
1307SQL> INSERT into orders values(&O_Id,&Orderno,&P_Id);
1308
1309Enter value for o_id: 4
1310Enter value for orderno: 24562
1311Enter value for p_id: 1
1312old 1: INSERT into orders values(&O_Id,&Orderno,&P_Id)
1313new 1: INSERT into orders values(4,24562,1)
1314
13151 row created.
1316
1317SQL> INSERT into orders values(&O_Id,&Orderno,&P_Id);
1318
1319Enter value for o_id: 5
1320Enter value for orderno: 34764
1321Enter value for p_id: 15
1322old 1: INSERT into orders values(&O_Id,&Orderno,&P_Id)
1323new 1: INSERT into orders values(5,34764,15)
1324
13251 row created.
1326
1327TABLE SECTION:
1328
1329SQL> SELECT * FROM orders;
1330 O_ID ORDERNO P_ID
1331 ---------- ---------- ----------
1332 1 77895 3
1333 2 44678 3
1334 3 22456 1
1335 4 24562 1
1336 5 34764 15
1337
1338TABLE -2: PERSONS
1339
1340SQL> CREATE table persons(p_Id number(5),
1341LASTNAME varchar2(10),
1342Firstname varchar2(15), Address varchar2(20),
1343city varchar2(10));
1344
1345Table created.
1346
1347SQL> INSERT into persons values(&p_Id,'&Lastname','&firstname','&Address','&city');
1348Enter value for p_id: 1
1349Enter value for lastname: Hansen
1350Enter value for firstname: Ola
1351Enter value for address: Timoteivn 10
1352Enter value for city: sadnes
1353
1354old 1: INSERT into persons values(&p_Id,'&Lastname','&firstname','&Address','&city')
1355new 1: INSERT into persons values(1,'Hansen','Ola','Timoteivn 10','sadnes')
1356
13571 row created.
1358
1359SQL> INSERT into persons values(&p_Id,'&Lastname','&firstname','&Address','&city');
1360Enter value for p_id: 2
1361Enter value for lastname: Svendson
1362Enter value for firstname: Tove
1363Enter value for address: Borgn 23
1364Enter value for city: Sandnes
1365
1366old 1: INSERT into persons values(&p_Id,'&Lastname','&firstname','&Address','&city')
1367new 1: INSERT into persons values(2,'Svendson','Tove','Borgn 23','Sandnes')
1368
13691 row created.
1370
1371SQL> INSERT into persons values(&p_Id,'&Lastname','&firstname','&Address','&city');
1372Enter value for p_id: 3
1373Enter value for lastname: Pettersen
1374Enter value for firstname: Kari
1375Enter value for address: Storgt 20
1376Enter value for city: Stavanger
1377old 1: INSERT into persons values(&p_Id,'&Lastname','&firstname','&Address','&city')
1378new 1: INSERT into persons values(3,'Pettersen','Kari','Storgt 20','Stavanger')
1379
13801 row created.
1381
1382SQL> SELECT * FROM persons;
1383 P_ID LASTNAME FIRSTNAME ADDRESS CITY
1384 ---------- ---------- --------------- ------------------- ----------
1385 1 Hansen Ola Timoteivn 10 sandnes
1386 2 Svendson Tove Borgn 23 Sandnes
1387 3 Pettersen Kari Storgt 20 Stavanger
1388LEFT JOIN SYNTAX
1389
1390SQL> SELECT column_name(s)
1391FROM table_name1
1392LEFT JOIN table_name2
1393ON table_name1.column_name=table_name2.column_name
1394LEFT JOIN EXAMPLE
1395
1396SQL> SELECT persons.lastname,persons.firstname,orders.orderno
1397 FROM persons
1398 LEFT JOIN orders
1399 ON persons.p_Id = orders.p_Id
1400 ORDER BY persons.lastname;
1401
1402OUTPUT
1403LASTNAME FIRSTNAME ORDERNO
1404------------------ ------------------ ---------------
1405Hansen Ola 22456
1406Hansen Ola 24562
1407Pettersen Kari 77895
1408Pettersen Kari 44678
1409Svendson Tove
1410
1411FULL OUTTER JOIN
1412
1413SQL> SELECT * FROM persons;
1414 P_ID LASTNAME FIRSTNAME ADDRESS CITY
1415---------- --------------- -------------------- ---------------- ----------
1416 1 Hansen Ola Timoteivn 10 sandnes
1417 2 Svendson Tove Borgn 23 Sandnes
1418 3 Pettersen Kari Storgt 20 Stavanger
1419SQL> SELECT * FROM orders;
1420 O_ID ORDERNO P_ID
1421---------- ---------- ----------
1422 1 77895 3
1423 2 44678 3
1424 3 22456 1
1425 4 24562 1
1426 5 34764 15
1427
1428FULL OUTER JOIN SYNTAX
1429
1430SQL>SELECT column_name(s)
1431FROM table_name1
1432FULL JOIN table_name2
1433ON table_name1.column_name=table_name2.column_name
1434
1435FULL OUTER JOIN EXAMPLE
1436
1437SQL> SELECT persons.lastname,persons.firstname,orders.orderno
1438 FROM persons
1439 FULL OUTER JOIN orders
1440 ON persons.p_Id = orders.p_Id
1441 ORDER BY persons.lastname;
1442
1443RIGHT OUTTER JOIN
1444
1445RIGHT OUTTER JOIN SYNTAX
1446
1447SQL>SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
1448FROM Persons
1449RIGHT JOIN Orders
1450ON Persons.P_Id=Orders.P_Id
1451ORDER BY Persons.LastName
1452RIGHT OUTTER JOIN EXAMPLE
1453
1454SQL> SELECT persons.lastname,persons.firstname,orders.orderno
1455 FROM persons
1456 RIGHT OUTER JOIN orders
1457 ON persons.p_Id = orders.p_Id
1458 ORDER BY persons.lastname;
1459
1460LASTNAME FIRSTNAME ORDERNO
1461------------------- ------------------ ---------------
1462Hansen Ola 24562
1463Hansen Ola 22456
1464Pettersen Kari 44678
1465Pettersen Kari 77895
1466
1467INNER JOIN
1468
1469INNTER JOIN SYNTAX
1470
1471SQL>SELECT column_name(s)
1472FROM table_name1
1473INNER JOIN table_name2
1474ON table_name1.column_name=table_name2.column_name
1475
1476INNTER JOIN EXAMPLE
1477
1478SQL> SELECT persons.lastname,persons.firstname,orders.orderno
1479 2 FROM persons
1480 3 INNER JOIN orders
1481 4 ON persons.p_Id = orders.p_Id
1482 5 ORDER BY persons.lastname;
1483
1484LASTNAME FIRSTNAME ORDERNO
1485------------------ ------------------ ---------------
1486Hansen Ola 22456
1487Hansen Ola 24562
1488Pettersen Kari 77895
1489Pettersen Kari 44678
1490
1491
1492LASTNAME FIRSTNAME ORDERNO
1493------------- --------------- ----------
1494Hansen Ola 22456
1495Hansen Ola 24562
1496Pettersen Kari 77895
1497Pettersen Kari 44678
1498Svendson Tove 34764
14996 rows selected.
1500
1501RESULT:
1502
1503Thus the joining tables have been executed successfully.
1504
1505
1506
1507
1508
1509
1510
1511EX.NO:7			SQL FUNCTIONS		
1512AIM
1513
1514To study the various SQL Functions operations on the database.
1515
1516CHARACTER/STRING FUNCTION:
1517
1518SQL> select upper('welcome') from dual;
1519-----------
1520WELCOME
1521
1522SQL> select upper('hai') from dual;
1523---
1524HAI
1525
1526
1527SQL> select lower('HAI') from dual;
1528LOW
1529---
1530hai
1531
1532
1533SQL> select initcap(„hello world') from dual;
1534INITCAP('Hello
1535--------------
1536Hello World
1537
1538
1539
1540SQL> select ltrim('	hai') from dual;
1541LTR
1542---
1543hai
1544
1545
1546
1547SQL> select rtrim('hai	')from dual;
1548
1549RTR
1550---
1551hai
1552
1553
1554SQL> select rtrim('	hai	')from dual;
1555RTRIM('
1556-------
1557hai
1558SQL> select concat('SRM',' university')from dual;
1559------------------------
1560SRM university
1561
1562
1563
1564SQL> select length('SRM‟)from dual;
1565LENGTH('SRM')
1566----------------------
156712
1568
1569
1570SQL> select replace('SRM university', 'SRM','Anna')from dual;
1571----------------
1572Anna university
1573
1574SQL> select substr('SRM', 7,6)from dual;
1575SUBSTR
1576------
1577lingam
1578SQL> select rpad('hai',3,'*')from dual;
1579
1580RPAD('
1581------
1582hai***
1583
1584SQL> select lpad('hai',3,'*')from dual;
1585LPAD('
1586------
1587***hai
1588
1589SQL> select replace('Dany','y','ie')from dual;
1590REPLACE
1591-------
1592Danie
1593
1594SQL> select translate('cold','ld','ol')from dual;
1595TRANSL
1596------
1597cool
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608DATE & TIME FUNCTION
1609
1610SQL> select sysdate from dual;
1611
1612SYSDATE
1613
1614---------
1615
161607-APR-10
1617
1618
1619SQL> select round(sysdate)from dual;
1620
1621ROUND(SYS
1622
1623---------
1624
162507-APR-10
1626
1627
1628SQL> select add_months(sysdate,3)from dual;
1629
1630ADD_MONTH
1631
1632---------
1633
163407-JUL-10
1635
1636
1637
1638SQL> select last_day(sysdate)from dual;
1639
1640LAST_DAY(
1641
1642---------
1643
164430-APR-10
1645
1646
1647SQL> select sysdate+20 from dual;
1648
1649SYSDATE+2
1650
1651---------
1652
165327-APR-10
1654
1655
1656SQL> select next_day(sysdate,'tuesday')from dual;
1657
1658NEXT_DAY(
1659
1660---------
1661
166213-APR-10
1663
1664NUMERIC FUNCTION
1665
1666SQL> select round(15.6789)from dual;
1667
1668ROUND(15.6789)
1669
1670--------------
167116
1672
1673SQL> select ceil(23.20)from dual;
1674
1675CEIL(23.20)
1676
1677-----------
167824
1679
1680SQL> select floor(34.56)from dual;
1681
1682FLOOR(34.56)
1683-----------
168434
1685
1686SQL> select trunc(15.56743)from dual;
1687
1688TRUNC(15.56743)
1689---------------
169015
1691
1692
1693SQL> select sign(-345)from dual;
1694
1695SIGN(-345)
1696----------
1697-1
1698
1699
1700SQL> select abs(-70)from dual;
1701ABS(-70)
1702---------
170370
1704
1705
1706MATH FUNCTION:
1707
1708SQL> select abs(45) from dual;
1709
1710ABS(45)
1711---------
171245
1713
1714
1715
1716SQL> select power(10,12) from dual;
1717
1718POWER(10,12)
1719------------
17201.000E+12
1721
1722SQL> select mod(11,5) from dual;
1723MOD(11,5)
1724---------
17251
1726
1727SQL> select exp(10) from dual;
1728EXP(10)
1729---------
173022026.466
1731
1732SQL> select sqrt(225) from dual;
1733SQRT(225)
1734---------
173515
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758RESULT:
1759
1760Thus the SQL Functions have been executed successfully.
1761
1762
1763EX.NO:8				SUB QUERIES
1764
1765
1766AIM
1767To study the various SQL sub queries operations on the database.
1768
1769
1770Sub Query can have more than one level of nesting in one single query. A SQL nested query is a SELECT query that is nested inside a SELECT, UPDATE, INSERT, or DELETE SQL query.
1771
17721. Select Command Is Used To Select Records From The Table.
17732. Where Command Is Used To Identify Particular Elements.
17743. Having Command Is Used To Identify Particular Elements.
17754. Min (Sal) Command Is Used To Find Minimum Salary.
1776
1777 Syntax For Creating A Table:
1778
1779 Sql: Create <Obj.Type> <Obj.Name> (Column Name.1 <Datatype>
1780(Size), Column Name.1 <Datatype> (Size) ……………………………);
1781
1782Sql> Create Table Emp2(Empno Number(5),
1783Ename Varchar2(20),
1784Job Varchar2(20),
1785Sal Number(6),
1786Mgrno Number(4),
1787Deptno Number(3));
1788
1789Syntax For Insert Records In To A Table:
1790
1791 Sql :> Insert Into <Table Name> Values< Val1, ‘Val2’,…..);
1792
1793Insertion
1794
1795Sql> Insert Into Emp2 Values(1001,'Mahesh','Programmer',15000,1560,200);
17961 Row Created.
1797Sql> Insert Into Emp2 Values (1002,'Manoj','Tester',12000,1560,200);
17981 Row Created.
1799Sql> Insert Into Emp2 Values(1003,'Karthik','Programmer',13000,1400,201);
18001 Row Created.
1801 Sql> Insert Into Emp2 Values(1004,'Naresh','Clerk',1400,1400,201);
18021 Row Created.
1803Sql> Insert Into Emp2 Values(1005,'Mani','Tester',13000,1400,200);
18041 Row Created.
1805Sql> Insert Into Emp2 Values(1006,'Viki','Designer',12500,1560,201);
18061 Row Created.
1807Sql> Insert Into Emp2 Values(1007,'Mohan','Designer',14000,1560,201);1 Row Created.
1808Sql> Insert Into Emp2 Values(1008,'Naveen','Creation',20000,1400,201);
18091 Row Created.
1810Sql> Insert Into Emp2 Values(1009,'Prasad','Dir',20000,1560,202);
18111 Row Created.
1812 Sql> Insert Into Emp2 Values(1010,'Agnesh','Dir',15000,1400,200);
18131 Row Created.
1814
1815Syntax For Select Records From The Table:
1816
1817 Sql> Select * From <Table Name>;
1818Sql> Select *From Emp2;
1819Empno Ename Job Sal Mgrno Dptno
1820---------- ---------- ---------- ---------- ---------- ----------
1821 1001 Mahesh Programmer 15000 1560 200
1822 1002 Manoj Tester 12000 1560 200
1823 1003 Karthik Programmer 13000 1400 201
1824 1004 Naresh Clerk 1400 1400 201
1825 1005 Mani Tester 13000 1400 200
1826 1006 Viki Designer 12500 1560 201
1827 1007 Mohan Designer 14000 1560 201
1828 1008 Naveen Creation 20000 1400 201
1829 1009 Prasad Dir 20000 1560 202
1830 1010 Agnesh Dir 15000 1400 200
1831
1832Table- 2
1833
1834Syntax For Creating A Table:
1835 Sql: Create <Obj.Type> <Obj.Name> (Column Name.1 <Datatype>
1836(Size), Column Name.1 <Datatype> (Size) ……………………………); 
1837Sql> Create Table Dept2(Deptno Number(3),
1838Deptname Varchar2(10),
1839Location Varchar2(15));
1840Table Created.
1841
1842 Syntax For Insert Records In To A Table:
1843 Sql :> Insert Into <Table Name> Values< Val1, ‘Val2’,…..);
1844
1845Insertion
1846Sql> Insert Into Dept2 Values(107,'Develop','Adyar');
18471 Row Created.
1848Sql> Insert Into Dept2 Values(201,'Debug','Uk');
18491 Row Created.
1850Sql> Insert Into Dept2 Values(200,'Test','Us');
1851Sql> Insert Into Dept2 Values(201,'Test','Ussr');
18521 Row Created.
1853Sql> Insert Into Dept2 Values(108,'Debug','Adyar');
18541 Row Created.
1855 Sql> Insert Into Dept2 Values(109,'Build','Potheri');
18561 Row Created.
1857
1858Syntax For Select Records From The Table:
1859 Sql> Select * From <Table Name>;
1860Sql> Select *From Dept2;
1861
1862 Deptno Deptname Location
1863 ---------- ---------- ---------------
1864 107 Develop Adyar
1865 201 Debug Uk
1866 200 Test Us
1867 201 Test Ussr
1868 108 Debug Adyar
1869 109 Build Potheri
18706 Rows Selected.
1871
1872General Syntax For Nested Query:
1873
1874Select "Column_Name1"
1875From "Table_Name1"
1876Where "Column_Name2" [Comparison Operator]
1877(Select "Column_Name3"
1878From "Table_Name2"
1879Where [Condition])
1880
1881Syntax Nested Query Statement:
1882
1883Sql> Select <Column_Name> From Frorm <Table _1> Where
1884 <Column_Name> <Relational _Operation> ‘Value’
1885 (Select (Aggrecate Function) From <Table_1> Where <Column
1886 Name> = ‘Value’
1887 (Select <Column_Name> From <Table_2> Where <Column_Name=
1888 ‘Value’));
1889
1890Nested Query Statement:
1891Sql> Select Ename From Emp2 Where Sal>
1892(Select Min(Sal) From Emp2 Where Dptno=
1893(Select Deptno From Dept2 Where Location='Uk'));
1894
1895Nested Query Output:
1896
1897Ename
1898----------
1899Mahesh
1900Manoj
1901Karthik
1902Mani
1903Viki
1904Mohan
1905Naveen
1906Prasad
1907Agnesh
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938RESULT:
1939
1940Thus the SQL sub query has been executed successfully.
1941EX.NO:9				VIEWS	
1942
1943
1944AIM
1945To study the various SQL view operations on the database.
1946
19471. CREATE VIEW command is used to define a view.
19482.  INSERT command is used to insert a new row into the view.
19493. DELETE command is used to delete a row from the view.
19504. UPDATE command is used to change a value in a tuple without changing all values in the tuple.
19515. DROP command is used to drop the view table 
1952
1953Commands Execution
1954
1955Creation Of Table
1956--------------------------------
1957Sql> Create Table Employee (
1958Employee_Namevarchar2(10),
1959Employee_Nonumber(8),
1960Dept_Name Varchar2(10),
1961Dept_No Number (5),Date_Of_Join Date);
1962Table Created.
1963
1964Table Description
1965-------------------------------
1966Sql> Desc Employee;
1967 Name Null? Type
1968 ------------------------------- -------- ------------------------
1969 Employee_Name Varchar2(10)
1970 Employee_No Number(8)
1971 Dept_Name Varchar2(10)
1972 Dept_No Number(5)
1973 Date_Of_Join Date
1974
1975Suntax For Creation Of View
1976--------------------------------------------------
1977Sql> Create <View> <View Name> As Select
1978 <Column_Name_1>, <Column_Name_2> From <Table Name>;
1979Creation Of View
1980------------------------------
1981Sql> Create View Empview As Select
1982Employee_Name,Employee_No,Dept_Name,Dept_No,Date_Of_Join From
1983Employee;
1984View Created.
1985Description Of View
1986--------------------------------
1987Sql> Desc Empview;
1988Name Null? Type
1989 ----------------------------------------- -------- ----------------------------
1990 Employee_Name Varchar2(10)
1991 Employee_No Number(8)
1992 Dept_Name Varchar2(10)
1993 Dept_No Number(5)
1994
1995Display View:
1996----------------------
1997Sql> Select * From Empview;
1998Employee_N Employee_No Dept_Name Dept_No
1999---------- ----------- ---------- ----------
2000Ravi 124 Ece 89
2001Vijay 345 Cse 21
2002Raj 98 It 22
2003Giri 100 Cse 67
2004
2005Insertion Into View
2006----------------------------------
2007Insert Statement:
2008Syntax:
2009Sql> Insert Into <View_Name> (Column Name1,………)
2010Values(Value1,….);
2011Sql> Insert Into Empview Values ('Sri', 120,'Cse', 67,'16-Nov-1981');
20121 Row Created.
2013Sql> Select * From Empview;
2014Employee_N Employee_No Dept_Name Dept_No
2015---------- ----------- ---------- ----------
2016Ravi 124 Ece 89
2017Vijay 345 Cse 21
2018Raj 98 It 22
2019Giri 100 Cse 67
2020Sri 120 Cse 67
2021Sql> Select * From Employee;
2022Employee_N Employee_No Dept_Name Dept_No Date_Of_J
2023---------- ----------- ---------- ---------- ---------
2024Ravi 124 Ece 89 15-Jun-05
2025Vijay 345 Cse 21 21-Jun-06
2026Raj 98 It 22 30-Sep-06
2027Giri 100 Cse 67 14-Nov-81
2028Sri 120 Cse 67 16-Nov-81
2029Deletion Of View:
2030Delete Statement:
2031Syntax:
2032Sql> Delete <View_Nmae>Where <Column Nmae> =’Value’;
2033Sql> Delete From Empview Where Employee_Name='Sri';
20341 Row Deleted.
2035Sql> Select * From Empview;
2036Employee_N Employee_No Dept_Name Dept_No
2037---------- ----------- ---------- ----------
2038Ravi 124 Ece 89
2039Vijay 345 Cse 21
2040Raj 98 It 22
2041Giri 100 Cse 67
2042Update Statement:
2043Syntax:
2044Aql>Update <View_Name> Set< Column Name> = <Column Name>
2045+<View> Where <Columnname>=Value;
2046Sql> Update Empkaviview Set Employee_Name='Kavi' Where
2047Employee_Name='Ravi';
20481 Row Updated.
2049Sql> Select * From Empkaviview;
2050Employee_N Employee_No Dept_Name Dept_No
2051---------- ----------- ---------- ----------
2052Kavi 124 Ece 89
2053Vijay 345 Cse 21
2054Raj 98 It 22
2055Giri 100 Cse 67
2056Drop A View:
2057Syntax:
2058Sql> Drop View <View_Name>
2059Example
2060Sql>Drop View Empview;
2061
2062 View Droped
2063Create A View With Selected Fields:
2064Syntax:
2065Sql>Create [Or Replace] View <View Name>As Select <Column
2066Name1>…..From <Table Anme>;
2067Example-2:
2068Sql> Create Or Replace View Empl_View1 As Select Empno, Ename,
2069Salary From Empl;
2070
2071Sql> Select * From Empl_View1;
2072Example-3:
2073Sql> Create Or Replace View Empl_View2 As Select * From Empl Where
2074Deptno=10;
2075Sql> Select * From Empl_View2;
2076Note:
2077Replace Is The Keyboard To Avoid The Error “Ora_0095:Name Is Already Used By An Existing
2078Abject”.
2079Changing The Column(S) Name M The View During As Select
2080Statement:
2081Type-1:
2082Sql> Create Or Replace View Emp_Totsal(Eid,Name,Sal) As Select
2083Empno,Ename,Salary From Empl;
2084View Created.
2085 Empno Ename Salary
2086---------- -------------------- ---------- ----------
2087 7369 Smith 1000
2088 7499 Mark 1050
2089 7565 Will 1500
2090 7678 John 1800
2091 7578 Tom 1500
2092 7548 Turner 1500
20936 Rows Selected.
2094View Created.
2095 Empno Ename Salary Mgrno Deptno
2096---------- -------------------- ---------- ---------- ----------------------------
2097 7578 Tom 1500 7298 10
2098 7548 Turner 1500 7298 10
2099View Created.
2100Sql> Select * From Emp_Totsal;
2101Type-2:
2102Sql> Create Or Replace View Emp_Totsal As Select Empno "Eid",Ename
2103"Name",Salary "Sal" From Empl;
2104Sql> Select * From Emp_Totsal;
2105Example For Join View:
2106Type-3:
2107Sql> Create Or Replace View Dept_Emp As Select A.Empno "Eid",A.Ename
2108"Empname",A.Deptno "Dno",B.Dnam
2109E "D_Name",B.Loc "D_Loc" From Empl A,Depmt B Where
2110A.Deptno=B.Deptno;
2111Sql> Select * From Dept_Emp;
2112Eid Name Sal
2113---------- -------------------- ---------- ----------
2114 7369 Smith 1000
2115 7499 Mark 1050
2116 7565 Will 1500
2117 7678 John 1800
2118 7578 Tom 1500
2119 7548 Turner 1500
21206 Rows Selected.
2121View Created.
2122Eid Name Sal
2123---------- -------------------- ---------- ----------
2124 7369 Smith 1000
2125 7499 Mark 1050
2126 7565 Will 1500
2127 7678 John 1800
2128 7578 Tom 1500
2129 7548 Turner 1500
21306 Rows Selected.
2131View Created.
2132 Eid Empname Dno D_Name D_Loc
2133---------- -------------------- ---------- ---------- ------------------------
2134 7578 Tom 10 Account New York
2135 7548 Turner 10 Account New York
2136 7369 Smith 20 Sales Chicago
2137 7678 John 20 Sales Chicago
2138 7499 Mark 30 Research Zurich
2139 7565 WILL 30 RESEARCH ZURICH
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178 RESULT:
2179Thus the SQL views have been executed successfully.
2180
2181
2182
2183EX.NO:10				BASICS OF PL/SQL
2184
2185	
2186AIM
2187To study the various basic PL/SQL view operations on the database.
2188
21891. PL/SQL CODING FOR ADDITION OF TWO NUMBERS
2190
2191SQL> declare
2192a number;
2193 b number;
2194c number;
2195begin
2196a:=&a;
2197b:=&b;
2198c:=a+b;
2199dbms_output.put_line('sum of'||a||'and'||b||'is'||c);
2200end;
2201 /
2202
2203INPUT:
2204Enter value for a: 23
2205old 6: a:=&a;
2206new 6: a:=23;
2207Enter value for b: 12
2208old 7: b:=&b;
2209new 7: b:=12;
2210
2211OUTPUT:
2212sum of23and12is35
2213
2214PL/SQL procedure successfully completed.
2215
22162. PL/ SQL GENERAL SYNTAX FOR IF CONDITION:
2217
2218SQL> DECLARE
2219 <VARIABLE DECLARATION>;
2220 BEGIN
2221 IF(CONDITION)THEN
2222 <EXECUTABLE STATEMENT >;
2223 END;
2224Coding for If Statement:
2225DECLARE
2226b number;
2227c number;
2228BEGIN
2229B:=10;
2230C:=20;
2231if(C>B) THEN
2232dbms_output.put_line('C is maximum');
2233end if;
2234end;
2235/
2236
2237OUTPUT:
2238
2239C is maximum
2240
2241PL/SQL procedure successfully completed.
2242
22433. PL/ SQL GENERAL SYNTAX FOR IF AND ELSECONDITION:
2244
2245SQL> DECLARE
2246 <VARIABLE DECLARATION>;
2247 BEGIN
2248 IF (TEST CONDITION) THEN
2249 <STATEMENTS>;
2250 ELSE
2251 <STATEMENTS>;
2252 ENDIF;
2253 END;
2254******************Less then or Greater Using IF ELSE **********************
2255SQL> declare
2256 n number;
2257 begin
2258 dbms_output. put_line('enter a number');
2259 n:=&number;
2260 if n<5 then
2261 dbms_output.put_line('entered number is less than 5');
2262 else
2263 dbms_output.put_line('entered number is greater than 5');
2264end if;
2265 end;
2266 /
2267
2268Input
2269Enter value for number: 2
2270old 5: n:=&number;
2271new 5: n:=2;
2272
2273Output:
2274entered number is less than 5
2275
2276PL/SQL procedure successfully completed.
2277
22784.PL/ SQL GENERAL SYNTAX FOR NESTED IF:
2279
2280SQL> DECLARE
2281 <VARIABLE DECLARATION>;
2282 BEGIN
2283 IF (TEST CONDITION) THEN
2284 <STATEMENTS>;
2285 ELSEIF (TEST CONDITION) THEN
2286 <STATEMENTS>;
2287 ELSE
2288 <STATEMENTS>;
2289 ENDIF;
2290 END;
2291********** GREATEST OF THREE NUMBERS USING IF ELSEIF************
2292SQL> declare
2293 a number;
2294b number;
2295c number;
2296d number;
2297begin
2298a:=&a;
2299b:=&b;
2300 c:=&b;
2301if(a>b)and(a>c) then
2302dbms_output.put_line('A is maximum');
2303 elsif(b>a)and(b>c)then
2304dbms_output.put_line('B is maximum');
2305else
2306dbms_output.put_line('C is maximum');
2307end if;
2308end;
2309 /
2310
2311INPUT:
2312Enter value for a: 21
2313old 7: a:=&a;
2314new 7: a:=21;
2315Enter value for b: 12
2316old 8: b:=&b;
2317new 8: b:=12;
2318Enter value for b: 45
2319old 9: c:=&b;
2320new 9: c:=45;
2321
2322OUTPUT:
2323C is maximum
2324
2325PL/SQL procedure successfully completed.
2326
23275.PL/ SQL GENERAL SYNTAX FOR LOOPING STATEMENT:
2328
2329SQL> DECLARE
2330 <VARIABLE DECLARATION>;
2331 BEGIN
2332 LOOP
2333 <STATEMENT>;
2334 END LOOP;
2335 <EXECUTAVLE STATEMENT>;
2336 END;
2337***********SUMMATION OF ODD NUMBERS USING FOR LOOP***********
2338SQL> declare
2339n number;
2340sum1 number default 0;
2341endvalue number;
2342begin
2343endvalue:=&endvalue;
2344 n:=1;
2345for n in 1..endvalue
2346loop
2347 if mod(n,2)=1
2348then
2349sum1:=sum1+n;
2350end if;
2351 end loop;
2352dbms_output.put_line('sum ='||sum1);
2353end;
2354 /
2355
2356INPUT:
2357Enter value for endvalue: 4
2358old 6: endvalue:=&endvalue;
2359new 6: endvalue:=4;
2360
2361OUTPUT:
2362 sum =4
2363
2364PL/SQL procedure successfully completed.
2365
23666.PL/ SQL GENERAL SYNTAX FOR LOOPING STATEMENT:
2367
2368SQL> DECLARE
2369 <VARIABLE DECLARATION>;
2370 BEGIN
2371 WHILE <condition>
2372 LOOP
2373 <STATEMENT>;
2374 END LOOP;
2375 <EXECUTAVLE STATEMENT>;
2376 END;
2377*********SUMMATION OF ODD NUMBERS USING WHILE LOOP**********
2378SQL> declare
2379n number;
2380sum1 number default 0;
2381endvalue number;
2382begin
2383endvalue:=&endvalue;
2384n:=1;
2385while(n<endvalue)
2386loop
2387sum1:=sum1+n;
2388n:=n+2;
2389end loop;
2390dbms_output.put_line('sum of odd no. bt 1 and' ||endvalue||'is'||sum1);
2391end;
2392/
2393
2394INPUT:
2395Enter value for endvalue: 4
2396old 6: endvalue:=&endvalue;
2397new 6: endvalue:=4;
2398
2399OUTPUT:
2400sum of odd no. bt 1 and4is4
2401PL/SQL procedure successfully completed.
2402
24037. TRIGGER
2404
2405TYPE 1- TRIGGER AFTER UPDATE
2406
2407SQL> CREATE OR REPLACE TRIGGER VIJAY
2408 AFTER UPDATE OR INSERT OR DELETE ON EMP
2409 FOR EACH ROW
2410 BEGIN
2411IF UPDATING THEN
2412 DBMS_OUTPUT.PUT_LINE('TABLE IS UPDATED');
2413ELSIF INSERTING THEN
2414DBMS_OUTPUT.PUT_LINE('TABLE IS INSERTED');
2415ELSIF DELETING THEN
2416DBMS_OUTPUT.PUT_LINE('TABLE IS DELETED');
2417END IF;
2418END;
2419/
2420
2421Trigger created.
2422SQL> update emp set income =900 where empname='kumar';
2423TABLE IS UPDATED
24241 row updated.
2425SQL> insert into emp values ( 4,'Chandru',700,250,80);
2426TABLE IS INSERTED
24271 row created.
2428SQL> DELETE FROM EMP WHERE EMPID = 4;
2429TABLE IS DELETED
24301 row deleted.
2431TYPE 2 - TRIGGER BEFORE UPDATE
2432------------------------------------------------------
2433SQL> CREATE OR REPLACE TRIGGER VASANTH
2434BEFORE UPDATE OR INSERT OR DELETE ON EMPLOYEE
2435FOR EACH ROW
2436BEGIN
2437IF UPDATING THEN
2438DBMS_OUTPUT.PUT_LINE('TABLE IS UPDATED');
2439ELSIF INSERTING THEN
2440DBMS_OUTPUT.PUT_LINE('TABLE IS INSERTED');
2441ELSIF DELETING THEN
2442DBMS_OUTPUT.PUT_LINE('TABLE IS DELETED');
2443END IF;
2444END;
2445/
2446Trigger created.
2447SQL> INSERT INTO EMP VALUES (4,'SANKAR',700,98,564);
2448TABLE IS INSERTED
24491 row created.
2450SQL> UPDATE EMP SET EMPID = 5 WHERE EMPNAME = 'SANKAR';
2451TABLE IS UPDATED
24521 row updated.
2453SQL> DELETE EMP WHERE EMPNAME='SANKAR';
2454TABLE IS DELETED
24551 row deleted
2456
2457
2458
2459Create a Trigger to check the age valid or not Using Message Alert:
2460
2461PROGRAM:
2462SQL> SET SERVEROUTPUT ON;
2463SQL> CREATE TRIGGER TRIGNEW
2464AFTER INSERT OR UPDATE OF AGE ON TRIG
2465FOR EACH ROW
2466BEGIN
2467IF(:NEW.AGE<0) THEN
2468DBMS_OUTPUT.PUT_LINE('INVALID AGE');
2469ELSE
2470DBMS_OUTPUT.PUT_LINE('VALID AGE');
2471END IF;
2472END;
2473/
2474Trigger created.
2475SQL> insert into trig values('abc',15);
2476Valid age
24771 row created.
2478SQL> insert into trig values('xyz',-12);
2479Invalid age
24801 row created.
2481NAME AGE
2482---------- ----------
2483abc 15
2484xyz -12
24853. Create a Trigger to check the age valid and Raise appropriate error code and
2486error message.
2487SQL> create table data(name char(10),age number(3));
2488Table created.
2489SQL> desc data;
2490Name Null? Type
2491 ----------------------------------------- -------- ------------------------
2492NAME CHAR(10)
2493 AGE NUMBER(3)
2494SQL> CREATE TRIGGER DATACHECK
2495AFTER INSERT OR UPDATE OF AGE ON DATA
2496FOR EACH ROW
2497BEGIN
2498IF(:NEW.AGE<0) THEN
2499RAISE_APPLICATION_ERROR(-20000,'NO NEGATIVE AGE ALLOWED');
2500END IF;
2501END;
2502/
2503Trigger created.
2504SQL> INSERT INTO DATA VALUES('ABC',10);
25051 ROW CREATED.
2506SQL> INSERT INTO DATA VALUES ('DEF',-15)
2507 *
2508ERROR at line 1:
2509ORA-20000: No negative age allowed
2510ORA-06512: at "4039.DATACHECK", line 3
2511ORA-04088: error during execution of trigger '4039.DATACHECK'
2512NAME AGE
2513---------- ----------
2514abc 10
25154. Create a Trigger for EMP table it will update another table SALARY while
2516inserting values.
2517SQL> CREATE TABLE SRM_EMP2(INAME VARCHAR2(10),
2518IID NUMBER(5),
2519SALARY NUMBER(10));
2520Table created.
2521SQL> CREATE TABLE SRM_SAL2(INAME VARCHAR2(10),
2522TOTALEMP NUMBER(5),
2523TOTALSAL NUMBER(10));
2524Table created.
2525
2526
25278. IMPLEMENTATION OF FACTORIAL USING FUNCTION
2528
2529I) PROGRAM:
2530 SQL>create function fnfact(n number)
2531return number is
2532b number;
2533begin
2534b:=1;
2535for i in 1..n
2536loop
2537b:=b*i;
2538end loop;
2539return b;
2540end;
2541/
2542 SQL>Declare
2543n number:=&n;
2544y number;
2545begin
2546y:=fnfact(n);
2547dbms_output.put_line(y);
2548end;
2549/
2550Function created.
2551Enter value for n: 5
2552old 2: n number:=&n;
2553new 2: n number:=5;
2554120
2555PL/SQL procedure successfully completed.
2556
2557
25589. PROCEDURE USING POSITIONAL PARAMETERS:
2559
2560PROCEDURE USING POSITIONAL PARAMETERS:
2561SQL> SET SERVEROUTPUT ON
2562SQL> CREATE OR REPLACE PROCEDURE PROC1 AS
2563 2 BEGIN
2564 3 DBMS_OUTPUT.PUT_LINE('Hello from procedure...');
2565 4 END;
2566 5 /
2567Output:
2568Procedure created.
2569SQL> EXECUTE PROC1
2570Hello from procedure...
2571
2572PL/SQL procedure successfully completed.
2573II) PROGRAM:
2574PROCEDURE USING NOTATIONAL PARAMETERS:
2575SQL> CREATE OR REPLACE PROCEDURE PROC2
2576 2 (N1 IN NUMBER,N2 IN NUMBER,TOT OUT NUMBER) IS
2577 3 BEGIN
2578 4 TOT := N1 + N2;
2579 5 END;
2580 6 /
2581Output:
2582Procedure created.
2583SQL> VARIABLE T NUMBER
2584SQL> EXEC PROC2(33,66,:T)
2585PL/SQL procedure successfully completed.
2586SQL> PRINT T
2587 T
2588----------
2589 99
2590
2591RESULT:
2592Thus the  pl/sql have been executed successfully.
2593
2594EX.NO:11		DESIGN AND DEVELOP APPLICATIONS	
2595
2596
2597SAMPLE: DESIGN AND IMPLEMENTATION OF LIBRARY MANAGEMENT SYSTEM
2598STEPS:
25991.	Create a database for library which request the using SQL
2600
26012.	Establish ODBC connection
2602
26033.	In the administrator tools open data source ODBC
2604
26054.	Click add button and select oracle in ORA home 90, click finish
2606
26075.	A window will appear given the data source home as oracle and select TNS source name as lion and give the used id as SWTT
2608
26096.	ADODC CONTROL FOR library FORM:-
2610
26117.	The above procedure must be follow except the table , A select the table as library
2612
26138.	Write appropriate Program in form each from created in VB from each from created in VB form project.
2614
2615
2616
2617Relational Database Schema
2618
2619Status	code	description							
2620									
2621Media	media_id	code							
2622									
2623Book	ISBN	title	author		year	dewey	price		
2624									
2625BookMedia	media_id	ISBN							
2626							
2627Customer	ID	name	addr	DOB	phone	username	password
2628									
2629Card	num	fines	ID						
2630								
2631Checkout	media_id	num	since	until				
2632									
2633Location	name	addr	phone						
2634								
2635Hold	media_id	num	name	until	queue			
2636									
2637Stored_In	media_id	name							
2638								
2639Librarian	eid	ID	Pay	name	since			
2640								
2641Video	title	year	director	rating	price			
2642									
2643VideoMedia	media_id	title	year						
2644CREATE TABLE Status ( code INTEGER, description CHAR(30), PRIMARY KEY (code) );
2645
2646CREATE TABLE Media( media_id INTEGER, code INTEGER, PRIMARY KEY (media_id),
2647
2648FOREIGN KEY (code) REFERENCES Status );
2649
2650CREATE TABLE Book(ISBNCHAR(14), title CHAR(128), author CHAR(64), year INTEGER, dewey INTEGER, price REAL, PRIMARY KEY (ISBN) );
2651
2652CREATE TABLE BookMedia( media_id INTEGER, ISBN CHAR(14), PRIMARY KEY (media_id),
2653
2654FOREIGN KEY (media_id) REFERENCES Media, FOREIGN KEY (ISBN) REFERENCES Book);
2655
2656CREATE TABLE Customer( ID INTEGER, name CHAR(64), addr CHAR(256), DOB CHAR(10),
2657
2658phone CHAR(30), username CHAR(16), password CHAR(32), PRIMARY KEY (ID),
2659
2660UNIQUE (username) );
2661
2662CREATE TABLE Card( num INTEGER, fines REAL, ID INTEGER, PRIMARY KEY (num),
2663
2664FOREIGN KEY (ID) REFERENCES Customer );
2665
2666CREATE TABLE Checkout( media_id INTEGER, num INTEGER, since CHAR(10), until CHAR(10), PRIMARY KEY (media_id),
2667
2668FOREIGN KEY (media_id) REFERENCES Media, FOREIGN KEY (num) REFERENCES Card );
2669
2670CREATE TABLE Location( name CHAR(64), addr CHAR(256), phone CHAR(30), PRIMARY KEY (name) );
2671
2672CREATE TABLE Hold( media_id INTEGER, num INTEGER, name CHAR(64), until CHAR(10),
2673
2674queue INTEGER, PRIMARY KEY (media_id, num), FOREIGN KEY (name) REFERENCES Location,
2675FOREIGN KEY (num) REFERENCES Card, FOREIGN KEY (media_id) REFERENCES Media );
2676
2677CREATE TABLE Stored_In( media_id INTEGER, name char(64), PRIMARY KEY (media_id),
2678
2679FOREIGN KEY (media_id) REFERENCES Media ON DELETE CASCADE, FOREIGN KEY (name) REFERENCES Location );
2680
2681CREATE TABLE Librarian( eid INTEGER, ID INTEGER NOT NULL, Pay REAL, Loc_name CHAR(64) NOT NULL, PRIMARY KEY (eid),
2682
2683FOREIGN KEY (ID) REFERENCES Customer ON DELETE CASCADE, FOREIGN KEY (Loc_name) REFERENCES Location(name) );
2684
2685CREATE TABLE Video( title CHAR(128), year INTEGER, director CHAR(64), rating REAL, price REAL, PRIMARY KEY (title, year) );
2686
2687CREATE TABLE VideoMedia( media_id INTEGER, title CHAR(128), year INTEGER,
2688
2689PRIMARY KEY (media_id), FOREIGN KEY (media_id) REFERENCES Media, FOREIGN KEY (title, year) REFERENCES Video );
2690
2691INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES
2692
2693(60201, 'Jason L. Gray', '2087 Timberbrook Lane, Gypsum, CO 81637', '09/09/1958', '970-273-9237', 'jlgray', 'password1');
2694
2695INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES
2696
2697(89682, 'Mary L. Prieto', '1465 Marion Drive, Tampa, FL 33602', '11/20/1961', '813-487-4873', 'mlprieto', 'password2');
2698
2699INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES
2700
2701(64937, 'Roger Hurst', '974 Bingamon Branch Rd, Bensenville, IL 60106', '08/22/1973', '847-221-4986', 'rhurst', 'password3');
2702INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES
2703
2704(31430, 'Warren V. Woodson', '3022 Lords Way, Parsons, TN 38363', '03/07/1945', '731-845-0077', 'wvwoodson', 'password4');
2705
2706INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES
2707
2708(79916, 'Steven Jensen', '93 Sunny Glen Ln, Garfield Heights, OH 44125', '12/14/1968', '216-789-6442', 'sjensen', 'password5');
2709
2710INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES
2711
2712(93265, 'David Bain', '4356 Pooh Bear Lane, Travelers Rest, SC 29690', '08/10/1947', '864-610-9558', 'dbain', 'password6');
2713
2714INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES
2715
2716(58359, 'Ruth P. Alber', '3842 Willow Oaks Lane, Lafayette, LA 70507', '02/18/1976', '337-316-3161', 'rpalber', 'password7');
2717
2718INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES
2719
2720(88564, 'Sally J. Schilling', '1894 Wines Lane, Houston, TX 77002', '07/02/1954', '832-366-9035', 'sjschilling', 'password8');
2721
2722INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES
2723
2724(57054, 'John M. Byler', '279 Raver Croft Drive, La Follette, TN 37766', '11/27/1954', '423-592-8630', 'jmbyler', 'password9');
2725
2726INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES
2727
2728(49312, 'Kevin Spruell', '1124 Broadcast Drive, Beltsville, VA 20705', '03/04/1984', '703-953-1216', 'kspruell', 'password10');
2729
2730INSERT INTO Card(num, fines, ID) VALUES ( 5767052, 0.0, 60201); INSERT INTO Card(num, fines, ID) VALUES ( 5532681, 0.0, 60201);
2731INSERT INTO Card(num, fines, ID) VALUES ( 2197620, 10.0, 89682); INSERT INTO Card(num, fines, ID) VALUES ( 9780749, 0.0, 64937); INSERT INTO Card(num, fines, ID) VALUES ( 1521412, 0.0, 31430); INSERT INTO Card(num, fines, ID) VALUES ( 3920486, 0.0, 79916); INSERT INTO Card(num, fines, ID) VALUES ( 2323953, 0.0, 93265); INSERT INTO Card(num, fines, ID) VALUES ( 4387969, 0.0, 58359); INSERT INTO Card(num, fines, ID) VALUES ( 4444172, 0.0, 88564); INSERT INTO Card(num, fines, ID) VALUES ( 2645634, 0.0, 57054); INSERT INTO Card(num, fines, ID) VALUES ( 3688632, 0.0, 49312); INSERT INTO Location(name, addr, phone) VALUES ('Texas Branch', '4832 Deercove Drive, Dallas, TX 75208', '214-948-7102');
2732
2733INSERT INTO Location(name, addr, phone) VALUES ('Illinois Branch', '2888 Oak Avenue, Des Plaines, IL 60016', '847-953-8130');
2734
2735INSERT INTO Location(name, addr, phone) VALUES ('Louisiana Branch', '2063 Washburn Street, Baton Rouge, LA 70802', '225-346-0068'); INSERT INTO Status(code, description) VALUES (1, 'Available'); INSERT INTO Status(code, description) VALUES (2, 'In Transit'); INSERT INTO Status(code, description) VALUES (3, 'Checked Out'); INSERT INTO Status(code, description) VALUES (4, 'On Hold'); INSERT INTO Media( media_id, code) VALUES (8733, 1); INSERT INTO Media( media_id, code) VALUES (9982, 1);
2736
2737INSERT INTO Media( media_id, code) VALUES (3725, 1);
2738
2739INSERT INTO Media( media_id, code) VALUES (2150, 1);
2740
2741INSERT INTO Media( media_id, code) VALUES (4188, 1);
2742
2743INSERT INTO Media( media_id, code) VALUES (5271, 2);
2744
2745INSERT INTO Media( media_id, code) VALUES (2220, 3);
2746
2747INSERT INTO Media( media_id, code) VALUES (7757, 1);
2748INSERT INTO Media( media_id, code) VALUES (4589, 1);
2749
2750INSERT INTO Media( media_id, code) VALUES (5748, 1);
2751
2752INSERT INTO Media( media_id, code) VALUES (1734, 1);
2753
2754INSERT INTO Media( media_id, code) VALUES (5725, 1);
2755
2756INSERT INTO Media( media_id, code) VALUES (1716, 4);
2757
2758INSERT INTO Media( media_id, code) VALUES (8388, 1);
2759
2760INSERT INTO Media( media_id, code) VALUES (8714, 1);
2761
2762INSERT INTO Book(ISBN, title, author, year, dewey, price) VALUES
2763
2764('978-0743289412', 'Lisey''s Story', 'Stephen King',
2765
27662006, 813, 10.0);
2767
2768INSERT INTO Book(ISBN, title, author, year, dewey, price) VALUES ('978-1596912366', 'Restless: A Novel', 'William Boyd', 2006, 813, 10.0);
2769
2770INSERT INTO Book(ISBN, title, author, year, dewey, price) VALUES ('978-0312351588', 'Beachglass', 'Wendy Blackburn', 2006, 813, 10.0);
2771
2772INSERT INTO Book(ISBN, title, author, year, dewey, price) VALUES ('978-0156031561', 'The Places In Between', 'Rory Stewart', 2006, 910, 10.0);
2773
2774INSERT INTO Book(ISBN, title, author, year, dewey, price) VALUES ('978-0060583002', 'The Last Season', 'Eric Blehm', 2006, 902, 10.0);
2775
2776INSERT INTO Book(ISBN, title, author, year, dewey, price) VALUES ('978-0316740401', 'Case Histories: A Novel', 'Kate Atkinson', 2006, 813, 10.0);
2777
2778INSERT INTO Book(ISBN, title, author, year, dewey, price) VALUES ('978-0316013949', 'Step on a Crack', 'James Patterson, et al.',
27792007, 813, 10.0);
2780
2781INSERT INTO Book(ISBN, title, author, year, dewey, price) VALUES ('978-0374105235', 'Long Way Gone: Memoirs of a Boy Soldier', 'Ishmael Beah', 2007, 916, 10.0);
2782
2783INSERT INTO Book(ISBN, title, author, year, dewey, price) VALUES ('978-0385340229', 'Sisters', 'Danielle Steel', 2006, 813, 10.0);
2784
2785INSERT INTO BookMedia(media_id, ISBN) VALUES (8733, '978-0743289412'); INSERT INTO BookMedia(media_id, ISBN) VALUES (9982, '978-1596912366'); INSERT INTO BookMedia(media_id, ISBN) VALUES (3725, '978-1596912366'); INSERT INTO BookMedia(media_id, ISBN) VALUES (2150, '978-0312351588'); INSERT INTO BookMedia(media_id, ISBN) VALUES (4188, '978-0156031561'); INSERT INTO BookMedia(media_id, ISBN) VALUES (5271, '978-0060583002'); INSERT INTO BookMedia(media_id, ISBN) VALUES (2220, '978-0316740401'); INSERT INTO BookMedia(media_id, ISBN) VALUES (7757, '978-0316013949'); INSERT INTO BookMedia(media_id, ISBN) VALUES (4589, '978-0374105235'); INSERT INTO BookMedia(media_id, ISBN) VALUES (5748, '978-0385340229'); INSERT INTO Checkout(media_id, num, since, until) VALUES (2220, 9780749, '02/15/2007', '03/15/2007');
2786
2787INSERT INTO Video(title, year, director, rating, price) VALUES ('Terminator 2: Judgment Day', 1991, 'James Cameron', 8.3, 20.0); INSERT INTO Video(title, year, director, rating, price) VALUES ('Raiders of the Lost Ark', 1981, 'Steven Spielberg', 8.7, 20.0); INSERT INTO Video(title, year, director, rating, price) VALUES ('Aliens', 1986, 'James Cameron', 8.3, 20.0);
2788
2789INSERT INTO Video(title, year, director, rating, price) VALUES ('Die Hard', 1988, 'John McTiernan', 8.0, 20.0);
2790
2791INSERT INTO VideoMedia(media_id, title, year) VALUES
2792
2793
2794( 1734, 'Terminator 2: Judgment Day', 1991);
2795
2796INSERT INTO VideoMedia(media_id, title, year) VALUES ( 5725, 'Raiders of the Lost Ark', 1981);
2797
2798INSERT INTO VideoMedia(media_id, title, year) VALUES ( 1716, 'Aliens', 1986);
2799
2800INSERT INTO VideoMedia(media_id, title, year) VALUES ( 8388, 'Aliens', 1986);
2801
2802INSERT INTO VideoMedia(media_id, title, year) VALUES ( 8714, 'Die Hard', 1988);
2803
2804INSERT INTO Hold(media_id, num, name, until, queue) VALUES (1716, 4444172, 'Texas Branch', '02/20/2008', 1);
2805
2806INSERT INTO Librarian(eid, ID, pay, Loc_name) Values (2591051, 88564, 30000.00, 'Texas Branch');
2807
2808INSERT INTO Librarian(eid, ID, pay, Loc_name) Values
2809
2810(6190164, 64937, 30000.00, 'Illinois Branch');
2811
2812INSERT INTO Librarian(eid, ID, pay, Loc_name) Values
2813
2814(1810386, 58359, 30000.00, 'Louisiana Branch');
2815
2816INSERT INTO Stored_In(media_id, name) VALUES(8733, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(9982, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(1716, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(1734, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(4589, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(4188, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(5271, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(3725, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(8388, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(5748, 'Illinois Branch');
2817INSERT INTO Stored_In(media_id, name) VALUES(2150, 'Louisiana Branch'); INSERT INTO Stored_In(media_id, name) VALUES(8714, 'Louisiana Branch'); INSERT INTO Stored_In(media_id, name) VALUES(7757, 'Louisiana Branch'); INSERT INTO Stored_In(media_id, name) VALUES(5725, 'Louisiana Branch');
2818
2819
2820SELECT C.ID, C.name, C.addr, C.DOB, C.phone, C.username,
2821
2822nvl((SELECT 'Librarian'
2823
2824FROM Librarian L
2825
2826WHERE L.ID = C.ID), 'Customer') AS role
2827
2828FROM Customer C
2829
2830WHERE C.username = <user input> AND C.password = <user input>; /* Book search for customers */
2831
2832SELECT B.ISBN, B.title, B.author, B.year,
2833
2834(SELECT COUNT(*)
2835
2836FROM BookMedia BM
2837
2838WHERE BM.ISBN = B.ISBN AND BM.code = 1) AS num_available FROM Book B
2839
2840WHERE B.title LIKE '%<user input>%' AND B.author LIKE '%<user input>%' AND B.year <= <user input> AND B.year >= <user input>;
2841
2842/* Find all copies of a book (used for placing holds or viewing detailed information). */
2843
2844SELECT BM.media_id, S.description,
2845
2846nvl((SELECT SI.name
2847
2848FROM Stored_In SI
2849
2850WHERE SI.media_id = BM.media_id), 'none') AS name FROM BookMedia BM, Media M, Status S
2851WHERE BM.ISBN = <user input> AND M.media_id = BM.media_id AND S.code = M.code;
2852
2853/* Video search for customers */
2854
2855SELECT V.title, V.year, V.director, V.rating
2856
2857(SELECT COUNT(*)
2858
2859FROM VideoMedia VM
2860
2861WHERE VM.ID = V.ID AND VM.code = 1) AS num_available FROM Video V
2862
2863WHERE V.title LIKE '%<user input>%' AND V.year <= <user input> AND V.year <= <user input>
2864
2865AND V.director LIKE '%<user input>%' AND V.rating >= <user input>; /* Find all copies of a video (used for placing holds or viewing detailed information). */
2866
2867SELECT VM.media_id, S.description,
2868
2869nvl((SELECT SI.name
2870
2871FROM Stored_In SI
2872
2873WHERE SI.media_id = VM.media_id), 'none') AS name FROM VideoMedia VM, Media M, Status S
2874
2875WHERE VM.title = <user input> AND VM.year = <user input> AND M.media_id = VM.media_id AND S.code = M.code; /* Find the status of a given media item */
2876
2877SELECT S.description
2878
2879FROM Status S, Media M
2880
2881WHERE S.code = M.code AND M.media_id = <user input>; /* Create a new Hold */
2882
2883INSERT INTO Hold(media_id, num, name, until, queue) VALUES (<user input>, <user input>, <user input>, <user input>, nvl((SELECT MAX(H.queue)
2884FROM Hold H
2885
2886WHERE H.media_id = <user input>), 0) + 1 );
2887
2888/* Cancel Hold, Step 1: Remove the entry from hold */ DELETE FROM Hold
2889
2890WHERE media_id = <user input> AND num = <user input> /* Cancel Hold, Step 2: Update queue for this item */ UPDATE Hold
2891
2892SET queue = queue-1
2893
2894WHERE media_id = <user input> AND queue > <user input>; /* Functions needed to view information about a customer */ /* View the customer's card(s) */ SELECT CR.num, CR.fines
2895
2896FROM Card CR
2897
2898WHERE CR.ID = <user input>;
2899
2900/* View media checked out on a given card */
2901
2902SELECT B.title, B.author, B.year, BM.media_id, CO.since, CO.until FROM Checkout CO, BookMedia BM, Book B
2903
2904WHERE CO.num = <user input> AND CO.media_id = BM.media_id AND B.ISBN =
2905
2906BM.ISBN
2907
2908UNION
2909
2910SELECT V.title, V.director, V.year, VM.media_id, CO.since, CO.until FROM Checkout CO, VideoMedia VM, Book B
2911
2912WHERE CO.num = <user input> AND CO.media_id = VM.media_id AND VM.title = V.title AND VM.year = V.year;
2913
2914/* View media currently on hold for a given card */
2915
2916SELECT B.title, B.author, B.year, BM.media_id, H.until, H.queue, SI.name FROM Hold H, BookMedia BM, Book B, Stored_In SI
2917WHERE H.num = <user input> AND H.media_id = BM.media_id AND B.ISBN = BM.ISBN
2918
2919AND SI.media_id = H.media_id
2920
2921UNION
2922
2923SELECT V.title, V.director, V.year, VM.media_id, H.until, H.queue, SI.name FROM Hold H, VideoMedia VM, Book B, Stored_In SI
2924
2925WHERE H.num = <user input> AND H.media_id = VM.media_id AND VM.title = V.title AND VM.year = V.year AND SI.media_id = H.media_id; /* View the total amount of fines the customer has to pay */ SELECT SUM(CR.fines)
2926
2927FROM Card CR
2928
2929WHERE CR.ID = <user input>;
2930
2931/* *\
2932
2933Functions reserved for librarians
2934
2935\* */
2936
2937/* Add new customer */
2938
2939INSERT INTO Customer(ID, name, addr, DOB, phone, username, password) VALUES (<user input>, <user input>, <user input>, <user input>, <user input>, <user input>, <user input>, );
2940
2941/* Find a customer */
2942
2943SELECT C.ID, C.name, C.addr, C.DOB, C.phone, C.username,
2944
2945nvl((SELECT 'Librarian'
2946
2947FROM Librarian L
2948
2949WHERE L.ID = C.ID), 'Customer') AS role
2950
2951FROM Customer C
2952
2953WHERE C.username = <user input> AND C.name LIKE '%<user input>%'; /* Add new card and assign it to a customer */
2954INSERT INTO Card(num, fines, ID) VALUES ( <user input>, 0, <user input>); /* Create an entry in Checkout */
2955
2956INSERT INTO Checkout(media_id, num, since, until) VALUES (<user input>, <user input>, <user input>, <user input>); /* Remove the entry for Stored_In */
2957
2958DELETE FROM Stored_In
2959
2960WHERE media_id = <user input>;
2961
2962/* Change the status code of the media */
2963
2964UPDATE Media
2965
2966SET code = <user input>
2967
2968WHERE media_id = <user input>;
2969
2970/* Remove the entry from Checkout */
2971
2972DELETE FROM Checkout
2973
2974WHERE media_id = <user input>;
2975
2976/* Create the entry in Stored_In */
2977
2978INSERT INTO Stored_In(media_id, name) VALUES (<user input>, <user input>); /* Find the next Hold entry for a given media */ SELECT H.num, H.name, H.until
2979
2980FROM Hold H
2981
2982WHERE H.queue = 1 AND H.media_id = <user input>;
2983
2984/* Change the Stored_In entry to the target library branch */ UPDATE Stored_In
2985
2986SET name = <user input>
2987
2988WHERE media_id = <user input>;
2989
2990/* Find the customer that should be notified about book arrival */ SELECT C.name, C.phone, CR.num FROM Customer C, Card CR, Hold H
2991WHERE H.queue = 1 AND H.name = <user input> AND H.media_id = <user input> AND
2992
2993CR.num = H.num AND C.ID = CR.ID;
2994
2995/* Add a new entry into the Book table */
2996
2997INSERT INTO Book(ISBN, title, author, year, dewey, price) VALUES (<user input>, <user input>, <user input>, <user input>, <user input>, <user input>);
2998
2999/* Add a new entry into the Video table */
3000
3001INSERT INTO Video(title, year, director, rating, price) VALUES (<user input>, <user input>, <user input>, <user input>, <user input>); /* Add a new Media object */
3002
3003INSERT INTO Media( media_id, code) VALUES (<user input>, 1); /* Add a new BookMedia object */
3004
3005INSERT INTO BookMedia(media_id, ISBN) VALUES (<user input>, <user input>); /* Add a new VideoMedia object */
3006
3007INSERT INTO VideoMedia(media_id, title, year) VALUES (<user input>, <user input>, <user input>);
3008
3009/* Remove an entry from the BookMedia table */ DELETE FROM BookMedia WHERE media_id = <user input>;
3010
3011/* Remove an entry from the VideoMedia table */ DELETE FROM VideoMedia WHERE media_id = <user input>;
3012
3013/* Remove an entry from the Media table */
3014
3015DELETE FROM Media
3016
3017WHERE media_id = <user input>;
3018
3019/* Remove an entry from the Book table */
3020
3021DELETE FROM Book
3022WHERE ISBN = <user input>;
3023
3024/* Remove an entry from the Video table */
3025
3026DELETE FROM Video
3027
3028WHERE title = <user input> AND year = <user input>; /* Update the customer's fines */ UPDATE Card
3029
3030SET fines = <user input>
3031
3032WHERE num = <user input>