This is part of my job in Web Programming project. The database is not related to this NTP server project. It is only used for illustrating how to create database, create tables and manipulate data.

Enter Mysql console mode:
mysql

mysql>use onlineshop;

Create table: (****please note commands can be lowercase or uppercase, but tables name and field names must follow the lower/upper case when it is created)

CUSTOMERS {
    int id PK
    varchar username
    varchar password
    varchar email
    varchar address
    varchar phone
}

CREATE TABLE CUSTOMERS (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL,
phone VARCHAR(20) NOT NULL
);

SIZES {
    int id PK
    varchar size
}

CREATE TABLE SIZES (
id INT AUTO_INCREMENT PRIMARY KEY,
size INT NOT NULL
);

BRANDS {
    int id PK
    varchar name
}

CREATE TABLE BRANDS (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);

STYLES {
    int id PK
    varchar name
}

CREATE TABLE STYLES (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL
);

SHOES {
    int id PK
    varchar serial_no
    int brand_id FK
    int style_id FK
    int size_id FK        
    decimal price
    int inventory
}

CREATE TABLE SHOES (
id INT AUTO_INCREMENT PRIMARY KEY,
serial_no VARCHAR(255) NOT NULL,
brand_id INT NOT NULL,
style_id INT NOT NULL,
size_id INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
inventory INT NOT NULL,
FOREIGN KEY (brand) REFERENCES BRANDS(id),
FOREIGN KEY (style) REFERENCES STYLES(id),
FOREIGN KEY (size) REFERENCES SIZES(id)
);

ORDERS {
    int id PK
    int customer_id FK
    int shoe_id FK
    int quantity
    datetime order_date
    decimal total_price
}

CREATE TABLE ORDERS (
id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
shoe_id INT NOT NULL,
quantity INT NOT NULL,
order_date DATETIME NOT NULL,
total_price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES CUSTOMERS(id),
FOREIGN KEY (shoe_id) REFERENCES SHOES(id)
);

Show table structure:

mysql>DESCRIBE SIZES;
+——-+——+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+——-+——+——+—–+———+—————-+
| id | int | NO | PRI | NULL | auto_increment |
| size | int | NO | | NULL | |
+——-+——+——+—–+———+—————-+
2 rows in set (0.01 sec)

mysql>DESCRIBE SHOES;
+———–+—————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———–+—————+——+—–+———+—————-+
| id | int | NO | PRI | NULL | auto_increment |
| part_no | varchar(255) | NO | | NULL | |
| brand_id | int | NO | MUL | NULL | |
| style_id | int | NO | MUL | NULL | |
| size_id | int | NO | MUL | NULL | |
| price | decimal(10,2) | NO | | NULL | |
| inventory | int | NO | | NULL | |
+———–+—————+——+—–+———+—————-+
7 rows in set (0.00 sec)

mysql>DESCRIBE ORDERS;

+————-+—————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+————-+—————+——+—–+———+—————-+
| id | int | NO | PRI | NULL | auto_increment |
| customer_id | int | NO | MUL | NULL | |
| shoe_id | int | NO | MUL | NULL | |
| quantity | int | NO | | NULL | |
| order_date | datetime | NO | | NULL | |
| total_price | decimal(10,2) | NO | | NULL | |
+————-+—————+——+—–+———+—————-+
6 rows in set (0.01 sec)

Delete a table:

DROP TABLE SHOES;

Change field name of a table:

ALTER TABLE STYLES
CHANGE COLUMN name style VARCHAR(255) NOT NULL;

ALTER TABLE SHOES
CHANGE COLUMN style style_id INT NOT NULL;

ALTER TABLE SHOES
CHANGE COLUMN size size_id INT NOT NULL;

Add the AUTO_INCREMENT and PRIMARY KEY constraints to the id column after create the table:

mysql>ALTER TABLE user
MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

Insert record:

mysql> INSERT INTO CUSTOMERS (username, password, email, address, phone) VALUES (‘PeterParker’,’123456′,’peterparker@gmail.com’,’PortRush Road 1′,’0411478111′),
(‘JohnSmith’,’123456′,’johnsmith@gmail.com’,’Magill Road 100′,’0422789222′),
(‘BruceLee’,’123456′,’brucelee@gmail.com’,’Penfold Road 999′,’0433331234′),
(‘JackieChan’,’123456′,’jackiechan@gmail.com’,’George Road 456′,’0455556666′),
(‘StanLee’,’123456′,’stanlee@gmail.com’,’MaryRose Road 2′,’0487569451′);

