Question

Consider a database schema with three relations:

- Parts (pid:integer, pname:string, year:integer, price:integer)

-Suppliers (sid:integer, sname: string, state:string, zipcode:string)

-Orders (pid:integer, sid:integer, quantity:integer)

The description is as follows: a factory keeps a database with parts that it uses, the suppliers of those
parts, and purchase orders. Each part is uniquely identified by pid. Each part has a string description
pname, year of fabrication and price per unit. Parts are provided by suppliers, and each supplier is
uniquely identified by sid. Each supplier has a name sname, and it operates at the given state and
zipcode. The company has a number of orders for parts, and each order contains the pid of the part
ordered, the sid of the supplier from which the part is ordered, and the quantity ordered. You are
allowed flexibility on the exact attribute types you use for your schema, as long as they reasonably match
the specification above (e.g., in terms of number types, string types). Include the schema definition in
your submission in a file called schema.sql.

Question 1

Write a PL/SQL function that takes a price as argument and returns the pid of the part that has the third-
nearest price to the one given. Note that nearest can be either higher or lower. In case there are multiple
parts with that property, choose the one with higher year. If there is still a tie, choose the one with
highest pid.

Question 2
Write a PL/SQL procedure that receives as arguments pid, sid and quantity of a prospective order.
First, you need to determine if the value (i.e., dollar amount) of that order will be lower or equal than 75%
of the average previous order value for that part. If the answer is yes, go ahead and input the new order
into the database.
Otherwise, compute the price value that would make the prospective order value be exactly at the 75%
limit above, and then insert a NEW part with that price, and the same attributes as the part given in the
pid parameter (except for the pid of course, for which you need to determine a unique value). Then,
input in the database an order with the sid and quantity given, but for the new pid.

Answer 1

I have created SQL query to accomplish the above requirement said in the question

Below are the SQL script to create the table schema and also given the insert data scripts

**************CREATE TABLE SCRIPTS*******************

PARTS

CREATE TABLE Parts(

pid int NOT NULL,

pname varchar(150) NOT NULL,

year int NOT NULL,

price int NOT NULL

)

SUPPLIERS

CREATE TABLE Suppliers(

sid int NOT NULL,

sname varchar(150) NOT NULL,

state varchar(150) NOT NULL,

zipcode varchar(150) NOT NULL

)

ORDERS

CREATE TABLE Orders(

pid int NOT NULL,

sid int NOT NULL,

quantity int NOT NULL

)

********************END********************

And below script is for adding data to the tables given above

*******************INSERT DATA SCRIPT*******************

PARTS

INSERT Parts ( pid , pname , year , price ) VALUES (1, N'Iron', 2017, 24)
INSERT Parts ( pid , pname , year , price ) VALUES (2, N'Blender', 2016, 22)
INSERT Parts ( pid , pname , year , price ) VALUES (3, N'Cornpopper', 2018, 24)
INSERT Parts ( pid , pname , year , price ) VALUES (4, N'Gas Grill', 2017, 149)
INSERT Parts ( pid , pname , year , price ) VALUES (5, N'Washer', 2018, 399)

SUPPLIERS

INSERT Suppliers ( sid , sname , state , zipcode ) VALUES (1, N'Sally', N'Lansing', N'49224')
INSERT Suppliers ( sid , sname , state , zipcode ) VALUES (2, N'Ann', N'Grant', N'49219')
INSERT Suppliers ( sid , sname , state , zipcode ) VALUES (3, N'Don', N'Ira', N'49034')
INSERT Suppliers ( sid , sname , state , zipcode ) VALUES (4, N'Tom', N'Kent', N'48391')
INSERT Suppliers ( sid , sname , state , zipcode ) VALUES (5, N'Al', N'Grant', N'49219')
INSERT Suppliers ( sid , sname , state , zipcode ) VALUES (6, N'Sally', N'Lansing', N'49224')
INSERT Suppliers ( sid , sname , state , zipcode ) VALUES (7, N'Mary', N'Ada', N'49441')
INSERT Suppliers ( sid , sname , state , zipcode ) VALUES (8, N'Tran', N'Harper', N'48421')
INSERT Suppliers ( sid , sname , state , zipcode ) VALUES (9, N'Mara', N'Ada', N'49441')
INSERT Suppliers ( sid , sname , state , zipcode ) VALUES (10, N'Dan', N'Grant', N'49219')

ORDERS

INSERT Orders ( pid , sid , quantity ) VALUES (1, 2, 10)
INSERT Orders ( pid , sid , quantity ) VALUES (2, 9, 30)
INSERT Orders ( pid , sid , quantity ) VALUES (5, 10, 20)
INSERT Orders ( pid , sid , quantity ) VALUES (4, 6, 25)
INSERT Orders ( pid , sid , quantity ) VALUES (3, 8, 90)
INSERT Orders ( pid , sid , quantity ) VALUES (2, 7, 66)
INSERT Orders ( pid , sid , quantity ) VALUES (1, 1, 43)
INSERT Orders ( pid , sid , quantity ) VALUES (5, 3, 56)
INSERT Orders ( pid , sid , quantity ) VALUES (2, 4, 62)
INSERT Orders ( pid , sid , quantity ) VALUES (5, 9, 48)
INSERT Orders ( pid , sid , quantity ) VALUES (4, 10, 33)

*********************END*****************

After you executed the above create and data insert scripts in the database the table schema and data will be similar as shown in the below screen

Select from Parts; Select from Suppliers;l Selectfrom Orders; 100 % ResultsMessages pid pname year pnce 2017 24 1 1 Iron 2 2 Blender 2016 22 3 3 Compopper 2018 24 4 4 Gas G 2017 149 55 Washer 2018 399 sid sname state zipcode 1 1Saly Lansing 49224 2 2 Ann Grant 49219 49034 48391 Grant49219 6 6 Sally Lansing 49224 49441 8 8Tra Harpe 48421 3 3 DonIra 4 4 Tom Kent 7 7 Mary Ada pid sid quantity 1 1 2 10 3 5 10 20 4 4 6 25 7 1 1 43 10 5 9 48 11 410 33

Below are the answers for the questions asked

Question 1

Write a PL/SQL function that takes a price as argument and returns the pid of the part that has the third-
nearest price to the one given. Note that nearest can be either higher or lower. In case there are multiple
parts with that property, choose the one with higher year. If there is still a tie, choose the one with
highest pid.

SQL CODE

I have given comment inside the code please have a look as it explains the code logic

CREATE FUNCTION GetHighestPID (@price int)

RETURNS INT

AS BEGIN

--Declared the variables to hold the pid for return value

--and also to hold Highest and lower price

DECLARE @PID INT

Declare @HighestPrice INT

DECLARE @LowestPrice INT

--Get the highest price nearer to price given

SELECT top 1 @HighestPrice = p.price FROM Parts P

WHERE P.price >= @price

ORDER BY P.price asc

--Get the lowest price nearer to price given

SELECT top 1 @LowestPrice = p.price FROM Parts P

WHERE P.price <= @price

ORDER BY P.price DESC

--If lower price has value and highest is null then choose the

--pid from the lowest value with higher price

IF(@PID IS NULL AND @HighestPrice IS NULL AND @LowestPrice IS NOT NULL)

BEGIN

SELECT TOP 1 @PID = P.Price FROM Parts P

WHERE P.price = @LowestPrice

ORDER BY P.price DESC

END

--If highest price has value and lower is null then choose the

--pid from the highest value with higher price

IF (@PID IS NULL AND @HighestPrice IS NOT NULL AND @LowestPrice IS NULL)

BEGIN

SELECT TOP 1 @PID = P.Price FROM Parts P

WHERE P.price = @LowestPrice

ORDER BY P.price DESC

END

--If highest and lowest both has value then choose the

--pid which has greater year value

--If both the year are same then skip to next if condition

IF (@PID IS NULL AND @HighestPrice IS NOT NULL AND @LowestPrice IS NOT NULL)

BEGIN

DECLARE @YEAR INT

SELECT TOP 1 @YEAR = [YEAR] FROM PARTS WHERE price = @LowestPrice

IF NOT EXISTS (SELECT 1 FROM Parts P

WHERE P.price = @HighestPrice AND P.[year] = @YEAR)

BEGIN

SELECT TOP 1 @PID = P.Price FROM Parts P

