EX NO:1 DDL COMMANDS

DATE:

AIM:
To create a database and write SQL quaries information from the
database.

DESCRIPTION:
DDL (data definition language) statement are used to
create ,changes the objects of the database. Typically a database
administrator is responsible for using DDL statement or production
database in a large data base system. The commands used are:
Create-it is used to create the table .
Alter- this command is used to add a new column, modify the existing
column definition and to include or drop integrity constraints
Drop- it will delete the table structure provided the table should empty.
Truncate-if there is no further use of records stored in the table and the
structure has to be retained and them the records alone can be deleted
Desc- this is used to view the structure of the table.
PROCEDURE:
step1:create table by using create table command with command
name,data type and size
step2:display the table by using desc command.
step3:add any new column with the exisiting by later command.
step4:modify the exisiting table with modify command.

Step5:delete the records in files by truncate command

Step6:delete the entire table by drop command

CREATE TABLE:
SYNTAX:
CREATE Table tablename
(column_name1datatype(size),column_namedatatype(size),…..);
EXAMPLE:
SQL>CREATE Table student(studname char(20),studid varchar2(10),studdept
varchar2(20),studage number(5));
Table created
SQL>desc student;
Name NULL? TYPE

STUDNAME CHAR(20)

STUDID VARCHAR(10)

STUDDEPT VARCHAR(20)

STUDAGE NUMNER(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

STUDNAME CHAR(20)
STUDID VARCHAR(10)
STUDDEPT VARCHAR(20)
STUDAGE VARCHAR(10)
STUD_ADDR VARCHAR(20)

SQL>ALTER Table student modify(studage number(10));

Tbale altered
SQL>desc student;

Name NULL? TYPE

STUDNAME CHAR(20)
STUDID VARCHAR(10)
STUDDEPT VARCHAR(20)
STUDAGE NUMBER(10)
STUD_ADDR VARCHAR(20)
SQL>ALTER student drop(studage number(10));
Table altered;
SQL>desc student;
Name NULL? TYPE
STUDNAME CHAR(20)
STUDID VARCHAR(10)
STUDDEPT VARCHAR(20)
STUD_ADRR VARCHAR(20)

TRUNCAQTE TABLE:
SYNTAX:
TRUNCATE Table tablename;
EXAMPLE:
SQL>TRUNCATE Table student;
Tabel truncate
SQL>desc student;
Name NULL? TYPE
STUDNAME CHAR(20)
STUDID VARCHAR(10)
STUDDEPT VARCHAR(20)
STUDAGE NUMBER(10)
STUD_ARRD NUMBER(10)

RENAME:
SYNTAX:
RENAME OLD_TABLE_NAME TO NEW _TABLE_NAME;

EXAMPLE:
SQL>RENAME student to student1;
Table altered
SQL>desc student1;
Name NULL? TYPE
STUDNAME CHAR(20)
STUDID VARCHAR(10)
STUDDEPT VARCHAR(20)
STUDAGE NUMBER(10)
STUD_ADDR VARCHAR(20)

DROP TABLE:
SYNTAX:
DROP TABLE TABLE_NAME;
EXAMPLE:
SQL>DROP TABLE student1;
Table dropped
SQL>desc student1;
ERROR:ORA-04043:OBJECT STUDENT1 DOES NOT ACCESS.
 DML COMMANDS
AIM:
To study and practice insertion,deletion,modifying,altered,
Updating and viewing records based on condition in RDBMS.
DESCRIPTION:
Data Manipulation Language:
DML comments are the most frequently used sql commands and is used to
query and manipulate the existing database objects. Some of the comments are
 Insert
 Select
 Update
 Delete
PROCEDURE:
STEP1:Create table by using create table command.
STEP2:Insert values into the table.
STEP3:Delete any records from the table.
STEP4:Update any values in the table.
STEP5:Display the values from the table by using select command.
SQL>create table student(studname char(20),studid varchar2(10),studdept
varchar2(20),studage number(5));
Table created.
SQL>desc Student;
Name NULL? TYPE
STUDNAME CHAR(20)
STUDID VARCHAR(10)
STUDDEPT VARCHAR(20)
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 a data are enclosed in
apostrophes. The values must be in the same order as they are defined.
SYNTAX:
Insert into tablename values(‘&column_name1’,’&column_name2’,…….);
SQL>Insert intpo student1 values(‘&studname’,’&studid’,’&studdept’,’&stud_age’);
Insert into student1 values(‘ram’,’101’,’mech’,’104’)
1 row created.
Insert into student1 values(‘vicky’,’102’,’eee’,’105’)
1 row created
Insert into students 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 retrive information from the table.it is generally reffered
to as querying 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;

STUDNAME STUDID STUDDEPT STUD_AGE

RAM 101 CSE 16
VICKY 102 ECE 17
SADDIQ 103 EEE 15
DAVID 104 MEC H 16
4 rows created.
UPDATED 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>Updated student1 set stud_id=’109’ where stud_name=’saddiq’;
SQL>select * from student1;
STUDNAME STUDID STUDDEPT STUD_AGE
RAM 101 CSE 16
VICKY 102 ECE 17
SADDIQ 109 EEE 15
DAVID 104 MECH 16
1 row created.
DELETE COMMAND:
After inserting row in the 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 student1 where stud_dept=’cse’;
1 row created.
EXAMPLE:
SQL>Delete from student1 where stud_dept=’cse’;
1 row deleted.
SQL>select * from student1;
STUDNAME STUDID STUDDEPT STUD_AGE
VICKY 102 ECE 17
SADDIQ 103 EEE 15
DAVID 104 MECH 16

RESULT:
THUS, the inserting,deleting,modifiying,alternating,updating and
viewing records based on conditions using sql commands were executed
and verified successfully.

EXP NO:2 CREATE THE SET OF TABLES,ADD FOREIGN KEYS AND

DATE: INCORPORATE THE REFERENCIAL THE REFERECIAL
INTEGRITY

AIM:
To create the set of tables ,add foreign keys and incorporate the
referencial integrity.

FOREIGN KEY:
1)the foreign key is the type of the that refers to the primary key of
the another table.
2)the table which has the primary key is called as the parent table.
3)the table which has the foreign key is called as the child table.