INSERT INTO BRANDS (name) VALUES (‘Gucci’),(‘Prada’),(‘Versace’),(‘Chanel’)(‘Calvin Klein’);

INSERT INTO STYLES (name) VALUES
(‘Flats’),(‘Long Boots’),(‘Short Boots’),(‘Trainers’),(‘Pumps’),(‘Sandal’),(‘Clogs’);

INSERT INTO SIZES (size) VALUES (4),(5),(6),(7),(8),(9),(10),(11);

INSERT INTO SHOES (part_no, brand_id, style_id, size_id, price, inventory) VALUES
(‘2345678152’,1,1,5,300,3),
(‘3564821523’,2,2,6,350,5),
(‘4152879522’,3,3,7,400,10),
(‘4365897412’,4,4,7,600,2),
(‘4511278522’,5,5,7,650,5),
(‘5645654565’,1,6,8,200,7),
(‘5882587455’,2,7,9,330,4),
(‘6984575621’,1,1,5,300,3),
(‘7556443544’,3,1,10,430,9),
(‘6754545467’,4,2,4,290,3),
(‘6700897099’,5,3,5,470,8),
(‘9899078056’,1,4,6,375,11),
(‘1221245678’,2,5,4,400,9),
(‘8080865656’,3,6,10,500,7);

INSERT INTO ORDERS (customer_id, shoe_id, quantity, order_date, total_price)
VALUES (7, 10, 2, ‘2024-03-13’, 199.99),
(8, 11, 1, ‘2024-03-24’, 259.99),
(9, 12, 1, ‘2024-04-5’, 549.97),
(10, 11, 1, ‘2024-04-7’, 259.99),
(12, 13, 1, ‘2024-04-29’, 549.97),
(7, 16, 2, ‘2024-05-3’, 549.97),
(8, 1, 2, ‘2024-06-20’, 259.99),
(9, 2, 2, ‘2024-06-21’, 449.97),
(10, 3, 3, ‘2024-06-21’, 759.99),
(12, 4, 1, ‘2024-06-20’, 149.97);

INSERT INTO STUDENTS (fname, lname, birthdate, contactno, email, semester, course) VALUES
(‘Bill’,’Tang’,’2024-03-13′,’123456789′,’billtang@gmail.com’,’10’,’Computer Science’);
INSERT INTO STUDENTS (fname, lname, birthdate, contactno, email, semester, course) VALUES
(‘Felix’,’Tang’,’2023-03-13′,’123456789′,’felixtang@gmail.com’,’11’,’Computer Science’);

Show table data:

export sql (exit mysql first)

mysqldump –databases onlineshop > onlineshop.sql

import sql (exit mysql first)

mysql < onlineshop.sql

Write a SQL query to retrieve the name and email address of all users who bought a given item in the last 5 days.

WRONG:
SELECT c.username, c.email
FROM CUSTOMERS c
JOIN ORDERS o ON c.id = o.customer_id
JOIN SHOES s ON o.shoe_id = s.id
WHERE s.id = ?
AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL 5 DAY)

CORRECT:
SELECT c.username, c.email
FROM CUSTOMERS c
JOIN ORDERS o ON c.id = o.customer_id
WHERE o.order_date >= DATE_SUB(CURDATE(),INTERVAL 5 DAY);

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Write a SQL query to show all shoes with with serial_no, brandname, style, size, price and inventory

SELECT
s.part_no,
b.brandname,
st.style,
si.size,
s.price,
s.inventory
FROM
SHOES s
JOIN
BRANDS b ON s.brand_id = b.id
JOIN
STYLES st ON s.style_id = st.id
JOIN
SIZES si ON s.size_id = si.id;

EXTRA COMMANDS FOR REFERENCES ONLY

Add one more column;

mysql> alter table FEEDBACK add (
posted_date DATE NOT NULL);

Add many columns:

mysql> alter table user add (
user_username varchar(255),
user_first_name varchar(255),
user_last_name varchar(255),
user_phone varchar(255),
user_address varchar(255),
user_city varchar(255),
user_state varchar(255),
user_zip varchar(255));

Delete a record:

DELETE FROM CUSTOMERS WHERE id = 2;

Delect column:

mysql> ALTER TABLE CUSTOMERS DROP COLUMN username

Add 1 column:

mysql> ALTER TABLE CUSTOMERS add column logic BOOLEAN NOT NULL default FALSE;

Change data value:

mysql> UPDATE CUSTOMERS SET admin = 1 WHERE user_id = 2;

Exit Mysql command mode:

mysql> exit
Bye

CS-Student.com

Create web interface for NTP server – practicing MySQL (Part 4)