Solved SQL exercises

In my browsing of the web, I found these 'Solved SQL Exercises'

ofBorja SotomayorIn which, over the course of 9 chapters, each of them with
a basic design of a database, we are presented with different queries,
both in selection and action, and Borja offers us his solution in language
SQL.

They constitute a very good practice in creating queries, which do not require
the domain of SQL to be resolved, as we can solve them with the
graphic designer of queries that incorporates any database from
desktop (Access, [Link] Base, etc).

They can also serve as a good foundation of ideas to develop different

exercises in a broader sense, as I have done, for example,
inResearchers, Faculties, and Teams.

The chapters, each with the structure of tables and relationships of a database
data are:

The Computer Store

2. Employees
3. The Warehouses
4. Movies and Theaters
5. The Directors
6. Parts and Suppliers
7. The Scientists
8. The Department Stores
9. The Researchers

I hope you enjoy them. As soon as I can, I will upload databases.

Access or Base format, with enough sample data to be able to perform
the exercises and check their results without the need to type.

Note: In the design of databases, you will see that the data types
used, differ from those of Access. The equivalence is quite simple:

Integer
char -> Text
Text
nvarchar -> Text

Enjoy them.
Resolution of exercises

/*

EXERCISES

Proposals

**********************************************************************
*

EXERCISES

**********************************************************************
**

Update the unit price of products in the category

MEAT PRODUCTS, increasing them by 10% (1)

*/

select * from products;

updateproducts

setPriceUnit = (priceUnit * 0.1) + priceUnit;

/*

2. Update the cellular phone of the supplier whose contact is

MANUEL ANDRADE, with the value 099010291 (2)

*/

select * from suppliers;

update suppliers

0990101291

wherecontacto='MANUEL ANDRADE';

/*

3. Delete the product YOGURT OF

FLAVORS (3)

*/

select * from products;

delete from products

FLAVORED YOGURT

/*

4. Make the following inquiries:

4.1 Show all orders: the order id, the last name, and first name
of the employee who attended to her (4)

the name of the client company

*/

SELECT * FROM ORDERS;

select * from employees;

select * from customers;

[Link]
orden

from orders ord, employees emp, clients cli

where [Link] = [Link] and [Link] = [Link];

/*

4.2 Show the total sum of each type of product ordered in all
the orders. (5)

*/

select * from order_details;

select product oid, sum(quantity) as 'Total Sum'

from order_details

group by product ID;

/*

4.3 Show the number of orders handled by each employee,

including those with 0 orders. (6)

*/

select * from employees;

select * from orders;

