SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE customers ( id INT AUTO_INCREMENT PRIMARY KEY, first_name NVARCHAR(15) NOT NULL, last_name NVARCHAR(15) NOT NULL, phone_number NVARCHAR(11) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); CREATE TABLE rental_details ( id INT AUTO_INCREMENT PRIMARY KEY, rental_date DATETIME DEFAULT CURRENT_TIMESTAMP, return_date DATETIME DEFAULT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); CREATE TABLE shoes ( id INT AUTO_INCREMENT PRIMARY KEY, size DECIMAL(3,1) NOT NULL, is_rentaled BOOLEAN NOT NULL DEFAULT FALSE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); CREATE TABLE rentals ( customer_id INT NOT NULL, rental_detail_id INT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (customer_id, rental_detail_id), FOREIGN KEY (customer_id) REFERENCES customers (id), FOREIGN KEY (rental_detail_id) REFERENCES rental_details (id) ); CREATE TABLE rental_shoes ( rental_detail_id INT NOT NULL, shoes_id INT NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (rental_detail_id, shoes_id), FOREIGN KEY (rental_detail_id) REFERENCES rental_details (id), FOREIGN KEY (shoes_id) REFERENCES shoes (id) ); INSERT INTO customers (first_name, last_name, phone_number) VALUES ('Taro', 'Tanaka', '09012345678'); SELECT * FROM customers; DELIMITER // CREATE PROCEDURE shoes_insert(IN i INT, IN s DECIMAL(3,1)) BEGIN DECLARE count INT DEFAULT 0; WHILE count < i DO SET count = count + 1; INSERT INTO shoes (size) VALUES (s); END WHILE; END // DELIMITER ; CALL shoes_insert(6, 20.0); CALL shoes_insert(10, 21.0); CALL shoes_insert(12, 22.0); CALL shoes_insert(15, 23.0); CALL shoes_insert(15, 23.5); CALL shoes_insert(15, 24.0); CALL shoes_insert(18, 24.5); CALL shoes_insert(18, 25.0); CALL shoes_insert(18, 25.5); CALL shoes_insert(20, 26.0); CALL shoes_insert(15, 26.5); CALL shoes_insert(15, 27.0); CALL shoes_insert(12, 28.0); SELECT * FROM shoes; SELECT size, count(size) FROM shoes WHERE is_rentaled = False GROUP BY size;