SYNTAX:
FOREIGN KEY(attribute) REFERENCES TABLE_NAME(attribute);

QUERY:CREATING TWO TABLES

SQL>CREATE TABLE department(dname varchar(20),did integer primary key);
Table created
SQL>CREATE TABLE student(studentname varchar(20),sid integer primary
key,foreign key(sid) references department(eid) ,sphone integer);
SQL>INSERT INTO department values(‘&dname’,’&did’);
Enter values of dname:cse
Enter values of did:101
old 1:insert into department values(‘&dname’,’&did’)
new 1:insert into department values(‘cse’,’101’)
1 row created
SQL>/
Enter the values of dname:ece
Enter the values of did:105
Old 1:insert into department values(‘dname’,’&did’)
New 1:insert into department values(‘ece’,’105’)
1 row created
SQL>/
Enter the value of dname:eee
Enter the value of did:109
old 1:insert into department values(‘dname’,’did’)
new 1:insert into department values(‘eee’,’109’)
1 row creates
SQL>SELECT * from department;
DNAME EID

CSE 101

ECE 105

EEE 109

REFERENCIAL INTEGRITY INCORPORATION:

Referencial integrity means the child table only accepts the values
Which is in the parent table. If any others them in the parent table it
shows
Error.
SQL>INSERT into student (‘&studentname’,’&sid’,’&sphone’);
Enter the value of studentname:ram
Enter the value of sid:101
Enter the value of sphone:9878767677
old 1:insert into student(‘&studentname’,’&sid’,’&sphone’);
new 1:insert into student(‘ram’,’101’,’9878767677’)
1 row created

SQL>/
Enter the value of studentname:seetha
Enter the value of sid:105
Enter the value of sphone:9865546774
Old 1:insert into student(‘&studentname’,’&sid’,’&sphone’);
New 1:insert into student(‘seetha’,’105’,’9865546774’)
1 row created
SQL>/
Enter the value of studentname:roja
Enter the value of sid:100
Enter the value of sphone:9577878885
ERROR: the value of sid is not found in parent key
SQL>SELECT * from student;
STUDENTNAME SID SPHONE

RAM 101 9878767677

SEETHA 105 9865546778

RESULT:
Thus the set of tables were created,foreign keys added and referencial
Integrity was incorporated.
EX.NO:3 QUERY THE DATABASE TABLES USING DIFFERENT
DATE: ‘WHERE’ CLAUSE CONDITIONS AND ALSO
IMPLEMENT AGGREGATION FUNCTIONS.

AIM:
To Query the database tables using different ‘where’clause condition and also
implement aggregation function.
WHERE CLAUSE:
The where clause is used to filter the tuples from the database tables.
SYNTAX:
SELECT col1,col2,…coln from table_name WHERE condition;
QUERY:CREATING TABLE:
SQL>CREATE Table demo(studentnamevarchar(80),sid integer,mark1 integer,mark2
Integer,mark3 integer);
Table created.
SQL>INSERT into demo values
(‘&studentname’,’&sid’,’&mark1’,’&mark2’,’&mark3’);
Enter value for studentname:ram
Entyer value for sid:101
Enter value for mark1:88
Enter value for mark2:90
Enter value fro mark3:98
Old 1:insert into demo
Values(‘&studentname’,’&sid’,’mark1’,’mark2’,’mark3’)

