SQL queries

[EmEhPa]

Kommentera ideer för varje del typ?

Create at least five queries to your SQL design that are needed to implement the functionality of your application. You will probably need to create more than five queries to make your application functional, however we require some specific cases to be implemented and described in the assignment report. Focus on the more important queries and features of your application (i.e., there is no need to show how you insert documents in your various collections). Explain and motivate each query.

General guidelines for queries:

1. At least two of the five queries should deal with data from more than one table, i.e., you should use at least two multirelation queries

2. You should make use of SQL JOIN

3. You should make use of Aggregation and/or Grouping

4. You should make use of at least two of the following: a. Triggers b. Procedures c. Functions

DE TVÅ FÖRSTA ÄR KLARA!!

[EmEhPa]

This function is used if someone asks for a product in a specified size. It returns the number of total in.

Motivation: This query can be used to quickly retrieve information about a specific product and its availability in a certain size. It's particularly useful for inventory management systems or when users want to check the availability of a product before making a request or purchase. The InStock function encapsulates the logic for determining availability, making the query concise and easy to understand.

/* IS DONE
This query will only return the information for the product with the specified ID ('1') and the total count of items 
in stock for size 'L' for that product.
*/

DELIMITER //
CREATE FUNCTION in_stock_for_product (product CHAR(16), size CHAR(5))
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE NrIn INT;

    SELECT COUNT(*) INTO NrIn
    FROM 
        Items i
    LEFT JOIN
        Lendings l 
    ON 
        i.ItemID = l.ItemID AND l.ReturnDate IS NULL
    WHERE
        i.ProductID = product
    AND 
        (i.Size = size OR (i.Size IS NULL AND size IS NULL))
    AND 
        l.ItemID IS NULL;

    RETURN NrIn;
END//
DELIMITER ;

/*KALLAR PÅ DEN OCH VISAR INFO*/
SELECT 
    p.ProductID,
    p.NameOfProduct AS ProductName,
    InStockForProduct(p.ProductID, 'L') AS TotIn
FROM 
    Products p
where
    p.ProductID = '1';

[EmEhPa]

This query provides an overview of the product inventory by displaying the product ID, name, type, available quantity, and size count for each product and size combination. It helps managing inventory by showing all products both available and not and the quantities for different sizes.

# ==============This one is done================== #

SELECT 
    i.ProductID,
    p.NameOfProduct AS ProductName,
    p.Type AS ProductType, 
    i.Quantity,
    i.Size AS SizeCount
FROM 
    Products p
JOIN 
    (SELECT ProductID, Size, count(*) as Quantity from Items group by ProductID, Size)
AS
    i ON p.ProductID = i.ProductID
WHERE
    i.Quantity > 0;

[EmEhPa]

This view will retrieve the SSN and name of a specified user along with the total number of items they have borrowed through history as TotalLendings and are currently borrowing as currLendings. This help us analyze user since the query provides valuable insights into user borrowing behavior within the system.

Tracking the total number of borrowings for each user helps in monitoring user engagement with the system. It allows administrators or analysts to identify active users who frequently utilize the borrowing feature.

This is also a way for the users to see how many items they are lending if they are insecure. The query enables the generation of customized reports summarizing user borrowing activities. These reports can be used for managerial purposes, such as performance reviews, resource planning, or decision-making related to system improvements.

Overall, by combining user demographic information with borrowing statistics, the query offers a comprehensive view of user behavior and activity within the system, facilitating informed decision-making and resource management.

# ==============This one is done================== #

CREATE VIEW number_of_borrowes AS
SELECT
    u.SSN,
    u.Name,
    COUNT(DISTINCT tot.LendingID) AS TotalLendings,
    COUNT(DISTINCT curr.LendingID) AS currLendings
FROM
    Users u
LEFT JOIN
    Lendings tot ON u.SSN = tot.SSN
LEFT JOIN
    Lendings curr ON u.SSN = curr.SSN 
AND
    curr.ReturnDate IS NULL
GROUP BY
    u.SSN,
    u.Name
ORDER BY
    TotalLendings DESC;

DROP view number_of_borrowes;
select * from number_of_borrowes;

# It is implemented in python aswell

[EmEhPa]

This trigger directly checks for the existence of a record where the ItemID matches the item being borrowed and the ReturnDate is NULL, the trigger communicates its intention clearly: to prevent the borrowing of an item that is already borrowed and not yet returned.

DELIMITER //
CREATE TRIGGER before_borrowing
BEFORE INSERT ON Lendings
FOR EACH ROW
BEGIN
    -- Check if the item is already borrowed
    IF EXISTS (
        SELECT 1
        FROM Lendings
        WHERE ItemID = NEW.ItemID AND ReturnDate IS NULL
    ) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Item is already borrowed and unavailable';
    END IF;
END //
DELIMITER ;

[EmEhPa]

The function update_return_date is designed to automate the process of updating the return date for a lending transaction in the system. This ensures that the return date is accurately recorded without requiring manual intervention from users.

DELIMITER //
CREATE FUNCTION update_return_date(lending_id INT)
RETURNS INT
DETERMINISTIC
BEGIN
    UPDATE Lendings
    SET ReturnDate = CURRENT_DATE
    WHERE LendingID = lending_id;
    RETURN NULL;
END //
DELIMITER ;

select update_return_date(129);
select * from Lendings;

[EmEhPa]

DELIMITER //
CREATE TRIGGER update_equipment_level_of_use
AFTER UPDATE ON Lendings
FOR EACH ROW
BEGIN
    IF OLD.ReturnDate IS NULL AND NEW.ReturnDate IS NOT NULL THEN
        UPDATE Items
        SET LevelOfUse = (LevelOfUse + 0.10)
        WHERE ItemID = NEW.ItemID;
    END IF;

END //

DELIMITER ;
``