MySQL (commands & problems) with examples, script, images, script (GitHub), grants, …

Create (PK), Insert, Update, Delete, Drop, Replace, Select | Union, Intersect, Except, Join| Between, <, >, =, |Having, Group By, Like, As, And| In, Not In, Exist, Distinct, Meta-Characters (%*)|Avg, Min, Max, Sum, Count | Increment|EXTRAS with images and scripts

How to download MySQL, install and test it ?
On YouTube, you can find a lot of videos, nevertheless, I found this one which have all information I needed to start it by myself. I hope this one could be also a good tip for what you are looking for (why should I re-write, explain and re-do it, if someone else has already done it and better ?). All content are in English, only the title is in French (why not ?). As a Software Engineer, I think it is always good to know more languages, don’t you ?

Statement syntax and meanings

Create (PK): Creates a new table with a primary key constraint
Insert: Inserts new rows of data into an existing table
Delete: Removes rows of data from an existing table
Drop: Deletes an existing table from the database
Replace: Inserts a new row into a table, or replaces an existing row if a matching primary key already exists
Select: Queries the database to retrieve data from one or more tables
Update: Modifies existing rows of data in a table
___________________________________________________________________

Except: Returns all rows from the first query that are not present in the second query
From: Specifies the table or tables from which to retrieve data in a query. <table_name_having_the_information_you_need> is mandatory and must contain at least one table. In case of multiple tables, each one must be separated using commas or joined using the JOIN keyword.
Intersect: Returns only the rows that are common between two queries
Join: Combines data from two or more tables based on a related column
Union: Combines the results of two or more SELECT statements into a single result set, with duplicates removed.
___________________________________________________________________

Between:
< : lower then
> : gratear then
= : equal
Meta-Characters :
%
* allows you to select all the fields from the specified table name, field Expression performs some computations on the specified fields such as adding numbers or putting together two string fields into one.
__________________________________________________________________

All: if nothing is specified, then ALL is assumed as the default;
And: “and”
As: alias
Distinct: optional keyword, that can be used to fine tune the results returned from the SQL SELECT statement;
Exist: to check if exists
Group By: is used to put together records that have the same field values.
Having: when working using the GROUP BY keyword, is a condition to specify criteria. Otherwise, helps you to make a filter form all data existing in the table;
In: ”inside”
Like: to compare
Not In: opposite form “In”;
Order By: is used to specify the sort order of the result set;
Where: is to specify a condition, and it is optional, it works like a criteria specification in the result set returned from the query.
___________________________________________________________________

Avg : average. Can be useful when used with others meta-characters, ex: salary, rates, sales, 50% of something, ….
Count : as it says: to count each “tuple” / line from the selected column.
Min : set to the minimum;
Max : the maximum;
Sum : to do math and only to sum
Increment : to increase some count, as it getting the data from the database;

So, from now on, it is possible to write this commands in a query form like this example below :

SELECT [DISTINCT|ALL ] { * | [fieldExpression [AS newName]} FROM tableName [alias] [WHERE condition][GROUP BY fieldName(s)]  [HAVING condition] ORDER BY fieldName(s)

The W3schools offers in their site, a reasonable quantity of exercises to practice all this concepts in an easy way. Plus, they show the answer if it is becoming hard in the beginning or if you need it.

To understand this concepts written above, you can find some questions, correspondents queries and results, without using JOIN, WITH, TOP or LIMIT, to not complicate all information. Once understand this concepts, it will be easier to use any query with those keys to obtain the desire result.
But firstable, you “MUST” understand all dependencies. The simple way is drawing down everything.

1. Get all movies of the genres ‘Drama’ or ‘Horror’, released between the years 2015 and 2020 (inclusive).

Display columns with the pairs: movie name and their respective genres.

i. Use the union operator:

SELECT titulo AS Nome_do_filme 
FROM genero, genero_filme, filme
WHERE (ano >= 2015 AND ano <= 2015 
AND genero.idGenero = genero_filme.idGenero 
AND filme.idFilme = genero_filme.idFilme 
AND genero.nome = “Drama”
UNION
SELECT titulo AS Nome_do_filme
FROM genero, genero_filme, filme
WHERE (ano >= 2015 AND ano <= 2020)
AND genero.idGenero = genero_filme.idGenero
AND filme.idFilme = genero_filme.idFilme
AND genero.nome = “Terror”;

ii. Use another method:

SELECT titulo AS Nome_do_filme 
FROM genero, genero_filme, filme 
WHERE (genero.nome = “Drama” OR genero.nome = “Terror”) 
AND (ano >= 2015 AND ano <= 2020) 
AND genero.idGenero = genero_filme.idGenero 
AND filme.idFilme = genero_filme.idFilme;

2.Present the movie titles and the average age of their cast, sorted in ascending order.

For this item, ascending order was considered alphabetical for the movie titles in the respective table, and not for the ages, according to the text interpretation.

SELECT filme.titulo, elenco.idFilme, avg(pessoa.idade) AS Media_de_idade FROM filme, elenco, pessoa 
WHERE elenco.idFilme = filme.idFilme 
AND pessoa.idPessoa = elenco.idPessoa 
GROUP BY elenco.idFilme 
ORDER BY filme.titulo ASC;

3. Change the movie names to “Remove” when they are of the “Horror” genre and are prior to the year 1990.

UPDATE filme SET filme.titulo = 'Remover'
WHERE idfilme IN (SELECT genero_filme.idfilme 
              FROM genero, genero_filme WHERE genero_filme.idFilme = filme.idFilme
              AND genero_filme.idGenero = genero.idGenero
              AND genero.nome = 'Terror'
              AND ano < 1990);

4)Delete from the movie table, the movies of the “Drama” genre spoken in “English”.

In the case of registering several movie types in the “genre” table for a single “name” (for example: “Drama / Romance” and still not wanting to modify all titles with the word “Drama” in the name, the operator “LIKE” was used “%”, but for being more comprehensive and allowing this modification in all cases. Otherwise, and as requested, a simpler command could have been used referring to “genre.name = ‘Horror’ ”.

DELETE
FROM filme
WHERE lingua = 'Ingles'
AND EXISTS (SELECT * FROM
               (SELECT filme.titulo
                FROM genero, genero_filme, filme
                WHERE genero.nome LIKE '%Drama%'
                AND genero_filme.idGenero = genero.idGenero
                AND filme.idFilme = genero_filme.idGenero) AS del);

FoR fUn, here directly to GitHub, it is possible to find this script, and much more commands to practice by your own. Feel free to play with.

And here to practice pgexercises.com/ with solutions.