PL/SQL LAB EXERCISES:

 RDBMS QUESTIONS:

DDL &DML COMMANDS:

1)(a) Create a table called Customer table 

 Name Type 

 Cust_name Varchar2(20) 

 Cust_street Varchar2(20) 

 Cust_city Varchar2(20) 

 (i) Insert records into the table. 

 (ii) Add salary column to the table. 

 (iii) Alter the table column domain. 

 (iv) Drop salary column of the customer table. 

 (v) Delete the rows of customer table whose cust_city is 

“New York”.

2)

(a) Create a table called Stock table

 Name Type

 Sid Number

 Sname Varchar2(20)

 Rating Varchar2(20)

 (i) Add column age to the Stock table.

 (ii) Insert values into the Stock table.

 (iii) Delete the row with Rating 8.

 (iv) Update the column details of Stock.

 (v) Insert null values into the table.

3)

(a) Create a table called Branch table.

 Name Type

 Branch name Varchar2(20)

 Branch_city Varchar2(20)

 Asserts Number

 (i) Increase the size of data type for Asserts to the

branch

 (ii) Add and drop a column to the Branch table

 (iii) Insert values to the table.

 (iv) Update the Branch_name column

 (v) Delete any two columns from the table

4)

Write a program for student database and do the following

operations.

 (i) Modification

 (ii) Deletion

 (iii) Aggregate functions count(), sum(), avg(), max(),min()

5)

(a) Consider the following tables namely “DEPARTMENTS”

and “EMPLOYEES” Their schemas are as follows.

 Departments (dept _no, dept_ name, dept_location);

 Employees (emp_id, emp_name, emp_salary, dept_no);

 (i) Develop a query to use all DDL commands and

constraints.

 (ii) Develop a query to use all DML commands and

constraints.

 (iii) Generate a report of employees who joins after

1-jan-2020.

6)

(a) Create a table called Employee with the following

structure.

 Empno (Number), Ename (Varchar2(20)), Job

(Varchar2(20) ), Sal (Number)

 (i) Add a column DoJ (DATE) with the Employee table.

 (ii) Insert any five records into the table.

 (iii) Update the column details of job

 (iv) Rename the column Sal as Emp_Salary of Employ

table using alter command.

 (v) Delete the employee whose empno is 101.

7)

(a) Create department table with the following structure.

 Deptno (Number), Deptname (Varchar2(20)), location

(Varchar2(20))

 (i) Add column designation to the department table.

 (ii) Insert values into the table.

 (iii) List the records of emp table grouped by deptno.

 (iv) Update the record where deptno is 5.

 (v) Delete any column data from the table

8)

Develop “Employee” details table.

 (i) Count the number of employees in department 20

 (ii) Find the minimum salary earned by clerk.

 (iii) Find minimum, maximum, average salary of all employees.

 (iv) List the minimum and maximum salaries for each job type.

 (v) List the employee names in descending order.

 (vi) List the employee id, names in ascending order by

empid.

9)

Create an employee data base and divided into certain the

database into certain departments and each department

consists of employees. The following two tables describes

the schemes.

 Dept_table (deptno, dname, loc)

 Emp_table (empno, ename, job, mgr, hiredate, sal, comm,

deptno)

 (i) Update the employee salary by 15%, whose

experience is greater than 10 years.

 (ii) Delete the employees, who completed 30 years of

service.

 (iii) Display the manager who is having maximum

number of employees working under him?

 (iv) Create a view, which contain employee names and

their manager.

 Generate a report of employees who gets more than

20000 salary

10)

Create Employee Database with Employee_Personal (ID,

NAME, DOJ, AGE, ADDRESS, DEPARTMENT,

DESIGNATION) and Employee Salary (ID, NAME,

SALARY, BP, DA, HRA, CCA, GROSS_PAY, NET_PAY).

Use sql sub queries to updates the SALARY by 10 times in

the EMPLOYEE_Salary table for all employee whose AGE

is greater than or equal to 29.

CREATE TABLE:

Create the tables


CREATE TABLE Employee_Personal (
    ID INT PRIMARY KEY,
    NAME VARCHAR(100),
    DOJ DATE,
    AGE INT,
    ADDRESS VARCHAR(255),
    DEPARTMENT VARCHAR(50),
    DESIGNATION VARCHAR(50)
);

CREATE TABLE Employee_Salary (
    ID INT PRIMARY KEY,
    NAME VARCHAR(100),
    SALARY DECIMAL(10,2),
    BP DECIMAL(10,2),
    DA DECIMAL(10,2),
    HRA DECIMAL(10,2),
    CCA DECIMAL(10,2),
    GROSS_PAY DECIMAL(10,2),
    NET_PAY DECIMAL(10,2)
);


insert:

SQL>INSERT INTO Employee_Personal (ID, NAME, DOJ, AGE, ADDRESS, DEPARTMENT, DESIGNATION)

VALUES 

(1, 'Alice', '2020-01-15', 30, '123 Main St', 'IT', 'Developer'),

(2, 'Bob', '2019-03-10', 28, '456 Elm St', 'HR', 'Manager');



SQL>INSERT INTO Employee_Salary (ID, NAME, SALARY, BP, DA, HRA, CCA, GROSS_PAY, NET_PAY)

VALUES 

(1, 'Alice', 50000, 50000, 0, 0, 0, 0, 0),

(2, 'Bob', 40000, 40000, 0, 0, 0, 0, 0);


-- Update the salary components for all employees

SQL>UPDATE Employee_Salary

