parcial final
mysql> show tables;
+-------------------------+
| Tables_in_concesionario |
+-------------------------+
| cilindraje |
| refcil |
| referencia |
| tipo |
| veiculo |
| veitip |
+-------------------------+
6 rows in set (0.00 sec)
mysql> describe cilindraje;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| codcil | char(3) | NO | PRI | NULL | |
| cilindraje | char(4) | NO | | NULL | |
+------------+---------+------+-----+---------+-------+
2 rows in set (0.05 sec)
mysql> describe refcil;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| codref | char(3) | NO | MUL | NULL | |
| codcil | char(3) | NO | MUL | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql> describe referencia;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| codref | char(3) | NO | PRI | NULL | |
| referencia | char(20) | NO | | NULL | |
| costo | int(10) | NO | | NULL | |
+------------+----------+------+-----+---------+-------+
3 rows in set (0.08 sec)
mysql> describe tipo;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| codtip | char(4) | NO | PRI | NULL | |
| tipo | char(20) | NO | | NULL | |
+--------+----------+------+-----+---------+-------+
2 rows in set (0.03 sec)
mysql> describe veiculo;
+--------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| codvei | char(3) | NO | PRI | NULL | |
| Nombre | char(20) | NO | | NULL | |
| modelo | char(4) | NO | | NULL | |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.07 sec)
mysql> describe veitip;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| codvei | char(3) | NO | MUL | NULL | |
| codtip | char(3) | NO | MUL | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.03 sec)
mysql> select * from cilindraje;
+--------+------------+
| codcil | cilindraje |
+--------+------------+
| x01 | 250 |
| x02 | 300 |
| x03 | 650 |
| x04 | 800 |
| x05 | 1000 |
| x06 | 680 |
| x07 | 660 |
| x08 | 450 |
| x09 | 150 |
| x10 | 110 |
| x11 | 140 |
| x12 | 115 |
| x13 | 125 |
| x14 | 600 |
+--------+------------+
14 rows in set (0.00 sec)
mysql> select * from refcil;
Empty set (0.00 sec)
mysql> select * from referencia;
+--------+------------+-----------+
| codref | referencia | costo |
+--------+------------+-----------+
| 001 | ninja | 123000000 |
| 002 | er | 15000000 |
| 003 | versys | 25000000 |
| 004 | zx | 32000000 |
| 005 | kx | 10000000 |
| 006 | klx | 16000000 |
| 007 | bws | 5000000 |
| 008 | crypton | 7000000 |
| 009 | libero | 7000000 |
| 010 | fz | 8000000 |
| 011 | fazer | 8000000 |
| 012 | xt | 12000000 |
| 013 | mt | 15000000 |
| 015 | yzf | 15000000 |
| 016 | xtz | 20000000 |
| 017 | gsx | 50000000 |
| 018 | dr | 20000000 |
| 019 | vstrom | 22000000 |
+--------+------------+-----------+
18 rows in set (0.00 sec)
mysql> select * from veiculo;
+--------+----------+--------+
| codvei | Nombre | modelo |
+--------+----------+--------+
| 001 | yamaha | 2014 |
| 002 | kawasaky | 2010 |
| 003 | honda | 2009 |
| 004 | suzuki | 2014 |
| 005 | ktm | 2014 |
| 006 | kymco | 2009 |
| 007 | bmw | 2010 |
+--------+----------+--------+
7 rows in set (0.00 sec)
mysql> select * from tipo;
+--------+------------------+
| codtip | tipo |
+--------+------------------+
| m01 | semi-automatica |
| m02 | urbanas |
| m03 | turismo |
| m04 | deportivas |
| m05 | super deportivas |
| m06 | enduro |
| m07 | todo terreno |
+--------+------------------+
7 rows in set (0.00 sec)
mysql> describe refcil;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| codref | char(3) | NO | MUL | NULL | |
| codcil | char(3) | NO | MUL | NULL | |
+--------+---------+------+-----+---------+-------+
mysql> insert into refcil(codref,codcil) values("003","x03");
Query OK, 1 row affected (0.03 sec)
mysql> insert into refcil(codref,codcil) values("002","x01");
Query OK, 1 row affected (0.02 sec)
mysql> insert into refcil(codref,codcil) values("004","x04");
Query OK, 1 row affected (0.02 sec)
mysql> insert into refcil(codref,codcil) values("005","x05");
Query OK, 1 row affected (0.02 sec)
mysql> insert into refcil(codref,codcil) values("006","x06");
Query OK, 1 row affected (0.02 sec)
mysql> insert into refcil(codref,codcil) values("007","x07");
Query OK, 1 row affected (0.03 sec)
mysql> insert into refcil(codref,codcil) values("008","x08");
Query OK, 1 row affected (0.03 sec)
mysql> insert into refcil(codref,codcil) values("009","x09");
Query OK, 1 row affected (0.03 sec)
mysql> select * from referencia where costo>12000000;
+--------+------------+-----------+
| codref | referencia | costo |
+--------+------------+-----------+
| 001 | ninja | 123000000 |
| 002 | er | 15000000 |
| 003 | versys | 25000000 |
| 004 | zx | 32000000 |
| 006 | klx | 16000000 |
| 013 | mt | 15000000 |
| 015 | yzf | 15000000 |
| 016 | xtz | 20000000 |
| 017 | gsx | 50000000 |
| 018 | dr | 20000000 |
| 019 | vstrom | 22000000 |
+--------+------------+-----------+
11 rows in set (0.02 sec)
mysql> select * from veiculo where nombre like'k%';
+--------+----------+--------+
| codvei | Nombre | modelo |
+--------+----------+--------+
| 002 | kawasaky | 2010 |
| 005 | ktm | 2014 |
| 006 | kymco | 2009 |
+--------+----------+--------+
3 rows in set (0.00 sec)
mysql> select * from tipo where tipo like '%s' or tipo like'%a';
+--------+------------------+
| codtip | tipo |
+--------+------------------+
| m01 | semi-automatica |
| m02 | urbanas |
| m04 | deportivas |
| m05 | super deportivas |
+--------+------------------+
4 rows in set (0.00 sec)mysql> select * from referencia where costo>=30000000 and referencia like'%x';
+--------+------------+----------+
| codref | referencia | costo |
+--------+------------+----------+
| 004 | zx | 32000000 |
| 017 | gsx | 50000000 |
+--------+------------+----------+
2 rows in set (0.00 sec)
mysql> select sum(costo) 'total libro' from referencia;
+-------------+
| total libro |
+-------------+
| 410000000 |
+-------------+
1 row in set (0.02 sec)
mysql> select * from tipo where codtip in('m01','m04','m07');
+--------+-----------------+
| codtip | tipo |
+--------+-----------------+
| m01 | semi-automatica |
| m04 | deportivas |
| m07 | todo terreno |
+--------+-----------------+
3 rows in set (0.03 sec)
mysql> select * from veiculo where codvei between '001' and '004';
+--------+----------+--------+
| codvei | Nombre | modelo |
+--------+----------+--------+
| 001 | yamaha | 2014 |
| 002 | kawasaky | 2010 |
| 003 | honda | 2009 |
| 004 | suzuki | 2014 |
+--------+----------+--------+
4 rows in set (0.00 sec)
mysql> alter table referencia change costo precio float(10) not null;
Query OK, 18 rows affected (0.51 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> select * from referencia
-> ;
+--------+------------+-----------+
| codref | referencia | precio |
+--------+------------+-----------+
| 001 | ninja | 123000000 |
| 002 | er | 15000000 |
| 003 | versys | 25000000 |
| 004 | zx | 32000000 |
| 005 | kx | 10000000 |
| 006 | klx | 16000000 |
| 007 | bws | 5000000 |
| 008 | crypton | 7000000 |
| 009 | libero | 7000000 |
| 010 | fz | 8000000 |
| 011 | fazer | 8000000 |
| 012 | xt | 12000000 |
| 013 | mt | 15000000 |
| 015 | yzf | 15000000 |
| 016 | xtz | 20000000 |
| 017 | gsx | 50000000 |
| 018 | dr | 20000000 |
| 019 | vstrom | 22000000 |
+--------+------------+-----------+
18 rows in set (0.03 sec)
mysql> select veiculo.codvei, veiculo.nombre from veiculo right join veitip on v
eiculo.codvei=veitip.codvei;
+--------+----------+
| codvei | nombre |
+--------+----------+
| 001 | yamaha |
| 002 | kawasaky |
| 003 | honda |
| 004 | suzuki |
| 005 | ktm |
| 006 | kymco |
| 007 | bmw |
+--------+----------+
7 rows in set (0.03 sec)
mysql> select veiculo.codvei, veiculo.nombre, veitip.codvei, veitip.codtip, tipo
.codtip, tipo.tipo from veiculo inner join veitip on veiculo.codvei=veitip.codve
i inner join tipo on veitip.codtip=tipo.codtip;
+--------+----------+--------+--------+--------+------------------+
| codvei | nombre | codvei | codtip | codtip | tipo |
+--------+----------+--------+--------+--------+------------------+
| 001 | yamaha | 001 | m01 | m01 | semi-automatica |
| 002 | kawasaky | 002 | m02 | m02 | urbanas |
| 003 | honda | 003 | m03 | m03 | turismo |
| 004 | suzuki | 004 | m04 | m04 | deportivas |
| 005 | ktm | 005 | m05 | m05 | super deportivas |
| 006 | kymco | 006 | m06 | m06 | enduro |
| 007 | bmw | 007 | m07 | m07 | todo terreno |
+--------+----------+--------+--------+--------+------------------+
7 rows in set (0.02 sec)
mysql>