SQL introduction

SQL

-- create database
CREATE DATABASE dpsv1;
-- use database
USE dpsv1;
-- create tables
CREATE TABLE `user` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(255),
  `email` VARCHAR(255),
  `password` VARCHAR(255)
);
CREATE TABLE `order` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `orderNumber` VARCHAR(255),
  `orderDate` DATE,
  `deliveryTime` DATETIME,
  `userName` VARCHAR(255),
  `userEmail` VARCHAR(255),
  `userPhone` VARCHAR(255),
  `paymentToken` VARCHAR(255),
  `paid` BOOLEAN,
  `delivery` BOOLEAN,
  `deliveryAddress` TEXT,
  `deliveryFee` DECIMAL(10, 2),
  `serviceFee` DECIMAL(10, 2),
  `note` TEXT,
  `discount` DECIMAL(10, 2),
  `total` DECIMAL(10, 2)
);
CREATE TABLE `menu` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255),
  `shortDescr` TEXT,
  `longDescr` TEXT,
  `price` DECIMAL(10, 2),
  `sellingPrice` DECIMAL(10, 2),
  `image` VARCHAR(255),
  `prepType` VARCHAR(255),
  `onPromo` BOOLEAN,
  `category` VARCHAR(255)
);
CREATE TABLE `favorite` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `userEmail` VARCHAR(255)
);
CREATE TABLE `category` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255),
  `desc` TEXT,
  `img` VARCHAR(255)
);
CREATE TABLE `account` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `userEmail` VARCHAR(255)
);
-- Insert data into the user table
INSERT INTO `user` (`username`, `email`, `password`)
VALUES ('philani_ndaba', 'philanin@gmail.com', 'password123'),
       ('jane_smith', 'janesmith@gmail.com', 'password456'),
       ('jahephi_Sibiya', 'jahephisibiya@gmail.com', 'password789');
-- Insert data into order table
INSERT INTO `order` (`orderNumber`, `orderDate`, `deliveryTime`, `userName`, `userEmail`, `userPhone`, `paymentToken`, `paid`, `delivery`, `deliveryAddress`, `deliveryFee`, `serviceFee`, `note`, `discount`, `total`)
VALUES ('ORD123', '2024-03-30', '2024-03-31 12:00:00', 'philani_ndaba', 'philanin@gmail.com', '1234567890', 'token123', TRUE, TRUE, '123 Main St, City', 5.00, 2.50, 'No special instructions', 0.00, 25.00),
       ('ORD456', '2024-03-29', '2024-03-30 18:00:00', 'jane_smith', 'jane@gmail.com', '9876543210', 'token456', TRUE, FALSE, '456 Elm St, Town', 7.50, 3.00, 'Leave at doorstep', 1.50, 30.00),
       ('ORD789', '2024-03-28', '2024-03-30 19:00:00', 'jahephi_Sibiya', 'jahephisibiya@gmail.com', '7864754321', 'token789', TRUE, FALSE, '356 Yonic St, Town', 5.00, 2.50, 'Leave at doorstep', 0.00, 25.00);
-- Insert data into menu table
INSERT INTO `menu` (`title`, `shortDescr`, `longDescr`, `price`, `sellingPrice`, `image`, `prepType`, `onPromo`, `category`)
VALUES ('Pizza', 'Classic cheese pizza', 'Our classic pizza topped with mozzarella cheese', 10.00, 8.00, 'pizza.jpg', 'Bake', TRUE, 'Main Course'),
       ('Burger', 'Juicy beef burger', 'Delicious beef patty topped with fresh veggies', 8.00, 6.50, 'burger.jpg', 'Grill', FALSE, 'Main Course'),
       ('Pizza', 'Something meaty', 'something meaty pizza topped with extra mozzarella cheese', 10.00, 8.00, 'pizza.jpg', 'Bake', TRUE, 'Main Course');
-- Insert data into favorite table
INSERT INTO `favorite` (`userEmail`)
VALUES ('philanin@gmail.com.com'),
       ('janesmith@gmail.com'),
       ('jahephisibiya@gmail.com');
-- Insert data into category table
INSERT INTO `category` (`title`, `desc`, `img`)
VALUES ('Main Course', 'Main course dishes', 'main_course.jpg'),
       ('Dessert', 'Sweet treats to end your meal', 'dessert.jpg'),
       ('Main Course', 'Main course dishes', 'main_course.jpg');
-- delete account table
DROP TABLE `account`;
-- Adding a row
INSERT INTO menu (title, shortDescr, longDescr, price, sellingPrice, image, prepType, onPromo, category) 
VALUES ('Caesar Salad', 'Classic salad with Caesar dressing', 'Fresh romaine lettuce topped with Parmesan cheese and croutons, served with creamy Caesar dressing.', 9.99, 8.49, 'caesar_salad.jpg', 'Mixed', FALSE, 1);
-- Updating a row in the menu table
UPDATE `menu`
SET `price` = 10.99
WHERE `title` = 'caesar salad';
-- deleting a row from the favorite table
DELETE FROM `favorite`
WHERE `userEmail` = 'philanin@gmail.com';