SNO DATE TITLE PAGE SIGNATURE

NO
1 Cr Create a database table, add constraints
(primary key, unique, check, Not null), insert
rows, update and delete rows using SQL DDL
and DML commands.

2 CrCreate a set of tables, add foreign key constraints and

incorporate referential integrity.

3 Query the database tables using different ‘where’

clause conditions and also implement aggregate functions

4 Q Query the database tables and explore sub queries and

simple join operations.

5 Q Query the database tables and explore natural, equi and

outer joins.

6 Write user defined functions and stored procedures in

SQL
7 Execute complex transactions and realize DCL and TCL
commands
8 W Write SQL Triggers for insert, delete, and update
operations in a database table.

9 Cr Create View and index for database tables with a large

number of records.

10 Create an XML database and validate it using XML

schema.
11 Create Document, column and graph based data using
NOSQL database tools
12 D Develop a simple GUI based database
application and incorporate all

13 Case study

1
 Ex. No. 1 WORKING WITH DDL, DML COMMANDS
DATE:
AIM:
To write simple queries using DDL,DML commands.
QUERY BASE:
o Structured Query Language (SQL) as we all know is the database language by the use
of which we can perform certain operations on the existing database and also we can
use this language to create a database.
o SQL commands are mainly categorized into five categories as:
o DDL – Data Definition Language
o DQL – Data Query Language
o DML – Data Manipulation Language
o DCL – Data Control Language
o TCL – Transaction Control Language
o MySQL Data Types
o Numeric data types
 INT
 TINYINT
 SMALLINT
 MEDIUMINT
 BIGINT
 FLOAT(m,d)
 DOUBLE(m,d)
 DECIMAL(m,d)
o Date and Time data types
 DATE
 DATETIME
 TIMESTAMP(m)
 TIME
 YEAR[(2|4)]
o String data types
 CHAR(size)
 VARCHAR(size)

2
  TINYTEXT(size)
 TEXT(size)
 MEDIUMTEXT(size)
 LONGTEXT(size)
 BINARY(size)
 VARBINARY(size)
o Large Object data types
 TINYBLOB
 BLOB(size)
 MEDIUMBLOB
 LONGTEXT
o DDL Commands
o CREATING DATA BASE
Syntax:
mysql>create database databasename;
o To display the available databases
Syntax:
mysql>show databases;
o To use the database
Syntax:
mysql>use database;
o To drop the database
Syntax:
mysql>drop database databasename

I. Creating a table without primary key and constraints:

Syntax:
mysql>create table tablename(column1 datatype1, column2 datatype2, column3
datatype3….etc.,);
II. To display the description about the table:
Syntax:
mysql >desc tablename;
III. Create table with primary key:
Way1:
Syntax:
mysql >create table tablename(column1 datatype1 primary key, column2
3
 datatype2,…etc.,);
Way2:
Syntax:
mysql >create table tablename(column1 datatype1, column2 datatype2, column3
datatype3 ..... primary key(columnname));
IV. Creating table using constraints:
Syntax:
mysql >create table tablename(column1 datatype1, column2 datatype2….
check(condition));
o ALTERING A TABLE
I. Adding primary key:
Syntax:
mysql >alter table tablename add primary key(columnname);
II. To remove a primary key:
Syntax:
mysql >alter table tablename drop primarykey;
[Link] constraints:
Syntax:
mysql >alter table tablename add check(condition);
IV. Adding a new column:
Syntax:
mysql >alter table tablename add columnname datatype;

V. Modifying existing column:

Syntax:
mysql >alter table tablename modify columnname datatype;
Removing a column.
Syntax:
mysql >alter table tablename drop column columnname ;
o DROP A TABLE
Syntax:
mysql >drop table tablename;

4
QUERY:
a. Create a table without primary key
mysql> create table stud_profile(rollno int(7),name
varchar(20),mobile_number int(10),dob date);
Query OK, 0 rows affected (0.19 sec)

mysql> show tables;

+ +
| Tables_in_details |
+ +
| stud_profile |
+ +
1 row in set (0.00 sec)

mysql> describe stud_profile;

+ + + + +
+ +
| Field | Type | Null | Key | Default |
Extra |
+ + + + +
+ +
| rollno | int(7) | YES | | NULL |
|
| name | varchar(20) | YES | | NULL |
|
| mobile_number | int(10) | YES | | NULL |
|
| dob | date | YES | | NULL |
|
+ + + + +
+ +
4 rows in set (0.00 sec)

5
Include the primary key
mysql> alter table stud_profile add primary key(rollno);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe stud_profile;

+ + + + + +
+
| Field | Type | Null | Key | Default | Extra
|
+ + + + + +
+
| rollno | int(7) | NO | PRI | 0 |
|
| name | varchar(20) | YES | | NULL |
|
| mobile_number | int(10) | YES | | NULL |
|
| dob | date | YES | | NULL |
|
+ + + + + +
+
4 rows in set (0.00 sec)

b. Drop the primary key

mysql> alter table stud_profile drop primary key;

