[go: up one dir, main page]
Scribd
Upload
13 views20 pages

Journal File

The document contains SQL queries and PL/SQL code examples for creating and managing database tables related to products, inventories, teams, and players. It includes instructions for displaying product availability, player details, and performing various operations such as adding columns and calculating fees. Additionally, it provides PL/SQL code blocks for tasks like printing numbers and managing employee salaries.

Uploaded by

manishamakavana91
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
13 views20 pages

Journal File

The document contains SQL queries and PL/SQL code examples for creating and managing database tables related to products, inventories, teams, and players. It includes instructions for displaying product availability, player details, and performing various operations such as adding columns and calculating fees. Additionally, it provides PL/SQL code blocks for tasks like printing numbers and managing employee salaries.

Uploaded by

manishamakavana91
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/ 20

0

CRDBMS JOURNAL
[Document subtitle]

MARCH 17, 2023


M.L PARMAR
GHOD DHOD ROAD
1

QUE:-1. Create following tables in Oracle and Perform the list of SQL
queries given below: PRODUCTS(product_id, product_name ,
description, price, category_id ) INVENTORIES(product_id,
warehouse_id, quantity) PRODUCT_CATEGORIES(category_id,
category_name) WAREHOUSES(warehouse_id, warehouse_name,
address, city, state, country) Perform the Following SQL Queries with
above tables
ANS:-

========================================================
====================================================
1:-Display the list of all
products which are not available.
========================================================
====================================================

==================================ANSI
STYLE========================================
select i.Product_id,i.warehouse_id,i.quantity,w.Warehouse_name
from Invantory i
inner join warehouse w
on i.warehouse_id=w.warehouse_id
where quantity=0;

===================================THETA
STYLE======================================
select i.Product_id,i.warehouse_id,i.quantity,w.Warehouse_name
from Invantory i,warehouse w
where i.warehouse_id=w.warehouse_id and quantity=0;

===================================SUB
QUERI=======================================
select Product_id,warehouse_id,quantity
from Invantory
where Warehouse_id in(select Warehouse_id from Invantory where quantity=0);
2

====================================OUTPUT================================
=========

========================================================
====================================================
2:-Display Product name, Warehouse
name, Quantity for all products.
========================================================
====================================================

===================================ANSI
STYLE======================================
select p.product_name,w.warehouse_name,i.quantity
from product p
inner join invantory i
on i.Product_Id=p.Product_Id
inner join warehouse w
on i.warehouse_id=w.warehouse_id;

===================================THETA
STYLE======================================
select p.product_name,w.warehouse_name,i.quantity
from product p,invantory i,warehouse w
where i.product_id=p.product_id and i.warehouse_id=w.warehouse_id;

===================================OUTPUT=================================
=====
3

========================================================
====================================================
3:-Display Product name, Category Name,
Quantity available in warehouse_name=”Amazon”.
========================================================
====================================================

==========================ANSI
STYLE===============================
select p.product_name,c.Catogary_name,i.quantity
from product p
inner join Invantory i
on i.Product_Id=p.Product_Id
inner join product_category c
on p.Category_id=c.Category_id
inner join warehouse w
on i.warehouse_id=w.warehouse_id
where Warehouse_name='Amazone';

==========================THETA
STYLE=============================
==
select p.product_name,c.Catogary_name,i.quantity
from product p,invantory i,warehouse w ,product_category c
where i.product_id=p.product_id and i.warehouse_id=w.warehouse_id and
p.Category_Id=c.Category_Id and warehouse_name='Amazone';

=============================OUTP
UT===============================
4

========================================================
====================================================
4:-Display Warehouse name, Address,
Quantity available for category_name=”Shoes”.
========================================================
====================================================

==========================ANSI
STYLE===============================

select w.warehouse_name,w.address,i.quantity,p.product_name

from product p

inner join Invantory i

on i.Product_Id=p.Product_Id

inner join product_category c

on p.Category_id=c.Category_id

inner join warehouse w

on i.warehouse_id=w.warehouse_id

where catogary_name='Sport';

==========================THETA
STYLE===============================

select w.warehouse_name,w.address,i.quantity,p.product_name

from product p,invantory i,warehouse w ,product_category c

where i.product_id=p.product_id and i.warehouse_id=w.warehouse_id and


p.Category_Id=c.Category_Id and catogary_name='Sport';

==========================OUTPUT=========
======================
5

QUE:-2. Create following tables in Oracle and Perform the list of SQL queries given
below:Team(TeamID, TeamName, CoachName) Players(Pid, PName, TeamId, PType,
Pcontact, PEmail, PDOB, PFee) Create the above tables with appropriate constraints.

ANS:-

========================================================
====================================================
2) Add one more column as
“Pmobile” of text type into Players
table.
========================================================
=====================================================

========================================================
====================================================
3) Get details of TeamName
and CoachName.
========================================================
====================================================
6

========================================================
====================================================
4) Retrieve List of players and their fee,
sorted in ascending order of player name.
========================================================
====================================================

========================================================
====================================================
5) Display maximum, minimum,
average, and total player fees.
7

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

========================================================
====================================================
6) Display names of players whose names
starts from “S” but not with “D”.
========================================================
====================================================

========================================================
====================================================
7) Display team wise total number players in
descending order of noofpalyers.
========================================================
====================================================
8

========================================================
====================================================
8) Display all players whose
birth year is 1995.
========================================================
====================================================

SELECT PName FROM Players WHERE (to_char(PDOB,'yy')='95');

PLAYER_ID PLAYER_NAM P_DOB


---------- ---------- ---------------
105 Viram 12-DEC-95
103 Tushar 12-MAY-95

========================================================
====================================================
9) Display all
players who are
“AllRounder”.
9

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

========================================================
====================================================
10) Increase player fee
Rs.200 for palyerid is 6.
========================================================
====================================================

========================================================
====================================================
11) Remove records of
players who are
wicketkeeper.
========================================================
====================================================
10

QUE:-3. Write a PL/SQL code block to Print the first 10


Even numbers.

OUTPUT:-

QUE:-4. Write a PL/SQL code for print the numbers 1 to 10.


11

OUTPUT:-

QUE:-5. Write a PL/SQL code block for Accept the dept_no


and print the no. of employees working in that department.
OUTPUT:-
12

QUE:-6. Write a PL/SQL code block that will accept an


employee number from the user and deduct a salary by
Rs.1000 from the input employee number. If employee has a
minimum salary of Rs.5000 after salary is deducted then
display message “SALARY IS NOT ENOUGHTO DEDUCT‟
13

otherwise deduct the salary. Use the table Emp(empno,


name, address, mobno,salary).

Output:-

QUE:-7. Write a PL/SQL block for accept empno and print its
details using cursor.

PL/SQL CODE:-

DECLARE

c_id customers.id%type;

c_name customers.name%type;

c_addr customers.address%type;
14

CURSOR c_customers is

SELECT id,name,address FROM customers;

BEGIN

OPEN c_customers;

LOOP

FETCH c_customers into c_id,c_name,c_addr;

EXIT WHEN c_customers%notfound;

dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

END LOOP;

CLOSE c_customers;

END;

Output:-

1 ramesh Allahabad

2 suresh kanpur

3 Mahesh Ghaziabad

4 Chandan Noida

5 Alex Paris

QUE:-8. Write a PL/SQL script which shows subject wise


marks scored by every student studyingin the course
entered by a user at runtime.

OUTPUT:-
15

QUE:-9. (A)Create following table with appropriate constraints


CustMast (custID, cname, city, phNo) BillMast (billNo, custID, bill
date, billAmt . Insert minimum 15 records.
(B)Write PL/SQL block to give 20% discount on bill amount more
than 3200 Rs/- else give 5% discount. And display the details.

OUTPUT:-
16

QUE:-10. (A)Create following tables with appropriate


constraints:- Doctormaster (doctpid, name, address,
specialization)
Patientmaster(patid,patname,DOB,address,phoneno,
doctpid) Insert minimum 15 records.

OUTPUT:-

CREATE TABLE Doctormasters


(
Doc_id int primary key,
Doc_Name VARCHAR2(15),
city varchar2(15),
Specialization VARCHAR2(50)
);

INSERT INTO Doctormasters VALUES


(1,'Dr.prit','rajkot','Cardiologist');
INSERT INTO Doctormasters VALUES
(2,'Dr.tushar','mumbai','Pediatrician');
17

INSERT INTO Doctormasters VALUES


(3,'Dr.vandit','surat','Dermatologist');
INSERT INTO Doctormasters VALUES
(4,'Dr.kenil','ahmedabed','Gynecologist');
INSERT INTO Doctormasters VALUES
(5,'Dr.harshal','surat','Orthopedic');
INSERT INTO Doctormasters VALUES
(6,'Dr.vipul','saitpur','pediatrician');
INSERT INTO Doctormasters VALUES
(7,'Dr.kevin','pune','Orthopedic');
INSERT INTO Doctormasters VALUES
(8,'Dr.manish','bangluru','Gynecologist');
INSERT INTO Doctormasters VALUES
(9,'Dr.khusi','godhra','Cardiologist');
INSERT INTO Doctormasters VALUES
(10,'Dr.naitik','jamnangr','Orthopedic');
INSERT INTO Doctormasters VALUES
(11,'Dr.rahul','jetpur','pediatreician');
INSERT INTO Doctormasters VALUES
(12,'Dr.bansi','junaghadh','cardiologist');
INSERT INTO Doctormasters VALUES
(13,'Dr.dhruv','ahemdabad','Gynecologist');
INSERT INTO Doctormasters VALUES
(14,'Dr.milan','rajkot','Dermatologist');
INSERT INTO Doctormasters VALUES
(15,'Dr.monu','mumbai','Orthopedic');
18

CREATE TABLE Patientmasters

Pati_d int primary key,

Pat_name VARCHAR2(15),

DOB DATE,

Address VARCHAR2(20),

Phone_no int,

Doc_pid int references Doctormasters(Doc_id)

);

INSERT INTO Patientmasters VALUES (101,'John Doe',to_date('12-03-2001','dd-mm-yyyy'),'Main


St',5552169203,1);

INSERT INTO Patientmasters VALUES (102,'Mary Smith',to_date('10-04-2002','dd-mm-yyyy'),'Park


Ave',5585201497,2);

INSERT INTO Patientmasters VALUES (103,'Robert Lee',to_date('11-04-2005','dd-mm-


yyyy'),'Broadway',4578639012',3);

INSERT INTO Patientmasters VALUES (104,'Jane Smith',to_date('02-03-2004','dd-mm-yyyy'),'5th


Ave',1452893456',4);

INSERT INTO Patientmasters VALUES (105,'vipul',to_date('22-01-2008','dd-mm-yyyy'),'1st


Str',7856127890,5);

INSERT INTO Patientmasters VALUES (106,'jack',to_date('30-12-2006','dd-mm-


yyyy'),'punagam',5421367890,6);

INSERT INTO Patientmasters VALUES (107,'john',to_date('15-10-2007','dd-mm-


yyyy'),'varachha',142305897,7);

INSERT INTO Patientmasters VALUES (108,'milan',to_date('12-02-2008','dd-mm-


yyyy'),'manigam',785236941,8);

INSERT INTO Patientmasters VALUES (109,'meet',to_date('11-12-2009','dd-mm-


yyyy'),'punagam',1452987365,9);

INSERT INTO Patientmasters VALUES (110,'mayur',to_date('01-08-2010','dd-mm-yyyy'),'kangaru


circle',1036548972,10);

INSERT INTO Patientmasters VALUES (111,'puja',to_date('03-07-2011','dd-mm-


yyyy'),'baroda',7836145911,11);
19

INSERT INTO Patientmasters VALUES (112,'naitik',to_date('05-06-2012','dd-mm-


yyyy'),'sitanagar',1258963479,12);

INSERT INTO Patientmasters VALUES (113,'mohit',to_date('19-01-2013','dd-mm-


yyyy'),'varachha',7852146389,13);

INSERT INTO Patientmasters VALUES (114,'vinay',to_date('27-12-2014','dd-mm-


yyyy'),'punagam',1025486379,14);

INSERT INTO Patientmasters VALUES (115,'manav',to_date('30-10-2015','dd-mm-


yyyy'),'velenja',1425897631,15);

PL/SQL BLOCK:-

You might also like