Skip to content

Instantly share code, notes, and snippets.

@MP-C

MP-C/.sql

Last active March 10, 2023 22:00
Show Gist options
  • Save MP-C/8a8b33d7dc2bd39a6782e37b12c270b1 to your computer and use it in GitHub Desktop.
Save MP-C/8a8b33d7dc2bd39a6782e37b12c270b1 to your computer and use it in GitHub Desktop.
Download ZIP
ScriptMediumMySQLtrainning
Raw
.sql
--to create the database
CREATE DATABASE 'filmsAndActors';
USE 'filmsAndActors';
SHOW TABLES;
--to clean this tables on DataBase, in case you have been pratice
DROP TABLE castM;
DROP TABLE gender_film;
DROP TABLE people;
DROP TABLE film;
DROP TABLE gender;
--creating (again) the tables in order my needs
CREATE TABLE people (
`idPeople` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`age` INT NOT NULL,
PRIMARY KEY (idPeople)
);
--to check the builded table
DESCRIBE people;
--repeat
CREATE TABLE film (
`idFilm` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`year` INT NOT NULL,
`language` VARCHAR(20) NOT NULL,
PRIMARY KEY (idFilm)
);
DESCRIBE film;
CREATE TABLE gender (
`idGender` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`description` VARCHAR(20) NOT NULL,
PRIMARY KEY (idGender)
);
DESCRIBE gender;
CREATE TABLE castM (
`idPeople` INT,
`idFilm` INT,
FOREIGN KEY (idPeople) REFERENCES people(idPeople),
FOREIGN KEY (idFilm) REFERENCES film(idFilm),
ON DELETE CASCADE
);
DESCRIBE castM;
CREATE TABLE gender_film (
`idFilm` INT,
`idGender` INT,
FOREIGN KEY (idFilm) REFERENCES film(idFilm),
FOREIGN KEY (idGender) REFERENCES gender(idGender),
ON DELETE CASCADE
);
--to describe each table
DESCRIBE people;
DESCRIBE film;
DESCRIBE gender;
DESCRIBE castM;
DESCRIBE gender_film;
--juste in case you try some inserts, with this you will clean all the existing data, but the table remains
DELETE FROM castM;
DELETE FROM gender_film;
DELETE FROM people;
DELETE FROM film;
DELETE FROM gender;
--insert some random data
INSERT INTO people (idPeople, name, age)
VALUES (1, 'Ben Affleck', '40'),
(2, 'Jennifer Lawrence', '31'),
(3, 'Robert De Niro', '79'),
(4, 'Sandra Bullock', '57'),
(5, 'Nicole Kidman', '54'),
(6, 'Charlize Theron', '46'),
(7, 'Margot Robbie', '31'),
(8, 'Leonardo DiCaprio', '47'),
(9, 'Brad Pitt', '57'),
(10, 'George MacKay', '29'),
(11, 'Tom Cruise', '59'),
(12, 'Palaco', '33')
;
INSERT INTO film (idFilm, title, year, language)
VALUES (1, 'The Way Back', '2020', 'English'),
(2, 'Joy', '2015', 'English'),
(3, 'Bird Box', '2018', 'English'),
(4, 'Bombshell', '2019', 'English'),
(5, 'Once Upon a Time in Hollywood', '2018', 'English'),
(6, '1917', '2018', 'French'),
(7, 'Mission Impossible', '1990', 'English'),
(8, 'IT', '1989', 'Portuguese')
;
INSERT INTO gender (idGender, name, description)
VALUES (1, 'Drama', 'amazing'),
(2, 'Drama/Comedy', 'nominated'),
(3, 'Terror', 'suspense'),
(4, 'True Facts', 'nominated bibliography'),
(5, 'Biografia', 'the best'),
(6, 'Action/story', 'the best'),
(7, 'Action', 'its a classic')
;
INSERT INTO castM (idPeople, idFilm)
VALUES (1, 1),
(2, 2),
(3, 2),
(4, 3),
(5, 4),
(6, 4),
(7, 4),
(8, 5),
(9, 5),
(7, 5),
(10, 6),
(11, 7),
(12, 8)
;
INSERT INTO gender_film (idFilm, idGender)
VALUES (1, 1),
(2, 2),
(3, 3),
(4, 5),
(5, 4),
(6, 6),
(7, 7),
(8, 3)
;
--to show what we have done
SELECT * FROM people;
SELECT * FROM film;
SELECT * FROM gender;
SELECT * FROM castM;
/*
1)Get all movies of the genres 'Drama' or 'Horror', released between the years 2015 and 2020 (inclusive).
Before come with a result, the best way is practing with small queries, before reach the big one. In this way a lot of errors corrections and time will be saved
*/
--OK
SELECT title AS name_do_film
FROM film
WHERE year >= 2015
AND year <= 2020;
--OK
SELECT idGender, name
FROM gender
WHERE (gender.name = "Drama" OR gender.name = "Terror");
--OK
SELECT gender.idGender, gender.name
FROM gender, gender_film
WHERE (gender.name = "Drama" OR gender.name = "Terror")
AND gender.idGender = gender_film.idGender;
--OK
SELECT gender.idGender, gender.name, title AS name_do_film
FROM gender, gender_film, film
WHERE (gender.name = "Drama" OR gender.name = "Terror")
AND gender.idGender = gender_film.idGender
AND film.idFilm = gender_film.idFilm;
--OK FINAL
SELECT title AS name_do_film
FROM gender, gender_film, film
WHERE (year >= 2015 AND year <= 2020)
AND gender.idGender = gender_film.idGender
AND film.idFilm = gender_film.idFilm
AND gender.name = "Drama"
UNION
SELECT title AS name_do_film
FROM gender, gender_film, film
WHERE (year >= 2015 AND year <= 2020)
AND gender.idGender = gender_film.idGender
AND film.idFilm = gender_film.idFilm
AND gender.name = "Terror";
SELECT * FROM gender_film;
--ii. using other way
SELECT title AS name_do_film
FROM gender, gender_film, film
WHERE (gender.name = "Drama" OR gender.name = "Terror")
AND (year >= 2015 AND year <= 2020)
AND gender.idGender = gender_film.idGender
AND film.idFilm = gender_film.idFilm;
/*2) Present the title of the films and the average age of their cast, ordered in ascending order.
*/
--ok film e castM
SELECT title, castM.idCastM
FROM film, castM
WHERE film.idFilm = castM.idFilm
ORDER BY title ASC;
--ok age media
SELECT avg(age) AS avg_age
FROM people;
--OK para ID dos dois film e castM
SELECT idCastM, idFilm
FROM castM
GROUP BY idCastM
ORDER BY idFilm ASC;
--OK Because FK DEPENDES ON CastM
SELECT idCastM, film.title, film.idFilm
FROM castM, film
WHERE film.idFilm = castM.idFilm
GROUP BY idCastM
ORDER BY idFilm ASC;
--OU CONTARIO DA ANTERIOR ESTA VAI DAR SEMPRE IRRADP
SELECT film.idFilm, film.title, idCastM
FROM film, castM,
WHERE film.idFilm = castM.idFilm;
--ok titleS por castM
SELECT film.title, film.idFilm, idCastM
FROM film, castM
WHERE castM.idFilm = film.idFilm
GROUP BY castM.idCastM
ORDER BY idFilm ASC;
--OK
SELECT idCastM, film.title, film.idFilm
FROM castM, film
WHERE film.idFilm = castM.idFilm
GROUP BY idCastM
ORDER BY idFilm ASC;
--ok SE idCastM na tbalea +=========+
SELECT film.title, idCastM
FROM film, castM
WHERE castM.idFilm = film.idFilm
GROUP BY castM.idCastM
ORDER BY film.idFilm ASC;
--===================================+
--OK TOP!!! title por Groupos do CastM
SELECT film.title, castM.idFilm
FROM film, castM
WHERE castM.idFilm = film.idFilm
GROUP BY castM.idFilm
ORDER BY film.title ASC;
--OK TOP!!! Meidaages por castM
SELECT castM.idFilm, avg(people.age) AS Media_de_age
FROM castM, people
WHERE people.idPeople = castM.idPeople
GROUP BY castM.idFilm
ORDER BY castM.idFilm ASC;
--OK FINAL!!!
SELECT film.title, avg(people.age) AS Media_de_age
FROM film, castM, people
WHERE castM.idFilm = film.idFilm
AND people.idPeople = castM.idPeople
GROUP BY castM.idFilm
ORDER BY film.title ASC;
/*
3) Present the movie titles and the average age of their cast, sorted in ascending order.
*/
--ok
UPDATE film
SET title = 'the shadow'
WHERE year < 1990
AND title = 'ERRO';
--ok
SELECT gender.name, gender_film.idGender, film.title
FROM gender, gender_film, film
WHERE gender.name LIKE "%Terror%"
AND gender_film.idgender = gender.idGender
AND film.idFilm = gender_film.idGender;
--OK But it does nothing "...e.idgender)f);" needs to have an alias at the end, whatever we want it to be.
UPDATE film
SET film.title = 'Remover'
WHERE year < 1990
AND film.title IN
(SELECT * FROM
(SELECT film.title
FROM gender, gender_film, film
WHERE gender.name = '%Terror%'
AND gender_film.idGender = gender.idGender
AND film.idFilm = gender_film.idGender) AS remov);
SELECT * FROM film;
/*
4) To delete all films of gender “drama” been talked in “English”
*/
--ok
SELECT film.title
FROM gender, gender_film, film
WHERE gender.name LIKE '%Drama%'
AND gender_film.idGender = gender.idGender
AND film.idFilm = gender_film.idGender;
--ok
DELETE
FROM film
WHERE language = 'English';
--to desactivate "safe mode" without using "DELETE on cascade" function
SET SQL_SAFE_UPDATES = 0;
DELETE
FROM film
WHERE language = 'English'
AND EXISTS (SELECT * FROM
(SELECT film.title
FROM gender, gender_film, film
WHERE gender.name LIKE '%Drama%'
AND gender_film.idGender = gender.idGender
AND film.idFilm = gender_film.idGender) AS t);
DELETE
FROM film
WHERE language = 'English'
AND gender.name IN (SELECT * FROM
(SELECT film.title
FROM gender, gender_film, film
WHERE gender.name LIKE '%Drama%'
AND gender_film.idGender = gender.idGender
AND film.idFilm = gender_film.idGender) AS t);
DELETE
FROM film
WHERE language = 'English'
AND gender.name LIKE '%Drama%' EXISTS
(SELECT film.title
FROM gender, gender_film, film
AND gender_film.idGender = gender.idGender
AND film.idFilm = gender_film.idGender);
SELECT * FROM film;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment