Skip to content
Snippets Groups Projects
Select Git revision
  • 63f84a9224f0fdde862439bd3cef1f4cb628a78a
  • main default protected
2 results

databaseSampleQueries.sql

Blame
  • Code owners
    Assign users and groups as approvers for specific file changes. Learn more.
    databaseSampleQueries.sql 7.43 KiB
    -- 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