Introdução à Banco de Dados

http://www.amazon.com/dp/B00U0SVNKI

Banco de Dados


O objetivo dessa disciplina é introduzir nos conceitos de bancos de dados em especial banco de dados relacionais, focando na Linguagem de Consulta Estruturada que é uma linguagem de pesquisa declarativa para banco de dados relacional baseadas na álgebra relacional.

Structure Query Language (SQL)

O SQL surgiu na década de 70 na IBM baseado no modelo relacional de Codd. É o padrão para banco de dados relacionais e é mantido pela American National Standards Institute (ANSI) e International Standards Organization (ISO). Existindo várias versões revisadas do SQL ANSI/ISO, as quais são: 1986, 1987, 1992, 1999 e 2003. E além desses padronizações, existem as extensões inseridas pelos desenvolvedores de banco de dados.

 

Pode ser utilizado por uma grande faixa de usuários, mesmo com pouca experiência em programação; É uma linguagem não procedural; Reduz o tempo necessário para a criação e manutenção de sistemas; e É uma linguagem similar ao inglês. E suas categorias são:

· Data Definition Language (DDL): Create ..., Drop ..., Alter ...;

· Data Manipulation Language (DML): Insert ..., Update ..., Delete ...;

· Data Query Language (DQL): Select ...;

· Data Transaction Language (DTL): Savepoint ..., Rollback ..., Commit ...;

· Data Control Language (DCL): Grant ..., Revoke ...;

Inserção de Metadados (Criação de Tabelas)

Comando que permite criar as estruturas de armazenamento de dados.

 

Sintaxe

CREATE TABLE nome_tabela [(definição_create,...)] [table_options] [select_statement] 

definição_create:

  nome_coluna tipo [NOT NULL | NULL] [DEFAULT valor_padrão]

  [[PRIMARY] KEY]

  | [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,...)

  | INDEX [nome_indice] (index_nome_coluna,...)

  | [CONSTRAINT [symbol]] UNIQUE [INDEX] [index_name] (index_col_name,...)

  | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)

definição_referência:

        REFERENCES nome_tabela [(index_nome_coluna,...)]

                   [ON DELETE opção_referência]

                   [ON UPDATE opção_referência]

opção_referência:

        RESTRICT | CASCADE | SET NULL | SET DEFAULT

 

Dicas

É possível criar uma tabela a partir de uma subconsulta;

CREATE TABLE tabela [coluna, ...] AS subconsulta.

 

Práticas

Criar a seguintes tabelas: Tmp: Número (PK), número inteiro. Cargo: Número (PK), número inteiro, auto-incremento; Nome, literal variável(30), não nulo. Departamento: Número (PK), número inteiro; Nome, literal variável(30), não nulo; Fator, número decimal com precisão 3 e escala 2. Empregado: Número (PK), número inteiro; Nome, literal variável (50), não nulo; Departamento, (FK) numérico, atualização cascata, exclusão restrita.

 

--SQLServer

CREATE TABLE CARGO (

NUMERO INTEGER IDENTITY( 1, 1),

NOME VARCHAR(30) NOT NULL,

PRIMARY KEY (NUMERO)

);

 

--Postgre|Oracle

CREATE TABLE CARGO (

NUMERO INTEGER,

NOME VARCHAR(30) NOT NULL,

PRIMARY KEY (NUMERO)

);

CREATE SEQUENCE CARGOSEQ INCREMENT BY 1 START WITH 1;

 

--MySQL

CREATE TABLE CARGO (

NUMERO INTEGER AUTO_INCREMENT,

NOME VARCHAR(30) NOT NULL,

PRIMARY KEY (NUMERO)

);

 

--Firebird

CREATE TABLE CARGO (

NUMERO INTEGER,

NOME VARCHAR(30) NOT NULL,

PRIMARY KEY (NUMERO)

);

CREATE GENERATOR CARGOGEN;

CREATE TRIGGER CARGOTRI FOR CARGO

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.NUMERO = GEN_ID(CARGOGEN, 1);

END;

CREATE TABLE TMP (

NUMERO INTEGER PRIMARY KEY

);

 

--Todos

CREATE TABLE DEPARTAMENTO (

NUMERO INTEGER,

NOME VARCHAR(30) NOT NULL,

FATOR DECIMAL(3,2),

PRIMARY KEY (NUMERO)

);

 

--SQLServer | Postgre | MySQL | Firebird

CREATE TABLE EMPREGADO (

NUMERO INTEGER,

NOME VARCHAR(50) NOT NULL,

DEPARTAMENTO INTEGER,

PRIMARY KEY (NUMERO),

FOREIGN KEY (DEPARTAMENTO) REFERENCES DEPARTAMENTO (NUMERO) ON UPDATE CASCADE

);

 

--Oracle

CREATE TABLE EMPREGADO (

NUMERO INTEGER,

NOME VARCHAR(50) NOT NULL,

DEPARTAMENTO INTEGER,

PRIMARY KEY (NUMERO),

FOREIGN KEY (DEPARTAMENTO) REFERENCES DEPARTAMENTO (NUMERO)

);

Inserção de Dados (Linhas | Registros | Ocorrências | Tuplas)

Comando que permite incluir os dados nas estruturas de armazenamento.

 

Sintaxe

INSERT [INTO] nome_tabela [(nome_coluna,...)]

        VALUES ((expressão | DEFAULT),...),(...),...

        | [INTO] nome_tabela [(nome_coluna,...)]

        SELECT ...

 

Dicas

Pode-se inserir linha a linha;

Pode-se dependendo do BD inserir uma lista;

Pode-se inserir linhas de uma tabela em outra;

Não se usa a cláusula VALUE e sim SELECT.

 

Práticas

Inserir esses dados nas tabela Cargo: Número, Nome; 1, Analista Trainee; 2, Analista Junior; 3, Analista Pleno; 4, Analista Sênior; 5, Analista Master:

 

--SQLServer | MySql | Firebird

INSERT INTO CARGO (NOME) VALUES ('ANALISTA TRAINEE');

INSERT INTO CARGO (NOME) VALUES ('ANALISTA JUNIOR');

INSERT INTO CARGO (NOME) VALUES ('ANALISTA PLENO');

INSERT INTO CARGO (NOME) VALUES ('ANALISTA SÊNIOR');

INSERT INTO CARGO (NOME) VALUES ('ANALISTA MASTER');

INSERT INTO CARGO (NOME) VALUES ('CONSULTOR');

 

--Postgre

INSERT INTO CARGO (NUMERO, NOME) VALUES 

(nextval('CARGOSEQ'), 'ANALISTA TRAINEE');

INSERT INTO CARGO (NUMERO, NOME) VALUES 

(nextval('CARGOSEQ'), 'ANALISTA JUNIOR');

INSERT INTO CARGO (NUMERO, NOME) VALUES

 (nextval('CARGOSEQ'), 'ANALISTA PLENO');

INSERT INTO CARGO (NUMERO, NOME) VALUES 

(nextval('CARGOSEQ'), 'ANALISTA SÊNIOR');

INSERT INTO CARGO (NUMERO, NOME) VALUES 

(nextval('CARGOSEQ'), 'ANALISTA MASTER');

INSERT INTO CARGO (NUMERO, NOME) VALUES 

(nextval('CARGOSEQ'), 'CONSULTOR');

 

--Oracle

INSERT INTO CARGO (NUMERO, NOME) VALUES 

(CARGOSEQ.NextVal, 'ANALISTA TRAINEE');

INSERT INTO CARGO (NUMERO, NOME) VALUES 

(CARGOSEQ.NextVal, 'ANALISTA JUNIOR');

INSERT INTO CARGO (NUMERO, NOME) VALUES 

(CARGOSEQ.NextVal, 'ANALISTA PLENO');

INSERT INTO CARGO (NUMERO, NOME) VALUES 

(CARGOSEQ.NextVal, 'ANALISTA SÊNIOR');

INSERT INTO CARGO (NUMERO, NOME) VALUES 

(CARGOSEQ.NextVal, 'ANALISTA MASTER');

INSERT INTO CARGO (NUMERO, NOME) VALUES 

(CARGOSEQ.NextVal, 'CONSULTOR');

 

Departamento: Número, Nome, Fator; 10, Administração (Admin), 1.75; 20, Marketing (Mkt), 1.85;

50, Compras (Com), 2. 73; 60, Tecnologia (Tec), 1.55; 80, Vendas (Ven), 1.87; 90, Executivo (Exec), 1.92; 110, Contabilidade (Contab), 2.35; 190, Contratos (Ctt), 2.23:

 

--Todos

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) 

VALUES (10, 'ADMINISTRAÇÃO (ADMIN)', 1.75);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) 

VALUES (20, 'MARKETING (MKT)', 1.85);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) 

VALUES (50, 'COMPRAS (COM)', 2.73);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) 

VALUES (60, 'TECNOLOGIA (TEC)', 1.55);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) 

VALUES (80, 'VENDAS (VEN)', 1.87);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) 

VALUES (90, 'EXECUTIVO (EXEC)', 1.92);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) 

VALUES (110, 'CONTABILIDADE (CONTAB)', 2.35);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) 

VALUES (190, 'CONTRATOS (CTT)', 2.23);

 

Inserir dados na tabela Empregados: Número, Nome, Departamento; 100, Antônio Palmeira, 10; 101, José Figueira, 20; 102, Maria Mangueira, 10; 103, João Abacateiro, 50; 104, Carlos Castanheiro, 60; 107, Mônica Videira, 90; 124, Pedro Laranjeira, 80; 141, Tiago Pessegueiro, 110;

142, Renata Videira, 80; 143, Gabriel Macieira, 110:

 

--Todos

INSERT INTO EMPREGADO VALUES (100, 'ANTÔNIO PALMEIRA', 10);

INSERT INTO EMPREGADO VALUES (101, 'JOSÉ FIGUEIRA', 20);

INSERT INTO EMPREGADO VALUES (102, 'MARIA MANGUEIRA', 10);

INSERT INTO EMPREGADO VALUES (103, 'JOÃO ABACATEIRO', 50);

INSERT INTO EMPREGADO VALUES (104, 'CARLOS CASTANHEIRO', 60);

INSERT INTO EMPREGADO VALUES (107, 'MÔNICA VIDEIRA', 90);

INSERT INTO EMPREGADO VALUES (124, 'PEDRO LARANJEIRA', 80);

INSERT INTO EMPREGADO VALUES (141, 'TIAGO PESSEGUEIRO', 110);

INSERT INTO EMPREGADO VALUES (142, 'RENATA VIDEIRA', 80);

INSERT INTO EMPREGADO VALUES (143, 'GABRIEL MACIEIRA', 110);

Alteração de Metadados (Modificar Estruturas)

Comando que permite modificar as estruturas de armazenamento.

 

Sintaxe

ALTER TABLE nome_tbl especificação_alter [, especificação_alter ...]

especificação_alter:

    ADD [COLUMN] definição_create [FIRST | AFTER nome_coluna ]

  | ADD [COLUMN] (definição_create, definição_create,...)

  | ADD INDEX [nome_indice] (index_nome_col,...)

  | ADD [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,...)

  | ADD [CONSTRAINT [symbol]] UNIQUE [index_name] (index_col_name,...)

  | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...)

            [definição_referncia]

  | ALTER [COLUMN] nome_col {SET DEFAULT literal | DROP DEFAULT}

  | CHANGE [COLUMN] nome_col_antigo definição_create

               [FIRST | AFTER nome_coluna]

  | MODIFY [COLUMN] definição_create [FIRST | AFTER nome_coluna]

  | DROP [COLUMN] nome_col

  | DROP PRIMARY KEY

  | DROP INDEX nome_indice

  | RENAME [TO] nome_nova_tbl

  | ORDER BY col

 

Práticas

Alterar os dados da tabela Empregado adicionando as colunas: Primeironome, literal variável (25); Sobrenome, literal variável (25); Departamento, (FK) número inteiro; Cargo, (FK) número inteiro, atualização cascata; Salário, número real; Comissão, número inteiro; Nascimento, data; Apelido, literal fixo(10), não nulo; Gerente, número inteiro:

 

--SQLServer

ALTER TABLE EMPREGADO

ADD PRIMEIRONOME VARCHAR(25),

SOBRENOME VARCHAR(25),

CARGO INTEGER,

SALARIO REAL,

COMISSAO INTEGER,

NASCIMENTO DATE,

APELIDO VARCHAR(10),

GERENTE INTEGER;

 

--Postgre | MySQL | Firebird

ALTER TABLE EMPREGADO

ADD PRIMEIRONOME VARCHAR(25),

ADD SOBRENOME VARCHAR(25),

ADD CARGO INTEGER,

ADD SALARIO REAL,

ADD COMISSAO INTEGER,

ADD NASCIMENTO DATE,

ADD APELIDO VARCHAR(10),

ADD GERENTE INTEGER;

 

--Oracle

ALTER TABLE EMPREGADO

ADD (PRIMEIRONOME VARCHAR(25),

SOBRENOME VARCHAR(25),

CARGO INTEGER,

SALARIO REAL,

COMISSAO INTEGER,

NASCIMENTO DATE,

APELIDO VARCHAR(10),

GERENTE INTEGER);

 

--SQLServer

ALTER TABLE EMPREGADO

ADD FOREIGN KEY (CARGO) REFERENCES CARGO (NUMERO) ON UPDATE CASCADE,

FOREIGN KEY (GERENTE) REFERENCES EMPREGADO (NUMERO);

 

--Postgre | MySQL | Firebird

ALTER TABLE EMPREGADO

ADD FOREIGN KEY (CARGO) REFERENCES CARGO (NUMERO) ON UPDATE CASCADE,

ADD FOREIGN KEY (GERENTE) REFERENCES EMPREGADO (NUMERO);

 

--Oracle

ALTER TABLE EMPREGADO

ADD FOREIGN KEY (CARGO) REFERENCES CARGO;

ALTER TABLE EMPREGADO

ADD FOREIGN KEY (GERENTE) REFERENCES EMPREGADO;

 

Fazer a alteração da tabela Empregrado:

 

--SQLServer | Postgre | Oracle | MySQL

ALTER TABLE EMPREGADO

DROP COLUMN NOME;

 

--Firebird

ALTER TABLE EMPREGADO

DROP NOME;

Alteração de Dados (Atualização)

UPDATE nome_tabela

            SET nome_coluna1=expr1 [, nome_coluna2=expr2 ...]

            [WHERE definição]

            [ORDER BY ...]

 

Dicas

Pode-se alterar linha;

Pode-se alterar listas;

Pode-se alterar subconsultas;

Pode-se alterar linhas com base em outra tabela.

 

Práticas

Atualizar as linhas de Empregado com os valores: Número, Primeironome, Sobrenome, Cargo, Departamento, Gerente, Salário, Comissão, Nascimento, Apelido; 100, Antônio, Palmeira, 1, 10, nulo, 1000, nulo, 01/07/80, Tonho; 101, José, Figueira, 1, 20, 100, 1500, nulo, 18/05/70, Zé; 102, Maria, Mangueira, 2, 10, 100, 2000, 3, 22/07/80, Ma; 103, João, Abacateiro, 3, 50, 100, 1500, 2, 03/03/71, Jo; 104, Carlos, Castanheiro, nulo, 60, 100, 2500, 3, 01/01/65, Ca; 107, Mônica, Videira, 1, 90, nulo, 1000, 2, 15/02/60, Mo; 124, Pedro, Laranjeira, 2, 80, 107, 1100, nulo, 30/07/83, Pedroca; 141, Tiago, Pessegueiro, 3, 80, 107, 2000, 2, 21/06/78, Ti; 142, Renata, Videira, 4, 80, 107, 1500, nulo, 02/12/72, Rê; 143, Gabriel, Macieira, nulo, 110, 107, 2000, 2, 11/11/81, Biel:

 

--SQLServer | Postgre | Oracle

UPDATE EMPREGADO SET

CARGO=1, GERENTE=NULL, SALARIO=1000, COMISSAO=NULL,

NASCIMENTO='01/07/80', APELIDO='TONHO' WHERE NUMERO=100;

UPDATE EMPREGADO SET

CARGO=1, GERENTE=100, SALARIO=1500, COMISSAO=NULL,

NASCIMENTO='18/05/70', APELIDO='ZÉ' WHERE NUMERO=101;

UPDATE EMPREGADO SET

CARGO=2, GERENTE=100, SALARIO=2000, COMISSAO=3,

NASCIMENTO='22/07/80', APELIDO='MA' WHERE NUMERO=102;

UPDATE EMPREGADO SET

CARGO=3, GERENTE=100, SALARIO=1500, COMISSAO=2,

NASCIMENTO='03/03/71', APELIDO='JO' WHERE NUMERO=103;

UPDATE EMPREGADO SET

CARGO=NULL, GERENTE=100, SALARIO=2500, COMISSAO=3,

