Ex:1 SQL COMMANDS
Date:
Exp no:1-a
IMPLEMENTATION OF DDL/DML COMMANDS
AIM:
DESCRIPTION:
DDL:
CREATE: Creates a database, new table, a view of a
table, index, cursor and trigger or other object in
database.
ALTER: Modifies an existing database object, such as a
table, view and index etc.
DROP: Deletes an entire table, a view of a table or
other object in the database.
RENAME: Used to renaming table, view etc
TRUNCATE: Used to delete record permanent of a
table.
DML:
INSERT: used to insert a record on the table.
UPDATE: modification of records in the table.
DELETE: used to remove records in the table.
SYNTAX:
DDL:
CREATE DATABASE database_name
CREATE TABLE table_name
(Column_name datatype[(size)],
Column_name datatype[(size)],)
ALTER TABLE table_name
ADD (column datatype [Default Expression])
[REFERENCES table_name (column_name)’
[CHECK condition]
DROP TABLE table_name
DROP INDEX table_name
ALTER TABLE table_name RENAME TO
new_table_name;
TRUNCATE TABLE table_name;
DML:
INSERT INTO table_name[(column_list)] values
(value_list)
DELETE FROM table_name [WHERE Condition]
UPDATE table_name SET column_name1 = value1,
column_name2 = value2, …..[WHERE Condition]
CODE & OUTPUT:
SQL> create table studentDB(rrn int,name
varchar(10),department varchar(10));
Table created.
SQL> alter table studentDB add(year int);
Table altered.
SQL> alter table studentDB add(section varchar(10));
Table altered.
Name Null? Type
----------------------------------------- -------- ----------------------------
RRN NUMBER(38)
NAME VARCHAR2(10)
DEPARTMENT VARCHAR2(10)
YEAR NUMBER(38)
SECTION VARCHAR2(10)
SQL> alter table studentDB drop column section;
Table altered.
Name Null? Type
----------------------------------------- -------- ----------------------------
RRN NUMBER(38)
NAME VARCHAR2(10)
DEPARTMENT VARCHAR2(10)
YEAR NUMBER(38)
SQL>alter table studentDB rename to classDB;
Table altered.
SQL> insert into classDB values(1001,'Adhi','CSE',2);
1 row created.
SQL> insert into classDB values(1002,'Billy','CSE',2);
1 row created.
SQL> insert into classDB values(1003,'Cillian','CSE',2);
1 row created.
SQL> insert into classDB values(1004,'Dilli','CSE',2);
1 row created.
SQL> insert into classDB values(1005,'Edith','CSE',2);
1 row created.
SQL> select * from classDB;
RRN NAME DEPARTMENT YEAR
---------- ------------------ ------------------------
--------------------
1001 Adhi CSE 2
1002 Billy CSE 2
1003 Cillian CSE 2
1004 Dilli CSE 2
1005 Edith CSE 2
SQL> delete from classDB where rrn=1005;
1 rows deleted.
RRN NAME DEPARTMENT YEAR
---------- ------------------ ------------------------
--------------------
1001 Adhi CSE 2
1002 Billy CSE 2
1003 Cillian CSE 2
1004 Dilli CSE 2
SQL> update classDB set department='IT' where rrn=1003;
1 row updated.
SQL> update classDB set department='IT' where rrn=1004;
1 row updated.
RRN NAME DEPARTMENT YEAR
---------- ------------------ ------------------------
--------------------
1001 Adhi CSE 2
1002 Billy CSE 2
1003 Cillian IT 2
1004 Dilli IT 2
RESULT:
Exp no:1-b
IMPLEMENTATION OF DCL/TCL COMMANDS
AIM:
DESCRIPTION:
DCL:
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.
TCL:
COMMIT: It is used to permanently save any
transaction into database.
SAVEPOINT: It is used to temporarily save a transaction
so that you can rollback to that point whenever
necessary.
ROLLBACK: It restores the database to last committed
state. It is also use with save point command to jump
to a save point in a transaction
SYNTAX:
GRANT COMMAND
Grant <database_priv [database_priv.....] > to
<user_name> identified by
<password>[,<password.....];
Grant <object_priv> | All on <object> to <user |
public> [ With Grant Option ];
REVOKE COMMAND
Revoke <database_priv> from <user [, user ]>;
Revoke <object_priv> on <object> from<user| public >;
<database_priv>
COMMIT:
Commit;
SAVEPOINT:
Savepoint savepoint_name;
ROLLBACK:
Rollback to savepoint_name;
CODE & OUTPUT:
SQL> grant all on classDB to public;
Grant succeeded.
SQL> revoke all on classDB from public;
Revoke succeeded.
-------------------------------------------------------------------------
SQL> update classDB set year='3' where name='Billy';
1 row updated.
SQL> savepoint A;
Savepoint created.
SQL> insert into classDB
values(1006,'Heisenberg','CHEM',2);
1 row created.
SQL> select * from classDB;
RRN NAME DEPARTMENT YEAR
---------- ------------------ ------------------------
--------------------
1001 Adhi CSE 2
1002 Billy CSE 3
1003 Cillian IT 2
1004 Dilli IT 2
1006 Heisenberg CHEM 2
SQL> rollback to A;
Rollback complete.
SQL> select * from classDB;
RRN NAME DEPARTMENT YEAR
---------- ------------------ ------------------------
--------------------
1001 Adhi CSE 2
1002 Billy CSE 3
1003 Cillian IT 2
1004 Dilli IT 2
SQL> commit;
Commit complete.
RESULT:
Exp no:1-C
IMPLEMENTATION OF DQL COMMANDS
AIM:
DESCRIPTION:
SELECT: It is used to retrieve data from the database.
SYNTAX:
SELECT column1,column2 FROM table_name
column1 , column2: names of the fields of the table
table_name: from where we want to apply query
SELECT * FROM table_name;
-- asterisks represent all attributes of the table
CODE & OUTPUT:
SQL> select rrn,name from classDB;
RRN NAME
---------- ------------------
1001 Adhi
1002 Billy
1003 Cillian
1004 Dilli
SQL> select * from classDB;
RRN NAME DEPARTMENT YEAR
---------- ------------------ ------------------------
--------------------
1001 Adhi CSE 2
1002 Billy CSE 3
1003 Cillian IT 2
1004 Dilli IT 2
RESULT:
