0

0
28

28

28

28
50 Parul University

50

50

50
10

10

10

10
03

03

03

03
Name: Priyanshu kumar Jha Scan to verify results
03

03

03

03
24

24

24

24
Email: 2403031050280@[Link]
Roll no: 2403031050280
Phone: 7033243445
Branch: Parul University
Department: CSE4_Batch 2
Batch: 2028
Degree: [Link] - CSE
0

80

80
28

28
02

02
50

50
05

05
PIET_Oracle DBMS_Course
0

10
31

31
03

03
30

30
03

03
0

0
PIET_Oracle DBMS_Session 2_COD
24

24

24

24
Attempt : 2
Total Mark : 50
Marks Obtained : 50

Section 1 : COD

1. Problem Statement
0

80

80

0
28

28
02

02
50

50
5

Alice is developing a blockchain-based supply chain tracking system to

10

10

10

10
03

03

03

manage the movement of products from manufacturers to customers. The 03

03

03

03

03
system needs to handle three main types of data: Products, Shipments,
24

24

24

24

and Transactions.

Write the DDL statements to create the following tables to handle the data:

Table Structure:
0

0
28

28

28

28

Tasks for Alice:

50

50

50

50
10

10

10

10

Create the Products table with the following columns: ProductID,

03

03

03

03
03

03

03

03
24

24

24

24
 0

0
28

28

28

28
ProductName, Category, Price, and [Link] the Shipments
50

50

50

50
table with the following columns: ShipmentID, ProductID, ShipmentDate,
10

10

10

10
Destination, and [Link] the Transactions table with the following
03

03

03

03
03

03

03

03
columns: TransactionID, ShipmentID, TransactionDate, Amount, and
24

24

24

24
[Link] the Products table to SupplyChainProducts to
better reflect its role in the [Link] the Shipments table to add a new
column ShipmentStatus to track the status of each shipment (e.g.,
Pending, Shipped, Delivered).
Note: The user must write only the query to create, rename, and alter the
table. The query to display the description of the table is already given.

Answer
0

80

80
28

28
02

02
50

50
[Link]
05

05
0

10
31

31
03

03
-- Create the Products table
30

30
03

03
CREATE TABLE Products (
0

0
24

24

24

24
ProductID NUMBER,
ProductName VARCHAR2(255),
Category VARCHAR2(100),
Price DECIMAL(10, 2),
StockQuantity NUMBER
);

-- Create the Shipments table

CREATE TABLE Shipments (
0

80

80

0
28

28
02

02

ShipmentID NUMBER,
50

50
5

ProductID NUMBER,
10

10

10

10
03

03

03

03
ShipmentDate DATE,
03

03

03

03
Destination VARCHAR2(255),
24

24

24

24

Quantity NUMBER
);

-- Create the Transactions table

CREATE TABLE Transactions (
TransactionID NUMBER,
ShipmentID NUMBER,
TransactionDate DATE,
Amount DECIMAL(10, 2),
0

0
28

28

28

28

PaymentStatus VARCHAR2(50)
50

50

50

50

);
10

10

10

10
03

03

03

03
03

03

03

03
24

24

24

24
 0

0
28

28

28

28
50 -- Rename the Products table to

50

50

50
SupplyChainProducts
10

10

10

10
ALTER TABLE Products
03

03

03

03
RENAME TO SupplyChainProducts;
03

03

03

03
24

24

24

24
-- Alter the Shipments table to add
ShipmentStatus column
ALTER TABLE Shipments
ADD ShipmentStatus VARCHAR2(50);-- Create
the Products table

Status : Correct Marks : 10/10

0

80

80
28

28
02

02
50

50
05

05
0

10
31

31
2. Problem Statement
03

03
30

30
03

03
0

0
Lucas is developing a food delivery application and needs to store data
24

24

24

24
about orders and restaurants. He has two tables: one for restaurants and
one for the orders placed at these restaurants.
Table Name: Restaurants
The Restaurants table will store restaurant details like the restaurant name
and cuisine type.

Table Name: Orders

0

80

80

0
28

28
02

02
50

50
The Orders table will store details about customer orders, including order
5

5
10

10

10

10
ID, restaurant ID, and the total price of the order.
03

03

03

03
03

03

03

03
24

24

24

24

Sample Input Records:

Task for Lucas:

0

Create the Restaurants table to store restaurant details, including

28

28

28

28
50

50

50

50

RestaurantID, RestaurantName, and [Link] the Orders table to

10

10

10

10

store order details, including OrderID, RestaurantID, and [Link] at

03

03

03

03
03

03

03

03
24

24

24

24
 0

0
28

28

28

28
least 5 records into the Restaurants table with the following fields:
50

50

50

50
RestaurantID, RestaurantName, and [Link] at least 5 records
10

10

10

10
into the Orders table with the following fields: OrderID, RestaurantID, and
03

03

03

03
03

03

03

03
TotalPrice.
24

24

24

24
Note: The user must write only the query to create and insert the sample
records in the tables. The Query to display the table is predefined.

Answer
[Link]
-- Create the Restaurants table without constraints
CREATE TABLE Restaurants (
0

80

80
28

28
RestaurantID NUMBER,
02

02
50

50
RestaurantName VARCHAR2(30),
05

05
0

10
31

31
CuisineType VARCHAR2(30)
03

03
30

30
03

03
);
0

0
24

24

24

24
-- Create the Orders table without constraints
CREATE TABLE Orders (
OrderID NUMBER,
RestaurantID NUMBER,
TotalPrice NUMBER
);

-- Insert sample records into the Restaurants table

0

80

80

0
28

28
INSERT INTO Restaurants (RestaurantID, RestaurantName,
02

02
50

50
CuisineType)
5

5
10

10

10

10
VALUES (1, 'Pizza Palace', 'Italian');
03

03

03

03
03

03

03

03
24

24

24

24

INSERT INTO Restaurants (RestaurantID, RestaurantName,

CuisineType)
VALUES (2, 'Sushi Spot', 'Japanese');

INSERT INTO Restaurants (RestaurantID, RestaurantName,

CuisineType)
VALUES (3, 'Burger King', 'American');

-- Insert sample records into the Orders table

0

0
28

28

28

28

INSERT INTO Orders (OrderID, RestaurantID, TotalPrice)

50

50

50

50

VALUES (1, 1, 25);

10

10

10

10
03

03

03

03
03

03

03

03
24

24

24

24
 0

0
28

28

28

28
50 INSERT INTO Orders (OrderID, RestaurantID, TotalPrice)

50

50

50
VALUES (2, 2, 30);
10

10

10

10
03

03

03

03
INSERT INTO Orders (OrderID, RestaurantID, TotalPrice)
03

03

03

03
24

24

24

24
VALUES (3, 3, 15);

Status : Correct Marks : 10/10

3. Problem Statement
Olivia is developing a simple ticket booking system for a local cinema. She
0

80

80
needs to create two tables to store data about movies and tickets.
28

28
02

02
50

50
05

05
0

10
Table Name: Movies
31

31
03

03
30

30
03

03
0

0
24

24

24

24
Table Name: Tickets

The Movies table will store movie details like the movie title and its release
year.
Smaple Input Record:
0

80

80

0
28

28
02

02
50

50
5

</strong>
10

10

10

10
03

03

03

03
03

03

03

03
24

24

24

24

Note: The user must write only the query to create and insert the sample
records in the table. The query to display the description of the table is
already given.

Answer
[Link]
0

CREATE TABLE Movies (

28

28

28

28
50

50

50

50

MovieTitle VARCHAR2(30),
10

10

10

10

ReleaseYear NUMBER
03

03

03

03
03

03

03

03
24

24

24

24
 0

0
28

28

28

28
50 );

50

50

50
10

10

10