NASCIMENTO='01/01/65', APELIDO='CA' WHERE NUMERO=104;

UPDATE EMPREGADO SET

CARGO=1, GERENTE=NULL, SALARIO=1000, COMISSAO=2,

NASCIMENTO='15/02/60', APELIDO='MO' WHERE NUMERO=107;

UPDATE EMPREGADO SET

CARGO=2, GERENTE=107, SALARIO=1100, COMISSAO=NULL,

NASCIMENTO='30/07/83', APELIDO='PEDROCA' WHERE NUMERO=124;

UPDATE EMPREGADO SET

CARGO=3, GERENTE=107, SALARIO=2000, COMISSAO=2,

NASCIMENTO='21/06/78', APELIDO='TI' WHERE NUMERO=141;

UPDATE EMPREGADO SET

CARGO=4, GERENTE=107, SALARIO=1500, COMISSAO=NULL,

NASCIMENTO='02/12/72', APELIDO='RE' WHERE NUMERO=142;

UPDATE EMPREGADO SET

CARGO=NULL, GERENTE=107, SALARIO=2000, COMISSAO=2,

NASCIMENTO='11/11/81', APELIDO='BIEL' WHERE NUMERO=143;

 

--MySQL

UPDATE EMPREGADO SET

CARGO=1, GERENTE=NULL, SALARIO=1000, COMISSAO=NULL,

NASCIMENTO='80/07/01', APELIDO='TONHO' WHERE NUMERO=100;

UPDATE EMPREGADO SET

CARGO=1, GERENTE=100, SALARIO=1500, COMISSAO=NULL,

NASCIMENTO='70/05/18', APELIDO='ZÉ' WHERE NUMERO=101;

UPDATE EMPREGADO SET

CARGO=2, GERENTE=100, SALARIO=2000, COMISSAO=3,

NASCIMENTO='80/07/22', APELIDO='MA' WHERE NUMERO=102;

UPDATE EMPREGADO SET

CARGO=3, GERENTE=100, SALARIO=1500, COMISSAO=2,

NASCIMENTO='71/03/03', APELIDO='JO' WHERE NUMERO=103;

UPDATE EMPREGADO SET

CARGO=NULL, GERENTE=100, SALARIO=2500, COMISSAO=3,

NASCIMENTO='65/01/01', APELIDO='CA' WHERE NUMERO=104;

UPDATE EMPREGADO SET

CARGO=1, GERENTE=NULL, SALARIO=1000, COMISSAO=2,

NASCIMENTO='60/02/15', APELIDO='MO' WHERE NUMERO=107;

UPDATE EMPREGADO SET

CARGO=2, GERENTE=107, SALARIO=1100, COMISSAO=NULL,

NASCIMENTO='83/07/30', APELIDO='PEDROCA' WHERE NUMERO=124;

UPDATE EMPREGADO SET

CARGO=3, GERENTE=107, SALARIO=2000, COMISSAO=2,

NASCIMENTO='78/06/21', APELIDO='TI' WHERE NUMERO=141;

UPDATE EMPREGADO SET

CARGO=4, GERENTE=107, SALARIO=1500, COMISSAO=NULL,

NASCIMENTO='72/12/02', APELIDO='RE' WHERE NUMERO=142;

UPDATE EMPREGADO SET

CARGO=NULL, GERENTE=107, SALARIO=2000, COMISSAO=2,

NASCIMENTO='81/11/11', APELIDO='BIEL' WHERE NUMERO=143;

 

--Firebird

UPDATE EMPREGADO SET

CARGO=1, GERENTE=NULL, SALARIO=1000, COMISSAO=NULL,

NASCIMENTO='07/01/80', APELIDO='TONHO' WHERE NUMERO=100;

UPDATE EMPREGADO SET

CARGO=1, GERENTE=100, SALARIO=1500, COMISSAO=NULL,

NASCIMENTO='05/18/70', APELIDO='ZÉ' WHERE NUMERO=101;

UPDATE EMPREGADO SET

CARGO=2, GERENTE=100, SALARIO=2000, COMISSAO=3,

NASCIMENTO='07/22/80', APELIDO='MA' WHERE NUMERO=102;

UPDATE EMPREGADO SET

CARGO=3, GERENTE=100, SALARIO=1500, COMISSAO=2,

NASCIMENTO='03/03/71', APELIDO='JO' WHERE NUMERO=103;

UPDATE EMPREGADO SET

CARGO=NULL, GERENTE=100, SALARIO=2500, COMISSAO=3,

NASCIMENTO='01/01/65', APELIDO='CA' WHERE NUMERO=104;

UPDATE EMPREGADO SET

CARGO=1, GERENTE=NULL, SALARIO=1000, COMISSAO=2,

NASCIMENTO='02/15/60', APELIDO='MO' WHERE NUMERO=107;

UPDATE EMPREGADO SET

CARGO=2, GERENTE=107, SALARIO=1100, COMISSAO=NULL,

NASCIMENTO='07/30/83', APELIDO='PEDROCA' WHERE NUMERO=124;

UPDATE EMPREGADO SET

CARGO=3, GERENTE=107, SALARIO=2000, COMISSAO=2,

NASCIMENTO='06/21/78', APELIDO='TI' WHERE NUMERO=141;

UPDATE EMPREGADO SET

CARGO=4, GERENTE=107, SALARIO=1500, COMISSAO=NULL,

NASCIMENTO='12/02/72', APELIDO='RE' WHERE NUMERO=142;

UPDATE EMPREGADO SET

CARGO=NULL, GERENTE=107, SALARIO=2000, COMISSAO=2,

NASCIMENTO='11/11/81', APELIDO='BIEL' WHERE NUMERO=143;

Algumas Funções

Permitem aumentar a capacidade de execução de consultas dos bancos de dados.

 

Sintaxe

SQLServer | Postgre | MySQL | Firebird

SUBSTRING(string texto, posicao_inicial, tamanho) - retorna uma string com o comprimento definido em "tamanho" extraida da string "texto", a partir da "posicao_inicial“;

Oracle: SUBSTR

 

SQLServer

CHARINDEX( string pesquisado, campo, posicao_inicial) – retorna a posição do string pesquisado dentro do campo informado.

Postgre: STRPOS

Oracle | MySQL: INSTR

Firebird: POSITION

 

Práticas

Fazendo uso das funções substring e charindex separar o conteúdo do campo Nome nos campos Primeironome e Sobrenome. Depois apagar o campo Nome ficando: Número, Primeironome, Sobrenome, Departamento; 100, Antônio, Palmeira, 10; 101, José, Figueira, 20; 102, Maria, Mangueira, 10; 103, João, Abacateiro, 50; 104, Carlos, Castanheiro, 60; 107, Mônica, Videira, 90; 124, Pedro, Laranjeira, 80; 141, Tiago, Pessegueiro, 80; 142, Renata, Videira, 80; 143, Gabriel, Macieira, 110:

 

--SQLServer

UPDATE EMPREGADO SET

PRIMEIRONOME = SUBSTRING(NOME,1,CHARINDEX(' ',NOME)-1),

SOBRENOME = SUBSTRING(NOME,CHARINDEX(' ',nome)+1,100);

 

--Postgre

UPDATE EMPREGADO SET

PRIMEIRONOME = SUBSTRING(NOME,1,STRPOS(NOME, ' ')-1),

SOBRENOME = SUBSTRING(NOME,STRPOS(NOME, ' ')+1,100);

 

--Oracle

UPDATE EMPREGADO SET

PRIMEIRONOME = SUBSTR(NOME,1,INSTR(NOME, ' ')-1),

SOBRENOME = SUBSTR(NOME,INSTR(NOME, ' ')+1,100);

 

--MySQL

UPDATE EMPREGADO SET

PRIMEIRONOME = SUBSTRING(NOME,1,INSTR(NOME, ' ')-1),

SOBRENOME = SUBSTRING(NOME,INSTR(NOME, ' ')+1,100);

 

--Firebird

UPDATE EMPREGADO SET

PRIMEIRONOME = SUBSTRING(NOME FROM 1 FOR POSITION(' ', NOME)-1),

SOBRENOME = SUBSTRING(NOME FROM POSITION(' ', NOME)+1 FOR 100);

 

Índices

Permite criar estruturas de indexação que auxiliam as estruturas de armazenamento aumentando a performance no acesso aos dados.

 

Sintaxe

CREATE [UNIQUE] INDEX nome_indice

       ON nome_tabela (index_col_name,...)

index_col_name:

       col_name [(length)] [ASC | DESC]

 

Trata-se de uma referência associada a uma chave, que tem a finalidade de otimizar a pesquisa, permitindo assim a localização rápida de um registro quando efetuada uma consulta. Trata-se de uma estrutura auxiliar associado a uma tabela, que possui ponteiros para os dados armazenados em colunas específicas. Fazendo uma analogia, o banco de dados usa o índice de maneira semelhante ao índice remissivo de um livro, verificando um determinado assunto no índice e depois localizando a sua posição.

 

Práticas

Criar índices por: Primeironome e Sobrenome; Sobrenome; Salário; Comissão; Cargo; Departamento; Apelido (Único):

 

CREATE INDEX EMPREGADOIX1 ON EMPREGADO (PRIMEIRONOME);

CREATE INDEX EMPREGADOIX2 ON EMPREGADO (SOBRENOME);

CREATE INDEX EMPREGADOIX3 ON EMPREGADO (SALARIO);

CREATE INDEX EMPREGADOIX4 ON EMPREGADO (COMISSAO);

CREATE INDEX EMPREGADOIX5 ON EMPREGADO (CARGO);

CREATE INDEX EMPREGADOIX6 ON EMPREGADO (DEPARTAMENTO);

CREATE UNIQUE INDEX EMPREGADOIX7 ON EMPREGADO (APELIDO);

Dicionário de Dados

