Hélio Engholm Jr

Especializado em Engenharia de Software

Apostila 2 SQL AVANÇADO - Instrutor HEngholmJr 

1.             Declarações básicas do SQL 

SP_HELP

Fornece um relatório dos objetos de um database.

SP_HELPDB

Fornece um relatório dos databases existentes.

SP_HELPTEXT

Lista o texto correspondente a uma stored procedure e de outros objetos.

SP_HELPSQL

Exibe informações a respeito de declarações (comandos) SQL, stored procedures e outros tópicos.

2            Uma seqüência básica de trabalho 

2.1        CREATE DATABASE 

Sintaxe:

            CREATE DATABASE database_name

            [ON {DEFAULT | database_device} [= size]

                        [, database_device [= size]]…]

            [LOG ON database_device [= size]

                        [, database_device [= size]…]

            [FOR LOAD]

OBS.: Tamanhos em megabytes

Exemplos:

            1. CREATE DATABASE pubs (o tamanho default é 2 Mb)

     2. CREATE DATABASE newpubs

     ON default = 256

     3. CREATE DATABASE newdb

     ON default = 50, newdata = 25

     4. CREATE DATABASE library

     ON library_dev1 = 10

2.2        CREATE TABLE 

 

Tipos de dados

Tipos de dados supridos pelo sistema

Binary

binary[(n)], varbinary[(n)]

Character

char[(n)], varchar[(n)]

Date and time

datetime, smalldatetime

Exact numeric

decimal[(p[,s])]

Approximate numeric

float[(n)], real

Integer

int, smallint, tinyint

Monetary

money, smallmoney

Special

bit, timestamp, user-defined datatypes

Text and imagem

text, imagem

Synonyms

binary, varying for varbinary, character for char, character, varying for varchar, dec for decimal, integer for int, double precision for float

Crie as tabelas abaixo:

CREATE TABLE cliente

    (

    cliente  numeric  (8,0) not null PRIMARY KEY,

    nome     varchar  (60)  null,

    telefone varchar  (20)  null

    )

CREATE TABLE pedidos

    (

    numero int NOT NULL ,

    cliente numeric(8, 0) NOT NULL ,

    telefone int NOT NULL ,

    PRIMARY KEY

        (

             cliente,

             numero

        ),

        FOREIGN KEY

        (

             cliente

        )

        REFERENCES cliente

        (

             cliente

        )

    )

2.3        INSERT 

Para inserirmos dados em uma tabela, devemos informar qual é a tabela, quais os campos que estamos inserindo e quais são seus valores.

INSERT cliente (cliente, nome, telefone)

    values (1001,’João’,’445-0988′)

INSERT cliente (cliente, nome, telefone)

    values (1002,’Alberto’,’465-9887′)

INSERT cliente (cliente, nome, telefone)

    values (1003,’Maria’,’789-9877′)

INSERT cliente (cliente, nome, telefone)

    values (1004,’Sônia’,null)

A ordem dos campos pode ser diferente da ordem que estes possuem na tabela:

INSERT cliente (nome, cliente, telefone)

    values (‘Carlos’,1005,null)

Caso existam valores para todos os campos, podemos omitir seus nomes.

INSERT cliente

    values (1006,’Viu só?’,’999-0000′)

2.4        SELECT 

Através do comando select, recuperamos os dados existentes no banco, de acordo com os critérios desejados.

Sintaxe:

            SELECT[ALL|DISTINCT] select_list

              [INTO[ new_table_name ]]

            [FROM{table_name|view_name}[optimizer_hints)]

              [[,{table_name2|view_name2}{optimizer_hints)]

              [...,{table_name16|view_name16}[(optimizer_hints)]]]

            [WHERE clause}

            [GROUP BY clause]

            [HAVING clause]

            [ORDER BY clause]

            [COMPUTE clause]

            [FOR BROWSE]

SELECT nome, telefone FROM cliente

SELECT * FROM cliente WHERE telefone LIKE ‘4%’

2.5        UPDATE 

Utilizado para modificar dados já cadastrados. Pode ser usado para atualizar todas as linhas ou para atualizar linhas que correspondam a determinados critérios.

UPDATE cliente SET telefone = ‘000-1111’

2.6        DELETE 

Para apagar linhas de uma tabela devermos especificar critérios, através da cláusula WHERE; caso contrário todas as linhas da tabela serão apagadas. A tabela, porém, não será eliminada. Continuará existindo, porém vazia.

DELETE cliente

    WHERE cliente = 1006

2.7        DROP TABLE 

Este procedimento irá remover a tabela completamente, não existindo nenhum procedimento de “recovery”. Portanto, assegure-se de que a tabela em questão realmente não é mais necessária, ou, pelo menos, faça um back up do banco antes.

Ao apagar uma tabela, saiba que os relacionamentos por ventura com ela existente impedirão sua deleção. Por isso, você deverá começar a apagar as tabelas desde as “filhas”.

DROP TABLE cliente

Msg 3726, Level 16, State 1

Could not drop object ‘cliente’.

It is being referenced by a foreign key constraint.

Assim, para apagar a tabela cliente, antes será necessário apagarmos a tabela pedidos.

DROP TABLE pedidos

DROP TABLE cliente

3 OUTROS 

3.1           OPERADORES ARITIMÉTICOS 

Operação

tipos de dados que podem usar esta operação

+

int, smallint, tinyint, numeric, decimal, float, real, money e smallmoney

-

int, smallint, tinyint, numeric, decimal, float, real, money e smallmoney

/

int, smallint, tinyint, numeric, decimal, float, real, money e smallmoney

*

int, smallint, tinyint, numeric, decimal, float, real, money e smallmoney

%

int, smallint e tinyint

Sintaxe

            { constant | column_name | function | (subquery) }

             [{ arithmetic_operator | bitwise_operator | string_operator }

              { constant | column_name | function | (subquery) }...]

            SELECT price, (price * 1.1), title

       FROM titles

3.2            MANIPULAÇÃO DE DADOS NUMÉRICOS 

  

Função

Parâmetros

ABS

(numeric_expr)

ACOS,ASIN,ATAN,ATN2

(float_expr)

COS,SIN,COT,TAN

(float_expr)

CEILING

(numeric_expr)

DEGREES

(numeric_expr)

EXP

(float_expr)

FLOOR

(numeric_expr)

LOG

(float_expr)

LOG10

(float_expr)

PI

()

POWER

(numeric_expr,y)

RADIANS

(numeric_expr)

RAND

([seed])

ROUND

(numeric_expr,length)

SIGN

(numeric_expr)

SQRT

(float_expr)

            SELECT title_id,

              ROUND(price*royalty/100,0)

              FROM titles

3.3            MANIPULANDO CARACTERES DE DADOS 

Função

Parâmetros

+

(expression expression)

ASCII

(char_expr)

CHAR

(integer_expr)

CHARINDEX

(‘pattern’, expression)

DIFFERENCE

(char_expr1,char_expr2)

LOWER

(char_expr)

LTRIM

(char_expr)

PATINDEX

(‘%patern%’, expression)

REPLICATE

(char_expr, integer_expr)

REVERSE

(char_expr)

RIGHT

(char_expr,integer_expr)

RTRIM

(char_expr)

SOUNDEX

(char_expr)

SPACE

(integer_expr)

STR

(float_expr[,lenght[,decimal]])

STUFF

(char_expr1, start, lenght, char_expr2)

SUBSTRING

(expression, start, lenght)

UPPER

(char_expr)

            SELECT au_lname + ‘, ‘ +

              Substring (au_fname,1,1) + ‘.’,

              au_id

              FROM authors

3.4            MANIPULANDO DADOS DE DATA E TEMPO 

FUNÇÃO

PARAMETROS

DATEADD

(datepart, number, date)

DATEDIFF

(datepart, date1, date2)

DATENAME

(datepart, date)

DATEPART

(datepart, date)

GETDATE

()

Tipos de data

Abreviações

Valores aceitos

year

yy

1752-9999

quarter

qq

1-4

mont

mm

1-12

day of year

dy

1-366

day

dd

1-31

week

wk

0-51

weekday

dw

1-7 (1 é domingo)

hour

hh

0-23

minute

mi

0-59

second

ss

0-59

millisecond

ms

0-999

            SELECT

              DATEDIFF (MONTH, pubdate, GETDATE())

              FROM Titles

3.5            FUNÇÕES DE SISTEMA 

FUNÇÃO

PARÂMETROS

COALESCE

(expression1,expression2,…expressionN)

COL_NAME

(‘table_id’, column_id)

COL_LENGHT

(‘table_name’, ’column_name’)

DATALENGHT

(‘expression’)

DB_ID

([‘databasename’])

DB_NAME

([database_id])

GETANSINULL

([‘databasename’])

HOST_ID

()

HOST_NAME

()

IDENT_INCR

(‘table_name’)

IDENT_SEED

(‘table_name’)

INDEX_COL

(‘table_name’, index_id, key_id’)

ISNULL

(expression, value)

NULLIF

(expression1,expression2)

OBJECT_ID

(‘object_name’)

OBJECT_NAME

(oblect_id)

STATS_DATE

(table_id,index_id)

SUSER_ID

([‘server_user_id’])

SUSER_NAME

([server_user_id])

USER_ID

([‘username’])

USER_NAME

([user_id])

            SELECT length = DATALENGTH(pub_name), pub_name

        FROM publishers

Resultado:    length   pub_name

              14       New Moon Books

              16       Binnet & Hardley

              20       Algodata Infosystems

              21       Five Lakes Publishing

              (4 row(s) affected)

3.6            CONVERSÃO DE DADOS 

  

CONVERT(datatype[(length)],expression[,style])

COM SEC.

SEC.

STANDARD

FORMATO DE SAIDA DOS DADOS

1

101

USA

mm/dd/yy

2

102

ANSI

yy.mm.dd

3

103

britânico

dd/mm/yy

10

110

USA

mm-dd-yy

12

112

ISO

yymmdd

            SELECT ‘Title Code’ = pub_id +

        UPPER(SUBSTRING(type,1,3)) +

        SUBSTRING(CONVERT(CHAR(4),DATEPART(YY,pubdate)),3,3)

     FROM titles

Resultado:    Title Code

              1389BUS91

              0736BUS91

              1389BUS91

              .

              .

              .

              (18 row(s) affected)

3.7        ESCOLHA DE LINHAS BASEADA EM COMPARAÇÕES 

SELECT select_list

            FROM table_list

            WHERE expression comparison_operator expression

Operadores de comparação:

·      (=,>,<,<=,>=,<>,!=,!<, e !>)

SELECT au_lname, city

FROM authors

WHERE state = ’CA’

3.8        ESCOLHA DE LINHAS BASEADA EM AMPLITUDES 

  

SELECT select_list

            FROM table_list

            WHERE expression [NOT] BETWEEN expression AND expression

SELECT pubdate, title

     FROM titles

     WHERE pubdate BETWEEN ’1/1/91’ AND ’12/31/91’

3.9        ESCOLHA DE LINHAS BASEADA EM LISTAS 

SELECT select_list

            FROM table_list

            WHERE expression [NOT] LIKE ‘string’

Wildcard

Descrição

%

Qualquer string de zero ou mais caracteres

_

Qualquer caractere único

[]

Qualquer caractere único com amplitude ou set especificado

[^]

Qualquer caractere único com amplitude ou set não especificado

SELECT title, type

     FROM titles

     WHERE type IN (’mod_cook’, ’trad_cook’)

3.10        ESCOLHA DE LINHAS BASEADA EM VALORES DESCONHECIDOS 

  

SELECT select_list

            FROM table_list

            WHERE column_name IS [NOT] NULL

SELECT title

     FROM titles

     WHERE price IS NULL

3.11        ESCOLHA DE LINHAS BASEADA EM BUSCA DE VARIOS ARGUMENTOS 

SELECT select_list

            FROM table_list

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

SELECT title_id, title, pub_id, price, pubdate

     FROM titles

     WHERE (title LIKE ‘T%’ OR pub_id = ‘0877’) AND

     (price > $16.00)

3.12        ELIMINANDO DUPLICATAS 

SELECT [ALL|DISTINCT] select_list

            FROM table_list

            WHERE search_conditions

SELECT DISTINCT city, state

     FROM authors

3.13        CLASSIFICANDO RESULTADOS 

SELECT column_name [,column_name...]

            FROM table_list

            [ORDER BY column_name|select_list_number|expression

            [ASC|DESC][,column_name|select_list_number|expression

            [ASC|DESC]..]

SELECT pub_id, type, price, title

     FROM titles

     ORDER BY type, price DESC

4            RECUPERAÇÃO DE DADOS - TóPICOS AVANÇADOS 

  

4.1        JOIN 

SELECT column_name, column_name [,column_name...]

            FROM table_name, table_name [,table_name...]

            WHERE table_name, column_name, join_operator, table_name,

            column_name

Join operators:

·      (=,>,<,<=,>=,<>,!=,!<,!>, =*, *=)

·      *=  à outer join

4.2        Natural JOIN 

  

SELECT publishers.pub_id, publishers.pub_name,

     publishers.state, authors . *

     FROM publishers, authors

     WHERE publishers.city = authors.city

4.3        Eqüijoin 

  

SELECT *

     FROM authors, publishers

     WHERE authors.city = publishers.city

4.4        JOINS com mais de duas Tabelas 

  

SELECT stor_name, qty, title

     FROM titles, stores, sales

     WHERE titles.title_id = sales.title_id

     AND stores.stor_id = sales.stor_id

4.5        Auto JOINS 

SELECT au1.au_fname, au.au_lname,

     au2.au_fname, au2.au_lname

     FROM authors au1, authors au2

     WHERE au1.city = ‘Oakland’

     AND au1.sate = ‘CA’

     AND au1.zip = au2.zip

     AND au1.au_id < au2.au_id

4.6        Outer JOINS 

  

SELECT titles.title_id, title, qty

     FROM titles, sales

     WHERE titles.title_id *= sales.tilte_id

4.7            CRIANDO TRIGGERS 

CREATE TRIGGER [owner.] trigger_name

     ON [owner.]table_name

     FOR {INSERT|UPDATE}

     AS

     IF UPDATE (column_name)…]

     [{AND|OR} UPDATE}

     sql_statements}