New1:insert into demo values(‘ram’,’101’,’88’,’90’,’98’)

1 row created.
SQL>/
Enter value for studentname:sam
Enter value for sid:102
Enter value for mark1:89
Enter the value for mark2:87
Enter the value for mark3:90
Old1:insert into demo
Values(‘&studentname’,’&sid’,’&mark1’,’&mark2’,’&mark2’,’&mark3’)
New1(’sam’,’102’,’89’,’87’,’90’)
1 row created.
SQL>/
Enter value for studentname:ravi
Enter value for sid:103
Enter value for mark1:89
Enter value for mark2:90
Enter value for mark3:78
Old1:insert into demo
Values(‘&studentname’,’&sid’,’&&mark1’,’&mark2’,’&mark3’)
New1:insert into demo
Values(‘ravi’,’103,’,’89’,’90’,’78’)
1 row created.
SQL>/
Enter value for studentname:gopi
Enter value for sid:104
Enter the value for mark1:89
Enter the value for mark2:98
Enter the value for mark3:78
Old1:insert into demo
Values(‘&studentname’,’&sid’,’&mark1’,’&mark2’,’&mark3’)
New1:insert into demo values(‘gopi’,’104,89,98,78)
1 row created.
QUERY:WHERE CLAUSE CONDITION
SQL>select * from demo;
STUDENTNA SID MARK1 MARK2 MARK3
ME
RAM 101 88 90 98
SAM 102 89 87 90
RAVI 103 89 90 78
GOPI 104 89 98 78
SQL>select mark1 from demo where sid>101;
MARK1
89
89
89
SQL>select studentname from demo where mark1>78;
STUDENTNAME
RAM
SAM
RAVI
GOPI
SQL>select sid from demo where mark2>90;
SID
104
AGGREGATE FUNCTIONS:
The function which takes multiple input parameter and produce
single output is called as the aggregation function.
There are five various functions:
SYNTAX:
MAX(column):
It return the maxiimum value from the value from the input column.
MIN(column):
It return the minimum value from the input column.
AVG(column):
It return the average vlue of the given input column.
SUM(column):
It returns the sum of the fiven input column.
COUNT(COLUMN):
IT return the total number of rows in the given input column.
QUERY:AGGEREGATE FUNCTIONS
SQL>select min(mark1)from demo;

Min(mark1)
88
SQL>select max(mark1) from demo;

Max(mark1)
89
SQL>select avg(mark1) from demo;
AVG(MARK1)
88.75
SQL>select sum(mark1) from demo;
SUM(MARK1)
 355
SQL>select count(mark1) from demo;
COUNT(MARK1)
4

RESULT:
Thus the where clause conditions and the aggregate function were
implemented successfully.
EX.NO:4 QUERY THE DATABASE TABLES AND EXPLORE SUB QUERIES
DATE: AND SIMPLE JOIN OPERTIONS

AIM:
To query the database tables and explore sun queries and simple join
operations.
SUBQUERIES:
The queries which as defined inside the another query is known as the
sub-queries. It is known as the nested queries.
There are two types of sub queries they are
Correlated
Non-correlated sub queries
SYNTAX:
SELECT columns from table where (…(select columns from table
where condition)…);
QUERY :THE QUERY TO FIND HIGNEST SALARYB FROM THE EMPLOYEE
TABLE
SQL>create table subquery(ename varchar(20),salary integer,eid integer);
Table created.
SQL>SELECT MAX(salary) from subquery;
MAX(SALARY)

SQL>INSERT INTO subquery values(‘&ename’,’&salary’,’&eid’);

Enter value for ename:ram
Enter value for salary:20000
Enter value for eid:101
Old1:insert into subquery values(‘&enmade’,’&salary’,’&eid’)
New1:insert into subquery values(‘ram’,’20000’,’101’)
I row created.
SQL>/
Enter value for ename:gopi
Enter value for salary:30000
Enter value for eid:102
Old1:INSERT INTO subquery values(&ename’,’&salary’,’&eid’)
New1:insert into subquery values(‘gopi’,’30000’,’102’)
1 row created.
SQL>/
Enter value for ename:sam
Enter value for salary:40000
Enter value for eid:103
Old1:insert into subquery values(‘&ename’,’&salary’,’&eid’)
New1:insert into subquery values(‘sam’,’40000’,’103’)
1 row created.
SQL>/
Enter value for ename:prem
Enter value for salary:50000
Entr value for eid:104
Old1:insert into values subquery values(‘&enmae’,’&salary’,’&eid’)
New1:insert into values subquery values(‘prem’,’50000’,’104’)
1 row created.
SQL>SELECT *from subquery;
ENAME SALARY EID
RAM 20000 101
GOPI 30000 102
SAM 400000 103
PREM 50000 104