Trata-se de uma coleção de metadados que contêm definições e representações de elementos de dados. Os Sistemas Gerenciadores de Bancos de Dados (SGBD's) fazem uso dos dicionários de dados, permitindo apenas consulta por parte dos usuários, e possuem as  seguintes informações: Definição dos elementos de dados; Perfis de usuários, papéis e privilégios; Descrição de objetos; Integridade de restrições; Informação de verificação; e Alocações de espaço.

 

Práticas

Visualizar a estrutura das tabelas Empregados e Departamentos:

 

--SQLServer

SELECT * FROM SYSOBJECTS O INNER JOIN SYSCOLUMNS C ON O.ID=C.ID WHERE O.NAME = 'DEPARTAMENTO';

 

--Postgre

SELECT

a.attnum,

a.attname AS field,

t.typname AS type,

a.attlen AS length,

a.atttypmod AS lengthvar,

a.attnotnull AS notnull,

c.relname AS name

FROM

pg_class c,

pg_attribute a,

pg_type t

WHERE

c.relname = 'departamento' and

a.attnum > 0

and a.attrelid = c.oid

and a.atttypid = t.oid

ORDER BY c.relname

 

--Oracle | MySQL

DESCRIBE DEPARTAMENTO;

Consulta de Dados

Comando que permite extrair os dados das estruturas de armazenamento como desejado pelo usuários.

 

Sintaxe

SELECT 

[ ALL | DISTINCT ] 

[ TOP n ] 

[ table_name. | table_alias. | view_name. ] 

| column_name [ [ AS ] column_alias ] 

| expression [ [ AS ] column_alias ] 

[ ..., n ] 

[ INTO new_table ] 

FROM table 

[ AS table_alias ] 

[ ( column_alias_1, [ column_alias_2, ..., n ] ) ] 

view_name [ AS table_alias ] 

[ INNER JOIN | LEFT [ OUTER ] JOIN | RIGHT [ OUTER ] JOIN table [ ON search_conditions ] 

[ WHERE search_conditions 

WHERE column_name =* column_name 

[ GROUP BY { group_by_expression, [ ..., n ] } ] 

[ HAVING search_conditions ] 

[ UNION query ] 

[ ORDER BY { order_by_expression [ ASC | DESC ] , [ ..., n ] } ]

 

Dicas

Operador de concatenação (||);

Em alguns BD se usa (+) para concatenação;

Concatena colunas ou strings de caracteres a outras colunas.

 

Práticas

Visualizar os campos: Número e Nome (Primeironome || ‘ ‘ || Sobrenome):

 

--SQLServer

SELECT NUMERO, PRIMEIRONOME + ' ' + SOBRENOME AS NOME FROM EMPREGADO;

 

--Postgre | Oracle | Firebird

SELECT NUMERO, PRIMEIRONOME || ' ' || SOBRENOME AS NOME FROM EMPREGADO;

 

--MySQL

SELECT NUMERO, CONCAT(PRIMEIRONOME, ' ', SOBRENOME) AS NOME FROM EMPREGADO;

 

Visualizar os registros dos empregados do departamento 20 ou 80 dos campos: Número; Primeironome; Sobrenome; e Departamento:

 

SELECT NUMERO, PRIMEIRONOME, SOBRENOME, DEPARTAMENTO

FROM EMPREGADO

WHERE DEPARTAMENTO = 20 OR DEPARTAMENTO = 80;

 

Visualizar os registros dos empregados juntamente com o nome do departamento: Número (Empregados); Primeironome (Empregados); Sobrenome (Empregados); Departamento (Empregados); e Nome (Departamentos).

 

SELECT E.NUMERO, E.PRIMEIRONOME, E.SOBRENOME, E.DEPARTAMENTO,

D.NOME

FROM EMPREGADO E INNER JOIN DEPARTAMENTO D

ON E.DEPARTAMENTO = D.NUMERO;

Precedência

Use parênteses (...) para forçar a ordem em que as expressões serão avaliadas.

Comparação

Se um ou ambos os argumentos são NULL, o resultado da comparação é NULL; Se ambos os argumentos em uma comparação são strings, eles são comparados como strings; Se ambos os argumentos são inteiros, eles são comparados como inteiros; Valores hexadecimais são tratados como strings binárias se não comparadas a um número; Se um dos argumentos é uma coluna TIMESTAMP ou DATETIME e o outro argumento é uma constante, a constante é convertida para um timestamp antes da comparação ser realizada; Por padrão, comparações de string são feita de modo independente do caso, usando o conjunto de caracteres atual (ISO-8859-1 Latin1 por padrão); Comparação strings com distinção de letras maiúsculas e minúsculas pode ser realizado com qualquer dos operadores, excerto o LIKE; Espaços em branco no fim da string, espaços, tabs e quebra de linha serão ignorados.

Projeção

Consiste na escolha das colunas que serão exibidas.

Seleção

Consiste na escolha das linhas que serão exibidas.

Junção

Consiste em relacionar dados de duas ou mais tabelas ou mesmo relacionar uma tabela com ela mesma podendo ser uma correlação ou autojunção.

Consulta Básica

Consiste em usar os argumentos básicos do comando Select que são o SELECT e FROM.

 

Dicas

As instruções SQL não fazem distinção entre maiúsculas e minúsculas;

As instruções SQL podem estar em uma ou mais linhas;

As palavras-chave não podem ser abreviadas ou quebradas;

Normalmente as cláusulas são colocadas em linhas separadas;

Os recuos são utilizados para dar mais legibilidade.

 

Práticas

Visualizar todos os registros e todos os campos da tabela departamentos.

 

SELECT * FROM DEPARTAMENTO;

Apelido (Alias)

Consiste em atribuir outro nome a um metadado mas que não irá afetar a estrutura física é apenas uma alteração lógica.

 

Práticas

Visualizar todos os registros nos campos Número, Nome (Primeironome + ‘ ‘ + Sobrenome) e após a consulta ser realizada observar o nome do campo para a expressão Nome. Finalize repetindo a consulta colocando o apelido Nome do Funcionário.

 

--SQLServer

SELECT NUMERO, PRIMEIRONOME + ' ' + SOBRENOME FROM EMPREGADO;

SELECT NUMERO, PRIMEIRONOME + ' ' + SOBRENOME AS NOME FROM EMPREGADO;

 

--Postgre | Oracle | Firebird

SELECT NUMERO, PRIMEIRONOME || ' ' || SOBRENOME FROM EMPREGADO;

SELECT NUMERO, PRIMEIRONOME || ' ' || SOBRENOME AS NOME FROM EMPREGADO;

 

--MySQL

SELECT NUMERO, CONCAT(PRIMEIRONOME, ' ', SOBRENOME) FROM EMPREGADO;

SELECT NUMERO, CONCAT(PRIMEIRONOME, ' ', SOBRENOME) AS NOME FROM EMPREGADO;

Campos Calculados

São campos lógicos baseados em operações aritméticas fazendo uso dos operadores e funções.

 

Operadores aritméticos:

· Adicionar:+

· Subtrair: -

· Multiplicar: *

· Dividir: / “retorna o quociente”

 

Funções matemáticas:

· Potenciação: POWER(número, potência)

· Radiciação: SQRT(número)

· Divisão: % | MOD “retorna o resto”

 

Precedência de Operadores:

· A multiplicação e a divisão têm prioridade sobre a adição e subtração;

· Os operadores com a mesma prioridade são avaliados da esquerda para a direita;

· Os parênteses são usados para forçar a avaliação priorizada e para esclarecer as instruções.

 

Práticas

Visualizar o Sobrenome, o salário, e o líquido dos empregados, considerando que todos possuem um desconto de 10% de INSS.

 

SELECT SOBRENOME, SALARIO, SALARIO - (SALARIO*0.10) AS LIQUIDO

FROM EMPREGADO;

Valores Nulos

São campos que não possuem nenhum tipo de dado o que chamamos de valor nulo.

 

Dicas

Nulo é uma valor que não está disponível;

Nulo é um valor desconhecido;

Nulo não é a mesma coisa que zero;

Nulo não é a mesma coisa que espaço em branco;

Para Oracle usar NVL(campo, valor);

Para MySQL usar IFNULL(campo, valor);

Para SQLServer usar ISNULL(campo, valor);

Para Postgre | Firebird usar COALESCE(campo,0).

 

Práticas

Visualizar o Sobrenome e o Salário Bruto (Salário + Comissão). Obs: A Comissão representa um percentual aplicado sobre o salário.

 

SELECT SOBRENOME, SALARIO + (SALARIO * COMISSAO / 100) AS BRUTO

FROM EMPREGADO;

 

Visualizar o Sobrenome e o Salário Bruto (Salário + Comissão). Obs: Quando a Comissão for nula deve-se substituir por zero.

 

--SQLServer

SELECT SOBRENOME, SALARIO + (SALARIO * ISNULL(COMISSAO,0) / 100) AS BRUTO FROM EMPREGADO;

 

--Postgre | Firebird

SELECT SOBRENOME, SALARIO + (SALARIO * COALESCE(COMISSAO,0) / 100) AS BRUTO FROM EMPREGADO;

 

--Oracle

SELECT SOBRENOME, SALARIO + (SALARIO * NVL(COMISSAO,0) / 100) AS BRUTO FROM EMPREGADO;

 

--MySQL

SELECT SOBRENOME, SALARIO + (SALARIO * IFNULL(COMISSAO,0) / 100) AS BRUTO FROM EMPREGADO;

Duplicidade

Consiste em eliminar na consulta, isto é, apenas na visualização dos dados, as duplicidades ou valores repetidos.

 

Dicas

A exibição default das consultas é de todas as linhas, incluindo linhas duplicadas.

 

Práticas

Visualizar o Sobrenome dos Empregados.

 

SELECT SOBRENOME FROM EMPREGADO;

 

Visualizar o Sobrenome dos Empregados sem duplicidade.

 

SELECT DISTINCT SOBRENOME FROM EMPREGADO;

Condicionamento

São seleções de dados resultantes de condicionamentos lógicos realizados pela cláusula WHERE.

 

Operadores de Comparação:

· Igual a: =

· Maior que: >

· Maior que ou igual a: >=

· Menor que: <

· Menor que ou igual a: <=

· Diferente de: <>

 

Operadores Lógicos:

· Verdadeiro para ambas expressões verdadeiras: AND

· Verdadeiro bastando uma das expressões verdadeiras: OR

· Inverso da expressão: NOT

 

Regras de Precedência

· Operadores Aritméticos;

· Operador de Concatenação;

· Condições de Comparação;

· IS [NOT] NULL, [NOT] LIKE, [NOT] IN;

· [NOT] BETWEEN;

· Condição lógica NOT;

· Condição lógica AND;

· Condição lógica OR.

 

Dicas

As strings de caracteres e valores de data aparecem entre aspas simples;

Os valores de caractere fazem distinção entre maiúsculas e minúsculas;

Os valores de data fazem distinção entre formatos;

O formato de data default é DD-MON-YY.

 

Práticas

Visualizar o Número e Sobrenome de todos os empregados do departamento 90.

 

SELECT NUMERO, SOBRENOME

FROM EMPREGADO

WHERE DEPARTAMENTO = 90;

 

Visualizar o Número, Sobrenome e Nascimento de todos os empregados que nasceram em 22/07/80.

 

--SQLServer | postgre | Oracle

SELECT NUMERO, SOBRENOME, NASCIMENTO FROM EMPREGADO WHERE NASCIMENTO = '22/07/80';

 

--MySQL

SELECT NUMERO, SOBRENOME, NASCIMENTO FROM EMPREGADO WHERE NASCIMENTO = '80/07/22';

 

--Firebird

SELECT NUMERO, SOBRENOME, NASCIMENTO FROM EMPREGADO WHERE NASCIMENTO = '07/22/80';

 

Visualizar o Número, Sobrenome e Nascimento de todos os empregados que nasceram entre (inclusive) 01/01/70 e 31/12/80.

 

--SQLServer | postgre | Oracle

SELECT NUMERO, SOBRENOME, NASCIMENTO FROM EMPREGADO WHERE NASCIMENTO

BETWEEN '01/01/70' AND '31/12/80';

 

--MySQL

SELECT NUMERO, SOBRENOME, NASCIMENTO FROM EMPREGADO WHERE NASCIMENTO

BETWEEN '70/01/01' AND '80/12/31';

 

--Firebird

SELECT NUMERO, SOBRENOME, NASCIMENTO FROM EMPREGADO WHERE NASCIMENTO

BETWEEN '01/01/70' AND '12/31/80';

 

Visualizar o Número, Sobrenome e Departamento de todos os empregados que pertencem aos departamentos: 10, 20, 50, 60, 80 ou 90.

 

SELECT NUMERO, SOBRENOME, DEPARTAMENTO

FROM EMPREGADO

WHERE DEPARTAMENTO IN (10, 20, 50, 60, 80, 90);

 

Visualizar o Número e Sobrenome de todos os empregados que possuem ‘eira’ no sobrenome.

 

SELECT NUMERO, SOBRENOME

FROM EMPREGADO

WHERE SOBRENOME LIKE ‘%EIRA%’;

 

Visualizar o Número e Sobrenome de todos os empregados que não possuem comissão.

 

SELECT NUMERO, SOBRENOME

FROM EMPREGADO

WHERE COMISSAO IS NULL;

 

Visualizar o Número, Sobrenome e Salário de todos os empregados que possuem salário entre (inclusive) 1500 e 2000, dos departamentos 20, 50 e 80.

 

SELECT NUMERO, SOBRENOME, SALARIO, DEPARTAMENTO

FROM EMPREGADO

WHERE SALARIO BETWEEN 1500 AND 2000

AND DEPARTAMENTO IN (20, 50, 80);

Ordenação

Permite realizar classificação, isto é, uma ordenação dos dados que serão exibidos.

 

Sintaxe

[ORDER BY {coluna, expr} [ASC|DESC]];

 

Dicas

É possível utilizar o alias na cláusula de ordenação;

É possível utilizar a posição da coluna na ordenação;

Pode-se classificar por colunas que não estejam no select;

Pode-se classificar por N colunas.

 

Práticas

Visualizar o Sobrenome e o Salário dos Empregados ordenados por Salário em ordem decrescente.

 

SELECT SOBRENOME, SALARIO

FROM EMPREGADO

ORDER BY SALARIO DESC;

Aninhamento

São comandos ou funções que se apresentam uma dentro da outra.

 

Dicas

Funções de uma única linha podem ser aninhadas em qualquer nível;

As funções aninhadas são avaliadas do nível mais interno para o mais externo.

 

Práticas

Exibir o tamanho do Apelido concatenado com o Sobrenome com os brancos e depois sem os brancos; Exibir o tamanho do Apelido concatenado com o Sobrenome com os brancos

 

--SQLServer

SELECT LEN(APELIDO + ' ' + SOBRENOME) FROM EMPREGADO;

 

--Postgre | Oracle

SELECT LENGTH(APELIDO || ' ' || SOBRENOME) FROM EMPREGADO;

 

--MySQL

SELECT LENGTH(CONCAT(APELIDO, ' ', SOBRENOME)) FROM EMPREGADO;

 

--Firebird

SELECT CHAR_LENGTH(APELIDO || ' ' || SOBRENOME) FROM EMPREGADO;

 

Exibir o tamanho do Apelido concatenado com o Sobrenome sem os brancos.

 

--SQLServer

SELECT LEN(RTRIM(APELIDO) + ' ' + SOBRENOME) FROM EMPREGADO;

 

--Postgre | Oracle

SELECT LENGTH(RTRIM(APELIDO) || ' ' || SOBRENOME) FROM EMPREGADO;

 

--MySQL

SELECT LENGTH(CONCAT(RTRIM(APELIDO), ' ', SOBRENOME)) FROM EMPREGADO;

 

--Firebird

SELECT CHAR_LENGTH(TRIM(APELIDO) || ' ' || SOBRENOME) FROM EMPREGADO;

Funções

Funções são recursos avançados do SQL e podem ser utilizadas para efetuar o seguinte: Executar cálculos; Modificar itens de dados individuais; Manipular a saída para grupos de linhas; Formatar datas e números para exibição; Converter tipos de dados de coluna; Algumas funções utilizam argumentos; Sempre retornam um valor.

 

Tipos

Funções de uma Única Linha - operam somente linhas únicas e retornam um resultado por linha;

Funções de Várias Linhas - podem manipular grupos de linhas para fornecer um resultado e são mais conhecidas como funções de grupo.

 

Dicas

Servem para manipular itens de dados;

Aceitam um ou mais argumentos;

Retornam um valor para cada linha retornada;

São do tipo: Caractere, Numéricas, Data, Conversão e Gerais;

Podem ser usadas em cláusulas: SELECT, WHERE e ORDER BY.

 

Funções Caractere

LOWER – converte para minúsculas;

UPPER – converte para maiúsculas;

SUBSTRING – retorna o substring de uma string;

LENGTH – retorna o número de caracteres;

CHARINDEX – retorna a posição de um string;

REPLACE - substitui uma string por outra string;

RTRIM | LTRIM | TRIM – retira brancos.

 

Práticas

Exibir todos os Primeironomes em minúsculas.

 

SELECT LOWER(PRIMEIRONOME) AS PRIMEIRONOME FROM EMPREGADO;

 

Exibir todos os Sobrenomes em maiúsculas.

 

SELECT UPPER(SOBRENOME) AS PRIMEIRONOME FROM EMPREGADO;

 

Exibir os 3 primeiros caracteres do Sobrenome.

 

--SQLServer | Postgre | MySQL

SELECT SUBSTRING(SOBRENOME,1,3) FROM EMPREGADO;

 

--Oracle

SELECT SUBSTR(SOBRENOME,1,3) FROM EMPREGADO;

 

--Firebird

SELECT SUBSTRING(SOBRENOME FROM 1 FOR 3) FROM EMPREGADO;

 

Exibir o tamanho do Sobrenome.

 

--SQLServer

SELECT LEN(SOBRENOME) FROM EMPREGADO;

 

--Postgre | Oracle | MySQL

SELECT LENGTH(SOBRENOME) FROM EMPREGADO;

 

--Firebird

SELECT CHAR_LENGTH(SOBRENOME) FROM EMPREGADO;

 

Exibir a posição no ‘(‘ no nome do departamento.

 

--SQLServer

SELECT CHARINDEX('(', NOME) FROM DEPARTAMENTO;

 

--Postgre

SELECT STRPOS(NOME, '(') FROM DEPARTAMENTO;

 

--Oracle | MySQL

SELECT INSTR(NOME, '(') FROM DEPARTAMENTO;

 

--Firebird

SELECT POSITION('(' IN NOME) FROM DEPARTAMENTO;

Funções Numérica

ROUND – arredonda a coluna;

TRUNC – trunca a coluna;

 

Práticas

Exibir o Fator.

 

SELECT FATOR FROM DEPARTAMENTO;

 

Exibir o Fator arredondando para 1 decimal.

 

SELECT ROUND(FATOR,1) FROM DEPARTAMENTO;

 

Exibir o Fator truncando para 1 decimal.

 

--SQLServer

--SELECT ROUND(FATOR, 1, 2) FROM DEPARTAMENTO;

 

--Postgre | Oracle | Firebird

SELECT TRUNC(FATOR, 1) FROM DEPARTAMENTO;

 

--MySQL

SELECT TRUNCATE(FATOR, 1) FROM DEPARTAMENTO;

MOD | %

Retorna o resto da divisão.

 

Dicas

Para exibir dados que não estão inseridos em tabelas do banco de dados pode-se fazer uso de uma tabela fictícia usada para exibir resultados chamada de DUAL.

 

Práticas

Visualizar o quociente da divisão de 5 por 2.

 

--SQLServer | Firebird

SELECT 5 / 2 FROM TMP;

 

--Postgre | MySQL

SELECT 5 / 2;

 

--Oracle

SELECT 5 / 2 FROM DUAL;

 

Visualizar o resto da divisão de 5 por 2.

 

--SQLServer

SELECT 5 % 2 FROM TMP;

 

--Postgre | MySQL

SELECT 5 MOD 2;

 

--Oracle

SELECT MOD (5, 2) FROM DUAL;

 

--Firebird

SELECT MOD(5, 2) FROM TMP;

Funções Data

Retornam dados no formato de datas.

 

Sintaxe

GETDATE | SYSDATE

 

Dicas

Os Bancos de Dados (BD) armazenam as datas em formato de número inteiro: século, ano, mês, dia, horas, minutos e segundos;

O formato de exibição default depende do BD, mas no geral é utilizado a exibição DD-MMM-AA;

Pode-se adicionar ou subtrair (aritmética com datas)

Operação; Resultado; Descrição

Data+Número; Data; Adiciona dias a datas

Data-Número; Data; Subtrai dias a datas

Data-Data; Dias; Subtrai uma data de outra

Data+Número/24; Data; Adiciona hora a datas

 

Práticas

Exiba e verifique o retorno: Exibir a data corrente; Exibir Data+Número; Exibir Data-Número; Exibir Data-Data; Exibir Data+Número/24.

 

--SQLServer

SELECT GETDATE() FROM TMP;

SELECT GETDATE() + 1 FROM TMP;

SELECT GETDATE() - 1 FROM TMP;

SELECT GETDATE() - '13/11/67' FROM TMP;

SELECT GETDATE() + 10/24 FROM TMP;

SELECT YEAR(GETDATE()) FROM TMP;

SELECT MONTH(GETDATE()) FROM TMP;

SELECT DAY(GETDATE()) FROM TMP;

 

--Postgre

SELECT CURRENT_TIMESTAMP;

SELECT CURRENT_TIMESTAMP + INTERVAL '1DAY';

SELECT CURRENT_TIMESTAMP - INTERVAL '1DAY';

SELECT CURRENT_TIMESTAMP - '13/11/67';

SELECT CURRENT_TIMESTAMP + INTERVAL '5HOURS';

SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP);

SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP);

SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP);

 

--Oracle

SELECT SYSDATE FROM DUAL;

SELECT SYSDATE + 1 FROM DUAL;

SELECT SYSDATE - 1 FROM DUAL;

SELECT SYSDATE - '13/11/67' FROM DUAL;

SELECT SYSDATE + 10/24 FROM DUAL;

SELECT YEAR(SYSDATE) FROM DUAL;

SELECT MONTH(SYSDATE) FROM DUAL;

SELECT DAY(SYSDATE) FROM DUAL;

 

--MySQL

SELECT CURRENT_TIMESTAMP;

SELECT CURRENT_TIMESTAMP + 1;

SELECT CURRENT_TIMESTAMP - 1;

SELECT CURRENT_TIMESTAMP - '13/11/67';

SELECT CURRENT_TIMESTAMP + 10/24;

SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP);

SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP);

SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP);

 

--Firebird

SELECT CURRENT_TIMESTAMP FROM TMP;

SELECT CURRENT_TIMESTAMP + 1 FROM TMP;

SELECT CURRENT_TIMESTAMP - 1 FROM TMP;

SELECT CURRENT_TIMESTAMP + 10/24 FROM TMP;

SELECT EXTRACT(YEAR FROM CURRENT_TIMESTAMP) FROM TMP;

SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP) FROM TMP;

SELECT EXTRACT(DAY FROM CURRENT_TIMESTAMP) FROM TMP;

Função Conversão

Conversão de dados entre diversos tipos.

 

Sintaxe

CAST

 

Práticas

Exibir a conversão do salário para string.

 

SELECT CAST(SALARIO AS CHAR(10)) FROM EMPREGADO;

Função de Busca e Troca

Recupera e altera dados.

 

Sintaxe

REPLACE

 

Práticas

Trocar no nome de departamento: Admin para Adm; Exec para Exe; e Contab para Con.

 

SELECT

REPLACE(NOME, '(ADMIN)', '(ADM)'),