WHERE P.price = @HighestPrice OR P.price = @LowestPrice

ORDER BY P.[Year] DESC

END

END

--If highest and lowest both has value then choose the

--pid which has greater pid value

IF (@PID IS NULL AND @HighestPrice IS NOT NULL AND @LowestPrice IS NOT NULL)

BEGIN

SELECT TOP 1 @PID = P.Price FROM Parts P

WHERE P.price = @HighestPrice OR P.price = @LowestPrice

ORDER BY P.PID DESC

END

--If all the above condition failed then

--take value from price exactly match with

--parameter value or else return null value

IF (@PID IS NULL)

BEGIN

SELECT TOP 1 @PID = P.Price FROM Parts P

WHERE P.price = @price

END

RETURN @PID

END

CREATE FUNCTION GetHighestPID (@price int) RETURNS INT AS BEGIN ョーDeclared the variables to hold the pid for return value --and also to hold Highest and lower price DECLAREPID INT Declare @HighestPrice INT DECLARE @LowestPrice INT --Get the highest price nearer to price given SELECT top 1 @HighestPrice = p.price FROM Parts P WHERE P.price >= @price ORDER BY P.price asc -Get the lowest price nearer to price given SELECT top 1 @LowestPrice = р.price FROM Parts P nHERE P.price <= @price ORDER BY P.price DESC If lower price has value and highest is null then choose the --pid from the lowest value with higher price IF(@PID IS NULL AND @HighestPrice IS NULL AND @LowestPrice IS NOT NULL) BEGIN SELECT TOP 1 @PID-P. Price FROM Parts P WHERE P.price @Lowe stPrice ORDER BY P.price DESC END If highest price has value and lower is null then choose the --pid from the highest value with higher price IF (@PID IS NULL AND @HighestPrice IS NOT NULL AND @LowestPrice IS NULL) BEGIN SELECT TOP 1 @PID-P. Price FROM Parts P WHERE P.price-@LowestPrice ORDER BY P.price DESC END E If highest and lowest both has value then choose the --pid which has greater year value --If both the year are same then skip to next if condition IF (@PID IS NULL AND @HighestPrice IS NOT NULL AND @LowestPrice IS NOT NULL) BEGIN DECLARE @YEAR INT SELECT TOP 1 @YEAR [YEAR] FROM PARTS WHERE price = @LowestPrice

you have to execute the function by using the below command

IF NOT EXISTS (SELECT 1 FROM Parts P WHERE P.price -@HighestPrice AND P. [year] @YEAR) BEGIN SELECT TOP 1 @PID P. Price FROM Parts P WHERE P.price @HighestPrice OR P.price = @LowestPrice ORDER BY P. [Year] DESC END END --If highest and lowest both has value then choose the --pid which has greater pid value IF (@PID IS NULL AND @HighestPrice IS NOT NULL AND @LowestPrice IS NOT NULL) BEGIN SELECT TOP 1 @PID P. Price FROM Parts P WHERE P.price-@HighestPrice OR P.price@LowestPrice ORDER BY P.PID DESC END If all the above condition failed thern --take value from price exactly match with --parameter value or else return null value IF (@PID IS NULL) BEGIN SELECT TOP 1 @PID P. Price FROM Parts P WHERE P.price = @price END RETURN PID END

Select dbo.GetHighestPID(26)

Replace the number with any price that you wish

Output Screen

Select dbo.GetHighestPID (26) 100 % Results te Messages (No column name) 1149

Question 2

Write a PL/SQL procedure that receives as arguments pid, sid and quantity of a prospective order.
First, you need to determine if the value (i.e., dollar amount) of that order will be lower or equal than 75%
of the average previous order value for that part. If the answer is yes, go ahead and input the new order
into the database.
Otherwise, compute the price value that would make the prospective order value be exactly at the 75%
limit above, and then insert a NEW part with that price, and the same attributes as the part given in the
pid parameter (except for the pid of course, for which you need to determine a unique value). Then,
input in the database an order with the sid and quantity given, but for the new pid.

SQL CODE

I have given comment inside the code please have a look as it explains the code logic

CREATE PROCEDURE dbo.CheckAndInsertParts @varPID int, @varSID INT, @varQuantity INT

AS

--Declared the variables to hold the average amount

