Taller Agrupar
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| autoincremento |
| cdcol |
| colegio |
| fechas |
| libreria |
| mysql |
| performance_schema |
| phpmyadmin |
| precios |
| test |
| turismo |
| webauth |
+--------------------+
13 rows in set (0.14 sec)
mysql> use turismo;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_turismo |
+-------------------+
| visitantes |
+-------------------+
1 row in set (0.00 sec)
mysql> select* from visitantes;
+----+--------------------------+--------------+-----------+-------------+
| id | nombre | ciudad | sexo | montocompra |
+----+--------------------------+--------------+-----------+-------------+
| 1 | Juan carlos Perez | Bogota | Masculino | 1500000 |
| 2 | Elizabeth Torres | Bogota | Femenino | 1000000 |
| 3 | Carmen Elvira Montes | Medellin | Femenino | 2000000 |
| 4 | Diana Patricia Garcia | Medellin | Femenino | 2500000 |
| 5 | Juan Alberto Cardona | Barranquilla | Masculino | 3500000 |
| 6 | Felipe Ardila Castro | Barranquilla | Masculino | 3000000 |
| 7 | Mariana Ocampo Cifuentes | Barranquilla | Femenino | 1800000 |
| 8 | Mirian Ocampo Cifuentes | Cartagena | Femenino | 1600000 |
| 9 | Martha Betancurt Ossa | Cartagena | Femenino | 6000000 |
| 10 | Mateo Vasquez Posada | Cartagena | Masculino | 3000000 |
| 11 | Pablo Andres Arboleda | Cartagena | Masculino | 1500000 |
+----+--------------------------+--------------+-----------+-------------+
11 rows in set (0.00 sec)
mysql> select ciudad,count(ciudad)as cantvis from visitantes group by ciudad;
+--------------+---------+
| ciudad | cantvis |
+--------------+---------+
| Barranquilla | 3 |
| Bogota | 2 |
| Cartagena | 4 |
| Medellin | 2 |
+--------------+---------+
4 rows in set (0.01 sec)
mysql> select ciudad,sum(montocompra)as totalcompra from visitantes group by ciudad;
+--------------+-------------+
| ciudad | totalcompra |
+--------------+-------------+
| Barranquilla | 8300000 |
| Bogota | 2500000 |
| Cartagena | 12100000 |
| Medellin | 4500000 |
+--------------+-------------+
4 rows in set (0.00 sec)
mysql> select sexo,sum(montocompra)as totalcompra from visitantes group by sexo;
+-----------+-------------+
| sexo | totalcompra |
+-----------+-------------+
| Femenino | 14900000 |
| Masculino | 12500000 |
+-----------+-------------+
2 rows in set (0.00 sec)
mysql> select ciudad,count(ciudad)as cant_visitantes from visitantes group by ciudad having count(ciudad)>2;
+--------------+-----------------+
| ciudad | cant_visitantes |
+--------------+-----------------+
| Barranquilla | 3 |
| Cartagena | 4 |
+--------------+-----------------+
2 rows in set (0.05 sec)
mysql> select ciudad,sexo,avg(montocompra)as promedio_compra from visitantes group by ciudad,sexo;
+--------------+-----------+-----------------+
| ciudad | sexo | promedio_compra |
+--------------+-----------+-----------------+
| Barranquilla | Femenino | 1800000 |
| Barranquilla | Masculino | 3250000 |
| Bogota | Femenino | 1000000 |
| Bogota | Masculino | 1500000 |
| Cartagena | Femenino | 3800000 |
| Cartagena | Masculino | 2250000 |
| Medellin | Femenino | 2250000 |
+--------------+-----------+-----------------+
7 rows in set (0.00 sec)
mysql> select ciudad,sum(montocompra) as mayores_a5000000 from visitantes group by ciudad;
+--------------+------------------+
| ciudad | mayores_a5000000 |
+--------------+------------------+
| Barranquilla | 8300000 |
| Bogota | 2500000 |
| Cartagena | 12100000 |
| Medellin | 4500000 |
+--------------+------------------+
4 rows in set (0.00 sec)
mysql> select ciudad,sum(montocompra) as mayores_a5000000 from visitantes group by ciudad having sum(montocompra)>5000000;
+--------------+------------------+
| ciudad | mayores_a5000000 |
+--------------+------------------+
| Barranquilla | 8300000 |
| Cartagena | 12100000 |
+--------------+------------------+
2 rows in set (0.00 sec)
mysql> create view visitantesa as select * from visitantes where nombre like'%a';
Query OK, 0 rows affected (0.06 sec)
mysql> create view visitantesb as select * from visitantes where nombre like'a%';
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-------------------+
| Tables_in_turismo |
+-------------------+
| visitantes |
| visitantesa |
| visitantesb |
+-------------------+
3 rows in set (0.00 sec)
mysql> select * from visitantesa;
+----+-----------------------+--------------+-----------+-------------+
| id | nombre | ciudad | sexo | montocompra |
+----+-----------------------+--------------+-----------+-------------+
| 4 | Diana Patricia Garcia | Medellin | Femenino | 2500000 |
| 5 | Juan Alberto Cardona | Barranquilla | Masculino | 3500000 |
| 9 | Martha Betancurt Ossa | Cartagena | Femenino | 6000000 |
| 10 | Mateo Vasquez Posada | Cartagena | Masculino | 3000000 |
| 11 | Pablo Andres Arboleda | Cartagena | Masculino | 1500000 |
+----+-----------------------+--------------+-----------+-------------+
5 rows in set (0.00 sec)
mysql> select * from visitantesb;
Empty set (0.00 sec)
mysql> exit