S2.02 - MySQL Queries

---

🎯 Objectives

We have the following tables with the columns associated between brackets:

• producto (codigo, nombre, precio, codigo_fabricante (FK))
• fabricante (codigo (IK), nombre) The objective is to perform queries in order to understand the SQL language through practice.

---

📝 Problem Statement:

The following SQL queries have to be performend in MySQL in order to extract the necessary information.

Database 1 Queries – Tienda Database

We worked with the Tienda database, which contains the following tables:

Table	Columns
producto	codigo, nombre, precio, codigo_fabricante
fabricante	codigo, nombre

Nº	Query Requirement
1	List the name of all products in the producto table.
2	List the names and prices of all products in the producto table.
3	List all the columns of the producto table.
4	List the product name, price in euros, and price in US dollars (USD).
5	List the product name, price in euros, and price in US dollars (USD) using the following column aliases: product name, euros, dollars.
6	List the names and prices of all products from the producto table, converting the names to uppercase.
7	List the names and prices of all products from the producto table, converting the names to lowercase.
8	List all manufacturer names in one column, and in another column display the first two characters of the manufacturer name in uppercase.
9	List the names and prices of all products from the producto table, rounding the price value.
10	List the names and prices of all products from the producto table, truncating the price to show no decimal places.
11	List the manufacturer codes that have products in the producto table.
12	List the manufacturer codes that have products in the producto table, removing duplicates.
13	List the manufacturer names in ascending order.
14	List the manufacturer names in descending order.
15	List the product names ordered first by name ascending, then by price descending.
16	Return the first 5 rows of the fabricante table.
17	Return 2 rows starting from the 4th row of the fabricante table (including the 4th row).
18	List the name and price of the cheapest product. (Use only ORDER BY and LIMIT, not MIN).
19	List the name and price of the most expensive product. (Use only ORDER BY and LIMIT, not MAX).
20	List the names of all products where the manufacturer code is equal to 2.
21	Return a list with product name, price, and manufacturer name of all products.
22	Return a list with product name, price, and manufacturer name of all products, ordered by manufacturer name alphabetically.
23	Return a list with product code, product name, manufacturer code, and manufacturer name for all products.
24	Return the name, price, and manufacturer of the cheapest product.
25	Return the name, price, and manufacturer of the most expensive product.
26	Return all products from manufacturer Lenovo.
27	Return all products from manufacturer Crucial with a price greater than €200.
28	Return all products from manufacturers Asus, Hewlett-Packard, and Seagate (without using IN).
29	Return all products from manufacturers Asus, Hewlett-Packard, and Seagate (using IN).
30	Return the name and price of all products whose manufacturer name ends with the vowel "e".
31	Return the name and price of all products whose manufacturer name contains the character "w".
32	Return product name, price, and manufacturer name for products priced ≥ 180 €, ordered first by price (descending) then by name (ascending).
33	Return manufacturer code and name, but only for manufacturers with associated products.
34	Return all manufacturers with their products (also show manufacturers without products).
35	Return only manufacturers that have no associated products.
36	Return all products from manufacturer Lenovo (without using INNER JOIN).
37	Return all product data with the same price as Lenovo’s most expensive product (without using INNER JOIN).
38	List the name of the most expensive product from manufacturer Lenovo.
39	List the name of the cheapest product from manufacturer Hewlett-Packard.
40	Return all products priced greater than or equal to Lenovo’s most expensive product.
41	List all Asus products with a price higher than the average price of Asus products.

---

Database 2 Queries – University Database

The University database was provided in the file schema_universidad.sql. The ER diagram was visualized using MySQL Workbench.

General Queries

Nº	Query Requirement
1	Return a list with surname1, surname2, and name of all students. Ordered alphabetically by surname1, surname2, and name.
2	Find the name and surnames of students without a registered phone number.
3	Return the list of students born in 1999.
4	Return the list of professors without a registered phone number and with NIF ending in K.
5	Return the list of subjects taught in the first semester, third year of degree with ID = 7.
6	Return a list of professors with their department (columns: surname1, surname2, name, department). Ordered alphabetically.
7	Return a list of subjects, start year, and end year of the academic course of the student with NIF 26902806M.
8	Return a list of departments with professors teaching in Computer Science (Plan 2015).
9	Return a list of all students enrolled in any subject during the academic year 2018/2019.

JOIN Queries

Nº	Query Requirement
1	List professors and their departments (including professors without departments). Ordered by department, surnames, and name.
2	Return professors not associated with any department.
3	Return departments with no professors.
4	Return professors who do not teach any subject.
5	Return subjects with no professor assigned.
6	Return departments that have not taught any subject in any academic year.

Summary Queries

Nº	Query Requirement
1	Return the total number of students.
2	Count students born in 1999.
3	Count professors per department (only those with professors). Ordered descending by count.
4	Return all departments with the number of professors (including departments with none).
5	Return all degrees with the number of subjects. Ordered descending by number of subjects.
6	Return degrees with more than 40 subjects.
7	Return degree name, subject type, and total credits per subject type.
8	Return the number of students enrolled in each academic year.
9	Return number of subjects taught by each professor (including those with none). Columns: id, name, surname1, surname2, count. Ordered descending.
10	Return all data of the youngest student.
11	Return professors with a department but no subjects taught.

---

🏆 Level Certification

Level	Requirement
1	37 correct queries (20 from Universidad)
2	37–56 correct queries (20 from Universidad)
3	>56 correct queries (20 from Universidad)

---

💻 Technologies Used

• MySQL Workbench → relational DB visualization & SQL queries
• Git → version control

---

📋 Requirements

• MySQL Server 8+
• MySQL Workbench

---

🛠️ Setup

1. Download the schema to create the database (e.g., schema_universidad.sql)
2. Run the script in MySQL Workbench to create the database.
3. Use MySQL Workbench for testing.

---

🔗 Contributions

The project ignores .idea/, .out/, .iml.

1. Fork the repository
2. Create a new branch: git checkout -b feature/NewFeature
3. Make your changes and commit them: git commit -m 'Add New Feature'
4. Push the changes to your branch: git push origin feature/NewFeature
5. Open a pull request