--Aula 01 - Querying Multiple Tables by Using Joins
-- ====================================================================================-- Autor : Lineu Alves Lima Filho
-- Data Criação : 02/11/2009
-- Descrição : Módulo 04 - Lição 01
-- ====================================================================================
-- Versão : 1.0
-- ====================================================================================
-- Versão : 1.1
-- Data Atualização : 17/02/2010
-- ====================================================================================
--Recriando o banco de dados DB2778
--DROP DATABASE DB2778
--GO
CREATE DATABASE DB2778
GO
USE DB2778
GO
--Criando a tabela departamentos
CREATE TABLE departamentos
(
depart_id INT IDENTITY PRIMARY KEY,
depart_nome CHAR(30) NOT NULL
)
GO
--Criando a tabela empregados
CREATE TABLE empregados
(
emp_id INT IDENTITY PRIMARY KEY,
emp_nome CHAR(50) NOT NULL,
depart_id INT,
emp_cidade VARCHAR(35),
CONSTRAINT fk_depart_id FOREIGN KEY (depart_id)
REFERENCES departamentos
)
GO
--Criando a tabela dependentes
CREATE TABLE dependentes
(
dep_id INT IDENTITY PRIMARY KEY,
emp_id INT NOT NULL,
dep_nome CHAR(50) NOT NULL,
CONSTRAINT fk_emp_id FOREIGN KEY (emp_id)
REFERENCES empregados
)
GO
--Inserindo dados na tabela departamentos
INSERT INTO departamentos (depart_nome)
VALUES ('Vendas'), ('Financeiro'), ('TI'), ('Compras'), ('Manutenção')
--Inserindo dados na tabela empregados
INSERT INTO empregados (emp_nome, depart_id, emp_cidade)
VALUES ('Zé',5,'São Paulo'), ('Mané',5,'Jundiaí'), ('Sheila',4,'Sumaré'),
('Ana',3,'Campinas'), ('Maria',2,'Americana'), ('Robertão',1,'Campinas')
--Inserindo dados na tabela dependentes
INSERT INTO dependentes (emp_id, dep_nome)
VALUES (1,'Zézinho'), (1,'Zéquinha'), (1,'Zéca'), (2,'Manézinho'), (4,'Aninha'), (5,'Mariazinha')
SELECT * FROM empregados
SELECT * FROM departamentos
SELECT * FROM dependentes
--Listar o NOME e o DEPARTAMENTO dos funcionários - com WHERE
SELECT E.emp_nome, D.depart_nome
FROM empregados E, departamentos D
WHERE E.depart_id = D.depart_id
--Ou
--Listar o NOME e o DEPARTAMENTO dos funcionários - com INNER JOIN
--Pode usar 'INNER JOIN' ou apenas 'JOIN' no SQLSERVER
SELECT E.emp_nome, D.depart_nome
FROM empregados E INNER JOIN departamentos D
ON E.depart_id = D.depart_id
--Ou
--Listar o NOME e o DEPARTAMENTO dos funcionários - com INNER JOIN e SEM table alias
SELECT empregados.emp_nome, departamento.depart_nome
FROM empregados INNER JOIN departamentos
ON empregados.depart_id = departamentos.depart_id
--Excluíndo a FOREIGN KEY fk_depart_id
ALTER TABLE empregados
DROP CONSTRAINT fk_depart_id
--Inserindo um funcionário sem departamento
INSERT INTO empregados (emp_nome, emp_cidade)
VALUES ('Marilda','Taubaté')
--Recriando a FOREIGN KEY
ALTER TABLE empregados
ADD CONSTRAINT fk_depart_id FOREIGN KEY (depart_id)
REFERENCES departamentos (depart_id)
GO
--Visualizando todos os funcionários, mesmo que não tenha um departamento (LEFT JOIN)
--Pegue tudo que tem do lado esquerdo independente de ter ao lado direito
--Pode usar 'LEFT OUTER JOIN' ou apenas 'LEFT JOIN' no SQLSERVER
SELECT E.emp_nome, D.depart_nome
FROM empregados E LEFT OUTER JOIN departamentos D
ON E.depart_id = D.depart_id
--Inserindo um departamento sem funcionário
INSERT INTO departamentos (depart_nome)
VALUES ('Contas a Pagar')
--Visualisando todos os departamentos, mesmo que não tenha funcionário (RIGHT JOIN)
--Pegue tudo que tem do lado direito independente de ter ao lado esquerdo
--Pode usar 'RIGHT OUTER JOIN' ou apenas 'RIGHT JOIN' no SQLSERVER
SELECT E.emp_nome, D.depart_nome
FROM empregados E RIGHT OUTER JOIN departamentos D
ON E.depart_id = D.depart_id
--Visualizando todos os funcionários, mesmo que não tenha departamento e
--todos os departamentos, mesmo que não tenha funcionário (FULL JOIN)
--Pegue tudo que tem do lado direito e esquerdo independente de ter de um lado ou não
--Pode usar 'FULL OUTER JOIN' ou apenas 'FULL JOIN' no SQLSERVER
SELECT E.emp_nome, D.depart_nome
FROM empregados E FULL OUTER JOIN departamentos D
ON E.depart_id = D.depart_id
--Listar o NOME e os DEPENDENTES dos funcionários
SELECT e.emp_nome, d.dep_nome
FROM empregados e INNER JOIN dependentes d
ON e.emp_id = d.emp_id
----Listar o NOME dos empregados, o DEPARTAMENTO e seus DEPENDENTES
SELECT e.emp_nome,
dpt.depart_nome,
d.dep_nome
FROM departamentos dpt INNER JOIN empregados e
ON dpt.depart_id = e.depart_id
INNER JOIN dependentes d
ON e.emp_id = d.emp_id
--Listar os funcionários que possuem dependentes e trabalham no departamento de TI
SELECT TOP 10 * FROM departamentos
SELECT E.emp_nome
FROM empregados E INNER JOIN dependentes D
ON E.emp_id = D.emp_id
AND e.depart_id = 3
/************************************************************************************/
--CROSS JOIN
CREATE TABLE alunos
(
aluno_id int identity,
nome_aluno char(30)
)
GO
CREATE TABLE professores
(
prof_id int identity,
nome_prof char(30)
)
GO
INSERT INTO alunos
VALUES ('Marinho'), ('Joãozinho'), ('Aninha')
INSERT INTO professores
VALUES ('Prof Roberto'), ('Prof Francisco'), ('Prof(a) Helena')
SELECT nome_prof, nome_aluno
FROM alunos CROSS JOIN professores
SELECT nome_prof, nome_aluno
FROM alunos, professores
--Aula 02 - Applying Joins for Typical Reporting Needs
USE DB2778
GO
--SELF-JOIN
CREATE TABLE funcionarios
(
func_id INT IDENTITY,
nome_func CHAR(30),
chefe_id CHAR(30)
)
GO
INSERT INTO funcionarios VALUES ('Joana',4)
,('Maria',4), ('Lucas',6), ('Mario',4), ('Henrique',6)
,('Julia',6), ('Roberto',4), ('Eduarda',6)
SELECT * FROM funcionarios
--Verificando o chefe de cada funcionário
SELECT F.nome_func AS [Funcionario],
C.nome_func AS [Chefe]
FROM funcionarios C INNER JOIN funcionarios F
ON F.chefe_id = C.func_id
--Verificando o chefe do funcionário Henrique
SELECT C.nome_func AS [Chefe]
FROM funcionarios C INNER JOIN funcionarios F
ON F.chefe_id = C.func_id
WHERE F.nome_func = 'Henrique'
--------------------------------------------------------------
-- Inline Table-valued Functions
--------------------------------------------------------------
-- Criando a função
CREATE FUNCTION dbo.fn_busca_func(@nome_func CHAR(30))
RETURNS TABLE
AS
RETURN (SELECT C.nome_func AS [Chefe]
FROM funcionarios C INNER JOIN funcionarios F
ON F.chefe_id = C.func_id
WHERE F.nome_func = @nome_func)
-- Chamando a função
SELECT * FROM dbo.fn_busca_func ('Roberto')
-- Creando uma View
CREATE VIEW vw_fun
AS
SELECT nome_func
FROM funcionarios
--Chamando a View
SELECT * FROM vw_fun
--Diferença de VIEW e FUNÇÃO
--VIEW não precisa passar parametro
--FUNÇÃO vc passa parametro
--Aula 03 - Combining and Limiting Result Sets
USE DB2778
GO
--UNION e UNION ALL
CREATE TABLE clientes
(
cod_cli INT IDENTITY PRIMARY KEY,
nome_cli CHAR(30) NOT NULL
)
GO
CREATE TABLE fornecedores
(
cod_fornec INT IDENTITY PRIMARY KEY,
nome_fornec CHAR(30) NOT NULL
)
GO
INSERT INTO clientes VALUES
('Mario da Silva'), ('Lineu Silva'), ('Agostinho Carrara')
INSERT INTO fornecedores VALUES
('Fernando Souza'), ('Mario da Silva'), ('Joelma Leite')
--Retornando as colunas nome_cli e nome_fornec com UNION
--unir as table sem repetir no nome do Mario da Silva (DISTINCT)
SELECT nome_cli FROM clientes
UNION
SELECT nome_fornec FROM fornecedores
--Retornando as colunas nome_cli e nome_fornec com UNION ALL
--unir as tabelas aparecendo todos os dados sem DISTINCT
SELECT nome_cli FROM clientes
UNION ALL
SELECT nome_fornec FROM fornecedores
--INTERSECT e EXCEPT
CREATE TABLE cidades01
(
nome_cid01 CHAR(30)
)
GO
CREATE TABLE cidades02
(
nome_cid02 CHAR(30)
)
GO
INSERT cidades01 VALUES('Jacareí'),
('São José dos Campos'), ('Taubaté'), ('Caçapava')
GO
INSERT cidades02 VALUES('Jacareí'),
('São José dos Campos'), ('Taubaté'), ('Lorena')
GO
-- Obtendo registros em comum com (INTERSECT)
--Tras comente o q estiver em ambas as tabelas
SELECT nome_cid01 FROM cidades01
INTERSECT
SELECT nome_cid02 FROM cidades02
-- Obtendo registros que existem na tabela cidades01 e
-- não existem na tabela cidades02 com (EXCEPT)
SELECT nome_cid01 FROM cidades01
EXCEPT
SELECT nome_cid02 FROM cidades02
-- Obtendo registros que existem na tabela cidades02 e
-- não existem na tabela cidades01 com (EXCEPT)
SELECT nome_cid02 FROM cidades02
EXCEPT
SELECT nome_cid01 FROM cidades01
-- TOP e TABLESAMPLE
CREATE TABLE pessoa
(
cod_pessoa INT,
primeiro_nome NVARCHAR(50),
nome_meio NVARCHAR(50),
sobrenome NVARCHAR(50)
)
GO
--Importando 4 colunas da tabela Person.Person do banco AdventureWorks2008
INSERT INTO pessoa
SELECT BusinessEntityID, FirstName, MiddleName, LastName
FROM AdventureWorks2008.Person.Person
--Retorna os 20 primeiros registros
SELECT TOP 20 * FROM pessoa
--Retorna 10 porcentos dos registros
SELECT TOP 10 PERCENT * FROM pessoa
--Retorna uma parte dos registros em torno dos 10 porcento
SELECT * FROM pessoa TABLESAMPLE (10 PERCENT)
--REPEATABLE (10)
--Retorna uma parte dos registros em torno dos 100 registros
SELECT * FROM pessoa TABLESAMPLE (100 ROWS)
--REPEATABLE (10)
Nenhum comentário:
Postar um comentário