SET 

    DA = BP * 0.20,

    HRA = BP * 0.10,

    CCA = BP * 0.05,

    GROSS_PAY = BP + (BP * 0.20) + (BP * 0.10) + (BP * 0.05),

    NET_PAY = (BP + (BP * 0.20) + (BP * 0.10) + (BP * 0.05)) * 0.90;

Update SALARY Using Subquerysql

SQL>UPDATE Employee_Salary

SET SALARY = SALARY * 10

WHERE ID IN (

    SELECT ID

    FROM Employee_Personal

    WHERE AGE >= 29

);


11)(a) Create a Student Database with the following tables. 

 STUDENT_PERSONAL_INFO with fields (NAME, 

ROLL_NO, ADDRESS, PHONE_NO) 

 STUDENT_CLASS_INFO with NAME, ROLL_NO, 

SECTION. 

 Use subqueries to display NAME, PHONE_NO of the 

Student Database whose section is “A”.

answer:

Step 1: Create Tables

SQL>
CREATE TABLE STUDENT_PERSONAL_INFO (
    NAME VARCHAR(100),
    ROLL_NO INT PRIMARY KEY,
    ADDRESS VARCHAR(255),
    PHONE_NO VARCHAR(15)
);

-- Table: STUDENT_CLASS_INFO
CREATE TABLE STUDENT_CLASS_INFO (
    NAME VARCHAR(100),
    ROLL_NO INT PRIMARY KEY,
    SECTION CHAR(1)
);

✅ Step 2: Sample Data (Optional for Testing)


SQL>
INSERT INTO STUDENT_PERSONAL_INFO (NAME, ROLL_NO, ADDRESS, PHONE_NO)
VALUES
('Alice', 101, '123 Maple St', '555-1234'),
('Bob', 102, '456 Oak St', '555-5678'),
('Charlie', 103, '789 Pine St', '555-9012');

SQL>
INSERT INTO STUDENT_CLASS_INFO (NAME, ROLL_NO, SECTION)
VALUES
('Alice', 101, 'A'),
('Bob', 102, 'B'),
('Charlie', 103, 'A');

✅ Step 3: Query using Subquery

SELECT NAME, PHONE_NO
FROM STUDENT_PERSONAL_INFO
WHERE ROLL_NO IN (
    SELECT ROLL_NO
    FROM STUDENT_CLASS_INFO
    WHERE SECTION = 'A'
);

PL/SQL:

1)Write pl/sql code using Trigger to salary with more than old salary.

2)Write a PL/SQL block to handle the following BUILT-IN EXCEPTIONS.

3)Write PL/SQL code in Function for Factorial number and Prime number.

4)Write a PL/SQL program to demonstrate Packages

5)Write a PL/SQL program to demonstrate Procedure and Functions.

6)Write a PL/SQL program to demonstrate Exceptions.

7)Write PL/SQL queries to create Triggers.

8)Write PL/SQL Programs in Cursors using Loops.

9)Write PL/SQL queries to create Triggers.

10)Write a PL/SQL program to demonstrate Functions.

11)Use appropriate Visual programming tools like oracle forms and reports, visual basic etc to create user interface screens and generate reports for library management.

Comments

Post a Comment

Popular posts from this blog

RDBMS LAB EXERCISES WITH ANSWER

  EXERCISE:1 Create a table called Customer table   Name Type   Cust_name Varchar2(20)   Cust_street Varchar2(20)   Cust_city Varchar2(20)   (i) Insert records into the table.   (ii) Add salary column to the table.   (iii) Alter the table column domain.   (iv) Drop salary column of the customer table.   (v) Delete the rows of customer table whose cust_city is  “New York”. 💠 Step-by-step SQL Queries: ✅ (1) Create the Customer table SQL> CREATE TABLE Customer (     Cust_name VARCHAR2(20),     Cust_street VARCHAR2(20),     Cust_city VARCHAR2(20) ); ✅ (2) Insert records into the table SQL> INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Alice', '1st Avenue', 'New York'); INSERT INTO Customer (Cust_name, Cust_street, Cust_city) VALUES ('Bob', '2nd Street', 'Los Angele...
Read more

DATA STRUCTURES-UNIT IV

Image
  UNIT IV   INTRODUCTION TO ALGORITHMS: Definition   of   Algorithms-   Overview   and   importance   of   algorithms-   pseudocode conventions, Asymptotic notations, practical complexities. Graph   Applications:   Breadth   first   traversal   –   Depth   first   traversal-   -Single   source shortest path – Minimal spanning trees – prim’s and kruskal’s algorithms   INTRODUCTION TO ALGORITHMS   4.1)Definition   of   Algorithms   ●       An algorithm is a well-defined, step-by-step procedure or set of rules used to solve a particular problem or perform a specific task. ●       Algorithms are widely applied in various fields like mathematics, computer science, and data processing. 4.2)Overview   and   importance   of   algorithms ●       In data...
Read more

DATA STRUCTURES-UNIT V

Image
                                                                                                      UNIT V DIVIDE AND CONQUER ALGORITHMS: General Method – Binary Search- Quick Sort- Merge Sort. BACKTRACKING: General method, 8 Queens, Graph coloring, Hamiltonian cycle. DIVIDE AND CONQUER ALGORITHMS: General Method Divide and Conquer   Algorithm  is a problem-solving technique used to solve problems by dividing the main problem into subproblems, solving them individually and then merging them to find solution to the original problem. Divide and Conquer is mainly useful when we divide a problem into independent subproblems. If we have overlapping subproblems, then w...
Read more