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;

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;

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);

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’

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.

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);

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 |

| 104 | ananthi | transport nagar | 94682 | 19000 |

| 106 | rajaboopathi | jk nagar | 90807 | 28000 |

+------------+---------------+-----------------+----------+--------+

5 rows in set (0.00 sec)

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