Scribd
Upload
14 views8 pages

Input and Outputs of PL SQL

Just a random pl sql document

Uploaded by

haamidatksa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
14 views8 pages

Input and Outputs of PL SQL

Just a random pl sql document

Uploaded by

haamidatksa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 8

====================== DBMS Assignment 6 Inputs/Outputs

============================

<<<<<<<<<<<<<<<<<<<<<<<<<<< Creating a Database company


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

mysql> create database company;


Query OK, 1 row affected (0.01 sec)

mysql> use company;


Database changed

<<<<<<<<<<<<<<<<<<<<<<<<<<<< Creating the table dept


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

mysql> CREATE TABLE dept(


-> DeptNo INT PRIMARY KEY,
-> Dname VARCHAR(30),
-> Loc VARCHAR(30));
Query OK, 0 rows affected (0.03 sec)

mysql> DESC dept;


+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| DeptNo | int | NO | PRI | NULL | |
| Dname | varchar(30) | YES | | NULL | |
| Loc | varchar(30) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

<<<<<<<<<<<<<<<<<<<<<<<<<<<< Creating the table emp


>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

mysql> CREATE TABLE emp(


-> EmpNo INT PRIMARY KEY,
-> Ename VARCHAR(30) NOT NULL,
-> Job CHAR(15),
-> MGR INT,
-> HireDate DATE,
-> Sal INT,
-> Comm INT,
-> DeptNo INT,
-> FOREIGN KEY (DeptNo) REFERENCES dept(DeptNo));
Query OK, 0 rows affected (0.05 sec)

mysql> DESC emp;


+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EmpNo | int | NO | PRI | NULL | |
| Ename | varchar(30) | NO | | NULL | |
| Job | char(15) | YES | | NULL | |
| MGR | int | YES | | NULL | |
| HireDate | date | YES | | NULL | |
| Sal | int | YES | | NULL | |
| Comm | int | YES | | NULL | |
| DeptNo | int | YES | MUL | NULL | |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)

<<<<<<<<<<<< Inserting values into emp (results in an error because we haven't


inserted values for dept which has the foreign key DeptNo) >>>>>>>>>>>>>>>>>>>>>

mysql> INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,320


,20);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint
fails (`company`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`DeptNo`) REFERENCES
`dept` (`DeptNo`))
mysql> SELECT* FROM emp;
Empty set (0.00 sec)

<<<<<<<<<<<<<<< Inserting values into dept >>>>>>>>>>>>>>>>>>>>

mysql> INSERT INTO dept VALUES(10,'Accounting','New York');


Query OK, 1 row affected (0.01 sec)

...
...
...

mysql> SELECT* FROM dept;


+--------+------------+----------+
| DeptNo | Dname | Loc |
+--------+------------+----------+
| 10 | Accounting | New York |
| 20 | Research | Dallas |
| 30 | Sales | Chicago |
| 40 | Operations | Boston |
+--------+------------+----------+
4 rows in set (0.00 sec)

<<<<<<<<<<<<<<< Inserting values into emp table (Won't result in an error this
time) >>>>>>>>>>>>>>>

mysql> INSERT INTO emp VALUES(7369,'Smith','Clerk',7902,'1980-12-17',800,300


,20);
Query OK, 1 row affected (0.00 sec)

...
...
...

mysql> SELECT* FROM emp;


+-------+--------+-----------+------+------------+------+------+--------+
| EmpNo | Ename | Job | MGR | HireDate | Sal | Comm | DeptNo |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | Smith | Clerk | 7902 | 1980-12-17 | 800 | 300 | 20 |
| 7499 | Allen | Salesman | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | Ward | Salesman | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | Jones | Manager | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | Martin | Salesman | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | Blake | Manager | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | Clark | Manager | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | Scott | Analyst | 7566 | 1987-04-19 | 3000 | NULL | 20 |
| 7839 | King | President | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | Turner | Salesman | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | Adams | Clerk | 7788 | 1987-05-23 | 1100 | NULL | 20 |
| 7900 | James | Clerk | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | Ford | Analyst | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | Miller | Clerk | 7782 | 1982-01-23 | 1300 | NULL | 10 |
+-------+--------+-----------+------+------------+------+------+--------+
14 rows in set (0.00 sec)

mysql> show tables;


+-------------------+
| Tables_in_company |
+-------------------+
| dept |
| emp |
+-------------------+
2 rows in set (0.00 sec)

<<<<<<<<<<<<<<<<<<<<< Creating a Procedure Proc1 >>>>>>>>>>>>>>>>>>>>>>>>>

mysql> delimiter //
mysql> create procedure Proc1()
-> begin
-> select EmpNo,Ename,Job from emp;
-> end; //
Query OK, 0 rows affected (0.06 sec)

mysql> call Proc1(); //


+-------+--------+-----------+
| EmpNo | Ename | Job |
+-------+--------+-----------+
| 7369 | Smith | Clerk |
| 7499 | Allen | Salesman |
| 7521 | Ward | Salesman |
| 7566 | Jones | Manager |
| 7654 | Martin | Salesman |
| 7698 | Blake | Manager |
| 7782 | Clark | Manager |
| 7788 | Scott | Analyst |
| 7839 | King | President |
| 7844 | Turner | Salesman |
| 7876 | Adams | Clerk |
| 7900 | James | Clerk |
| 7902 | Ford | Analyst |
| 7934 | Miller | Clerk |
+-------+--------+-----------+
14 rows in set (0.01 sec)

Query OK, 0 rows affected (0.03 sec)


<<<<<<<<<<<<<<<<<<<<<< Creating the Table deptsal >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

mysql> create table deptsal AS


-> SELECT DeptNo,0 AS Total_Salary
-> FROM Dept;
-> //
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0

mysql> show tables; //


+-------------------+
| Tables_in_company |
+-------------------+
| dept |
| deptsal |
| emp |
+-------------------+
3 rows in set (0.00 sec)

mysql> desc deptsal; //


+--------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------+------+-----+---------+-------+
| DeptNo | int | NO | | NULL | |
| Total_Salary | int | NO | | 0 | |
+--------------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)

<<<<<<<<<<<<<<<<<<<<<<< Creating the Procedure totalsalary


>>>>>>>>>>>>>>>>>>>>>>>>>

mysql> create procedure totalsalary(IN param1 int)


-> begin
-> update deptsal
-> set Total_Salary = (select sum(Sal) from emp where DeptNo=param1)

-> where DeptNo=param1;


-> end//
Query OK, 0 rows affected (0.01 sec)

mysql> call totalsalary(10); //


Query OK, 1 row affected (0.01 sec)

mysql> call totalsalary(20); //


Query OK, 1 row affected (0.01 sec)

mysql> call totalsalary(30); //


Query OK, 1 row affected (0.01 sec)

mysql> call totalsalary(40); //


ERROR 1048 (23000): Column 'Total_Salary' cannot be null
mysql> select* from deptsal; //
+--------+--------------+
| DeptNo | Total_Salary |
+--------+--------------+
| 10 | 8750 |
| 20 | 10875 |
| 30 | 10577 |
| 40 | 0 |
+--------+--------------+
4 rows in set (0.00 sec)

<<<<<<<<<<<<<<<<<<<<<<< Creating the Procedure maxsal >>>>>>>>>>>>>>>>>>>>>>>>>

mysql> create procedure maxsal(out high_sal int)


-> begin
-> select max(Sal) into high_sal from emp;
-> end//
Query OK, 0 rows affected (0.01 sec)

mysql> call maxsal(@M); //


Query OK, 1 row affected (0.01 sec)

mysql> drop procedure totalsal;


-> //
Query OK, 0 rows affected (0.02 sec)

mysql> drop procedure totalsalr; //


Query OK, 0 rows affected (0.01 sec)

mysql> select @high_sal; //


+----------------------+
| @high_sal |
+----------------------+
| NULL |
+----------------------+
1 row in set (0.00 sec)

mysql> select @M; //


+------+
| @M |
+------+
| 5000 |
+------+
1 row in set (0.00 sec)

<<<<<<<<<<<<<<<<<<<<<<< Creating the Procedure counter >>>>>>>>>>>>>>>>>>>>>>>>>

mysql> create procedure counter(inout count int, in increment int)


-> begin
-> SET count=count+increment;
-> end//
Query OK, 0 rows affected (0.01 sec)

mysql> set @c=0 //


Query OK, 0 rows affected (0.00 sec)

mysql> call COUNTER(@C,1)


-> //
Query OK, 0 rows affected (0.00 sec)