select name as 'Name', surname as 'Surname', count([Link]
Order number

from employees emp

left join orders ord

[Link]=[Link]

group by name, surname;

/*

4.4 Show the providers and the total amount of money sold in the
products from that supplier. (7)

*/

select * from suppliers;

select * from products;

select * from order_details;

[Link]
Add money

from suppliers prov, products prod, order_details d

[Link]=[Link]=[Link]
rid
group by name of province;

/*

5. Execute the following stored procedure.

5.1 Write a stored procedure that takes a parameter as input

supplier code and (8)

returns the number of orders that include products from

this provider.

*/

DROP LANGUAGE IF EXISTS plpgsql;

CREATE LANGUAGE plpgsql;

select * from products;

select * from orders;

select * from order_details;

create or replace function num_ordene_proveedor(idprooint) returns int

if

as$$

declare

numint;

begin

select count([Link]) into num from products p join

order_details dor

on([Link]=[Link])joinorders ordon([Link]
enid=[Link])
where provider id = id proo

group by provider ID;

returnnum;

end;

$$LANGUAGEplpgsql;

selectnum_ordene_proveedor(10)

/*

5.2 Write a stored procedure that receives a parameter

name of a category and (9)

returns the product code of that category that has the most
units sold.

*/

select * from categories;

select * from products;

select * from order_details;

create or replace function mas_vendido(nomCatcharacter(50)) returns i

integer

as$$

declare

numint;

begin
 SELECT [Link], SUM(cantidad) INTO NUM FROM productos p
join categories on ([Link] = [Link])

join order_details doron([Link]=[Link])

[Link]=nomCat

group by description, [Link]

order by sum DESC

limit1;

returnnum;

end;

PL/pgSQL

select_sold('MEAT')
UF1888-E4: SQL Exercise
Objectives:

Put into practice the SQL knowledge acquired.

Statement:

The exercises to be performed will retrieve data from two tables:

the employee table (TEMPLE1)

the department of (TDEPAR2)

Data content

The real names and characteristics of the columns are shown in the following
table:

Employee Table

nuempl name i surname dept telephone fetching ctr nv s

10 CRISTINA I HERNANDEZ A00 2070 January 1, 1965 66 18 M

20 MICHAEL L TAPIA B01 2140 October 10, 197361 18 H

30 SALOME A KEMPES C01 2080 April 5, 1975 60 20 M

50 JUAN B GARRIDO E01 1507 August 17, 194958 16 H

60 ISIDRO F SUAREZ D11 2290 September 14, 1973

55 16 H

70 EVA D BRIDGE D21 2400 30.09.1980 56 16 M

90 ELENA W HERRANZ E11 2230 August 15, 197055 16 M

100 THOMAS Q SOLAR E21 2170 June 19, 1980 54 14 H

nuempl name I last name dept telephone fetching ctr nv s

110 VICENTE G LUENGO A00 1140 May 16, 1958 58 19 H

120 SIMON - OTERO A00 2022 December 5, 1963

58 14 H

130 DOLORES M QUINTANA C01 2140 July 28, 1971 55 16 M

140 HELIODE A GRANDCHILD C01 2210 December 15, 1976

56 18 M

150 BRUNO - ALVAREZ D11 2250 February 12, 1971

55 16 H

160 ELISA R PINTO D11 1307 November 10, 1977

54 17 M

170 MATEO J YARZA D11 2050 September 15, 1978

54 16 H

180 MARINA S SANDOVAL D11 1050 07.07.1973 53 17 M

190 JAIME H WALKER D11 2100 July 27, 1974 53 16 H

200 DAVID - GOOD MORNING D11 2340 March 3, 1966 55 16 H

210 Wenceslaus T Jury D11 2310 April 11, 1979 52 17 H

220 JIMENA K LUQUE D11 2300 August 29, 196855 18 M

230 JAIME J JIMENEZ D21 1080 November 21, 1966

53 14 H

240 Savior M MARTINEZ D21 2260 December 5, 1979

55 17 H

250 DANIEL S SIERRA D21 2357 October 30, 196952 15 H

260 SUSANA P JUNQUERA D21 2302 September 11, 1975

52 16 M

270 MARIA L PEREZ D21 2217 September 30, 1980

55 15 M
 nuempl name I surname dept phone fetching ctr nv s

280 GRACE R SANCHEZ E11 2007 March 24, 1967 54 17 M

290 JUAN R PALACES E11 2192 30.05.1980 42 12 H

300 PEDRO I SIERRA E11 3522 June 19, 1972 48 14 H

310 MATILDE F SERNA E11 2130 September 12, 1964

43 12 M

320 RAMON V MORAN E21 7112 July 7, 1965 52 16 H

330 WILLY - LERMA E21 1132 February 23, 1976

55 14 H

340 JAVIER R GIL E21 1162 May 5, 1947 54 16 H

999 ANGEL E MALDONADO E11 ---- March 22, 1988 69 20 H

TDEPAR2 (Department Table)

numdep name of the department

A00 PROCESS CENTER 10

B01 PLANNING 20

C01 INFORMATION 30

D01 DEVELOPMENT 200

D11 MANUFACTURING 60

D21 ADMINISTRATION 70

E01 SERVICES 50
 number of dependencies name of the department

E11 OPERATIONS 90

E21 SOFTWARE 100

Resolution

Creation of the tables

CREATE TABLE temple1 (
nuempl CHAR(6) NOT NULL,
name CHAR(12) NOT NULL,
initial CHAR(1) NOT NULL,
last name CHAR(15) NOT NULL,
dept CHAR(3) NOT NULL,
tlfn CHAR(4)
fetching DATE NOT NULL,
codtra SMALLINT NOT NULL,
niveduc SMALLINT NOT NULL,
sex CHAR(1) NOT NULL
fechnac DATE NOT NULL,
salary DECIMAL(9,2) NOT NULL
);
CREATE TABLE tdepar2 (
numdep CHAR(3) NOT NULL,
namedep CHAR(36) NOT NULL
numdirec CHAR(6) NOT NULL
);

Data insertion
SET DATESTYLE TO 'European';
INSERT INTO temple1 ( nuempl, name, initial, last name, dept, phone, date)
codtra
VALUES ( 10, 'CRISTINA', 'I', 'HERNANDEZ', 'A00', 2070,
01.01.1965
( 20, 'MIGUEL', 'L', 'TAPIA', 'B01', 2140, '10.10.1973', 61,
02.02.1948
( 30, 'SALOME', 'A', 'KEMPES', 'C01', 2080, '05.04.1975',
60, 20, 'M', '11.05.1941', 3825 ),
( 50, 'JUAN', 'B', 'GARRIDO', 'E01', 1507, '17.08.1949', 58,
15.09.1925
( 60, 'ISIDRO', 'F', 'SUAREZ', 'D11', 2290, '14.09.1973',
55, 16, 'H', '07.07.1945', 3225
( 70, 'EVA', 'D', 'PUENTE', 'D21', 2400, '30.09.1980', 56,
26.05.1953
( 90, 'ELENA', 'W', 'HERRANZ', 'E11', 2230, '15.08.1970',
55, 16, 'M', '15.05.1941', 2945 ),
 ( 100, 'TOMAS', 'Q', 'SOLER', 'E21', 2170, '19.06.1980', 54,
18.12.1956
( 110, 'VICENTE', 'G', 'LUENGO', 'A00', 1140, '16.05.1958',
58, 19, 'H', '11.05.1929', 4650
( 120, 'SIMON', '', 'OTERO', 'A00', 2022, '05.12.1963', 58,
18.10.1942
( 130, 'DOLORES', 'M', 'QUINTANA', 'C01', 2140,
28.07.1971
( 140, 'HELIODORA', 'A', 'NIETO', 'C01', 2210, '15.12.1976',
56, 18, 'M', '19.01.1946', 2842 ),
( 150, 'BRUNO', '', 'ALVAREZ', 'D11', 2250, '12.02.1971',
55, 16, 'H', '17.05.1947', 2528 ),
( 160, 'ELISA', 'R', 'PINTO', 'D11', 1307, '11.10.1977', 54,
12.04.1955
( 170, 'MATEO', 'J', 'YARZA', 'D11', 2050, '15.09.1978', 54,
05.01.1951
( 180, 'MARINA', 'S', 'SANDOVAL', 'D11', 1050, '07.07.1973',
53, 17, 'M', '21.02.1949', 2134 ),
( 190, 'JAIME', 'H', 'WALKER', 'D11', 2100, '27.07.1974',
53, 16, 'H', '25.06.1952', 2045 ),
( 200, 'DAVID', '', 'BONDIA', 'D11', 2340, '03.03.1966', 55,
29.05.1941
( 210, 'WENCESLAO', 'T', 'JURADO', 'D11', 2310,
11.04.1979
( 220, 'JIMENA', 'K', 'LUQUE', 'D11', 2300, '29.08.1968',
55, 18, 'M', '19.03.1948', 2984 ),
( 230, 'JAIME', 'J', 'JIMENEZ', 'D21', 1080, '21.11.1966',
53, 14, 'H', '30.05.1935', 2218
( 240, 'SALVADOR', 'M', 'MARTINEZ', 'D21', 2260,
05.12.1979
( 250, 'DANIEL', 'S', 'SIERRA', 'D21', 2357, '30.10.1969',
52, 15, 'H', '12.11.1939', 1918 ),
( 260, 'SUSANA', 'P', 'JUNQUERA', 'D21', 2302, '11.09.1975',
52, 16, 'M', '05.10.1936', 1725 ),
( 270, 'MARIA', 'L', 'PEREZ', 'D21', 2217, '30.09.1980', 55,
23.05.1953
( 280, 'ENGRACIA', 'R', 'SANCHEZ', 'E11', 2007,
24.03.1967
( 290, 'JUAN', 'R', 'PALACIOS', 'E11', 2192, '30.05.1980',
42, 12, 'H', '09.07.1946', 1380 ),
( 300, 'PEDRO', 'I', 'SIERRA', 'E11', 3522, '19.06.1972',
48, 14, 'H', '27.10.1936', 1775 ),
( 310, 'MATILDE', 'F', 'SERNA', 'E11', 2130, '12.09.1964',
43, 12, 'M', '21.04.1931', 1574 ),
( 320, 'RAMON', 'V', 'MORAN', 'E21', 7112, '07.07.1965', 52,
16, 'H', '11.08.1932', 1995
( 330, 'WILLY', '', 'LERMA', 'E21', 1132, '23.02.1976', 55,
18.07.1941
( 340, 'JAVIER', 'R', 'GIL', 'E21', 1162, '05.05.1947', 54,
17.05.1926
( 999, 'ANGEL', 'E', 'MALDONADO', 'E11', '----',
22.03.1988
INSERT INTO tdepar2 ( numdep, nomdep, numdirec )
VALUES ( 'A00', 'CENTER PROCESSES', 000010 )
B01
C01
D01
D11
D21
 E01
E11
E21

Consultations

1.- Obtain a list of all the female employees from the departments that
start with D and E. The previous list must contain information about the
staff number, first name, last name and phone number.

nuempl
FROM temple1
INNER JOIN tdepar2 ON ([Link] = [Link])
WHERE sex='M' AND ([Link] LIKE 'D%' OR [Link] LIKE
'E%');
Correction with numdept instead of nondept
employee number
FROM temple1
INNER JOIN tdepar2 ON ([Link] = [Link])
WHERE gender='M' AND (tdepar2.department_number LIKE 'D%' OR tdepar2.department_number LIKE
'E%');
2.- Obtain a list of all employees (first name and last name) who earn
more than €2000 a month and who joined the company after January 1st
1975. We also want the information corresponding to your work code.
and the number of staff of its directors.

SELECT name, surname, codtra, numdirec

FROM temple1
INNER JOIN tdepar2 ON ([Link] = [Link])
WHERE salary > 2000 AND fetching > '01/01/1975';
3.- Obtain a list with the last name, department number, and salary
monthly of employees from departments 'A00', 'B01', 'C01' and 'D01'. The
output is wanted in descending order of salary within each department.

SELECT last_name, department, salary

FROM temple1
WHERE dept IN ( 'A00', 'B01', 'C01' , 'D01')
ORDER BY dept, salary DESC;
4.- A list is requested that retrieves the average salary of each department along with
with the number of employees it has. The result should not include employees.
that have a work code greater than 54, nor departments with less
of three employees. It is desired to be sorted by department number.

SELECT salary, numempl

FROM (
SELECT AVG(salary) as salary, COUNT(*) AS numempl, dept
FROM temple1
WHERE NOT codtra > 54
GROUP BY dept
) AS result
WHERE NOT numempl < 3
ORDER BY dept ASC;
Another way
SELECT dept, AVG(salary), COUNT(*)
FROM temple1
WHERE codtra <= 54
GROUP BY dept
HAVING COUNT(*) > 2
ORDER BY dept
;
5.-Select all employees from departments 'D11' and 'E11' whose
first surname starts with S.

SELECT *
FROM temple1
WHERE dept IN ( 'D11', 'E11' ) AND last_name LIKE 'S%';
6.- Obtain the first name, last name, and date of admission of the directors
departments ordered by number of staff.

SELECT name, surname, date

FROM temple1
WHERE CAST(nuempl AS INT) IN (
SELECT CAST(numdirec AS INT) FROM
tdepar2 WHERE numdirec <> ''
)
ORDER BY CAST(nuempl AS INT);
Another way
SELECT employee_number, name, last_name, date_of_birth
FROM temple1, tdepar2
WHERE [Link] = [Link]
ORDER BY CAST(nuempl AS INT)
;
7.- Obtain a list of women from the departments that start with
And for E whose education level is above average; in this case also
sorted by number of personnel.

Tightened to the statement

employee number
FROM temple1
INNER JOIN tdepar2 ON ([Link] = [Link])
WHERE sex='M' AND ([Link] LIKE 'D%' OR [Link] LIKE
'E%') AND niveduc > ( SELECT AVG(niveduc) FROM temple1 )
ORDER BY nuempl;
Numdep instead of nomdep
employee number
FROM temple1
INNER JOIN tdepar2 ON ([Link] = [Link])
WHERE gender='M' AND (tdepar2.department_num LIKE 'D%' OR tdepar2.department_num LIKE
'E%') AND niveduc > ( SELECT AVG(niveduc) FROM temple1 )
ORDER BY nuempl;
Another way
SELECT nuempl, name, surname
FROM temple1
WHERE sex = 'M' AND (dept LIKE 'D%' OR dept LIKE 'E%')
AND niveduc > (SELECT AVG(niveduc) FROM temple1)
ORDER BY CAST(nuempl AS INT)
;
8.- Select all employees whose name is the same as any of them
people from department D21 whose work code is different from
all those from E21 (the list must contain the employee number, name,
last name, department and work code.

SELECT *
FROM temple1
WHERE name IN (SELECT name FROM temple1 WHERE dept='D21') AND
codtra NOT IN (SELECT codtra FROM temple1 WHERE dept='E21')
AND
dept <> 'D21';
SELECT nuempl, name, surname, department, code
FROM temple1
WHERE name = ANY (SELECT name FROM temple1
WHERE dept = 'D21')
AND codtra <> ALL ( SELECT codtra FROM temple1
WHERE dept = 'E21')
AND dept <> 'D21'
;
NOTE: Those from the department itself have been excluded as they are the reference or
comparison pattern.
9.- List the employees who are not directors (the information that must
to appear is the number of staff, surname, and department.

SELECT *
FROM temple1
WHERE CAST(nuempl AS INT) NOT IN (SELECT CAST(numdirec AS INT) FROM
tdepar2 WHERE numdirec <> ''
10.- Select pairs of employees (of opposite sex) who were born on
same day (with information about surname and date of birth).

SELECT *
FROM temple1
WHERE fechnac = (SELECT fechnac FROM temple1 WHERE sex = 'M'
INTERSECT
SELECT birthdate FROM temple1 WHERE gender =
'H');
11.- Obtain a list of all the employees who belong to it
department that Tomás Soler.

SELECT *
FROM temple1
WHERE dept = (select dept from temple1 WHERE name = 'Tomás' AND
Soler
Correction with department number
SELECT *
FROM temple1
WHERE dept = (select dept from temple1 WHERE name = 'TOMAS' AND
LAST NAME = 'SOLER'
Subqueries - exercises
Subqueries allow extracting information that includes multiple tables,
they generally report a single data point to a set of results with a
column. For the complete theoretical part and the conditions and restrictions
seeSubqueries.

In this article we will see how to develop examples by analyzing the data and
assembling the subquery step by step.

Exercises
Using Northwind

use northwind
go

Some theory on Subqueries

They can be used in two ways:

1. Within the field list of the Select statement

select FieldList, (Select Field from SubqueryTable where…) from

MainTable

2. In the Where clause

Select ListadeCampos, OtroCampo, UltimoCampo from Tabla

Where Campo = ( Select… )

1.—List of Customers who purchased in August 1997

select * from Customers

where CustomerID in
(select distinct CustomerID from Orders where Datename(mm,OrderDate)
August
and year(OrderDate) = 1997)
go

Note the subquery in bold.

2.—List of the quantities sold of the products

discontinued. Include only those that are in stock
First: the discontinued products
select * from Products where Discontinued = 1
go

Where are the units sold?

select * from [Order Details]
go

Setting up the solution

select [Link], [Link], [Link] from Products As P

where [Link] = 1 and [Link] > 0
go
Codes of the discontinued items that are in stock
select [Link] from Products As P where [Link] = 1 and
[Link] > 0
go

Sold quantities of each product

select Sum([Link]), [Link] from [Order Details] As OD
where [Link] in (select [Link] from Products
As P where [Link] = 1 and [Link] > 0
Group by [Link]
go

SOLUTION
select [Link], [Link], [Link],
(select Sum([Link]) from [Order Details] As OD where [Link] in
(select [Link] from Products As P where [Link] = 1 and
[Link] > 0
Sold Quantity
from Products as P where [Link] = 1 and [Link] > 0
go

3.—Employees and the number of generated and UNATTENDED orders

Employees
[Link]
from Employees As E

Count of UNATTENDED Orders

select COUNT([Link]) As 'Unattended Orders' from Orders As O where
ShippedDate is null
go

SOLUTION
select [Link], Employee = [Link] + Space(1) + [Link],
(—START OF THE SUBCONSULTATION
select COUNT([Link]) from Orders As O where ShippedDate is null
AND [Link] = [Link]
As 'Unattended Orders' – END OF THE SUBCONSULTATION
from Employees As E
where (select COUNT([Link]) from Orders As O where ShippedDate is null
AND [Link] = [Link]) > 0
order by 'Unattended Orders' desc
go
 If you already master SQL SELECT queries with WHERE,
GROUP BY o HAVING the following that you should learn are the
SQL subqueries.
Below you will find the tutorial divided into three parts:

Response to what are subqueries.

Explanation of how subqueries are used in SQL.
Various examples of subqueries, from easier to more complex.

What is a SQL subquery?

First a definition: a subquery is an instruction
SELECT SQL nested within another SELECT, INSERT,
DELETE or UPDATE. Also, you should know that they will be carried out almost
always within the WHERE clause of the queries.
Another way to explain it would be to say that we have a SELECT
inside another SELECT query, INSERT ... INTO, UPDATE or
DELETE.
There are also three different ways to create a subquery:

1. With ANY, ALL, SOME preceding the nested query.

2. Using the expression IN or NOT IN before the
subquery.
3. EXISTS or NOT EXISTS followed by the subquery.

How is a SQL subquery used?

In this part, I will focus on nested queries within
from the WHERE clause, since I already explained the INSERT INTO queries
in another article.
For to carry out a SQL query with
subqueries we will need:

A normally formed query of the type SELECT, UPDATE or

DELETE
 A WHERE or HAVING clause with the desired conditions
plus one of the previous SQL operators (ANY,
ALL, SOME, IN, NOT IN, EXISTS or NOT EXISTS) and operators of
comparison like "=", ">" or "<"
The SELECT query in parentheses.

There are several important points about its use to consider.

to avoid errors:

The subquery will always go in parentheses, without exceptions.

Subqueries can be nested within other subqueries.
The subquery must always return the same type and number.
of values to compare: there cannot be a subquery that
return two columns for a comparison with one column.
The subquery should always (although it is not mandatory) be
position yourself to the right of the relational operator.

Now that you have the theory swirling in your head, to

next I show you a small example fulfilling the three points
recently commented.

Example SELECT query

First, I am going to create a normal SELECT query that retrieves
the name and surnames of the rows of a logging called citizens

1. SELECT name, surnames

[Link] citizens

Add the WHERE clause

Now I add the WHERE clause to the previous query and I am going to
add one of the compatible operators.

[Link] first_name, last_name

2. FROM citizens
[Link] dni IN ....

Subquery in parentheses
And finally, I am going to perform the subquery in parentheses that
select the DNI column from a table 'delinquents':

1. SELECT first_name, last_name

[Link] citizens
 [Link] dni IN ( SELECT dni FROM delinquents );

That's it! What will happen now is that the database will:

First, resolve the subquery.

2. Then compare the values returned by the DNI of the
subquery with the consultation's DNI SELECT "FATHER".
3. It will return the values of the main query as the result of
the query.

Examples of subqueries in SQL

Time for examples. From here I am going to show you several
examples for you to fully internalize subqueries.
I will start with a couple of simple examples, following the trend.
from the previous section and I will continue with more complex examples and
advanced.
For the following examples, I will create 2 tables:

1. Table about video games which I will call video game.

2. Table about players that I will call player.

The SQL CREATE queries are as follows:

1. CREATE TABLE video_game (

2. id INT (11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
3. title VARCHAR(100) NOT NULL,
4. language VARCHAR(5) NOT NULL,
5. TEXT NULL
6. decimal note (3,1),
7. INT DEFAULT 0
8.);
9.
[Link] TABLE player(
[Link] INT (11) NOT NULL AUTO_INCREMENT,
[Link] VARCHAR(80) NOT NULL
[Link] INT NOT NULL,
14.is_famous INT NOT NULL DEFAULT 0,
15.video_game_id INT NOT NULL
[Link] KEY(id_videojuego) REFERENCES videojuego(id),
registration_date DATE NULL
18.);

Now that I have my two tables, let's carry out a few

subqueries.
Example subqueries 1
Retrieve all games in which at least 1 player plays:

1. SELECT *
[Link] video game
[Link] id IN ( SELECT id_video_game FROM player );

Another alternative way to perform this subquery would be

using the ANY or SOME clause:

1. SELECT *
2. FROM video game
[Link] id = ANY ( SELECT id_game FROM player );
4.
5.//alternative with SOME
6.
[Link] *
[Link] video game
9. WHERE id = SOME ( SELECT id_video_game FROM player );

The two conditions are actually the same since SOME is a

ALIAS of ANY, which means they are the same command.

Example of subqueries 2
In the following example, I will select all the players that
they play a game in Spanish:

[Link]
[Link] player
[Link] id_videojuego IN ( SELECT id FROM videojuego WHERE language ='es');

Example 3 of nested queries

Now I am going to make another query with a subquery and
aggregate functions, specifically using MAX.
To demonstrate it to you, I select the players who have the highest
age among the players:

[Link] name, last names

[Link] player
[Link] age = ( SELECT MAX(age) FROM player );
Example 4 subquery with nested subquery
In this example, I am going to raise the difficulty a bit, I am going to show you.
the almost infinite possibilities of subqueries using for it a
nested subquery within another subquery. You will understand it better.
with the example.
Remember: Subqueries are resolved before their query
principal

[Link] *
[Link] video game
[Link] language IN ( SELECT language FROM video_game WHERE id = SOME ( SELECT
CT video_game_id FROM player WHERE is_famous = 1);

Did you understand it? The query as a whole selects all

the video games of one of the languages that some player plays
famous.
In execution order of the subqueries (from the last to the
principal) I select:

The ID of all famous players

2. The language of all video games whose ID matches the ID
of famous players.
3. All the data of the video games that match their language
some of the languages from the subquery of point 2.

#5 Example of subquery with UPDATE SQL

I think with the SELECT queries all the ...
possibilities of subqueries, but for you to see graphically
an example of how to use them in UPDATE or DELETE I leave you now
example.
Specifically, I am going to add 1 to the total_players counter of
those video games that have a player playing it on the date '2019-
12-01

UPDATE video game

[Link] total_players = players + 1
[Link] id IN ( SELECT id_video_game FROM player WHERE date ='2019-12-01')
 Conclusion about SQL subqueries
Well, I didn't want to go on any longer, I will continue with the
subqueries if necessary in another article.
I hope it has been useful to you! If so, it would be great if
You will leave a comment with any questions/contributions so that we can all improve together.
the article. Also, if you share it, you will help us stay motivated
and in creating useful content for programmers around the world.
Thank you!
Invalid request. Please provide the text you would like to have translated.
The provided text is a URL and cannot be translated.