REPLACE(NOME, '(EXEC)', '(EXE)'),

REPLACE(NOME, '(CONTAB)', '(CON)')

FROM DEPARTAMENTO;

Função de Condicionamento

Condiciona a exibição dos dados permitindo o uso de expressões condicionais.

 

Sintaxe

CASE expr WHEN cond THEN retorno

[WHEN condN THEN retornoN]

[ELSE retorno]

END

 

Práticas

Visualizar Número, Sobrenome, Salário e Departamento dos empregados;

Visualizar Número, Sobrenome, Salário e Departamento dos empregados aplicando 10% de aumento nos salários dos depart. 10 e 20, 15% dos depart. 80 e 90, e 5% nos demais.

 

SELECT NUMERO, SOBRENOME,

    CASE

    WHEN DEPARTAMENTO IN (10,20) THEN SALARIO + (SALARIO*0.10) 

    WHEN DEPARTAMENTO IN (80,90) THEN SALARIO + (SALARIO*0.15) 

    ELSE SALARIO + (SALARIO*0.5)

END AS SALARIO, 

    DEPARTAMENTO

        FROM EMPREGADO;

Produto Cartesiano

Um produto cartesiano será formado quando uma condição de junção for omitida e como consequência todas as linhas da primeira tabela serão unidas a todas a linhas da segunda tabela.

 

Práticas

Visualizar Número, Sobrenome e Nome de Departamento dos empregados não colocando a junção das tabelas e observe o resultado.

 

--Todos

SELECT E.NUMERO, E.SOBRENOME, D.NOME FROM EMPREGADO E, DEPARTAMENTO D;

 

--SQLServer | Postgre | Oracle | MySQL

SELECT E.NUMERO, E.SOBRENOME, D.NOME FROM EMPREGADO E CROSS JOIN DEPARTAMENTO D;

Junção

Quando forem necessários dados de mais de uma tabela no banco de dados, será usada uma condição de junção. As linhas de uma tabela podem ser unidas a linhas de outra tabela de acordo com os valores comuns existentes nas colunas correspondentes, ou seja, em geral colunas de chave primária e chave estrangeira.

 

Tipos de Junção

· Eqüijunção

· Não-eqüijunção

· Junção externa

· Autojunção

 

Dicas

Quando criar uma instrução SELECT que una tabelas, coloque o nome da tabela antes do nome da coluna para fins de clareza e para melhorar o acesso ao banco de dados;

Caso apareça o mesmo nome de coluna em mais de uma tabela, o nome da coluna deve vir antecedido do nome da tabela (AMBÍGUIDADE);

Para unir N tabelas, é necessário um mínimo de N-1 condições de junção. Por exemplo, para unir 4 tabelas, é necessário um mínimo de 3 junções. Essa regra pode não se aplicar se sua tabela tiver uma chave primária concatenada. Nesse caso, será necessário mais de uma coluna para identificar exclusivamente cada linha;

Para melhorar a sintaxe deve-se apelidar as tabelas.

Eqüijunção

Junção por igualdade.

 

Dicas

Equijunção também são chamadas de junções simples ou internas;

Observe que nas junções simples ou internas se não existe uma relação a tupla não é gerada.

 

Tupla: Cada linha formada por uma lista ordenada de colunas representa um registro. Os registros não precisam conter informações em todas as colunas, podendo assumir valores nulos quando assim se fizer necessário.

 

Práticas

Exibir o Sobrenome do empregado e o Nome do departamento de um empregado.

 

SELECT E.SOBRENOME, D.NOME

FROM EMPREGADO E

INNER JOIN DEPARTAMENTO D

ON E.DEPARTAMENTO = D.NUMERO;

 

Determine o nome do cargo e do departamento de um empregado.

 

SELECT E.SOBRENOME, C.NOME, D.NOME

FROM EMPREGADO E 

INNER JOIN CARGO C

ON E.CARGO = C.NUMERO

INNER JOIN DEPARTAMENTO D

ON E.DEPARTAMENTO = D.NUMERO;

Não-eqüijunção

São junções que não usam condições de igualdade.

 

Práticas

Exibir o Número, Sobrenome, nome do cargos de cada empregados que podem ser galgados na empresa.

 

SELECT E.NUMERO, E.SOBRENOME, C.NOME

FROM EMPREGADO E

INNER JOIN CARGO C

ON C.NUMERO > E.CARGO;

Junção Externa

Quando se deseja exibir dados que não possuem uma relação comum entre tabelas.

 

Dicas

Em banco como Oracle pode-se utilizar a extensão (+) e em SQL Server (*);

Só pode ser utilizado de um lado da expressão;

Não se pode utilizar o operador IN e nem o OR.

 

Práticas

Exibir o Número, Sobrenome, Apelido, nome do cargo de todos os empregados, mesmo daqueles que ainda não possuem cargos atribuídos.

 

SELECT E.NUMERO, E.SOBRENOME, E.APELIDO, C.NOME

FROM EMPREGADO E

RIGHT JOIN CARGO C

ON C.NUMERO = E.CARGO;

Autojunção

Quando se une uma tabela a ela mesma.

 

Práticas

Exibir o Número, Primeironome dos empregados e o Número e o Primeironome dos seus respectivos gerentes.

 

SELECT E.NUMERO, E.PRIMEIRONOME, G.NUMERO, G.PRIMEIRONOME

FROM EMPREGADO E LEFT JOIN EMPREGADO G

ON E.GERENTE = G.NUMERO;

Funções de Grupo

Operam um conjunto de linhas, as funções de grupo operam em conjuntos de linhas para fornecer um resultado por grupo. Esses conjuntos podem ser a tabela inteira ou dividida em grupos.

 

Dicas

Funções de grupo são mais conhecidas como funções agregadas: AVG, COUNT, MAX, MIN e SUM;

ALL faz com que a função considere todos os valores, inclusive as duplicidades;

DISTINCT faz com que a função considere somente valores não duplicados;

Todas as funções de grupos ignoram valores nulos, para garantir a aplicabilidade para todos os registros use (*).

 

Práticas

Exibir a média de salários pago pelo empregador.

 

SELECT AVG(SALARIO) FROM EMPREGADO;

 

Exibir o somatório dos salários.

 

SELECT SUM(SALARIO) FROM EMPREGADO;

 

Exibir a quantidade de empregados.

 

SELECT COUNT(SALARIO) FROM EMPREGADO;

 

Exibir o maior salário.

 

SELECT MAX(SALARIO) FROM EMPREGADO;

 

Exibir o menor salário.

 

SELECT MIN(SALARIO) FROM EMPREGADO;

Grupos de Dados

Usa-se o GROUP BY para dividir as linhas de uma tabela em grupos.

 

Práticas

Exibir a média de salários pagos por Departamento.

 

SELECT D.NOME, AVG(E.SALARIO) AS MEDIAS

FROM DEPARTAMENTO D INNER JOIN EMPREGADO E

ON D.NUMERO = E.DEPARTAMENTO

GROUP BY D.NOME;

 

Exibir o somatório dos salários pagos por Cargo.

 

SELECT C.NOME, SUM(E.SALARIO) AS SOMATORIO

FROM CARGO C INNER JOIN EMPREGADO E

ON C.NUMERO = E.CARGO

GROUP BY C.NOME;

 

Exibir a quantidade de empregados por gestor.

 

SELECT G.SOBRENOME, COUNT(E.NUMERO) AS QTDE

FROM EMPREGADO G INNER JOIN EMPREGADO E

ON G.NUMERO = E.GERENTE

GROUP BY G.SOBRENOME;

 

Exibir o maior salário por gestor.

 

SELECT G.SOBRENOME, MAX(E.SALARIO) AS MAIORSALARIO

FROM EMPREGADO G INNER JOIN EMPREGADO E

ON G.NUMERO = E.GERENTE

GROUP BY G.SOBRENOME;

 

Exibir o menor salário por gestor.

 

SELECT G.SOBRENOME, MIN(E.SALARIO) AS MENORSALARIO

FROM EMPREGADO G INNER JOIN EMPREGADO E

ON G.NUMERO = E.GERENTE

GROUP BY G.SOBRENOME;

Grupo de Dados com Filtro

Usa-se o HAVING para filtrar as linhas de uma tabela em grupos.

 

Práticas

Exibir a média de salários pagos por Departamento cujo a média seja maior ou igual a 2000.

 

SELECT D.NOME, AVG(E.SALARIO) AS MEDIAS

FROM DEPARTAMENTO D INNER JOIN EMPREGADO E

ON D.NUMERO = E.DEPARTAMENTO

GROUP BY D.NOME

HAVING AVG(E.SALARIO) >= 2000;

 

Exibir o somatório dos salários pagos por Cargo cujo o somatório seja menor que 1500.

 

SELECT D.NOME, SUM(E.SALARIO) AS MEDIAS

FROM DEPARTAMENTO D INNER JOIN EMPREGADO E

ON D.NUMERO = E.DEPARTAMENTO

GROUP BY D.NOME

HAVING AVG(E.SALARIO) < 1500;

ROLLUP

Usa-se o ROLLUP para produzir linhas superagregadas pela referência cruzada de colunas. O agrupamento ROLLUP produz um conjunto de resultados que contém as linhas agrupadas normais e os valores de subtotais.

 

Práticas

Exibir o somatório de Salário agrupado por Departamento e Cargo dos empregados com Número de Departamento menor que 60, exibindo os subtotais de Departamento e Cargo.

 

--Todos

SELECT CAST(DEPARTAMENTO AS CHAR), CAST(CARGO AS CHAR), SUM(SALARIO)

FROM EMPREGADO

WHERE DEPARTAMENTO < 60

GROUP BY DEPARTAMENTO, CARGO

UNION

SELECT '', 'TOTAL', COALESCE(SUM(SALARIO), 0)

FROM EMPREGADO;

 

--SQLServer | Oracle

SELECT DEPARTAMENTO, CARGO, SUM(SALARIO)

FROM EMPREGADO

WHERE DEPARTAMENTO < 60

GROUP BY ROLLUP (DEPARTAMENTO, CARGO);

 

--MySQL

SELECT DEPARTAMENTO, CARGO, SUM(SALARIO)

FROM EMPREGADO

WHERE DEPARTAMENTO < 60

GROUP BY DEPARTAMENTO, CARGO WITH ROLLUP;

CUBE

Usa-se o CUBE para produzir um conjunto de resultados que contém as linhas de ROLLUP e as linhas de tabulação cruzada. Pode-se usá-lo para produzir valores de tabulação cruzada com uma instrução SELECT simples.

 

Práticas

Exibir o somatório de Salário agrupado por Departamento e Cargo dos empregados com Número de Departamento menor que 60, exibindo a referência cruzada de Departamento e Cargo.

 

--SQLServer | Oracle

SELECT DEPARTAMENTO, CARGO, SUM(SALARIO)

FROM EMPREGADO

WHERE DEPARTAMENTO < 60

GROUP BY CUBE (DEPARTAMENTO, CARGO);

Ordenação de Dados

Usa-se o ORDER BY para classificar as linhas de uma seleção.

 

Práticas

Exibir a média de salários pagos por Departamento em ordem decrescente.

 

SELECT D.NOME, AVG(E.SALARIO) AS MEDIAS

FROM DEPARTAMENTO D INNER JOIN EMPREGADO E

ON D.NUMERO = E.DEPARTAMENTO

GROUP BY D.NOME

ORDER BY AVG(E.SALARIO) DESC;

 

Exibir o somatório dos salários pagos por Cargo em ordem ascendente.

 

SELECT C.NOME, SUM(E.SALARIO) AS MEDIAS

FROM CARGO C INNER JOIN EMPREGADO E

ON C.NUMERO = E.CARGO

GROUP BY C.NOME

ORDER BY 1 ASC;

 

SELECT D.NOME, AVG(E.SALARIO) AS MEDIAS

FROM DEPARTAMENTO D INNER JOIN EMPREGADO E

ON D.NUMERO = E.DEPARTAMENTO

GROUP BY D.NOME

ORDER BY AVG(E.SALARIO) DESC;

 

SELECT C.NOME, SUM(E.SALARIO) AS MEDIAS

FROM CARGO C INNER JOIN EMPREGADO E

ON C.NUMERO = E.CARGO

GROUP BY C.NOME

ORDER BY 1 ASC;

Aninhamento de Grupo de Dados

As funções de grupo podem ser aninhadas até uma profundidade de dois.

 

Práticas

Exibir o salário médio máximo pago por Departamento.

 

--Oracle

SELECT MAX(AVG(E.SALARIO)) AS MEDIAS

FROM EMPREGADO E

GROUP BY E.DEPARTAMENTO;

Subconsulta

Consiste em executar uma consulta (externa) a partir de outra consulta (interna).

 

Dicas

Existem operadores que retornam apenas uma linha (=, <>, >, >=, <, <=) e o que retorna várias linhas (IN);

Coloque subconsultas entre parênteses;

A quantidade de níveis de subconsultas dependem do banco de dados.

 

Práticas

Quais os empregados possuem salário maior que o de Pedroca?

 

SELECT APELIDO, SALARIO

FROM EMPREGADO

WHERE SALARIO > 

(SELECT SALARIO FROM EMPREGADO WHERE APELIDO='PEDROCA');

 

Qual o nome (Primeironome + Sobrenome) dos empregados possuem o menor salário?

 

--SQLSever

SELECT PRIMEIRONOME + ' ' + SOBRENOME AS NOME, SALARIO FROM EMPREGADO

WHERE SALARIO = (SELECT MIN(SALARIO) FROM EMPREGADO);

 

--Postgre | Oracle | Firebird

SELECT PRIMEIRONOME || ' ' || SOBRENOME AS NOME, SALARIO FROM EMPREGADO

WHERE SALARIO = (SELECT MIN(SALARIO) FROM EMPREGADO);

 

--MySQL

SELECT CONCAT(PRIMEIRONOME, ' ', SOBRENOME) AS NOME, SALARIO FROM EMPREGADO

WHERE SALARIO = (SELECT MIN(SALARIO) FROM EMPREGADO);

 

Exibir o menor Salário por Departamento onde o menor Salário seja o maior que o menor Salário do Departamento de código 50.

 

SELECT D.NOME, MIN(E.SALARIO)

FROM EMPREGADO E

INNER JOIN DEPARTAMENTO D

ON E.DEPARTAMENTO = D.NUMERO

GROUP BY D.NOME

HAVING MIN(E.SALARIO) > 

(SELECT MIN(SALARIO) FROM EMPREGADO WHERE DEPARTAMENTO = 50);

 

O que há de errado com a instrução abaixo? A subconsulta retornou mais de 1 valor. Isso não é permitido quando a subconsulta segue um =, !=, <, <= , >, >= ou quando ela é usada como uma expressão.

 

SELECT Numero, Sobrenome

FROM Empregado

WHERE Salario = (SELECT MIN(Salario)

FROM Empregado

GROUP BY Departamento);

ANY

Seleciona algum conjunto de dados.

 

Práticas

Exibir Número, Sobrenome, Salário dos empregados que possuirem algum salário menor que o menor salário do departamento de código 50.

 

SELECT NUMERO, SOBRENOME, SALARIO

FROM EMPREGADO

WHERE SALARIO < ANY

(SELECT MIN(SALARIO) FROM EMPREGADO WHERE DEPARTAMENTO = 50);

ALL

Seleciona todos os dados.

 

Práticas

Exibir Número, Sobrenome, Salário dos todos os empregados que possuirem salário menor que o menor salário do departamento de código 50.

 

SELECT NUMERO, SOBRENOME, SALARIO

FROM EMPREGADO

WHERE SALARIO < ALL

(SELECT MIN(SALARIO) FROM EMPREGADO WHERE DEPARTAMENTO = 50);

Correlacionadas

Ocorre quando uma subconsulta faz referência a uma coluna de uma tabela referenciada na instrução mãe. Uma subconsulta correlacionada é avaliada uma vez para cada linha processada pela instrução mãe. A instrução mãe pode ser uma instrução SELECT, UPDATE ou DELETE.

 

Práticas

Exibir todos os empregados (Sobrenome, Salário e Número do Departamento) que ganham acima do salário médio de seus respectivos departamentos.

 

SELECT EXT.SOBRENOME, EXT.SALARIO, EXT.DEPARTAMENTO

FROM EMPREGADO EXT

WHERE EXT.SALARIO >

(SELECT AVG(ITN.SALARIO)

FROM EMPREGADO ITN

WHERE ITN.DEPARTAMENTO = EXT.DEPARTAMENTO);

EXISTS

O operador EXISTS testa a existência de linhas no conjunto de resultados da subconsulta. Se o valor da linha de uma subconsulta for encontrado: A pesquisa na consulta interna não continuará; e A condição será indicada por um flag TRUE. Se o valor de linha de uma subconsulta não for encontrado: A condição será indicada por um flag FALSE; A pesquisa continuará na consulta interna.

 