mysql> select @C as count //


+-------+
| count |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)

mysql> call COUNTER(@C,1)//


Query OK, 0 rows affected (0.00 sec)

mysql> select @C as count; //


+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set (0.00 sec)

///////////////////////////////////////////////////////////////////////////////////
/////

PART B

<<<<<<<<<<<<<<<<<<<<<<<< Creating a Database commerce >>>>>>>>>>>>>>>>>>>>>>>>>>>>>

mysql> create database commerce;


Query OK, 1 row affected (0.03 sec)

mysql> use commerce;


Database changed

<<<<<<<<<<<<<<<<<<<<<<<<< Creating a Table employee >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

mysql> CREATE TABLE EMPLOYEE(


-> emp_id int PRIMARY KEY,
-> first_name varchar(50) NOT NULL,
-> last_name varchar(50) NOT NULL,
-> hiredate DATE NOT NULL,
-> salary int NOT NULL
-> );
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;


+--------------------+
| Tables_in_commerce |
+--------------------+
| employee |
+--------------------+
1 row in set (0.00 sec)

<<<<<<<<<<<<<<<<<<<<<<<<<<< Inserting Values into Employee


>>>>>>>>>>>>>>>>>>>>>>>>>>
mysql> insert into employee values(7369,'Mohammad','Ali','1980-12-17',100000);
Query OK, 1 row affected (0.02 sec)

mysql> insert into employee values(7499,'Haamid','Ashraf','2023-02-20',200000),


(7325,'Tom','Hardy','2022-03-02',700000),(7521,'Fahim','Abullais','2021-02-
22',400000),(7566,'Mariyam','Hassen','2024-04-02',600000),
(7346,'Fatima','Abdulla','2020-04-02',800000);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> select* from employee;


+--------+------------+-----------+------------+--------+
| emp_id | first_name | last_name | hiredate | salary |
+--------+------------+-----------+------------+--------+
| 7325 | Tom | Hardy | 2022-03-02 | 700000 |
| 7346 | Fatima | Abdulla | 2020-04-02 | 800000 |
| 7369 | Mohammad | Ali | 1980-12-17 | 100000 |
| 7499 | Haamid | Ashraf | 2023-02-20 | 200000 |
| 7521 | Fahim | Abullais | 2021-02-22 | 400000 |
| 7566 | Mariyam | Hassen | 2024-04-02 | 600000 |
+--------+------------+-----------+------------+--------+
6 rows in set (0.00 sec)

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Creating a Procedure raisesal


>>>>>>>>>>>>>>>>>>>>>>>>>

mysql> CREATE PROCEDURE raisesal(IN id INT)


-> BEGIN
-> DECLARE v_exp INT;
-> DECLARE v_curr_sal DECIMAL(10,2);
-> SELECT DATEDIFF(CURDATE(),HIREDATE)/365 INTO v_exp
-> FROM employee
-> WHERE emp_id=id;
-> IF v_exp < 2 THEN
-> UPDATE Employee
-> SET salary=salary*1.05
-> WHERE emp_id=id;
-> ELSEIF v_exp BETWEEN 2 AND 5 THEN
-> UPDATE Employee
-> SET salary=salary*1.07
-> WHERE emp_id=id;
-> ELSE
-> UPDATE Employee
-> SET salary=salary*1.10
-> WHERE emp_id=id;
-> END IF;
-> END //
Query OK, 0 rows affected (0.01 sec)

mysql> call raisesal(7325)//


Query OK, 1 row affected (0.01 sec)

mysql> delimiter ;
mysql> select* from employee;
+--------+------------+-----------+------------+--------+
| emp_id | first_name | last_name | hiredate | salary |
+--------+------------+-----------+------------+--------+
| 7325 | Tom | Hardy | 2022-03-02 | 749000 |
| 7346 | Fatima | Abdulla | 2020-04-02 | 800000 |
| 7369 | Mohammad | Ali | 1980-12-17 | 100000 |
| 7499 | Haamid | Ashraf | 2023-02-20 | 200000 |
| 7521 | Fahim | Abullais | 2021-02-22 | 400000 |
| 7566 | Mariyam | Hassen | 2024-04-02 | 600000 |
+--------+------------+-----------+------------+--------+
6 rows in set (0.00 sec)

You might also like