10
CREATE TABLE Tickets (
03

03

03

03
TicketID NUMBER,
03

03

03

03
24

24

24

24
MovieID NUMBER,
TicketPrice NUMBER
);

-- Inserting data into the Movies table

INSERT INTO Movies (MovieTitle, ReleaseYear) VALUES ('The
Avengers', 2012);
INSERT INTO Movies (MovieTitle, ReleaseYear) VALUES ('Iron Man',
2008);
0

80

80
28

28
INSERT INTO Movies (MovieTitle, ReleaseYear) VALUES ('Black
02

02
50

50
Panther', 2018);
05

05
0

10
31

31
03

03
30

30
-- Inserting data into the Tickets table
03

03
0

0
24

24

24

24
INSERT INTO Tickets (TicketID, MovieID, TicketPrice) VALUES (1, 1,
10);
INSERT INTO Tickets (TicketID, MovieID, TicketPrice) VALUES (2, 2,
12);
INSERT INTO Tickets (TicketID, MovieID, TicketPrice) VALUES (3, 3,
15);

Status : Correct Marks : 10/10

0

80

80

0
28

28
02

02
50

50
5

5
10

10

10

10
4. Problem Statement
03

03

03

03
03

03

03

03
Syed is tasked with setting up the database schema for managing
24

24

24

24

employee information. The platform requires the following table:

Table Name: Employee

The Employee table will store details such as employee name, street, and
city. The following data must be inserted into the table:
Sample Input Records:
0

0
28

28

28

28
50

50

50

50
10

10

10

10

</strong>
03

03

03

03
03

03

03

03
24

24

24

24
 0

0
28

28

28

28
Note: The user must write only the query to create and insert the sample
50

50

50

50
10

10

10

10
records in the table.
03

03

03

03
03

03

03

03
The query to display the description of the table is already given.
24

24

24

24
Answer
[Link]
CREATE TABLE Employee (
Emp_name VARCHAR2(30),
Street VARCHAR2(30),
City VARCHAR2(30)
);
0

80

80
28

28
02

02
50

50
05

05
0

10
31

31
03

03
INSERT INTO Employee (Emp_name, Street, City) VALUES ('Adam', 'Spring',
30

30
03

03
'Pittsfield');
0

0
24

24

24

24
INSERT INTO Employee (Emp_name, Street, City) VALUES ('Brooks',
'Senator', 'Brooklyn');
INSERT INTO Employee (Emp_name, Street, City) VALUES ('Curry', 'North',
'Rye');
INSERT INTO Employee (Emp_name, Street, City) VALUES ('Demalo',
'SunShine', 'San Deago');

Status : Correct Marks : 10/10

0

80

80

0
28

28
02

02
50

50
5

5
10

10

10

10
03

03

03

03
5. Problem Statement
03

03

03

03
24

24

24

24

Leka is working as an intern at a vacation rental company. Her task is to

manage and analyze rental and booking data. The database admin has
already created two tables, Rentals and Bookings. Leka needs to perform
several operations on these tables based on specific conditions.

Table Name: Rentals

0

0
28

28

28

28

Table Name: Bookings

50

50

50

50
10

10

10

10
03

03

03

03
03

03

03

03
24

24

24

24
 0

0
28

28

28

28
Sample Input Records:
50

50

50

50
10

10

10

10
03

03

03

03
03

03

03

03
24

24

24

24
Tasks for Diego
Insert Records: Insert at least 5 records into the Rentals and Bookings
tables with the values mentioned.
Update Prices: Apply a 20% discount to rentals located in 'Forest' or
'Mountain' with a price greater than $120.
Delete Bookings: Delete bookings made before '2024-02-01' where the
0

80

80
customer ID is less than 203.
28

28
02

02
50

50
05

05
0

10
Select Rentals: Retrieve rentals that are available in July or August 2024
31

31
03

03
and priced under $100.
30

30
03

03
0

0
24

24

24

24
Note:
Ensure that your INSERT, UPDATE, DELETE, and SELECT queries are
correctly formatted and use appropriate date and number formats for
Oracle SQL.

Answer
[Link]
0

80

80

0
28

28
02

02

INSERT INTO Rentals (RentalID, Location, PricePerNight, AvailableFrom,

50

50
5

5
10

10

10

10
AvailableTo)
03

03

03

VALUES (1, 'Beach', 120.00, TO_DATE('2024-07-25', 'YYYY-MM-DD'), 03

03

03

03

03
TO_DATE('2024-09-10', 'YYYY-MM-DD'));
24

24

24

24

INSERT INTO Rentals (RentalID, Location, PricePerNight, AvailableFrom,

AvailableTo)
VALUES (2, 'Mountain', 250.00, TO_DATE('2024-08-01', 'YYYY-MM-DD'),
TO_DATE('2024-10-01', 'YYYY-MM-DD'));

INSERT INTO Rentals (RentalID, Location, PricePerNight, AvailableFrom,

AvailableTo)
0

VALUES (3, 'City', 90.00, TO_DATE('2024-07-20', 'YYYY-MM-DD'),

28

28

28

28

TO_DATE('2024-08-30', 'YYYY-MM-DD'));
50

50

50

50
10

10

10

10
03

03

03

03
03

03

03

03
24

24

24

24
 0

0
28

28

28

28
INSERT INTO Rentals (RentalID, Location, PricePerNight, AvailableFrom,
50

50

50

50
AvailableTo)
10

10

10

10
VALUES (4, 'Beach', 80.00, TO_DATE('2024-07-28', 'YYYY-MM-DD'),
03

03

03

03
TO_DATE('2024-09-15', 'YYYY-MM-DD'));
03

03

03

03
24

24

24

24
INSERT INTO Rentals (RentalID, Location, PricePerNight, AvailableFrom,
AvailableTo)
VALUES (5, 'Forest', 150.00, TO_DATE('2024-07-22', 'YYYY-MM-DD'),
TO_DATE('2024-09-20', 'YYYY-MM-DD'));

INSERT INTO Bookings (BookingID, RentalID, CustomerID, BookingDate)

VALUES (1, 1, 201, TO_DATE('2024-07-01', 'YYYY-MM-DD'));
0

80

80
28

28
INSERT INTO Bookings (BookingID, RentalID, CustomerID, BookingDate)
02

02
50

50
VALUES (2, 2, 202, TO_DATE('2023-12-15', 'YYYY-MM-DD'));
05

05
0

10
31

31
03

03
30

30
INSERT INTO Bookings (BookingID, RentalID, CustomerID, BookingDate)
03

03
0

0
24

24

24

24
VALUES (3, 3, 203, TO_DATE('2024-01-10', 'YYYY-MM-DD'));

INSERT INTO Bookings (BookingID, RentalID, CustomerID, BookingDate)

VALUES (4, 4, 204, TO_DATE('2023-11-20', 'YYYY-MM-DD'));

INSERT INTO Bookings (BookingID, RentalID, CustomerID, BookingDate)

VALUES (5, 5, 205, TO_DATE('2024-07-15', 'YYYY-MM-DD'));

UPDATE Rentals
0

80

80

0
28

28
SET PricePerNight = PricePerNight * 0.8
02

02
50

50
WHERE Location IN ('Forest', 'Mountain') AND PricePerNight > 120;
5

5
10

10

10

10
03

03

03

DELETE FROM Bookings 03

03

03

03

03
24

24

24

24

WHERE BookingDate < TO_DATE('2024-02-01', 'YYYY-MM-DD')

AND CustomerID < 203;

SELECT *
FROM Rentals
WHERE PricePerNight < 100
AND AvailableFrom <= TO_DATE('2024-08-31', 'YYYY-MM-DD')
AND AvailableTo >= TO_DATE('2024-07-01', 'YYYY-MM-DD');
0

0
28

28

28

28

Status : Correct Marks : 10/10

50

50

50

50
10

10

10

10
03

03

03

03
03

03

03

03
24

24

24

24