List of Experiments
1. Execute DDL commands - creation of table, alter, modify and drop column.
2. Execute DML commands – Select, Update, Delete
3. Execute TCL commands – COMMIT, SAVEPOINT, ROLLBACK
4. Create Virtual tables.
5. Perform various Joins.
6. Apply UNION, INTERSECTION and MINUS operations on tables.
7. Execute PL/SQL Procedures to find the sum of 2 numbers.
8. Execute PL/SQL Procedures to find the given number is ODD or EVEN.
9. Execute PL/SQL Procedure to find the factorial of a given number.
10. Execute PL/SQL Procedure to print the Fibonacci series up to a given number.
[Link] DATA DEFINITION LANGUAGES (DDL) COMMANDS
AIM: To execute DDL commands - creation of table, alter, modify and drop column.
SYNTAX:
DDL COMMANDS
A) Create Table Command:
CREATE TABLE <table name>
(<col1><datatype>(<size>),<col2><datatype><size>));
B) Modifying the structure of the tables
ALTER TABLE <tablename>ADD(<new col><datatype(size),<new
col>datatype(size));
C) Drop the table.
DROP TABLE <tablename>;
SQL QUERY:
A) CREATE TABLE Employee(empidint,empname varchar(10),emploc varchar(10),empdept
varchar(10), email varchar(10), phone varchar(10), salary int);
INSERT INTO Employee VALUES
(001,'Abhi','Mysore','Science','abhijithc@[Link]',789456,3000);
INSERT INTO Employee VALUES
(002,'surya','Mysore','Science','csurya2020@[Link]',74185296,3500);
SELECT * FROM Employee;
B) ALTER TABLE Employee ADD(place varchar(10));
INSERT INTO Employee (place) VALUES('Kerala');
SELECT * FROM Employee;
C) DROP TABLE Employee;
RESULT
Thus the DDL commands was performed successfully and executed.
[Link] DATA MANUPLATION LANGUAGES (DML) COMMANDS
AIM: To execute DML commands – Select, Update, Delete
SYNTAX:
DML COMMANDS
A) Select Command:
SELECT column1, column2, ...
FROM table_name;
B) Modifying the structure of the tables
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
C) Delete the table.
DELETE FROM table_name WHERE condition;
SQL QUERY:
A) CREATE TABLE Employee(empidint,empname varchar(10),emploc varchar(10),empdept
varchar(10), email varchar(10), phone varchar(10), salary int);
INSERT INTO Employee VALUES
(001,'Abhi','Mysore','Science','abhijithc@[Link]',789456,3000);
INSERT INTO Employee VALUES
(002,'surya','Mysore','Science','csurya2020@[Link]',74185296,3500);
SELECT * FROM Employee;
SELECT empid,emploc FROM Employee;
A) UPDATE Employee
SET emploc = 'Kerala', empdept=’Maths'
WHERE empid = 1;
SELECT * FROM Employee;
B) DELETE FROM Employee WHERE empid=2;
RESULT
Thus the DML commands was performed successfully and executed.
[Link] TRANSACTION CONTROL LANGUAGES (TCL) COMMANDS
AIM: To execute TCL commands – COMMIT,SAVEPOINT, ROLLBACK
SYNTAX:
TCL COMMANDS
A) COMMIT : Using commit command, permanently save a transaction.
COMMIT;
B) Use savepoint command to temporarily save a transaction.
SAVEPOINT savepoint_name;
C) Using rollback command restore to last committed state.
SQLQUERY:
CREATE TABLE Student(stdid int, stdname varchar(10),age int);
INSERT INTO Student VALUES (001,'Abhi',12);
INSERT INTO Student VALUES (002,'Abhishek',14);
INSERT INTO Student VALUES (003,'Bhagya',14);
INSERT INTO Student VALUES (004,'Kripa',14);
A) COMMIT;
SELECT * FROM Student;
B) INSERT INTO Student VALUES (005,'Manoj',14);
INSERT INTO Student VALUES (006,'Manivarnan',13);
INSERT INTO Student VALUES (007,'Prasad',14);
INSERT INTO Student VALUES (008,'Prakash',14);
SAVEPOINT A;
SELECT * FROM Student;
DELETE FROM Student WHERE stdid=2;
DELETE FROM Student WHERE stdid=7;
SAVEPOINT B;
SELECT * FROM Student;
C) ROLLBACK TO A;
SELECT * FROM Student;
RESULT
Thus the TCL commands was performed successfully and executed.
[Link] VIEWS
AIM: To Create View named ‘Employee_table’ with attributes employee name and ID.
SYNTAX:
A) Create a view named ‘Employee_table’
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
B) Update the employee name as suryanarayan in the view where ID is 002.
C) The organization wants to display only the details like employee number,
employee name, department name, salary of the employees as another view
D) Execute the DML commands on the view created.
SQLQUERY:
CREATE TABLE Employee(empidint,empname varchar(10),emploc varchar(10),empdept
varchar(10), email varchar(10), phone varchar(10), salary int);
INSERT INTO Employee VALUES
(001,'Abhi','Mysore','Science','abhijithc@[Link]',789456,3000);
INSERT INTO Employee VALUES
(002,'surya','Mysore','Science','csurya2020@[Link]',74185296,3500);
INSERT INTO Employee VALUES
(001,'Abhi','Mysore','Science','abhijithc@[Link]',789456,3000);
INSERT INTO Employee VALUES
(002,'surya','Mysore','Science','csurya2020@[Link]',74185296,3500);
INSERT INTO Employee VALUES
(001,'Abhi','Mysore','Science','abhijithc@[Link]',789456,3000);
INSERT INTO Employee VALUES
(002,'surya','Mysore','Science','csurya2020@[Link]',74185296,3500);
A) CREATE VIEW Employee_table AS SELECT empname,empid from Employee;
B) UPDATE Employee_table SET Empname=’Surya Narayan’ WHERE empid=2;
C) CREATE VIEW Employee_details AS SELECT empid, empname,empdept, salary from
Employee;
D) SELECT * FROM Employee_details;
DROP VIEW Employee_details;
RESULT
Thus the View commands was performed successfully and executed.
[Link] JOINS
AIM: To perform Joins in two tables.
SYNTAX:
A) Inner Join
B) Left Join
C) Right Join
SQLQUERY:
CREATE TABLE Student(stid int, stname varchar(10), age int);
INSERT INTO Student VALUES (001,'Abhi',13);
INSERT INTO Student VALUES (002,'Kiran',14);
INSERT INTO Student VALUES (003,'Swaroop',13);
INSERT INTO Student VALUES (004,'Chandrika',13);
INSERT INTO Student VALUES (005,'Ashwathi',13);
SELECT * FROM Student;
CREATE TABLE Department(dptid int, dpname varchar(10), Course varchar(10),stid int);
INSERT INTO Department VALUES (101,'IT',’BSc’,001);
INSERT INTO Department VALUES (102,'IT',’BSc’,003);
INSERT INTO Department VALUES (103,'IT',’BCA’,005);
INSERT INTO Department VALUES (104,'CSE',’BSc’,004);
INSERT INTO Department VALUES (105,'CSE',’BCA’,002);
A) SELECT [Link], [Link], [Link] FROM student
INNER JOIN department ON [Link]=[Link];
B) SELECT [Link],[Link],[Link] FROM student
LEFT JOIN department ON [Link]= [Link];
C) SELECT [Link], [Link], [Link] FROM student
RIGHT JOIN department ON [Link]= [Link];
RESULT
Thus the sql joins were performed successfully and executed.
[Link] UNION, INTERSECTION AND MINUS
AIM: To perform UNION, INTERSECTION and MINUS operations on tables.
SYNTAX:
A) UNION
SELECT column_name(s) FROM table1
[WHERE condition]
UNION
SELECT column_name(s) FROM table2;
[WHERE condition]
B) INTERSECTION
SELECT column_name(s) FROM table1
[WHERE condition];
INTERSECT
SELECT column_name(s)FROM table1
[WHERE condition];
C) MINUS
SELECT column_name(s) FROM table_name
[WHERE condition];
MINUS
SELECT column_name(s) FROM table_name
[WHERE condition];
SQLQUERY:
CREATE TABLE COLOUR_A(COL Varchar(10));
INSERT INTO COLOUR_A VALUES(‘RED’);
INSERT INTO COLOUR_A VALUES(‘YELLOW’);
INSERT INTO COLOUR_A VALUES(‘GREEN’);
INSERT INTO COLOUR_A VALUES(‘ORANGE’);
INSERT INTO COLOUR_A VALUES(‘WHITE’);
SELECT * FROM COLOUR_A;
CREATE TABLE COLOUR_B(COL varchar(10));
INSERT INTO COLOUR_B VALUES(‘VIOLET’);
INSERT INTO COLOUR_B VALUES(‘PINK’);
INSERT INTO COLOUR_B VALUES(‘BLACK’);
INSERT INTO COLOUR_B VALUES(‘ORANGE’);
INSERT INTO COLOUR_B VALUES(‘WHITE’);
SELECT * FROM COLOUR_B;
A) SELECT COL FROM COLOUR_A UNION SELECT COL FROM COLOUR_B;
B) SELECT COL FROM COLOUR_A MINUS SELECT COL FROM COLOUR_B;
C) SELECT COL FROM COLOUR_A INTERSECT SELECT COL FROM
COLOUR_B;
RESULT
Thus the UNION, MINUS, INTERSECT were performed successfully and executed.
[Link] PL/SQL Procedures- Sum of Two Numbers
AIM: To execute PL/SQL Procedures to find the sum of 2 numbers.
SYNTAX:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
PROCEDURE:
Declare
Var1 integer;
Var2 integer;
Var3 integer;
Begin
Var1:=&var1;
Var2:=&var2;
Var3:=var1+var2;
dbms_output.put_line(var3);
End;
/
RESULT:
Thus the procedure for Sum of two numbers was performed and executed
successfully.
[Link] PL/SQL Procedures- ODD or EVEN
AIM: To execute PL/SQL Procedures to find the given number is ODD or
EVEN.
SYNTAX:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
PROCEDURE:
declare
n number:=&n;
begin
if mod(n,2)=0
then
dbms_output.put_line('number is even');
else
dbms_output.put_line('number is odd');
end if;
end;
/
RESULT:
Thus the procedure, to find if the number is odd or even was performed
and executed successfully.
[Link] PL/SQL Procedures- FACTORIAL of a number
AIM: To execute PL/SQL Procedure to find the factorial of a given number.
SYNTAX:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
PROCEDURE:
declare
n number;
facnumber:=1;
i number;
begin
n:=&n;
for i in 1..n
loop
fac:=fac*i;
end loop;
dbms_output.put_line('factorial='||fac);
end;
/
RESULT:
Thus the procedure , to find the factorial of a number was performed and
executed successfully .
[Link] PL/SQL Procedures- FIBNONACCI SERIES
AIM: To execute PL/SQL Procedure to print the Fibonacci series up to a given
number.
SYNTAX:
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
PROCEDURE:
declare
first number:=0;
second number:=1;
third number;
n number:=&n;
i number;
begin
dbms_output.put_line('Fibonacci series is:');
dbms_output.put_line(first);
dbms_output.put_line(second);
for i in 2..n
loop
third:=first+second;
first:=second;
second:=third;
dbms_output.put_line(third);
end loop;
end;
/
RESULT:
Thus the procedure , to print the Fibonacci series up to a given number
was performed and executed successfully .