Exercise 1 - Exercise solved with 1 table

We are going to apply much of what we know to do a review exercise that makes different
manipulations on a single table. It will be a table that contains product data: code, name,
price and registration date, so that we can work with text, numeric, and date type data.
The steps we will take (in case anyone dares to try it before seeing the solution) will be:
• Create the database
• Start using it
• Introducir 3 datos de ejemplo
• Show all the data
• Show the data that has a certain name
• Show the data that begins with a certain initial
• Only the name and price of those who meet a condition (price > 22)
• See the average price of those whose name starts with "Silla"
• Modify the table structure to add a new field: 'category'
• Assign the value 'utensil' to the category of all existing products
• Modify the products that start with the word 'Chair', so that their category is 'chair'.
• See the list of categories (without duplicate data appearing)
• See the number of products we have in each category.

The first step is to create the database:

create database productos1;
And start using it:
use products1;
To create the table we would do:
create table products (
code varchar(3),
name varchar(30),
decimal price(6,2)
registration date
primary key (code)
);
To introduce several sample data:
insert into products values ('a01','Sharpener', 2.50, '2007-11-02');
insert into products values ('s01', 'Chair model ZAZ', 20, '2007-11-03');
insert into products values ('s02','Chair model XAX', 25, '2007-11-03');
We can see all the data to verify that it is correct:
select * from products;
and we should obtain

+--------+----------------+--------+------------+
code price
+--------+----------------+--------+------------+
a01 Sharpener 2.50
s01 Chair mod. ZAZ
| s02 Chair model XAX
+--------+----------------+--------+------------+

To see what products are called 'Sharpener':

select * from products where name='Sharpener';

+--------+----------+--------+------------+
code price
+--------+----------+--------+------------+
a01 Sharpener 2007-11-02
+--------+----------+--------+------------+

If we want to know which ones start with S:

select * from products where name like 'S%';

+--------+----------------+--------+------------+
code price
+--------+----------------+--------+------------+
| s01 Chair model ZAZ
s02 Chair model XAX
+--------+----------------+--------+------------+

If we want to see which ones have a price higher than 22, and we also do not want to see all the fields,
sino
select name, price from products where price > 22;

+----------------+--------+
name precio
+----------------+--------+
Chair mod. XAX
+----------------+--------+

Average price of chairs:

select avg(price) from products where left(name,5) = 'Chair';

+-------------+
average(price)
+-------------+
22.5
+-------------+

Looking at the first letters to know if it's a chair or not... maybe it's not the best option. It seems
It is more reasonable to add a new piece of data: the 'category'. We are going to modify the structure of the table to
do it
alter table products add category varchar(10);

We check what has happened with a 'select' that shows all the data:
select * from products;
+--------+----------------+--------+------------+-----------+
code price
+--------+----------------+--------+------------+-----------+
a01 Sharpener | 2.50 | 2007-11-02 | NULL
|
| s01 Chair model ZAZ |
| s02 Chair model XAX |
+--------+----------------+--------+------------+-----------+

Right now, all categories have the value NULL, and that is not very useful. We are going to assign a value.
"utensil" to the category of all existing products
update products set category='utensil';

And since we are at it, we will modify the products that start with the word 'Chair' so that their
category is 'chair'
update products set category='chair' where left(name,5) = 'Chair';

+--------+----------------+--------+------------+-----------+
code price
+--------+----------------+--------+------------+-----------+
a01 Sharpener | 2.50 | 2007-11-02 | utensilio |
s01 Chair mod. ZAZ |
s02 Chair model XAX |
+--------+----------------+--------+------------+-----------+

To see the list of categories (without duplicate data appearing), we must use the word 'distinct'.
select distinct category from products;

+-----------+
categoria
+-----------+
utensil
chair |
+-----------+

Finally, to see the number of products we have in each category, we should use "count" and
group the data with 'group by', like this:
select category, count(*) from products group by category;

+-----------+----------+
category
+-----------+----------+
chair | 2 |
utensil 1
+-----------+----------+
 Exercise 2 - Proposed exercise with 1 table
We want to create a database to store information about PDAs.
In a first approach, we will use a single table called PDA, which will have the following fields:
Code
Name
Operating System
Memory (mb)
Bluetooth (s/n)

1- Create the table.

2- Enter the data into it:

- ptx, Palm Tungsten TX, PalmOS, 128, s

p22, Palm Zire 22, PalmOS, 16, n
i3870, Compaq Ipaq 3870, Windows Pocket PC 2002, 64, s

Make the inquiries

3- Devices with more than 64 MB of memory.

4- Devices whose operating system is not 'PalmOS'.

5- Devices whose operating system contains the word 'Windows'.

6- List of operating systems (without duplicates)

7- Name and code of the equipment with the most memory.

8- Name and brand (we will assume that the brand is the first word of the name, up to the first
space) of each team, sorted by brand and then by name.

9- Devices with less memory than average.

10- Number of devices with each operating system.

11- Operating systems for which we have 2 or more machines in the database.

12- Add a 'price' field to the PDA table, with a default value of NULL.

13- Modify the data of the equipment with code 'p22' to indicate that its price is 119.50. List the
equipment whose price we do not know.
 Exercise 3 - Exercise solved with 2 tables
1- Create a database called 'sports', and within it two tables: player and team. From the player
desea almacenar: codigo (txt 12), nombre, apellido 1, apellido 2, demarcacion (ej: delantero). De cada
team: code (txt 8), name, sport (e.g., basketball). Each team will be made up of several
players, and we will assume that each player can only be part of one team.

2- Enter the data:

In teams:
- rcm, Real Campello, basketball
- can, Canoe, swimming
ssj, Sporting de San Juan, soccer
In players:
Raúl Martínez López
- rl, Raúl, López, , saltador (del Canoa)
Jordi López
Roberto Linares, base (plays for Real Campello)

3- Create a query that displays: athlete's name, first surname, position, name of
team (for all players in the database).

4- Create a query that shows the name of the teams for which we do not know the players.

5- Create a query that shows the first and last names of the players whose first or second last name
It's 'López'.

6- Create a query that shows the first name and last name of the swimmers.

7- Create a query that shows the number of players on each team.

8- Create a query that shows the number of players in each sport.

9- Create a query that shows the team with the most players.

10- Add a field to the players table where you can store the seniority (in years), which
set to NULL by default, and modify the record of 'Roberto Linares' to indicate that his
The seniority is 4 years.