Query OK, 0 rows affected (0.62 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe stud_profile;

+ + + + + +
+
| Field | Type | Null | Key | Default | Extra
|
+ + + + + +
+
| rollno | int(7) | NO | | 0 |
|
| name | varchar(20) | YES | | NULL |
|
| mobile_number | int(10) | YES | | NULL |
|
| dob | date | YES | | NULL |
|
+ + + + + +
+
4 rows in set (0.00 sec)

c. Remove the column mobile_number

6
 mysql> alter table stud_profile drop mobile_number;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe stud_profile;

+ + + + + +
+
| Field | Type | Null | Key | Default | Extra
|
+ + + + + +
+
| rollno | int(7) | NO | | 0 |
|
| name | varchar(20) | YES | | NULL |
|
| dob | date | YES | | NULL |
|
+ + + + + +
+
4 rows in set (0.00 sec)

d. Delete the table value

mysql> delete from stud_profile;
Query OK, 0 rows affected (0.00 sec)

mysql> describe stud_profile;

+ + + + + +
+
| Field | Type | Null | Key | Default | Extra
|
+ + + + + +
+
| rollno | int(7) | NO | | 0 |
|
| name | varchar(20) | YES | | NULL |
|
| dob | date | YES | | NULL |
|
+ + + + + +
+
4 rows in set (0.00 sec)

e. Truncate the table

7
 mysql> truncate table stud_profile;
Query OK, 0 rows affected (0.18 sec)

mysql> describe stud_profile;

Query OK, 0 rows affected (0.18 sec)
+ + + + + +
+
| Field | Type | Null | Key | Default | Extra
|
+ + + + + +
+
| rollno | int(7) | NO | | 0 |
|
| name | varchar(20) | YES | | NULL |
|
| dob | date | YES | | NULL |
|
+ + + + + +
+
3 rows in set (0.00 sec)

f. Drop the table

mysql> drop table stud_profile;
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;

Empty set (0.00 sec)

8
 Working With DML COMMANDS

I. To insert the values to all the fields :

Syntax:
mysql>insert into tablename values(integervalue,’charactervalue’);
II. To insert the values to specific fields:
Syntax:
mysql>insert into tablename(columnname1,columnname2,..) values (integervalue,
’charactervalue’);
III. To insert the values during runtime:
Syntax:
SQL>insert into tablename values(&integer value ,’&character value’);

Display records from Table:

Syntax:
SQL> select columnname from tablename[where(condition)];

Delete rows from a table:

Syntax:
SQL> delete from tablename[where(condition)];

Update the Value of a Field:

Syntax:
SQL>update tablename set values/expression [where(condition)];

DATABASE:
Create a stable named Stud_Mark to perform DML operations

Columnname Datatype Size

rollno number 7
name number 5
sub_mark1 number 3
sub_mark2 number 3

9
 sub_mark3 number 3

ER DIAGRAM:

sub_mark1
name

sub_mark

rollno
sub_mark

Stud_Mark

QUERY:
i. Create a table with primary key and constraint for sub_mark1 and sub_mark2
mysql> mysql> create table Stud_Marks(rollno int(7),name
int(5),sub_mark1 int(3),sub_mark2 int(3),sub_mark3
int(3),primary key(rollno));
Query OK, 0 rows affected (0.16 sec)

mysql> show tables;

+ +
| Tables_in_marks |
+ +
| Stud_Marks |
1 row in set (0.00 sec)

mysql> describe Stud_Marks;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| rollno | int(7) | NO | PRI | 0 | |
| name | int(5) | YES | | NULL | |
| sub_mark1 | int(3) | YES | | NULL | |
| sub_mark2 | int(3) | YES | | NULL | |
| sub_mark3 | int(3) | YES | | NULL | |
+ + + + + + +
5 rows in set (0.00 sec)

ii. Include the constraint for sub_mark3

10
 mysql> alter table Stud_Marks add check(sub_mark3>0);
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0

iii. Change the datatype of the field name as varchar2(20)

mysql> alter table Stud_Marks modify name varchar(20);
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe Stud_Marks;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| rollno | int(7) | NO | PRI | 0 | |
| name | varchar(20) | YES | | NULL | |
| sub_mark1 | int(3) | YES | | NULL | |
| sub_mark2 | int(3) | YES | | NULL | |
| sub_mark3 | int(3) | YES | | NULL | |
+ + + + + + +
5 rows in set (0.01 sec)

iv. Add a new column total

mysql> alter table Stud_Marks add total int(3);

Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> describe Stud_Marks;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| rollno | int(7) | NO | PRI | 0 | |
| name | varchar(20) | YES | | NULL | |
| sub_mark1 | int(3) | YES | | NULL | |
| sub_mark2 | int(3) | YES | | NULL | |
| sub_mark3 | int(3) | YES | | NULL | |
| total | int(3) | YES | | NULL | |
+ + + + + + +

v. Insert 4 record into the table stud_mark [check for constraints]

11
 mysql> insert into
Stud_Marks(rollno,name,sub_mark1,sub_mark2,sub_mark3)values(2
000,'Praveena',100,100,100);
Query OK, 1 row affected (0.04 sec)

mysql> insert into

Stud_Marks(rollno,name,sub_mark1,sub_mark2,sub_mark3)values(2
001,'varshini',90,100,100);
Query OK, 1 row affected (0.03 sec)

mysql> insert into

Stud_Marks(rollno,name,sub_mark1,sub_mark2,sub_mark3)values(2
003,'shanthi',90,99,100);
Query OK, 1 row affected (0.03 sec)

mysql> insert into

Stud_Marks(rollno,name,sub_mark1,sub_mark2,sub_mark3)values(2
004,'ganesan',90,100,100);
Query OK, 1 row affected (0.03 sec)

mysql> insert into

Stud_Marks(rollno,name,sub_mark1,sub_mark2,sub_mark3)values(2
005,'karthi',90,34,10);
Query OK, 1 row affected (0.02 sec)

mysql> insert into

Stud_Marks(rollno,name,sub_mark1,sub_mark2,sub_mark3)values(2
006,'ram',34,20,30);
Query OK, 1 row affected (0.02 sec)

vi. Display the inserted records

12
 mysql> select * from Stud_Marks;
+ + + + + +
---+
| rollno | name | sub_mark1 | sub_mark2 | sub_mark3 |
total |
+ + + + + +
---+
| 200 0 | Praveena | 100 | 100 | 100 |
NULL |
| 200 1 | varshini | 90 | 100 | 100 |
NULL |
| 200 3 | shanthi | 90 | 99 | 100 |
NULL |
| 200 4 | ganesan | 90 | 100 | 100 |
NULL |
| 200 5 | karthi | 90 | 34 | 10 |
NULL |
| 200 6 | ram | 34 | 20 | 30 |
NULL |
+ + + + + +
---+
6 rows in set (0.00 sec)

vii. Display the students failed in all the subjects

mysql> select *from Stud_Marks where sub_mark1<35 and
sub_mark2<35 and sub_mark3<35;
+ + + + + + +
| rollno | name | sub_mark1 | sub_mark2 | sub_mark3 | total |
+ + + + + + +
| 2006 | ram | 34 | 20 | 30 | NULL |
+ + + + + + +
1 row in set (0.00 sec)

viii. Update the column total

mysql> update Stud_Marks set
total=sub_mark1+sub_mark2+sub_mark3;
Query OK, 6 rows affected (0.03 sec)
Rows matched: 6 Changed: 6 Warnings: 0

mysql> select *from Stud_Marks;

+ + + + + +
---+
| rollno | name | sub_mark1 | sub_mark2 | sub_mark3 |
total |
+ + + + + +
---+

13
 | 2000 | Praveena | 100 | 100 | 100 |
300 |
| 2001 | varshini | 90 | 100 | 100 |
290 |
| 2003 | shanthi | 90 | 99 | 100 |
289 |
| 2004 | ganesan | 90 | 100 | 100 |
290 |
| 2005 | karthi | 90 | 34 | 10 |
134 |
| 2006 | ram | 34 | 20 | 30 |
84 |
+ + + + + +
---+
6 rows in set (0.00 sec)

ix. Insert 1 record except for the column total

mysql> insert into
Stud_Marks(rollno,name,sub_mark1,sub_mark2,sub_mark3)values
(2007,'anitha',36,54,65);
Query OK, 1 row affected (0.03 sec)

mysql> select *from Stud_Marks;

+ + + + + +
---+
| rollno | name | sub_mark1 | sub_mark2 | sub_mark3 |
total |
+ + + + + +
---+
| 200 0 | Praveena | 100 | 100 | 100 |
300 |
| 200 1 | varshini | 90 | 100 | 100 |
290 |
| 200 3 | shanthi | 90 | 99 | 100 |
289 |
| 200 4 | ganesan | 90 | 100 | 100 |
290 |
| 200 5 | karthi | 90 | 34 | 10 |
134 |
| 200 6 | ram | 34 | 20 | 30 |
84 |
| 200 7 | anitha | 36 | 54 | 65 |
NULL |
+ + + + + +
---+
7 rows in set (0.00 sec)

14
 x. Calculate the total for the newly inserted record
mysql> update Stud_Marks set
total=sub_mark1+sub_mark2+sub_mark3 where rollno=2007;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select *from Stud_Marks;

+ + + + + +
---+
| rollno | name | sub_mark1 | sub_mark2 | sub_mark3 |
total |
+ + + + + +
---+
| 2000 | Praveena | 100 | 100 | 100 |
300 |
| 2001 | varshini | 90 | 100 | 100 |
290 |
| 2003 | shanthi | 90 | 99 | 100 |
289 |
| 2004 | ganesan | 90 | 100 | 100 |
290 |
| 2005 | karthi | 90 | 34 | 10 |
134 |
| 2006 | ram | 34 | 20 | 30 |
84 |
| 2007 | anitha | 36 | 54 | 65 |
155 |
+ + + + + +
---+

xi. Delete the record with rollno 20002

mysql> delete from Stud_Marks where rollno=2006;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select *from Stud_Marks;

+ + + + + +
---+
| rollno | name | sub_mark1 | sub_mark2 | sub_mark3 |
total |
+ + + + + +
---+
| 2000 | Praveena | 100 | 100 | 100 |
300 |
| 2001 | varshini | 90 | 100 | 100 |
290 |
| 2003 | shanthi | 90 | 99 | 100 |
289 |

15
 | 2004 | ganesan | 90 | 100 | 100 |
290 |
| 2005 | karthi | 90 | 34 | 10 |
134 |
| 2007 | anitha | 36 | 54 | 65 |
155 |
+ + + + + +
---+
7 rows in set (0.00 sec)

xii. Display all the students who have secured above 80 mark in sub_mark1 and
sub_mark2.
mysql> select *from Stud_Marks where sub_mark1>80 and
sub_mark2>80;
+ + + + + +
---+
| rollno | name | sub_mark1 | sub_mark2 | sub_mark3 |
total |
+ + + + + +
---+
| 2000 | Praveena | 100 | 100 | 100 |
300 |
| 2001 | varshini | 90 | 100 | 100 |
290 |
| 2003 | shanthi | 90 | 99 | 100 |
289 |
| 2004 | ganesan | 90 | 100 | 100 |
290 |
+ + + + + +
---+
4 rows in set (0.00 sec)

xiii. Display all the students who have secured above 80 mark in sub_mark1 or
sub_mark2.
mysql> select *from Stud_Marks where sub_mark1>80 or
sub_mark2>80;
+ + + + + +
---+
| rollno | name | sub_mark1 | sub_mark2 | sub_mark3 |
total |
+ + + + + +
---+
| 2000 | Praveena | 100 | 100 | 100 |
300 |
| 2001 | varshini | 90 | 100 | 100 |
290 |

16
 | 2003 | shanthi | 90 | 99 | 100 |
289 |
| 2004 | ganesan | 90 | 100 | 100 |
290 |
| 2005 | karthi | 90 | 34 | 10 |
134 |
+ + + + + +
---+
5 rows in set (0.01 sec)

xiv. Display all the students who have secured marks 80 in sub_mark1 and sub_mark2.
mysql> select *from Stud_Marks where sub_mark1==80 or
sub_mark2==80;
+ + + + + +
---+
| rollno | name | sub_mark1 | sub_mark2 | sub_mark3 |
total |
+ + + + + +
---+
| 2008 | John | 80 | 80 | 80 |
240 |
| 2009 | Smith | 80 | 80 | 100 |
260 |
+ + + + + +
---+
2 rows in set (0.01 sec)

xv. Display all the students who have secured marks between 60 to 80 in sub_mark3.
mysql> select name from Stud_Marks where sub_mark3>60 and
sub_mark2<80;
+ +
| name |
+ +
| anitha |

xvi. Display all the students who have secured marks (65,75,85) in sub_mark2.
mysql> select name from Stud_Marks where sub_mark3=65 or
sub_mark3=75 or sub_mark3=85;
+ +
| name |
+ +
| anitha |
+ +
1 row in set (0.00 sec)

17
 xvii. Display the names of the student starting with 'a'.
mysql> select name from Stud_Marks where name like 'a%';
+ +
| name |
+ +
| anitha |
+ +
1 row in set (0.00 sec)

xviii. Display the names of the student having 'h'

mysql> select name from Stud_Marks where name like'%h%';
+ +
| name |
+ +
| varshini |
| shanthi |
| karthi |
| anitha |
+ +
4 rows in set (0.00 sec)

RESULT:
Thus, the simple queries are written and executed successfully.

18
 Cre Create a set of tables, add foreign key constraints and incorporate
Ex. No. 2 referential integrity.
DATE:

AIM:
To create a set of tables, add foreign key constraints and incorporate referential integrity
CONSTAINTS:
In SQL, we can create a relationship between two tables using the FOREIGN KEY

Here, the customer_id field in the Orders table is FOREIGN KEY which
references the id field in the Customers table. This means that the value of
the customer_id (of the Orders table) must be a value from the id column
(of the Customers table).

WITHOUT FOREIGN KEY CONSTRAINT:

19
CREATING FOREIGN KEY:

20
RESULT:
Thus the foreign key used to different set of table was created and executed successfully

21
 WORKING WITH SQL CLAUSE AND SQL AGGREGATE
Ex. No. 3
FUNCTIONS
DATE:

AIM:
To write simple queries using aggregate functions and SQL clauses.
QUERY BASE:
SQL AGGREGATE FUNCTIONS

1. COUNT
 COUNT function is used to Count the number of rows in a database table. It can work
on both numeric and non-numeric data types.
 COUNT function uses the COUNT(*) that returns the count of all the rows in a
specified table. COUNT(*) considers duplicate and Null.
Syntax:
COUNT(*) or COUNT( [ALL|DISTINCT] expression )
Select count(argument) from tablename [where(condition)];
2. AVG
 The AVG function is used to calculate the average value of the numeric type. AVG
function returns the average of all non-Null values.
Syntax:
AVG() or AVG( [ALL|DISTINCT] expression )
Select avg(argument) from tablename [where(condition)];
3. SUM

22
  Sum function is used to calculate the sum of all selected columns. It works on numeric
fields only.
Syntax:
SUM() or SUM( [ALL|DISTINCT] expression )
Select sum(argument) from tablename [where(condition)];
4. MIN
 MIN function is used to find the minimum value of a certain column. This function
determines the smallest value of all selected values of a column.
Syntax:
MIN() or MIN( [ALL|DISTINCT] expression )
Select min(argument) from tablename [where(condition)];
5. MAX
 MAX function is used to find the maximum value of a certain column. This function
determines the largest value of all selected values of a column.
Syntax:
MAX() or MAX( [ALL|DISTINCT] expression )
Select max(argument) from tablename [where(condition)];
SQL CLAUSES

1. GROUP BY
 SQL GROUP BY statement is used to arrange identical data into groups. The GROUP
BY statement is used with the SQL SELECT statement.
 The GROUP BY statement follows the WHERE clause in a SELECT statement and
precedes the ORDER BY clause.
 The GROUP BY statement is used with aggregation function.
Syntax:
select column from table_name group by column

23
 2. HAVING
 HAVING clause is used to specify a search condition for a group or an aggregate.
 Having is used in a GROUP BY clause. If you are not using GROUP BY clause then
you can use HAVING function like a WHERE clause.
Syntax:
select column1, column2 from table_name group by column1, column2 having
conditions;
3. ORDER BY
 The ORDER BY clause sorts the result-set in ascending or descending order.
 It sorts the records in ascending order by default. DESC keyword is used to sort the
records in descending order.
Syntax:
select column1, column2 from table_name order by column1, column2... ASC|DESC;
where,
ASC: It is used to sort the result set in ascending order by expression.
DESC: It sorts the result set in descending order by expression.
DATABASE:
sailors_master
Columnname Datatype Size
sid number
sname varchar2 20
rating number
age number 2,1

ER DIAGRAM:

rating
sname

age

sid

sailors_master

24
 QUERY:
a. Create a table

mysql> create database details1;

Query OK, 1 row affected (0.00 sec)

mysql> use details1;

Database changed

mysql> create table sailor_master(sid int,sname

varchar(20),rating int,age float,primary key(sid));
Query OK, 0 rows affected (0.19 sec)

mysql> describe sailor_master;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| sid | int(11) | NO | PRI | 0 | |
| sname | varchar(20) | YES | | NULL | |
| rating | int(11) | YES | | NULL | |
| age | float | YES | | NULL | |
+ + + + + + +
4 rows in set (0.01 sec)
b. Insert the following values into the table
sid sname rating age
22 Alice 7 45.0
29 Bob 1 33.0
31 Zoro 8 55.5
32 Ravi 8 25.5
58 Raj 10 35.0
64 Lubber 7 35.0
71 Horatio 10 16.0
74 Andy 9 35.0
85 Crist 3 25.5
95 Hary 3 63.5

25
 mysql> mysql> insert into
sailor_master(sid,sname,rating,age)values(22,'Alice',7,45.0);
Query OK, 1 row affected (0.04 sec)

mysql> insert into

sailor_master(sid,sname,rating,age)values(29,'Bob',1,33.0);
Query OK, 1 row affected (0.04 sec)

mysql> insert into

sailor_master(sid,sname,rating,age)values(31,'Zoro',8,55.5);
Query OK, 1 row affected (0.02 sec)

mysql> insert into

sailor_master(sid,sname,rating,age)values(32,'Ravi',8,25.5);
Query OK, 1 row affected (0.03 sec)

mysql> insert into

sailor_master(sid,sname,rating,age)values(58,'Raj',10,35.5);
Query OK, 1 row affected (0.03 sec)

mysql> insert into

sailor_master(sid,sname,rating,age)values(64,'Lubber',7,35.0)
;
Query OK, 1 row affected (0.03 sec)

mysql> insert into

sailor_master(sid,sname,rating,age)values(71,'Horatio',10,16.
0);
Query OK, 1 row affected (0.03 sec)

mysql> insert into

sailor_master(sid,sname,rating,age)values(74,'Andy',9,35.0);
Query OK, 1 row affected (0.03 sec)

mysql> insert into

sailor_master(sid,sname,rating,age)values(85,'crist',3,25.5);
Query OK, 1 row affected (0.03 sec)

mysql> insert into

sailor_master(sid,sname,rating,age)values(95,'Hary',3,63.5);
Query OK, 1 row affected (0.03 sec)

mysql> select *from sailor_master;

+ + + + +
| sid | sname | rating | age |
+ + + + +
| 22 | Alice | 7 | 45 |
| 29 | Bob | 1 | 33 |
| 31 | Zoro | 8 | 55.5 |
| 32 | Ravi | 8 | 25.5 |
| 58 | Raj | 10 | 35.5 |

26
 | 64| Lubber | 7 | 35 |
| 71| Horatio | 10 | 16 |
| 74| Andy | 9 | 35 |
| 85| crist | 3 | 25.5 |
| 95| Hary | 3 | 63.5 |
+ + + + +
10 rows in set (0.00 sec)
c. Answer to the following query
i. Count the number of sailors
mysql> select count(*)count from sailor_master;
+ +
| count |
+ +
| 10 |
+ +
1 row in set (0.00 sec)

ii. Find the sum of rating in sailors

mysql> select sum(rating)sum from sailor_master;
+ +
| sum |
+ +
| 66 |
+ +
1 row in set (0.00 sec)

iii. Find the average age of all sailors

mysql> select avg(age)average from sailor_master;
+ +
| average |
+ +
| 36.95 |
+ +
1 row in set (0.00 sec)

iv. Find the average age of sailors with a rating of 10

mysql> select avg(age)average from sailor_master where
rating=10;
+ +
| average |
+ +
| 25.75 |
+ +
1 row in set (0.01 sec)

v. Find the age of youngest sailor

27
 mysql> select min(age)youngest_sailor from sailor_master;
+ -+
| youngest_sailor |
+ -+
| 16 |
+ -+
1 row in set (0.00 sec)

vi. Find the age of oldest sailor

mysql> select max(age)oldest_sailor from sailor_master;
+ +
| oldest_sailor |
+ +
| 63.5 |
+ +
1 row in set (0.00 sec)

vii. Find the age of youngest sailor whose rating level is 8

mysql> select min(age)youngest_sailor from sailor_master
where rating=8;
+ +
| youngest_sailor |
+ +
| 25.5 |
+ +
1 row in set (0.00 sec)

viii. Find the sum of age of sailors with rating greater than 5
mysql> select sum(age)sum_of_age from sailor_master where
rating>5;
+ +
| sum_of_age |
+ +
| 247.5 |
+ +
1 row in set (0.00 sec)

ix. Display the names of sailor having ‘o’ as a second letter

mysql> select sname from sailor_master where sname like'_o%';
+ +
| sname |
+ +
| Bob |
| Zoro |
| Horatio |
+ +
3 rows in set (0.00 sec)

28
 x. Display the count of sailor having rating as 3
mysql> select count(*)count from sailor_master where
rating=3;
+ +
| count |
+ +
| 2 |
+ +
1 row in set (0.01 sec)

xi. Sort the sailors details based on rating

mysql> select * from sailor_master order by rating;
+ + + + +
| sid | sname | rating | age |
+ + + + +
| 29 | Bob | 1 | 33 |
| 95 | Hary | 3 | 63.5 |
| 85 | crist | 3 | 25.5 |
| 64 | Lubber | 7 | 35 |
| 22 | Alice | 7 | 45 |
| 32 | Ravi | 8 | 25.5 |
| 31 | Zoro | 8 | 55.5 |
| 74 | Andy | 9 | 35 |
| 58 | Raj | 10 | 35.5 |
| 71 | Horatio | 10 | 16 |
+ + + + +
10 rows in set (0.00 sec)

xii. Display the sailors name with high rating and age between 30 to 40
mysql> select *from sailor_master where rating == (select
max(rating) from sailor_master) and (age > 30 and age < 40);
+ + + + +
| sid | sname | rating | age |
+ + + + +
| 58 | Raj | 10 | 35.5 |
+ + + + +
1 rows in set (0.00 sec)

RESULT:

Thus, the simple queries using aggregated functions are written and executed successfully.

29
 Ex. No. 4 WORKING WITH NESTED QUERIES AND SUB QUERIES
DATE:

AIM:
To write nested and sub queries.
QUERY BASE:

Foreign key creation:

Syntax:
create table tablename(column1 datatype1,column2 datatype2,….child columnname
references parent tablename(parent columnname);
Nested Query:
Syntax:
select column_name from table_name where column_name expression operator (select
column_name from table_name where ... );
DATABASE:
department
Columnname Datatype Size
dno number Primary key
dname varchar2 30
location varchar2 20
numstaff number

employee
Columnname Datatype Size
eno number Primary key
ename varchar2 30

30
 salary number
supno number
dno number Foreign key

works
Columnname Datatype Size
eno number Foreign key
pno number Foreign key
role varchar2 20

project
Columnname Datatype Size
pno number Primary key
pname varchar2 30
ptype varchar2 20
budget number

ER DIAGRAM:

salary
enam
location
dname
supn
numstaf
eno
dno dno

department works_ employee

eno pno

ptype work
pname
s
budget
role
pno

project

31
 QUERY:
a. Create the following table
mysql> create table employee(eno int,ename
varchar(20),scalary int,supno int,dno int,primary
key(eno),foreign key(dno) references dep(dno));
mysql> describe employee;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| eno | int(11) | NO | PRI | 0 | |
| ename | varchar(20) | YES | | NULL | |
| scalary | int(11) | YES | | NULL | |
| supno | int(11) | YES | | NULL | |
| dno | int(11) | YES | MUL | NULL | |
+ + + + + + +

mysql> create table work(eno int,pno int,role

varchar(20),foreing key(pno) references project(pno),foreing
key(eno)references employee(eno));
mysql> describe work;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| eno | int(11) | YES | MUL | NULL | |
| pno | int(11) | YES | MUL | NULL | |
| role | varchar(20) | YES | | NULL | |
+ + + + + + +

mysql> create table project(pno int,pname varchar(20),ptype

varchar(20),budget int,primary key(pno));
mysql> describe project;
+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| pno | int(11) | NO | PRI | 0 | |
| pname | varchar(20) | YES | | NULL | |
| ptype | varchar(20) | YES | | NULL | |
| budget | int(11) | YES | | NULL | |
+ + + + + + +

32
 mysql> create table dep (dno int,dname varchar(20),location
varchar (20),numstaf int,primary key(dno));
mysql> describe dep;
+ + + + + +
+
| Field | Type | Null | Key | Default | Extra
|
+ + + + + +
+
| dno | int(11) | NO | PRI | 0 |
|
| dname | varchar(20) | YES | | NULL |
|
| location | varchar(20) | YES | | NULL |
|
| numstaf | int(11) | YES | | NULL |
|
+ + + + + +
+
4 rows in set (0.00 sec)

mysql> select * from employee;

+ + + + + +
| eno | ename | scalary | supno | dno |
+ + + + + +
| 1 | [Link] mam | 100000 | 101 | 1 |
| 2 | [Link] | 10000 | 102 | 1 |
| 3 | [Link] | 1000034 | 103 | 1 |
| 5 | madhu | 100000 | 106 | 4 |
+ + + + + +
4 rows in set (0.00 sec)

mysql> select * from dep;

+ + + + +
| dno | dname | location | numstaf |
+ + + + +
| 1 | it | blocka | 5 |
| 2 | ece | blockb | 6 |
| 3 | cse | blockc | 8 |
| 4 | service | blocke | 3 |
+ + + + +
4 rows in set (0.00 sec)

mysql> select * from work;

+ + + +
| eno | pno | role |
+ + + +
| 1 | 1 | leader |
| 2 | 2 | sub leader |
| 3 | 3 | head leader |
| 1 | 9 | head1 leader |

33
 + + + +
4 rows in set (0.00 sec)

b. Answer to the following queries

i. Get a list of project names of the employees working on them
mysql> select pname from project where pno in (select pno
from work where eno in (select eno from employee));
+ +
| pname |
+ +
| payroll |
| database |
| voter id |
+ +

34
 ii. Get the names of employees in the ‘information’ department
mysql> select ename from employee where dno in (select dno
from dep where dname='it');
+ +
| ename |
+ +
| [Link] mam |
| [Link] |
| [Link] |
+ +

iii. Get the names of all projects worked on by the employee.

mysql> select pname from project where pno in (select pno
from work where eno in (select eno from employee));
+ +
| pname |
+ +
| payroll |
| database |
| voter id |
+ +

iv. Get a list of employee names earning more than £25000

mysql> select ename from employee where scalary > 25000;
+ +
| ename |
+ +
| [Link] mam |
| [Link] |
| madhu |
+ +
3 rows in set (0.00 sec)

v. Get the employees with a lower than average salary

mysql> select ename from employee where scalary > (select
avg(scalary) from employee);
+ +
| ename |
+ +
| [Link] |
+ +

vi. Display the total number of projects worked by each employee

mysql> select count(pno) from work group by eno;
+ +
| count(pno) |
+ +
| 1 |
| 1 |
| 1 |

35
 + +

vii. How many employees are there in the ‘information’ department?

mysql> select count(ename) from employee where dno in (select
dno from dep where
dname='it');
+ +
| count(ename) |
+ +
| 3 |
+ +

viii. Get a list of employee names for projects named ‘payroll’ or ‘database’.
mysql> select ename from employee where eno in (select eno
from work where pno in (select pno from project where pname
='payroll'or pname='database'));
+ +
| ename |
+ +
| [Link] mam |
| [Link] |
+ +

ix. Get the names of all employees with an above average salary.
mysql> select ename from employee where scalary > (select
avg(scalary) from employee);
+ +
| ename |
+ +
| [Link] |
+ +

x. Get the names and salaries of all employees in the ‘information’ department
with salaries above average for the ‘information’ department.
mysql> select ename,scalary from employee where scalary
>(select avg(scalary) from employee where dno in(select dno
from dep where dname='IT'))and dno in(select dno from dep
where dname='IT');
+ + +
| ename | scalary |
+ + +
| [Link] | 1000034 |
+ + +
1 row in set (0.00 sec)

36
 xi. Get the employee name and their department who is working with the project
worth between 30000 to 50000
mysql> select ename from employee where eno in (select eno
from work where pno in (select pno from project where budget
> 30000 and budget < 50000));
+ +
| ename |
+ +
| [Link] mam |
+ +

xii. Get the names and salaries of employees in the ‘information’ department who
has a higher salary than maximum salary in the ‘service’ department
mysql> select ename from employee where eno in (select eno
from work where pno in (select pno from project where budget
> 30000 and budget < 50000));
+ +
| ename |
+ +
| [Link] mam |
+ +

xiii. Sort the projects based on ptype

mysql> select * from project order by ptype;
+ + + + +
| pno | pname | ptype | budget |
+ + + + +
| 1 | payroll | tupe A | 10000 |
| 2 | database | tupe b | 10001 |
| 3 | voter id | tupe c | 20000 |
+ + + + +

RESULT:
Thus, the nested and sub queries are written and executed successfully.

37
 Ex. No. 5 WORKING WITH SQL JOIN
DATE:
AIM:
To write queries using SQL Join.
QUERY BASE:
SQL JOIN
 JOIN means to combine something. In case of SQL, JOIN means "to combine two or
more tables".
Types of SQL JOIN
 INNER JOIN
 LEFT JOIN
 RIGHT JOIN
1. INNER JOIN
 In SQL, INNER JOIN selects records that have matching values in both tables as long
as the condition is satisfied. It returns the combination of all rows from both the tables
where the condition satisfies.

Syntax:
select columns from table1 inner join table2 on [Link] = [Link];
2. LEFT OUTER JOIN
 The LEFT OUTER JOIN returns all rows from the left hand table specified in the ON
condition and only those rows from the other table where the join condition is fulfilled.

38
 Syntax:
select columns from table1 left [outer] join table2 on [Link] = [Link];

3. RIGHT OUTER JOIN

 The MySQL Right Outer Join returns all rows from the RIGHT-hand table specified in
the ON condition and only those rows from the other table where he join condition is
fulfilled.

Syntax:
select columns from table1 right [outer] join table2 on [Link] = [Link];
DATABASE:
department
Columnname Datatype Size
dno number Primary key
dname varchar2 30
location varchar2 20

doctor
Columnname Datatype Size
ssn number Primary key
docname varchar2 30
salary number
dno number Foreign key

takescare
Columnname Datatype Size
ssn number Foreign key
pid number Foreign key

patient
Columnname Datatype Size

39
 pid number Primary key
pname varchar2 30
disease varchar2 20
date_of_admission Date
ward_no number

ER DIAGRAM:

salary
docname
location
dname

ssn
dno dno

department works_ doctor

ssn pid

disease
pname takes
care
date_of_a
dmission
pid
ward_no

patient

QUERY:
a. Create the following table

40
 mysql> create database hospital;
Query OK, 1 row affected (0.00 sec)

mysql> use hospital;

Database changed
mysql> create table department(dno int,dname
varchar(30),location varchar(20),primary key(dno));
Query OK, 0 rows affected (0.22 sec)

mysql> describe hospital;

ERROR 1146 (42S02): Table '[Link]' doesn't
exist
mysql> describe department;
+ + + + + +
+
| Field | Type | Null | Key | Default | Extra
|
+ + + + + +
+
| dno | int(11) | NO | PRI | 0 |
|
| dname | varchar(30) | YES | | NULL |
|
| location | varchar(20) | YES | | NULL |
|
+ + -+ + + +
+
3 rows in set (0.01 sec)

mysql> create table doctor(ssn int,docname

varchar(30),salary int,dno int,primary key(ssn),foreign
key(dno) references department(dno));
Query OK, 0 rows affected (0.18 sec)

mysql> describe doctor;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| ssn | int(11) | NO | PRI | 0 | |
| docname | varchar(30) | YES | | NULL | |
| salary | int(11) | YES | | NULL | |
| dno | int(11) | YES | MUL | NULL | |
+ + + + + + +
4 rows in set (0.00 sec)

mysql> create table patient(pid int,pname

varchar(30),disease varchar(20),date_of_admission
date,ward_no int,primary key(pid));
Query OK, 0 rows affected (0.24 sec)

41
 mysql> describe patient;
+ + + + +
-+ +
| Field | Type | Null | Key | Default
| Extra |
+ + + + +
-+ +
| pid | int(11) | NO | PRI | 0
| |
| pname | varchar(30) | YES | | NULL
| |
| disease | varchar(20) | YES | | NULL
| |
| date_of_admission | date | YES | | NULL
| |
| ward_no | int(11) | YES | | NULL
| |
+ + + + +
-+ +
5 rows in set (0.01 sec)

mysql> create table takescare(ssn int,pid int,foreign

key(ssn) references doctor(ssn),foreign key(pid)
references patient(pid));
Query OK, 0 rows affected (0.23 sec)

mysql> describe takescare;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| ssn | int(11) | YES | MUL | NULL | |
| pid | int(11) | YES | MUL | NULL | |
+ + + + + + +
2 rows in set (0.00 sec)

mysql> insert into department values(1,'Ortho','block

A');
Query OK, 1 row affected (0.04 sec)

mysql> insert into department values(2,'Surgery','block

B');
Query OK, 1 row affected (0.03 sec)

mysql> insert into department values(2,'Child

specialist','block C');
ERROR 1062 (23000): Duplicate entry '2' for key
'PRIMARY'

42
 mysql> insert into department values(3,'Child
specialist','block C');
Query OK, 1 row affected (0.03 sec)

mysql> select *from department;

+ + + +
| dno | dname | location |
+ + + +
| 1 | Ortho | block A |
| 2 | Surgery | block B |
| 3 | Child specialist | block C |
+ + + +
3 rows in set (0.00 sec)

mysql> insert into doctor

values(10,'Varshini',100000,2);
Query OK, 1 row affected (0.08 sec)

mysql> insert into doctor values(20,'Shanthi',60000,3);

Query OK, 1 row affected (0.02 sec)

mysql> insert into doctor values(25,'Ganesan',65000,1);

Query OK, 1 row affected (0.02 sec)

mysql> insert into doctor values(25,'Jana',45000,1);

ERROR 1062 (23000): Duplicate entry '25' for key
'PRIMARY'
mysql> insert into doctor values(35,'Jana',45000,1);
Query OK, 1 row affected (0.03 sec)

mysql> describe doctor;

+ + + + + + +
| Field | Type | Null | Key | Default | Extra |
+ + + + + + +
| ssn | int(11) | NO | PRI | 0 | |
| docname | varchar(30) | YES | | NULL | |
| salary | int(11) | YES | | NULL | |
| dno | int(11) | YES | MUL | NULL | |
+ + + + + + +
4 rows in set (0.00 sec)

mysql> select *from doctor;

+ + + + +
| ssn | docname | salary | dno |
+ + + + +
| 10 | Varshini | 100000 | 2 |
| 20 | Shanthi | 60000 | 3 |
| 25 | Ganesan | 65000 | 1 |
| 35 | Jana | 45000 | 1 |
+ + + + +

43
 4 rows in set (0.00 sec)

mysql> insert into patient

values(1,'Karthick','fracture','2020/01/21',1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into patient

values(2,'Ram','fever','2020/01/20',2);
Query OK, 1 row affected (0.02 sec)

mysql> insert into patient

values(3,'Ram','fever','2020/01/19',2);
Query OK, 1 row affected (0.03 sec)

mysql> insert into patient

values(4,'Sugi','Malaria','2020/01/21',4);
Query OK, 1 row affected (0.02 sec)

mysql> insert into patient

values(5,'Anitha','dengue','2020/01/18',3);
Query OK, 1 row affected (0.02 sec)

mysql> select *from patient;

+ + + + +
--+
| pid | pname | disease | date_of_admission |
ward_no |
+ + + + +
- -+
| 1 | Karthick | fracture | 2020-01-21 |
1 |
| 2 | Ram | fever | 2020-01-20 |
2 |
| 3 | Ram | fever | 2020-01-19 |
2 |
| 4 | Sugi | Malaria | 2020-01-21 |
4 |
| 5 | Anitha | dengue | 2020-01-18 |
3 |
+ + + + +
--+
5 rows in set (0.00 sec)

mysql> insert into takescare values(10,1);

Query OK, 1 row affected (0.02 sec)

mysql> insert into takescare values(35,2);

Query OK, 1 row affected (0.02 sec)

44
 mysql> insert into takescare values(25,4);
Query OK, 1 row affected (0.03 sec)

mysql> insert into takescare values(20,4);

Query OK, 1 row affected (0.02 sec)

mysql> insert into takescare values(35,3);

Query OK, 1 row affected (0.02 sec)

mysql> select *from takescare;

+ + +
| ssn | pid |
+ + +
| 10 | 1 |
| 35 | 2 |
| 25 | 4 |
| 20 | 4 |
| 35 | 3 |
+ + +
5 rows in set (0.00 sec)

i. Get the names of doctor in the ‘surgery’ department

mysql> select [Link] from doctor d,department de where
[Link]='Surgery' and [Link]=[Link];
+ +
| docname |
+ +
| Varshini |
+ +
1 row in set (0.00 sec)

ii. Get the names of all patients who take treatment under the doctor. Get the
name during runtime.
mysql> select [Link] from patient p,takescare t,doctor d
where [Link]=[Link] and [Link]=[Link] and
[Link]='Varshini';
+ +
| pname |
+ +
| Karthick |
+ +
1 row in set (0.00 sec)

iii. Get a list of doctor names with their department names for doctor earning
more than Rs. 50000

45
 mysql> select [Link],[Link] from department de,doctor
d where [Link]>50000 and [Link]=[Link];
+ + +
| docname | dname |
+ + +
| Varshini | Surgery |
| Shanthi | Child specialist |
| Ganesan | Ortho |
+ + +
3 rows in set (0.00 sec)

iv. Get the doctor name with a lower than average salary
mysql> select docname from doctor where salary<(select
avg(salary) from doctor);
+ +
| docname |
+ +
| Shanthi |
| Ganesan |
| Jana |
+ +
3 rows in set (0.00 sec)

v. Display the total number of patients taking treatment under each doctor
mysql> select count(pid) from takescare group by ssn;
+ +
| count(pid) |
+ +
| 1 |
| 1 |
| 1 |
| 2 |
+ +
4 rows in set (0.00 sec)

vi. How many doctors are there in the ‘child specialist’ department?
mysql> select count(ssn) from doctor where dno in(select
dno from department where dname='Child specialist');

+ +
| count(ssn) |
+ +
| 2 |
+ +
1 rows in set (0.00 sec)

46
 vii. Get a list of doctor names who inspect ward number 2.
mysql> select [Link] from doctor d,takescare t,patient
p where p.ward_no=2 and [Link]=[Link] and [Link]=[Link];
+ +
| docname |
+ +
| Jana |
+ +
1 rows in set (0.00 sec)

viii. Get the names of doctor who is having maximum salary.

mysql> select docname from doctor where salary=(select
max(salary)fromdoctor);
+ +
| docname |
+ +
| Varshini |
| Shanthi |
| Ganesan |
| Jana |
+ +
4 rows in set (0.00 sec)

ix. Get the names and salaries of all doctor the ‘ortho’ department with salaries
above average for the ‘ortho' department.
mysql> select [Link],[Link] from doctor d,department
de where [Link]='Ortho' and [Link]=[Link] and
salary>(select avg(salary) from doctor d,department de
where [Link]='Ortho' and [Link]=[Link]);
+ + +
| docname | salary |
+ + +
| Ganesan | 65000 |
+ + +
1 row in set (0.00 sec)

x. Display the name of the doctor who are taking care of more than two patient.
mysql> select [Link] from doctor d,takescare t group by
[Link] having count([Link]>2);
+ +
| docname |
+ +
| Varshini |
+ +
1 rows in set (0.00 sec)

xi. Get the doctor name and their department who is inspecting ward 1 and 4.

47
 mysql> select [Link],[Link] from doctor d,department
de,takescare t,patient p where p.ward_no=1 and p.ward_no=4
and [Link]=[Link] and [Link]=[Link] and [Link]=[Link];
+ + +
| docname | dname |
+ + +
| Ganesan | Ortho |
+ + -+
1 rows in set (0.00 sec)

xii. Sort the doctor name based on docname

mysql> select docname from doctor order by docname;
+ +
| docname |
+ +
| Ganesan |
| Jana |
| Shanthi |
| Varshini |
+ +
4 rows in set (0.00 sec)

RESULT:
Thus, the queries using Joins are written and executed successfully.
48
 Ex. No. 9 Write user defined functions and stored procedures in SQL
DATE:

AIM:
To write queries using SQL views, synonyms and sequence.
QUERY BASE:
SQL VIEW
 View is a virtual table created by a query by joining one or more tables.
I. CREATING VIEW
 A VIEW is created by SELECT statements. SELECT statements are used to take data
from the source table to make a VIEW.
Syntax:
create [or replace] view view_name as select columns from tables [where conditions];
Parameters:
 OR REPLACE: It is optional. It is used when a VIEW already exist. If you do not
specify this clause and the VIEW already exists, the CREATE VIEW statement will
return an error.
 view_name: It specifies the name of the VIEW that you want to create in MySQL.
 WHERE conditions: It is also optional. It specifies the conditions that must be met for
the records to be included in the VIEW.
II. DISPLAY VIEW
Syntax:
SELECT * FROM VIEW_NAME;
III. UPDATE VIEW
 ALTER VIEW statement is used to modify or update the already created VIEW without
dropping it.
Syntax:
alter view view_name as select columns from table where conditions;

49
 IV. DROP VIEW
Syntax:
drop view [if exists] view_name;
Parameters:
 view_name: It specifies the name of the VIEW that you want to drop.

 IF EXISTS: It is optional. If you do not specify this clause and the VIEW doesn't exist,
the DROP VIEW statement will return an error.
SQL SEQUENCES
 A sequence is a set of integers 1, 2, 3, ... that are generated in order on a specific
demand.
 Sequences are frequently used in the databases because many applications require each
row in a table to contain a unique value and sequences provide an easy way to generate
them.
AUTO_INCREMENT Column
Syntax:
create table tableName (columnName datatype NOT NULL AUTO_INCREMENT =
value, ...);

DATABASE:
flight_header
Columnname Datatype Size
flight_id number Primary key
flight_name varchar2 25

ticket_header
Columnname Datatype Size
flight_id number Foreign key
ticket_no number Foreign key
origin varchar2 30
destination varchar2 30
no_of_adults number
no_of_children number

ticket_detail

50
 Columnname Datatype Size
ticket_no number Primary key
name varchar2
gender varchar2
age number
fare number

ER DIAGRAM:
origin
ticket_no
Flight_name
flight_id destination
flight_id

flight_header ticket_heade

no_of_adults

gender no_of_child
name ren
age

ticket_no
fare

ticket_details

QUERY:
a. Create the following table (use sequence in flight_id in flight_header table)

51
 mysql> create table flight_header(flight_id
int,flight_name varchar(20),primary key(flight_id));
ERROR 1046 (3D000): No database selected
mysql> use six;
Database changed
mysql> create table flight_header(flight_id
int,flight_name varchar(20),primary key(flight_id));
Query OK, 0 rows affected (0.25 sec)

mysql> insert into flight_header values(1,"Air india");

Query OK, 1 row affected (0.04 sec)

mysql> insert into flight_header values(2,"Air asia");

Query OK, 1 row affected (0.02 sec)

mysql> insert into flight_header values(3,"King

fisher");
Query OK, 1 row affected (0.03 sec)

mysql> create table ticket_details(ticket_no int,name

varchar(20),gender varchar(20),age int,fare int,primary
key(ticket_no));
Query OK, 0 rows affected (0.19 sec)

mysql> insert into ticket_details

values(1,"sri","female",19,17000);
Query OK, 1 row affected (0.02 sec)

mysql> insert into ticket_details

values(2,"malini","female",18,2000);
Query OK, 1 row affected (0.03 sec)

mysql> drop table ticket_details;

Query OK, 0 rows affected (0.06 sec)

mysql> create table ticket_details(ticket_no int,name

varchar(20),gender varchar(20),age int,fare int,primary
key(ticket_no));
Query OK, 0 rows affected (0.20 sec)

mysql> insert into ticket_details

values(1,"sri","female",19,17000);
Query OK, 1 row affected (0.02 sec)

mysql> insert into ticket_details

values(2,"malini","female",18,20000);
Query OK, 1 row affected (0.03 sec)

mysql> insert into ticket_details

values(3,"anu","female",45,10000);
Query OK, 1 row affected (0.03 sec)

52
 mysql> insert into ticket_details
values(4,"dhanvessh","male",3,15000);
Query OK, 1 row affected (0.02 sec)

mysql> insert into ticket_details

values(5,"santhosh","male",15,11000);
Query OK, 1 row affected (0.02 sec)

mysql> create table ticket_header(flight_id

int,ticket_no int,origin varchar(20),destination
varchar(20),no_of_adults int,no_of_children int,foreign
key(flight_id)references
flight_header(flight_id),foreign
key(ticket_no)references ticket_details(ticket_no));
Query OK, 0 rows affected (0.25 sec)

mysql> insert into ticket_header

values(1,1,"madurai","london",2,3);
Query OK, 1 row affected (0.02 sec)

mysql> insert into ticket_header

values(1,2,"chennai","mumbai",2,3);
Query OK, 1 row affected (0.02 sec)

mysql> insert into ticket_header

values(1,3,"madurai","chennai",2,3);
Query OK, 1 row affected (0.02 sec)

i. Display the ticket number from both ticket_header and ticket_detail

mysql> create view tno as(select ticket_no from
ticket_header) union (select ticket_no from ticket_details);
Query OK, 0 rows affected (0.02 sec)

mysql> select * from tno;

+ +
| ticket_no |
+ +
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+ +
5 rows in set (0.00 sec)

53
 ii. Display only the common flight_id that are present in flight_header and
ticket_header
mysql> create view flig as(select flight_id from
flight_header where flight_id in(select flight_id from
ticket_header));
Query OK, 0 rows affected (0.03 sec)

mysql> select * from flig;

+ +
| flight_id |
+ +
| 1 |
+ +
1 row in set (0.00 sec)

iii. Display the flight that is not booked

mysql> create view fb as(select flight_id from flight_header
where flight_id not in(select flight_id from
ticket_header));
Query OK, 0 rows affected (0.03 sec)

mysql> select * from fb;

+ +
| flight_id |
+ +
| 2 |
| 3 |
+ +
2 rows in set (0.00 sec)

iv. Create a view for the ticket_header table and display the number of details in
the view
mysql> create view tc as(select count(*)from ticket_header);
Query OK, 0 rows affected (0.03 sec)

mysql> select * from tc;

+ +
| count(*) |
+ +
| 3 |
+ +
1 row in set (0.00 sec)

54
 v. Create a view to display the passenger detail of the flight having id 01

mysql> create view dp as(select * from ticket_details where

ticket_no in(select ticket_no from ticket_header where
flight_id=1));
Query OK, 0 rows affected (0.03 sec)

mysql> select * from dp;

+ + + + + +
| ticket_no | name | gender | age | fare |
+ + + + + +
| 1 | sri | female | 19 | 17000 |
| 2 | malini | female | 18 | 20000 |
| 3 | anu | female | 45 | 10000 |
+ + + + + +
3 rows in set (0.00 sec)
vi. Create a view for ticket_detail having flight id 01 and display the total amount
earned by flight 01 from view
mysql> create view fs as(select sum(fare) from ticket_details
where ticket_no in(select ticket_no from ticket_header where
flight_id=1));
Query OK, 0 rows affected (0.03 sec)

mysql> select * from fs;

+ +
| sum(fare) |
+ +
| 47000 |
+ +
1 row in set (0.00 sec)

vii. Create a view to display the name of the flight booked by the person alice
mysql> create view fn as(select flight_name from
flight_header where flight_id in(select flight_id from
ticket_header where ticket_no in(select ticket_no from
ticket_details where name="alice")));
Query OK, 0 rows affected (0.04 sec)

mysql> select * from fn;

+ +
| flight_name |
+ +
| Air india |
+ +
1 row in set (0.00 sec)

55
 viii. Create a view to display the name of the person how is youngest in the
ticket_detail table
mysql> create view dd as(select name from ticket_details
where age in(select min(age) from ticket_details));
Query OK, 0 rows affected (0.03 sec)

mysql> select * from dd;

+ +
| name |
+ +
| dhanvessh |
+ +
1 row in set (0.01 sec)

ix. Create a view to display the flight name starting with letter ‘k’
mysql> create view af as(select flight_name from
flight_header where flight_name like 'k%');
Query OK, 0 rows affected (0.03 sec)

mysql> select * from af;

+ +
| flight_name |
+ +
| King fisher |
+ +
1 row in set (0.00 sec)

x. Create a view to display the total passenger and flight id for each flight
mysql> create view dt as(select
count(t.ticket_no),f.flight_id from ticket_details
t,ticket_header f where f.ticket_no=t.ticket_no group by
f.flight_id);
Query OK, 0 rows affected (0.03 sec)

mysql> select * from dt;

+ + +
| count(t.ticket_no) | flight_id |
+ + +
| 3 | 1 |
+ + +
1 row in set (0.00 sec)

56
 xi. Alter the view created in Q.x. to display the total fare collected in each flight
mysql> alter view dt as(select sum([Link]),f.flight_id from
ticket_details t,ticket_header f where
f.ticket_no=t.ticket_no group by f.flight_id); Query OK, 0
rows affected (0.03 sec)

mysql> select * from dt;

+ + +
| sum([Link]) | flight_id |
+ + +
| 47000 | 1 |
+ + +
1 row in set (0.00 sec)

RESULT:
Thus, queries using views, synonyms and sequences are written and executed
successfully.

57
 Ex. No. 6 Write user defined functions and stored procedures in SQL
DATE:
AIM:
To write queries using PL/SQL procedures.
QUERY BASE:
 A procedure can return one or more than one value through parameters or may not
return at all. The procedure can be used in SQL queries.
I. CREATING PROCEDURE
Syntax
CREATE PROCEDURE procedure_name[ (parameter datatype [, parameter datatype]) ]
BEGIN
Declaration_section
Executable_section
END;
Parameter:
 procedure_name: name of the procedure.
 Parameter: number of parameters. It can be one or more than one.
 declaration_section: all variables are declared.
 executable_section: code for the function is written here.
II. DROP A PROCEDURE
 When a procedure is dropped, it is removed from the database.
Syntax:
Drop procedure[ IF EXISTS ] procedure_name;
III. CALL A PROCEDURE
mysql> call procedure_name;
DATABASE:
ticket_detail
Columnname Datatype Size
ticket_no number Primary key
name varchar2
gender varchar2
age number
fare number

58
 ER DIAGRAM:

gender
name
age

ticket_no
fare

ticket_details

QUERY:
i. Write a procedure to display the female passenger list
mysql> create procedure p1()
-> select *from ticket_detail where gender='female';
Query OK, 0 rows affected (0.02 sec)

mysql> call p1();

+ + + + + +
| ticket_no | name | gender | age | fare |
+ + + + + +
| 18 | Praveena | female | 18 | 500 |
| 21 | varshini | female | 15 | 400 |
| 25 | shanthi | female | 35 | 550 |
+ + + + + +
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ii. Write a procedure to display the passenger detail who are gents and having
the highest ticket fare
mysql> create procedure p2()
-> select *from ticket_detail where gender='male' and
fare=(select max(fare) from ticket_detail);
Query OK, 0 rows affected (0.00 sec)
mysql> call p2();

+ + + + + +
| ticket_no | name | gender | age | fare |
+ + + + + +
| 5 | karthick | male | 15 | 600 |
+ + + + + +
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

59
 iii. Write a procedure to display the name of the passengers who have age ranging
from 20 to 45. Display in capital letter
mysql> create procedure p3()
-> select upper(name) from ticket_detail where age>20 and
age<45;
Query OK, 0 rows affected (0.00 sec)

mysql> call p3();

+ +
| upper(name) |
+ +
| GANESAN |
| SHANTHI |
+ +
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

iv. Write a procedure to display the name of the passenger who has the fare above
the average fare. Display in lower case
mysql> create procedure p4()
-> select lower(name) from ticket_detail where
fare>(select avg(fare) from ticket_detail);
Query OK, 0 rows affected (0.00 sec)
mysql> call p4;
+ +
| lower(name) |
+ +
| karthick |
| praveena |
| shanthi |
+ +
3 rows in set (0.00 sec)

v. Write a procedure to display the passenger name who is having second letter
as ‘l’
mysql> create procedure p5()
-> select name from ticket_detial where name like '_a%';
Query OK, 0 rows affected (0.00 sec)
mysql> call p5();

+ +
| name |
+ +
| karthick |
| Ganesan |
| varshini |
+ +
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

60
 RESULT:
Thus, queries using procedures are written and executed successfully.
61
 Ex. No. 7 Execute complex transactions and realize DCL and TCL commands
DATE:

AIM:
To execute complex transactions and also realize various DCL and
TCL commands.

DCL is short name of Data Control Language which includes commands

such as GRANT and mostly concerned with rights,permissions and other
controls of the database system.
 GRANT - allow users access privileges to the database
 REVOKE - withdraw users access privileges given by using the GRANT command

Tables Used: Consider the following tables namely

"DEPARTMENTS" and "EMPLOYEES"

Their schemas are as follows , Departments ( dept _no , dept_ name , dept_location
);Employees ( emp_id , emp_name , emp_salary );

Q1: Develop a query to grant all privileges of employees table into departments
table

Ans: SQL> Grant all on employees to

departments; Grant succeeded.

Q2: Develop a query to grant some privileges of employees table into departments
table
Ans: SQL> Grant select, update , insert on departments to
departments with grant option; Grant succeeded.

Q3: Develop a query to revoke all privileges of employees

table from departments table
Ans: SQL> Revoke all on employees from departments; Revoke succeeded.

Q4: Develop a query to revoke some privileges of employees table from

departments
table
Ans: SQL> Revoke select, update , insert on departments from departments; Revoke

62
 TCL is short name of Transaction Control Language which deals with a
transaction within a database.
 COMMIT - commits a Transaction
 SAVEPOINT - to roll back the transaction making points within groups
 ROLLBACK - rollback a transaction in case of any error occurs

63
 Thus the SQL program used to complex transactions DCL and TCL
commands are executed successfully.

64
EX NO:8 W
Date:
Write SQL Triggers for insert, delete, and update operations in a database table.

AIM:
To write SQL Triggers for insert, delete, and update operations in a database
table.

 Trigger is invoked by Oracle engine automatically whenever a specified event

occurs. Trigger is stored intodatabase and invoked repeatedly, when specific condition match.
 Triggers are stored programs, which are automatically executed or fired when some event occurs.
 Triggers are written to be executed in response to any of the following events.
 A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
 A database definition (DDL) statement (CREATE, ALTER, or DROP).
 A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
 Triggers could be defined on the table, view, schema, or database with which the event is associated.

65
RESULT:

Thus the PL/SQL Triggers program was created and executed successfully.

66
EX NO:10 Create an XML database and validate it using XML schema.
DATE:

AIM:
To create an XML database to validate it using XML Schema

PROCEDURE:

The XML schemas are used to represent the

The goal or purpose of XML schema is to define the building blocks of an XMLdocument.
These can be used as analternative to XML DTD.
The XML schema language is called as XML Schema Definition (XSD) language.
XML schema defines elements, attributes, elements having child elements, order of child elements. It
also definesfixed and default values of elements and attributes.
XML schema also allows the developer to us data
types.

67
OUTPUT:

RESULT:
Thus the XML database was created and validated successfully.

68
EX NO:11 Cr Create Document, column and graph based data using NOSQL database tools.
DATE:

AIM:
To create a Document, column and Graph based data using NOSQL Database

PROCEDURE:
DOCUMENT ORIENTED DATABASE NOSQL DATABASE TOOLS(MONGO DB)
 A collection of documents
 Data in this model is stored inside documents.
 A document is a key value collection where the key allows access to its value.
 Documents are not typically forced to have a schema and therefore are flexible andeasy to change.
 Documents are stored into collections in order to group different kinds of data.
 Documents can contain many different key-value pairs, or key-array pairs, or even nested
documents.
 Here is a comparison between the classic relational model and the document model
 Example of Document Oriented databases : MongoDB, CouchDB

SINGLE DOCUMENT INSERTION QUERY:

OUTPUT:

69
MULTIPLE DOCUMENT INSERTION QUERY:

OUTPUT:

70
COLUMN BASED DATABASE NOSQL DATABASE TOOL(CASANDRA)
 Column-oriented databases primarily work on columns and every column is treated
individually.
 Values of a single column are stored contiguously.
 Column stores data in column specific files.
 In Column stores, query processors work on columns too.
 All data within each column data file have the same type which makes it ideal for compression.
 Column stores can improve the performance of queries as it can access specific column data.
 Example of Column-oriented databases : BigTable, Cassandra, SimpleDB etc.

71
GRAPH BASED DATABASE:

A Graph datastructure consist of a finite set of ordered pairs called edges or arcs of certain entities called nodes or edges

h
OrientDB database is not only a Document database but also a Graph database.
New concepts such as Vertex and Edge are used to store the data in the
form of graph. It applies polymorphism on vertices. The base class for Vertex
is V.

Vertex Creation

Execute the following query to create a vertex without 'name' and on the base class V.

If the above query is executed successfully, you will get the following output

Execute the following query to create a new vertex class named v1, then create vertex in
that class.

Execute the following query to create a new vertex of the class named v1,
defining its properties such as brand = 'Maruti'and name = 'Swift'.

72
RESULT:

Thus the NOSQL database tools are used to Document based, Column Based
and Graph based databases are created andexecuted successf

73
EX NO:12
DATE:
Devolep a simple GUI based database application and
incorporate all

AIM:
To create a Simple GUI based database application and incorporate all the features

PROCEDURE:
[Link] A SIMPLE CUSTOMER SCREEN WHICH TAKES CUSTOMER
Add Labels from the Tool Box and Add corresponding Text Boxes for Name and Country
field.
Use radioButton when you want to select a single option from multiple choices.
To keep the radio Buttons in a group, first Drag a GroupBox and inside it drag the
radioButtons.
Use checkbox when you want to select multiple options from a set of choices.
Give unique names for each radioButtons and checkboxes
In the following form radioButton for Male is named as radioMale and for Female as
radioFemale
Similarly radioButton for Married is named as radioMarried and for Unmarried as
radioUnmarried.
Finally, a Preview Button is added at the bottom of the form which when
clicked will show the given data inanother form.

74
CREATING A PREVIEW SCREEN

75
 CREATING NAVIGATIONAL MENUS:

To create a user friendly interface we can use something called

MDIParent Form and name it as MDICustomer.
2In the Design View of MDIParent, Click File and you will find some menu options
Edit a menu name and give it Enter Customer 5. Then double click the Enter Customer
6. When this menu is clicked you have to invoke the Customer Data Entry Form which isnamed here
asfrmCustomerDataEntry
To keep the form within the MDIParent select the [Link] = this;
To show the form use the function Show ()
in the [Link] file specify the form MDICustomer. This will invoke the
MDICustomer form when the application isrun.

76
 REUSING CODE BY CREATING CLASSES AND OBJECTS AND FORM VALIDATION

1. An important feature of programming is to avoid the reusability of code.

2. To write the validations of different types, create a new class.

3. View  Solution Explorer  Select your project  Right Click  Add  New Item  Add a newClass and
name it [Link]

4. In [Link] you can define various functions with different names and parameters. An example is

77
 givenbelow –

In frmCustomerDataEntry:
1. In the Preview button Click event we have write some code to specify that if the user does not enter any

CustomerName then an error message should be displayed.

2. We have to call the CheckCustomerName (string) function in the CustomerValidation class.

This code is specified in the try-catch block below

78
 ACQUAINTED

CONNECTING TO SQL SERVER:

As C# is a Microsoft Product, it is better to use a Microsoft Data management
software which is SQL Server.
[Link] (ActiveX Data Object) helps to connect the User Interface to the Database
and also sends and retrieves datafrom SQL Server
To Create a Database connection you have to follow the steps below –
1 e
. :

79
INSERTING INTO DATABASE:

80
RESULT:
Thus the Visual Studio C# Program used to Database Application GUI was designed
successfully.

81
EX NO:13

DATE: CASE STUDY

AIM:
To design database using ER modeling, normalization constraints and to implement
the Operations of BankManagement System using the visual basic as front end and oracle as
back end todesign a forms

PROCEDURE:
Database DESIGN USING ER MODEL AND NORMALIZATION

 Count the customers with grades above Bangalore's average.

 Find the name and numbers of all salesmen who had more than one customer.
 List all salesmen and indicate those who have and don't have customers in their cities (UseUNION
operation.)
 Create a view that finds the salesman who has the customer with the highest order of a day.
 Demonstrate the DELETE operation by removing salesman with id 1000. All his orders must also be
deleted

CREATE TABLE SALESMAN (SALESMAN_ID NUMBER (4), NAME VARCHAR2 (20),

CITY VARCHAR2 (20), COMMISSION VARCHAR2 (20), PRIMARY KEY
(SALESMAN_ID));
CREATE TABLE CUSTOMER1 (CUSTOMER_ID NUMBER (4), CUST_NAME
VARCHAR2 (20), CITY VARCHAR2 (20), GRADE NUMBER (3), PRIMARY KEY
(CUSTOMER_ID), SALESMAN_ID REFERENCES SALESMAN (SALESMAN_ID) ON
DELETE SET NULL);
CREATE TABLE ORDERS (ORD_NO NUMBER (5), PURCHASE_AMT NUMBER (10, 2),
ORD_DATE DATE, PRIMARY KEY (ORD_NO), CUSTOMER_ID REFERENCES
CUSTOMER1 (CUSTOMER_ID) ON DELETE CASCADE, SALESMAN_ID REFERENCES
SALESMAN (SALESMAN_ID) ON DELETE CASCADE);

82
Table DescriptionsDESC SALESMAN

Table DescriptionsDESC SALESMAN

DESC CUSTOMER1;DESC ORDERS;

83
 Insertion of Values to Tables
INSERT INTO SALESMAN VALUES (1000, ‗JOHN','BANGALORE','25 %'); INSERT INTO
CUSTOMER1 VALUES (10,

‗PREETHI','BANGALORE', 100, 1000);

INSERT INTO ORDERS VALUES (50, 5000, ‗04-MAY-17', 10, 1000);SELECT * FROM SALESMAN

SELECT * FROM ORDERS;

Queries

SELECT * FROM CUSTOMER's average.

SELECT GRADE, COUNT (DISTINCT CUSTOMER_ID) FROM CUSTOMER1 GROUP BY

GRADE HAVING GRADE > (SELECTAVG(GRADE) FROM CUSTOMER1 WHERE

CITY='BANGALORE');

84
List all salesmen and indicate those who have and don't have customers in their cities (UseUNION
operation.) SELECT SALESMAN.SALESMAN_ID, NAME, CUST_NAME, COMMISSION
FROM SALESMAN, CUSTOMER1 WHERE [Link] = [Link] UNION
SELECT SALESMAN_ID, NAME, 'NO MATCH', COMMISSION FROM SALESMAN
WHERE NOT CITY = ANY (SELECT CITY FROM CUSTOMER1) ORDER BY 2 DESC;

Create a view that finds the salesman who has the customer with the
highest order of a day.
CREATE VIEW ELITSALESMAN AS SELECT B.ORD_DATE, A.SALESMAN_ID,
[Link] FROM SALESMAN A, ORDERS B WHERE
A.SALESMAN_ID = B.SALESMAN_IDAND B.PURCHASE_AMT=(SELECT MAX
(PURCHASE_AMT) FROM ORDERS C
WHERE C.ORD_DATE = B.ORD_DATE);

Demonstrate the DELETE operation by removing salesman with id 1000. All his
orders must also be deleted.
Use ON DELETE CASCADE at the end of foreign key definitions while
creating child table orders and then execute the
following: DELETE FROM SALESMAN WHERE SALESMAN_ID=1000;

85
RESULT:
Thus the SQL Program used to databases are designed using ER modeling,
normalization constraints and the Operations of Bank Management System using
visual basic as front end and oracle as back end to design forms successfu

86
87 | P a g e