Práticas

Exibir os empregados (Sobrenome, Salário e Número do Departamento) com no mínimo uma pessoa subordinada a eles.

 

SELECT EXT.SOBRENOME, EXT.SALARIO, EXT.DEPARTAMENTO

FROM EMPREGADO EXT

WHERE EXISTS

(SELECT 'X'

FROM EMPREGADO ITN

WHERE ITN.GERENTE = EXT.NUMERO);

União

Retorna todas as linhas selecionadas por uma consuta. Use esse operador para retornar todas as linhas de várias tabelas e eliminar as linhas duplicadas.

 

Dicas

Necessariamente os campos das tabelas unidas devem possuir os mesmos tipos de dados;

Os campos devem estar posicionados na mesma ordem;

A quantidade de campos deve ser a mesma;

A cláusula ORDER BY deve ser utilizada no final da união.

 

Práticas

Exibir a união das tabelas Cargo e Departamento para os campos Número e Nome.

 

SELECT NUMERO, NOME

FROM CARGO

UNION

SELECT NUMERO, NOME

FROM DEPARTAMENTO;

Intersecção

Serve para retornar todas as linhas comuns a várias consultas.

 

Práticas

Exibir a intersecção das tabelas Cargo e Departamento para os campos Número e Nome.

 

--SQLServer | Postgre | Oracle

SELECT NUMERO, NOME FROM CARGO INTERSECT SELECT NUMERO, NOME FROM DEPARTAMENTO;

 

--MySQL

SELECT NUMERO, NOME FROM CARGO INNER JOIN DEPARTAMENTO USING (NUMERO, NOME);

Subtração

Serve para obter linhas retornadas pela primeira consulta e ausentes na segunda consulta.

 

Práticas

Exibir a subtração das tabelas Cargo e Departamento para os campos Número e Nome.

 

--SQLServer | Oracle

SELECT NUMERO, NOME FROM CARGO MINUS SELECT NUMERO, NOME FROM DEPARTAMENTO;

 

--Postgre

SELECT NUMERO, NOME FROM CARGO EXCEPT SELECT NUMERO, NOME FROM DEPARTAMENTO;

 

--MySQL

SELECT DISTINCT NUMERO, NOME FROM CARGO WHERE (NUMERO, NOME) NOT IN (SELECT NUMERO, NOME FROM DEPARTAMENTO);

Macrosubstituição

Alguns bancos permitem passagem de parâmetros com o uso de variáveis de substituição.

 

Dicas

No Oracle a macrosubstituição é com (&);

No SQLServer a macrosubstituição é com (:);

 

Práticas

Exibir o Sobrenome do empregado que possuir o Número informado pelo usuário.

 

SELECT SOBRENOME

FROM EMPREGADO

WHERE NUMERO = :Informe_Numero;

Seleção de Seleção

Alguns BD permitem a selecão de dados nas cláusulas SELECT e/ou FROM.

 

--Oracle

SELECT CARGO FROM

(SELECT C.NOME AS CARGO, AVG(E.SALARIO) AS MEDIASALARIO

FROM CARGO C INNER JOIN EMPREGADO E

ON C.NUMERO = E.CARGO

GROUP BY C.NOME);

 

--SQLServer

SELECT E.NUMERO, E.SOBRENOME, E.SALARIO, E.DEPARTAMENTO AS DEPCOD, (SELECT D.NOME FROM DEPARTAMENTO D WHERE D.NUMERO = E.DEPARTAMENTO) AS DEPNOME

FROM EMPREGADO E;

Análise Top-N

Alguns BD permitem a selecão das N primeiras linhas que satisfazem a uma determinada seleção.

 

Práticas

Exibir os 2 primeiros registros de empregados cujos os salários sejam menores que 2000.

 

--SQLServer

SELECT TOP 2 NUMERO, APELIDO, SALARIO FROM EMPREGADO

WHERE SALARIO < 2000;

 

--Postgre | MySQL

SELECT NUMERO, APELIDO, SALARIO FROM EMPREGADO

WHERE SALARIO < 2000 LIMIT 2;

 

--Oracle

SELECT NUMERO, APELIDO, SALARIO FROM EMPREGADO

WHERE SALARIO < 2000 AND ROWNUM <= 2;

 

--Firebird

SELECT FIRST 2 NUMERO, APELIDO, SALARIO FROM EMPREGADO

WHERE SALARIO < 2000;

Excluir Registros

Excluir registros.

 

Sintaxe

DELETE FROM nome

       [WHERE definição]

       [ORDER BY ...];

Excluir e recriar tabela: 

Apaga e recria uma tabela.

 

Sintaxe

TRUNCATE TABLE nome_tabela;

Excluir  Tabela

Apaga uma tabela.

 

Sintaxe

DROP TABLE nome_tabela;

Transações

Tratamento de dados de forma atômica.

 

Dicas

Em alguns BD as instruções DDL e DCL são automáticas.

 

Práticas

Exibir os dados da tabela Empregado;

Salvar um ponto A;

Apagar todos os empregados do Departamento 50;

Exibir os dados da tabela Empregado;

Salvar um ponto B;

Apagar todos os empregados com Salário = 1000;

Exibir os dados da tabela Empregado;

Restaurar o ponto A;

Exibir os dados da tabela Empregado;

Restaurar o ponto B;

Exibir os dados da tabela Empregado.

 

--SQLServer

SAVE TRANSACTION A;

SELECT * FROM DEPARTAMENTO;

DELETE FROM DEPARTAMENTO WHERE NUMERO = 110;

SELECT * FROM DEPARTAMENTO;

UPDATE DEPARTAMENTO SET NOME = 'TESTE' WHERE NUMERO = 90;

SELECT * FROM DEPARTAMENTO;

ROLLBACK TRANSACTION A;

SAVE TRANSACTION B;

INSERT INTO DEPARTAMENTO (NUMERO, NOME) VALUES (200, 'TESTE2');

SELECT * FROM DEPARTAMENTO;

COMMIT TRANSACTION B;

SELECT * FROM DEPARTAMENTO;

Visão

Serve para apresentar combinações ou subconjuntos lógicos de dados através de views de tabelas. A view é uma tabela lógica baseada em uma tabela ou em outra view. Ela não contém dados próprios, mas é uma janela por meio da qual os dados das tabelas podem ser vistos e algumas vezes alterados. As tabelas nas quais uma view se baseia são denominadas de tabelas-base. A view é armazenada como uma instrução SELECT no dicionário de dados.

 

Sintaxe

CREATE VIEW [ schema_name . ] view_name

[ (column [ ,...n ] ) ] 

AS select_statement;

 

Dicas

Serve para restringir o acesso a dados;

Serve para facilitar as consultas complexas;

Serve para permitir a independência dos dados;

Serve para apresentar diferentes visões dos mesmos dados.

 

View Simples

Cria dados a partir de uma tabela apenas;

Não contém funções e nem grupo de dados;

Permite operações DML.

 

Práticas

Criar uma visão denominada VISAO_1, que contenha os campos Número, Primeironome, Sobrenome dos empregados do departamento 80. Depois de criada insira um novo registro nessa visão. Finalizando exiba todos os dados da tabela Empregado.

 

CREATE VIEW VISAO_1 AS

SELECT NUMERO, PRIMEIRONOME, SOBRENOME FROM EMPREGADO

WHERE DEPARTAMENTO=80;

 

INSERT INTO VISAO_1 VALUES (200, 'ANTONIETA', 'LARANJEIRA');

 

SELECT * FROM EMPREGADO;

 

View Complexas

Cria dados a partir de várias tabelas;

Contém funções ou grupo de dados;

Nem sempre permite operações DML.

 

Práticas

Criar uma visão denominada VISAO_2, que contenha os campos Nome (Primeironome + ‘ ‘ + Sobrenome) e o campo Salariobase (Salário – 500) dos empregados do departamento 30. Depois de criada tente insir um novo registro nessa visão. Finalizando exiba todos os dados da tabela Empregado.

 

CREATE VIEW VISAO_2 AS

SELECT (PRIMEIRONOME + ' ' + SOBRENOME) AS NOME, (SALARIO - 500) AS SALARIOBASE

FROM EMPREGADO WHERE DEPARTAMENTO=10;

 

INSERT INTO VISAO_2 VALUES ('JULIETA PEREIRA', 400);

 

SELECT * FROM EMPREGADO;

Controle de Usuários

Criar novos usuários para o BD.

 

Sintaxe

CREATE LOGIN login

WITH PASSWORD = ‘senha';

CREATE USER usuario

FOR LOGIN [operador]

WITH DEFAULT_SCHEMA = [dbo];

Oracle

CREATE USER usuario

IDENTIFIED BY ‘senha';

 

Práticas

Para adicionar um usuário ao banco de dados do SQL Server você tem que seguir três passos: Primeiro: você deve criar um login, que é um "cara" que tem permisssão de se logar no SQL Sever

 

CREATE LOGIN USUARIO WITH PASSWORD = 'senha';

 

Segundo: você deve criar um usuário para o banco de dados que deseja mapeando esse usuário para o login criado, assim seu usuário conseguirá se logar no SQL Server e entrar no banco de dados desejado.

 

CREATE USER USUARIO FROM LOGIN USUARIO;

 

Terceiro: você deve dar ou remover permissões ao usuário porque até o segundo passo o usuário criado só tem direito a entrar no banco de dados, dando as permissões o usuário já pode operar no banco de dados.

 

Se o usuário for comum você pode adicioná-lo apenas as roles de db_reader e db_writer, que permitirá que o usuário faça select, insert, delete e update em todas as tabelas do referido banco de dados.

 

EXEC SP_ADDROLEMEMBER 'DB_DATAREADER', 'USUARIO'

Privilégios

Fornecer acesso aos objetos dos BD.

 

Sintaxe

GRANT privilégio ON objeto TO usuário;

REVOKE privilégio ON objeto FROM usuário;

 

Práticas

Fornecer acesso aos objetos dos BD.

 

GRANT CREATE TABLE, CREATE VIEW TO operador1;

GRANT SELECT ON EMPREGADO TO operador1;

 

Retirar acesso aos objetos dos BD.

 

REVOKE CREATE TABLE, CREATE VIEW FROM operador1;

REVOKE SELECT ON EMPREGADO FROM operador1;

Seqüências

Alguns bancos como o Postgre e o Oracle possuem um objeto chamado de sequencia que equivale no SQLServer a regra de IDENTITY( 1, 1).

 

Sintaxe

CREATE [TEMPORARY | TEMP] SEQUENCE name

[INCREMENT [BY] increment ]

[MINVALUE minvalue | NO MINVALUE]

[MAXVALUE maxvalue | NO MAXVALUE]

[START [ WITH ] start]

[CACHE cache]

[[ NO ] CYCLE]

Vínculos

A grande vantagem dos vínculos de banco de dados é que eles permitem aos usuários acessarem dados de um banco de dados remoto de modo que eles fiquem conectados por meio do conjunto de privilégios do proprietário do objeto. Em outras palavras, um usuário local pode acessar um banco de dados remoto sem necessariamente ser um usuário do banco de dados remoto.

 

Práticas

Vincular dados.

 

CREATE PUBLIC DATABASE LINK banco

USING ‘usuário’;

SELECT * FROM objeto@banco;

Objetos de Bancos de Dados 

Postgre:

· Tables

· Views

· Functions

· Domains

· Rules

· Triggers

· Indices

· Sequences

· Composite Types

· Enum Types

· Base Types

· Aggregates

· Operators

Principais Funções do SQLServer

ASCII(string)

pega o valor em ASCII da string

 

CHAR(integer)

troca inteiro do ASCII em um caracter

 

LEN(string)

Identifica o comprimento de uma expressão em caracteres

 

LOWER(string)

converte uma string uppercase para lowercase.

 

LTRIM(string)

remove os espaços em branco

 

PATINDEX(posicao, expressao)

devolve a posicão de uma string dentro de um   texto. Se não encontrar, retorna zero.

 

REPLICATE(string, integer)

repete N vezes um caractere especificado

 

REVERSE(string)

retorna o inverso de uma expressao

 

RTRIM(string)

remove os espaços em branco à direita de uma string

 

SPACE(integer)

que retorna o número de espaços em branco informados no parâmetro

 

STUFF(string texto, X, Y, string texto_a_inserir)

apaga da string "texto" os y caracteres a partir da posição x e os substitui por "texto_a_inserir"

 

SUBSTRING(string texto, posicao_inicial, tamanho)

retorna uma string com o comprimento definido em "tamanho" extraida da string "texto", a partir da "posicao_inicial"

 

UPPER(string)

retorna string em maiusculas

 

DATEADD (parte, numero, data)

adiciona um valor a parte de uma data

 

DATEDIFF (parte, data inicial, data final) 

subtrai a data inicial da data final, indicando o   resultado na unidade definida em "parte"

 

GETDATE()

retorna a data atual do sistema

 

DATENAME (parte, data)

retorna o nome da parte de uma data

 

DATEPART(parte, data)

retorna a parte de uma data

 

CAST(expressao as datatype)

converte uma expressao no datatype informado

 

COL_LENGTH(nome_da_tabela, nome_da_coluna)

retorna o tamanho da coluna

 

COL_NAME(id_da_tabela, id_da_coluna) 

retorna o nome da coluna

 

DATALENGTH(expressao)

retorna o numero de bytes usados para armazenar a expressao

 

DB_ID(nome_do_banco)

retorna o ID do banco informado

 

DB_NAME(id_do_banco)

retorna o nome do banco

 

HOST_ID()

retorna a ID da estação que está acessando o SQL Server

 

HOST_NAME()

retorna o nome da estação que está acessando o SQL Server

 

IDENT_INCR(nome_da_tabela_ou_view)

retorna o valor incrementado

 

IDENT_SEED(tabela_ou_view)

retorna o valor inicial da coluna

 

INDEX_COL(nome_da_tabela, indice_id, chave_id)

retorna o nome da coluna que participa do índice

 

ISNULL(expressao, valor)

se a expressao for null, troca pelo valor especificado

 

ISNUMERIC(expressao)

retorna 1 se a expressao for numerica e 0 se não for

 

NEWID()

retorna um novo valor do tipo uniqueidentifier

 

NULLIF(expressao_1, expressao_2)

retorna nulo se as duas expressoes forem equivalentes. Se não forem, retorna a primeira expressao.

 

OBJECT_ID(nome_do_objeto)

retorna o ID de um objeto, a partir do nome fornecido

 

OBJECT_NAME(ID_do_objeto)

retorna o nome do objeto, a partir do ID fornecido

 

PARSENAME(objeto, parte)

retorna a parte do nome de um objeto, desde que tenha sido qualificado

 

STATS_DATE(tabela_id, indice_id)

retorna a data em que as estatísticas do índice foram atualizadas

 

SUSER_SID(nome_do_usuario)

retorna o ID do usuario informado

 

SUSER_NAME(usuario_id)

retorna o id do usuário no servidor. O argumento é opcional.

 

SUSER_SNAME(id_do_usuario)

retorna o nome do usuario informado. Se nenhum ID de usuario for passado para a função, retorna o nome do usuario logado

 

USER_ID(nome_do_usuario)

retorna o ID do usuario informado para o BD em uso

 

USER_NAME(id_do_usuario)

retorna o usuario conectado ao BD

 

ABS(numero)

retorna o valor absoluto do numero

 

ACOS(float)

retorna o arco-coseno do numero informado

 

ASIN(float)

retorna o arco-seno do numero informado

 

ATAN(float)

retorna o arco-tangente do numero informado

 

ATN2(Float expressao_1, float expressao_2)

arco-tangente do valor definido pela divisão da primeira expressão pela segunda

 

CEILING(numero)

retorna o menor inteiro que seja maior ou igual ao numero informado

 

COS(float)

retorna o coseno do numero informado

 

COT(float)

retorna o cotangente do numero informado

 

DEGREES(numero)

converte radianos para graus

 

EXP(float)

retorna o exponencial de um numero especificado

 

FLOOR(numero)

retorna o maior inteiro que seja menor ou igual ao numero informado

 

LOG(float)

retorna o logaritmo natural do numero informado

 

LOG10(float)

retorna o logaritmo base 10 do numero informado

 

PI()

retorna o valor de PI 3.1415926535897931.

 

POWER(numero, potencia)

retorna o valor elevado à potencia informada

 

RADIANS(numero)

converte graus para radianos

 

RAND(expressao)

um número aleatório entre 0 e 1. Expressão é   opcional e será usada como semente da cadeia pseudo-aleatória

 

ROUND(numero, precisao, arredonda_ou_trancar)

arredonda ou tranca o numero fornecido de acordo com a precisao informada. Se o terceiro parametro não for passado para a funçao, o numero é arredondado. Se quiser que o numero seja truncado, deve-se fornecer o valor 1

 

SIGN(numero)