SQL>SELECT * FROM subquery where salary=(select max(salary)from subquery);

ENAME SALARY EID

SAM 400000 103

SIMPLE JOIN OPERATIONS(INNER JOIN,OUTTER JOINS-LEFT,RIGHT,FULL)

Join operation is used display the records from two o r more tables.
INNER JOIN:
It is a type of the ANSI join in which the similar rows on both tables
only displayed.
SYNTAX:
SELECT * FROM tab_1 inner join tab_2 on tab_1.column=tab_2.column;
OUTTER JOIN:
LEFT OUTTER JOIN:
In the left outter join the similar rows on the both tables and along with
the left table records are displayed.
SYNTAX:
SELECT * FROM tab_1 LEFT JOIN tab_2 ON tab_1.column=tab_2.column;
RIGHT OUTTER JOIN:
In the right outter join the silmailar row on the both tables and along with
the right table records are displayed.
SYNTAX:
SELECT*FROM tab_1 RIGHT JOIN tab_2 ONtab_1.column=tab_2.column;
FULL OUTTER JOIN:
In the full outter join rather than similarities all the records are
matched if there is no record the NULL values is placed.
SYNTAX:
SLECT * FROM tab1 FULL JOIN tab_2 ON tab1.column=tab_2.column;
QUERY JOINS
SQL>CREATE TABLE department (dname vrchar(40),floor integer);
Table created.
SQL>INSERT INTO department values(&dname]’,’&floor);
Enter the values for dname:cooking
Enter the values for floor:1
Old1:insert into department values(‘&dname’,’&floor’)
New1:insert into department values(‘cooking’,’1’)
1 row created.
SQL>/
Enter the values for dname:washing
Enter the values for floor:2
Old1:insert into department values(‘&dname’,’&floor’)
New1:insert into department values(‘washing’,’2’)
1 row created.
SQL>/
Enter value for dnmae:cleaning
Enter value for floor:3
Old1:insert into department values(‘&dname’,’&floor’)
New1:insert into department values(‘cleaning’,’3’)
1 row created.
SQL>/
Enter value for dname:manufacturing
Enter value for floor:4
Old1:insert into department values(‘&dname’,’&floor’)
New1:insert into department values(‘manufacturing’,’4’)
1 row created.
SQL>/
Enter value for dname:accounting
Enter value for floor:5
Old1:insert into values (‘&dname’,’&floor’)
New1:insert into department values(‘accounting’,’5’)
1 row created.
SQL>create table employee(floor integer,eid integer);
Table created.
SQL>INSERT INTO employee values(‘&floor’,’&eid’)
Enter value for floor:1
Enter value for eid:101
Old1:insert into employee values(‘&floor’,’&eid’)
New1:insert into employee values(‘1’,’101’)
1 row created.
SQL>/
Enter value for floor:2
Enter value for eid:102
Old1:insert into employee values(‘&floor’,’&eid’)
New1:insert into employee values(‘2’,’102’)
1 row created.
SQL>/
Enter value for floor:3
Enter value for eid:103
Old1:insert into employee values(‘&floor’,’&eid’)
New1:insert into employee values(‘3’,’103’)
SQL>SELECT * FROM employee;
FLOOR EID
1 101
2 102
3 103
SQL>SELECT * FROM department;
DNAME FLOOR
COOKING 1
WASHING 2
CLEANING 3
MANUFACTURING 4
ACCOUNTING 5
SQL>SELECT * FROM employee INNER JOIN department ON
employee.floor=department.floor;
FLOOR EID DNAME FLOOR
1 101 COOKING 1
2 102 WASHING 2
3 103 CLEANING 3

SQL>SELECT * FROM employee LEFT JOIN department ON

employee.floor=department.floor;
FLOOR EID DNAME FLOOR
1 101 COOKING 1
2 102 WASHING 2
3 103 CLEANING 3

SQL>SELECT * FROM employee RIGHT JOIN department ON

employee.floor=department.floor;
FLOOR EID DNAME FLOOR
1 101 COOKING 1
2 102 WASHING 2
3 103 CLEANING 3
ACCOUNTING 5
MANUFACTURIN 4
G

SQL>SELECT * FROM employee FULL JOIN department ON

employee.floor=department.floor;
FLOOR EID DNAME FLOOR
1 101 COOKING 1
2 102 WASHING 2
3 103 CLEANING 3
ACCOUNTING 5
MANUFACTURIN 4
G

RESULT:
Thus the various sub queries were implemented and the
join operations were performed successfully and output was
verified.