Atividad 4

Comando Group BY

Clicl aqui para archivo sql hecho 

Microsoft Windows [Versión 10.0.22000.675]

(c) Microsoft Corporation. Todos los derechos reservados.

C:\Users\Santiaguito>c:

C:\Users\Santiaguito>cd/xampp/mysql/bin

C:\xampp\mysql\bin>mysql -uroot -p

Enter password:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 8

Server version: 10.4.28-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> source C:\xampp\turismo.sql

Query OK, 0 rows affected (0.002 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.002 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 1 row affected (0.005 sec)

Database changed

Query OK, 0 rows affected (0.007 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.020 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 0 rows affected (0.001 sec)

Query OK, 11 rows affected (0.007 sec)

Records: 11 Duplicates: 0 Warnings: 0

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.003 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.001 sec)

MariaDB [turismo]> show databases;

+--------------------+

• Database |

+--------------------+

• information_schema |
• libreria |
• mysql |
• performance_schema |
• phpmyadmin |
• test |
• turismo |
• universidad |

+--------------------+

8 rows in set (0.026 sec)

MariaDB [turismo]> show turismo

-> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'turismo' at line 1

MariaDB [turismo]> show database turismo

-> ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'database turismo' at line 1

MariaDB [turismo]> show tables

-> ;

+-------------------+

• Tables_in_turismo |

+-------------------+

• visitantes |

+-------------------+

1 row in set (0.003 sec)

MariaDB [turismo]> select * visitantes;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'visitantes' at line 1

MariaDB [turismo]> select * table visitantes;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'table visitantes' at line 1

MariaDB [turismo]> describe visitantes;

+-------------+----------------+------+-----+---------+----------------+

• Field | Type | Null | Key | Default | Extra |

+-------------+----------------+------+-----+---------+----------------+

• id | int(11) | NO | PRI | NULL | auto_increment |
• nombre | char(40) | NO | | NULL | |
• ciudad | char(15) | NO | | NULL | |
• sexo | char(15) | NO | | NULL | |
• montocompra | float unsigned | YES | | NULL | |

+-------------+----------------+------+-----+---------+----------------+

5 rows in set (0.006 sec)

MariaDB [turismo]> 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.006 sec)

MariaDB [turismo]> 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.002 sec)

MariaDB [turismo]> 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.019 sec)

MariaDB [turismo]> 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.002 sec)

MariaDB [turismo]> select ciudad, sum(montocompra) as Mayores_a_5000000 from visitantes group by ciudad having sum(montocompra)>5000000;

+--------------+-------------------+

• ciudad | Mayores_a_5000000 |

+--------------+-------------------+

• Barranquilla | 8300000 |
• Cartagena | 12100000 |

+--------------+-------------------+

2 rows in set (0.001 sec)

MariaDB [turismo]> create view visitantesa as select * from visitantes where nombre like'%a';

Query OK, 0 rows affected (0.012 sec)

MariaDB [turismo]> show visitantesa;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'visitantesa' at line 1

MariaDB [turismo]> describe visitantesa;

+-------------+----------------+------+-----+---------+-------+

• Field | Type | Null | Key | Default | Extra |

+-------------+----------------+------+-----+---------+-------+

• id | int(11) | NO | | 0 | |
• nombre | char(40) | NO | | NULL | |
• ciudad | char(15) | NO | | NULL | |
• sexo | char(15) | NO | | NULL | |
• montocompra | float unsigned | YES | | NULL | |

+-------------+----------------+------+-----+---------+-------+

5 rows in set (0.006 sec)

MariaDB [turismo]> create view visitantesb as select * from visitantes where sexo= 'Masculino';

Query OK, 0 rows affected (0.004 sec)

MariaDB [turismo]>