--REPASO EL2
------------
use master
go

if db_id('mi_negocio') is not null
begin
	drop database mi_negocio
end
go

create database mi_negocio
go

USE mi_negocio
go

set dateformat ymd
go

CREATE TABLE pais (
  Idpais char(3) primary key,
  NombrePais varchar(40) not null
  )
go


CREATE TABLE Cliente (
  IdCliente char(5) primary key,
  NomCliente varchar(40) not null,
  DirCliente varchar(60) not null,
  idpais char(3) references pais,
  fonoCliente varchar(25) not null
)
go

CREATE TABLE Proveedor (
  IdProveedor int primary key,
  NomProveedor varchar(40) not null,
  DirProveedor varchar(60) not null,
  NomContacto varchar(80) not null,
  CargoContacto varchar(50) not null,
  idpais char(3) References pais,
  fonoProveedor varchar(15) not null,
  FaxProveedor varchar(15) not null
)
go

CREATE TABLE Producto (
  IdProducto int primary key,
  NomProducto varchar(40) not null,
  IdProveedor int References Proveedor,
  CantxUnidad INT not null,
  PrecioUnidad decimal(10,0) not null,
  UnidadesEnExistencia smallint not null,
  UnidadesEnPedido smallint not null)
go


/*2. INSERTAR 3 REGISTROS EN CADA TABLA.*/
INSERT INTO PAIS VALUES ('123','COLOMBIA')
INSERT INTO PAIS VALUES ('134','MEXICO')
INSERT INTO PAIS VALUES ('145','CUBA')
GO


INSERT INTO CLIENTE VALUES ('23489','JUAN','CALLE ANGAMOS','123','987654321')
INSERT INTO CLIENTE VALUES ('24578','ANTONIO','CALLE VILLA','134','987654325')
INSERT INTO CLIENTE VALUES ('25678','MARCOS','CALLE REAL','145','987568752')
GO

INSERT INTO PROVEEDOR VALUES ('385','PIERO', ' CALLE AMANCAES', 'ALFONSO', ' GERENTE', '123', '98765425', '2354876541' )
INSERT INTO PROVEEDOR VALUES ('356','MARCOS', ' CALLE ANGAMOS', 'MARCO', ' PRESIDENTE', '134', '98765425', '2354876541' )
INSERT INTO PROVEEDOR VALUES ('365','ANDREAS', ' CALLE BENAVIDES', 'JOHN', ' ADMINISTRADOR', '145', '98765425', '2354876541' )
GO


INSERT INTO PRODUCTO VALUES ('456','VERDURAS', '385', 20, 50.0, 50, 2 )
INSERT INTO PRODUCTO VALUES ('467','FRUTAS', '356', 40, 20.0, 20, 7 )
INSERT INTO PRODUCTO VALUES ('478','CARNE', '365', 30, 40.0, 30, 1 )
GO



--inner join y subconsulta




/*3. Obtener los datos de los paises que tienen
     clientes.*/
SELECT *
FROM PAIS AS P INNER JOIN CLIENTE AS C
ON P.Idpais= C.idpais
WHERE P.NombrePais LIKE 'C%'
GO

SELECT P.*
FROM PAIS AS P
WHERE P.Idpais IN ( SELECT C.idpais
			FROM CLIENTE AS C
			WHERE P.NombrePais LIKE 'C%')
GO




/* 4. Seleccionar los países que empiecen por C que tengan clientes ya registrados*/
IF OBJECT_ID('USP_IPA') IS NOT NULL
DROP PROC USP_IPA
GO
CREATE PROC USP_IPA
 @NOMPA VARCHAR(30)
 AS
 SELECT P.NombrePais
 FROM PAIS AS P INNER JOIN CLIENTE AS C
ON P.Idpais= C.idpais
WHERE P.NombrePais LIKE @NOMPA
GO
/*Ejecutar*/

EXEC USP_IPA 'C%'




/*5. Procedimiento almacenado que permite mostrar los
productos de un determinado proveedor,el nombre del
proveedor debe ser ingresado como parámetro.*/
IF OBJECT_ID('USP_NOP') IS NOT NULL
DROP PROC USP_NOP
GO
CREATE PROC USP_NOP
 @NOMPRO VARCHAR(30)
 AS
 SELECT P.NomProducto AS 'NOMBRE DEL PRODUCTO'
 FROM PRODUCTO AS P INNER JOIN PROVEEDOR AS PR
ON P.IdProveedor= PR.IdProveedor
WHERE PR.NomProveedor LIKE @NOMPRO
GO
/*Ejecutar*/

EXEC USP_NOP 'PIERO'




/*6. Procedimiento almacenado que permite mostrar los
proveedores de un determinado país, el nombre del país
debe ser ingresado como parámetrode entrada.*/
IF OBJECT_ID('USP_NOMPA') IS NOT NULL
DROP PROC USP_NOMPA
GO
CREATE PROC USP_NOMPA
 @NOMPA VARCHAR(30)
 AS
 SELECT PR.*
 FROM PAIS AS P INNER JOIN PROVEEDOR AS PR
ON PR.idpais= P.Idpais
WHERE P.NombrePais = @NOMPA
GO
/*Ejecutar*/

EXEC USP_NOMPA 'CUBA'