SQL
CREATE DATABASE LABORATORIO;
USE LABORATORIO;
CREATE TABLE INVOICES (
LETTER CHAR,
NUMBER INT,
CLIENT_ID INT,
ARTICLE_ID INT,
DATE DATE,
AMOUNT DOUBLE,
PRIMARY KEY(LETTER, NUMBER)
);
CREATE TABLE ARTICLES (
ARTICLE_ID INT PRIMARY KEY,
NAME VARCHAR(50),
PRICE DOUBLE,
STOCK INT
);
CREATE TABLE CUSTOMERS (
CUSTOMER_ID INT PRIMARY KEY,
FIRST_NAME VARCHAR(25),
LAST_NAME VARCHAR(25),
CUIT CHAR(16),
ADDRESS VARCHAR(50),
COMMENTS VARCHAR(50)
);
ALTER TABLE INVOICES
CHANGE COLUMN CLIENT_ID CUSTOMER_ID INT,
CHANGE COLUMN ARTICLE_ID ARTICLE_ID INT,
MODIFY COLUMN AMOUNT DOUBLE UNSIGNED;
ALTER TABLE ARTICLES
CHANGE COLUMN ARTICLE_ID IDARTICULO INT,
MODIFY COLUMN NAME VARCHAR(75),
MODIFY COLUMN PRICE DOUBLE UNSIGNED NOT NULL,
MODIFY COLUMN STOCK INT UNSIGNED NOT NULL;
ALTER TABLE CUSTOMERS
CHANGE COLUMN CUSTOMER_ID IDCLIENTE INT,
MODIFY COLUMN FIRST_NAME VARCHAR(30) NOT NULL,
MODIFY COLUMN LAST_NAME VARCHAR(35) NOT NULL,
CHANGE COLUMN COMMENTS OBSERVATIONS VARCHAR(255);
INSERT INTO INVOICES
VALUES
('A', 28, 14, 335, '2021-03-18', 1589.50),
('A', 39, 26, 157, '2021-04-12', 979.75),
('B', 8, 17, 95, '2021-04-25', 513.35),
('B', 12, 5, 411, '2021-05-03', 2385.70),
('B', 19, 50, 157, '2021-05-26', 979.75);
INSERT INTO ARTICLES
VALUES
(95, 'Webcam with Plug & Play Microphone', 513.35, 39),
(157, 'Apple AirPods Pro', 979.75, 152),
(335, 'Samsung Automatic Washer-Dryer', 1589.50, 12),
(411, 'Gloria Trevi / Gloria / CD+DVD', 2385.70, 2);
INSERT INTO CUSTOMERS
VALUES
(5, 'Santiago', 'González', '23-24582359-9', 'Uriburu 558 - 7ºA', 'VIP'),
(14, 'Gloria', 'Fernández', '23-35965852-5', 'Constitución 323', 'GBA'),
(17, 'Gonzalo', 'López', '23-33587416-0', 'Arias 2624', 'GBA'),
(26, 'Carlos', 'García', '23-42321230-9', 'Pasteur 322 - 2ºC', 'VIP'),
(50, 'Micaela', 'Altieri', '23-22885566-5', 'Santamarina 1255', 'GBA');
SELECT COMPANYNAME, CITY, COUNTRY FROM CLIENTES_NEPTUNO;
SELECT COMPANYNAME, CITY, COUNTRY FROM CLIENTES_NEPTUNO
ORDER BY COUNTRY;
SELECT COMPANYNAME, CITY, COUNTRY FROM CLIENTES_NEPTUNO
ORDER BY COUNTRY, CITY LIMIT 10;
SELECT COMPANYNAME, CITY, COUNTRY FROM CLIENTES_NEPTUNO
ORDER BY COUNTRY, CITY LIMIT 5 OFFSET 10;
Retrieve all records from the NACIMIENTOS table where the nationality is 'FOREIGN'.
SELECT * FROM BIRTHS WHERE NATIONALITY = 'FOREIGN';
From the NACIMIENTOS table, obtain a list of all babies born to mothers under the age of majority. Display all fields of the
table in the query result and order the result from lowest to highest by the mothers' age.
SELECT * FROM BIRTHS WHERE MOTHER_AGE < 18 ORDER BY MOTHER_AGE;
From the NACIMIENTOS table, obtain a list of all babies born to mothers who are the same age as the father. Display all
fields of the table in the query result.
SELECT * FROM BIRTHS WHERE MOTHER_AGE = FATHER_AGE;
From the NACIMIENTOS table, obtain a list of all babies born to mothers who are 40 years younger or less than the father.
SELECT * FROM BIRTHS WHERE FATHER_AGE - MOTHER_AGE <= 40;
From the CLIENTES_NEPTUNO table, obtain a list of all clients residing in Argentina. Display all fields of the table in the
query result.
SELECT * FROM CLIENTES_NEPTUNO WHERE COUNTRY = 'ARGENTINA';
From the CLIENTES_NEPTUNO table, obtain a list of all clients, excluding those residing in Argentina. Display all fields of the
table in the query result and order the result alphabetically by country names.
SELECT * FROM CLIENTES_NEPTUNO WHERE COUNTRY <> 'ARGENTINA' ORDER BY COUNTRY;
From the NACIMIENTOS table, obtain a list of all babies born with less than 20 weeks of gestation. Display all fields of the
table in the query result and order the result from highest to lowest by the values of the WEEKS column.
SELECT * FROM BIRTHS WHERE WEEKS < 20 ORDER BY WEEKS DESC;
From the NACIMIENTOS table, obtain a list of all female babies born to single foreign mothers over 40 years old. Display all
fields of the table in the query result.
SELECT * FROM BIRTHS WHERE GENDER = 'FEMALE' AND NATIONALITY = 'FOREIGN' AND MARITAL_STATUS_MOTHER =
'SINGLE' AND MOTHER_AGE > 40;
From the CLIENTES_NEPTUNO table, obtain a list of all clients residing in South American countries. (The South American
countries listed in this table are Argentina, Brazil, and Venezuela). Display all fields of the table in the query result and order
the records alphabetically by country names and cities.
SELECT * FROM CLIENTES_NEPTUNO WHERE COUNTRY IN ('ARGENTINA', 'BRAZIL', 'VENEZUELA')
From the NACIMIENTOS table, obtain a list of all babies born with a gestational age between 20 and 25 weeks, inclusive.
Display all fields of the table in the query result and order the result by the gestational weeks of the newborns, from lowest to
highest.
SELECT * FROM BIRTHS WHERE WEEKS BETWEEN 20 AND 25 ORDER BY WEEKS;
From the NACIMIENTOS table, use the IN operator and obtain a list of all babies born in the communes 1101, 3201, 5605,
8108, 9204, 13120, and 15202. Display all fields of the table in the query result and order the records from lowest to highest
by the commune numbers.
SELECT * FROM BIRTHS WHERE COMMUNE IN (1101, 3201, 5605, 8108, 9204, 13120, 15202) ORDER BY COMMUNE;
From the CLIENTES_NEPTUNO table, obtain a list of all clients whose ID starts with the letter C. Display all fields of the table
in the query result.
SELECT * FROM CLIENTES_NEPTUNO WHERE CUSTOMER_ID LIKE 'C%';
From the CLIENTES_NEPTUNO table, obtain a list of all clients residing in a city that starts with the letter B and has a total of
5 characters. Display all fields of the table in the query result.
This query is not provided, but it could be written as:
SELECT * FROM CLIENTES_NEPTUNO WHERE CITY LIKE 'B____';
From the NACIMIENTOS table, obtain a list of all fathers who have more than 10 children.
SELECT * FROM BIRTHS WHERE CHILDREN_TOTAL > 10;
Generate a backup of the "LABORATORIO" database (complete) into a single file with the following name: "BACKUP
LABORATORIO BD."
a. Execute the "Server Data Export" command.
b. Select the "LABORATORIO" database.
c. Check the option "Export to Self-Contained File."
d. Define the name and location of the file, then click the "Start Export" button.
e. Verify that the file has been generated at the selected location.
Drop the database "LABORATORIO".
Restore the file "BACKUP LABORATORIO BD." Verify, after the restoration, that the database has been generated.
a. Execute the "Server Data Import" command.
b. Check the option "Import to Self-Contained File."
c. Locate, select, and open the file "BACKUP LABORATORIO BD."
d. Click the "Start Import" button.
Use the CLIENTES_NEPTUNO table and generate a query that displays the columns IDCLIENTE and COMPANYNAME. In the
query result, a new column named LOCATION should be shown, which concatenates the columns ADDRESS, CITY, and
COUNTRY, separating the values of these fields by a dash. For this first exercise, use the CONCAT function.
SELECT IDCLIENTE, COMPANYNAME, CONCAT(DIRECTION, ' - ', CITY, ' - ', COUNTRY) AS LOCATION
FROM CLIENTES_NEPTUNO;
Repeat the previous exercise using the CONCAT_WS function.
SELECT IDCLIENTE, COMPANYNAME, CONCAT_WS(' - ', DIRECTION, CITY, COUNTRY) AS LOCATION
FROM CLIENTES_NEPTUNO;
Modify the previous exercise to display the values loaded in the field whose name is COMPANYNAME in uppercase. This
column should be displayed with the name COMPANY.
SELECT IDCLIENTE, UPPER(COMPANYNAME) AS COMPANY, CONCAT_WS(' - ', DIRECTION, CITY, COUNTRY) AS LOCATION
FROM CLIENTES_NEPTUNO;
Modify the previous exercise to display the values loaded in the IDCLIENTE field in lowercase. This column should be
displayed with the name CODE.
SELECT LOWER(IDCLIENTE) AS CODE, UPPER(COMPANYNAME) AS COMPANY, CONCAT_WS(' - ', DIRECTION, CITY,
COUNTRY) AS LOCATION
FROM CLIENTES_NEPTUNO;
Use the NACIMIENTOS table and generate a query that displays the FECHA column. In the query result, a new column
named SEX should display the initial of the data loaded in the SEX field, and another column named TYPE should display the
initial of the data loaded in the BIRTH_TYPE field.
Use the CLIENTES_NEPTUNO table and generate a query that displays all fields of the table. Add a new column to the query
named CODE. It should concatenate the first letter of the CITY field and the first and last two letters of the COUNTRY field.
The data in this new column should be displayed in uppercase.
SELECT *, UPPER(CONCAT(LEFT(CITY, 1), LEFT(COUNTRY, 1), RIGHT(COUNTRY, 2))) AS CODE FROM CLIENTES_NEPTUNO;
Utilize the NACIMIENTOS table and generate a query that displays the first 5 columns of the table. Add a new column to the
query named MONTH that extracts the birth month from the DATE field. Sort the result from smallest to largest by the values
in the MONTH column.
SELECT SEX, DATE, BIRTH_TYPE, ATTENTION, DELIVERY_PLACE, SUBSTRING(DATE, 4, 2) AS MONTH
FROM BIRTHS
ORDER BY MONTH;
Utilize the NACIMIENTOS table and generate a query that displays the columns SEX, DATE, and BIRTH_TYPE. In the query
result, a new column named NATIONALITY should replace the values "Chilena" in the original NATIONALITY column with
"Ciudadana".
SELECT SEX, DATE, BIRTH_TYPE,
REPLACE(NATIONALITY, 'Chilena', 'Ciudadana') AS NATIONALITY
FROM BIRTHS;
Using the PEDIDOS_NEPTUNO table, obtain a list of all orders placed throughout the year 1998.
SELECT * FROM PEDIDOS_NEPTUNO
WHERE YEAR(ORDERDATE) = 1998;
Using the PEDIDOS_NEPTUNO table, obtain a list of all orders placed during the months of August and September of the
year 1997.
SELECT * FROM PEDIDOS_NEPTUNO
WHERE MONTH(ORDERDATE) IN (8, 9) AND YEAR(ORDERDATE) = 1997;
Using the PEDIDOS_NEPTUNO table, obtain a list of all orders placed on the first day of each month, regardless of the year.
SELECT * FROM PEDIDOS_NEPTUNO
WHERE DAY(ORDERDATE) = 1;
Use the PEDIDOS_NEPTUNO table and obtain a list of all records contained in the table. In a new column named "DAYS
ELAPSED," display the number of days elapsed from the date each order was placed until today.
SELECT *, DATEDIFF(CURDATE(), ORDERDATE) AS 'DAYS ELAPSED' FROM PEDIDOS_NEPTUNO;
Modify the previous query and add another column named "DAY" to reflect the name of the day on which each order was
placed.
SELECT *, DATEDIFF(CURDATE(), ORDERDATE) AS 'DAYS ELAPSED',
DAYNAME(ORDERDATE) AS DAY FROM PEDIDOS_NEPTUNO;
Modify the previous query and add another column named "DAY OF YEAR" to reflect the day of the year on which each order
was placed.
SELECT *, DATEDIFF(CURDATE(), ORDERDATE) AS 'DAYS ELAPSED',
DAYNAME(ORDERDATE) AS DAY, DAYOFYEAR(ORDERDATE) AS 'DAY OF YEAR' FROM PEDIDOS_NEPTUNO;
Modify the previous query and add another column named "MONTH" to reflect the name of the month in which each order
was placed.
SELECT *, DATEDIFF(CURDATE(), ORDERDATE) AS 'DAYS ELAPSED',
DAYNAME(ORDERDATE) AS DAY, DAYOFYEAR(ORDERDATE) AS 'DAY OF YEAR',
MONTHNAME(ORDERDATE) AS 'MONTH' FROM PEDIDOS_NEPTUNO;
Modify the previous query and add another column named "FIRST DUE DATE" to calculate the first due date of each invoice,
assuming that it is 30 days after the issue date.
SELECT *, DATEDIFF(CURDATE(), ORDERDATE) AS 'DAYS ELAPSED',
DAYNAME(ORDERDATE) AS DAY, DAYOFYEAR(ORDERDATE) AS 'DAY OF YEAR',
MONTHNAME(ORDERDATE) AS 'MONTH', DATE_ADD(ORDERDATE, INTERVAL 30 DAY)
AS 'FIRST DUE DATE'
FROM PEDIDOS_NEPTUNO;
Modify the previous query and add another column named "SECOND DUE DATE" to calculate the second due date of each
invoice, assuming that it is 2 months after the issue date.
SELECT *, DATEDIFF(CURDATE(), ORDERDATE) AS 'DAYS ELAPSED',
DAYNAME(ORDERDATE) AS DAY, DAYOFYEAR(ORDERDATE) AS 'DAY OF YEAR',
MONTHNAME(ORDERDATE) AS 'MONTH',
DATE_ADD(ORDERDATE, INTERVAL 30 DAY) AS 'FIRST DUE DATE',
DATE_ADD(ORDERDATE, INTERVAL 2 MONTH) AS 'SECOND DUE DATE'
FROM PEDIDOS_NEPTUNO;
Use the table PEDIDOS_NEPTUNO and get a list of all records loaded in the table. Generate a new column named IVA that
calculates 21% of the charge of each order, obtaining a numeric value with a maximum of 2 decimals.
SELECT *, ROUND(CARGO * 0.21, 2) AS VAT
FROM PEDIDOS_NEPTUNO;
Modify the previous query, adding a new column named NETO that calculates the total to be paid by each customer for the
purchases made (i.e., adding the VAT to the original charge, keeping a maximum of 2 decimals).
SELECT *, ROUND(CARGO * 0.21, 2) AS VAT,
ROUND(CARGO * 1.21, 2) AS NET
FROM PEDIDOS_NEPTUNO;
Modify the previous query, adding a new column named ROUNDING TO FAVOR CLIENT that returns the lower integer value
of the net calculated previously.
SELECT *, ROUND(CARGO * 0.21, 2) AS VAT,
ROUND(CARGO * 1.21, 2) AS NET,
FLOOR(ROUND(CARGO * 1.21, 2)) AS 'ROUNDING TO FAVOR CLIENT'
FROM PEDIDOS_NEPTUNO;
Modify the previous query, adding a new column named ROUNDING TO FAVOR COMPANY that returns the upper integer
value of the net calculated before.
SELECT *, ROUND(CARGO * 0.21, 2) AS VAT,
ROUND(CARGO * 1.21, 2) AS NET,
FLOOR(ROUND(CARGO * 1.21, 2)) AS 'ROUNDING TO FAVOR CLIENT',
CEIL(ROUND(CARGO * 1.21, 2)) AS 'ROUNDING TO FAVOR COMPANY'
FROM PEDIDOS_NEPTUNO;
Calculate the number of records loaded in the table PEDIDOS_NEPTUNO.
SELECT COUNT(*) FROM PEDIDOS_NEPTUNO;
Calculate the number of orders loaded in the table PEDIDOS_NEPTUNO that were delivered by the carrier named SPEEDY
EXPRESS. The column in which the result is obtained will be displayed with the name DELIVERIES SPEEDY EXPRESS.
SELECT COUNT(TRANSPORTISTA) AS 'DELIVERIES SPEEDY EXPRESS'
FROM PEDIDOS_NEPTUNO
WHERE TRANSPORTISTA = 'SPEEDY EXPRESS';
Calculate the number of orders loaded in the table PEDIDOS_NEPTUNO that were served by employees whose last name
begins with the letter C. The column in which the result is obtained must be displayed with the name SALES.
SELECT COUNT(EMPLEADO) SALES
FROM PEDIDOS_NEPTUNO
WHERE EMPLEADO LIKE 'C%';
Calculate the average price of all products loaded in the table named PRODUCTOS_NEPTUNO. The result column must be
displayed with the name AVERAGE PRICE. And the result should display, at most, only 2 decimals.
SELECT ROUND(AVG(PRECIOUNIDAD), 2) 'AVERAGE PRICE'
FROM PRODUCTOS_NEPTUNO;
Modify the previous query to obtain the lowest price from the table. The column where the result is obtained should be
displayed with the name LOWER PRICE.
SELECT ROUND(AVG(PRECIOUNIDAD), 2) 'AVERAGE PRICE',
MIN(PRECIOUNIDAD) 'LOWER PRICE'
FROM PRODUCTOS_NEPTUNO;
Modify the previous query to obtain the highest price from the table. The new column should be displayed with the name
HIGHEST PRICE.
SELECT ROUND(AVG(PRECIOUNIDAD), 2) 'AVERAGE PRICE', MIN(PRECIOUNIDAD) 'LOWER PRICE',
MAX(PRECIOUNIDAD) 'HIGHEST PRICE'
FROM PRODUCTOS_NEPTUNO;
Based on the table PRODUCTOS_NEPTUNO, generate a query that shows the highest price corresponding to each category.
The column where this price is obtained should be displayed with the name HIGHEST PRICE. The column showing the
categories should be displayed with the name CATEGORY.
SELECT NOMBRECATEGORIA AS CATEGORY, MAX(PRECIOUNIDAD) AS 'HIGHEST PRICE'
FROM PRODUCTOS_NEPTUNO
GROUP BY CATEGORY;
Calculate the number of deliveries made by each carrier, using the table PEDIDOS_NEPTUNO. The column where the results
are obtained should be displayed with the name DELIVERIES.
SELECT TRANSPORTISTA,
COUNT(IDPEDIDO) AS DELIVERIES
FROM PEDIDOS_NEPTUNO
GROUP BY TRANSPORTISTA;
Use the NACIMIENTOS table and calculate the number of births according to the gender of the babies. The column where
the results are obtained should be displayed with the name BIRTHS.
SELECT SEXO, COUNT(SEXO) AS BIRTHS
FROM NACIMIENTOS
GROUP BY SEXO;
Using the PEDIDOS_NEPTUNO table, calculate the total expenses per customer. The column where the results are obtained
should be displayed with the name TOTAL EXPENSES and should display at most 2 decimals. The column containing the
customer names should be titled CUSTOMER.
SELECT NOMBRECOMPANIA AS CUSTOMER,
ROUND(SUM(CARGO), 2) AS 'TOTAL EXPENSES'
FROM PEDIDOS_NEPTUNO
GROUP BY CUSTOMER;
Using the PRODUCTOS table, calculate the quantity of products belonging to each section. The column where the results
are obtained should be displayed with the name QUANTITY. Order the query from highest to lowest according to the values
in the QUANTITY column.
SELECT SECCION,
COUNT(SECCION) AS QUANTITY
FROM PRODUCTOS
GROUP BY SECCION
ORDER BY QUANTITY DESC;
Using the PEDIDOS_NEPTUNO table, calculate the number of sales made per month and year. The columns should be
displayed with the names YEAR, MONTH, and SALES respectively. Order the result by year and month to obtain a
chronological list of sales.
SELECT YEAR(FECHAPEDIDO) AS 'YEAR',
MONTHNAME(FECHAPEDIDO) AS MONTH,
COUNT(IDPEDIDO) AS SALES
FROM PEDIDOS_NEPTUNO
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH(FECHAPEDIDO);
Use the PEDIDOS_NEPTUNO table and calculate the requested statistics.
SELECT EMPLEADO,
ROUND(SUM(CARGO), 2) AS REVENUE,
ROUND(AVG(CARGO), 2) AS AVERAGE,
MAX(CARGO) AS 'BEST SALE',
MIN(CARGO) AS 'WORST SALE',
COUNT(CARGO) AS SALES
FROM PEDIDOS_NEPTUNO
GROUP BY EMPLEADO;
To create a new table named MALES from the BIRTHS table with all the fields of the original table and copy only the records
where the gender is 'MALE', you can use the following SQL query:
CREATE TABLE MALES
SELECT * FROM BIRTHS WHERE GENDER = 'MALE';
To create a new table named WOMEN from the BIRTHS table with all the fields of the original table and copy only the records
where the gender is 'FEMALE', you can use the following SQL query:
CREATE TABLE WOMEN
SELECT * FROM BIRTHS WHERE GENDER = 'FEMALE';
To create a new table named INDETERMINATE from the BIRTHS table with all the fields of the original table and copy only the
records where the gender is 'INDETERMINATE', you can use the following SQL query:
CREATE TABLE INDETERMINATE
SELECT * FROM BIRTHS WHERE GENDER = 'INDETERMINATE';
The provided SQL commands are correct. They first disable the SQL_SAFE_UPDATES mode to allow the UPDATE statement
to modify rows based on a WHERE clause without a key. Then, they update the COUNTRY column in the
CLIENTES_NEPTUNO table where the country is 'UNITED STATES' to 'USA'. Finally, they select all rows from the
CLIENTES_NEPTUNO table to verify the changes.
SET SQL_SAFE_UPDATES = 0;
UPDATE CLIENTES_NEPTUNO
SET PAIS = 'USA'
WHERE PAIS = 'UNITED STATES';
SELECT * FROM CLIENTES_NEPTUNO;
Update the CLIENTES_NEPTUNO table to display all values in the COMPANY_NAME field in uppercase.
UPDATE CLIENTES_NEPTUNO
SET COMPANY_NAME = UPPER(COMPANY_NAME);
Display the contents of the CLIENTES_NEPTUNO table to verify the change.
SELECT * FROM CLIENTES_NEPTUNO;
Update the CLIENTES_NEPTUNO table to display all values in the CITY and COUNTRY fields in uppercase.
UPDATE CLIENTES_NEPTUNO
SET CITY = UPPER(CITY), COUNTRY = UPPER(COUNTRY);
Display the contents of the CLIENTES_NEPTUNO table to verify the change.
SELECT * FROM CLIENTES_NEPTUNO;
Add a new column named EMPLOYEE_NAME in the EMPLOYEES table that accepts text strings with a maximum length of 30
characters. Place this column to the right of the EMPLOYEE_ID field. Populate this new column by concatenating the values
loaded in the LAST_NAME and FIRST_NAME fields, separating those values by a comma and a space. Remove the
LAST_NAME and FIRST_NAME columns. Display the contents of the EMPLOYEES table to verify the change.
ALTER TABLE EMPLOYEES ADD COLUMN EMPLOYEE_NAME VARCHAR(30) AFTER EMPLOYEE_ID;
UPDATE EMPLOYEES
SET EMPLOYEE_NAME = CONCAT(LAST_NAME, ', ', FIRST_NAME);
ALTER TABLE EMPLOYEES DROP COLUMN LAST_NAME, DROP COLUMN FIRST_NAME;
SELECT * FROM EMPLOYEES;
In the CLIENTES table, create a new column called TIPO that accepts text strings with a maximum of 3 characters. This
column should be placed at the end of the table. Populate this new column by setting the value VIP for all clients residing in
the city of MADRID. To verify the change, display the contents of the CLIENTES table.
ALTER TABLE CLIENTES ADD COLUMN TIPO VARCHAR(3);
UPDATE CLIENTES SET TIPO = 'VIP' WHERE CITY = 'MADRID';
SELECT * FROM CLIENTES;
Since all clients listed in the CLIENTES table reside in Spain, add the prefix +34- to each of the phone numbers listed in the
TELEFONO field. If a client does not have the phone number loaded, the prefix should not be added to that phone number.
Display the contents of the CLIENTES table to verify the change made.
ALTER TABLE CLIENTES MODIFY COLUMN TELEFONO VARCHAR(20);
UPDATE CLIENTES SET TELEFONO = CONCAT('+34-', TELEFONO) WHERE TELEFONO IS NOT NULL;
SELECT * FROM CLIENTES;
Add a new column named DATE in the table PRODUCTS that accepts dates
ALTER TABLE PRODUCTS ADD DATE DATE;
Populate the new column with coherent dates by concatenating the fields DAY, MONTH, and YEAR
UPDATE PRODUCTS SET DATE = CONCAT(YEAR, '-', MONTH, '-', DAY);
Drop the original columns (DAY, MONTH, and YEAR)
ALTER TABLE PRODUCTS DROP DAY, DROP MONTH, DROP YEAR;
Update the ORIGIN field so that where it contains the value SPAIN, it is replaced by SPAIN
UPDATE PRODUCTS SET ORIGIN = 'SPAIN'
WHERE ORIGIN = 'SPAIN';
Show the contents of the table PRODUCTS to verify the changes made
SELECT * FROM PRODUCTS;
Update the field SUSPENDIDO of the table PRODUCTOS_NEPTUNO.
ALTER TABLE PRODUCTOS_NEPTUNO MODIFY SUSPENDIDO VARCHAR(2);
Update the field SUSPENDIDO, setting it to 'NO' if it was '0', and 'YES' otherwise.
UPDATE PRODUCTOS_NEPTUNO
SET SUSPENDIDO = IF(SUSPENDIDO = '0', 'NO', 'YES');
Show the contents of the table PRODUCTOS_NEPTUNO to verify the changes made.
SELECT * FROM PRODUCTOS_NEPTUNO;
Update the prices of all products in the table PRODUCTOS_NEPTUNO, increasing them by 10% and keeping a total of 2
decimals for each of the prices. Show the contents of the table PRODUCTOS_NEPTUNO to verify the changes made.
UPDATE PRODUCTOS_NEPTUNO SET UNITPRICE = ROUND(UNITPRICE * 1.1, 2);
SELECT * FROM PRODUCTOS_NEPTUNO;
Look at the table SUPPLIERS and its content. For all suppliers who don't have a value loaded in the REGION field, display the
value NULL in that field. Show the content of the table SUPPLIERS to verify the change made
UPDATE SUPPLIERS SET REGION = NULL WHERE REGION = '';
SELECT * FROM SUPPLIERS;
Look at the content of the CLIENTS table. Update the CITY field so that all values loaded in this column display the first letter
in uppercase and the rest in lowercase. Show the content of the table SUPPLIERS to verify the change made.
UPDATE CLIENTS
SET CITY = CONCAT(UPPER(LEFT(CITY, 1)), LOWER(SUBSTRING(CITY, 2, LENGTH(CITY))));
SELECT * FROM CLIENTS;
Generate a new table named PRODUCT_SUSPENDED from the table PRODUCTOS_NEPTUNO. Populate this new table with
all fields from the PRODUCTOS_NEPTUNO table, but only those records where the SUSPENDED field contains the word YES.
Display the contents of the PRODUCT_SUSPENDED table once generated. The new table should contain 8 products
(records)
CREATE TABLE SUSPENDED_PRODUCTS
SELECT * FROM PRODUCTOS_NEPTUNO
WHERE SUSPENDED = 'YES';
SELECT * FROM SUSPENDED_PRODUCTS;
From this moment onwards, all products supplied by supplier 1 are suspended indefinitely.
UPDATE PRODUCTOS_NEPTUNO SET SUSPENDED = 'YES'
WHERE SUPPLIERID = 1;
INSERT INTO SUSPENDED_PRODUCTS (PRODUCTID,
PRODUCTNAME, CONTACTNAME, CATEGORYNAME, UNITPRICE, SUSPENDED, SUPPLIERID)
SELECT PRODUCTID, PRODUCTNAME, CONTACTNAME, CATEGORYNAME, UNITPRICE,
SUSPENDED, SUPPLIERID
FROM PRODUCTOS_NEPTUNO
WHERE SUSPENDED = 'YES';
DELETE FROM PRODUCTOS_NEPTUNO WHERE SUSPENDED = 'YES';
Using the PRODUCTOS_NEPTUNO table, retrieve a list of all products whose price exceeds the average price. This list should
contain all fields from the table. Finally, sort the result alphabetically based on the product names.
SELECT * FROM PRODUCTOS_NEPTUNO
WHERE UNITPRICE >
(SELECT AVG(UNITPRICE) FROM PRODUCTOS_NEPTUNO)
ORDER BY PRODUCTNAME;
Take the PRODUCTOS_NEPTUNO table and get a list of all products whose price is higher than the most expensive product
in the SUSPENDED_PRODUCTS table. This should contain all fields from the table. Then, sort the result from highest to
lowest according to the obtained prices.
SELECT * FROM PRODUCTOS_NEPTUNO
WHERE UNITPRICE >
(SELECT MAX(UNITPRICE) FROM SUSPENDED_PRODUCTS)
ORDER BY UNITPRICE DESC;
Using the VARONES table, retrieve a list of all babies who were born with a number of gestational weeks less than the baby
of undetermined sex with the shortest gestation. The list should display all fields from the table.
SELECT * FROM MALES
WHERE WEEKS <
(SELECT MIN(WEEKS) FROM UNDETERMINED);
Given the PRODUCTOS_NEPTUNO table, retrieve a list of all products whose name begins with the initial of the employee's
last name whose EMPLOYEEID is number 8. This should display all fields from the PRODUCTOS_NEPTUNO table and should
be sorted alphabetically by product names.
SELECT * FROM PRODUCTOS_NEPTUNO
WHERE LEFT(PRODUCTNAME, 1) =
(SELECT LEFT(LAST_NAME, 1) FROM EMPLOYEES
WHERE EMPLOYEEID = 8)
ORDER BY PRODUCTNAME;
Using the PRODUCTOS_NEPTUNO table, retrieve a list of all products belonging to the supplier with the highest ID. The list
should display all fields from the PRODUCTOS_NEPTUNO table and should be sorted alphabetically by product names.
SELECT * FROM PRODUCTOS_NEPTUNO
WHERE SUPPLIERID =
(SELECT MAX(SUPPLIERID) FROM SUPPLIERS)
ORDER BY PRODUCTNAME;
Given the PRODUCTOS_NEPTUNO table, extract a list of all products belonging to the BEVERAGES category and whose price
is higher than the most expensive product in the CONDIMENTS category. The list should display all fields from the table.
SELECT * FROM PRODUCTOS_NEPTUNO
WHERE CATEGORYNAME = 'BEVERAGES' AND
UNITPRICE >
(SELECT MAX(UNITPRICE) FROM PRODUCTOS_NEPTUNO
WHERE CATEGORYNAME = 'CONDIMENTS');
From the WOMEN table, obtain a list of all baby girls born to mothers older than the oldest mother listed in the MALES table.
The list should display all fields from the WOMEN table.
SELECT * FROM WOMEN
WHERE MOTHER_AGE >
(SELECT MAX(MOTHER_AGE) FROM MALES);
Using the CLIENTES_NEPTUNO table, extract a list of all customers who have made purchases for an amount greater than
$500. The list should display the COMPANYNAME, CITY, and COUNTRY fields and should be sorted alphabetically by
company names.
SELECT COMPANYNAME, CITY, COUNTRY
FROM CLIENTES_NEPTUNO
WHERE COMPANYNAME IN
(SELECT COMPANYNAME FROM PEDIDOS_NEPTUNO
WHERE AMOUNT > 500)
ORDER BY COMPANYNAME;
Utilize the CLIENTES_NEPTUNO table to generate a query that displays the fields IDCLIENTE, COMPANYNAME, CITY, and
COUNTRY. Then, add a column named CONTINENT, in which the values defined in the conditions are shown.
SELECT IDCLIENTE, COMPANYNAME, CITY, COUNTRY,
CASE
WHEN COUNTRY IN ('ARGENTINA', 'BRAZIL', 'VENEZUELA') THEN 'SOUTH AMERICA'
WHEN COUNTRY IN ('MEXICO', 'USA', 'CANADA') THEN 'NORTH AMERICA'
ELSE 'EUROPE'
END AS CONTINENT
FROM CLIENTES_NEPTUNO
ORDER BY CONTINENT, COUNTRY;
Utilize the PEDIDOS_NEPTUNO table to generate a query that displays the fields ORDERID, COMPANYNAME, ORDERDATE,
and AMOUNT. Then, add a column named EVALUATION in which the values defined in the conditions are shown.
SELECT ORDERID, COMPANYNAME, ORDERDATE, AMOUNT,
CASE
WHEN AMOUNT > 700 THEN 'EXCELLENT'
WHEN AMOUNT > 500 THEN 'VERY GOOD'
WHEN AMOUNT > 250 THEN 'GOOD'
WHEN AMOUNT > 50 THEN 'REGULAR'
ELSE 'BAD'
END AS EVALUATION
FROM PEDIDOS_NEPTUNO
ORDER BY AMOUNT DESC;
Utilize the PRODUCTOS_NEPTUNO table to generate a query that displays the fields PRODUCTID, PRODUCTNAME,
CATEGORYNAME, and UNITPRICE. Add a column named TYPE in which the values defined in the conditions are shown.
SELECT PRODUCTID, PRODUCTNAME, CATEGORYNAME, UNITPRICE,
CASE
WHEN UNITPRICE > 100 THEN 'DELUXE'
WHEN UNITPRICE > 10 THEN 'REGULAR'
ELSE 'ECONOMIC'
END AS TYPE
FROM PRODUCTOS_NEPTUNO
ORDER BY UNITPRICE DESC;
Obtain a list of all babies born with less than 20 weeks of gestation. The list should display babies of any gender, therefore,
the query must be performed on the VARONES, MUJERES, and INDETERMINADOS tables.
SELECT * FROM MALES WHERE WEEKS < 20
UNION
SELECT * FROM FEMALES WHERE WEEKS < 20
UNION
SELECT * FROM UNDETERMINED WHERE WEEKS < 20;
Then, get a list of all babies born during the month of September, with more than 40 weeks of gestation, and born to married
Chilean mothers. The list should display babies of any gender, therefore, the query should be performed on the MALES,
FEMALES, and UNDETERMINED tables.
SELECT * FROM MALES WHERE BIRTHDATE LIKE '%/09/%' AND NATIONALITY = 'CHILEAN' AND MOTHER_MARITAL_STATUS
= 'MARRIED' AND WEEKS > 40
UNION
SELECT * FROM FEMALES WHERE BIRTHDATE LIKE '%/09/%' AND NATIONALITY = 'CHILEAN' AND
MOTHER_MARITAL_STATUS = 'MARRIED' AND WEEKS > 40
UNION
SELECT * FROM UNDETERMINED WHERE BIRTHDATE LIKE '%/09/%' AND NATIONALITY = 'CHILEAN' AND
MOTHER_MARITAL_STATUS = 'MARRIED' AND WEEKS > 40;
Obtain a list of all products (available for sale and suspended) whose price exceeds $80. The search should be performed
on the PRODUCTOS_NEPTUNO and PRODUCTOS_SUSPENDIDOS tables. Then, sort the result alphabetically by product
names.
SELECT * FROM PRODUCTOS_NEPTUNO
WHERE UNITPRICE > 80
UNION
SELECT * FROM SUSPENDED_PRODUCTS
WHERE UNITPRICE > 80
ORDER BY PRODUCTNAME;
Modify the previous query to add a column called CONDITION, which displays the text "FOR SALE" if the record comes from
the PRODUCTOS_NEPTUNO table; or the text "SUSPENDED" if the record comes from the PRODUCTOS_SUSPENDIDOS
table.
SELECT *, 'FOR SALE' AS CONDITION FROM PRODUCTOS_NEPTUNO
WHERE UNITPRICE > 80
UNION
SELECT *, 'SUSPENDED' AS CONDITION FROM PRODUCTOS_SUSPENDIDOS
WHERE UNITPRICE > 80
ORDER BY PRODUCTNAME;
Generate a list of all products belonging to the BEVERAGES category, regardless of whether they are for sale or suspended
(the search should be performed in the PRODUCTOS_NEPTUNO and PRODUCTOS_SUSPENDIDOS tables). Then, sort the
list alphabetically by product names.
SELECT *, 'FOR SALE' AS CONDITION FROM PRODUCTOS_NEPTUNO
WHERE CATEGORYNAME = 'BEVERAGES'
UNION
SELECT *, 'SUSPENDED' AS CONDITION FROM PRODUCTOS_SUSPENDIDOS
WHERE CATEGORYNAME = 'BEVERAGES'
ORDER BY PRODUCTNAME;
Duplicate the product whose ID is number 43 from the PRODUCTOS_NEPTUNO table into the PRODUCTOS_SUSPENDIDOS
table through an append query.
INSERT INTO PRODUCTOS_SUSPENDIDOS
(IDPRODUCTO, NOMBREPRODUCTO, NOMBRECONTACTO, NOMBRECATEGORIA, PRECIOUNIDAD,
SUSPENDIDO, IDPROVEEDOR)
SELECT IDPRODUCTO, NOMBREPRODUCTO, NOMBRECONTACTO, NOMBRECATEGORIA,
PRECIOUNIDAD, SUSPENDIDO, IDPROVEEDOR
FROM PRODUCTOS_NEPTUNO
WHERE IDPRODUCTO = 43 ;
Repeat the query generated in step 5 to observe that the number of products obtained remains the same.
SELECT * FROM PRODUCTOS_NEPTUNO WHERE CATEGORYNAME = 'BEVERAGES'
UNION
SELECT * FROM PRODUCTOS_SUSPENDIDOS WHERE CATEGORYNAME = 'BEVERAGES'
ORDER BY PRODUCTNAME;
Modify the query from step 5 to display the duplicated product.
SELECT * FROM PRODUCTOS_NEPTUNO WHERE CATEGORYNAME = 'BEVERAGES'
UNION ALL
SELECT * FROM PRODUCTOS_SUSPENDIDOS WHERE CATEGORYNAME = 'BEVERAGES'
ORDER BY PRODUCTNAME;
Delete the product whose ID is number 43 from the PRODUCTOS_SUSPENDIDOS table.
SET SQL_SAFE_UPDATES = 0;
DELETE FROM PRODUCTOS_SUSPENDIDOS WHERE IDPRODUCTO = 43;
Create a table named EQUIPOS with only one field named EQUIPO. This field should be of type VARCHAR, capable of storing
up to 20 characters, and it should be the primary key of the table.
CREATE TABLE EQUIPOS (
EQUIPO VARCHAR(20) PRIMARY KEY
);
Load the names of the following teams into the EQUIPOS table: ARGENTINA, BRASIL, PARAGUAY, CHILE, URUGUAY,
COLOMBIA, ECUADOR, PERÚ, BOLIVIA, VENEZUELA.
INSERT INTO EQUIPOS
VALUES ('ARGENTINA'), ('BRASIL'), ('CHILE'), ('PARAGUAY'), ('URUGUAY'),
('COLOMBIA'), ('ECUADOR'), ('PERÚ'), ('BOLIVIA'), ('VENEZUELA');
Generate a Cartesian product based on the same table to create a fixture where each team plays against the other teams
(one match as home team and another as away team). Sort the result alphabetically by the name of the home team.
SELECT [Link] AS LOCAL_TEAM, [Link] AS VISITOR_TEAM
FROM EQUIPOS L CROSS JOIN EQUIPOS V
WHERE [Link] <> [Link]
ORDER BY [Link];
Then, sort the result alphabetically by the names of the contacts, and when the contact name (provider's name) repeats,
sort the products provided by the provider, also alphabetically.
SELECT [Link], IDPRODUCTO, NOMBREPRODUCTO, PRECIOUNIDAD
FROM PROVEEDORES P JOIN PRODUCTOS_NEPTUNO PN
ON [Link] = [Link]
ORDER BY [Link], NOMBREPRODUCTO;
Create a listing showing the COMPANY field from the CLIENTS table and the fields ORDER_NUMBER, ORDER_DATE, and
PAYMENT_METHOD from the ORDERS table. Generate the JOIN using the FROM clause. Then, sort the listing alphabetically
by the names of the companies.
SELECT COMPANY, ORDER_NUMBER, ORDER_DATE, PAYMENT_METHOD
FROM CLIENTS C JOIN ORDERS O
ON C.CLIENT_CODE = O.CLIENT_CODE
ORDER BY COMPANY;
Modify the previous query to show only those customers who have not placed any orders.
SELECT COMPANY, ORDER_NUMBER, ORDER_DATE, PAYMENT_METHOD
FROM CLIENTS C LEFT JOIN ORDERS O
ON C.CLIENT_CODE = O.CLIENT_CODE
WHERE O.ORDER_NUMBER IS NULL
ORDER BY COMPANY;
Then, modify the previous query to show only the COMPANY field.
SELECT COMPANY
FROM CLIENTS C LEFT JOIN ORDERS O
ON C.CLIENT_CODE = O.CLIENT_CODE
WHERE O.ORDER_NUMBER IS NULL
ORDER BY COMPANY;
Is there any supplier that is currently not selling any products to our company? Answer this question through a query using
the SUPPLIERS and PRODUCTS_NEPTUNE tables. Display all fields from both tables in the query result.
SELECT * FROM SUPPLIERS S LEFT JOIN PRODUCTS_NEPTUNE PN
ON S.SUPPLIER_ID = PN.SUPPLIER_ID
WHERE PN.PRODUCT_ID IS NULL;
Is there any product for which we do not know who the supplier is? Answer this question through a query using the
SUPPLIERS and PRODUCTS_NEPTUNE tables. You can display all fields from both tables in the query result.
SELECT * FROM SUPPLIERS S RIGHT JOIN PRODUCTS_NEPTUNE PN
ON S.SUPPLIER_ID = PN.SUPPLIER_ID
WHERE S.SUPPLIER_ID IS NULL;