DBMS Manual

JAI SHRIRAM ENGINEERING COLLEGE
AVINASHIPALAYAM, TIRUPUR-638 660.
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING
Regulation: 2021
LAB MANUAL
CS3481 – DATABASE MANAGEMENT SYSTEMS LABORATORY
II CSE/IV SEM
Prepared By Approved By
(Ms.K.Saranya AP/CSE) (Prof A. Gokilavani HOD/ CSE)
VISION AND MISSION
VISION
To produce a proficient software and hardware professionals, researchers along
with entrepreneurs by imparting sound knowledge in Computer Science and
Engineering with technical expertise to meet the global challenges
MISSION
M1: To enhance the students with practical knowledge in design and develop
the innovative system to serve global demands and standards
M2: To create competent and high quality engineers by edify the state of the
art tools and skills which are necessary to adopt emerging technological
and societal changes
M3: To sensitize the graduates to become a responsible Computer Science
Engineers with ethical and human values
JAI SHRIRAM ENGINEERING COLLEGE
TIRUPPUR – 638 660
Approved by AICTE, New Delhi & Affiliated to Anna University, Chennai
Recognized by UGC & Accredited by NAAC and NBA (CSE and ECE)

SEMESTER: IV
CS3481 - DATABASE MANAGEMENT SYSTEMS LABORATORY
COURSE OUTCOMES (COs)
COs Knowledge Level Course Outcomes
C218.1 K6 Create databases with different types of key constraints.

K6 Construct simple and complex SQL queries using DML and DCL
C218.2
commands.
K3 Use advanced features such as stored procedures and triggers and
C218.3
incorporate in GUI based application development.
K6 Create an XML database and validate with meta-data (XML
C218.4
schema).
C218.5 K6 Create and manipulate data using NOSQL database.

LIST OF EXPERIMENTS MAPPING WITH COs, POs & PSOs
CO
Ex.No Name of the Experiment Mapped POs
CO1, 1,2,3,4,5,10,11,
Create a database table, add constraints (primary key, CO2 12
1 unique, check, Not null), insert rows, update and delete
rows using SQL DDL and DML commands.
CO1, 1,2,3,4,5,10,11,
Queries to demonstrate implementation of Integrity
2 CO2 12
Constraint
Query the database tables using different ‘where’ CO1, 1,2,3,4,5,10,11,

3 clause conditions and also implement aggregate CO2 12
functions.
CO1, 1,2,3,4,5,10,11,
Query the database tables and explore sub queries and
4 CO2 12
simple join operations
Query the database tables and explore natural, equi and CO1, 1,2,3,4,5,10,11,
5 CO2 12
outer joins.
Write user defined functions and stored procedures in SQL. CO3 1,2,3,4,5,10,11,
6 12
Execute complex transactions and realize DCL and TCL CO3 1,2,3,4,5,10,11,
7 commands. 12
Write SQL Triggers for insert, delete, and update CO3 1,2,3,4,5,10,11,
8 12
operations in a database table
Create View and index for database tables with a large CO3 1,2,3,4,5,10,11,
9 number of records. 12
Create an XML database and validate it using XML CO4 1,2,3,4,5,10,11,

10 schema. 12
Create Document, column and graph based data using CO5 1,2,3,4,5,10,11,
11 NOSQL database tools. 12
CO1, 1,2,3,4,5,10,11,
Develop a simple GUI based database application and CO2, 12
12 incorporate all the above-mentioned features CO3,
CO4,
CO5,
CO1, 1,2,3,4,5,10,11,
CO2, 12
Case Study using any of the real life database CO3,
13
applications CO4,
CO5,
CONTENT BEYOND THE SLLABUS

CO1, 1,2,3,4,5,10,11,
MISCELLANEOUS FUNCTIONS IN SQL CO2, 12
Ex.No, : 1 Create a database table, add constraints (primary key, unique, check,
Not null), insert rows, update and delete rows using SQL DDL and DML commands.
AIM:
To create a database and write SQL queries to retrieve information from the database.
DESCRIPTION:
Data Definition Language
DDL (Data Definition Language) statements are used to create, change the objects of a database.
Typically a database administrator is responsible for using DDL statements or production databases in
a large database system. The commands used are:
• Create - It is used to create a table.
• Alter - This command is used to add a new column, modify the existing column definition
and to include or drop integrity constraint.
• Drop - It will delete the table structure provided the table should be empty.
• Truncate - If there is no further use of records stored in a table and the structure has to be
retained, and then the records alone can be deleted.
• Desc - This is used to view the structure of the table.
PROCEDURE:
Step 1: Create table by using create table command with column name, data type and size.
Step 2: Display the table by using desc command.
Step 3: Add any new column with the existing table by alter table command.
Step 4: Modify the existing table with modify command.
Step 5: Delete the records in files by truncate command.

Step 6: Delete the Entire table by drop command
Create Table
Syntax:
Create table tablename

(
column_name1 datatype(size),
column_name2 datatype(size),
column_name3 datatype(size),………);
Example:
SQL> Create table Student(Stud_name char(20), Stud_id varchar2(10), Stud_dept varchar2(20),

Stud_age number(5));
Table created.
SQL> desc Student;
Name Null? Type

STUD_NAME CHAR(20)
STUD_ID VARCHAR(10)
STUD_DEPT VARCHAR(20)
STUD_AGE NUMBER(5)
Alter Table
Syntax:
Alter table tablename add (column_name datatype(size));

Alter table tablename modify (column_name datatype(size));
Alter table tablename drop (column_name);
Example:
SQL> Alter table Student add (Stud_addr varchar2 (20));

Table altered.
SQL> desc Student;

Name Null? Type
STUD _ NAME CHAR(20)

STUD_ID VARCHAR(10)
STUD_AGE VARCHAR(10)
STUD_ADDR VARCHAR(20)
SQL> Alter table Student modify (Stud_age number(10));

Table altered.
SQL> desc Student;

Name Null? Type
STUD _ NAME CHAR(20)

STUD_ID VARCHAR(10)
STUD_AGE NUMBER(10)
SQL> Alter table Student drop (Stud_age number(10));
Table altered.
SQL> desc Student;
Name Null? Type

STUD_NAME CHAR(20)
STUD_ID VARCHAR(10)
Truncate Table
Syntax:
Truncate table table_name;
Example:
SQL> Truncate table Student;
Table truncated.
SQL> desc Student
Name Null? Type

STUD_NAME CHAR(20)
STUD_ID VARCHAR(10)
STUD_AGE NUMBER(10)
Rename
Syntax
Alter table table_name rename new_table_name
SQL> alter table student rename student1;
SQL> desc student1;
Name Null? Type

STUD_NAME CHAR(20)
STUD_ID VARCHAR(10)
STUD_AGE NUMBER(10)
Drop Table
Syntax:
Drop table tablename;

Example:
SQL> Drop table Student1;
Table dropped.
SQL> desc Student1;

ERROR: ORA-04043: object Student1 does not exist
DML COMMANDS
AIM:
To Study and Practice Insertion, Deletion, Modifying, Altering, Updating and Viewing records based on
conditions in RDBMS.
DESCRIPTION:
Data Manipulation Language
DML 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
PROCEDURE:
Step 1: Create table by using create table command.

Step 2: Insert values into the table
Step 3: Delete any records from the table
Step 4: Update any values in the table.
Step 5: Display the values from the table by using select command.
SQL> Create table Student(Stud_name char(20), Stud_id varchar2(10), Stud_ dept varchar2(20),
Stud_age number(5));
Table created.
SQL> desc Student;
Name Null? Type

STUD_NAME CHAR(20)
STUD_ID VARCHAR(10)
STUD_AGE NUMBER(5)
Insert:
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.
Syntax:
insert into tablename values(
‘&column_name1’,
‘&column_name2’, ‘
‘&column_name3’,…..);
Example:
SQL> Insert into Student1 values(‘&stud_name’, ‘&stud_id’, ‘&stud_dept’, ‘&stud_rollno’);
Insert into Student1 values (‘Ram’, ‘101’, ‘MECH’, ‘104’)

1 row created.
Insert into Student1 values (‘Vicky’, ‘102’, ‘EEE’, ‘105’)
1 row created.
Insert into Student1 values (‘Saddiq’, ‘102’, ‘CSE’, ‘101’)
1 row created.
Insert into Student1 values (‘David’, ‘104’, ‘EEE’, ‘103’)
1 row created.
Select Command:
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.
Syntax:
Select * from table_name;
Example:
SQL> select * from Student1;
STUD_NAME STUD_ID STUD_DEPT STUD_ROLLNO
Ram 101 MECH 104
Vicky 102 EEE 105
Saddiq 103 CSE 101
David 104 EEE 103
4 rows selected
Update 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.
Syntax:
Update table_name set column_name=’value’ where condition;

Example:
SQL> Update Student1 set stud_id=’109’ where stud_name=’Saddiq’;

Ram 101 MECH 104
Vicky 102 EEE 105
Saddiq 103 CSE 101
David 104 EEE 103
1 row updated.
Delete 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. Syntax:
Delete from table_name where condition;
Example:
SQL> Delete from Student1 where stud_dept=’CSE’;

1 row deleted.

Ram 101 MECH 104
Vicky 102 EEE 105
David 104 EEE 103
Ex. No.: 02 Queries to demonstrate implementation of Integrity Constraint
Aim:
To study and practice to create table, add foreign key constraints and incorporate referential
integrity.
Description:
A referential integrity constraint is also known as foreign key constraint. A foreign key is a key whose
values are derived from the Primary key of another table.
The table from which the values are derived is known as Master or Referenced Table and the Table in
which values are inserted accordingly is known as Child or Referencing Table,
In other words, we can say that the table containing the foreign key is called the child table, and the
table containing the Primary key/candidate key is called the referenced or parent table.
Procedure:
Step 1: Create the master or referenced table with required fields.
Step 2: Create the child table.
Step 3: Create the primary key in master table.
Step 4: Apply the insert and delete constrains.
CONSTRAINTS:
1) Primary key
2) Foreign key/references
3) Check
4) Unique
5) Not null
6) Null
7) Default
CONSTRAINTS CAN BE CREATED IN THREE WAYS:

1) Column level constraints
2) Table level constraints
3) Using DDL statements-alter table command
OPERATION ON CONSTRAINT:
i) ENABLE
ii) DISABLE
iii) DROP
NOT NULL:
Syntax:
Create table tablename(
fieldname1 datatype(constraint)not null,
fieldname2 datatype,
…………….
fieldnamen datatype);
Example:
SQL> create table notnull (eno varchar(10) not null, ename varchar(10),esalary number(20));
Table created
SQL>insert into notnull values(‘1’,’abdul’,’20000’)
1 row created.
SQL>insert into notnull values(‘’,’raj’,’30000’)

*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“SCOTT”.”NOTNULL”.”ENO”)
CHECK:
Check constraint specify conditions that each tuple must satisfy.
Syntax:
Fieldname1 datatype(constraint),
Fieldname2 datatype,
………………….
Fieldname3 datatype);
Example:
SQL> create table con ( empid decimal(10) not null, empname varchar(20),empsalary decimal(10),
check(empsalary>10000));
SQL>insert into con values (‘1’,’kumar’,’20000’)
1 row created
SQL>insert into con values(‘2’,’raja’,’9000’)

*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0010283) violated
UNIQUE:
Used to set unique constraint to the specified column name which will not allow redundant
values
Syntax:
fieldname1 datatype(constraint)unique,
…………….
Example:
SQL> create table conn(eno varchar(10) unique, ename varchar(20));
Table created.
SQL> insert into conn values(‘1’,’hello’)
1 row created.
SQL>insert into conn values(‘1’,’hi’)

*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0010285) violated
PRIMARY KEY:
Primary key is a constraint for both unique and not null.
Syntax:
Fieldname1 datatype(constraint)primary key,
…………….
Example:
SQL> create table con(empid varchar(10),empname varchar(20) primary key);
Table created.
ADDING CONSTRIANT:
Used to set any constraint to the specified column at the last by specifying the constraint type and
field name.
Syntax:
Fieldname1 datatype(constraint),
constraint constraintname constrainttype(fieldname));
Example:
SQL> create table con(empid varchar(10),empname varchar(10),constraint c1 primary key(empid));
Table created.
SQL> insert into con values (‘1’,’anand’)
SQL>insert into con values (‘1’,’vijay’)

*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.C1) violated
ADD CONSTRAINT (ALTER)
Used to set the constraint for the table already created by using alter command.
Syntax:
Alter table tablename add constraint constraintname (fieldname)datatype,primary key.
Example:
SQL> create table con(empid varchar(10),empname varchar(10));
Table created.
SQL> alter table con add constraint c1 primary key (empid);
Table altered.
SQL> desc con;
Name Null? Type
NOT NULL
EMPID VARCHAR(10)
EMPNAME VARCHAR(10)
DROP CONSTRAINT:
Used to drop the constraint.
Syntax:
Alter table table_name drop constraint constraint_name.
Example:
SQL> alter table con drop constraint c1;
Table altered.
SQL> desc con;

Name Null? Type
EMPID VARCHAR(10)
EMPNAME VARCHAR(10)
REFERENTIAL INTEGRITY:
Used to refer the primary key of the parent table from the child table.
Syntax:
a) Create table tablename(
Fieldname1 datatype primary key,
…………….
b) Create table tablename(Fieldname1 datatype references,
Parent tablename(fieldname)
…………….
Fieldname n datatype);
Example:
SQL> create table parent(eno varchar(10),ename varchar(10) primary key);
Table created.
SQL>insert into parent values (‘1’,’ajay’)
1 row created.
SQL>insert into parent values (‘2’,’bala’)
1 row created.
SQL> create table child (eno varchar(10),ename varchar(10) references parent(ename));
Table created.
SQL>insert into child values (‘1’,’ajay’)

1 row created.
SQL>insert into child values (‘2’,’balaji’)

ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C0010290) violated - parent key not
Found
ON DELETE CASCADE:
The changes done in parent table is reflected in the child table when references are made.
Syntax:

Fieldname1 datatype references,
Parent tablename(fieldname),
On delete cascade);
Example:
SQL> create table parent(eno varchar(10),ename varchar(10) primary key);
Table created.
SQL>insert into parent values (‘1’,’a’)

1 row created.
SQL> create table child(eno varchar(10),ename varchar(10) references parent(ename) on delete

cascade);
Table created.
SQL> insert into child values (‘2’,’a’)

1 row created.
SQL> select * from parent;
ENO ENAME
1 a
SQL> select * from child;

ENO ENAME
2 a
SQL> delete from parent where eno=1;
1 row deleted.
SQL> select * from parent;
no rows selected
SQL> select * from child;
no rows selected
Ex.No. : 03 Query the database tables using different ‘where’ clause conditions and also
implement aggregate functions.
Aim:
To study and execute various database queries using where clause and aggregate functions.
Description:
The WHERE clause is used to filter records. It is used to extract only those records that fulfill a
specified condition.
Where clause’s
Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal.( Note: In some versions of SQL this operator may be written as !=)
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column
The WHERE clause is not only used in SELECT statements, it is also used in UPDATE, DELETE, etc.!
Equal
SQL> select * from table_name where field=condition
Example
SQL> select * from student1 where stud_rollno=101;

Saddiq 103 CSE 101
Greater Than
SQL> select * from student1 where stud_rollno >101;

Ram 101 MECH 104
Vicky 102 EEE 105
David 104 EEE 103
Less Than
SQL> select * from student1 where stud_rollno <105;
Ram 101 MECH 104
Saddiq 103 CSE 101
David 104 EEE 103
Between
SQL> select * from student1 where stud_rollno between 103 AND 105;
Ram 101 MECH 104
Vicky 102 EEE 105
David 104 EEE 103
Like
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
LIKE Operator Description

WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least 2
characters in length
WHERE CustomerName LIKE 'a %' Finds any values that start with "a" and are at least 3
characters in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"
Example
SQL > Select * from student1 where stud_name like ‘d%’;
David 104 EEE 103
Other Where Clauses

Union:
The UNION operator is used to combine the result-set of two or ore SELECT statements.
• Every SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in every SELECT statement must also be in the same order
Syntax:
select column_name(s) from table1
union
select column_name(s) from table2;
SQL> select subject from student union select subject from staff order by subject;
Union with where

Syntax
Select <fieldlist> from <tablename1> where (condition)

union
select<fieldlist> from<tablename2> where (condition);
Example
SQL> SELECT dept, subject FROM student
WHERE subject='DBMS'
UNION
SELECT dept, subject FROM staff
WHERE subject='DBMS'
ORDER BY City;
Union All
select column_name(s) from table1
union all
select column_name(s) from table2;
Example
select subject from student
union all
select subject from staff
order by subject;
Intersect:
Syntax:
Select <fieldlist> from <tablename1> where (condition) intersect select<fieldlist> from<tablename2>
where (condition);
Example
SQL> select stud_id from student intersect select staff_id from staff;
In:
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
Syntax:
Select <fieldlist> from <tablename1> where (condition) in select<fieldlist> from<tablename2> where
(condition);
Example
SQL>select * from student1 where stud_dept in ('cse', 'mech');

Ram 101 MECH 104
Saddiq 103 CSE 101
SQL>select * from customers where stud_dept not in ('cse', 'mech', 'it');

Vicky 102 EEE 105
David 104 EEE 103
Not like:
Syntax:
Select <fieldlist> from <tablename> where <fieldname> not like <expression>;
All:
The ALL operator:
• returns a boolean value as a result

• returns TRUE if ALL of the subquery values meet the condition
• is used with SELECT, WHERE and HAVING statements
ALL means that the condition will be true only if the operation is true for all values in the range.
Syntax:
Select <fieldlist> from <tablename1>where <fieldname> all Select <fieldlist> from <tablename2>
where (condition);
Example
SQL> select stud_name from student where stud_id = all (select subject_id from subject where sem
= 4);
Any:
The ANY operator:
• returns a Boolean value as a result
• returns TRUE if ANY of the sub query values meet the condition
ANY means that the condition will be true if the operation is true for any of the values in the range.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name
FROM table_name
WHERE condition);
The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).
Example
SQL> select stud_name from student where stud_id = any (select subject_id from subject where sem
= 4);
The above SQL statement lists the student name if it finds ANY records in the subject table has sem
equal to 4 (this will return TRUE if the sem column having value 4)
Aggregate Functions
MySQL's aggregate function is used to perform calculations on multiple values and return the result in a
single value like the average of all values, the sum of all values, and maximum & minimum value
among certain groups of values. We mostly use the aggregate functions with SELECT statements in the
data query languages.
Aggregate Function Descriptions

count() It returns the number of rows, including rows with NULL values in a group.
sum() It returns the total summed values (Non-NULL) in a set.
average() It returns the average value of an expression.
min() It returns the minimum (lowest) value in a set.
max() It returns the maximum (highest) value in a set.
groutp_concat() It returns a concatenated string.
first() It returns the first value of an expression.
last() It returns the last value of an expression.
Sql > create table student(rollno decimal, sname varchar(15), mark1 decimal, mark2 decimal);
Table created
Sql> insert into student values(101, ‘kareem’,95,90);
Sql> insert into student values(102, ‘kaasim’,92,97);
Sql> insert into student values(103, ‘ram’,85,95);
Sql> insert into student values(104, ‘sai’,93,91);
Sql> select * from student;

Rollno sname mark1 mark2
101 kareem 95 90
102 kaasim 92 97
103 ram 85 95
104 sai 93 91
Count
The COUNT () function returns the number of rows that matches a specified criterion.
Syntax
SELECT COUNT (column_name) FROM table_name WHERE condition;
Example
SELECT COUNT (rollno) FROM student;
4
Sum
The SUM () function returns the total sum of a numeric column.
Syntax
SELECT SUM(column_name) FROM table_name WHERE condition;
Example
SELECT SUM(mark1) FROM student;
365
SELECT SUM(mark1) FROM student WHERE mark2>=95;

192
Average
The AVG() function returns the average value of a numeric column.
Syntax
SELECT AVG(column_name) FROM table_name WHERE condition;
Example
SELECT AVG(mark1) FROM student;
91
SELECT AVG(mark2) FROM student WHERE mark2>=95;

96
Min
The MIN() function returns the smallest value of the selected column.
SELECT MIN(column_name) FROM table_name WHERE condition;

Example
SELECT MIN(mark1) FROM student;
85
Max
The MAX() function returns the largest value of the selected column.
SELECT MAX(column_name) FROM table_name WHERE condition;

Example
SELECT MAX(mark1) FROM student;
95
Ex.no. : 04 Query the database tables and explore sub queries and simple join
Aim : operations Sub Quires and Join Operations
To implement and execute simple, nested, sub & join operation queries in mysql database.
Simple Queries
The SQL SELECT DISTINCT Statement

The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a
column often contains many duplicate values; and sometimes you only want to list the different
(distinct) values.
Syntax
SELECT DISTINCT column1, column2, ...FROM table_name;
Example
SELECT DISTINCT STU_DEPT FROM student1;

STU_DEPT
CSE
EEE
MECH
The SQL AND, OR and NOT Operators

The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition:
⚫ The AND operator displays a record if all the conditions separated by AND are TRUE.
⚫ The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE
AND Syntax
FROM table_name
WHERE condition1 AND condition2 AND condition3 ..
Example
SELECT * FROM student1 WHERE stud_id=101 AND stud_dept=’mech’;
Ram 101 MECH 104
OR Syntax
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
Example
SELECT * FROM student1 WHERE stud_id=101 OR stud_dept=’EEE’;

Ram 101 MECH 104
NOT Syntax
FROM table_name
WHERE NOT condition;
Example
SELECT * FROM student WHERE NOT stud_id=101;

Vicky 102 EEE 105
David 104 EEE 103
The SQL ORDER BY Keyword

The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.
ORDER BY Syntax
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
Example
SELECT * FROM student1 ORDER BY STUD_ID ;

Ram 101 MECH 104
Vicky 102 EEE 105
Saddiq 103 CSE 101
David 104 EEE 103
SELECT * FROM student1 ORDER BY STUD_ID DESC;

David 104 EEE 103
Saddiq 103 CSE 101
Vicky 102 EEE 105
Ram 101 MECH 104
Subqueries
A MySQL sub query is a query nested within another query such as SELECT, INSERT, UPDATE or
DELETE. In addition, a MySQL sub query can be nested inside another sub query.
A MySQL sub query is called an inner query while the query that contains the sub query is called an
outer query. A sub query can be used anywhere that expression is used and must be closed in
parentheses.
Example SubQueries
1. SELECT lastName, firstName FROM employees WHERE officeCode IN (SELECT officeCode

FROM offices WHERE country = 'USA');
In this example:
⚫ The sub query returns all office codes of the offices located in the USA.
⚫ The outer query selects the last name and first name of employees who work in the offices whose
office codes are in the result set returned by the sub query.
2. Select max(sid) from classa where sid <( select max(sid) from classa)
SQL Joins
Here are the different types of the Joins in SQL:
(INNER) JOIN : Returns records that have matching values in both tables
LEFT (OUTER) JOIN : Return all records from the left table, and the matched records from the
right table
RIGHT (OUTER) JOIN : Return all records from the right table, and the matched records from the
left table
FULL (OUTER) JOIN : Return all records when there is a match in either left or right table
Note : To perform join operation we need two different tables.
Sql> select * from student;

Rollno sname mark1 mark2
101 kareem 95 90
102 kaasim 92 97
103 ram 85 95
104 sai 93 91
Sql > select * from sports

Rollno sname sdept game
101 kareem CSE cricket
104 sai ECE football
105 ravi IT cricket
107 fizal CSE chess
Inner Join
The INNER JOIN keyword selects records that have matching values in both tables.
Syntax
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Example
SELECT student.name, student.mark1, sports.game FROM student INNER JOIN sports ON
student.rollno=sports.rollno;
sname mark1 game

kareem 95 cricket
sai 93 football
Left Join
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the
right table (table2). The result is NULL from the right side, if there is no match.
Syntax
FROM table1
LEFT JOIN table2
Example
SELECT student.name, student.mark1, sports.game FROM student LEFT JOIN sports ON
sname mark1 game

kareem 95 cricket
kaasim 92 Null
ram 85 Null
sai 93 football
RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the
left table (table1). The result is NULL from the left side, when there is no match.
Syntax
FROM table1
RIGHT JOIN table2
Example
SELECT student.name, student.mark1, sports.game FROM student RIGHT JOIN sports ON
sname mark1 game

kareem 95 cricket
sai 93 football
Null Null cricket
Null Null chess
FULL OUTER JOIN Keyword
The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right
(table2) table records.
Note: FULL OUTER JOIN or FULL JOIN is not directly performed in sql so we can achive it by union
operation of left join and right join.
Syntax
FROM table1
FULL OUTER JOIN table2
If the above syntax is not working then we can go with union operation.
FROM table1
LEFT JOIN table2
Union
FROM table1
RIGHT JOIN table2
Example
SELECT student.name, student.mark1, sports.game FROM student LEFT JOIN sports ON
student.rollno=sports.rollno
Union
SELECT student.name, student.mark1, sports.game FROM student RIGHT JOIN sports ON
student.rollno=sports.rollno
Ex.No. 5 Query the database tables and explore natural, equi and outer joins.
Aim:
To study and execute SQL natural join, equi join and outer joins.
Query the database tables and explore natural, equi and outer joins.
Procedure:
Natural join is an SQL join operation that creates join on the base of the common columns in the
tables. To perform natural join there must be one common attribute(Column) between two tables.
Natural join will retrieve from multiple relations.
It works in three steps.
⚫ Natural join is an SQL join operation that creates join on the base of the common columns in the
tables.
⚫ To perform natural join there must be one common attribute(Column) between two tables.
⚫ Natural join will retrieve from multiple relations. It works in three steps.
Tables
Student
Roll sname dept
101 aaa cse
105 eee it
102 bbb ece
103 ccc eee
104 ddd cse
105 eee it
Game
Gid gname roll
1 cricket 101
2 volley ball 102
3 cricket 104
4 carom 106
5 chess 107
Natural Join Syntax

SELECT *
FROM table1
NATURAL JOIN table2;
Example
Select * from student NATURAL JOIN game;
Roll sname dept gid game

101 aaa cse 1 cricket
102 bbb ece 2 volly ball
104 ddd cse 3 cricket
Equi join:
⚫ EQUI JOIN creates a JOIN for equality or matching column(s) values of the relative tables.
⚫ EQUI JOIN also create JOIN by using JOIN with ON and then providing the names of the columns
with their relative tables to check equality using equal sign (=).
Syntax
SELECT column_list
FROM table1, table2....
WHERE table1.column_name =
table2.column_name;
Example
SELECT student.roll,student.sname,game.gname FROM student,game WHERE student.roll=game.roll;
Or
SELECT student.roll,student.sname,game.gname FROM student JOIN game ON student.roll=game.roll;
Roll sname game
101 aaa cricket
102 bbb volly ball
104 ddd cricket
Non Equi Join :
NON EQUI JOIN performs a JOIN using comparison operator other than equal(=) sign like >, <, >=, <=
with conditions.
Syntax
SELECT column_list
FROM table1, table2....
WHERE table1.column_name >
table2.column_name;
Example
SELECT student.roll,student.sname,game.gname FROM student,game WHERE student.roll>game.roll;
Roll dept game

105 eee cricket
105 eee volley ball
105 eee cricket
102 bbb cricket
103 ccc cricket
103 ccc volley ball
104 ddd cricket
104 ddd volley ball
105 eee cricket
105 eee volley ball
105 eee cricket
Full outer join
The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right
(table2) table records. Full outer join and full join are same
Syntax
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
Example
select student.roll,student.sname,game.gname from student left join game on student.roll=game.roll
union
select student.roll,student.sname,game.gname from student right join game on student.roll=game.roll;
Roll sname game

101 aaa cricket
105 eee Null
102 bbb volley ball
103 ccc Null
104 ddd cricket
Null Null carom
Null Null chess
Ex.No. :06
Write user defined functions and stored procedures in SQL.
Aim:
To Write a program using procedures and functions
MySQL Stored Function

A stored function is a special kind stored program that returns a single value. You use stored functions
to encapsulate common formulas or business rules that are reusable among SQL statements or stored
programs.
Different from a stored procedure, you can use a stored function in SQL statements wherever an
expression is used. This helps improve the readability and maintainability of the procedural code.
syntax
The following illustrates the simplest syntax for creating a new stored function:
CREATE FUNCTION function_name(parameter 1,parameter 2,…)
RETURNS datatype
[NOT] DETERMINISTIC
Statements
Example
Function to concatenate two strings

USE `sample1`;
DROP function IF EXISTS `funcon`;
DELIMITER $$
USE `sample1`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `funcon`(s CHAR(20)) RETURNS char(50)
CHARSET utf8mb4
DETERMINISTIC
BEGIN
RETURN CONCAT('Hello, ',s,'!!');
RETURN 1;
END$$
DELIMITER ;
Executing function
select funcon('world');
# funcon('world')
'Hello, world!!'
Stored procedure
MySQL stored procedure using CREATE PROCEDURE statement. In addition, we will show you
how to call stored procedures from SQL statements.
syntax
DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT * FROM products;
END //
DELIMITER ;
Example
create table cus(cid integer,cname char(20),address varchar(75),salary int,post varchar(20));
insert into cus values(1,'aa','77,anna salai,arcot',10000,'clerk');

insert into cus values(3,'bb','01,anna salai,chennai',15000,'staff');
insert into cus values(2,'cc','25,rajaji nagar,banglore',15000,'staff');
insert into cus values(4,'dd','02,mettu street,kochin',10000,'secretary');
insert into cus values(5,'ee','21,north street,mumbai',15000,'manager');
select* from cus;
Cid cname address salary post

1 aa 77,anna salai,arcot 10000 clerk
3 bb 01,anna salai,chennai 15000 staff
2 cc 25,rajaji nagar,banglore 15000 staff
4 dd 02,mettu street,kochin 10000 secretary
5 ee 21,north street,mumbai 15000 manager
Creating stored procedure
USE `sample1`;
DROP procedure IF EXISTS `new_pro`;
DELIMITER $$
USE `sample1`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_pro`()
BEGIN
UPDATE cus
SET salary = salary + 500;
END$$
DELIMITER ;
Executing stored procedure

call new_pro;
5 rows effected
SQL>select* from cus;
Cid cname address salary post

1 aa 77,anna salai,arcot 10500 clerk
3 bb 01,anna salai,chennai 15500 staff
2 cc 25,rajaji nagar,banglore 15500 staff
4 dd 02,mettu street,kochin 10500 secretary
5 ee 21,north street,mumbai 15500 manager
Ex.No. :07 Execute complex transactions and realize DCL and TCL commands.
Aim:
To study and execute various Data Control Language and Transaction Control Language
commands in SQL.
Procedure:
1: Start
2: Create the table with its essential attributes.
3: Insert the record into table
4: Execute the DCL commands GRANT and REVOKE
5: Execute the TCL commands COMMIT, SAVEPOINT and ROLLBACK.
6: Stop
DCL Commands.
DCL includes commands such as GRANT and REVOKE which mainly deal with the rights,
permissions, and other controls of the database system.
GRANT:
This command gives users access privileges to the database. For this first we have to create user.
CREATE USER Statement

MySQL allows us to specify which user account can connect to a database server. The user account
details in MySQL contains two information – username and host from which the user is trying to
connect in the format username@host-name.
If the admin user is connecting through localhost then the user account will be admin@localhost.
MySQL stores the user account in the user grant table of the mysql database.
The CREATE USER statement in MySQL allows us to create new MySQL accounts or in other words,
the CREATE USER statement is used to create a database account that allows the user to log into the
MySQL database.
Syntax;
CREATE USER user_account IDENTIFIED BY password;
NOTE: in our system user is root@localhost

We have already learned about how to create users in MySQL using MySQL | create user statement. But
using the Create User Statement only creates a new user but does not grant any privileges to the user
account. Therefore to grant privileges to a user account, the GRANT statement is used.
Syntax:
GRANT privileges_names ON object TO user;
Parameters Used in Grant Command
privileges_name: These are the access rights or privileges granted to the user.
object:It is the name of the database object to which permissions are being granted. In the case of
granting privileges on a table, this would be the table name.
user:It is the name of the user to whom the privileges would be granted.
Various privileges used are, SELECT, INSERT, DELETE, INDEX, UPDATE, CREATE, ALTER,
DROP, GRANT.
1. Granting SELECT Privilege to a User in a Table:
To grant Select Privilege to a table named “users” where User Name is root, the following GRANT
statement should be executed.
GRANT SELECT ON Users TO 'root'@'localhost;
2. Granting more than one Privilege to a User in a Table:

To grant multiple Privileges to a user named “root” in a table “users”, the following GRANT statement
should be executed.
GRANT SELECT, INSERT, DELETE, UPDATE ON Users TO 'root'@'localhost;
3. Granting All the Privilege to a User in a Table:

To Grant all the privileges to a user named “root” in a table “users”, the following Grant statement
should be executed.
GRANT ALL ON Users TO 'root'@'localhost;
4. SQL Grant command is specifically used to provide privileges to database objects for a user. This
command also allows users to grant permissions to other users too.
Syntax:
grant privilege_name on object_name
to {user_name | public | role_name}
Example
grant insert,
select on accounts to Ram
REVOKE:
This command withdraws the user’s access privileges given by using the GRANT command.
Revoke command withdraw user privileges on database objects if any granted. It does operations
opposite to the Grant command. When a privilege is revoked from a particular user U, then the
privileges granted to all other users by user U will be revoked.
Syntax:
REVOKE privilege_name on object_name
from {user_name | public | role_name}
Example
REVOKE insert,
select on accounts from Ram
TCL (Transaction Control Language)

Transaction Control Language(TCL) commands are used to manage transactions in database.
These are used to manage the changes made by DML statements.
It also allows statements to be grouped together into logical transactions.
TCL Commands are
⚫ Commit
Commit command is used to permanently save any transaction into database.Following is Commit
command's syntax,
commit;
⚫ Rollback
This command restores the database to last commited state. It is also use with savepoint command to
jump to a savepoint in a transaction. Following is Rollback command's syntax,
rollback to savepoint-name;
⚫ Savepoint
savepoint command is used to temporarily save a transaction so that you can rollback to that point
whenever necessary.
Following is savepoint command's syntax,
savepoint savepoint-name;
Example
Create the table class with the ID & NAME attributes Apply TCL commands and show the result.
SQL> CREATE TABLE CLASS(id int,sname varchar(20));
SQL> insert into class values(1,'john');

SQL> insert into class values(2,'raj');
SQL> insert into class values(3,'rahman');
SQL>Select * from class

Id sname
1 john
2 raj
3 rahman
Let’s use some SQL queries on the above table and see the results.
SQL> INSERT into class values(5,'Rahul');

commit;
SQL> UPDATE class set name='abhijit' where id='5';
savepoint A;
SQL> INSERT into class values(6,'Chris');
savepoint B;
SQL> INSERT into class values(7,'Bravo');
savepoint C;
SQL> SELECT * from class;
The resultant table will look like,

Id sname
1 john
2 raj
3 rahman
5 abhijit
6 Chris
7 Bravo
Now rollback to savepoint B

rollback to B;
SELECT * from class;
The resultant table will look like
Id sname
1 john
2 raj
3 rahman
5 abhijit
6 Chris
Now rollback to savepoint A

rollback to A;
SELECT * from class;
The result table will look like
Id sname
1 john
2 raj
3 rahman
5 abhijit
Ex.No. 8 Write SQL Triggers for insert, delete, and update operations in a database table
Aim:
To execute programs for insert, delete, and update operations in a database table using triggers.
MySQL trigger
A MySQL trigger is a stored program (with queries) which is executed automatically to respond to a specific
event such as insertion, updation or deletion occurring in a table.
In order to create a new trigger, you use the CREATE TRIGGER statement. The following illustrates the
syntax of the CREATE TRIGGER statement:
CREATE TRIGGER trigger_name trigger_time trigger_event

ON table_name
FOR EACH ROW
BEGIN
...
END;
Let’s examine the syntax above in more detail.
⚫ You put the trigger name after the CREATE TRIGGER statement. The trigger name should
follow the naming convention [trigger time]_[table name]_[trigger event], for example
before_employees_update.
⚫ Trigger activation time can be BEFORE or AFTER. You must specify the activation time when you
define a trigger. You use the BEFORE keyword if you want to process action prior to the change is
made on the table and AFTER if you need to process action after the change is made.
⚫ The trigger event can be INSERT, UPDATE or DELETE. This event causes the trigger to be invoked. A
trigger only can be invoked by one event. To define a trigger that is invoked by multiple events, you
have to define multiple triggers, one for each event.
⚫ A trigger must be associated with a specific table. Without a table trigger would not exist therefore you
have to specify the table name after the ON keyword.
⚫ You place the SQL statements between BEGIN and END block. This is where you define the logic for
the trigger
Example
SQL> Create table account1(acct_num int,amount int)
SQL> insert into account1 values(1,150)
SQL> select * from account1
Acc_num amount
1 150
Trigger for update
DELIMITER $$
USE `sample1`$$
CREATE DEFINER = CURRENT_USER TRIGGER `sample1`.`new_table_BEFORE_UPDATE`
BEFORE UPDATE ON àccount1` FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END$$
DELIMITER ;
SQL> update account1 set amount=2000 where acct_num=1

Acc_num amount
1 100
Trigger for insert
DELIMITER $$
USE `sample1`$$
CREATE DEFINER = CURRENT_USER TRIGGER `sample1`.`bank_BEFORE_INSERT` BEFORE
INSERT ON àccount1` FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END
$$
DELIMITER ;
SQL> insert into account1 values(2,-100)

Acc_num amount
1 100
2 0
Trigger for Delete
DELIMITER $$
USE `sample1`$$
CREATE DEFINER = CURRENT_USER TRIGGER `sample1`.`bank_BEFORE_DELETE` BEFORE
DELETE ON àccount1` FOR EACH ROW
BEGIN
delete from account2 where acct_num=2;
END
$$
DELIMITER ;
Acc_num amount
1 100
2 0

Ex.No.9: Create View and index for database tables with a large number of records.
AIM
To execute and verify the SQL commands for Views and Index.
PROCEDURE
STEP 1: Start
STEP 2: Create the table with its essential attributes.
STEP 3: Insert attribute values into the table.
STEP 4: Create the view from the above created table.
STEP 5: Execute different Commands and extract information from the View.
STEP 6: Stop
VIEWS:
SQL includes the ability to create stored queries that they can then be used as a basis for other queries.
These stored queries are also called views. A view is simply a derived table that is built upon the base
tables of the database. Base tables are the original database tables that actually contain data. Views do
not actually store data they are temporary tables. To define a view, we must give the view a name and
state the query that computes the view.
Syntax:
Create view v as <query expression>

Where query expression is any legal query expression and view name is represented by v.
SQL> select * from classa;
SID SNAME SDEPT TOTAL
1 aarthi IT 450
2 ezhil ECE 590

3 sakthi IT 900
4 vino ECE 600
7 viji IT 900
6 sumathi ECE 890
6 rows selected.
SQL> select * from classb;
ID NAME GRADE
1 aarthi b
2 ezhil b
6 sumathi a
7 viji a
CREATING A VIEW:
The first step in creating a view is to define the defining query, which is the query on
which the view is based. While it is not required that the defining query be written before
creating a view, it is generally a good idea. Any errors in the query can be caught and
corrected before the view is created.
Query for defining query:
SQL> select classa.sid, classa.sname, classa.sdept, classb.name1 from classa, classb where
classa.sid=classb.id order by classa.sid;
SID SNAME SDEPT Name1
SQL> create view classa_b as select classa.sid, classa.sname, classa.sdept, classb.grade

from classa, classb where classa.sid=classb.id order by classa.sid;
View created.
SQL> select * from classa_b;

SID SNAME SDEPT GRADE
1 aarthi IT B
2 ezhil ECE B
6 sumathi ECE A
7 viji IT A
SID SNAME SDEPT
1 aarthi IT
7 viji IT
RENAMING COLUMNS IN A VIEW:
Another useful feature available when creating a view is that columns can be renamed
in the CREATE VIEW statement. The new column names only apply to the views, the column
names in the base tables do not change.
Query:
SQL> create view classxy12(id,dept) as select sid,sdept from classa;

View created.
SQL> select * from classxy12;
ID DEPT
1 IT
2 ECE
3 IT
4 ECE
5 IT
6 ECE
6 rows selected.
USING AGGREGATION FUNCTIONS WITH VIEWS:

Aggregate function that take a collection of value as input and return a single value
.SQL offers five built in aggregate function such as count, sum ,average, minimum and
maximum. Count function will count all the rows including duplicates or null. Sum function
calculates the sum of all values in specified column. The average function produces arithmetic
mean of all the selected values or fields. The maximum function produces max values of all
the selected values of a given fields. The minimum function produces min values of all the
selected values of a given fields.
Query:
SQL> select * from classa
# sid, sname, sdept, total
'1', 'john', 'it', '100'
'2', 'raj', 'it', '200'
'3', 'rahman', 'it', '300'
'4', 'joseph', 'it', '400'
'5', 'ram', 'it', '600'
SQL> create view student_count_min(sno,count) as select min(total),count(total)from
classa;
View created.
SQL> select *from student_count_min;
# sno, count
'100', '5'
SQL> create view student_sum_avg(tot,avgtotal)as select sum(total),avg(total) from classa;
View created.
SQL> select * from student_sum_avg;
# tot, avgtotal
'1600', '320.0000'
SQL> create view stud_max(tot) as select max(total) from classa;
View created.
SQL> select * from stud_max;
# tot
'600'
CREATE INDEX
The CREATE INDEX command is used to create indexes in tables (allows duplicate
values).
Indexes are used to retrieve data from the database very fast. The users cannot see
the indexes, they are just used to speed up searches/queries.
CREATE INDEX dup_name ON classa(sname);

If you want to create an index on a combination of columns, you can list the column
names within the parentheses, separated by commas:
CREATE INDEX dup_name ON classa(sname,sdept);
DROP INDEX
DROP INDEX index_name;
DROP INDEX dup_name;
EX. NO: 10 Create an XML database and validate it using XML schema
Aim
Creating XML database and validate it using XML schema
Procedure
XML
o Xml (eXtensible Markup Language) is a mark up language.
o XML is designed to store and transport data.
o Xml was released in late 90’s. it was created to provide an easy to use and store self
describing data.
o XML became a W3C Recommendation on February 10, 1998.
o XML is not a replacement for HTML.
o XML is designed to be self-descriptive.
o XML is designed to carry data, not to display data.
o XML tags are not predefined. You must define your own tags.
o XML is platform independent and language independent.
XML Schema
XML Schema is commonly known as XML Schema Definition (XSD). It is used to describe
and validate the structure and the content of XML data. XML schema defines the elements,
attributes and data types. Schema element supports Namespaces. It is similar to a database
schema that describes the data in a database.
How to validate XML against XSD in java:
Java XML Validation API can be used to validate XML against an XSD. javax.xml.validation.Validator
class is used in this program to validate xml file against xsd file.Here are the sample XSD and XML files
used.
Employee.xsd
<?xml version="1.0" encoding="UTF-8"?>