4.8        INSERT TRIGGER 

  

CREATE TRIGGER loan_insert

     ON loan

     FOR INSERT

     AS

              UPDATE copy

              SET on_loan = ’y’

              FROM copy, inserted

              WHERE copy.isbn = insertedd.isbn

              AND copy.copy_no = inserted.copy_no

4.9        DELETE TRIGGER 

  

CREATE TRIGGER member_delete

     ON member FOR DELETE

     AS

              IF (SELECT COUNT (*)

                   FROM loan, deleted

                   WHERE loan.member_no = deleted.member_no) > 0

                   BEGIN

                   PRINT ’Transaction cannot be processed.’

                   PRINT ’This member still has books on loan.’

                   ROLLBACK TRANSACTION

                   END

              ELSE

                   DELETE reservation

                   FROM reservation, deleted

                   WHERE reservation.member_no = deleted.member_no

4.10        UPDATE TRIGGER 

CREATE TRIGGE member_update

     ON member

     FOR UPDATE

     AS

     IF UPDATE (member_no)

     BEGIN

              RAISEERROR (Transaction cannot be processed.\

              ***** Member number cannot be modified.’, 10, 1)

              ROLLBACK TRANSACTION

     END

4.11            BULK COPY PROGRAM (BCP) 

 

            bcp [[database_name.]owner.]table_name {in|out} datafile

     [/m maxerrors] [/f formatfile] [/e errfile]

     [/F firstrow] [/L lastrow] [/b batchsize]

     [/n] [/c] [/E]

     [/t field_term] [/r row_term]

     [/i inputfile] [/o outputfile]

     [/U login_id] [/P password] [/S servername] [/v] [/a packet_size]

Exemplo:

bcp sau05..PROG in a:PROG.txt -U “usuário” -P “senha”

-S graciosa




+ Artigos