databaseSampleQueries.sql

-- SQL CRUD Operations for each entity in the database

-- CRUD Operations for User Table
-- Select Users who logged in after January 1, 2024
SELECT * FROM `User` WHERE `LastLogin` > '2024-01-01';
-- Insert a new user into the User table
INSERT INTO `User` (`Username`, `Password`, `Email`, `FirstName`, `LastName`, `DateOfBirth`) VALUES ('newuser', 'password123', 'new.user@example.com', 'New', 'User', '1992-02-02');
-- Update the email address of the user with UserID = 1
UPDATE `User` SET `Email` = 'updated.email@example.com' WHERE `UserID` = 1;
-- Delete the user with UserID = 2
DELETE FROM `User` WHERE `UserID` = 2;

-- CRUD Operations for Address Table
-- Select all addresses in Norway
SELECT * FROM `Address` WHERE `Country` = 'Norway';
-- Insert a new shipping address for the user with ID 3
INSERT INTO `Address` (`UserID`, `Street`, `City`, `State`, `PostalCode`, `Country`, `IsDefault`, `AddressType`) VALUES (3, 'Bogstadveien 30', 'Oslo', 'Oslo', '0355', 'Norway', 1, 'Shipping');
-- Change the default status of the address for UserID 1
UPDATE `Address` SET `IsDefault` = 0 WHERE `AddressID` = 1 AND `UserID` = 1;
-- Delete the address with AddressID = 3
DELETE FROM `Address` WHERE `AddressID` = 3;

-- CRUD Operations for Product Table
-- Select all products associated with BrandID 2 (Apple)
SELECT * FROM `Product` WHERE `BrandID` = 2;
-- Insert a new product, Google Nest Hub
INSERT INTO `Product` (`Name`, `Description`, `Price`, `StockQuantity`, `BrandID`, `CategoryID`, `SKU`, `IsActive`) VALUES ('Google Nest Hub', 'Smart display with Google Assistant.', 89.99, 200, 1, 1, 'SKU9015', 1);
-- Update the price of the product with ProductID = 10
UPDATE `Product` SET `Price` = 99.99 WHERE `ProductID` = 10;
-- Delete the product with ProductID = 5
DELETE FROM `Product` WHERE `ProductID` = 5;

-- CRUD Operations for Brand Table
-- Select all brands with IDs from 1 to 3
SELECT * FROM `Brand` WHERE `BrandID` BETWEEN 1 AND 3;
-- Insert a new brand, Asus
INSERT INTO `Brand` (`Name`, `Description`) VALUES ('Asus', 'Taiwanese multinational computer and phone hardware and electronics company.');
-- Update the description for the brand with BrandID = 7
UPDATE `Brand` SET `Description` = 'Updated brand description here' WHERE `BrandID` = 7;
-- Delete the brand with BrandID = 6
DELETE FROM `Brand` WHERE `BrandID` = 6;

-- CRUD Operations for Category Table
-- Select all categories in the database
SELECT * FROM `Category`;
-- Insert a new category for printers
INSERT INTO `Category` (`Name`, `Description`) VALUES ('Printers', 'Devices that produce a hard copy by transferring ink onto paper.');
-- Update the description of the category with CategoryID = 4
UPDATE `Category` SET `Description` = 'Updated category description' WHERE `CategoryID` = 4;
-- Delete the category with CategoryID = 3
DELETE FROM `Category` WHERE `CategoryID` = 3;

-- CRUD Operations for Order Table
-- Select orders where the total amount is greater than $1000
SELECT * FROM `Order` WHERE `TotalAmount` > 1000;
-- Insert a new order with details including userID, order date, total amount, and status ID
INSERT INTO `Order` (`UserID`, `OrderDate`, `TotalAmount`, `OrderStatusID`) VALUES (2, '2024-04-25', 1200.50, 3);
-- Change the order status of the order with OrderID = 1 to 'Shipped'
UPDATE `Order` SET `OrderStatusID` = 2 WHERE `OrderID` = 1;
-- Delete the order with OrderID = 3
DELETE FROM `Order` WHERE `OrderID` = 3;

-- CRUD Operations for OrderItem Table
-- Select all items associated with OrderID 1
SELECT * FROM `OrderItem` WHERE `OrderID` = 1;
-- Insert two units of product ID 7 to order ID 1 with a subtotal
INSERT INTO `OrderItem` (`OrderID`, `ProductID`, `Quantity`, `Subtotal`) VALUES (1, 7, 2, 3999.98);
-- Update the quantity and subtotal for product ID 7 in order ID 1
UPDATE `OrderItem` SET `Quantity` = 3, `Subtotal` = 5999.97 WHERE `OrderID` = 1 AND `ProductID` = 7;
-- Delete the order item entry for product ID 11 from order ID 2