SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
CREATE TABLE actor ( actor_id numeric NOT NULL , first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (actor_id) ); CREATE INDEX idx_actor_last_name ON actor(last_name); CREATE GENERATOR actor_generator; SET TERM ^ ; CREATE TRIGGER actor_before_trigger FOR actor ACTIVE BEFORE INSERT AS BEGIN IF (NEW.actor_id IS NULL) THEN NEW.actor_id = GEN_ID(actor_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER actor_before_update FOR actor ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table country -- CREATE TABLE country ( country_id SMALLINT NOT NULL, country VARCHAR(50) NOT NULL, last_update TIMESTAMP, PRIMARY KEY (country_id) ); ---DROP GENERATOR country_generator; CREATE GENERATOR country_generator; SET TERM ^ ; CREATE TRIGGER country_before_trigger FOR country ACTIVE BEFORE INSERT AS BEGIN IF (NEW.country_id IS NULL) THEN NEW.country_id = GEN_ID(country_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER country_before_update FOR country ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table city -- CREATE TABLE city ( city_id int NOT NULL, city VARCHAR(50) NOT NULL, country_id SMALLINT NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (city_id), CONSTRAINT fk_city_country FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE NO ACTION ON UPDATE CASCADE ); CREATE INDEX idx_fk_country_id ON city(country_id); --- DROP GENERATOR city_generator; CREATE GENERATOR city_generator; SET TERM ^ ; CREATE TRIGGER city_before_trigger FOR city ACTIVE BEFORE INSERT AS BEGIN IF (NEW.city_id IS NULL) THEN NEW.city_id = GEN_ID(city_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER city_before_update FOR city ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table address -- CREATE TABLE address ( address_id int NOT NULL, address VARCHAR(50) NOT NULL, address2 VARCHAR(50) DEFAULT NULL, district VARCHAR(20) NOT NULL, city_id INT NOT NULL, postal_code VARCHAR(10) DEFAULT NULL, phone VARCHAR(20) NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (address_id) ); CREATE INDEX idx_fk_city_id ON address(city_id); ALTER TABLE address ADD CONSTRAINT fk_address_city FOREIGN KEY (city_id) REFERENCES city (city_id) ON DELETE NO ACTION ON UPDATE CASCADE; --DROP GENERATOR city_generator; CREATE GENERATOR address_generator; SET TERM ^ ; CREATE TRIGGER address_before_trigger FOR address ACTIVE BEFORE INSERT AS BEGIN IF (NEW.address_id IS NULL) THEN NEW.address_id = GEN_ID(address_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER address_before_update FOR address ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table language -- CREATE TABLE language ( language_id SMALLINT NOT NULL , name CHAR(20) NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (language_id) ); ---DROP GENERATOR language_generator; CREATE GENERATOR language_generator; SET TERM ^ ; CREATE TRIGGER language_before_trigger FOR language ACTIVE BEFORE INSERT AS BEGIN IF (NEW.language_id IS NULL) THEN NEW.language_id = GEN_ID(language_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER language_before_update FOR language ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table category -- CREATE TABLE category ( category_id SMALLINT NOT NULL, name VARCHAR(25) NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (category_id) ); ---DROP GENERATOR category_generator; CREATE GENERATOR category_generator; SET TERM ^ ; CREATE TRIGGER category_before_trigger FOR category ACTIVE BEFORE INSERT AS BEGIN IF (NEW.category_id IS NULL) THEN NEW.category_id = GEN_ID(category_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER category_before_update FOR category ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table customer -- CREATE TABLE customer ( customer_id INT NOT NULL, store_id INT NOT NULL, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, email VARCHAR(50) DEFAULT NULL, address_id INT NOT NULL, active CHAR(1) DEFAULT 'Y' NOT NULL, create_date TIMESTAMP NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (customer_id), CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE CASCADE ); CREATE INDEX idx_customer_fk_store_id ON customer(store_id); CREATE INDEX idx_customer_fk_address_id ON customer(address_id); CREATE INDEX idx_customer_last_name ON customer(last_name); ---DROP GENERATOR customer_generator; CREATE GENERATOR customer_generator; SET TERM ^ ; CREATE TRIGGER customer_before_trigger FOR customer ACTIVE BEFORE INSERT AS BEGIN IF (NEW.customer_id IS NULL) THEN NEW.customer_id = GEN_ID(customer_generator, 1) ; NEW.last_update=current_timestamp; NEW.create_date=current_timestamp; END^ CREATE TRIGGER customer_before_update FOR customer ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table film -- CREATE TABLE film ( film_id int NOT NULL, title VARCHAR(255) NOT NULL, description BLOB SUB_TYPE TEXT DEFAULT NULL, release_year VARCHAR(4) DEFAULT NULL, language_id SMALLINT NOT NULL, original_language_id SMALLINT DEFAULT NULL, rental_duration SMALLINT DEFAULT 3 NOT NULL, rental_rate DECIMAL(4,2) DEFAULT 4.99 NOT NULL, length SMALLINT DEFAULT NULL, replacement_cost DECIMAL(5,2) DEFAULT 19.99 NOT NULL, rating VARCHAR(10) DEFAULT 'G', special_features VARCHAR(100) DEFAULT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (film_id), CONSTRAINT fk_film_language FOREIGN KEY (language_id) REFERENCES language (language_id) , CONSTRAINT fk_film_language_original FOREIGN KEY (original_language_id) REFERENCES language (language_id) ); ALTER TABLE film ADD CONSTRAINT CHECK_special_features CHECK(special_features is null or special_features like '%Trailers%' or special_features like '%Commentaries%' or special_features like '%Deleted Scenes%' or special_features like '%Behind the Scenes%'); ALTER TABLE film ADD CONSTRAINT CHECK_special_rating CHECK(rating in ('G','PG','PG-13','R','NC-17')); CREATE INDEX idx_fk_language_id ON film(language_id); CREATE INDEX idx_fk_original_language_id ON film(original_language_id); ---DROP GENERATOR film_generator; CREATE GENERATOR film_generator; SET TERM ^ ; CREATE TRIGGER film_before_trigger FOR film ACTIVE BEFORE INSERT AS BEGIN IF (NEW.film_id IS NULL) THEN NEW.film_id = GEN_ID(film_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER film_before_update FOR film ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table film_actor -- CREATE TABLE film_actor ( actor_id INT NOT NULL, film_id INT NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (actor_id,film_id), CONSTRAINT fk_film_actor_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT fk_film_actor_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE ); CREATE INDEX idx_fk_film_actor_film ON film_actor(film_id); CREATE INDEX idx_fk_film_actor_actor ON film_actor(actor_id) ; SET TERM ^ ; CREATE TRIGGER film_actor_before_trigger FOR film_actor ACTIVE BEFORE INSERT AS BEGIN NEW.last_update=current_timestamp; END^ CREATE TRIGGER film_actor_before_update FOR film_actor ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table film_category -- CREATE TABLE film_category ( film_id INT NOT NULL, category_id SMALLINT NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (film_id, category_id), CONSTRAINT fk_film_category_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE, CONSTRAINT fk_film_category_category FOREIGN KEY (category_id) REFERENCES category (category_id) ON DELETE NO ACTION ON UPDATE CASCADE ); CREATE INDEX idx_fk_film_category_film ON film_category(film_id); CREATE INDEX idx_fk_film_category_category ON film_category(category_id); SET TERM ^ ; CREATE TRIGGER film_category_before_trigger FOR film_category ACTIVE BEFORE INSERT AS BEGIN NEW.last_update=current_timestamp; END^ CREATE TRIGGER film_category_before_update FOR film_category ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table film_text -- CREATE TABLE film_text ( film_id SMALLINT NOT NULL, title VARCHAR(255) NOT NULL, description BLOB SUB_TYPE TEXT, PRIMARY KEY (film_id) ); -- -- Table structure for table inventory -- CREATE TABLE inventory ( inventory_id INT NOT NULL, film_id INT NOT NULL, store_id INT NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (inventory_id), CONSTRAINT fk_inventory_film FOREIGN KEY (film_id) REFERENCES film (film_id) ON DELETE NO ACTION ON UPDATE CASCADE ); CREATE INDEX idx_fk_film_id ON inventory(film_id); CREATE INDEX idx_fk_film_id_store_id ON inventory(store_id,film_id); ---DROP GENERATOR inventory_generator; CREATE GENERATOR inventory_generator; SET TERM ^ ; CREATE TRIGGER inventory_before_trigger FOR inventory ACTIVE BEFORE INSERT AS BEGIN IF (NEW.inventory_id IS NULL) THEN NEW.inventory_id = GEN_ID(inventory_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER inventory_before_update FOR inventory ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table staff -- CREATE TABLE staff ( staff_id SMALLINT NOT NULL, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, address_id INT NOT NULL, picture BLOB DEFAULT NULL, email VARCHAR(50) DEFAULT NULL, store_id INT NOT NULL, active SMALLINT DEFAULT 1 NOT NULL, username VARCHAR(16) NOT NULL, password VARCHAR(40) DEFAULT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (staff_id), CONSTRAINT fk_staff_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE NO ACTION ON UPDATE CASCADE ); CREATE INDEX idx_fk_staff_store_id ON staff(store_id); CREATE INDEX idx_fk_staff_address_id ON staff(address_id); ---DROP GENERATOR inventory_generator; CREATE GENERATOR staff_generator; SET TERM ^ ; CREATE TRIGGER staff_before_trigger FOR staff ACTIVE BEFORE INSERT AS BEGIN IF (NEW.staff_id IS NULL) THEN NEW.staff_id = GEN_ID(staff_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER staff_before_update FOR staff ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table store -- CREATE TABLE store ( store_id INT NOT NULL, manager_staff_id SMALLINT NOT NULL, address_id INT NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (store_id), CONSTRAINT fk_store_staff FOREIGN KEY (manager_staff_id) REFERENCES staff (staff_id) , CONSTRAINT fk_store_address FOREIGN KEY (address_id) REFERENCES address (address_id) ); CREATE INDEX idx_store_fk_manager_staff_id ON store(manager_staff_id); CREATE INDEX idx_fk_store_address ON store(address_id); ---DROP GENERATOR store_generator; CREATE GENERATOR store_generator; SET TERM ^ ; CREATE TRIGGER store_before_trigger FOR store ACTIVE BEFORE INSERT AS BEGIN IF (NEW.store_id IS NULL) THEN NEW.store_id = GEN_ID(store_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER store_before_update FOR store ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- -- Table structure for table payment -- CREATE TABLE payment ( payment_id int NOT NULL, customer_id INT NOT NULL, staff_id SMALLINT NOT NULL, rental_id INT DEFAULT NULL, amount DECIMAL(5,2) NOT NULL, payment_date TIMESTAMP NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (payment_id), CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) , CONSTRAINT fk_payment_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) ); CREATE INDEX idx_fk_staff_id ON payment(staff_id); CREATE INDEX idx_fk_customer_id ON payment(customer_id); ---DROP GENERATOR payment_generator; CREATE GENERATOR payment_generator; SET TERM ^ ; CREATE TRIGGER payment_before_trigger FOR payment ACTIVE BEFORE INSERT AS BEGIN IF (NEW.payment_id IS NULL) THEN NEW.payment_id = GEN_ID(payment_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER payment_before_update FOR payment ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ CREATE TABLE rental ( rental_id INT NOT NULL, rental_date TIMESTAMP NOT NULL, inventory_id INT NOT NULL, customer_id INT NOT NULL, return_date TIMESTAMP DEFAULT NULL, staff_id SMALLINT NOT NULL, last_update TIMESTAMP NOT NULL, PRIMARY KEY (rental_id), CONSTRAINT fk_rental_staff FOREIGN KEY (staff_id) REFERENCES staff (staff_id) , CONSTRAINT fk_rental_inventory FOREIGN KEY (inventory_id) REFERENCES inventory (inventory_id) , CONSTRAINT fk_rental_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ); CREATE INDEX idx_rental_fk_inventory_id ON rental(inventory_id); CREATE INDEX idx_rental_fk_customer_id ON rental(customer_id); CREATE INDEX idx_rental_fk_staff_id ON rental(staff_id); CREATE UNIQUE INDEX idx_rental_uq ON rental (rental_date,inventory_id,customer_id); ---DROP GENERATOR payment_generator; CREATE GENERATOR rental_generator; SET TERM ^ ; CREATE TRIGGER rental_before_trigger FOR rental ACTIVE BEFORE INSERT AS BEGIN IF (NEW.rental_id IS NULL) THEN NEW.rental_id = GEN_ID(rental_generator, 1) ; NEW.last_update=current_timestamp; END^ CREATE TRIGGER rental_before_update FOR rental ACTIVE BEFORE UPDATE AS BEGIN NEW.last_update=current_timestamp; END^ SET TERM ; ^ -- FK CONSTRAINTS ALTER TABLE customer ADD CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE; ALTER TABLE inventory ADD CONSTRAINT fk_inventory_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE; ALTER TABLE staff ADD CONSTRAINT fk_staff_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE NO ACTION ON UPDATE CASCADE; ALTER TABLE payment ADD CONSTRAINT fk_payment_rental FOREIGN KEY (rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE; -- -- View structure for view customer_list -- CREATE VIEW customer_list AS SELECT cu.customer_id AS ID, cu.first_name||' '||cu.last_name AS name, a.address AS address, a.postal_code AS zip_code, a.phone AS phone, city.city AS city, country.country AS country, case when cu.active=1 then 'active' else '' end AS notes, cu.store_id AS SID FROM customer AS cu JOIN address AS a ON cu.address_id = a.address_id JOIN city ON a.city_id = city.city_id JOIN country ON city.country_id = country.country_id; -- -- View structure for view film_list -- CREATE VIEW film_list AS SELECT film.film_id AS FID, film.title AS title, film.description AS description, category.name AS category, film.rental_rate AS price, film.length AS length, film.rating AS rating, actor.first_name||' '||actor.last_name AS actors FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id LEFT JOIN film ON film_category.film_id = film.film_id JOIN film_actor ON film.film_id = film_actor.film_id JOIN actor ON film_actor.actor_id = actor.actor_id; -- -- View structure for view staff_list -- CREATE VIEW staff_list AS SELECT s.staff_id AS ID, s.first_name||' '||s.last_name AS name, a.address AS address, a.postal_code AS zip_code, a.phone AS phone, city.city AS city, country.country AS country, s.store_id AS SID FROM staff AS s JOIN address AS a ON s.address_id = a.address_id JOIN city ON a.city_id = city.city_id JOIN country ON city.country_id = country.country_id; -- -- View structure for view sales_by_store -- CREATE VIEW sales_by_store AS SELECT s.store_id ,c.city||','||cy.country AS store ,m.first_name||' '||m.last_name AS manager ,SUM(p.amount) AS total_sales FROM payment AS p INNER JOIN rental AS r ON p.rental_id = r.rental_id INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id INNER JOIN store AS s ON i.store_id = s.store_id INNER JOIN address AS a ON s.address_id = a.address_id INNER JOIN city AS c ON a.city_id = c.city_id INNER JOIN country AS cy ON c.country_id = cy.country_id INNER JOIN staff AS m ON s.manager_staff_id = m.staff_id GROUP BY s.store_id , c.city||','||cy.country , m.first_name||' '||m.last_name; -- -- View structure for view sales_by_film_category -- -- Note that total sales will add up to >100% because -- some titles belong to more than 1 category -- CREATE VIEW sales_by_film_category AS SELECT c.name AS category , SUM(p.amount) AS total_sales FROM payment AS p INNER JOIN rental AS r ON p.rental_id = r.rental_id INNER JOIN inventory AS i ON r.inventory_id = i.inventory_id INNER JOIN film AS f ON i.film_id = f.film_id INNER JOIN film_category AS fc ON f.film_id = fc.film_id INNER JOIN category AS c ON fc.category_id = c.category_id GROUP BY c.name; -- -- View structure for view actor_info -- /* CREATE VIEW actor_info AS SELECT a.actor_id, a.first_name, a.last_name, GROUP_CONCAT(DISTINCT CONCAT(c.name, ': ', (SELECT GROUP_CONCAT(f.title ORDER BY f.title SEPARATOR ', ') FROM sakila.film f INNER JOIN sakila.film_category fc ON f.film_id = fc.film_id INNER JOIN sakila.film_actor fa ON f.film_id = fa.film_id WHERE fc.category_id = c.category_id AND fa.actor_id = a.actor_id ) ) ORDER BY c.name SEPARATOR '; ') AS film_info FROM sakila.actor a LEFT JOIN sakila.film_actor fa ON a.actor_id = fa.actor_id LEFT JOIN sakila.film_category fc ON fa.film_id = fc.film_id LEFT JOIN sakila.category c ON fc.category_id = c.category_id GROUP BY a.actor_id, a.first_name, a.last_name; */ -- TO DO PROCEDURES -- TO DO TRIGGERS
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear