Scribd
Upload
98 views10 pages

Dbms Worksheet-3: Name: - Praduman Kumar Section: - 20ITB5 UID: - 20BCS9446

The document provides examples of SQL queries on database tables. It includes: 1) Creating department and employee tables with sample data inserts and updates. 2) Queries to list records by department number, update a record, delete a column, and order results. 3) Additional queries on the employee table including descriptions, listings, filtering by salary range, and filtering by manager or department. 4) A PL/SQL program to print a variable's value inside and outside a loop using the EXIT statement. 5) A PL/SQL program to calculate and print the factorial of a given number.

Uploaded by

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

Dbms Worksheet-3: Name: - Praduman Kumar Section: - 20ITB5 UID: - 20BCS9446

The document provides examples of SQL queries on database tables. It includes: 1) Creating department and employee tables with sample data inserts and updates. 2) Queries to list records by department number, update a record, delete a column, and order results. 3) Additional queries on the employee table including descriptions, listings, filtering by salary range, and filtering by manager or department. 4) A PL/SQL program to print a variable's value inside and outside a loop using the EXIT statement. 5) A PL/SQL program to calculate and print the factorial of a given number.

Uploaded by

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

DBMS WORKSHEET-3

Name: - Praduman kumar


Section: - 20ITB5
UID: - 20BCS9446

1. Create department table with the following structure.


Name Type
Deptno Number
Deptname Varchar2(10)
location Varchar2(10)
Add column designation to the department table.
Insert values into the table.
List the records of dept table grouped by deptno.
Update the record where deptno is 9.
Delete any column data from the table.
List all the records of database in descending order.

Code:-
create table dept(
deptno number(2,0),
dname varchar2(14),
loc varchar2(13),
constraint pk_dept primary key (deptno)
)

insert into DEPT (DEPTNO, DNAME, LOC)


values(10, 'ACCOUNTING', 'NEW YORK')

insert into dept


values(20, 'RESEARCH', 'DALLAS')
UPDATE dept
SET dname = 'Account', Location= 'Frankfurt'
WHERE deptno = 9

DELETE FROM dept WHERE dname= 'Account’

SELECT column-list|* FROM table-name ORDER BY DESC;

Screenshot:-
2. Create table EMP with the following description :
Name Type
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(3)
AGE NUMBER(3)
ESAL NUMBER
1. Get the description EMP table.
2. List all employees details.
3. List all employee names and their salaries, whose salary lies
between 1500/- and 3500/- both inclusive.
4. List all employee names and their and their manager whose
manager is 7902 or 7566 0r 7789.
5. List all employees who belongs to the department 10 or 20

Code:-

create table emp(


empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0),
constraint pk_emp primary key (empno),
constraint fk_deptno foreign key (deptno) references dept (deptno)
)
1. Get the description of EMP table.

SQL>desc emp;

RESULT:-
Name Null? Type

--------------------------------- --------------------- ------------


---------------
EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(9)
MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(3)

AGE NUMBER(3)
ESAL NUMBER(10)

2. List all employee details.

SQL>select * from emp;

RESULT:-

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO AGE ESAL

-------- ---------- --------- ---------- --------- ---------- -------


--- ---------- ---------- -----------------

7369 SMITH CLERK 7902 17-DEC-80 800 0 20 25 0

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 25 0

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 25 0

7566 JONES MANAGER 7839 02-APR-81 2975 500 20 25 0


7698 BLAKE MANAGER 7839 01-MAY-81 2850 1400 30 25 0
3. List all employee names and their salaries, whose salary lies
between 1500/- and 3500/- both inclusive.

INPUT:-
SQL>select ename from emp where sal between 1500 and 3500;

RESULT:-

ENAME
----------

ALLEN

JONES

BLAKE

CLARK

SCOTT
TURNER

FORD

russel

greg

9 rows selected.

4. List all employee names and their and their manager whose
manager is 7902 or 7566 0r 7789.

INPUT SQL>select ename from emp where mgr in(7602,7566,7789);

RESULT:-

ENAME

-------
SCOTT
FORD

5. List all employees who belongs to the department 10 or 20.

INPUT SQL>select ename from emp where deptno in (10,20);

RESULT:-

ENAME

---------

SMITH

JONES
CLARK
SCOTT

KING

ADAMS

FORD

MILLER

8 rows selected.

3. Write a program in PL/SQL to print the value of a variable inside


and outside a loop using LOOP EXIT statement.

Code:-

DECLARE
n NUMBER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE ('The value of n inside the loop is: ' ||
TO_CHAR(n));
n := n + 1;
IF n > 5 THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('The value of n outside the loop is: ' ||
TO_CHAR(n));
END;
/

Screenshot:-
Output:-

4. Write a program in PL/SQL to print factorial of a number

Here, first, we take three variables num, fact, and temp and assign the
value in num variable (i.e which number factorial we want).
And then after we assign fact variable to 1.
Examples:
Input : 4
Output : 24

Input : 6
Output : 720

Code:-

declare

-- declare variable num , fact


-- and temp of datatype number
num number := 6;
fact number := 1;
temp number;

begin

temp :=num;

-- here we check condition


-- with the help of while loop
while( temp>0 )
loop
fact := fact*temp;
temp := temp-1;

end loop;

dbms_output.put_line('factorial of '|| num || ' is ' || fact);

end;

-- Program End

Screenshot:-
Output :-

factorial of 6 is 720.

You might also like