LIST OF EXPERIMENTS
1. Creation of a database and writing SQL queries to retrieve information from the database.
2. Implementation of DML, DCL and TCL
3. Queries to demonstrate implementation of Integrity Constraints
4. Practice of Inbuilt functions
5. Creation of Views, Synonyms, Sequence, Indexes, Save point.
6. Implementation of Nested Queries
7. Implementation of Join and Set operators
8. Creating an Employee Database to set various constraints.
9. Implementation of Virtual tables using Views
10. Study of PL/SQL block.
11. Write a PL/SQL block to satisfy some conditions by accepting input from the user.
12. Write a PL/SQL block that handles all types of exceptions.
13. Creation of Procedures and functions.
14. Creation of database triggers and cursors.
15. Application Development using Front End Tools and Database Connectivity.
EX.NO: 1 IMPLEMENTATION OF DDL COMMANDS
DATE:
AIM:
To create a DDL to perform the Creation of a database and writing SQL queries to retrieve
information from the database.
DDL COMMANDS
1. The Create Table Command - It defines each column of the table uniquely. Each column
has minimum of three attributes, a name, data type and size.
Syntax:
Create table <table name> (<col1> <datatype>(<size>),<col2> <datatype><size>));
Ex: create table emp(empno number(4) primary key, ename char(10));
2. Modifying the structure of tables.
a) Add new columns
Syntax:
Alter table <tablename> add(<new col><datatype(size),<new col>datatype(size));
Ex: alter table emp add(sal number(7,2));
3. Dropping a column from a table.
Syntax:
Alter table <tablename> drop column <col>;
Ex: alter table emp drop column sal;
4. Modifying existing columns.
Syntax:
Alter table <tablename> modify(<col><newdatatype>(<newsize>));
Ex: alter table emp modify(ename varchar2(15));
5. Renaming the tables
Syntax:
Rename <oldtable> to <new table>;
Ex: rename emp to emp1;
6. truncating the tables.
Syntax:
Truncate table <tablename>;
Ex: trunc table emp1;
7. Destroying tables.
Syntax:
Drop table <tablename>;
Ex: drop table emp;
DESC
Example: desc emp;
Name Null?
Type
EmpNo NOT NULL number(5)
EName VarChar(15)
Job NOT NULL Char(10)
DeptNo NOT NULL number(3)
PHONE_NO number (10)
RESULT:
Thus the DDL commands have been executed and verified successfully.
EX.NO: 2 IMPLEMENTATIONS OF DML AND DCL COMMANDS
DATE:
AIM:
To study the various DML commands and implement them on the database.
DML COMMANDS
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.
Insert Command - This is used to add one or more rows to a table. The values are separated by
commas and the data types char and date are enclosed in apostrophes. The values must be entered
in the same order as they are defined.
Select Commands - It is used to retrieve information from the table. It is generally referred to as
querying the table. We can either display all columns in a table or only specify column from the
table.
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.
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.
1. INSERTING THE VALUES INTO THE TABLE
SQL> create table student(id number(6)primary key, name varchar(15), deptno number(2),
maths number(2), english number(2));
Table created.
SQL> desc student
Name Null? Type
ID NOT NULL NUMBER(6)
NAME VARCHAR2(15)
DEPTNO NUMBER(2)
MATHS NUMBER(2)
ENGLISH NUMBER(2)
SQL> insert into student values(163044,'krithica',12,70,85);
1 row created.
SQL> insert into student values(163054,'monisha',11,75,78);
1 row created.
SQL> insert into student values(163019,'dhanalakshmi',11,80,85);
1 row created.
SQL> insert into student values(163023,'dharshini',10,95,90);
1 row created.
SQL> insert into student values(163128,'nikitha',9,75,85);
1 row created.
SQL> select * from student;
ID NAME DEPTNO MATHS ENGLISH
163044 krithica 12 70 85
163054 monisha 11 75 78
163019 dhanalakshmi 11 80 85
163023 dharshini 10 95 90
163128 nikitha 9 75 85
2. UPDATING THE VALUES IN THE TABLE
SQL> update student set english=90 where maths=75;
2 rows updated.
SQL> select * from student;
ID NAME DEPTNO MATHS ENGLISH
163044 krithica 12 70 85
163054 monisha 11 75 90
163019 dhanalakshmi 11 80 85
163023 dharshini 10 95 90
3 SELECT :
a) Select columns from the table
SQL> select id, maths from student;
ID MATHS
163044 70
163054 75
163019 80
163023 95
163128 75
b) Select rows from the table
SQL> select * from student where maths>80;
ID NAME DEPTNO MATHS ENGLISH
163019 dhanalakshmi 11 80 85
163023 dharshini 10 95 90
c) Select entire table
SQL> select * from student;
ID NAME DEPTNO MATHS ENGLISH
163044 krithica 12 70 85
163054 monisha 11 75 90
163019 dhanalakshmi 11 80 85
163023 dharshini 10 95 90
163128 nikitha 9 75 90
4. DELETE:
SQL> delete from student where maths=70;
1 row deleted.
SQL> select * from student;
ID NAME DEPTNO MATHS ENGLISH
163054 monisha 11 75 90
163019 dhanalakshmi 11 80 85
163023 dharshini 10 95 90
163128 nikitha 9 75 90
ARRANGE IN ASCENDING ORDER
SQL> select * from student order by id;
ID NAME DEPTNO MATHS ENGLISH
163019 dhanalakshmi 11 80 85
163023 dharshini 10 95 90
163054 monisha 11 75 90
163128 nikitha 9 75 90
ARRANGE IN DESCENDING ORDER
SQL> select * from student order by id desc;
ID NAME DEPTNO MATHS ENGLISH
163128 nikitha 9 75 90
163054 monisha 11 75 90
163023 dharshini 10 95 90
163019 dhanalakshmi 11 80 85
DCL COMMANDS
The DCL language is used for controlling the access to the table and hence securing the
database. DCL is used to provide certain privileges to a particular user. Privileges are
rights to be allocated. The privilege commands are namely, Grant and Revoke. The
various privileges that can be granted or revoked are, Select Insert Delete Update
References Execute All.
GRANT COMMAND: It is used to create users and grant access to the database. It
requires database administrator (DBA) privilege, except that a user can change their
password. A user can grant access to their database objects to other users.
REVOKE COMMAND: Using this command, the DBA can revoke the granted
database privileges from the user.
1. G
R
A
N
T
:
a) Grant all privileges:
SQL>Grant all on student to
departments; Grant succeeded.
b) Grant some privileges of student:
SQL>grant select, update, insert on student to student with
grant option. Grant succeeded.
2. REV
OKE
a) Revoke all privileges:
SQL>revoke all on students3 from
departments; Revoke succeeded.
b) Revoke some privileges in student to student:
SQL>revoke select,update,insert on student to
student; Revoke succeeded.
TCL COMMAND
COMMIT: command is used to save the Records.
ROLL BACK: command is used to undo the
Records.
SAVE POINT command is used to undo the Records in a particular transaction.
1. SAVE POINT: sets a savepoint within a
transaction SQL>save point s1;
Save point created.
2. ROLL BACK: rollbacks a transaction in case of any
error occurs. SQL> rollback s1;
SQL>select * from student;
3. COMMIT : commits a
Transaction.
SQL>commit;
Commit completed
4. SET TRANSACTION:
SQL> set transaction [read write | read only];
RESULT:
Thus the DML, DCL and TCL commands have been executed and verified
successfully.