retorna sinal positivo, negativo ou zero do numero

 

SIN(float)

retorna o seno do angulo especificado

 

SQRT(float)

retorna a raiz quadrada de um numero

 

TAN(float)

retorna a tangente de um numero informado

 

SQUARE(float)

retorna o quadrado de um numero

 

Principais Funções do Oracle

 

ABS(n)

Devolve o valor absoluto de (n).

 

CEIL(n)

Obtém o valor inteiro imediatamente superior ou igual a "n".

 

FLOOT(n)

Devolve o valor inteiro imediatamente inferior ou igual a "n".

 

MOD (m, n)

Devolve o resto resultante de dividir "m" entre "n".

 

NVL (valor, expressão)

Substitui um valor nulo por outro valor.

 

POWER (m, exponente)

Calcula a potência de um número.

 

ROUND (numero [, m])

Arredonda números com o número de dígitos de precisão indicados.

 

SIGN (valor)

Indica o signo do "valor".

 

SQRT(n)

Devolve a raiz quadrada de "n".

 

TRUNC (numero, [m])

Trunca números para que tenham uma certa quantidade de dígitos de precisão.

 

VAIRANCE (valor)

Devolve a média de um conjunto de valores.

 

CHR(n)

Devolve o caractere cujo valor em binário é equivalente a "n".

 

CONCAT (cad1, cad2)

Devolve "cad1" concatenada com "cad2".

 

LOWER (cad)

Devolve a cadeia "cad" em minúsculas.

 

UPPER (cad)

Devolve a cadeia "cad" em maiúsculas.

 

INITCAP (cad)

Converte a cadeia "cad" a tipo título.

 

LPAD (cad1, n[,cad2])

Adiciona caracteres à esquerda da cadeia até que tenha uma certa longitude.

 

RPAD (cad1, n[,cad2])

Adiciona caracteres à direita até que tenha uma certa longitude.

 

LTRIM (cad [,set])

Suprime um conjunto de caracteres à esquerda da cadeia.

 

RTRIM (cad [,set])

Suprime um conjunto de caracteres à direita da cadeia.

 

REPLACE (cad, cadeia_busca [, cadeia_substitucao])

Substitui um caractere ou caracteres de uma cadeia com 0 ou mais caracteres.

 

SUBSTR (cad, m [,n])

Obtém parte de uma cadeia.

 

TRANSLATE (cad1, cad2, cad3)

Converte caracteres de uma cadeia em caracteres diferentes, segundo um plano de substituição marcado pelo usuário.

 

ASCII(cad)

Devolve o valor ASCII da primeira letra da cadeia "cad".

 

INSTR (cad1, cad2 [, comeco [,m]])

Permite uma busca de um conjunto de caracteres em uma cadeia, mas não suprime nenhum caractere depois.

 

LENGTH (cad)

Devolve o número de caracteres de cad.

 

SYSDATE

Devolve a data do sistema.

 

ADD_MONTHS (data, n)

Devolve a data "data" incrementada em "n" meses.

 

LASTDAY (data)

Devolve a data do último dia do mês que contém "data".

 

MONTHS_BETWEEN (data1, data2)

Devolve a diferença em meses entre as datas "data1" e "data2".

 

NEXT_DAY (data, cad)

Devolve a data do primeiro dia da semana indicado por "cad" depois da data indicada por "data".

 

TO_CHAR

Transforma um tipo DATE ou NUMBER em uma cadeia de caracteres.

 

TO_DATE

Transforma um tipo NUMBER ou CHAR em DATE.

 

TO_NUMBER

Transforma uma cadeia de caracteres em NUMBER.

Tipos de Dados

SQLServer

· BIGINT: valores numéricos inteiros.

· BIT: bits ou seja somente poderá conter os valores lógicos 0 ou 1.

· CHAR(N): N caracteres fixos no formato não Unicode, com espaços em branco.

· DATE: somente data.

· DATETIME: data e hora, com precisão de centésimos de segundos.

· DECIMAL(18,0): mesmas funcionalidades do tipo NUMERIC.

· FLOAT: valores numéricos aproximados com precisão de ponto flutuante.

· IMAGE: dados no formato binário.

· INT: valores numéricos inteiros.

· NCHAR(N): N caracteres fixos no formato Unicode, com espaços em branco.

· NTEXT: caracteres no formato Unicode.

· NUMERIC(18,0): valores numéricos com casas decimais, utilizando precisão.

· NVARCHAR(N): Armazena N caracteres no formato Unicode.

· REAL: valores numéricos aproximados com precisão de ponto flutuante.

· SMALLDATETIME: data e hora, com precisão de minutos.

· SMALLINT: valores numéricos inteiros.

· TEXT: caracteres no formato não Unicode.

· TIME: somente hora. Pode armazenar segundos até a fração de 9999999

· TINYINT: valores numéricos inteiros.

· VARCHAR(N): N caracteres (até 8.000) no formato não Unicode.

Postgre

· bigint: inteiro de oito bytes com sinal.

· bit: cadeia de bits de comprimento fixo.

· bool: booleano lógico (verdade/falso).

· bytea: dados binários.

· char(n): cadeia de caracteres de comprimento fixo.

· date: data de calendário (ano, mês,dia).

· decimal[(p, s)] : numérico exato com precisão selecionável.

· float8: número de ponto flutuante de precisão dupla.

· Int: inteiro de quatro bytes com sinal.

· numeric[(p, s)]: numérico exato com precisão selecionável.

· real: número de ponto flutuante de precisão simples.

· smallint: inteiro de dois bytes com sinal.

· text: cadeia de caracteres de comprimento variável.

· time: hora do dia.

· timestamp: data e hora.

· varbit(n): cadeia de bits de comprimento variável.

· varchar(n): cadeia de caracteres de comprimento variável.

Oracle

· BLOB, CLOB, NCLOB e BFILE: Tipos de dados para conteúdos binários.

· CHAR(n): Conjunto de caracteres de tamanho fixo.

· DATE: Um valor de data.

· LONG RAW: Dados binários..

· LONG: Conjunto de caracteres de tamanho variável.

· NUMBER(p, e): Representa um número.

· VARCHAR2(n): Conjunto de caracteres de tamanho variável.

MySQL

· BIGINT: inteiros grandes.

· BLOB: BLOB normal.

· BOOL: Booleano.

· CHAR: o mesmo que CHAR(1).

· DATE: data. Exibido como YYYY-MM-DD.

· DATETIME: data e hora. Exibido como YYYY-MM-DD HH:MM:SS.

· DECIMAL[(M,D)]: número de ponto flutuante armazenado como char.

· DOUBLE: números de ponto flutuante de precisão dupla.

· FLOAT(precisão): números de ponto flutuante de precisão simples ou dupla.

· INT: inteiros regulares.

· INTEGER: o mesmo que INT.

· LONGBLOB: BLOB longo.

· LONGTEXT: TEXT longo.

· SMALLINT: inteiros pequenos.

· TEXT: TEXT normal.

· TIME: hora. Exibido como HH:MM:SS.

· TINYBLOB: BLOB pequeno.

· TINYTEXT: TEXT pequeno.

· VARCHAR(n): variável string de tamanho variável.

Usuários e Senhas Padrões

SQLServer: Usuário sa; Senha definida.

Postgre: Usuário root; Sem senha.

Oracle: Usuário SYSTEM; Senha manager.

MySQL: Usuário root; Sem senha.

Firebird: Usuário sysdba; Senha masterkey.

Exemplos

CREATE TABLE DISCIPLINA (COD INTEGER, DSC VARCHAR(50), PRIMARY KEY(COD));
CREATE TABLE PROFESSOR (MAT INTEGER, NOM VARCHAR(50), PRIMARY KEY(MAT));

CREATE TABLE ALUNO (MAT INTEGER, NOM VARCHAR(50), PRIMARY KEY(MAT));

CREATE TABLE NOTA (MAT INTEGER, DIS INTEGER, NT1 FLOAT, NT2 FLOAT, NT3 FLOAT, PRIMARY KEY (MAT,DIS), FOREIGN KEY (MAT) REFERENCES ALUNO (MAT), FOREIGN KEY (DIS) REFERENCES DISCIPLINA (COD) ON DELETE CASCADE ON UPDATE CASCADE);

DROP TABLE PROFESSOR;

CREATE TABLE TEMPOR (MED FLOAT);

 

CREATE USER 'U1'@'localhost' IDENTIFIED BY 'u1';

CREATE USER 'U2'@'localhost' IDENTIFIED BY 'u2';

CREATE USER 'U3'@'localhost' IDENTIFIED BY 'u3';

GRANT ALL PRIVILEGES ON *.* TO 'U1'@'localhost';

GRANT INSERT ON exemplo.disciplina TO 'U2'@'localhost';

GRANT UPDATE ON exemplo.disciplina TO 'U2'@'localhost';

GRANT SELECT ON exemplo.disciplina TO 'U2'@'localhost';

GRANT DELETE ON exemplo.disciplina TO 'U3'@'localhost';

REVOKE DELETE ON exemplo.disciplina TO 'U3'@'localhost'

FLUSH PRIVILEGES;

DROP USER 'U3'@'localhost';

 

START TRANSACTION;

SELECT @A:=AVG(NT1) FROM NOTA WHERE MAT=1;

UPDATE TEMPOR SET MED=@A;

COMMIT;

 

CREATE TRIGGER GATILHO BEFORE INSERT ON NOTA

FOR EACH ROW UPDATE NT3 SET NT3 = (NT1 + NT2) / 2;

Exercícios

Obs: a solução foi realizada no SQLServer.

 

--Tabela Cargos

CREATE TABLE CARGO (

NUMERO INTEGER IDENTITY( 1, 1),

NOME VARCHAR(30) NOT NULL,

PRIMARY KEY (NUMERO)

);

INSERT INTO CARGO (NOME) VALUES ('ANALISTA TRAINEE');

INSERT INTO CARGO (NOME) VALUES ('ANALISTA JUNIOR');

INSERT INTO CARGO (NOME) VALUES ('ANALISTA PLENO');

INSERT INTO CARGO (NOME) VALUES ('ANALISTA SÊNIOR');

INSERT INTO CARGO (NOME) VALUES ('ANALISTA MASTER');

INSERT INTO CARGO (NOME) VALUES ('CONSULTOR');

SELECT * FROM CARGO;

 

--Tabela Departamentos

CREATE TABLE DEPARTAMENTO (

NUMERO INTEGER,

NOME VARCHAR(30) NOT NULL,

FATOR DECIMAL(3,2),

PRIMARY KEY (NUMERO)

);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) VALUES (10, 'ADMINISTRAÇÃO (ADMIN)', 1.75);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) VALUES (20, 'MARKETING (MKT)', 1.85);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) VALUES (50, 'COMPRAS (COM)', 2.73);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) VALUES (60, 'TECNOLOGIA (TEC)', 1.55);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) VALUES (80, 'VENDAS (VEN)', 1.87);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) VALUES (90, 'EXECUTIVO (EXEC)', 1.92);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) VALUES (110, 'CONTABILIDADE (CONTAB)', 2.35);

INSERT INTO DEPARTAMENTO (NUMERO, NOME, FATOR) VALUES (190, 'CONTRATOS (CTT)', 2.23);

SELECT * FROM DEPARTAMENTO;

 

--Tabela Empregados

CREATE TABLE EMPREGADO (

NUMERO INTEGER,

PRIMEIRONOME VARCHAR(25),

SOBRENOME VARCHAR(25),

CARGO INTEGER,

DEPARTAMENTO INTEGER,

SALARIO REAL,

COMISSAO INTEGER,

NASCIMENTO DATE,

APELIDO VARCHAR(10),

GERENTE INTEGER,

PRIMARY KEY (NUMERO),

FOREIGN KEY (CARGO) REFERENCES CARGO (NUMERO) ON UPDATE CASCADE,

FOREIGN KEY (DEPARTAMENTO) REFERENCES DEPARTAMENTO (NUMERO) ON UPDATE CASCADE

);

INSERT INTO EMPREGADO (NUMERO, PRIMEIRONOME, SOBRENOME) VALUES (100, 'ANTÔNIO', 'PALMEIRA');

INSERT INTO EMPREGADO (NUMERO, PRIMEIRONOME, SOBRENOME) VALUES (101, 'JOSÉ', 'FIGUEIRA');

INSERT INTO EMPREGADO (NUMERO, PRIMEIRONOME, SOBRENOME) VALUES (102, 'MARIA', 'MANGUEIRA');

INSERT INTO EMPREGADO (NUMERO, PRIMEIRONOME, SOBRENOME) VALUES (103, 'JOÃO', 'ABACATEIRO');

INSERT INTO EMPREGADO (NUMERO, PRIMEIRONOME, SOBRENOME) VALUES (104, 'CARLOS', 'CASTANHEIRO');

INSERT INTO EMPREGADO (NUMERO, PRIMEIRONOME, SOBRENOME) VALUES (107, 'MÔNICA', 'VIDEIRA');

INSERT INTO EMPREGADO (NUMERO, PRIMEIRONOME, SOBRENOME) VALUES (124, 'PEDRO', 'LARANJEIRA');

INSERT INTO EMPREGADO (NUMERO, PRIMEIRONOME, SOBRENOME) VALUES (141, 'TIAGO', 'PESSEGUEIRO');

INSERT INTO EMPREGADO (NUMERO, PRIMEIRONOME, SOBRENOME) VALUES (142, 'RENATA', 'VIDEIRA');

INSERT INTO EMPREGADO (NUMERO, PRIMEIRONOME, SOBRENOME) VALUES (143, 'GABRIEL', 'MACIEIRA');

UPDATE EMPREGADO SET CARGO=7, DEPARTAMENTO=10, GERENTE=NULL, SALARIO=1000, COMISSAO=NULL, NASCIMENTO='01/07/80', APELIDO='TONHO' WHERE NUMERO=100;

UPDATE EMPREGADO SET CARGO=7, DEPARTAMENTO=20, GERENTE=100, SALARIO=1500, COMISSAO=NULL, NASCIMENTO='18/05/70', APELIDO='ZÉ' WHERE NUMERO=101;

UPDATE EMPREGADO SET CARGO=8, DEPARTAMENTO=10, GERENTE=100, SALARIO=2000, COMISSAO=3, NASCIMENTO='22/07/80', APELIDO='MA' WHERE NUMERO=102;

UPDATE EMPREGADO SET CARGO=9, DEPARTAMENTO=50, GERENTE=100, SALARIO=1500, COMISSAO=2, NASCIMENTO='03/03/71', APELIDO='JO' WHERE NUMERO=103;

UPDATE EMPREGADO SET CARGO=NULL, DEPARTAMENTO=60, GERENTE=100, SALARIO=2500, COMISSAO=3, NASCIMENTO='01/01/65', APELIDO='CA' WHERE NUMERO=104;

UPDATE EMPREGADO SET CARGO=7, DEPARTAMENTO=90, GERENTE=NULL, SALARIO=1000, COMISSAO=2, NASCIMENTO='15/02/60', APELIDO='MO' WHERE NUMERO=107;

UPDATE EMPREGADO SET CARGO=8, DEPARTAMENTO=80, GERENTE=107, SALARIO=1100, COMISSAO=NULL, NASCIMENTO='30/07/83', APELIDO='PEDROCA' WHERE NUMERO=124;

UPDATE EMPREGADO SET CARGO=9, DEPARTAMENTO=110, GERENTE=107, SALARIO=2000, COMISSAO=2, NASCIMENTO='21/06/78', APELIDO='TI' WHERE NUMERO=141;

UPDATE EMPREGADO SET CARGO=10, DEPARTAMENTO=80, GERENTE=107, SALARIO=1500, COMISSAO=NULL, NASCIMENTO='02/12/72', APELIDO='RE' WHERE NUMERO=142;

UPDATE EMPREGADO SET CARGO=NULL, GERENTE=110, SALARIO=2000, COMISSAO=2, NASCIMENTO='11/11/81', APELIDO='BIEL' WHERE NUMERO=143;

SELECT * FROM EMPREGADO;

 

--Questões:

--1.Mostre todos os dados dos empregados que nasceram após o ano de 1980. (0,25)

SELECT * FROM EMPREGADO WHERE YEAR(NASCIMENTO) > 1980;

 

--2.Mostre o sobrenome, o cargo, o salário e a comissão dos empregados que recebem comissão. (0,25)

SELECT SOBRENOME, CARGO, SALARIO, COMISSAO FROM EMPREGADO WHERE COMISSAO IS NOT NULL;

 

--3.Mostre os empregados que não tem comissão com um aumento de 10% em seus salários, arredondando-os. (0,25)

SELECT NUMERO, APELIDO, COMISSAO, SALARIO, SALARIO + (SALARIO *.10) AS BRUTO FROM EMPREGADO WHERE COMISSAO IS NULL;

 

