SQL Queries, Stored Procedures, and Functions for Database Systems

This assignment solution focuses on SQL queries and stored procedures within a database context. It begins with a series of SQL queries (12 in total), providing the queries themselves and their corresponding outputs. The queries cover various aspects of database interaction, including SELECT statements with JOINs, WHERE clauses, GROUP BY, and aggregate functions like SUM, AVG, and COUNT. The solution also includes a section on SQL stored procedures and functions, explaining their purpose, advantages, and how they are used to update data and integrate procedural logic within SQL statements. It discusses the history and evolution of stored procedures in different database systems like Oracle, Microsoft SQL Server, and PostgreSQL, and concludes with a brief bibliography of relevant sources.

Running head: SQL STORED PROCEDURES
SQL Stored Procedures
Name of the Student:
Name of the University:
Author Note

1
SQL STORED PROCEDURES
Part I: Entity-Relationship Diagram
Part II: SQL Queries
Query 1
SELECT `Description` FROM `item` WHERE `Category`= "Book" AND `Price` < 5
The output for the query is
Query 2

2
SQL STORED PROCEDURES
SELECT item.Description FROM item JOIN buys ON item.ItemID = buys.ItemID JOIN user ON
user.UserID = buys.UserID WHERE user.UserName = "Smith" GROUP BY item.Description
The output of the query is
Query 3
SELECT SUM (buys.price), user.UserName FROM user Join buys on user.UserID = buys.UserID
GROUP by Buys.UserID HAVING SUM (buys.price) > 1000
The output of the following query is
Query 4
SELECT AVG(`Price`), Category FROM `item` GROUP BY Category
The output of the following query is
Query 5
select item.Description from item Join rate on item.ItemID = rate.ItemID where item.ItemID not in (select
ItemID from rate)
This query does not provide any output as there are not value as such in the database.
Query 6

3
SQL STORED PROCEDURES
SELECT COUNT(buys.`UserID`), Item.ItemID, item.Description
FROM `buys`
JOIN item on buys.ItemID = item.ItemID
GROUP BY buys.ItemID LIMIT 1
The following output is provided by the query
Query 7
SELECT * FROM `item` WHERE `Price`> 100 GROUP BY `Description` ORDER BY `Category`
DESC
The output of the query is
Query 8
SELECT COUNT(UserID)>1, ItemID FROM buys GROUP BY `ItemID`
The output of the following query is

4
SQL STORED PROCEDURES
Query 9
SELECT User.UserName, item.Description
FROM user
JOIN buys on user.UserID = buys.UserID
JOIN item on buys.ItemID = item.ItemID
WHERE item.price < buys.price
The output of the query is
Query 10
SELECT User.UserName, item.Description FROM user JOIN buys on user.UserID = buys.UserID JOIN
item on buys.ItemID = item.ItemID Where buys.UserID Not In (SELECT rate.UserID FROM user JOIN
rate on user.UserID = rate.UserID JOIN item on rate.ItemID = item.ItemID)
The output of the query is

5
SQL STORED PROCEDURES
Query 11
SELECT AVG(`Ratings`), COUNT(`Ratings`)>1 FROM rate GROUP BY ItemID
The following output is provided by the query
Query 12
SELECT t1.UserID, t1.ItemID, t1.averageRating
FROM(
SELECT t1.UserID, t1.ItemID, t2.averageRating
FROM(
SELECT c.UserID AS UserID, p.ItemID AS ItemID
FROM User c
CROSS JOIN Item p
WHERE (c.UserID, p.ItemID) NOT IN (SELECT UserID, ItemID FROM buys)
ORDER BY c.UserID) t1
JOIN(
SELECT p.ItemID AS ItemID, AVG(r.ratings) AS averageRating
FROM Item p
LEFT JOIN rate r ON r.ItemID = p.ItemID

6
SQL STORED PROCEDURES
GROUP BY p.ItemID) t2 ON t2.ItemID = t1.ItemID) t1
JOIN(
SELECT t1.UserID, MAX(t2.averageRating) AS maxRating
FROM(
SELECT c.UserID AS UserID, p.ItemID AS ItemID
FROM User c
CROSS JOIN Item p
WHERE (c.userID, p.ItemID) NOT IN (SELECT userID, ItemID FROM buys)
ORDER BY c.userID) t1
JOIN(
SELECT p.itemID AS ItemID, AVG(r.ratings) AS averageRating
FROM Item p
LEFT JOIN rate r ON r.ItemId = p.ItemId
GROUP BY p.ItemID) t2 ON t2.ItemID = t1.ItemID
GROUP BY t1.UserID) t2 ON t2.UserID = t1.UserID AND t2.maxRating = t1.averageRating
ORDER BY t1.UserID;
The output of the query is provided below

7
SQL STORED PROCEDURES
Part III: SQL Stored Procedures and Functions
The SQL stored procedures is designed in such a way that the data in the databases can be
updated very easily. Additionally, the language is declarative and the DML statements do not require the
control flow constructions. The stored procedural languages are generally used in the database servers.
The server stored procedure queries can be saved in the system so that the queries are not required to run
repeatedly and the system can call the queries whenever required. Thus this helps the coder in the call the
stored functions whenever they require in their code. This language provides the user the option of
combining the data manipulation language with the procedural languages so that some non-database
action can be performed very easily by the users. It is also know that there are options for the relational
database is provided in this. The stored procedures enable procedural logic in the sql statements and
storage of temporary data that can be forwarded for the next query is available in this language. The
functions enable the aggregation and timestamp abilities for the user. This can be used over all the
RDBMS platforms. The oracle company was the first to integrate the SQL and logical procedures through
the PL/SQL queries. The Microsoft SQL Server made use of the Transact-SQL. Postgre SQL was
developed at first by the Oracle but was later changed to the PL/PSM. In addition to this, the SQL:2003
standard was developed. Stored procedures, triggers and user defined functions work on MySQL without
the need to modify.

8
SQL STORED PROCEDURES
Bibliography
Freedman, C., Ismert, E. and Larson, P.Å., 2014. Compilation in the Microsoft SQL Server Hekaton
Engine. IEEE Data Eng. Bull., 37(1), pp.22-30.
Mills, R.J., 2017. Using Analogical Problem Construction As An Advance Organizer To Teach Advanced
Database (SQL) Nomenclature. The Review of Business Information Systems (Online), 21(1), p.1.