Scribd
Upload
31 views26 pages

Experiment No. 1: Problem 1

The document outlines a series of experiments involving SQL and PL/SQL programming tasks. It includes creating database schemas for customer-sale and employee-pay scenarios, performing various SQL operations, and developing PL/SQL programs and procedures for tasks such as finding the largest number, generating prime numbers, and updating employee salaries. Additionally, it involves creating triggers and views to manage and manipulate data effectively.

Uploaded by

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

Experiment No. 1: Problem 1

The document outlines a series of experiments involving SQL and PL/SQL programming tasks. It includes creating database schemas for customer-sale and employee-pay scenarios, performing various SQL operations, and developing PL/SQL programs and procedures for tasks such as finding the largest number, generating prime numbers, and updating employee salaries. Additionally, it involves creating triggers and views to manage and manipulate data effectively.

Uploaded by

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

Experiment No.

Problem 1 :
Build the following database schemas and perform the manipulation operations on these
schemas using SQL DDL, DML, TCL and DCL commands.

(i) Database Schema for a customer-sale scenario

Customer(Custid : integer, cust_name: string)

Item(item_id: integer, item_name: string, price:

integer)

Sale(bill_no: integer, bill_data: date, cust_id: integer, item_id: integer, qty_sold: integer)

For the above schema, perform the following:-

a) Create the tables with the appropriate integrity constraint.

Solution a)
b) Insert around 10 records in each of the

tables Solution b)
c) List all the bills for the current date with the customer names and
item numbers

Solution b)
d. List the total Bill details with the quantity sold price of the item
and the final amount
e) List the details of the customer who have bought a product which has a
price > 200
f) Give a count of how many products have been bought by each customer
g) Give a list of products bought by a customer having cust_id as 5

h) List the item details which are sold as of today


i) Create a view which lists out the bill_no, bill_date, cust_id, item_id, price, qty_sold,
amount.
j) create a view which lists the date wise daily sales for the last one week.
b) Database Schema for a Employee-pay scenario:-

1. Employee(emp_id : integer, emp_name: string)

2. Department (dept_id: integer, dept_name:string)

3. Paydetails(emp_id : integer, dept_id: integer, basic: integer,deductions:

integer, additions: integer, DOJ: date)

4. payroll(emp_id : integer, pay_date: date)

For the above schema, perform the following: —

a) Create the tables with the appropriate integrity constraints


b) Insert around 10 records in each of the tables
c) List the employee details department wise
d) List all the employee names who joined after particular date
e) List the details of employees whose basic salary is between
10,000 and 20,000

f) Give a count of how many employees are working in each department


g) Give a name of the employees whose netsalary>10,000.

h) List the details for an employee_id=5


i) Create a view which lists out the emp_name, department, basic, deductions,netsalary

j) Create a view which lists the emp_name and his netsalary


Problem 2 :
Construct a PL/SQL program to find largest number from the given three
numbers.

Output
Problem 3 :
Build a PL/SQL program to generate all prime numbers below 100.
Output
Problem 4:
Construct a PL/SQL program to demonstrate %type and %row type
attributes.

Output
Problem 5:
Develop a PL/SQL procedure to find reverse of a given number.

Output
Problem 6 :
Create a PL/SQL procedure to update the salaries of all employees by 10%
in their basic pay.

Output
Problem 7 :
Execute a PL/SQL procedure to demonstrate IN, OUT and INOUT
parameters.

Output
Problem 8:
Design a PL/SQL trigger before/after update on employee table
for each row/statement.

Output
Problem
Build 8:
a PL/SQL trigger before/after insert on employee table for
each row/statement.

Output:

You might also like