· 8 years ago · Jan 24, 2018, 08:14 AM
1Linguagem SQL
2Guia Prático de Aprendizagem
3Luciana Ferreira Baptista
4Respostas dos ExercÃcios
5Editora Érica Ltda.
62
7Linguagem SQL - Guia Prático de Aprendizagem
8CapÃtulo 1
91.
10CREATE DATABASE Concessionaria
112.
12USE Concessionaria
133.
14CREATE TABLE Veiculo (
15chassi CHAR(17) PRIMARY KEY,
16marca VARCHAR(10),
17modelo VARCHAR(20),
18anoFabricacao INT,
19anoModelo INT,
20combustivel CHAR(1)
21)
224.
23ALTER TABLE Veiculo
24ADD valor money, motor VARCHAR(20)
255.
26ALTER TABLE Veiculo
27DROP COLUMN motor
286.
29CREATE INDEX VeiculoMarcaModelo
30ON Veiculo (marca, modelo)
317.
32CREATE INDEX VeiculoAnoFabricacao
33ON Veiculo (anoFabricacao DESC)
348.
35DROP INDEX VeiculoMarcaModelo
36ON Veiculo
379.
38DROP TABLE Veiculo
3910.
40USE master
41DROP DATABASE Concessionaria
423
43Respostas dos ExercÃcios
44CapÃtulo 2
451.
46INSERT INTO Funcionario
47(idFuncionario, nome, endereco, cidade, estado, email, dataNascto)
48VALUES
49(5, ‘Carlos Dias’, ‘Av. Lapa, 121’, ‘Itu’, ‘SP’, ‘carlao@gmail.com’,
50‘1990-03-31’);
51INSERT INTO Funcionario
52(idFuncionario, nome, endereco, cidade, estado, email, dataNascto)
53VALUES
54(6, ‘Ana Maria da Cunha’, ‘Av. São Paulo, 388’, ‘Itu’, ‘SP’,
55‘aninhacunha@gmail.com’, ‘1988-04-12’);
56INSERT INTO Funcionario
57(idFuncionario, nome, endereco, cidade, estado, email, dataNascto)
58VALUES
59(7, ‘Cláudia Regina Martins’, ‘Rua Holanda, 89’, ‘Campinas’, ‘SP’,
60‘cregina@gmail.com’, ‘1988-12-04’);
61INSERT INTO Funcionario
62(idFuncionario, nome, endereco, cidade, estado, email, dataNascto)
63VALUES
64(8, ‘Marcela Tatho’, ‘Rua Bélgica, 43’, ‘Campinas’, ‘SP’,
65‘marctatho@hotmail.com’, ‘1987-11-09’);
66INSERT INTO Funcionario
67(idFuncionario, nome, endereco, cidade, estado, email, dataNascto)
68VALUES
69(9, ‘Jorge Luis Rodrigues’, ‘Av. da Saudade, 1989’, ‘São Paulo’,
70‘SP’, ‘jorgeluis@yahoo.com.br’, ‘1990-05-05’);
71INSERT INTO Funcionario
72(idFuncionario, nome, endereco, cidade, estado, email, dataNascto)
73VALUES
74(10, ‘Ana Paulo Camargo’, ‘Rua Costa e Silva’, ‘JundiaÃ’, ‘SP’,
75‘apcamargo@gmail.com’, ‘1991-06-30’);
76INSERT INTO Funcionario
77(idFuncionario, nome, endereco, cidade, estado, email, dataNascto)
78VALUES
79(11, ‘Ivo Cunha’, ‘Av. Raio de Luz, 100’, ‘Campinas’, ‘SP’, ‘ivo@
80bol.com.br’, ‘1987-04-11’);
81INSERT INTO Funcionario
82(idFuncionario, nome, endereco, cidade, estado, email, dataNascto)
83VALUES
84(12, ‘Carlos Luis de Souza’, ‘Rua Nicolau Coelho, 22’, ‘São Paulo’,
85‘SP’, ‘cls@bol.com.br’, ‘1988-04-30’);
864
87Linguagem SQL - Guia Prático de Aprendizagem
882.
89UPDATE Funcionario SET
90cidade = ‘Valinhos’
91WHERE cidade = ‘Itu’
923.
93UPDATE Funcionario SET
94cargo = ‘AI’, salario = 1100 -- auxiliar de informática
95WHERE cidade = ‘Valinhos’
96UPDATE Funcionario SET
97cargo = ‘PC’, salario = 1700 -- programador de computador
98WHERE cidade = ‘Campinas’
99UPDATE Funcionario SET
100cargo = ‘TI’, salario = 750 -- Técnico de informática
101WHERE cidade = ‘JundiaÃ’
1024.
103SELECT nome, cargo
104FROM Funcionario
1055.
106SELECT idFuncionario, email
107FROM Funcionario
108WHERE estado=’SP’
1096.
110DELETE FROM Funcionario
111WHERE idFuncionario = 5
1127.
113SELECT DISTINCT cidade, estado
114FROM Funcionario
115WHERE cargo=’PC’
116CapÃtulo 3
1171.
118SELECT nome, salario*1.30
119FROM Funcionario
1202.
121SELECT nome, salario, salario*0.80
122FROM Funcionario
123WHERE cidade = ‘Campinas’
1245
125Respostas dos ExercÃcios
1263.
127SELECT nome, salario
128FROM Funcionario
129WHERE salario > 1500
1304.
131SELECT nome, cidade
132FROM Funcionario
133WHERE NOT cidade=’Valinhos’
1345.
135SELECT idFuncionario, cidade
136FROM Funcionario
137WHERE cidade=’Valinhos’ OR cidade=’Campinas’
1386.
139SELECT idFuncionario, cargo
140FROM Funcionario
141WHERE NOT cidade=’São Paulo’ AND salario >= 1000
1427.
143SELECT nome
144FROM Funcionario
145WHERE cargo IS NOT NULL
1468.
147SELECT nome, salario
148FROM Funcionario
149WHERE salario BETWEEN 500 AND 1500
1509.
151SELECT nome, email
152FROM Funcionario
153WHERE email LIKE ‘%hotmail%’
15410.
155SELECT nome, email
156FROM Funcionario
157WHERE email LIKE ‘%.br’
158ORDER BY nome
15911.
160SELECT nome, email
161FROM Funcionario
162WHERE email NOT LIKE ‘%.com’
163ORDER BY nome
16412.
165SELECT nome, email
166FROM Funcionario
167WHERE email LIKE ‘__r%’
1686
169Linguagem SQL - Guia Prático de Aprendizagem
170CapÃtulo 4
1711.
172SELECT nome, DAY(dataNascto) dia, MONTH(dataNascto) mes,
173YEAR(dataNascto) ano
174FROM Funcionario
1752.
176SELECT DISTINCT DATENAME(MONTH,dataNascto) AS nome_mes
177FROM Funcionario
178ORDER BY nome_mes
1793.
180SELECT idFuncionario, nome
181FROM Funcionario
182WHERE YEAR(dataNascto)=1987
1834.
184SELECT nome, DAY(dataNascto)
185FROM Funcionario
186WHERE MONTH(dataNascto)=4 AND YEAR(dataNascto)=1988
1875.
188SELECT nome, DATEADD(MONTH, 2, dataNascto)
189FROM Funcionario
1906.
191SELECT nome, DATEDIFF(YEAR, dataNascto, GETDATE())
192FROM Funcionario
1937.
194SELECT idFuncionario, nome, YEAR(dataNascto)
195FROM Funcionario
196WHERE (MONTH(dataNascto) BETWEEN 3 AND 5) AND
197YEAR(dataNascto)=1990
1988.
199SELECT nome, YEAR(dataNascto)
200FROM Funcionario
201WHERE estado=’SP’
2029.
203SELECT nome
204FROM Funcionario
205WHERE YEAR(dataNascto) < 1990
20610.
207SELECT DISTINCT cidade, estado
208FROM Funcionario
209WHERE YEAR(dataNascto) > 1989
2107
211Respostas dos ExercÃcios
21211.
213SELECT *
214FROM Funcionario
215WHERE YEAR(dataNascto) IN (1988, 1990)
21612.
217SELECT nome
218FROM Funcionario
219WHERE DAY(dataNascto) = 30
220CapÃtulo 5
2211.
222SELECT nome, salario+PI()
223FROM Funcionario
2242.
225SELECT SQRT(DAY(dataNascto))
226FROM Funcionario
227WHERE cidade=’Valinhos’
2283.
229SELECT LOG(MONTH(dataNascto))
230FROM Funcionario
231WHERE YEAR(dataNascto)=1990
2324.
233SELECT nome, DAY(dataNascto)
234FROM Funcionario
235WHERE POWER(DAY(dataNascto),3) >= 1000
2365.
237SELECT ROUND((salario * 1.156),0)
238FROM Funcionario
239WHERE salario > 1000
2406.
241SELECT ABS(1500 - salario)
242FROM Funcionario
2437.
244SELECT idFuncionario, SQRT(idFuncionario)
245FROM Funcionario
246WHERE dataNascto < ‘1989-04-01’
2478.
248SELECT nome, ROUND((salario * 0.65),1)
249FROM Funcionario
2508
251Linguagem SQL - Guia Prático de Aprendizagem
2529.
253SELECT LOG(idFuncionario)
254FROM Funcionario
25510.
256SELECT SQRT(idFuncionario)
257FROM Funcionario
25811.
259SELECT POWER(idFuncionario,2)
260FROM Funcionario
26112.
262SELECT ABS(idFuncionario - 10) AS valor_abs
263FROM Funcionario
264ORDER BY valor_abs DESC
265CapÃtulo 6
2661.
267SELECT UPPER(nome)
268FROM Funcionario
2692.
270SELECT DISTINCT DATENAME(MONTH,dataNascto),
271LEN(DATENAME(MONTH,dataNascto))
272FROM Funcionario
2733.
274SELECT REPLACE(nome,’ ‘,’-’)
275FROM Funcionario
2764.
277SELECT LEFT(nome,3), RIGHT(nome,3)
278FROM Funcionario
2795.
280SELECT SQRT(LEN(nome))
281FROM Funcionario
2826.
283SELECT DISTINCT SUBSTRING(cidade,3,5)
284FROM Funcionario
2857.
286SELECT DISTINCT SUBSTRING(cidade,3,5)
287FROM Funcionario
2889
289Respostas dos ExercÃcios
2908.
291SELECT CHAR(idFuncionario)
292FROM Funcionario
293WHERE cidade=’Campinas’
2949.
295SELECT ASCII(nome)
296FROM Funcionario
297WHERE DAY(dataNascto) > 20
29810.
299SELECT RTRIM(LEFT(cidade,4))
300FROM Funcionario
30111.
302SELECT LTRIM(RIGHT(cidade,6))
303FROM Funcionario
30412.
305SELECT DISTINCT LOWER(cidade)
306FROM Funcionario
307CapÃtulo 7
3081.
309SELECT MAX(salario), MIN(salario)
310FROM Funcionario
311WHERE estado=’SP’
3122.
313SELECT SUM(salario)
314FROM Funcionario
315WHERE nome LIKE ‘%Cunha’
3163.
317SELECT AVG(salario)
318FROM Funcionario
319WHERE email LIKE ‘%yahoo%’
3204.
321SELECT COUNT(*)
322FROM Funcionario
323WHERE email LIKE ‘%br’
3245.
325SELECT MIN(dataNascto)
326FROM Funcionario
32710
328Linguagem SQL - Guia Prático de Aprendizagem
3296.
330SELECT MAX(dataNascto) AS Maior_Nascimento
331FROM Funcionario
3327.
333SELECT COUNT(*) AS Quantidade_de_Valinhos
334FROM Funcionario
335WHERE cidade=’Valinhos’
3368.
337SELECT SUM(salario)
338FROM Funcionario
339WHERE cidade=’Campinas’
3409.
341SELECT AVG(salario)
342FROM Funcionario
343WHERE cidade=’São Paulo’
34410.
345SELECT SUM(salario)
346FROM Funcionario
347WHERE nome LIKE ‘Ana%’
34811.
349SELECT COUNT(*)
350FROM Funcionario
351WHERE nome LIKE ‘%Luis%’
35212.
353SELECT MIN(salario), MAX(salario)
354FROM Funcionario
355WHERE endereco LIKE ‘Av. São Paulo%’
356CapÃtulo 8
3571.
358SELECT cargo, COUNT(*) AS quantidade
359FROM Funcionario
360GROUP BY cargo
361ORDER BY quantidade
3622.
363SELECT cargo, COUNT(*)
364FROM Funcionario
365WHERE NOT cargo IS NULL
366GROUP BY cargo
36711
368Respostas dos ExercÃcios
3693.
370SELECT cargo, AVG(salario) AS Media_Salarios_Cargo
371FROM Funcionario
372GROUP BY cargo
3734.
374SELECT cargo, SUM(salario)
375FROM Funcionario
376GROUP BY cargo
377HAVING SUM(salario) > 3000
3785.
379SELECT cargo, SUM(salario)
380FROM Funcionario
381WHERE estado=’SP’
382GROUP BY cargo
3836.
384UPDATE Funcionario SET
385ativo=1
386WHERE (cidade=’JundiaÃ’) OR (cidade=’São Paulo’)
3877.
388UPDATE Funcionario SET
389ativo=0
390WHERE NOT ((cidade=’JundiaÃ’) OR (cidade=’São Paulo’))
3918.
392SELECT ativo, COUNT(*)
393FROM Funcionario
394GROUP BY ativo
3959.
396SELECT cidade, SUM(salario)
397FROM Funcionario
398GROUP BY cidade
39910.
400SELECT cidade, AVG(salario)
401FROM Funcionario
402GROUP BY cidade
403HAVING NOT AVG(salario) IS NULL
40411.
405SELECT cargo, SUM(salario), AVG(salario)
406FROM Funcionario
407GROUP BY cargo
408HAVING SUM(salario) < 5000
40912.
410SELECT cidade, cargo, SUM(salario), AVG(salario)
411FROM Funcionario
412GROUP BY cidade, cargo
41312
414Linguagem SQL - Guia Prático de Aprendizagem
415CapÃtulo 9
4161.
417SELECT TOP 4 nome
418FROM Funcionario
4192.
420SELECT TOP 2 *
421FROM Funcionario
422WHERE cidade=’Valinhos’
4233.
424SELECT TOP 1 nome, dataNascto
425FROM Funcionario
426ORDER BY dataNascto ASC
4274.
428SELECT TOP 2 cidade, COUNT(*)
429FROM Funcionario
430GROUP BY cidade
4315.
432SELECT TOP 2 cargo, COUNT(*)
433FROM Funcionario
434GROUP BY cargo
4356.
436SELECT TOP 30 PERCENT *
437FROM Funcionario
4387.
439SELECT TOP 6 nome, email
440FROM Funcionario
4418.
442SELECT TOP 70 PERCENT idFuncionario, cargo, ativo
443FROM Funcionario
4449.
445SELECT TOP 1 idFuncionario, salario
446FROM Funcionario
447WHERE NOT salario IS NULL
448ORDER BY salario ASC
44910.
450SELECT TOP 1 nome, salario
451FROM Funcionario
452ORDER BY salario DESC
45313
454Respostas dos ExercÃcios
45511.
456SELECT TOP 1 nome, endereco
457FROM Funcionario
45812.
459SELECT TOP 90 PERCENT *
460FROM Funcionario
46113.
462SELECT TOP 1 *
463FROM Funcionario
464WHERE cidade=’São Paulo’
46514.
466SELECT TOP 20 PERCENT nome, endereco, cidade, estado
467FROM Funcionario
46815.
469SELECT TOP 2 *
470FROM Funcionario
471WHERE YEAR(dataNascto) = 1988
472CapÃtulo 10
4731.
474CREATE DATABASE Compras
4752.
476USE Compras
4773.
478CREATE TABLE Cliente(
479IdCliente int identity primary key,
480Nome varchar(50) NOT NULL,
481Endereco varchar(50) NOT NULL,
482Cidade varchar(50) NOT NULL,
483Estado char(2) NOT NULL
484)
485CREATE TABLE Produto(
486IdProduto INT IDENTITY PRIMARY KEY,
487Descricao VARCHAR(50) NOT NULL,
488Preco DECIMAL(5,2) NOT NULL,
489Qtde INT NOT NULL
490)
491CREATE TABLE Compram (
492IdCompra INT IDENTITY(1000,2),
493IdCliente INT,
494IdProduto INT,
49514
496Linguagem SQL - Guia Prático de Aprendizagem
497Data DATETIME NOT NULL,
498Qtde INT,
499Valor DECIMAL(5,2),
500PRIMARY KEY(IdCompra,IdCliente,IdProduto)
501)
5024.
503ALTER TABLE Cliente
504ADD sexo CHAR(1) NOT NULL
5055.
506INSERT INTO Cliente
507(Nome,Endereco,Cidade,Estado,Sexo)
508VALUES
509(‘José de Oliveira’,’Av. Jatobá,34’,’JundiaÃ’,’SP’,’F’)
510INSERT INTO Cliente
511(Nome,Endereco,Cidade,Estado,Sexo)
512VALUES
513(‘Maria da Silva’,’Av. Presidente,12’,’Itatiba’,’MG’,’F’)
514INSERT INTO Cliente
515(Nome,Endereco,Cidade,Estado,Sexo)
516VALUES
517(‘Antonio Carlos’,’R. Florença,5’,’JundiaÃ’,’SP’,’M’)
518INSERT INTO Cliente
519(Nome,Endereco,Cidade,Estado,Sexo)
520VALUES
521(‘Luisa de Souza’,’Av. Jatobá,45’,’JundiaÃ’,’MG’,’F’)
522INSERT INTO Cliente
523(Nome,Endereco,Cidade,Estado,Sexo)
524VALUES
525(‘Calos de Souza’,’Av. Jatobá,45’,’JundiaÃ’,’SP’,’M’)
5266.
527INSERT INTO Produto
528(Descricao,Preco,Qtde)
529VALUES
530(‘Lápis’,1.50,20)
531INSERT INTO Produto
532(Descricao,Preco,Qtde)
533VALUES
534(‘Borracha’,1.00,15)
535INSERT INTO Produto
536(Descricao,Preco,Qtde)
537VALUES
538(‘Caneta’,1.75,35)
53915
540Respostas dos ExercÃcios
541INSERT INTO Produto
542(Descricao,Preco,Qtde)
543VALUES
544(‘Compasso’,5.20,10)
545INSERT INTO Produto
546(Descricao,Preco,Qtde)
547VALUES
548(‘Régua’,0.75,16)
549INSERT INTO Produto
550(Descricao,Preco,Qtde)
551VALUES
552(‘Papel Sulfi te’,10.50,5)
5537.
554INSERT INTO Compram
555(IdCliente,IdProduto,Data,Qtde,Valor)
556VALUES
557(1,1,’2010-12-01’,2,1.50)
558INSERT INTO Compram
559(IdCliente,IdProduto,Data,Qtde,Valor)
560VALUES
561(2,1,’2010-12-03’,5,1.50)
562INSERT INTO Compram
563(IdCliente,IdProduto,Data,Qtde,Valor)
564VALUES
565(1,3,’2011-01-05’,13,1.75)
566INSERT INTO Compram
567(IdCliente,IdProduto,Data,Qtde,Valor)
568VALUES
569(1,4,’2011-01-11’,1,5.20)
570INSERT INTO Compram
571(IdCliente,IdProduto,Data,Qtde,Valor)
572VALUES
573(3,2,’2011-03-16’,7,1.00)
574INSERT INTO Compram
575(IdCliente,IdProduto,Data,Qtde,Valor)
576VALUES
577(4,5,’2011-05-21’,10,0.75)
578INSERT INTO Compram
579(IdCliente,IdProduto,Data,Qtde,Valor)
580VALUES
581(2,6,’2011-06-07’,2,10.50)
582INSERT INTO Compram
583(IdCliente,IdProduto,Data,Qtde,Valor)
584VALUES
585(5,3,’2011-06-07’,2,1.75)
58616
587Linguagem SQL - Guia Prático de Aprendizagem
5888.
589UPDATE Cliente
590SET Estado = ‘SP’
5919.
592SELECT Nome, Estado
593FROM Cliente
59410.
595UPDATE Cliente
596SET Sexo = ‘M’
597WHERE Nome = ‘José de Oliveira’
59811.
599SELECT Descricao, Preco
600FROM Produto
60112.
602DELETE FROM Produto
603WHERE Descricao = ‘Papel Sulfi te’
60413.
605UPDATE Produto
606SET Qtde = 15
607WHERE Descricao = ‘Lápis’
60814.
609SELECT TOP 2 LOWER(Descricao)
610FROM Produto
61115.
612SELECT SUM(Valor)
613FROM Compram
614WHERE IdProduto = 1
61516.
616SELECT AVG(valor)
617FROM Compram
618WHERE IdCliente = 1
61917.
620SELECT Nome
621FROM Cliente
622WHERE Cidade = ‘JundiaÃ’
62318.
624SELECT IdCliente, UPPER(Nome)
625FROM Cliente
626WHERE Nome LIKE ‘%Carlos%’
62717
628Respostas dos ExercÃcios
62919.
630SELECT Descricao, Preco, Qtde
631FROM Produto
632WHERE Preco > 1 AND Qtde >= 10
63320.
634SELECT *
635FROM Cliente
636ORDER BY Nome
63721.
638SELECT DISTINCT cidade, COUNT(*)
639FROM Cliente
640GROUP BY Cidade
641ORDER BY COUNT(*)
64222.
643SELECT SUM(Preco) AS SomaPreco, AVG(Preco) AS MediaPreco
644FROM Produto
64523.
646SELECT MAX(Preco) AS PrecoMaisCaro, MIN(Preco) AS PrecoMaisBarato
647FROM Produto
64824.
649SELECT SUM(Valor)
650FROM Compram
651WHERE YEAR(Data) = ‘2010’
65225.
653SELECT TOP 1 Valor
654FROM Compram
655WHERE YEAR(Data) = ‘2011’
656ORDER BY Data
65726.
658SELECT Nome
659FROM Cliente
660WHERE Sexo = ‘F’
66127.
662SELECT *
663FROM Compram
664WHERE DAY(Data) IN (‘1’,’11’)
66528.
666SELECT Descricao, Preco, (Preco + (Preco*0.1)) AS
667PrecoAcrescido10porCento
668FROM Produto
66918
670Linguagem SQL - Guia Prático de Aprendizagem
67129.
672SELECT IdCliente, COUNT(*) AS QuantidadeCompra
673FROM Compram
674GROUP BY IdCliente
67530.
676UPDATE Produto
677SET Preco = (Preco - (Preco*0.1))
678WHERE Qtde < 15
67931.
680SELECT IdProduto, DAY(Data)
681FROM Compram
68232.
683SELECT DISTINCT Sexo, COUNT(*)
684FROM Cliente
685GROUP BY Sexo
68633.
687DELETE FROM Compram
688WHERE IdCompra = 1000
68934.
690SELECT Descricao, POWER(Qtde,2) AS QtdeAoQuadrado
691FROM Produto
692WHERE Qtde > 15 AND Qtde < 25
69335.
694SELECT SQRT(Qtde) AS RaizDaQuantidade
695FROM Produto
696WHERE Descricao LIKE ‘C%’
69736.
698SELECT Nome
699FROM Cliente
700WHERE Endereco LIKE ‘Av. Jatobá%’
70137.
702SELECT Nome, LEN(Nome) AS QuantidadeDeCaractere
703FROM Cliente
70438.
705SELECT IdCompra, Valor, (Valor-(Valor*0.2)) AS
706Valor20PorCentoDesconto
707FROM Compram
708WHERE IdCliente = 2
70939.
710SELECT YEAR(Data), COUNT(*)
711FROM Compram
712GROUP BY YEAR(Data)
71319
714Respostas dos ExercÃcios
71540.
716SELECT IdCompra, DAY(Data) AS DiaDaCompra, DATENAME(MONTH,Data)
717AS MesDaCompra, YEAR(Data) AS AnoDasCompras
718FROM Compram
71941.
720SELECT IdProduto, SUM(Valor*Qtde)
721FROM Compram
722GROUP BY IdProduto
723HAVING SUM(Valor*Qtde) > 7
72442.
725DELETE FROM Compram
726WHERE IdCliente BETWEEN 3 AND 5
72743.
728DROP TABLE Produto
72944.
730USE MASTER
731DROP DATABASE Compras
732CapÃtulo 11
733O leitor poderá executar o arquivo empresa.sql disponÃvel no site da Editora
734Érica, que criará as tabelas e colocará dados nelas.
7351.
736CREATE DATABASE Empresa
7372.
738USE Empresa
7393.
740CREATE TABLE Fornecedores (
741CodFor INT IDENTITY NOT NULL,
742Empresa VARCHAR(40),
743Contato VARCHAR(30),
744Cargo VARCHAR(30),
745Endereco VARCHAR(60),
746Cidade VARCHAR(15),
747CEP VARCHAR(10),
748Pais VARCHAR(15),
749PRIMARY KEY (CodFor)
750)
751CREATE TABLE Categorias (
752CodCategoria INT IDENTITY NOT NULL,
753Descr VARCHAR(15),
754PRIMARY KEY (CodCategoria)
755)
75620
757Linguagem SQL - Guia Prático de Aprendizagem
758CREATE TABLE Clientes (
759CodCli CHAR(5) NOT NULL,
760Nome VARCHAR(40) NOT NULL,
761Contato VARCHAR(30) NOT NULL,
762Cargo VARCHAR(30) NOT NULL,
763Endereco VARCHAR(60) NOT NULL,
764Cidade VARCHAR(15) NOT NULL,
765Regiao VARCHAR(15) NOT NULL,
766CEP VARCHAR(10) NOT NULL,
767Pais VARCHAR(15) NOT NULL,
768Telefone VARCHAR(24) NOT NULL,
769Fax VARCHAR(24) NOT NULL,
770PRIMARY KEY(CodCli)
771)
772CREATE TABLE Funcionarios(
773CodFun INT IDENTITY NOT NULL,
774Sobrenome VARCHAR(20),
775Nome VARCHAR(10),
776Cargo VARCHAR(30),
777DataNasc DATE,
778Endereco VARCHAR(60),
779Cidade VARCHAR(15),
780CEP VARCHAR(10),
781Pais VARCHAR(15),
782Fone VARCHAR(24),
783Salario MONEY DEFAULT 0.0,
784PRIMARY KEY (CodFun)
785)
786CREATE TABLE Produtos(
787CodProd INT IDENTITY NOT NULL,
788Descr VARCHAR(40),
789CodFor INT,
790CodCategoria INT ,
791Preco MONEY DEFAULT 0.0,
792Unidades SMALLINT DEFAULT 0,
793Descontinuado BIT,
794PRIMARY KEY (CodProd),
795FOREIGN KEY (CodCategoria) REFERENCES Categorias(CodCategoria)
796ON DELETE CASCADE,
797FOREIGN KEY (CodFor) REFERENCES Fornecedores(CodFor) ON DELETE
798CASCADE
799)
800CREATE TABLE Pedidos(
801NumPed INT NOT NULL,
802CodCli CHAR(5),
803CodFun INT DEFAULT 0 ,
804DataPed DATE,
805DataEntrega DATE,
806Frete MONEY DEFAULT 0.0,
807PRIMARY KEY (NumPed),
808FOREIGN KEY (CodCli) REFERENCES Clientes(CodCli) ON DELETE
809CASCADE,
81021
811Respostas dos ExercÃcios
812FOREIGN KEY (CodFun) REFERENCES Funcionarios(CodFun) ON DELETE
813CASCADE
814)
815CREATE TABLE DetalhesPed(
816NumPed INT ,
817CodProd INT ,
818Preco MONEY,
819Qtde SMALLINT ,
820Desconto FLOAT,
821PRIMARY KEY (NumPed, CodProd),
822FOREIGN KEY (NumPed) REFERENCES Pedidos(NumPed) ON DELETE
823CASCADE,
824FOREIGN KEY (CodProd) REFERENCES Produtos(CodProd) ON DELETE
825CASCADE
826)
827CapÃtulo 12
8281.
829SELECT TOP 1 Descr, Preco
830FROM Produtos
831ORDER BY Preco DESC
8322.
833SELECT TOP 5 NumPed, DataPed
834FROM Pedidos
835ORDER BY Frete
8363.
837SELECT Nome, Cargo
838FROM Clientes
839UNION
840SELECT Nome, Cargo
841FROM Funcionarios
842WHERE Pais = ‘Reino Unido’
8434.
844SELECT TOP 3 Nome, Sobrenome, Cargo, Salario
845FROM Funcionarios
846ORDER BY Salario DESC
8475.
848SELECT TOP 1 Nome, Sobrenome
849FROM Funcionarios
850ORDER BY DataNasc DESC
8516.
852SELECT TOP 5 *
853FROM Pedidos
854ORDER BY DataPed
85522
856Linguagem SQL - Guia Prático de Aprendizagem
8577.
858SELECT TOP 6 *
859FROM Pedidos
860WHERE YEAR(DataPed) = 1996
8618.
862SELECT Nome, Cargo
863FROM Funcionarios
864WHERE Pais = ‘EUA’
865UNION
866SELECT Contato, Cargo
867FROM Fornecedores
868WHERE Pais = ‘EUA’
8699.
870SELECT Nome, Contato, Pais
871FROM Clientes
872WHERE Pais = ‘Brasil’
873UNION
874SELECT Nome, Contato, Pais
875FROM Clientes
876WHERE Pais = ‘Alemanha’
87710.
878SELECT Nome, Contato, Cidade
879FROM Clientes
880WHERE Cidade = ‘Madrid’
881UNION
882SELECT Nome, Contato, Cidade
883FROM Clientes
884WHERE Cidade = ‘Paris’
88511.
886SELECT Descr, Preco
887FROM Produtos
888WHERE CodCategoria = 2
889UNION
890SELECT Descr, Preco
891FROM Produtos
892WHERE CodCategoria = 4
89312.
894SELECT Nome, Cargo, Pais
895FROM Funcionarios
896WHERE Pais = ‘Reino Unido’
897UNION
898SELECT Contato, Cargo, Pais
899FROM Fornecedores
900WHERE Pais = ‘França’
90123
902Respostas dos ExercÃcios
903CapÃtulo 13
9041.
905SELECT Pais, COUNT(*) AS QtdeClientes
906FROM Clientes
907GROUP BY Pais
9082.
909SELECT SUM(Preco) AS Soma, AVG(Preco) AS Media, MAX(Preco) AS
910MaiorPreco, MIN(Preco) AS MenorPreco
911FROM Produtos
9123.
913SELECT C.Pais, COUNT(P.NumPed) AS QtdePedidos
914FROM Clientes C, Pedidos P
915WHERE C.CodCli = P.CodCli
916GROUP BY C.Pais
917ORDER BY COUNT(P.NumPed) DESC
9184.
919SELECT Nome, Sobrenome, Cargo, Salario, (Salario*1.10) AS
920Salario_Novo
921FROM Funcionarios
9225.
923SELECT SUM(DP.Preco) AS SomadosPrecos
924FROM DetalhesPed DP, Pedidos P
925WHERE (DP.NumPed = P.NumPed) AND (YEAR(P.DataEntrega) = 1997) AND
926(MONTH(P.DataEntrega) = 5)
9276.
928SELECT C.CodCli, C.Nome, C.Pais
929FROM Clientes C, Pedidos P
930WHERE (C.CodCli = P.CodCli) AND (YEAR(P.DataPed) = 1997) AND
931(MONTH(P.DataPed) = 09)
932ORDER BY (C.Pais)
9337.
934SELECT F.Nome, P.*
935FROM Funcionarios F, Pedidos P
936WHERE (F.CodFun = P.CodFun) AND (F.Nome LIKE ‘A%’)
9378.
938SELECT P.Descr, P.Unidades
939FROM Produtos P, Fornecedores F
940WHERE (P.CodFor = F.CodFor) AND (F.Empresa = ‘Exotic Liquids’)
9419.
942SELECT DISTINCT P.Descr
943FROM Produtos P, DetalhesPed DP, Pedidos Pd
944WHERE (P.CodProd = DP.CodProd) AND (DP.NumPed = Pd.NumPed)
945AND (DP.Qtde >= 50) AND (YEAR(Pd.DataPed) = 1997)
946ORDER BY P.Descr
94724
948Linguagem SQL - Guia Prático de Aprendizagem
94910.
950SELECT DISTINCT C.Descr, P.Descr
951FROM Categorias C, Produtos P, DetalhesPed DP, Pedidos Pd
952WHERE (C.CodCategoria = P.CodCategoria) AND (P.CodProd =
953DP.CodProd) AND (DP.NumPed = Pd.NumPed) AND (DP.Qtde >= 50) AND
954(YEAR(Pd.DataEntrega) = 1997)
955ORDER BY C.Descr DESC
956CapÃtulo 14
9571.
958SELECT C.*
959FROM Clientes C INNER JOIN Pedidos P
960ON C.Codcli = P.Codcli
961AND YEAR(P.DataPed) = 1996
9622.
963SELECT F.Nome
964from Funcionarios F INNER JOIN Pedidos P ON F.Codfun = P.Codfun
965INNER JOIN Clientes C ON P.Codcli = C.Codcli
966AND C.Nome = ‘Around the horn’
9673.
968SELECT P.*
969FROM Pedidos P INNER JOIN Clientes C ON P.Codcli = C.Codcli
970AND C.Nome = ‘Comércio Mineiro’
9714.
972SELECT F.*
973FROM Funcionarios F INNER JOIN Pedidos P ON F.CodFun = P.CodFun
974AND YEAR(P.DataPed) = 1996 AND MONTH(P.DataPed)= 9
9755.
976SELECT P.*, C.Descr
977FROM Produtos P INNER JOIN categoria C ON P.CodCategoria =
978C.CodCategoria
979AND C.Descr = ‘laticÃnios’
9806.
981SELECT P.*, Pd.NumPed AS NumeroPedido
982FROM Produtos P INNER JOIN DetalhesPed DP ON P.CodProd =
983DP.CodProd
984INNER JOIN Pedidos Pd ON DP.NumPed = Pd.NumPed
985AND Pd.DataPed = ‘1996-07-08’
9867.
987SELECT F.Nome, P.NumPed AS NumeroPedido
988FROM Funcionarios F INNER JOIN Pedidos P ON F.CodFun = P.CodFun
989AND P.DataPed = ‘1997-05-01’
99025
991Respostas dos ExercÃcios
9928.
993SELECT F.Nome, P.*
994FROM Funcionarios F INNER JOIN Pedidos P ON F.CodFun = P.CodFun
995AND F.Salario > 10000
9969.
997SELECT P.NumPed, C.Nome
998FROM Pedidos P INNER JOIN Clientes C ON P.CodCli = C.CodCli
999AND MONTH(P.DataPed) = 5 and YEAR(P.DataPed) = 1997
100010.
1001SELECT DISTINCT C.Descr, P.Descr
1002FROM Categorias C INNER JOIN Produtos P ON C.CodCategoria =
1003P.CodCategoria
1004INNER JOIN DetalhesPed DP ON P.CodProd = DP.CodProd
1005AND DP.Qtde <= 10
1006INNER JOIN Pedidos Pd ON DP.NumPed = Pd.NumPed
1007AND YEAR(Pd.DataPed) = 1998
1008ORDER BY C.Descr DESC
100911.
1010SELECT DP.*
1011FROM DetalhesPed DP INNER JOIN Pedidos P ON DP.NumPed = P.NumPed
1012AND YEAR(P.DataEntrega) = 1997
101312.
1014SELECT DISTINCT C.Descr, P.Descr
1015FROM Categorias C CROSS JOIN Produtos P
1016CapÃtulo 15
10171.
1018SELECT *
1019FROM Pedidos
1020WHERE CodCli IN (SELECT CodCli
1021FROM Clientes
1022WHERE Pais = ‘Alemanha’)
10232.
1024SELECT *
1025FROM Produtos
1026WHERE CodCategoria IN (SELECT CodCategoria
1027FROM Categorias
1028WHERE Descr = ‘Condimentos’)
10293.
1030SELECT Descr
1031FROM Produtos
1032WHERE CodFor NOT IN (SELECT CodFor
1033FROM Fornecedores
1034WHERE Pais = ‘EUA’)
103526
1036Linguagem SQL - Guia Prático de Aprendizagem
10374.
1038SELECT Descr
1039FROM Produtos
1040WHERE CodProd IN (SELECT CodProd
1041FROM DetalhesPed
1042WHERE NumPed IN (SELECT NumPed
1043FROM Pedidos
1044WHERE YEAR(Dataped) <> 1997
1045AND MONTH(Dataped) <> 3 ))
10465.
1047SELECT CodProd, Descr, Preco
1048FROM Produtos
1049WHERE Preco = (SELECT MIN(Preco)
1050FROM Produtos)
10516.
1052SELECT Nome, Salario
1053FROM Funcionarios
1054WHERE Salario = (SELECT MAX(Salario)
1055FROM Funcionarios)
10567.
1057SELECT Nome, Salario
1058FROM Funcionarios
1059WHERE Salario = (SELECT MAX(Salario)
1060FROM Funcionarios)
1061OR Salario = (SELECT MIN(Salario)
1062FROM Funcionarios)
1063ORDER BY Salario
10648.
1065SELECT CodProd, Descr, Preco
1066FROM Produtos
1067WHERE Preco > (SELECT AVG(Preco)
1068FROM Produtos)
10699.
1070SELECT Nome, Sobrenome, Cargo, Salario
1071FROM Funcionarios
1072WHERE Cargo = ‘Representante de Vendas’
1073AND Salario < ALL (SELECT Salario
1074FROM Funcionarios
1075WHERE Cargo LIKE ‘gerente%’
1076OR Cargo LIKE ‘coordenador%’)
107710.
1078SELECT Nome, Sobrenome, Cargo, Salario
1079FROM Funcionarios
1080WHERE Cargo LIKE ‘Coordenador%’
1081AND Salario > ANY (SELECT Salario
1082FROM Funcionarios
1083WHERE Cargo = ‘Representante de Vendas’)
108427
1085Respostas dos ExercÃcios
108611.
1087SELECT F.Nome, P.*
1088FROM Funcionarios F, Pedidos P
1089WHERE F.CodFun = P.CodFun AND P.Frete > (SELECT AVG(Frete)
1090FROM Pedidos)
109112.
1092SELECT *
1093FROM Produtos
1094WHERE Preco < ALL (SELECT Preco
1095FROM Produtos
1096WHERE CodCategoria IN (SELECT CodCategoria
1097FROM Categorias
1098WHERE Descr = ‘Confeitos’))
1099CapÃtulo 16
11001.
1101CREATE VIEW Preco_Baixo AS
1102SELECT CodProd, Descr, Preco
1103FROM Produtos
1104WHERE Preco < (SELECT AVG(Preco)
1105FROM Produtos)
11062.
1107SELECT *
1108FROM Preco_Baixo
1109WHERE Descr LIKE ‘C%’
11103.
1111CREATE VIEW Funcionarios_Cargo AS
1112SELECT Cargo, COUNT(*) AS FuncionariosPorCargo
1113FROM Funcionarios
1114GROUP BY Cargo
11154.
1116SELECT Cargo
1117FROM Funcionarios_Cargo
1118WHERE FuncionariosPorCargo = (SELECT MAX(FuncionariosPorCargo)
1119FROM Funcionarios_Cargo)
11205.
1121CREATE VIEW Produtos_Categoria AS
1122SELECT P.Descr AS DescrProduto, C.Descr AS DescrCategoria
1123FROM Produtos P, Categorias C
1124WHERE P.CodCategoria = C.CodCategoria
11256.
1126SELECT DescrCategoria, COUNT(*)AS
1127QuantidadeDeProdutosPorCategoria
1128FROM Produtos_Categoria
1129GROUP BY DescrCategoria
113028
1131Linguagem SQL - Guia Prático de Aprendizagem
11327.
1133CREATE VIEW Clientes_Resumo AS
1134SELECT CodCli, Nome, Contato, Cargo, Pais
1135FROM Clientes
11368.
1137CREATE VIEW Pedidos_Resumo_abr97 AS
1138SELECT NumPed,CodCli,DataEntrega
1139FROM Pedidos
1140WHERE YEAR(DataEntrega) = 1997 AND MONTH(DataEntrega) = 4
11419.
1142SELECT C.*
1143FROM Clientes_Resumo C INNER JOIN Pedidos_Resumo_abr97 P
1144ON C.CodCli = P.CodCli
114510.
1146CREATE VIEW Clientes_Resumo_W AS
1147SELECT *
1148FROM Clientes_Resumo
1149WHERE Nome LIKE ‘W%’
115011.
1151DROP VIEW Preco_Baixo
1152DROP VIEW Funcionarios_Cargo
1153DROP VIEW Produtos_Categoria
1154DROP VIEW Clientes_Resumo
1155DROP VIEW Pedidos_Resumo_abr97
1156DROP VIEW Clientes_Resumo_W
1157CapÃtulo 17
11581.
1159DECLARE @i INT;
1160SET @i = 100
1161WHILE @i >= 0
1162BEGIN
1163PRINT @i;
1164SET @i = @i - 2;
1165END;
11662.
1167DECLARE @a INT, @b INT, @c INT;
1168SET @a = 1;
1169SET @b = 2;
1170SET @c = 3;
1171IF(@a > @b) AND (@b>@c)
1172BEGIN
1173PRINT @a;
1174PRINT @b;
1175PRINT @c;
1176END
117729
1178Respostas dos ExercÃcios
1179ELSE IF(@a>@c) AND (@c>@b)
1180BEGIN
1181PRINT @a;
1182PRINT @c;
1183PRINT @b;
1184END
1185ELSE IF(@b>@a) AND (@a>@c)
1186BEGIN
1187PRINT @b;
1188PRINT @a;
1189PRINT @c;
1190END
1191ELSE IF(@b>@c) AND (@c>@a)
1192BEGIN
1193PRINT @b;
1194PRINT @c;
1195PRINT @a;
1196END
1197ELSE IF(@c>@a) AND (@a>@b)
1198BEGIN
1199PRINT @c;
1200PRINT @a;
1201PRINT @b;
1202END
1203ELSE IF (@c>@b) AND (@b>@a)
1204BEGIN
1205PRINT @c;
1206PRINT @b;
1207PRINT @a;
1208END
12093.
1210DECLARE @num1 INT;
1211DECLARE @num2 INT;
1212SET @num1 = ROUND(10 * RAND(),0,1)
1213SET @num2 = ROUND(10 * RAND(),0,1)
1214IF ((@num1%2) = 0)
1215PRINT CONVERT(CHAR(2),@num1) + ‘O Numero 1 é Par!’;
1216ELSE
1217PRINT CONVERT(CHAR(2),@num1) + ‘O Numero 1 é Impar!’
1218IF ((@num2%2) = 0)
1219PRINT CONVERT(CHAR(2),@num2) + ‘O Numero 2 é Par!’;
1220ELSE
1221PRINT CONVERT(CHAR(2),@num2) + ‘O Numero 2 é Impar!’
12224.
1223SELECT QTDE,
1224CASE
1225WHEN QTDE < 10 THEN ‘DESCONTO = 0’
1226WHEN QTDE >= 10 AND QTDE < 30 THEN ‘DESCONTO = 3’
1227WHEN QTDE >= 30 AND QTDE < 50 THEN ‘DESCONTO = 5’
1228WHEN QTDE >=50 AND QTDE < 70 THEN ‘DESCONTO = 7’
1229ELSE ‘DESCONTO = 9’
1230END AS DescontoPedido
1231FROM DetalhesPed
123230
1233Linguagem SQL - Guia Prático de Aprendizagem
1234OU
1235SELECT QTDE,
1236CASE
1237WHEN QTDE < 10 THEN 0
1238WHEN QTDE < 30 THEN 3
1239WHEN QTDE < 50 THEN 5
1240WHEN QTDE < 70 THEN 7
1241ELSE 9
1242END AS DescontoPedido
1243FROM DetalhesPed
12445.
1245DECLARE @i INT;
1246DECLARE @lidos INT;
1247DECLARE @num INT;
1248SET @i = 0;
1249SET @lidos = ROUND(10 * RAND(),0,1);
1250PRINT ‘Quantidade de números lidos: ‘ + CONVERT(CHAR(2),@lidos)
1251WHILE(@i < @lidos)
1252BEGIN
1253SET @num = ROUND(100 * RAND(),0,1);
1254IF (@num%2)=0
1255PRINT(@num);
1256SET @i += 1;
1257END;
12586.
1259DECLARE @i INT;
1260SET @i = 0;
1261WHILE(@i <= 1000)
1262BEGIN
1263IF ((@i%10)=0)
1264PRINT(@i);
1265SET @i = @i + 1;
1266END;
12677.
1268SELECT NOME,PAIS,
1269CASE(PAIS)
1270WHEN ‘Brasil’ THEN ‘Exportação’
1271ELSE ‘Importação’
1272END AS Situacao
1273FROM CLIENTES
12748.
1275DECLARE @NOME VARCHAR(50)
1276SET @NOME = ‘NATHAN CIRILLO E SILVA’
1277PRINT(@NOME)
1278PRINT(LEN(@NOME))
127931
1280Respostas dos ExercÃcios
1281CapÃtulo 18
12821.
1283CREATE PROCEDURE Busca_Func @CodFun INT
1284AS
1285SELECT Nome, Sobrenome, Cargo
1286FROM Funcionarios
1287WHERE CodFun = @CodFun
1288EXEC Busca_Func 2
12892.
1290CREATE PROCEDURE Insere_Fornec
1291@Empresa VARCHAR(40),
1292@Contato VARCHAR(30),
1293@Cargo VARCHAR(30),
1294@Endereco VARCHAR(60),
1295@Cidade VARCHAR(15),
1296@Cep VARCHAR(10),
1297@Pais VARCHAR(15)
1298AS
1299INSERT INTO Fornecedores
1300VALUES
1301(@Empresa, @Contato, @Cargo, @Endereco, @Cidade, @Cep, @Pais)
1302EXEC Insere_Fornec ‘CPS’, ‘José Silva’, ‘Operário’, ‘Rua 25 de
1303Março’, ‘São Paulo’, ‘12345’, ‘Brasil’
13043.
1305CREATE PROCEDURE Insere_Detalhes
1306@NumPed INT,
1307@CodProd INT,
1308@Preco MONEY,
1309@Qtde SMALLINT,
1310@Desconto FLOAT
1311AS
1312DECLARE @ContagemNumPed INT
1313DECLARE @ContagemCodProd INT
1314DECLARE @Verifi caNumPedECodProd INT
1315SELECT @ContagemNumPed = COUNT(*)
1316FROM Pedidos
1317WHERE NumPed = @NumPed
1318SELECT @ContagemCodProd = COUNT(*)
1319FROM Produtos
1320WHERE CodProd = @CodProd
1321SELECT @Verifi caNumPedECodProd = COUNT(*)
1322FROM DetalhesPed
1323WHERE NumPed = @NumPed AND CodProd = @CodProd
132432
1325Linguagem SQL - Guia Prático de Aprendizagem
1326IF (@ContagemCodProd = 0)
1327PRINT ‘Codigo do Produto não Cadastrado!’;
1328ELSE IF (@ContagemNumPed = 0)
1329PRINT ‘Numero do Pedido não Cadastrado!’;
1330ELSE
1331BEGIN
1332IF (@Verifi caNumPedECodProd <> 0)
1333PRINT ‘Produto já cadastrado para esse pedido’
1334ELSE
1335BEGIN
1336INSERT INTO DetalhesPed
1337VALUES
1338(@NumPed, @CodProd, @Preco, @Qtde, @Desconto)
1339PRINT ‘Dados Cadastrados com Sucesso!’
1340END;
1341END;
1342EXEC Insere_Detalhes 10868, 199, 1, 1, 1 --Codigo do Produto não
1343Cadastrado!
1344EXEC Insere_Detalhes 100, 11, 1, 1, 1 --Numero do Pedido não
1345Cadastrado!
1346EXEC Insere_Detalhes 10867, 53, 1, 1, 1 --Produto já cadastrado
1347para esse pedido
1348EXEC Insere_Detalhes 10867, 55, 1, 1, 1 --Dados Cadastrados com
1349Sucesso!
13504.
1351CREATE PROCEDURE Aumenta_Preco
1352@CodProd INT,
1353@PercAum DECIMAL(5,2)
1354AS
1355IF (@CodProd = 0)
1356UPDATE Produtos
1357SET Preco = Preco + (Preco * @PercAum);
1358ELSE
1359UPDATE Produtos
1360SET Preco = Preco + (Preco * @PercAum)
1361WHERE CodProd = @CodProd
1362EXEC Aumenta_Preco 43, 0.0 --um produtos
1363EXEC Aumenta_Preco 0, 0.0 --todos
13645.
1365CREATE PROCEDURE Exclui_Produto
1366@CodProd INT
1367AS
1368IF (NOT EXISTS (SELECT CodProd
1369FROM Produtos
1370WHERE CodProd = @CodProd))
137133
1372Respostas dos ExercÃcios
1373PRINT ‘Não há produto cadastrado com esse código para ser
1374excluÃdo!’;
1375ELSE
1376BEGIN
1377DELETE FROM Produtos
1378WHERE CodProd = @CodProd
1379PRINT ‘Dados ExcluÃdos com Sucesso!’
1380END;
1381EXEC Exclui_Produto 444 --Não há produto cadastrado com esse
1382código para ser excluÃdo!
1383EXEC Exclui_Produto 77 --Dados ExcluÃdos com Sucesso!
13846.
1385CREATE PROCEDURE Altera_Produto
1386@CodProd INT,
1387@DescrProd VARCHAR(40)
1388AS
1389IF (NOT EXISTS (SELECT CodProd
1390FROM Produtos
1391WHERE CodProd = @CodProd))
1392PRINT ‘Não há produto cadastrado com esse código para ser
1393alterado!’;
1394ELSE
1395BEGIN
1396UPDATE Produtos
1397SET Descr=@DescrProd
1398WHERE CodProd = @CodProd
1399PRINT ‘Produto alterado com Sucesso!’
1400END;
1401EXEC Altera_Produto 444, ‘chocolate’ --Não há produto cadastrado
1402com esse código para ser alterado!
1403EXEC Altera_Produto 76, ‘chocolate’
14047.
1405CREATE PROC Exclui_Pedido
1406@numPed INT
1407AS
1408IF (NOT EXISTS (SELECT NumPed
1409FROM Pedidos
1410WHERE NumPed = @numPed))
1411PRINT ‘Não há pedido cadastrado com esse número!’;
1412ELSE
1413BEGIN
1414DELETE FROM DetalhesPed
1415WHERE NumPed = @numPed
1416DELETE FROM Pedidos
1417WHERE NumPed = @numPed
141834
1419Linguagem SQL - Guia Prático de Aprendizagem
1420PRINT ‘Pedido ExcluÃdo com Sucesso!’
1421END;
1422EXEC Exclui_Pedido 22 --Não há pedido cadastrado com esse número!
1423EXEC Exclui_Pedido 10867 --Pedido ExcluÃdo com Sucesso!
14248.
1425CREATE PROC Funcionarios_Cargo
1426@cargo VARCHAR(30)
1427AS
1428IF (NOT EXISTS (SELECT Cargo
1429FROM Funcionarios
1430WHERE Cargo = @cargo))
1431PRINT ‘Não há funcionários cadastrado com esse cargo!’;
1432ELSE
1433BEGIN
1434SELECT *
1435FROM Funcionarios
1436WHERE Cargo=@cargo
1437END;
1438EXEC Funcionarios_Cargo ‘secretária’ --Não há funcionários
1439cadastrado com esse cargo
1440EXEC Funcionarios_Cargo ‘representante de vendas’
14419.
1442CREATE PROCEDURE Aumenta_Salario
1443@CodFun INT,
1444@PercAum DECIMAL(5,2)
1445AS
1446IF (@CodFun = 0)
1447UPDATE Funcionarios
1448SET Salario = Salario + (Salario * @PercAum);
1449ELSE
1450UPDATE Funcionarios
1451SET Salario = Salario + (Salario * @PercAum)
1452WHERE CodFun = @CodFun
1453EXEC Aumenta_Salario 5, 0.0 --um funcionário
1454EXEC Aumenta_Salario 0, 0.0 --todos
145510.
1456CREATE PROC Clientes_Cidade
1457@cidade VARCHAR(15)
1458AS
1459IF (NOT EXISTS (SELECT Cidade
1460FROM Clientes
1461WHERE Cidade = @cidade))
1462PRINT ‘Não há clientes cadastrado para essa cidade!’;
146335
1464Respostas dos ExercÃcios
1465ELSE
1466BEGIN
1467SELECT *
1468FROM Clientes
1469WHERE Cidade=@cidade
1470END;
1471EXEC Clientes_Cidade ‘JundiaÃ’ --Não há clientes cadastrado para
1472essa cidade
1473EXEC Clientes_Cidade ‘Madrid’
1474CapÃtulo 19
14751.
1476CREATE FUNCTION RetornaDiaDaSemana(@data DATE)
1477RETURNS VARCHAR(10)
1478AS
1479BEGIN
1480RETURN(DATENAME(WEEKDAY,@data));
1481END;
1482SELECT dbo.RetornaDiaDaSemana (‘2009-05-01’)
14832.
1484ALTER FUNCTION RetornaDiaDaSemanaPortugues(@data DATE)
1485RETURNS VARCHAR(15)
1486AS
1487BEGIN
1488DECLARE @diaSemana INT = DATEPART(WEEKDAY,@data);
1489DECLARE @diaSemanaPortugues VARCHAR(15);
1490SET @diaSemanaPortugues = CASE @diaSemana
1491WHEN 1 THEN ‘Domingo’
1492WHEN 2 THEN ‘Segunda-feira’
1493WHEN 3 THEN ‘Terça-feira’
1494WHEN 4 THEN ‘Quarta-feira’
1495WHEN 5 THEN ‘Quinta-feira’
1496WHEN 6 THEN ‘Sexta-feira’
1497WHEN 7 THEN ‘Sábado’
1498END;
1499RETURN(@diaSemanaPortugues);
1500END;
1501SELECT dbo.RetornaDiaDaSemanaPortugues(‘2009-05-01’)
15023.
1503CREATE FUNCTION SomaIntervalo(@valorMinimo INT, @valorMaximo INT)
1504RETURNS INT
1505AS
1506BEGIN
1507DECLARE @soma INT = 0;
1508WHILE @valorMinimo <= @valorMaximo
150936
1510Linguagem SQL - Guia Prático de Aprendizagem
1511BEGIN
1512SET @soma = @soma + @valorMinimo;
1513SET @valorMinimo = @valorMinimo + 1;
1514END;
1515RETURN @soma;
1516END
1517SELECT dbo.SomaIntervalo (1, 3)
1518SELECT dbo.SomaIntervalo (3, 1)
15194.
1520CREATE FUNCTION DataFormatada(@Dia INT, @Mes INT, @Ano INT)
1521RETURNS CHAR(10)
1522AS
1523BEGIN
1524DECLARE @data CHAR(10);
1525SET @data = CONVERT(CHAR(2),@Dia) + ‘/’ + CONVERT(CHAR(2),@Mes)
1526+ ‘/’ + CONVERT(CHAR(4),@Ano);
1527RETURN @data;
1528END
1529SELECT dbo.DataFormatada (01, 03, 2011)
15305.
1531CREATE FUNCTION PrimeiroDiaData(@Data DATE)
1532RETURNS CHAR(10)
1533AS
1534BEGIN
1535DECLARE @mes INT, @ano INT;
1536DECLARE @primeiroDia CHAR(10);
1537SET @mes = MONTH(@Data);
1538SET @ano = YEAR(@Data);
1539SET @primeiroDia = dbo.DataFormatada(1, @mes, @ano);
1540RETURN @primeiroDia;
1541END
1542SELECT dbo.PrimeiroDiaData (‘2010-11-23’)
15436.
1544CREATE FUNCTION MediaNotas(@nota1 FLOAT, @nota2 FLOAT, @nota3
1545FLOAT, @nota4 FLOAT)
1546RETURNS FLOAT
1547AS
1548BEGIN
1549RETURN (@nota1 + @nota2 + @nota3 + @nota4) /4;
1550END
1551SELECT dbo.MediaNotas (1, 3, 4, 4)
155237
1553Respostas dos ExercÃcios
15547.
1555CREATE FUNCTION AreaQuadrado(@lado INT)
1556RETURNS INT
1557AS
1558BEGIN
1559RETURN @lado * @lado;
1560END
1561SELECT dbo.AreaQuadrado (2)
15628.
1563CREATE FUNCTION SomaPares(@valorMinimo INT, @valorMaximo INT)
1564RETURNS INT
1565AS
1566BEGIN
1567DECLARE @soma INT = 0;
1568WHILE @valorMinimo <= @valorMaximo
1569BEGIN
1570IF (@valorMinimo%2) = 0
1571SET @soma = @soma + @valorMinimo;
1572SET @valorMinimo = @valorMinimo + 1;
1573END;
1574RETURN @soma;
1575END
1576SELECT dbo.SomaPares (1, 5)
15779.
1578ALTER FUNCTION SomaImpares()
1579RETURNS INT
1580AS
1581BEGIN
1582DECLARE @i INT = 0, @soma INT = 0;
1583WHILE @i <= 50
1584BEGIN
1585IF (@i%2) = 1
1586SET @soma = @soma + @i;
1587SET @i = @i + 1;
1588END;
1589RETURN @soma;
1590END
1591SELECT dbo.SomaImpares()
159210.
1593CREATE FUNCTION Equacao2Grau(@a INT, @b INT, @c INT)
1594RETURNS VARCHAR(30)
1595AS
1596BEGIN
1597DECLARE @resp VARCHAR(30);
1598DECLARE @delta FLOAT, @valor1 FLOAT, @valor2 FLOAT;
1599SET @delta = POWER(@b,2) - 4 * @a * @c;
1600IF @delta < 0
160138
1602Linguagem SQL - Guia Prático de Aprendizagem
1603SET @resp = ‘Não há resposta’
1604ELSE
1605IF @delta = 0
1606BEGIN
1607SET @valor1 = (- @b + SQRT(@delta)) / 2 * @a;
1608SET @resp = ‘Resp1= ‘ + CONVERT(CHAR(5),@valor1)
1609END
1610ELSE
1611BEGIN
1612SET @valor1 = (- @b + SQRT(@delta)) / 2 * @a;
1613SET @valor2 = (- @b - SQRT(@delta)) / 2 * @a;
1614SET @resp = ‘Resp1= ‘ + CONVERT(CHAR(5),@valor1) + ‘ e
1615Resp2= ‘ + CONVERT(CHAR(5),@valor2);
1616END
1617RETURN @resp;
1618END
1619SELECT dbo.Equacao2Grau(3, 1, 2) --Não há resposta
1620SELECT dbo.Equacao2Grau(-1, 4, -4) --Resp1=2
1621SELECT dbo.Equacao2Grau(1, -5, 6) --Resp1=3 e Resp2=2
1622CapÃtulo 20
16231.
1624SELECT *
1625FROM Clientes
1626WHERE Cidade = ‘Buenos Aires’
1627ORDER BY Nome
16282.
1629SELECT *
1630FROM Fornecedores
1631WHERE Pais = ‘Japão’
1632ORDER BY Cargo DESC
16333.
1634SELECT Cargo, COUNT(*) AS QtdeFornecedorPorCargo
1635FROM Fornecedores
1636GROUP BY Cargo
16374.
1638SELECT Contato, Cargo, ‘Clientes’ AS tipo
1639FROM Clientes
1640WHERE Pais = ‘Alemanha’
1641UNION
1642SELECT Contato, Cargo, ‘Fornecedores’ AS tipo
1643FROM Fornecedores
1644WHERE Pais = ‘Brasil’
1645ORDER BY tipo
164639
1647Respostas dos ExercÃcios
16485.
1649SELECT Contato, Cargo
1650FROM Clientes
1651WHERE Cargo = ‘Agente de vendas’
1652UNION
1653SELECT Contato, Cargo
1654FROM Fornecedores
1655WHERE Cargo = ‘Gerente de Marketing’
16566.
1657SELECT TOP 3 Descr, Preco
1658FROM Produtos
1659ORDER BY Preco DESC
16607.
1661SELECT TOP 4 Descr, Preco
1662FROM Produtos
1663ORDER BY Preco
16648.
1665SELECT SUM(Preco) AS Soma, AVG(Preco) AS Media, MAX(Preco) AS
1666MaiorPreco, MIN(Preco) AS MenorPreco
1667FROM Produtos
1668WHERE CodCategoria = (SELECT CodCategoria
1669FROM Categorias
1670WHERE Descr = ‘Frutos do Mar’)
16719.
1672SELECT *, (Preco*1.10) AS PrecoAumentado
1673FROM Produtos
1674WHERE CodCategoria = (SELECT CodCategoria
1675FROM Categorias
1676WHERE Descr = ‘Bebidas’)
167710.
1678SELECT *, (Preco - (Preco*0.2)) AS PrecoDiminuido
1679FROM Produtos
1680WHERE CodCategoria = (SELECT CodCategoria
1681FROM Categorias
1682WHERE Descr = ‘Condimentos’)
168311.
1684SELECT p.*
1685FROM Pedidos p INNER JOIN Clientes c ON p.CodCli = c.CodCli
1686AND c.Cargo = ‘Proprietário’
1687OU
1688SELECT *
1689FROM Pedidos
1690WHERE CodCli IN (SELECT CodCli
1691FROM Clientes
1692WHERE Cargo = ‘Proprietário’)
169340
1694Linguagem SQL - Guia Prático de Aprendizagem
169512.
1696SELECT DISTINCT c.*
1697FROM Clientes C INNER JOIN Pedidos Pd ON C.CodCli = Pd.CodCli
1698INNER JOIN DetalhesPed DP ON Pd.NumPed = DP.NumPed
1699INNER JOIN Produtos P ON DP.CodProd = P.CodProd
1700AND P.Descr = ‘Guaraná Fantástica’
1701OU
1702SELECT *
1703FROM Clientes
1704WHERE CodCli IN (SELECT CodCli
1705FROM Pedidos
1706WHERE NumPed IN (SELECT NumPed
1707FROM DetalhesPed
1708WHERE CodProd IN (SELECT CodProd
1709FROM Produtos
1710WHERE Descr = ‘Guaraná Fantástica’)))
171113.
1712SELECT *
1713FROM Clientes
1714WHERE CodCli IN (SELECT CodCli
1715FROM Pedidos
1716WHERE YEAR(DataPed)=1997 AND MONTH(DataPed)=09)
171714.
1718SELECT *
1719FROM Produtos
1720WHERE CodProd IN (SELECT CodProd
1721FROM DetalhesPed
1722WHERE NumPed NOT IN (SELECT NumPed
1723FROM Pedidos
1724WHERE YEAR(DataPed) = 1996 AND MONTH(DataPed) = 04))
172515.
1726SELECT Nome
1727FROM Clientes
1728WHERE CodCli IN (SELECT CodCli
1729FROM Pedidos
1730WHERE NumPed IN (SELECT NumPed
1731FROM DetalhesPed
1732WHERE CodProd NOT IN (SELECT CodProd
1733FROM Produtos
1734WHERE Descr = ‘chocolade’)) )
173516.
1736SELECT Descr
1737FROM Produtos
1738WHERE CodProd IN (SELECT CodProd
1739FROM DetalhesPed
174041
1741Respostas dos ExercÃcios
1742WHERE NumPed IN (SELECT NumPed
1743FROM Pedidos
1744WHERE CodCli IN (SELECT CodCli
1745FROM Clientes
1746WHERE Nome LIKE ‘A%’)))
174717.
1748CREATE VIEW Produtos_carnes AS
1749SELECT P.*
1750FROM Produtos P JOIN Categorias C ON P.CodCategoria=C.
1751CodCategoria
1752AND C.Descr=’carnes/aves’
175318.
1754CREATE VIEW Pedidos_EUA AS
1755SELECT *
1756FROM Pedidos
1757WHERE CodCli IN (SELECT CodCli
1758FROM Clientes
1759WHERE Pais=’EUA’)
176019.
1761CREATE VIEW Pedidos_entregues_berlin_1997 AS
1762SELECT P.*
1763FROM Pedidos P JOIN Clientes C ON P.CodCli=C.CodCli
1764AND C.Cidade=’Berlin’ AND YEAR(P.DataEntrega)=1997
176520.
1766CREATE VIEW Pedidos_descontinuados AS
1767SELECT Pd.NumPed
1768FROM Pedidos Pd JOIN DetalhesPed DP ON Pd.NumPed=DP.NumPed
1769JOIN Produtos Pr ON DP.CodProd=Pr.CodProd
1770AND Pr.Descontinuado=1
177121.
1772SELECT TOP 5 NumPed, DataEntrega
1773FROM Pedidos
1774ORDER BY Frete
177522.
1776SELECT Nome, Sobrenome
1777FROM Funcionarios
1778WHERE DataNasc = (SELECT MAX(DataNasc)
1779FROM Funcionarios)
178023.
1781CREATE VIEW Clientes_n96 AS
1782SELECT Nome, Contato, Cargo
1783FROM Clientes
1784WHERE CodCli NOT IN (SELECT CodCli
1785FROM Pedidos
1786WHERE YEAR(DataEntrega)=1996)
178742
1788Linguagem SQL - Guia Prático de Aprendizagem
178924.
1790SELECT Cargo, COUNT(Cargo)
1791FROM Clientes_n96
1792GROUP BY Cargo
179325.
1794CREATE VIEW Valores_Pais AS
1795SELECT Pais, SUM(Preco*Qtde) AS valor
1796FROM Clientes C JOIN Pedidos P ON C.CodCli=P.CodCli
1797JOIN DetalhesPed DP ON DP.NumPed=P.NumPed
1798GROUP BY Pais
179926.
1800SELECT Pais
1801FROM Valores_Pais
1802WHERE valor > (SELECT AVG(valor)
1803FROM Valores_Pais)
180427.
1805CREATE PROCEDURE Diminui_Preco
1806@CodProd INT,
1807@Perc DECIMAL(5,2)
1808AS
1809UPDATE Produtos
1810SET Preco = Preco - (Preco * @Perc)
1811WHERE CodProd = @CodProd
1812EXEC Diminui_Preco 43, 0.0 --um produtos
1813EXEC Diminui_Preco 90, 0.0 --não existe o produto
181428.
1815CREATE PROCEDURE Fornecedores_Pais
1816@Pais VARCHAR(15)
1817AS
1818SELECT *
1819FROM Fornecedores
1820WHERE Pais=@Pais
1821EXEC Fornecedores_Pais ‘Brasil’
182229.
1823CREATE PROCEDURE Conta_Categoria
1824@Categoria VARCHAR(15)
1825AS
1826SELECT COUNT(CodProd)
1827FROM Produtos
1828WHERE CodCategoria IN (SELECT CodCategoria
1829FROM Categorias
1830WHERE Descr=@Categoria)
1831EXEC Conta_Categoria ‘Confeitos’
183243
1833Respostas dos ExercÃcios
183430.
1835CREATE PROCEDURE Media_Frete
1836@DataInicial DATE,
1837@DataFinal DATE
1838AS
1839SELECT AVG(Frete) AS media, SUM(Frete) AS soma
1840FROM Pedidos
1841WHERE DataEntrega BETWEEN @DataInicial AND @DataFinal
1842EXEC Media_Frete ‘1996-01-01’, ‘1996-12-31’
184331.
1844CREATE FUNCTION SeuNomeParImpar(@SeuNome VARCHAR(10))
1845RETURNS VARCHAR(5)
1846AS
1847BEGIN
1848DECLARE @resp VARCHAR(5);
1849IF (LEN(@SeuNOme) % 2) = 0
1850SET @resp = ‘Par’
1851ELSE
1852SET @resp = ‘Impar’;
1853RETURN @resp;
1854END
1855SELECT dbo.SeuNomeParImpar(‘Luciana’) --Impar
1856SELECT dbo.SeuNomeParImpar(‘Caio’) --Par
185732.
1858CREATE TRIGGER AlertaInsercaoFornecedor
1859ON Fornecedores
1860FOR INSERT AS
1861PRINT (‘Nova inserção de fornecedor !!!’)
1862INSERT INTO Fornecedores
1863VALUES (‘Editora Erica’, ‘João’, ‘Editor’, ‘Rua de São Paulo’,
1864‘São Paulo’, ‘’, ‘Brasil’)
186533.
1866CREATE TRIGGER Mensagem_Exclui_Pedido
1867ON Pedidos
1868FOR DELETE AS
1869PRINT (‘*** Pedido ExcluÃdo ***’)
1870DELETE FROM Pedidos where NumPed=0