TALLER #4 ALMACENES XYZ

Microsoft Windows XP [Versión 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrador>cd..

C:\Documents and Settings>cd..

C:\>cd Archivos de programa\EasyPHP3.1\mysql\bin

C:\Archivos de programa\EasyPHP3.1\mysql\bin>mysql -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.35-community-log MySQL Community Server (GPL)

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


//////////////SE CREA LA BASE DE DATOS ALMACENESXYZ/////////////////


mysql> create database AlmacenesXYZ;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| almacenesxyz |
| mysql |
| phpmyadmin |
+--------------------+
4 rows in set (0.00 sec)

mysql> use almacenesxyz;
Database changed


///////////////////////SE CREAN LAS TABLAS Y SE VAN OSTRANDO////////////////////////////////


mysql> create table Clientes (idCliente varchar(10)not null,
nomCliente varchar(25)not null,
ApCliente varchar (25)not null,
dirCliente varchar(20)not null,
telCliente varchar(12)not null,primary key(idCliente));
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------------+
| Tables_in_almacenesxyz |
+------------------------+
| clientes |
+------------------------+
1 row in set (0.00 sec)

mysql> create table articulos(idArticulo varchar(10)not null,
nomArticulo varchar(10)not null,
valorArticulo float not null,
cantArticulo int not null,
descripcion varchar(50)not null, primary key(idArticulo));
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+------------------------+
| Tables_in_almacenesxyz |
+------------------------+
| articulos |
| clientes |
+------------------------+
2 rows in set (0.00 sec)

mysql> create table facturas(numeroFac varchar(10)not null,
fechaFact date not null, subtotal float not null,
iva float not null, totalfact float not null,
idCliente varchar(10) not null,
primary key (numeroFac),
foreign key(idCliente)references Clientes (idCliente));
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+------------------------+
| Tables_in_almacenesxyz |
+------------------------+
| articulos |
| clientes |
| facturas |
+------------------------+
3 rows in set (0.00 sec)

mysql> create table facturaArticulo (idArticulo varchar (10)not null,
numeroFac varchar(10)not null,
subtotalArt float not null,
cantArt int not null,
primary key (idArticulo, numeroFac),
foreign key (idArticulo) references articulos (idArticulo),
foreign key (numeroFac) references facturas (numeroFac));
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------------+
| Tables_in_almacenesxyz |
+------------------------+
| articulos |
| clientes |
| facturaarticulo |
| facturas |
+------------------------+
4 rows in set (0.00 sec)


///////////////////////////////SE INSERTAN LOS DATOS EN LAS TABLAS//////////////////////////////////////////////////


mysql> insert into clientes values
("001", "David", "Ballesteros", "Los Tejares", "3205489044"),
("002", "Jose", "Paternina", "Mochila", "3016970089"),
("003", "Cristian", "Ordosgoitia", "El Cortijo", "3017240312"),
("004", "Juan", "Perez", "Morroa", "3017309437");

Query OK, 4 rows affected (0.00 sec)
Registros: 4 Duplicados: 0 Peligros: 0

mysql> select * from clientes;
+-----------+------------+-------------+-------------+------------+
| idCliente | nomCliente | ApCliente | dirCliente | telCliente |
+-----------+------------+-------------+-------------+------------+
| 001 | David | Ballesteros | Los Tejares | 3205489044 |
| 002 | Jose | Paternina | Mochila | 3016970089 |
| 003 | Cristian | Ordosgoitia | El Cortijo | 3017240312 |
| 004 | Juan | Perez | Morroa | 3017309437 |
+-----------+------------+-------------+-------------+------------+
4 rows in set (0.00 sec)

mysql> insert into articulos values
("01", "Lavadora", "20000", "50", "Lavadora Oster"),
("02", "Nevera", "200000", "100", "Nevera Marca Mabe"),
("03", "Televisor", "500000", " 100", "Televisor LG 25 pulg.");

Query OK, 3 rows affected (0.00 sec)
Registros: 3 Duplicados: 0 Peligros: 0

mysql> select * from articulos;
+------------+-------------+---------------+--------------+-----------------------|
| idArticulo | nomArticulo |valorArticulo | cantArticulo | descripcion |
+------------+-------------+---------------+--------------+-----------------------|
| 01 | Lavadora | 20000 | 50 | Lavadora Oster |
| 02 | Nevera | 200000 | 100 | Nevera Marca Mabe |
| 03 | Televisor | 500000 | 100 | Televisor LG 25 pulg. |
+------------+-------------+---------------+--------------+-----------------------|
3 rows in set (0.00 sec)

mysql> insert into facturas values
("0001", "2009-09-26", 756000, 144000, 900000, "001"),
("0002", "2009-09-27", 588000, 112000, 700000, "002"),
("0003", "2009-09-30", 420000, 80000, 500000, "003");

Query OK, 3 rows affected (0.00 sec)
Registros: 3 Duplicados: 0 Peligros: 0

mysql> select * from facturas;
+-----------+------------+----------+--------+-----------+-----------+
| numeroFac | fechaFact | subtotal | iva | totalfact | idCliente |
+-----------+------------+----------+--------+-----------+-----------+
| 0001 | 2009-09-26 | 756000 | 144000 | 900000 | 001 |
| 0002 | 2009-09-27 | 588000 | 112000 | 700000 | 002 |
| 0003 | 2009-09-30 | 420000 | 80000 | 500000 | 003 |
+-----------+------------+----------+--------+-----------+-----------+
3 rows in set (0.00 sec)

mysql> insert into facturaArticulo values
("01", "0001", 20000, 5), ("02", "0002", 200000, 2),
("03", "0003", 500000, 3);

Query OK, 3 rows affected (0.00 sec)
Registros: 3 Duplicados: 0 Peligros: 0

mysql> select * from facturaArticulo;
+------------+-----------+-------------+---------+
| idArticulo | numeroFac | subtotalArt | cantArt |
+------------+-----------+-------------+---------+
| 01 | 0001 | 20000 | 5 |
| 02 | 0002 | 200000 | 2 |
| 03 | 0003 | 500000 | 3 |
+------------+-----------+-------------+---------+
3 rows in set (0.00 sec)


/////////////////////////////////////SE REALIZA LA CONSULTA DE LOS CLIENTES ORDENADOS POR APELLIDO////////////////////////////////////////////


mysql> select * from clientes order by ApCliente;
+-----------+------------+-------------+-------------+------------+
| idCliente | nomCliente | ApCliente | dirCliente | telCliente |
+-----------+------------+-------------+-------------+------------+
| 001 | David | Ballesteros | Los Tejares | 3205489044 |
| 004 | Fabian | Diaz | Morroa | 3017309437 |
| 003 | Cristian | Ordosgoitia | El Cortijo | 3017240312 |
| 002 | Jose | Paternina | Mochila | 3016970089 |
+-----------+------------+-------------+-------------+------------+
4 rows in set (0.00 sec)


/////////////////////////////////////SE REALIZA LA CONSULTA DEL NOMBRE Y VALOR DE LOS ARTÍCULOS ORDENADOS POR NOMBRE////////////////////////////////////////


mysql> select nomArticulo, valorArticulo from articulos order by nomArticulo;
+-------------+---------------+
| nomArticulo | valorArticulo |
+-------------+---------------+
| Lavadora | 20000 |
| Nevera | 200000 |
| Televisor | 500000 |
+-------------+---------------+
3 rows in set (0.00 sec)



////////////////////////////////EL NUMERO DE FACTURA TOTAL Y EL NOMBRE DEL CLIENTE DE TODAS LAS FACTURASHECHAS A NOMBRE DE JUAN PEREZ//////////////////////////////////////////



mysql> smysql> select facturas.numerofac, totalfact, nomcliente from clientes, facturas where ((facturas.idcliente=clientes.idcliente) and (nomcliente="Juan") and (apcliente="Perez"));
+-----------+-----------+------------+
| numerofac | totalfact | nomcliente |
+-----------+-----------+------------+
| 1 | 170000 | Juan |
+-----------+-----------+------------+
1 row in set (0.02 sec)


//////////////////////////////////LOS NUMERIS Y FECHAS DE LAS FACTURAS REALIZADAS EL DIA DE HOY/////////////////////////////////////////////////


mysql> select numeroFac, fechaFact from Facturas where fechaFact="2009-10-07";


//////////////////////////////////////////TODOS LOS ARTICULOS FACTURADOS EL LA FACTURA CON NUMERO 0001////////////////////////////////////////



mysql> select nomArticulo, idarticulo from articulos, facturaarticulo where ((articulos.idarticulo=facturaarticulo.idarticulo) and (numerofac=0001));





HECHO POR : DAVID MIGUEL BALLESTEROS ROJAS
JOSE VITOLA PATERNINA

INGENIERIA DE SISTEMAS 4 - SEMESTRE