<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.journaldev.com/Employee"
xmlns:empns="http://www.journaldev.com/Employee" elementFormDefault="qualified">
<element name="empRequest" type="empns:empRequest"></element>
<element name="empResponse" type="empns:empResponse"></element>
<complexType name="empRequest">
<sequence>
<element name="id" type="int"></element>
</sequence>
</complexType>
<complexType name="empResponse">
<sequence>
<element name="id" type="int"></element>
<element name="role" type="string"></elemen>
<element name="fullName" type="string"></element>
</sequence>
</complexType>
</schema>
Notice that above XSD contains two root element and namespace also, I have created two sample XML
file from XSD.
EmployeeRequest.xml
<empns:empRequest xmlns:empns="http://www.journaldev.com/Employee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.journaldev.com/Employee Employee.xsd ">
EmployeeResponse.xml
<empns:empResponse xmlns:empns="http://www.journaldev.com/Employee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.journaldev.com/Employee Employee.xsd ">
<empns:id>1</empns:id>
<empns:role>Developer</empns:role>
<empns:fullName>Pankaj Kumar</empns:fullName>
</empns:empResponse>
Here is another XML file that doesn’t confirms to the Employee.xsd.
employee.xml
<?xml version="1.0"?>
<Employee>
<name>Pankaj</name>
<age>29</age>
<role>Java Developer</role>
<gender>Male</gender>
</Employee>
Here is the program that is used to validate all three XML files against the XSD. The
validateXMLSchema method takes XSD and XML file as argument and return true if validation is
successful or else returns false.
XMLValidation.java
package com.journaldev.xml;
import java.io.File;
import java.io.IOException;
import javax.xml.XMLConstants;
import javax.xml.transform.stream.StreamSource;
import javax.xml.validation.Schema;
import javax.xml.validation.SchemaFactory;
import javax.xml.validation.Validator;
import org.xml.sax.SAXException;
public class XMLValidation {
public static void main(String[] args) {
System.out.println("EmployeeRequest.xml validates against Employee.xsd?

"+validateXMLSchema("Employee.xsd", "EmployeeRequest.xml"));
System.out.println("EmployeeResponse.xml validates against Employee.xsd?

"+validateXMLSchema("Employee.xsd", "EmployeeResponse.xml"));
System.out.println("employee.xml validates against Employee.xsd?

"+validateXMLSchema("Employee.xsd", "employee.xml"));
public static boolean validateXMLSchema(String xsdPath, String xmlPath){
try {
SchemaFactory factory =
SchemaFactory.newInstance(XMLConstants.W3C_XML_SCHEMA_NS_URI);
Schema schema = factory.newSchema(new File(xsdPath));
Validator validator = schema.newValidator();
validator.validate(new StreamSource(new File(xmlPath)));
} catch (IOException | SAXException e) {
System.out.println("Exception: "+e.getMessage());
return false;
return true;
}
Output of the above program is:
EmployeeRequest.xml validates against Employee.xsd? true
EmployeeResponse.xml validates against Employee.xsd? true
Exception: cvc-elt.1: Cannot find the declaration of element 'Employee'.
employee.xml validates against Employee.xsd? false

Ex.No. 11 Create Document, column and graph based data using NOSQL database tools.
AIM:
To create a document-based NoSQL database using MongoDB, with columns and graphs,
and populate it with sample data.
NOSL:
NOSQL (Not Only SQL) is a type of database that does not use the traditional relational
model that has been used in traditional SQL databases. Instead, NoSQL databases use a non•
relational approach for data storage and retrieval. They are designed to handle large amounts of
unstructured, semi-structured, and structured data, including text, images, videos, and social
media data.
NoSQL databases are often used in big data and real-time web applications, where high
performance and scalability are essential. Some popular examples of NoSQL databases include
MongoDB, Cassandra, Couchbase, and Apache HBase.
VIEW DATABASE:
In MongoDB, a view is a virtual collection that presents the results of a pre-defined
aggregation pipeline as if it were a collection of documents. Views do not store data
themselves but rather provide a read-only representation of the data in one or more underlying
collections.
QUERY AND OUTPUT:
test> show dbs

admin 40.00 Ki8
config 60.00 KiB
local 72.00 KiB
test>
CREATING DATABASE:
In the mongo shell, you can create a database with the help of the following command. This
command actually switches you to the new database if the given name does not exist and if the
given name exists, then it will switch you to the existing database. Now at this stage, if you use
the show command to see the database list where you will find that your new database is not
present in that database list because, in MongoDB, the database is actually created when you
start entering data in that database.
)
SYNTAX:
use database_name
QUERY AND OUTPUT:
test> show dbs

admin 40.00 Ki8
config 60.00 KiB
local 72.00 KiB
test> use CSE
switched to db CSE
CSE> show dbs
admin 56.00 KiB
config 72.00 KiB
Local 72.00 KiB
CSE>
CREATING COLLECTION:
In MongoDB, a collection is a grouping of MongoDB documents, similar to a table in a
relational database. To create a collection in MongoDB, you can use the db.createCollection()
method.
SYNTAX:
db .createCollection(name, options)
QUERY AND OUTPUT:
mongosh> use CSE

switched to db CSE
CSE> db.createCollection(
{ ok: 1 }
CSE> I
INSERTMANY COMMAND:
insertMany is a method in MongoDB that allows you to insert multiple documents into a
collection at once. The method takes an array of documents as its argument, and inserts each
document as a separate document in the collection.
SYNTAX:
Downloaded by saranya babu (saranya17113@gmail.com)

db.collection.insertMany(
[ <document 1 > , <document2>, ... ],
{
writeConcern: <document>,
ordered: <boolean>
}
)
QUERY AND OUTPUT:
CSE> db.Student.insertMany([
{name: ,age:25,city:
{name: age:30,city:
{name: ,age:35,city:
{name: ,age:0,city:
])
acknowledged: true,
insertedlds: {
ObjectId("6u58acce4653642c945d4f93"),
ObjectId("6u58acce4653642c945d4f9u"),
ObjectId("658acce4653642c945d4f95"),
ObjectId("658acce4653642c945d4f96")
CSE> db.Student.findO
[
_id: ObjectId("658acce465362c945d4f93"),
name:
age: 25,
city:
} I
{
_id: ObjectId("6u58acce4653642c945d4f94"),
name:
age: 30,
city:
} I
{
name:
age: 35,
city:
} I
{
_id: ObjectId("6u58acce65362c945d4£96"),
name:
age: 0,
city:
CSE>
GRAPHICAL VIEW:
Step 1: Open MongoDB Compass Application.
Step 2: Click Connect on the displayed window.

Step 3: From the Databases select the database which has the collection you want to
insert document.
Step 4: Click the collection-> ADD DATA-> Insert document. Add fields to the
document and click Insert.
Insert Document
To Collection CSE.Student
o« o (3
I _id: ObjectId('645ba7fcf3ddd549c012f33a') Object Id
2 name: "Jennifer " String
3 age: 24 Int32
4 city: "Florida," String
-- 3
(
CSE.Student DOCU
Documents Aggregations Schema Explain Plan Indexes Validation
Fite,C g Type a query: { field: 'value' }

GE
GLEE9D z son cu«mo 1-6of6 >
_id: ObjectId('6458acce4653642c9454f93')
name: "John"
age: 25
city: "New York"
_id: ObjectId('6458acce4653642c945d4f94')
name; "Mary"
age: 30
city: "Los Angeles"
FINDING A SINGLE DOCUMENT:
In MongoDB, we can find a single document using the findOne() method, This method
returns the first document that matches the given filter query expression.
SYNTAX:
db.collection_name.findOne ()
QUERY AND OUTPUT:
Ihis document is available tree ot charge on -

mangod> use CSE
switched to db CSE
CSE> db.Student.findOneO
{
name:
age: 25,
city:
CSE>
DISPLAYING DOCUMENTS IN A FORMATTED WAY:

In MongoDB, we can display documents of the specified collection in a well-formatted way
using the pretty() method.
SYNTAX:
db.collection_name.find(). pretty()
QUERY AND OUTPUT:
CSE> db Student find0 pretty0

[
id ObjectId("658acce4653642c945du£93"),
name;
age
city:
},
{
id ObjectId("6u58acce4653642c945du£9u"),
name;
age:
city:
},
{
id ObjectId("6u58acce4653642c945d4f95"),
name:
age
city:
},
{
id ObjectId("658acce4653642c945d4f96"),
name
age:
city
},
{
id ObjectId("6458b7lac0822e0f680e6d77"),
name;
GREATER THAN FILTER QUERY:

To get the specific numeric data using conditions like greater than equal or less than equal
use the $gte or $1te operator in the find() method.
SYNTAX:
db.collection_name.find( {<key > : { $gte : < value >} } )

or
db.collection_name.find( {< key > : { $lte : < value >} } )

QUERY AND OUTPUT:
CSE> db.Student.find({
[
id: ObjectId("6u58acceu653642c945d0£93"),
name:
age: 25,
city:
},
{
_id: ObjectId("6u58acceu65362c945du+9u"),
name:
age:
city:
},
{
name:
age:
city:
},
{
name:
age:
city:
DELETING THE FIRST DOCUMENT:

In this example, we are deleting the first document from the Student collection by passing an
empty document in the db.collection.deleteOne() method.
SYNTAX:
db.collection_name.deleteOne( { } )
QUERY AND OUTPUT:

CSE> db.Student.deleteOne({})
{ acknowledged: , deletedCount:
CSE> db.Student.find()
[
id: ObjectId("6u58acceu65362c945du£95"),
name;
age:
city:
},
{
id: ObjectId("6u58acceu65362c9u5du£96"),
name:
age:
city:
},
{
id: ObjectId("6u5ba7+cf3ddd59c012£33a"),
name;
age:
city:
CSE>
DROPPING A COLLECTION:
In MongoDB, a collection is a group of MongoDB documents that are stored together.
Dropping a collection in MongoDB means permanently deleting the entire collection and all of its
contents from the database.
SYNTAX:
db.collection_name.drop()
QUERY AND OUTPUT:

CSE> db.Student.drop()
trut
CSE>
DROP A DATABASE:
In MongoDB, databases hold collections of documents. On a single MongoDB server, we can
run multiple databases. when you install MongoDB some databases are automatically generated to
use. many times you need to delete some database when the database is no longer used.
db.dropDatabase() the command is used to drop an existing database. This command will
delete the currently selected database. If you have not selected any database, then it will delete the
default 'test' database.
SYNTAX:
db.dropDatabase()
QUERY AND OUTPUT:
CSE> db.dropDatabase()
{ ok: 1, dropped: }
CSE>
Ex.No. 12 Develop a simple GUI based database application and incorporate all the above-
mentioned features ( Displaying student mark list)
Aim:
Write a program in Java to create Displaying student mark listu sing JSP and Databases (three tire
architecture).
Definition, usage and procedure
Three tier architecture is a very common architecture. A three tier architecture is typically split into a
presentation or GUI tier, an application logic tier, and a data tier.
Presentation tier encapsulates the presentation logic required to serve clients. A JSP in the presentation
tier intercepts client requests, manages logons, sessions, accesses the business services, and finally
constructs a response, which gets delivered to client.
Business tier provides the business services. This tier contains the business logic and the business data.
All the business logic is centralized into this tier as opposed to 2-tier systems where the business logic is
scattered between the front end and the backend. The benefit of having a centralized business tier is that
same business logic can support different types of clients like browser, WAP (Wireless Application
Protocol) client. In our exercise we will use servlet as business tier.
Data Tier
Data tier is used by the databases
JSP
Java Server Pages (JSP) is a server-side programming technology that enables the creation of dynamic,
platform-independent method for building Web-based applications. JSP have access to the entire family
of Java APIs, including the JDBC API to access enterprise databases
Servlet
A servlet is a small Java program that runs within a Web server. Servlets receive and respond to
requests from Web clients, usually across HTTP, the HyperText Transfer Protocol
Client:
Step1: In index.html on the client side declare the contents that you like to transfer to the server using
html form and input type tags.
Step2: create a submit button and close all the included tags.
Servlet:
Step 1: Import all necessary packages
Step 2: Define a class that extends servlet
Step 3: In the doPost() method, do the following: i) Set the content type of the response to "text/html"
ii) connect with the database which has the student marklist iii) query the data to the database
Step 4: Display the student marklist
First Create database as db8 in that create table as mark with the following field
create table mark(rno varchar(20),name1 varchar(20),m1 varchar(20),m2 varchar(20),m3
varchar(20),m4 varchar(20),m5 varchar(20),m6 varchar(20))
insert into mark values('100','mohammed','90','90','90','90','90','90')
select * from mark
index.html
<head>
<title>Three Tier Application</title>
<style type="text/css">
body{color:blue;font-family:courier;text-align:center}
</style>
</head>
<body>
<h2>EXAMINATION RESULT</h2><hr/>
<form name="f1" method="GET" action="marklist.jsp">
Enter Your Reg.No:
<input type="text" name="rno"/><br/><br/>

<input type="submit" value="SUBMIT"/>
</form>
</body>
</html>
<html>
Marklist.jsp
<%@page import="java.util.Properties"%>
<%@page contentType="text/html" language="java" import="java.sql.*"%>
<html>
<head>
<title>Three Tier Application</title>
<style type="text/css">
body{color:blue;font-family:courier;text-align:center}
</style>
</head>
<body>
<h2>EXAMINATION RESULT</h2><hr/>
<%
String str=request.getParameter("rno");
Class.forName("org.apache.derby.jdbc.ClientDriver");
Properties p=new Properties();
p.put("user","root");
p.put("password","root");
Connection con=DriverManager.getConnection("jdbc:derby://localhost:1527/db8",p);
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery( " Select * FROM mark WHERE rno ='"+str+"'");
while(rs.next())
{
%>
Register No:<%=rs.getString(1)%><br/>
Name:<%=rs.getString(2)%><br/>
<table border="1">
<th>SUBJECT</th><th>Mark</th>
<tr><td>NPM</td><td><%=rs.getString(3)%></td></tr>
<tr><td>OOAD</td><td><%=rs.getString(4)%></td></tr>
<tr><td>CNS</td><td><%=rs.getString(5)%></td></tr>
<tr><td>Es</td><td><%=rs.getString(6)%></td></tr>
<tr><td>Web Technology</td><td><%=rs.getString(7)%></td></tr>
<tr><td>UID</td><td><%=rs.getString(8)%></td></tr>
</table>
<%
}
%>
<br/>
<a href="index.jsp">Back</a>
</body>
</html>
Sample Output:
Ex.No 13 Case Study of Cop Friendly App – Eseva
Aim
To case Study of Cop Friendly APP –Eseva
About Case study

The Case Study is all about Smart Cop, a mobile application or tool developed by Sapio Analytics along
with Dinosys Infotech that places a strong emphasis on combining existing interdisciplinary
information, improving analysis techniques, and developing more efficient police strategies. For
example, the Data-Driven Approach to Crime Scene Management is a technology that specializes in
handling evidence collected at a crime scene, and its notification feature aids in pulling data from
various media. Escape routes and the location of the incident could also be tracked. Also, describes how
the police can effectively manage their limited resources with this new data-driven and AI-based
policing
Purpose of this Case Study

The purpose of the case study is to demonstrate how using the SMART COP tool developed by Sapio
Analytics along with Dinosys Infotech when used on a daily basis in policing can help police agencies to
figure out what works in crime reduction and crime prevention initiatives, as well as lead to much more
effective decisions, faster actions, which can lead to better policing, higher impact on citizens, and
improved citizen satisfaction. It's to demonstrate the value of such efficacious, efficient, and cost-
effective law enforcement strategies and tactics based on data and analytics, especially when it's aided
by Sapio Analytics' exclusive systems.
Background of the Product

Sapio Analytics along with Dinosys Infotech is providing training consultations and analytical solutions
backed by Artificial Intelligence to the enforcement bodies in matters related to cyber-crime, suspect
profiling, predictive policing, and so on. The Smart Cop tool developed by Sapio Analytics along with
Dinosys is an essential guarantee to modernize the police to manage local security. It will also
emphasize the new exigencies of the police management system. It can likewise be utilized for mapping
crime which can monitor high-crime locations. With this, police can monitor very closely and have a
real-time pulse on criminal activities. The mounted Smart Cop App will enhance the balance, security,
sustenance, and scalability of the existing department. The application facilitates an integrated policing
management system along with an information processing platform that will empower front-line Police
Officers who are on a beat towards achieving Global Policing Standards
Problem Statement
India is known as the world's largest democratic country, little is known about how it polices such a
vast, complex, and unpredictable country. As a result, police officers encounter challenges and barriers
in carrying out their duties on a daily basis. Some of the problems faced are
• The police leadership has not placed a high priority on using technology to deliver services to citizens.
• Investigations are being delayed due to a lack of collaboration between internal divisions.
• The training standards are quite inadequate and do not account for the use of new technology.
• There is a significant disparity between the rate at which crimes are committed and the rate at which
FIRs are filed.
• The workload is one of the key causes of police inefficiency once again
Solution to the Problem
The fundamental function of police forces is to uphold and investigate crimes, safeguard the safety of
citizens, and enforce laws. In a large and populous country like India, police forces must be well-
equipped to fulfill their duties effectively. As a result, the police force must adjust to changing
conditions. Police modernization has been needed for data protection, counterterrorism/insurgency, and
reliance on technology for policing. This necessitates more investment in technological advancement
and modernization. So, to solve the problem in a modern way Sapio Analytics Came up with Smart Cop
which aims to train the law enforcement agencies on emerging technology, mitigating cybercrimes,
enhancing their skill set as well as capacity building so that they are combatready in a real-time basis.
The Smart Cop is an essential guarantee to modernize the police to manage local security. It will also
emphasize the new exigencies of the police management system.
Benefits of the App
The main aim of Smart Cop is to digitize the whole functioning of beats. In the due process, we
facilitated the beat police with one application and have integrated the IT applications and databases
which are in line with the beat system, and converged them on to Smart Cop
• Effectively utilizing Information and Communication Technology (ICT) traversing from E-Governance to
M-Governance
• Empowering and delegating the frontline Beat Police Officers for demonstrating quick & smart decision
making
• Delivering Services 'Anytime & Anywhere' for faster response to the Citizens
• Seamless integration of different application functionalities through Single-Sign-On services
• Efficient identification and tracking of suspects, quicker resolution of cases, and increased rate of
convictions for the offenders
• Proactively preventing crimes through real-time intelligence inputs and analysis relating to crimes and
criminals
• Encouraging transparency and accountability in every police personnel
Conclusive Summary
The case study shows how as the rate of crime rises; the utilization of existing Artificial Intelligence
algorithms is proving to be extremely beneficial. The tool developed by Sapio Analytics along with
Dinosys Infotech- SMART COP sets a promising example. To a considerable extent, Smart Cop aids
in the prediction of crime as well as the criminal. Artificial intelligence has the potential to become a
permanent element of the criminal justice system. Technological reforms are required to accomplish
the vision of SMART policing, it's important to train the police for new challenges, and strengthen
their investigative and emergency response capabilities. This will eventually increase public
confidence in the police force's effectiveness and its ability to serve efficiently. The police force
must be eager to bring a change and adopt new-age technologies and systems into the realm of law
enforcement for it to be more proactive than reactive.
Content Beyond the Syllabus
Miscellaneous functions in SQL
Aim:
To execute date and other miscellaneous functions in SQL
Description:
a) SYSDATE
This will give the current date and time.
Ex:
SQL> select sysdate from dual;
SYSDATE
24-DEC-06
b) CURRENT_DATE
This will returns the current date in the session’s timezone.
Ex:
SQL> select current_date from dual;
CURRENT_DATE
24-DEC-06
c) CURRENT_TIMESTAMP
This will returns the current timestamp with the active time zone information.
Ex:
SQL> select current_timestamp from dual;
CURRENT_TIMESTAMP
24-DEC-06 03.42.41.383369 AM +05:30
d) SYSTIMESTAMP
This will returns the system date, including fractional seconds and time zone of the database.
Ex:
SQL> select systimestamp from dual;
SYSTIMESTAMP
24-DEC-06 03.49.31.830099 AM +05:30
e) LOCALTIMESTAMP
This will returns local timestamp in the active time zone information, with no time zone information
shown.
Ex:
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
24-DEC-06 03.44.18.502874 AM
f) DBTIMEZONE
This will returns the current database time zone in UTC format. (Coordinated Universal Time)
Ex:
SQL> select dbtimezone from dual;
DBTIMEZONE
-07:00
g) SESSIONTIMEZONE
This will returns the value of the current session’s time zone.
Ex:
SQL> select sessiontimezone from dual;
SESSIONTIMEZONE
+05:30
h) TO_CHAR
This will be used to extract various date formats.The available date formats as follows. Syntax:
to_char (date, format)
SQL> select to_char(sysdate,'dd month yyyy hh:mi:ss am dy') from dual;
TO_CHAR(SYSDATE,'DD MONTH YYYY HH:MI
24 december 2006 02:03:23 pm sun
i) TO_DATE
This will be used to convert the string into data format. Syntax: to_date (date)
Ex:
SQL> select to_char(to_date('24/dec/2006','dd/mon/yyyy'), 'dd * month * day') fromdual;
TO_CHAR(TO_DATE('24/DEC/20
24 * december * Sunday
-- If you are not using to_char oracle will display output in default date format.
j) ADD_MONTHS
This will add the specified months to the given date. Syntax: add_months (date, no_of_months)
Ex:
SQL> select add_months(to_date('11-jan-1990','dd-mon-yyyy'), -5) from dual;
ADD_MONTH
11-AUG-89
If no_of_months is zero then it will display the same date.
If no_of_months is null then it will display nothing.
k) MONTHS_BETWEEN
This will give difference of months between two dates. Syntax: months_between (date1, date2)
Ex:
SQL> select months_between(to_date('11-aug-1990','dd-mon-yyyy'), to_date('11-jan-
1990','dd-mon-yyyy')) from dual; MONTHS_BETWEEN(TO_DATE('11-AUG-1990','DDMON-
YYYY'),TO_DATE('11-JAN-1990','DD-MON-YYYY'))
l) NEXT_DAY
This will produce next day of the given day from the specified date. Syntax: next_day (date, day)
Ex:
SQL> select next_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun') from dual;
NEXT_DAY(
31-DEC-06
-- If the day parameter is null then it will display nothing.
m) LAST_DAY
This will produce last day of the given date. Syntax: last_day (date)
Ex:
SQL> select last_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun') from dual;
LAST_DAY(
31-DEC-06
n) EXTRACT
This is used to extract a portion of the date value. Syntax: extract ((year | month | day | hour |
minute | second), date)
Ex:
SQL> select extract(year from sysdate) from dual;
EXTRACT(Y
EARFROMS
YSDATE)
2006
-- You can extract only one value at atime.
o) GREATEST
This will give the greatest date. Syntax: greatest (date1, date2, date3 … daten)
Ex:
SQL> select greatest(to_date('11-jan-90','dd-mon-yy'),to_date('11-mar-90','dd-
monyy'),to_date('11- apr-90','dd-mon-yy')) from dual;
GREATEST( 11-APR-90, AUG-1990','DD-MON-YYYY')
-7
p) LEAST
This will give the least date. Syntax: least (date1, date2, date3 … daten)
Ex:
SQL> select least(to_date('11-jan-90','dd-mon-yy'),to_date('11-mar-90','dd-
monyy'), to_date('11-apr- 90','dd-mon-yy')) from dual;
LEAST(
11-JAN-90
q) UID
This will returns the integer value corresponding to the user currently logged in.
Ex:
SQL>
select
uid from
dual;
UID
319
r) USER
This will returns the login’s user name.
Ex:
SQL>
select
user
from
dual;
USER
SAKETH
s) VSIZE
This will returns the number of bytes
in the expression. Ex:
SQL> select vsize(123), vsize('computer'), vsize('12-jan-90')
from dual; VSIZE(123) VSIZE('COMPUTER') VSIZE('12-JAN-
90')
389
t) RANK
This will give the non-
sequential ranking. Ex:
SQL> select rank(2975) within group(order by saldesc) from emp;
RANK(2975)WITHINGROUP(ORDERBYSALDESC)
4
t) DENSE_RANK
This will give the
sequential
ranking. Ex:
SQL> select dense_rank(2975) within group(order by saldesc) from emp;
DENSE_RANK(2975)WITHINGROUP(ORDERBYSALDESC)
3

DBMS Manual

Uploaded by

Copyright:

Available Formats

DBMS Manual

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DBMS Manual

Uploaded by

Copyright:

Available Formats

JAI SHRIRAM ENGINEERING COLLEGE

AVINASHIPALAYAM, TIRUPUR-638 660.

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

CS3481 – DATABASE MANAGEMENT SYSTEMS LABORATORY

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

COs Knowledge Level Course Outcomes

C218.1 K6 Create databases with different types of key constraints.

C218.5 K6 Create and manipulate data using NOSQL database.

Query the database tables using different ‘where’ CO1, 1,2,3,4,5,10,11,

Create an XML database and validate it using XML CO4 1,2,3,4,5,10,11,

CONTENT BEYOND THE SLLABUS

Data Definition Language

• Create - It is used to create a table.

• Desc - This is used to view the structure of the table.

Step 2: Display the table by using desc command.

Step 4: Modify the existing table with modify command.

Step 5: Delete the records in files by truncate command.

Create table tablename

SQL> Create table Student(Stud_name char(20), Stud_id varchar2(10), Stud_dept varchar2(20),

SQL> desc Student;

Name Null? Type

Alter table tablename add (column_name datatype(size));

SQL> Alter table Student add (Stud_addr varchar2 (20));

SQL> desc Student;

STUD _ NAME CHAR(20)

SQL> Alter table Student modify (Stud_age number(10));

SQL> desc Student;

STUD _ NAME CHAR(20)

SQL> desc Student;

Name Null? Type

Truncate table table_name;

SQL> desc Student

Name Null? Type

Alter table table_name rename new_table_name

SQL> alter table student rename student1;

SQL> desc student1;

Name Null? Type

Drop table tablename;

SQL> Drop table Student1;

SQL> desc Student1;

Data Manipulation Language

Step 1: Create table by using create table command.

SQL> desc Student;

Name Null? Type

SQL> Insert into Student1 values(‘&stud_name’, ‘&stud_id’, ‘&stud_dept’, ‘&stud_rollno’);

Insert into Student1 values (‘Ram’, ‘101’, ‘MECH’, ‘104’)

Select * from table_name;

Update table_name set column_name=’value’ where condition;

SQL> Update Student1 set stud_id=’109’ where stud_name=’Saddiq’;

SQL> select * from Student1;

Delete from table_name where condition;

SQL> Delete from Student1 where stud_dept=’CSE’;

SQL> select * from Student1;

CONSTRAINTS CAN BE CREATED IN THREE WAYS:

SQL>insert into notnull values(‘’,’raj’,’30000’)

ORA-01400: cannot insert NULL into (“SCOTT”.”NOTNULL”.”ENO”)

SQL>insert into con values(‘2’,’raja’,’9000’)

SQL>insert into conn values(‘1’,’hi’)

SQL>insert into con values (‘1’,’vijay’)

SQL> alter table con add constraint c1 primary key (empid);