--4.Mostre os sobrenomes de todos os empregados juntamente com o número de anos e de meses completados de nascimento. (0,25)

SELECT SOBRENOME, NASCIMENTO, GETDATE() AS DATACORRENTE, DATEDIFF(YEAR,NASCIMENTO,GETDATE()) AS ANOS, DATEDIFF(MONTH,NASCIMENTO,GETDATE()) AS MESES FROM EMPREGADO 

 

--5.Mostre os empregados cujos os sobrenomes iniciem com J, K, L ou M. (0,25)

SELECT SOBRENOME FROM EMPREGADO WHERE SOBRENOME LIKE 'J%' OR SOBRENOME LIKE 'K%' OR SOBRENOME LIKE 'L%' OR SOBRENOME LIKE 'M%';

 

--6.Mostre todos os funcionários e indique, através de “Sim” ou “Não”, se eles recebem ou não uma comissão. (0,25)

SELECT APELIDO, CASE WHEN COMISSAO IS NULL THEN 'NÃO' ELSE 'SIM' END AS COMISSAO FROM EMPREGADO;

 

--7.Mostre os nomes de departamentos, os nomes dos empregados, os nomes dos cargos e os salários dos empregados que trabalham nos departamentos 50, 60 ou 90. (0,25)

SELECT E.PRIMEIRONOME AS EMPREGADO, D.NOME AS DEPARTAMENTO, C.NOME AS CARGO, E.SALARIO

            FROM EMPREGADO E

        INNER JOIN DEPARTAMENTO D ON E.DEPARTAMENTO = D.NUMERO

        INNER JOIN CARGO C ON E.CARGO = C.NUMERO

            WHERE E.DEPARTAMENTO IN (50, 60, 90);

                                              

--8.Quantos empregados têm um primeironome terminado em “o” e em “a”? (0,25)

SELECT 'O' AS TERMINA, COUNT(NUMERO) AS QTDE FROM EMPREGADO WHERE PRIMEIRONOME LIKE '%O'

UNION

SELECT 'A' AS TERMINA, COUNT(NUMERO) AS QTDE FROM EMPREGADO WHERE PRIMEIRONOME LIKE '%A';

 

--9.Mostre o nome de todos os departamentos, além dos números dos empregados que trabalham em cada departamento. Certifique-se de que os departamentos sem empregados também sejam incluídos. (0,25)

SELECT        D.NUMERO, 

                        D.NOME,

        (SELECT COUNT(E.NUMERO) FROM EMPREGADO E WHERE E.DEPARTAMENTO = D.NUMERO) AS QTDE_EMP

FROM DEPARTAMENTO D;

 

--10.Quais cargos são encontrados nos departamentos 10 e 50? (0,25)

SELECT DISTINCT C.NOME FROM EMPREGADO E INNER JOIN CARGO C ON E.CARGO = C.NUMERO WHERE E.DEPARTAMENTO IN (10,50);

 

--11.Quais cargos são encontrados nos departamentos administrativo e executivo, e quantos empregados desempenham esses cargos? Mostre primeiro o cargo com o maior número de empregados. (0,25)

SELECT E.DEPARTAMENTO, C.NOME,

(SELECT COUNT(E.NUMERO) FROM EMPREGADO E WHERE E.CARGO = C.NUMERO) AS QTDE_EMP

FROM EMPREGADO E 

INNER JOIN CARGO C ON E.CARGO = C.NUMERO 

WHERE E.DEPARTAMENTO IN (SELECT X.NUMERO FROM DEPARTAMENTO X WHERE X.NOME LIKE 'ADM%' OR X.NOME LIKE 'EXE%');

 

--12.Mostre todos os empregados que nasceram na primeira metade do mês (antes do dia 16). (0,25)

SELECT E.APELIDO, E.NASCIMENTO FROM EMPREGADO E WHERE DAY(E.NASCIMENTO) < 16;

 

--13.Mostre o primeironome, o salário e o número de todos os empregados. (0,25)

SELECT E.PRIMEIRONOME, E.SALARIO, E.NUMERO FROM EMPREGADO E;

 

--14.Mostre todos os empregados que têm gerentes com um salário acima ou igual a 1000. Mostre os seguintes dados: primeironome do empregado e do gerente e salário do gerente. (0,25)

SELECT E.APELIDO, E.SALARIO, E.GERENTE FROM EMPREGADO E WHERE E.GERENTE IS NOT NULL 

            AND   EXISTS (SELECT 'X' FROM EMPREGADO E WHERE E.GERENTE IS NULL AND E.SALARIO >= 1000);

 

--15.Mostre o número do departamento, o nome, o número do empregado e o salário médio de todos os departamentos juntamente com os nomes, os salários e os cargos dos empregados que trabalham em cada departamento. (0,25)

SELECT        E.NUMERO, 

                        E.APELIDO,

        E.SALARIO,

        C.NOME AS CARGO,

                        E.DEPARTAMENTO, 

        D.NOME,

                        (SELECT AVG(X.SALARIO) FROM EMPREGADO X WHERE X.DEPARTAMENTO = E.DEPARTAMENTO) AS SAL_MED

FROM EMPREGADO E

INNER JOIN DEPARTAMENTO D ON E.DEPARTAMENTO = D.NUMERO

INNER JOIN CARGO C ON E.CARGO = C.NUMERO;

 

--16.Mostre o número do departamento e o menor salário e o maior salário. (0,25)

SELECT        D.NUMERO,

                        (SELECT MIN(E.SALARIO) FROM EMPREGADO E WHERE E.DEPARTAMENTO = D.NUMERO) AS MENOR,

                        (SELECT MAX(E.SALARIO) FROM EMPREGADO E WHERE E.DEPARTAMENTO = D.NUMERO) AS MAIOR

FROM DEPARTAMENTO D;

 

--17.Mostre os números, os nomes dos departamentos em que não há empregados trabalhando. (0,25)

SELECT D.NUMERO, D.NOME FROM DEPARTAMENTO D WHERE D.NUMERO NOT IN (SELECT E.DEPARTAMENTO FROM EMPREGADO E);

 

--18.Mostre os números, os nomes dos departamentos e os números dos empregados trabalhando em cada departamento que inclua mais de 2 empregados, inclusive. (0,25)

SELECT        D.NUMERO,

                        D.NOME,

                        (SELECT COUNT(E.NUMERO) FROM EMPREGADO E WHERE E.DEPARTAMENTO = D.NUMERO) AS QTDE_EMP

FROM DEPARTAMENTO D

WHERE (SELECT COUNT(E.NUMERO) FROM EMPREGADO E WHERE E.DEPARTAMENTO = D.NUMERO) >= 2;

 

--19.Mostre os números, os nomes dos departamentos e os números dos empregados trabalhando em cada departamento que tenha o maior número de empregados. (0,25)

SELECT TOP 1

            D.NUMERO,

            D.NOME,

            (SELECT COUNT(E.NUMERO) FROM EMPREGADO E WHERE E.DEPARTAMENTO = D.NUMERO) AS QTDE_EMP

FROM DEPARTAMENTO D

ORDER BY 3 DESC;

 

--20.Mostre os números, os nomes dos departamentos e os números dos empregados trabalhando em cada departamento que tenha o menor número de empregados. (0,25)

SELECT TOP 1

            D.NUMERO,

            D.NOME,

            (SELECT COUNT(E.NUMERO) FROM EMPREGADO E WHERE E.DEPARTAMENTO = D.NUMERO) AS QTDE_EMP

FROM DEPARTAMENTO D

ORDER BY 3;

 

--21.Mostre o número do funcionário, o sobrenome, o salário, o número do departamento e o salário médio de seu departamento para todos os funcionários. (0,25)

SELECT        E.NUMERO, 

                        E.SOBRENOME,

        E.SALARIO,

        D.NOME AS DEPARTAMENTO,

                        (SELECT AVG(X.SALARIO) FROM EMPREGADO X WHERE X.DEPARTAMENTO = E.DEPARTAMENTO) AS SAL_MED

FROM EMPREGADO E

INNER JOIN DEPARTAMENTO D ON E.DEPARTAMENTO = D.NUMERO;

 

--22.Mostre todos os funcionários nascidos no dia da semana em que foi correspondente ao número de empregados. (0,25)

SELECT E.APELIDO, DAY(E.NASCIMENTO) AS DIA FROM EMPREGADO E WHERE DAY(E.NASCIMENTO) = (SELECT COUNT(X.NUMERO) FROM EMPREGADO X);

 

--23.Crie uma visão geral de aniversários com base na data de nascimento dos empregados. Classifique os aniversários em ordem crescente. (0,25)

SELECT E.NUMERO, E.APELIDO, E.NASCIMENTO FROM EMPREGADO E ORDER BY E.NASCIMENTO ASC;

 

--24.Crie uma consulta composta para produzir uma lista de empregados que mostre percentuais de aumento, número dos empregados.

--Considerando que os empregados dos departamentos 10, 50 e 110 receberão aumento de 5%,

--os do departamento 60 receberão um aumento de 10%,

--os dos departamento 20 e 80 receberão um aumento de 15%

--e os do departamento 90 não receberão aumento. (0,25)

SELECT        E.NUMERO,

                        E.APELIDO,

        E.SALARIO,

        E.DEPARTAMENTO,

        CASE

            WHEN E.DEPARTAMENTO IN (10,50,110) THEN E.SALARIO + (E.SALARIO * .5)

            WHEN E.DEPARTAMENTO IN (60) THEN E.SALARIO + (E.SALARIO * .10)

            WHEN E.DEPARTAMENTO IN (20,80) THEN E.SALARIO + (E.SALARIO * .15)

            WHEN E.DEPARTAMENTO IN (90) THEN E.SALARIO

        END AS AUMENTO

FROM EMPREGADO E;

 

--25.Exiba a data corrente. (0,25)

SELECT TOP 1 GETDATE() FROM CARGO;

 

--26.Exiba o tempo em anos, mês e dias decorridos do nascimento até a data corrente. (0,25)

SELECT

            E.APELIDO,

    E.NASCIMENTO,

    DATEDIFF(YEAR, E.NASCIMENTO, GETDATE()) AS ANOS,

    DATEDIFF(MONTH, E.NASCIMENTO, GETDATE()) AS MESES,

    DATEDIFF(DAY, E.NASCIMENTO, GETDATE()) AS DIAS

FROM EMPREGADO E

 

--27.Crie uma consulta para exibir os seguintes itens referentes aos departamentos:

--o número seja maior que 80 com o salário total de cada cargo em um departamento. (0,25)

SELECT D.NUMERO, D.NOME AS DEPARTAMENTO, C.NOME AS CARGO, SUM(E.SALARIO) AS SAL_TOT

FROM EMPREGADO E

INNER JOIN DEPARTAMENTO D

ON E.DEPARTAMENTO = D.NUMERO

INNER JOIN CARGO C

ON E.CARGO = C.NUMERO

WHERE D.NUMERO > 80

GROUP BY D.NUMERO, D.NOME, C.NOME;

 

--28.Crie uma consulta para exibir os seguintes itens referentes aos departamentos:

--o número seja maior que 80 com o salário total. (0,25)

SELECT D.NUMERO, D.NOME AS DEPARTAMENTO, SUM(E.SALARIO) AS SAL_TOT

FROM EMPREGADO E

INNER JOIN DEPARTAMENTO D

ON E.DEPARTAMENTO = D.NUMERO

INNER JOIN CARGO C

ON E.CARGO = C.NUMERO

WHERE D.NUMERO > 80

GROUP BY D.NUMERO, D.NOME;

 

--29.Crie uma consulta para exibir os seguintes itens referentes aos departamentos:

--o número seja maior que 80 com o salário total de cada cargo, independente do departamento. (0,25)

SELECT SUM(E.SALARIO) AS SAL_TOT

FROM EMPREGADO E

INNER JOIN DEPARTAMENTO D

ON E.DEPARTAMENTO = D.NUMERO

INNER JOIN CARGO C

ON E.CARGO = C.NUMERO

WHERE D.NUMERO > 80;

 

--30.Crie uma consulta que exiba o agrupamento por departamento e por cargo. (0,25)

SELECT E.DEPARTAMENTO, E.CARGO

FROM EMPREGADO E

GROUP BY E.DEPARTAMENTO, E.CARGO;

 

--31.Crie uma consulta para exibir os 2 empregados que ganham mais. (0,25)

SELECT TOP 2 E.APELIDO, E.SALARIO

FROM EMPREGADO E

ORDER BY E.SALARIO DESC;

 

--32.Crie uma consulta para exibir o número, o sobrenome, o nascimento e o salário dos empregados com os nomes de seus respectivos gerentes. (0,25)

SELECT E.NUMERO, E.SOBRENOME AS EMPREGADO, E.NASCIMENTO, E.SALARIO, G.SOBRENOME AS GERENTE

FROM EMPREGADO E 

LEFT JOIN EMPREGADO G ON E.GERENTE = G.NUMERO;

 

--33.Crie uma consulta hierárquica para exibir o número do empregado, o número do gerente e o sobrenome de todos os empregados que estejam um nível abaixo do gerente. (0,25)

SELECT E.NUMERO, 'GERENTE: ' + E.SOBRENOME AS RECURSO, E.NASCIMENTO, E.SALARIO

FROM EMPREGADO E 

WHERE E.GERENTE IS NULL

UNION

SELECT E.NUMERO, 'EMPREGADO: ' + E.SOBRENOME AS RECURSO, E.NASCIMENTO, E.SALARIO

FROM EMPREGADO E 

WHERE E.GERENTE IS NOT NULL;

 

--34.Exibir os 2 primeiros registros de empregados cujos os salários sejam maiores que 1500. (0,25)

SELECT TOP 2 E.APELIDO, E.SALARIO FROM EMPREGADO E WHERE E.SALARIO > 1500;

 

--35.Exibir a subtração das tabelas Cargo e Departamento para os campos número e nome. (0,25)

SELECT C.NUMERO, C.NOME FROM CARGO C

EXCEPT

SELECT D.NUMERO, D.NOME FROM DEPARTAMENTO D;

 

--36.Exibir a intersecção das tabelas Cargo e Departamento para os campos número e nome. (0,25)

SELECT C.NUMERO, C.NOME FROM CARGO C

INTERSECT

SELECT D.NUMERO, D.NOME FROM DEPARTAMENTO D;

 

--37.Exibir a união das tabelas Cargo e Departamento para os campos número e nome. (0,25)

SELECT C.NUMERO, C.NOME FROM CARGO C

UNION

SELECT D.NUMERO, D.NOME FROM DEPARTAMENTO D;

 

--38.Exibir os empregados com os dados sobrenome, salário e número do departamento, com no mínimo uma pessoa subordinada a eles. (0,25)

SELECT E.NUMERO, E.SOBRENOME, E.DEPARTAMENTO FROM EMPREGADO E WHERE E.GERENTE IS NULL AND EXISTS (SELECT 'X' FROM EMPREGADO X WHERE X.GERENTE = E.NUMERO);

 

--39.Exibir o somatório de Salário agrupado por Departamento e Cargo dos empregados com Número de Departamento menor que 60, exibindo os subtotais de Departamento e Cargo. (0,25)

SELECT E.DEPARTAMENTO, E.CARGO, SUM(SALARIO) AS SOM_SAL

FROM EMPREGADO E

GROUP BY E.DEPARTAMENTO, E.CARGO WITH ROLLUP

 

--40.Quais os empregados possuem salário maior que o de João Abacateiro? (0,25)

SELECT E.PRIMEIRONOME + ' ' + E.SOBRENOME AS NOME, E.SALARIO FROM EMPREGADO E WHERE E.SALARIO > (SELECT X.SALARIO FROM EMPREGADO X WHERE X.PRIMEIRONOME = 'JOÃO' AND X.SOBRENOME = 'ABACATEIRO')

 

--Baseado no modelo abaixo responda as questões abaixo:

 

 

--1) A matrícula e o nome dos clientes com a idade superior a 45 anos. 

SELECT CLIENTE.ID_PE_MAT, PESSOA.VC_PE_NOM

FROM PESSOA INNER JOIN CLIENTE ON PESSOA.ID_PE_MAT = CLIENTE.ID_PE_MAT

WHERE (((Now()-[PESSOA].[DT_PE_NAS])>45));

 

--2) A matrícula dos técnicos cujos os nomes iniciem com A,F,G ou M. 

SELECT TECNICO.ID_PE_MAT

FROM PESSOA INNER JOIN TECNICO ON PESSOA.ID_PE_MAT=TECNICO.ID_PE_MAT

WHERE PESSOA.VC_PE_NOM Like 'A%' OR PESSOA.VC_PE_NOM Like 'F%' OR PESSOA.VC_PE_NOM Like 'G%' OR PESSOA.VC_PE_NOM Like 'M%';

 

--3) A quantidade de pessoas que possuem apenas uma localização. 

