5)COMMANDS :
Create Table DEPARTMENT(DEPT_NAME Varchar(20),MANAGER_NAME
Varchar(255));
Create Table EMPLOYEE(EMP_ID int,EMP_NAME Varchar(20),DEPT_NAME
Varchar(255));
Create table student(reg_no int(3) primary key,sname varchar(20));
Create table course(cid int(3) primary key, cname varchar(20),s_id int(3), foreign key
(s_id) references student(reg_no));
INSERT INTO DEPARTMENT VALUES ( "IT", "ROHAN"),( "SALES", "RAHUL"),( "HR",
"TANMAY"),( "FINANCE", "ASHISH"),("MARKETING", "SAMAY");
INSERT INTO EMPLOYEE VALUES (1, "SUMIT", "HR"),(2, "JOEL", "IT"),(3, "BISWA",
"MARKETING"),(4, "VAIBHAV", "IT"),(5, "SAGAR", "SALES");
INSERT INTO STUDENT values(1,’AAA’),(2,’BBB’),(3,’CCC’),(4,’DDD’),(5,’EEE’);
INSERT INTO COURSE VALUES (101,’Intro to JAVA’,3),(102,’Data Science’,1),(103,’AI
& ML’,4), (104,’Python’,3);
Natural Join
SELECT * FROM EMPLOYEE NATURAL JOIN DEPARTMENT;
Equi join
select * from student s join course c where s.reg_no = c.s_id;
Outer join
Left outer join
SELECT * FROM DEPARTMENT d LEFT OUTER JOIN EMPLOYEE e ON
d.DEPT_NAME = e.DEPT_NAME;
Right outer join
SELECT * FROM DEPARTMENT d RIGHT OUTER JOIN EMPLOYEE e ON
d.DEPT_NAME = e.DEPT_NAME;
Full outer join
SELECT * FROM DEPARTMENT d LEFT OUTER JOIN EMPLOYEE e ON
d.DEPT_NAME = e.DEPT_NAME UNION SELECT * FROM DEPARTMENT d
RIGHT OUTER JOIN EMPLOYEE e ON d.DEPT_NAME = e.DEPT_NAME;
6)COMMANDS :
Procedure Implementation
Creating the table ‘product_items’ and displaying the contents
SQL> create table product_items(itemid number(3), actualprice number(5), ordid
number(4), prodid number(4));
Table created.
SQL> insert into product_items values(101, 2000, 500, 201);
1 row created.
SQL> insert into product_items values(102, 3000, 1600, 202);
1 row created.
SQL> insert into product_items values(103, 4000, 600, 202);
1 row created.
SQL> select * from product_items;
ITEMID
ACTUALPRIC
ORDID PRODID
--------- ----------- -------- ---------
101 2000 500 201
102 3000 1600 202
103 4000 600 202
Program for general procedure – selected record’s price is incremented by 500,
executing the procedure created and displaying the updated table
SQL> create procedure itsum(identity number, total number) is price number;
2 null_price exception;
3 begin
4 select actualprice into price from product_items where itemid=identity;
5 if price is null then
6 raise null_price;
7 else
8 update product_items set actualprice=actualprice+total where itemid=identity;
9 end if;
10 exception when null_price then
12 dbms_output.put_line('price is null');
13 end;
14 /
Procedure created.
SQL> exec itsum(101, 500);
PL/SQL procedure successfully
completed.
SQL> select * from product_items;
ITEMID ACTUAL
PRICE
ORDID PRODID
--------- ----------- --------- ---------
101 2500 500 201
102 3000 1600 202
103 4000 600 202
Procedure for ‘in’ parameter – creation, execution
SQL> set serveroutput on;
SQL> create procedure yyy (a IN number) is price number;
2 begin
3 select actualprice into price from product_items where itemid=a;
4 dbms_output.put_line('Actual price is ' || price);
5 if price is null then
6 dbms_output.put_line('price is null');
7 end if;
8 end;
9/
Procedure created.
SQL> exec yyy(103);
Actual price is 4000
PL/SQL procedure successfully completed.
Procedure for ‘out’ parameter – creation, execution
SQL> set serveroutput on;
SQL> create procedure zzz (a in number, b out number) is identity number;
2 begin
3 select ordid into identity from product_items where itemid=a;
4 if identity<1000 then
5 b:=100;
6 end if;
7 end;
8/
Procedure created.
SQL> declare
2 a number;
3 b number;
4 begin
5 zzz(101,b);
6 dbms_output.put_line('The value of b is '|| b);
7 end;
8/
The value of b is 100
PL/SQL procedure successfully completed.
Procedure for ‘inout’ parameter – creation, execution
SQL> create procedure itit ( a in out number) is
2 begin
3 a:=a+1;
4 end;
5/
Procedure created.
SQL> declare
2 a number:=7;
3 begin
4 itit(a);
5 dbms_output.put_line(„The updated value is „||a);
6 end;
7/
The updated value is 8
PL/SQL procedure successfully completed.
Function Implementation
Create the table ‘train’ to be used for functions
SQL>create table train ( tno number(10), tfare number(10));
Table created.
SQL>insert into train values (1001, 550);
1 row created.
SQL>insert into train values (1002, 600);
1 row created.
SQL>select * from train;
TNO TFARE
--------- ------------
1001 550
1002 600
To create the table ‘itempls’
SQL> create table employee (ename varchar2(10), eid number(5), salary number(10));
Table created.
SQL> insert into employee values('xxx',11,10000);
1 row created.
SQL> insert into employee values('yyy',12,10500);
1 row created.
SQL> insert into employee values('zzz',13,15500);
1 row created.
SQL> select * from employee;
ENAME EID SALARY
---------- --------- ---------
xxx 11 10000
yyy 12 10500
zzz 13 15500
Program for function and it’s execution
SQL> create function aaa (trainnumber number) return number is
2 Train function train.tfare % type;
3 begin
4 select tfare into trainfunction from train where tno=trainnumber;
5 return(trainfunction);
6 end;
7/
Function created.
SQL> set serveroutput on;
SQL> declare
2 total number;
3 begin
4 total:=aaa (1001);
5 dbms_output.put_line('Train fare is Rs. '||total);
6 end;
7/
Train fare is Rs.550
PL/SQL procedure successfully completed.
Factorial of a number using function — program and execution
SQL> create function fact (a number) return number is
2 fact number:=1;
3 b number;
4 begin
5 b:=a;
6 while b>0
7 loop
8 fact:=fact*b;
9 b:=b-1;
10 end loop;
11 return(fact);
12 end;
13 /
Function created.
SQL> set serveroutput on;
SQL> declare
2 a number:=7;
3 f number(10);
4 begin
5 f:=fact(a);
6 dbms_output.put_line(„The factorial of the given number is‟||f);
7 end;
8/
The factorial of the given number is 5040
PL/SQL procedure successfully completed.
7)COMMANDS :
DCL COMMANDS
mysql> create database dcl;
Query OK, 1 row affected (0.03 sec)
mysql> use dcl;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dcl |
| user1 |
| user2 |
| user3 |
| world |
+--------------------+
5 rows in set (0.03 sec)
mysql> create user tom;
Query OK, 0 rows affected (0.07 sec)
mysql> create user john;
Query OK, 0 rows affected (0.02 sec)
mysql> create user allison;
Query OK, 0 rows affected (0.03 sec)
mysql> create table student(roll_no int(255),name varchar(100),branch
varchar(100),age int(255));
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> insert into student values(10,'anitha','CSE',14);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(14,'bavani','ECE',15);
Query OK, 1 row affected (0.03 sec)
mysql> insert into student values(16,'vikranth','EEE',16);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(17,'yamini','civil',18);
Query OK, 1 row affected (0.03 sec)
mysql> insert into student values(19,'krishna','mech',20);
Query OK, 1 row affected (0.03 sec)
mysql> select * from student;
+---------+----------+--------+------+
| roll_no | name | branch | age |
+---------+----------+--------+------+
| 10 | anitha | CSE | 14 |
| 14 | bavani | ECE | 15 |
| 17 | yamini | civil | 18 |
| 19 | krishna | mech | 20 |
+---------+----------+--------+------+
5 rows in set (0.00 sec)
mysql> GRANT all on student to allison;
Query OK, 0 rows affected (0.02 sec)
mysql> show grants for allison;
+------------------------------------------------------+
| Grants for allison@% |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `allison`@`%` |
| GRANT ALL PRIVILEGES ON `dcl`.`student` TO `allison`@`%` |
+------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> GRANT select,insert,update on student to john;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for john;
+---------------------------------------------------------------+
| Grants for john@% |
+---------------------------------------------------------------+
| GRANT USAGE ON *.* TO `john`@`%` |
| GRANT SELECT, INSERT, UPDATE ON `dcl`.`student` TO `john`@`%` |
+---------------------------------------------------------------+
2 rows in set (0.02 sec)
mysql> GRANT select,insert,update,delete on student to tom;
Query OK, 0 rows affected (0.03 sec)
mysql> show grants for tom;
+-----------------------------------------------------------------------+
| Grants for tom@% |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `tom`@`%` |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `dcl`.`student` TO ` tom `@`%` |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> REVOKE select, update on student from john;
Query OK, 0 rows affected (0.03 sec)
mysql> show grants for john;
+----------------------------------+
| Grants for john@% |
+----------------------------------+
| GRANT USAGE ON *.* TO `john`@`%` |
| GRANT INSERT ON `dcl`.`student` TO `john`@`%` |
+----------------------------------+
1 row in set (0.00 sec)
mysql> REVOKE delete on student from tom;
Query OK, 0 rows affected (0.02 sec)
mysql> show grants for tom;
+----------------------------------+
| Grants for tom@% |
+----------------------------------+
| GRANT USAGE ON *.* TO ` tom`@`%` |
| GRANT SELECT, INSERT, UPDATE ON `dcl`.`student` TO ` tom `@`%` |
+----------------------------------+
1 row in set (0.00 sec)
mysql> REVOKE all on student from allison;
Query OK, 0 rows affected (0.03 sec)
mysql> show grants for allison;
+---------------------------------+
| Grants for allison@% |
+---------------------------------+
| GRANT USAGE ON *.* TO ` allison`@`%` |
+---------------------------------+
1 row in set (0.00 sec)
TCL COMMANDS
mysql> drop database account_details;
Query OK, 1 row affected (0.02 sec)
mysql> create database account_details;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| account_details |
| clear |
| dbms |
| dcl |
| hello |
| information_schema |
| joinoperation |
| menagerie |
| mysql |
| new_schema |
| performance_schema |
| sakila |
| samp |
| sample |
| sys |
| tcl |
| user1 |
| user2 |
| user3 |
| world |
+--------------------+
20 rows in set (0.00 sec)
mysql> use account_details;
Database changed
mysql> create table account_details(account_no int(50),customer_name
varchar(100),address varchar(100),phone_no int(50),amount decimal(50));
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> insert into account_details values(101,'raj','kk nagar',56789,20000);
Query OK, 1 row affected (0.01 sec)
mysql> insert into account_details values(102,'ravi','mgr nagar',59713,10000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into account_details values(103,'selvam','royal nagar',89436,17500);
Query OK, 1 row affected (0.00 sec)
mysql> insert into account_details values(104,'ananthi','transport
nagar',94682,35000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into account_details values(105,'jansi','padma nagar',67823,10900);
Query OK, 1 row affected (0.00 sec)
mysql> select*from account_details;
+------------+---------------+-----------------+----------+--------+
| account_no | customer_name | address | phone_no | amount |
+------------+---------------+-----------------+----------+--------+
| 101 | raj | kk nagar | 56789 | 20000 |
| 102 | ravi | mgr nagar | 59713 | 10000 |
| 103 | selvam | royal nagar | 89436 | 17500 |
| 104 | ananthi | transport nagar | 94682 | 35000 |
| 105 | jansi | padma nagar | 67823 | 10900 |
+------------+---------------+-----------------+----------+--------+
5 rows in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into account_details values(106,'rajaboopathi','jk nagar',90807,28000);
Query OK, 1 row affected (0.00 sec)
mysql> savepoint stu1;
Query OK, 0 rows affected (0.00 sec)
mysql> select*from account_details;
+------------+---------------+-----------------+----------+--------+
| account_no | customer_name | address | phone_no | amount |
+------------+---------------+-----------------+----------+--------+
| 101 | raj | kk nagar | 56789 | 20000 |
| 102 | ravi | mgr nagar | 59713 | 10000 |
| 103 | selvam | royal nagar | 89436 | 17500 |
| 104 | ananthi | transport nagar | 94682 | 35000 |
| 105 | jansi | padma nagar | 67823 | 10900 |
| 106 | rajaboopathi | jk nagar | 90807 | 28000 |
+------------+---------------+-----------------+----------+--------+
6 rows in set (0.00 sec)
mysql> update account_details set amount=19000 where account_no=104;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> savepoint stu2;
Query OK, 0 rows affected (0.00 sec)
mysql> select*from account_details;
+------------+---------------+-----------------+----------+--------+
| account_no | customer_name | address | phone_no | amount |
+------------+---------------+-----------------+----------+--------+
| 101 | raj | kk nagar | 56789 | 20000 |
| 102 | ravi | mgr nagar | 59713 | 10000 |
| 103 | selvam | royal nagar | 89436 | 17500 |
| 104 | ananthi | transport nagar | 94682 | 19000 |
| 105 | jansi | padma nagar | 67823 | 10900 |
| 106 | rajaboopathi | jk nagar | 90807 | 28000 |
+------------+---------------+-----------------+----------+--------+
6 rows in set (0.00 sec)
mysql> delete from account_details where account_no=105;
Query OK, 1 row affected (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from account_details where account_no=105;
Query OK, 0 rows affected (0.00 sec)
mysql> savepoint stu3;
Query OK, 0 rows affected (0.00 sec)
mysql> select*from account_details;
+------------+---------------+-----------------+----------+--------+
| account_no | customer_name | address | phone_no | amount |
+------------+---------------+-----------------+----------+--------+
| 101 | raj | kk nagar | 56789 | 20000 |
| 102 | ravi | mgr nagar | 59713 | 10000 |
| 103 | selvam | royal nagar | 89436 | 17500 |
| 104 | ananthi | transport nagar | 94682 | 19000 |
| 106 | rajaboopathi | jk nagar | 90807 | 28000 |
+------------+---------------+-----------------+----------+--------+
5 rows in set (0.00 sec)
mysql> ROLLBACK TO SAVEPOINT stu3;
Query OK, 0 rows affected (0.00 sec)
mysql> select*from account_details;
+------------+---------------+-----------------+----------+--------+
| account_no | customer_name | address | phone_no | amount |
+------------+---------------+-----------------+----------+--------+
| 101 | raj | kk nagar | 56789 | 20000 |
| 102 | ravi | mgr nagar | 59713 | 10000 |
| 103 | selvam | royal nagar | 89436 | 17500 |
| 104 | ananthi | transport nagar | 94682 | 19000 |
| 106 | rajaboopathi | jk nagar | 90807 | 28000 |
+------------+---------------+-----------------+----------+--------+
5 rows in set (0.00 sec)
8)COMMANDS:
To create a simple trigger that does not allow insert update and delete
operations on the table
SQL> create trigger trigg1 before insert or update or delete on employee for each row
2 begin
3 raise_application_error(-20010,'You cannot do manipulation');
4 end;
5/
Trigger created.
SQL> insert into employee values('aaa',14,34000);
insert into employee values('aaa',14,34000)
* ERROR at line 1:
ORA-20010: You cannot do manipulation
ORA-06512: at "STUDENT.TRIGG1", line 2
ORA-04088: error during execution of trigger 'STUDENT.TRIGG1'
SQL> delete from employee where ename='xxx';
delete from employee where ename='xxx'
ERROR at line 1:
ORA-20010: You cannot do manipulation
ORA-06512: at "STUDENT.TRIGG1", line 2
ORA-04088: error during execution of trigger 'STUDENT.TRIGG1'
SQL> update employee set eid=15 where ename='yyy';
update employee set eid=15 where ename='yyy'
ERROR at line 1:
ORA-20010: You cannot do manipulation
ORA-06512: at "STUDENT.TRIGG1", line 2
ORA-04088: error during execution of trigger 'STUDENT.TRIGG1'
To drop the created trigger
SQL> drop trigger trigg1;
Trigger dropped
To create a trigger that raises an user defined error message and does not allow
updation and insertion
SQL> create trigger trigg2 before insert or update of salary on employee for each row
2 declare
3 triggsal employee.salary%type;
4 begin
5 select salary into triggsal from employee where eid=12;
6 if(:new.salary>triggsal or :new.salary<triggsal) then
7 raise_application_error(-20100,'Salary has not been changed');
8 end if;
9 end;
10 /
Trigger created.
SQL> insert into employee values ('bbb',16,45000);
insert into employee values ('bbb',16,45000)
ERROR at line 1:
ORA-04098: trigger 'STUDENT.TRIGG2' is invalid and failed re-validation
SQL> update employee set eid=18 where ename='zzz';
update employee set eid=18 where ename='zzz'
ERROR at line 1:
ORA-04298: trigger 'STUDENT.ITTRIGGS' is invalid and failed re-validation