--total quanity, price and precentage values

DECLARE @AverageAmount INT

DECLARE @TotalQuantity INT

DECLARE @TotalPrice Decimal(6,2)

Declare @SeventyFivePercentPrice Decimal(6,2)

--Get the total quantity of the given pid from orders table

SELECT @TotalQuantity = SUM(Quantity) FROM Orders Where pid = @varPID

--Get the total price of the given pid from orders table by joining with parts table

SELECT @TotalPrice = SUM(ItemPrice) FROM

(SELECT p.pid, o.quantity * p.price As ItemPrice

FROM Orders as o

inner join parts as p on p.pid = o.pid

WHERE p.pid = @varPID) as a

--Get average amount

SET @AverageAmount = @TotalPrice / @TotalQuantity

--Calculate 75 % of the amount for given pid

SET @SeventyFivePercentPrice = (@AverageAmount / 100) * 75

--If 75 % amount of average price is greater than the given quantity and price

IF(@SeventyFivePercentPrice > (SELECT (@varQuantity * price) FROM Parts where pid = @varPID))

BEGIN

DECLARE @NEWPID INT

DECLARE @SPID INT

SELECT @NEWPID = MAX(PID) + 1 FROM PARTS

SELECT @SPID = MAX(SID) + 1 FROM SUPPLIERS

--Insert new value in parts with given parameter value

INSERT INTO PARTS (pid, pname, year, price)

SELECT @NEWPID, pname, year, @SeventyFivePercentPrice / 100 FROM Parts where pid = @varPID

--Insert new value in orders with given parameter value

INSERT INTO Orders (pid, sid, quantity)

SELECT @NEWPID, @varSID, @varQuantity

END

--If 75 % amount of average price is equal the price of given quantity

--then insert in orders table alone

ELSE

BEGIN

INSERT INTO Orders (pid, sid, quantity)

SELECT @varPID, @varSID, @varQuantity

END

GO

CREATE PROCEDURE dbo.CheckAndInsertParts @varPID int, @varSID INT, @varQuantity INT AS --Declared the variables to hold the average amount --total quanity, price and precentage values DECLARE AverageAmount INT DECLARE eTotalQuantity INT DECLARE eTotalPrice Decimal (6,2) Declare @SeventyFivePercentPrice Decimal (6,2) --Get the total quantity of the given pid from orders table SELECT @TotalQuantity- SUM (Quantity) FROM Orders Where pid @varPID -Get the total price of the given pid from orders table by joining with parts table SELECT @TotalPrice = sum(ItemPrice) FROM (SELECT p.pid, o.quantityp.price As ItemPrice FROM Orders as o inner join parts as p on p.pid-o.pid WHERE p.pid -@varPID) as a -Get average amount SET @AverageAmounteTotalPrice / @TotalQuantity -Calculate 75 % of the amount for given pid SET @SeventyFivePercentPrice -(@AverageAmount 100) 75 -If 75 % amount of average price is greater than the given quantity and price IF @SeventyFivePercentPrice > (SELECT (@varQuantity price) FROM Parts where pid -@varPID)) BEGIN DECLARE NEWPID INT DECLARE SPID INT SELECT @NEMPID MAX(PID) 1 FROM PARTS SELECT @SPĪD MAX(SID) 1 FROM SUPPLIERS -Insert new value in parts with given parameter value INSERT INTO PARTS (pid, pname, year, price) SELECT @NEWPID, pname, year, @SeventyFivePercentPrice / 100 FROM Parts where pid - @varPID -Insert new value in orders with given parameter value INSERT INTO Orders (pid, sid, quantity) SELECT @NEMPİD, @varSID, @varQuantity END -If 75 % amount of average price is equal the price of given quantity -then insert in orders table alone ELSE BEGIN INSERT INTO Orders (pid, sid, quantity) SELECT @varPID. @varSID, @varQuantity END GO

Output Screen

EXEC dbo. CheckAndInsertParts 1, 1,| 30 91 % (1 row(s) affected)

As the amount is less than 75% of average amount of supplied pid = 1 so a new record inserted in the Orders table

0005 1 ql 2 9 1 6 8 7 1 3 4 9 1-1 p:1-2 5 4 3 2 1 52541 123456789 10-12