SELECT LOCALIZACAO.ID_PE_MAT, Count(LOCALIZACAO.ID_LO_COD) AS QTDE

FROM LOCALIZACAO

GROUP BY LOCALIZACAO.ID_PE_MAT

HAVING Count(LOCALIZACAO.ID_LO_COD)=1;

 

--4) A quantidade de gestores. 

SELECT Count(*) AS QTDE_GESTORES

FROM TECNICO

WHERE (((TECNICO.ID_TE_GER) Is Null));

 

--5) O nome e o sexo M se 0 e F se 1 para os departamentos técnico e comercial. 

SELECT PESSOA.VC_PE_NOM, NU_PE_SEX

FROM DEPARTAMENTO INNER JOIN (PESSOA INNER JOIN TECNICO ON PESSOA.ID_PE_MAT = TECNICO.ID_PE_MAT) ON DEPARTAMENTO.ID_DE_COD = TECNICO.ID_DE_COD

WHERE (((DEPARTAMENTO.VC_DE_DSC)="TÉCNICO")) OR (((DEPARTAMENTO.VC_DE_DSC)="COMERCIAL"));

 

--6) Os 2 maiores salários líquidos dos técnicos. 

SELECT TOP 2 TECNICO.NU_TE_SAL + TECNICO.NU_TE_GRT - TECNICO.NU_TE_DES AS LIQ

FROM TECNICO

ORDER BY 1 DESC;

 

--7) A média de salários base por departamento. 

SELECT DEPARTAMENTO.VC_DE_DSC, Avg(TECNICO.NU_TE_SAL) AS MédiaDeNU_TE_SAL

FROM DEPARTAMENTO INNER JOIN TECNICO ON DEPARTAMENTO.ID_DE_COD = TECNICO.ID_DE_COD

GROUP BY DEPARTAMENTO.VC_DE_DSC;

 

--8) A quantidade de ordens que foram canceladas e encerradas do ano corrente. 

SELECT COUNT(ANDAMENTO.ID_OS_NUM) AS QTDE

FROM ANDAMENTO

WHERE (YEAR(ANDAMENTO.DT_AN_STA)=2013) AND (ANDAMENTO.VC_AN_STA="ENCERRADAS" OR ANDAMENTO.VC_AN_STA="CANCELADAS");

 

--9) A matrícula e o nome dos técnicos sem cargo informado. 

SELECT TECNICO.ID_PE_MAT, PESSOA.VC_PE_NOM

FROM PESSOA INNER JOIN TECNICO ON PESSOA.ID_PE_MAT = TECNICO.ID_PE_MAT

WHERE (((TECNICO.ID_CA_COD) Is Null));

 

--10) A matrícula e o nome dos todos os técnicos e a descrição de seus departamentos caso exista a informação. 

SELECT TECNICO.ID_PE_MAT, PESSOA.VC_PE_NOM, DEPARTAMENTO.VC_DE_DSC

FROM DEPARTAMENTO RIGHT JOIN (PESSOA INNER JOIN TECNICO ON PESSOA.ID_PE_MAT = TECNICO.ID_PE_MAT) ON DEPARTAMENTO.ID_DE_COD = TECNICO.ID_DE_COD;

 

--11) A matrícula e o nome dos técnicos e a descrição de todos os departamentos caso exista a informação. 

SELECT TECNICO.ID_PE_MAT, PESSOA.VC_PE_NOM, DEPARTAMENTO.VC_DE_DSC

FROM DEPARTAMENTO LEFT JOIN (PESSOA RIGHT JOIN TECNICO ON PESSOA.ID_PE_MAT = TECNICO.ID_PE_MAT) ON DEPARTAMENTO.ID_DE_COD = TECNICO.ID_DE_COD;

 

--12) A soma dos salários base dos técnicos por cargo cujo o salário base seja maior que 2000. 

SELECT CARGO.VC_CA_DSC, Sum(TECNICO.NU_TE_SAL) AS SomaDeNU_TE_SAL

FROM CARGO INNER JOIN TECNICO ON CARGO.ID_CA_CAD=TECNICO.ID_CA_COD

WHERE (((TECNICO.NU_TE_SAL)>2000))

GROUP BY CARGO.VC_CA_DSC;

 

--13) A soma dos salários base dos técnicos por cargo cujo o valor da soma seja maior que 5000. 

SELECT CARGO.VC_CA_DSC, Sum(TECNICO.NU_TE_SAL) AS SomaDeNU_TE_SAL

FROM CARGO INNER JOIN TECNICO ON CARGO.ID_CA_CAD = TECNICO.ID_CA_COD

GROUP BY CARGO.VC_CA_DSC

HAVING Sum(TECNICO.NU_TE_SAL) > 5000;

 

--14) A soma dos salários líquidos dos técnicos por cargo cujo a gratificação seja maior que 1000 e a soma dos salários líquido seja maior que 3000.  

SELECT CARGO.VC_CA_DSC, Sum(TECNICO.NU_TE_SAL+TECNICO.NU_TE_GRT-TECNICO.NU_TE_DES) AS LIQ

FROM CARGO INNER JOIN TECNICO ON CARGO.ID_CA_CAD = TECNICO.ID_CA_COD

WHERE TECNICO.NU_TE_GRT > 1000

GROUP BY CARGO.VC_CA_DSC

HAVING Sum(TECNICO.NU_TE_SAL+TECNICO.NU_TE_GRT-TECNICO.NU_TE_DES) > 3000;

 

--15) Criar índice que não permita duplicidade para nomes dos cargos. 

CREATE UNIQUE INDEX IX01 ON CARGOS (VC_CA_NOM);

 

--16) Nomes das pessoas sem duplicidade. 

SELECT DISTINCT PESSOA.VC_PE_NOM

FROM PESSOA;

 

--17) Quantidade de OS abertas mas não atendidas no mês corrente. 

SELECT OS.DT_OS_ABE

FROM OS INNER JOIN ANDAMENTO ON OS.ID_OS_NUM <> ANDAMENTO.ID_OS_NUM

WHERE MONTH(OS.DT_OS_ABE) = MONTH(Now());

 

--18) A matrícula, o nome e as descrições dos departamentos e cargos dos técnicos. 

SELECT TECNICO.ID_PE_MAT, PESSOA.VC_PE_NOM, DEPARTAMENTO.VC_DE_DSC, CARGO.VC_CA_DSC

FROM CARGO INNER JOIN (DEPARTAMENTO INNER JOIN (PESSOA INNER JOIN TECNICO ON PESSOA.ID_PE_MAT = TECNICO.ID_PE_MAT) ON DEPARTAMENTO.ID_DE_COD = TECNICO.ID_DE_COD) ON CARGO.ID_CA_CAD = TECNICO.ID_CA_COD;

 

--19) A matrícula, o nome, os e-mails e fones dos clientes. 

SELECT CLIENTE.ID_PE_MAT, PESSOA.VC_PE_NOM, FONE.VC_FO_NUM, EMAIL.VC_EM_END

FROM ((PESSOA INNER JOIN CLIENTE ON PESSOA.ID_PE_MAT = CLIENTE.ID_PE_MAT) INNER JOIN FONE ON PESSOA.ID_PE_MAT = FONE.ID_PE_MAT) INNER JOIN EMAIL ON PESSOA.ID_PE_MAT = EMAIL.ID_PE_MAT;

 

--20) A matrícula, o nome, o fone, o e-mail do cliente da solicitação de OS=15. 

SELECT CLIENTE.ID_PE_MAT, PESSOA.VC_PE_NOM, FONE.VC_FO_NUM, EMAIL.VC_EM_END

FROM ((PESSOA INNER JOIN (CLIENTE INNER JOIN OS ON CLIENTE.ID_PE_MAT = OS.ID_CL_MAT) ON PESSOA.ID_PE_MAT = CLIENTE.ID_PE_MAT) INNER JOIN EMAIL ON PESSOA.ID_PE_MAT = EMAIL.ID_PE_MAT) INNER JOIN FONE ON PESSOA.ID_PE_MAT = FONE.ID_PE_MAT

WHERE (((OS.ID_OS_NUM)=15));

 

--21) O parecer da solicitação de número 25. 

SELECT ANDAMENTO.ID_OS_NUM, ANDAMENTO.VC_AN_PAR

FROM ANDAMENTO

WHERE (((ANDAMENTO.ID_OS_NUM)=25));

 

--22) O andamento da solicitação de número 30. 

SELECT ANDAMENTO.*

FROM ANDAMENTO

WHERE (((ANDAMENTO.ID_OS_NUM)=30));

 

--23) A(s) matrícula(s) e nome(s) do(s) técnico(s) que possuem o maior salário. 

SELECT TECNICO.ID_PE_MAT, PESSOA.VC_PE_NOM

FROM PESSOA INNER JOIN TECNICO ON PESSOA.ID_PE_MAT = TECNICO.ID_PE_MAT

WHERE NU_TE_SAL = (SELECT MAX(NU_TE_SAL) FROM TECNICO);

 

--24) O(s) nome(s) do(s) técnico(s) que mais realizaram atendimentos. 

SELECT TECNICO.ID_PE_MAT, PESSOA.VC_PE_NOM

FROM PESSOA INNER JOIN TECNICO ON PESSOA.ID_PE_MAT = TECNICO.ID_PE_MAT

WHERE TECNICO.ID_PE_MAT IN (SELECT TOP 2 TECNICO.ID_PE_MAT

FROM TECNICO INNER JOIN ANDAMENTO ON TECNICO.ID_PE_MAT = ANDAMENTO.ID_TE_MAT

GROUP BY TECNICO.ID_PE_MAT

ORDER BY Count(ANDAMENTO.ID_OS_NUM) DESC);

 

--25) A quantidade de caracteres existentes no nome das pessoas. 

SELECT LEN(PESSOA.VC_PE_NOM) AS QTDE

FROM PESSOA;

 

--26) A união dos cargos e departamentos ordenado por descrição decrescente. 

SELECT ID_CA_CAD, VC_CA_DSC

FROM CARGO

UNION SELECT ID_DE_COD, VC_DE_DSC

FROM DEPARTAMENTO;

 

--27) O menor salário líquido pago por departamento. 

SELECT DEPARTAMENTO.VC_DE_DSC, Min(TECNICO.NU_TE_SAL+TECNICO.NU_TE_GRT-TECNICO.NU_TE_DES) AS LIQ

FROM DEPARTAMENTO INNER JOIN TECNICO ON DEPARTAMENTO.ID_DE_COD = TECNICO.ID_DE_COD

GROUP BY DEPARTAMENTO.VC_DE_DSC;

 

--28) O maior salário líquido pago por departamento e por cargo. 

SELECT DEPARTAMENTO.VC_DE_DSC, CARGO.VC_CA_DSC, Max([TECNICO].[NU_TE_SAL]+[TECNICO].[NU_TE_GRT]-[TECNICO].[NU_TE_DES]) AS LIQ

FROM CARGO INNER JOIN (DEPARTAMENTO INNER JOIN TECNICO ON DEPARTAMENTO.ID_DE_COD = TECNICO.ID_DE_COD) ON CARGO.ID_CA_CAD = TECNICO.ID_CA_COD

GROUP BY DEPARTAMENTO.VC_DE_DSC, CARGO.VC_CA_DSC;

Apresentações


Introdução

BD-01 Apresentação.ppt


Modelo Relacional

BD-02 Introducao.ppt


SQL

DB-02a Introdução Modelo Relacional.ppt


Comandos

BD-03 SQL.ppt


Resumo

BD-04 SQL Comandos.ppt


SQL Injection

BD-05 SQL Resumo.ppt


Avaliação

BD-06 SQL Injection.ppt


Mapa Mental
Aula 1
Aula 2
Aula 3
Aula 4
Aula 5
Aula 6
Aula 7
Aula 8
Aula 9
Aula 10
Aula 11
Aula 12

Aulas de SQL

Exemplos de SQL

Exemplos de Utilização dos Comandos SQL

Gramática BNF para SQL ANSI

Guia de Referência do MySQL

Guia de Referência do Postgre

Guia de Referência do SQLServer

Lista de Exercícios de SQL

Manual de Referência do MySQL

Padrão da Linguagem SQL

Exercícios

Exercícios I

Exercícios II

Exercícios III

Exercícios IV

Exercícios V

Exercícios VI

Aulas

Scriptda Aula de 29 Abril 2014

Ferramentas

AdministraçãoFirebird

AdministraçãoMySQL

AdministraçãoOracle

AdministraçãoPostgre

AdministraçãoSQLServer

AdministraçãoSQLServer Studio

HeidiSQL

InstalaçãoFirebird

InstalaçãoMySQL

InstalaçãoOracle

InstalaçãoPostgre

InstalaçãoSQLServer

ModelagemDBDesigner

ModelagemERWin

MySQL Installer

MySQL Reference Manual

MySQLFront

e-Books

Introduçãoa Sistemas de Banco de Dados

MasteringOracle SQL

SQLGuia Prático

Referências

BEIGHLEY, Lynn. Use a Cabeça SQL, 2008.

CARVALHO, Rogério. SQL Guia Prático, 2007.

DATE, Christopher. Introdução a Sistemas de Banco de Dados, 2008.

HEUSER, Carlos. Projeto de Banco de Dados, 1998.

MISTRY, Ross. Introducing Microsoft SQL Server 2008 R2. Washington: Microsoft Press, 2010.

OLIVEIRA, Haley. Postgresql 8 Referencia. The PostgreSQL Global Development Group, 2005.

OLIVEIRA, Haley. Postgresql 8 Tutorial. The PostgreSQL Global Development Group, 2005.

ORACLE. MySQL 5.5 Reference Guide, 2011.

XAVIER, Fabrício. SQL dos Conceitos às Consultas Complexas, 2009.

Biografias

Christopher Date formou-se no Reino Unido pela High Wycombe Royal Grammar School em 1958 e recebeu o bacharelado em Matemática pela Universidade de Cambridge em 1962. Iniciou as atividades na área da computação como programador na Leo Computers Ltd em Londres onde também começou a lecionar. Em 1967 começou a trabalhar na IBM na Inglaterra também como programador e instrutor de computação, ficando entre os anos de 1969 e 1974 como o principal instrutor da área educacional da IBM. Saber mais.

Autor

Stricto Sensu com Mestrado em Engenharia de Software pelo Centro de Estudos e Sistemas Avançados do Recife (CESAR) com a dissertação Uso do Balanced Scorecard na Avaliação de Fábricas de Software utilizadas como Instrumento de Ensino de Tecnologia da Informação Baseado em Problemas Reais, Especialista em Gestão Pública pela Universidade de Pernambuco (UPE), Lato Sensu em Metodologias de Desenvolvimento pela União Brasileira de Tecnologia (UNIBRATEC), Bacharel em Sistemas de Informação pela Faculdade Integrada do Recife (FIR), Bacharelado em Ciência da Computação pela Universidade Católica de Pernambuco (UNICAP) e Instrução Técnica no Núcleo de Informática da Católica (NIC).
 
Iniciou as atividades profissionais em 1986 como Programador na ELÓGICA desenvolvendo para baixas (dBase,Clipper) e altas plataformas (COBOL,Natural), e como Analista de Sistemas passando pelas instituições CETEPE, FISEPE, ITECI, APPLY e estando hoje na Agência Estadual de Tecnologia da Informação (ATI) por concurso público, trabalhando como Analista em Gestão da Tecnologia da Informação e Comunicação, estando em exercício na Procuradoria Geral do Estado de Pernambuco (PGE), atuando como Coordenador da Área de Sistemas.
 
Desde 1992 trabalha também ministrando aulas em cursos técnicos em empresas como ITECI e IBRATEC. E desde 2006 é professor de cursos de graduação e pós-graduação em Computação nas instituições UNIBRATEC, FAREC, JOAQUIM NABUCO, FIR/ESTÁCIO, IFPE e UFRPE. Durante esses anos realizou diversos cursos de extensão como os de Análise de Ponto de Função, Administração de Redes, ASP. NET, CSharp, Oracle, Análise Orientada a Objetos, Análise Estruturada, Delphi, SQLWindows, Adabas, Natural e muitos outros. Possuindo experiência na área de Engenharia de Software nas disciplinas de Teoria da Computação, Sistemas Colaborativos, Desenvolvimento Orientação a Objetos e Estrutural para aplicações Web e Desktop, Metodologias de Desenvolvimento como RUP e Scrum, e Gestão de Projetos com PMBOK e SWEBOK. Trabalha atualmente na coordenação de projetos e com educação presencial e a distância, ministrando aulas e realizando pesquisas em ambientes virtuais de ensino e aprendizado e linguagens de programação.

 

br.linkedin.com/in/alvarofpinheiro/
https://www.facebook.com/alvarofpinheiroaulas

https://sites.google.com/site/alvarofpinheiro/


counter free
Comments