Exercicio sql
VALUES ('Playstation 2','Sony','04-03-2000')
INSERT INTO Console
VALUES ('Playstation 3','Sony','23-03-2007')
INSERT INTO Console
VALUES ('Xbox 360','Microsoft','22-11-2005')
INSERT INTO Console
VALUES ('Wii','Nintendo','19-11-2006')
INSERT INTO Jogo
VALUES ('Tomb Raider',3,'',12.00)
INSERT INTO Jogo
VALUES ('Super Smash Bros',4,'',10.00)
INSERT INTO Jogo
VALUES ('Metal Gear Solid 4',2,'',11.00)
INSERT INTO Jogo
VALUES ('Shadow of the Colossus',1,'',10.00)
INSERT INTO TbCliente
VALUES ('Roger Santos','Rua Professor Alegre',33220001,'02-01-2012','')
INSERT INTO TbCliente
VALUES ('Mauricio Oliveira','Av. Principal',88810911,'20-04-2013','')
INSERT INTO TbCliente
VALUES ('Monkey D. Luffy','Rua Thousand Sunny',50000000,'10-09-2009','')
INSERT INTO Locacao
VALUES (1,2.5,'12-04-2012','')
INSERT INTO Locacao
VALUES (2,0,'02-11-2013','')
INSERT INTO Locacao
VALUES (1,0,'10-02-2010','')
INSERT INTO JogosLocacao
VALUES (1,1,9.5)
INSERT INTO JogosLocacao
VALUES (2,3,11.0)
INSERT INTO JogosLocacao
VALUES (8,4,10.0)
----VIEWS E SUBCONSULTAS
--a)
CREATE VIEW vwNotNull
AS SELECT Cliente.ClienteID, Cliente.Nome, Cliente.Endereco, Cliente.DataCadastro, Locacao.LocacaoID, Locacao.NumeroCupom, Locacao.DataLocacao FROM Cliente
INNER JOIN Locacao
ON Cliente.ClienteID = Locacao.ClienteID
--b)
SELECT TbCliente.Nome, Locacao.DataLocacao FROM Locacao
INNER JOIN TbCliente
ON Locacao.ClienteID = TbCliente.ClienteID
WHERE Locacao.LocacaoID IN ( SELECT Locacao.LocacaoID FROM Locacao INNER JOIN JogosLocacao ON Locacao.LocacaoID = JogosLocacao.LocacaoID GROUP BY Locacao.LocacaoID HAVING COUNT(*) > 3 )
----STORED PROCEDURES
--a)
CREATE PROCEDURE spQtdeLocacao
@IDCliente int,
@Qtde int output
AS
BEGIN
SET @Qtde = ( SELECT COUNT(*) FROM Locacao