SQL - PARTE 3

Apostilas
http://www.euder.com.br/down.htm

 Exercícios

Tabelas:

Empresas

            COD               CHAR(2)                   CHAVE PRIMÁRIA

            DESCR          VARCHAR(20)            NÃO VAZIO

Empregados

            COD               CHAR(2)                   CHAVE PRIMÁRIA

            NOME            VARCHAR(20)          NÃO VAZIO

            SALARIO        REAL

Dependentes

            COD               CHAR(2)                  CHAVE PRIMÁRIA

            NOME            VARCHAR(20)         NÃO VAZIO

Relacionamentos:

Empresas_Empregados

COD_EMP     CHAR(2)                   
                       CHAVE ESTRANGEIRA EMPRESA

COD_FUN     CHAR(2)                  
                       CHAVE ESTRANGEIRA FUNCIONARIO

            COD_EMP+COD_FUN                   CHAVE PRIMÁRIA

Empregados_Dependentes

            COD_FUN     CHAR(2)                   
                                   CHAVE ESTRANGEIRA FUNCIONARIO

            COD_DEP     CHAR(2)                  
                                   CHAVE ESTRANGEIRA DEPENDENTES 
           COD_FUN+COD_DEP                   CHAVE PRIMÁRIA

                       

Inserir Elementos:

Empresas

01                EMPRESA_A

02                EMPRESA_B

Funcionários

01                FUNCIONARIO_A             200

02                FUNCIONARIO_B              130     

03                FUNCIONÁRIO_C              400

04                FUNCIONÁRIO_D             130

Dependentes

            01        DEPENDE_A                      

02        DEPENDE_B           

03                DEPENDE_C

Empre_Emp

01                01

01                02

02                03

02        04

Empr_dep

01                01

01                02

02                03

Solução

-- TABELA EMPRESA

CREATE TABLE EMPRESA

 (

 COD CHAR(2) NOT NULL,

 NOME VARCHAR(20) NOT NULL,

 PRIMARY KEY (COD)

 );

-- TABELA EMPREGADOS

CREATE TABLE EMPREGADOS

(

COD CHAR(2) NOT NULL,

NOME VARCHAR(20) NOT NULL,

SALARIO REAL

PRIMARY KEY (COD)

);

-- TABELA DEPENDENTES

CREATE TABLE DEPENDENTES

(

COD CHAR(2) NOT NULL,

NOME VARCHAR(20) NOT NULL,

PRIMARY KEY (COD)

);

-- RELACIONAMENTO EMPRESAS X EMPREGADOS

CREATE TABLE EMP_EMP

(

COD_EMP CHAR(2) NOT NULL,

 COD_FUN CHAR(2) NOT NULL,

 PRIMARY KEY (COD_EMP,COD_FUN),

 FOREIGN KEY (COD_EMP) REFERENCES EMPRESA(COD),

 FOREIGN KEY (COD_FUN) REFERENCES EMPREGADOS(COD)

 );

-- RELACIONAMENTO EMPREGADOS X DEPENDENTES

CREATE TABLE EMP_DEP

(

 COD_FUN CHAR(2) NOT NULL,

 COD_DEP CHAR(2) NOT NULL,

 PRIMARY KEY (COD_FUN,COD_DEP),

 FOREIGN KEY (COD_FUN) REFERENCES EMPREGADOS(COD),

 FOREIGN KEY (COD_DEP) REFERENCES DEPENDENTES(COD)

 );

-- ELEMENTOS DA TABELA EMPRESA

INSERT INTO EMPRESA VALUES ('01','EMPRESA_A')

INSERT INTO EMPRESA VALUES ('02','EMPRESA_B')

-- ELEMENTOS DA TABELA EMPREGADOS

INSERT INTO EMPREGADOS VALUES ('01','FUNCIONARIO_A',200)

INSERT INTO EMPREGADOS VALUES ('02','FUNCIONARIO_B',130)

INSERT INTO EMPREGADOS VALUES ('03','FUNCIONARIO_C',400)

INSERT INTO EMPREGADOS VALUES ('04','FUNCIONARIO_D',130)

-- ELEMENTOS DA TABELA DEPENDENTES

INSERT INTO DEPENDENTES VALUES ('01','DEPENDE_A')

INSERT INTO DEPENDENTES VALUES ('02','DEPENDE_B')

INSERT INTO DEPENDENTES VALUES ('03','DEPENDE_C')

-- ELEMENTOS DA TABELA EMP_EMP

INSERT INTO EMP_EMP VALUES ('01','01')

INSERT INTO EMP_EMP VALUES ('01','02')

INSERT INTO EMP_EMP VALUES ('02','03')

INSERT INTO EMP_EMP VALUES ('02','04')

-- ELEMENTOS DA TABELA EMP_DEP

INSERT INTO EMP_DEP VALUES ('01','01')

INSERT INTO EMP_DEP VALUES ('01','02')

INSERT INTO EMP_DEP VALUES ('01','03')

Recuperação  

Operadores de comparação (=,>,<,<=,>=,<>,!=,!<, e !>)

Amplitude (BETWEEN and NOT BETWEEN)

Lista (IN and NOT IN)

Combinação de Strings (LIKE and NOT LIKE)

Valores desconhecidos (IS NULL e IS NOT NULL)

Combinações destes (AND, OR)

Negações (NOT)

SELECT select_list

            FROM table_list

            WHERE column_name IS [NOT] NULL

SELECT select_list

            FROM table_list

            WHERE expression comparison_operator expression         

-- OBTER AS EMPRESAS DOS FUNCIONÁRIOS

SELECT  COD, NOME FROM EMPRESA, EMP_EMP

WHERE  EMPRESA.COD = EMP_EMP.COD_FUN

 SELECT select_list

            FROM table_list

            WHERE [NOT] expression {AND|OR}[NOT] expression

-- OBTER O NOME DO FUNCIONARIO DE CODIGO 01

SELECT  EMPREGADOS.NOME FROM EMPREGADOS, EMP_EMP

WHERE  EMPREGADOS.COD = EMP_EMP.COD_FUN AND

EMP_EMP.COD_FUN = '01'

-- FORMA ERRADA DE BUSCA INFORMAÇÃO

SELECT  COD_EMP, EMPRESA.NOME AS EMPRESA,EMPREGADOS.NOME AS FUNCIONARIO

FROM EMP_EMP, EMPRESA,EMPREGADOS

WHERE 

EMP_EMP.COD_EMP = EMPRESA.COD

SELECT select_list

            FROM table_list

            WHERE expression [NOT] LIKE ‘string’

Outras opções

Seus dados existem pessoas com nome 'Sousa' ou 'Souza', poderá utilizar: LIKE '%sou_a%'

Utilizar cochetes para delimitar uma faixa,

ex: Procurar por nomes que iniciam pelas letras   F e X

SELECT * FROM EMPREGADOS WHERE NOME LIKE '[FX]%'

ex: Procurar nomes de uma letra até outra, ex: Letra A até a Letra F

SELECT * FROM EMPREGADOS WHERE NOME LIKE '[A-F]%'

ex: Procurar nomes que não comecem com a Letra F

SELECT * FROM EMPREGADOS WHERE NOME LIKE '[^F]%'

-- Nome da Empresa do Empregado 3

SELECT EMPRESA.NOME AS EMPRESA  FROM EMPRESA

WHERE

COD IN ( SELECT COD_EMP FROM EMP_EMP WHERE  COD_FUN='03')

-- OBTER OS CODIGOS DOS DEPENDENTES E OS NOMES

-- DOS EMPREGADOS RELACIONADOS COM O DEPENDENTES

SELECT  COD_DEP,COD_FUN,NOME

FROM  EMP_DEP,EMPREGADOS

WHERE EMP_DEP.COD_FUN = EMPREGADOS.COD

-- OBTER O NOME DO DEPENDENTE DO CODIGO '02'

SELECT NOME

FROM DEPENDENTES

WHERE COD IN

(SELECT COD_DEP FROM EMP_DEP WHERE COD_DEP = '02')

-- OBTER OS NOMES DOS DEPENDENTES

SELECT NOME

FROM DEPENDENTES

WHERE COD IN

(SELECT COD_DEP FROM EMP_DEP WHERE COD_DEP = DEPENDENTES.COD)

-- OBTER OS NOMES DO EMPREGADOS QUE COMEÇAM COM

-- A LETRA F

SELECT NOME

FROM EMPREGADOS

WHERE NOME LIKE 'F%'

-- OBTER OS NOMES DO EMPREGADOS QUE TEM A SUBCADEIA

-- D

SELECT NOME

FROM EMPREGADOS

WHERE NOME LIKE '%D%'

-- OBTER OS NOMES DO EMPREGADOS QUE NÃO TEM A SUBCADEIA

-- D

SELECT NOME

FROM EMPREGADOS

WHERE NOME NOT LIKE '%D%'

-- OBTER OS DADOS DO FUNCIONARIOS COM

-- SALARIOS ACIMA DE 130

SELECT *

FROM EMPREGADOS

WHERE SALARIO > 130

SELECT select_list

            FROM table_list

            WHERE expression [NOT] BETWEEN expression AND expression

-- OBTER OS DADOS DO FUNCIONARIOS COM

-- SALARIOS ENTRE 45  E 130

SELECT *

FROM EMPREGADOS

WHERE SALARIO BETWEEN 45 AND 130

OU

-- OBTER OS DADOS DO FUNCIONARIOS COM

-- SALARIOS ENTRE 45  E 130

SELECT *

FROM EMPREGADOS

WHERE SALARIO >= 45 AND SALARIO <= 130

-- OBTER OS DADOS DO FUNCIONARIOS EM ORDEM

-- ASCENDENTE DE SALARIO

SELECT *

FROM EMPREGADOS

ORDER BY  SALARIO

-- OBTER OS DADOS DO FUNCIONARIOS EM ORDEM

-- DESCENDENTE DE SALARIO

SELECT *

FROM EMPREGADOS

ORDER BY  SALARIO  DESC

-- OBTER A MEDIA DOS SALARIOS

SELECT AVG(SALARIO) AS MEDIA FROM EMPREGADOS

-- OBTER A MEDIA DOS SALARIOS > 130

SELECT AVG(SALARIO) AS MEDIA FROM EMPREGADOS

WHERE SALARIO > 130

-- OBTER O MAIOR SALARIO

SELECT MAX(SALARIO) AS MAIOR FROM EMPREGADOS

-- OBTER A QUANTIDADE DE TUPLAS

SELECT COUNT(SALARIO) AS QTD FROM EMPREGADOS

-- OBTER A SOMA DOS SALARIOS

SELECT SUM(SALARIO) AS SOMA FROM EMPREGADOS

-- REAJUSTAR O SALARIO DOS EMPREGADOS

-- QUE TEM UM SALARIO IGUAL A 400

UPDATE EMPREGADOS SET SALARIO = SALARIO * 1.23

WHERE SALARIO = 400

Até a próxima e cuide-se bem.