{"id":141,"date":"2014-11-12T23:17:19","date_gmt":"2014-11-12T23:17:19","guid":{"rendered":"https:\/\/www.uniessa.hiperlogic.com.br\/?p=141"},"modified":"2014-11-12T23:33:06","modified_gmt":"2014-11-12T23:33:06","slug":"sqldml","status":"publish","type":"post","link":"https:\/\/www.uniessa.hiperlogic.com.br\/?p=141","title":{"rendered":"SQL\/DML"},"content":{"rendered":"

Os comandos da Linguagem de Modelagem de Dados do SQL s\u00e3o respons\u00e1veis por:<\/span><\/p>\n

\u2013 Comandos de inser\u00e7\u00e3o de tuplas em tabelas<\/span><\/p>\n

\u2013 Comandos de altera\u00e7\u00e3o e supress\u00e3o de tuplas<\/span><\/p>\n

\u2013 Comandos de consulta (b\u00e1sicos e complexos)<\/span><\/p>\n

Def. A SQL\/DML(Data Manipulation Language) \u00e9 um subconjunto da SQL usada para recuperar, inserir, atualizar e suprimir dados de tabelas do BD.<\/span><\/p>\n

\u2022 INSERT \u2013 inser\u00e7\u00e3o de linhas;<\/span><\/p>\n

\u2022 DELETE \u2013 supress\u00e3o de linhas;<\/span><\/p>\n

\u2022 UPDATE \u2013 atualiza\u00e7\u00e3o de dados;<\/span><\/p>\n

\u2022 SELECT \u2013 recupera\u00e7\u00e3o de tabelas;<\/span><\/p>\n

OBS: ser\u00e3o mostradas caracter\u00edsticas do padr\u00e3o SQL implementadas pelo PostgreSQL, omitindo funcionalidades adicionais e destacando eventuais omiss\u00f5es.<\/span><\/p>\n

Insert<\/span><\/p>\n

INSERT INTO tabela [ ( coluna [, …] ) ]<\/span><\/p>\n

{ DEFAULT VALUES <\/span><\/p>\n

|VALUES ( { express\u00e3o | DEFAULT } [, …] ) <\/span><\/p>\n

[, …] <\/span><\/p>\n

| comando-select<\/span><\/p>\n

}<\/span><\/p>\n

Exemplo 1<\/span><\/p>\n

INSERT INTO tabela VALUES (express\u00e3o [, …] )<\/span><\/p>\n

INSERT INTO employee<\/span><\/p>\n

VALUES (‘John’, ‘B’, ‘Smith’, ‘123456789’,<\/span><\/p>\n

DATE ‘1965-01-09’, ‘731 Fondren, Houston, TX’,<\/span><\/p>\n

‘M’, 30000, ‘333445555’, 5);<\/span><\/p>\n

Compatibilidade de tipos: <\/em><\/span><\/p>\n

employee (fname VARCHAR (15) NOT NULL,<\/span><\/p>\n

minit CHAR, lname VARCHAR (15) NOT NULL,<\/span><\/p>\n

ssn CHAR(9) PRIMARY KEY, bdate DATE,<\/span><\/p>\n

address VARCHAR(30), sex CHAR CHECK (sex IN (\u2019M\u2019, \u2019F\u2019)),<\/span><\/p>\n

salary DECIMAL(10,2), superssn CHAR(9), dno INT NOT NULL) <\/span><\/p>\n

Exemplo 2<\/span><\/p>\n

INSERT INTO tabela ( coluna [, …] ) <\/span><\/p>\n

VALUES (express\u00e3o [, …] ) <\/span><\/p>\n

INSERT INTO employee(fname, minit, lname, ssn, dno)<\/span><\/p>\n

VALUES (‘John’, ‘B’, ‘Smith’, ‘123456789’, 5);<\/span><\/p>\n

Exemplo 3<\/span><\/p>\n

INSERT INTO tabela comando-select<\/p>\n

\n

INSERT INTO works_on<\/p>\n

SELECT ssn, pnumber, 0<\/p>\n

FROM employee, project;<\/p>\n

Delete<\/span><\/p>\n

DELETE FROM tabela [ [ AS ] alias ]<\/p>\n

[ WHERE condi\u00e7\u00e3o <\/p>\n

| WHERE CURRENT OF cursor_name ]<\/p>\n

DELETE FROM employee WHERE ssn = ‘123456789’<\/p>\n

OBS: o uso de cursores ser\u00e1 visto posteriormente<\/p>\n

Update<\/span><\/p>\n

UPDATE tabela [ [ AS ] alias ]<\/p>\n

SET {coluna = { expresss\u00e3o | DEFAULT } <\/p>\n

| ( coluna [, …] ) = <\/p>\n

( { express\u00e3o | DEFAULT } [, …] )<\/p>\n

} [, …]<\/p>\n

[ WHERE condi\u00e7\u00e3o<\/p>\n

| WHERE CURRENT OF cursor_name ]<\/p>\n

OBS: o uso de cursores ser\u00e1 visto posteriormente<\/p>\n

Update Exemplo 1<\/span><\/p>\n

UPDATE employee <\/p>\n

SET address = ‘Av. Joao Naves de Avila, 2121’,<\/p>\n

salary = salary * 1.5<\/p>\n

WHERE ssn = ‘123456789’<\/p>\n

Update Contra-Exemplo<\/span><\/p>\n

UPDATE works_on<\/p>\n

SET (pno, hours) =<\/p>\n

(SELECT pnumber, 10 <\/p>\n

FROM project WHERE pnumber = 1)<\/p>\n

WHERE essn=’123456789′;<\/p>\n

OBS: o padr\u00e3o permite associa\u00e7\u00e3o de uma lista de atributos com uma tupla resultante da sa\u00edda de uma consulta, mas isto n\u00e3o foi implementado pelo PostgreSql 8.3, onde as express\u00f5es devem ser independentes. Logo o comando acima n\u00e3o funciona no PostgreSql 8.3 (teste isso nas vers\u00f5es mais recentes)<\/p>\n

Update Exemplo 2<\/span><\/p>\n

UPDATE works_on<\/p>\n

SET (pno, hours) = (1, 10)<\/p>\n

WHERE essn=’123456789′;<\/p>\n

Select<\/span><\/p>\n

SELECT [ALL | DISTINCT] <\/p>\n

* | express\u00e3o [ AS nome_saida ] [, …]<\/p>\n

FROM item_from [, …] <\/p>\n

[ WHERE condi\u00e7\u00e3o ]<\/p>\n

[ GROUP BY express\u00e3o [, …] ]<\/p>\n

[ HAVING condi\u00e7\u00e3o [, …] ]<\/p>\n

[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]<\/p>\n

[ ORDER BY express\u00e3o [ ASC | DESC | USING operador ] <\/p>\n

[ NULLS { FIRST | LAST } ] [, …] ]<\/p>\n

[ FOR { UPDATE | SHARE } <\/p>\n

[ OF nome_tabela [, …] ] [ NOWAIT ] […] ]<\/p>\n

\u2013Consultas b\u00e1sicas e complexas<\/span><\/p>\n

Considerando os diversos par\u00e2metros do comando SELECT, <\/p>\n

para efeito did\u00e1tico, vamos dividir nosso estudo em:<\/p>\n

\uf06c consultas b\u00e1sicas: na condi\u00e7\u00e3o do WHERE n\u00e3o existe <\/p>\n

outro SELECT<\/p>\n

\uf06c consultas complexas: s\u00e3o consultas aninhadas, onde na <\/p>\n

condi\u00e7\u00e3o do SELECT existe outra cl\u00e1usula SELECT.<\/p>\n

SELECT-FROM-WHERE<\/span><\/p>\n

Formato de comando SELECT para consultas b\u00e1sicas:<\/p>\n

SELECT lista-de-atributos<\/p>\n

FROM lista-de-tabelas<\/p>\n

WHERE condi\u00e7\u00e3o<\/p>\n

OBS:<\/p>\n

\u2013 condi\u00e7\u00f5es sem cl\u00e1usula SELECT;<\/p>\n

\u2013 os exemplos a seguir seguem a numera\u00e7\u00e3o de EN e est\u00e3o <\/p>\n

baseados no BD company;<\/p>\n

Restri\u00e7\u00e3o + Proje\u00e7\u00e3o<\/span><\/p>\n

QO) Listar a data de nascimento e o endere\u00e7o dos <\/p>\n

empregados com nome : John B. Smith.<\/p>\n

SELECT bdate, address<\/p>\n

FROM employee<\/p>\n

WHERE fname=\u2019John\u2019 <\/p>\n

AND minit=\u2019B\u2019 <\/p>\n

AND lname=\u2019Smith\u2019;<\/p>\n

Restri\u00e7\u00e3o + Proje\u00e7\u00e3o + Jun\u00e7\u00e3o<\/span><\/p>\n

Q1) Listar o nome e endere\u00e7o dos empregados que trabalham <\/p>\n

no departamento \u2019Research<\/p>\n

SELECT fname, minit, lname, address<\/p>\n

FROM employee, department<\/p>\n

WHERE dno=dnumber AND dname=\u2019Research\u2019<\/p>\n

Jun\u00e7\u00e3o com duas condi\u00e7\u00f5es<\/span><\/p>\n

Q2) Para todo projeto localizado em \u2019Stafford\u2019, listar o <\/p>\n

n\u00famero do projeto, o n\u00famero do departamento que o <\/p>\n

controla e o \u00faltimo nome, endere\u00e7o e data de nascimento <\/p>\n

do gerente do departamento.<\/p>\n

SELECT pnumber, dnum, lname, address, bdate<\/p>\n

FROM project, department, employee<\/p>\n

WHERE plocation=\u2019Stafford\u2019 <\/p>\n

AND dnum=dnumber <\/p>\n

AND ssn=mgrssn<\/p>\n

Ambiguidade de nomes de atributos<\/span><\/p>\n

Suponha que DNUMBER e NAME s\u00e3o os nomes dos <\/p>\n

atributos DNO e LNAME em EMPLOYEE, <\/p>\n

respectivamente. Al\u00e9m disso, suponha que NAME \u00e9 o <\/p>\n

nome do atributo DNAME em DEPARTMENT.<\/p>\n

Ent\u00e3o:<\/p>\n

employee(fname, minit, name, ssn, bdate, address, sex, <\/p>\n

salary, superssn, dnumber)<\/p>\n

department(name, dnumber, mgrssn, mgrstartdate)<\/p>\n

Qualificando atributos<\/span><\/p>\n

Q1a) Listar o nome e endere\u00e7o dos empregados que <\/p>\n

trabalham no departamento \u2019Research\u2019 considerando os <\/p>\n

esquemas abaixo<\/p>\n

employee(fname, minit, name, ssn, bdate, address, sex, <\/p>\n

salary, superssn, dnumber)<\/p>\n

department(name, dnumber, mgrssn, mgrstartdate)<\/p>\n

SELECT fname, minit, employee.name<\/p>\n

FROM employee, department<\/p>\n

WHERE employee.dnumber=department.dnumber <\/p>\n

AND department.name=\u2019Research\u2019<\/p>\n

Apelidos de Tabelas (Renomea\u00e7\u00e3o)<\/span><\/p>\n

Q8) Para cada empregado, liste o seu primeiro e o seu \u00faltimo <\/p>\n

nome acompanhados do \u00faltimo nome de seu supervisor.<\/p>\n

SELECT e.fname, e.lname, s.lname<\/p>\n

FROM employee AS e, employee AS s<\/p>\n

WHERE e.superssn=s.ssn<\/p>\n

Q1b’) Listar o nome e o endere\u00e7o dos empregados que <\/p>\n

trabalham no departamento \u2019Research\u2019 considerando as <\/p>\n

novas tabelas employee e department e usando apelidos<\/p>\n

SELECT fname, minit, e.name, address<\/p>\n

FROM employee e, department d<\/p>\n

WHERE e.dnumber=d.dnumber <\/p>\n

AND d.name=\u2019Research\u2019<\/p>\n

Exemplo Q1c\u2013Omitindo WHERE e uso do * para consulta sem proje\u00e7\u00e3o<\/span><\/p>\n

Qlc’) Listar todos os valores de atributos de todos os empregados<\/p>\n

SELECT * FROM employee<\/p>\n

Produto Cartesiano<\/span><\/p>\n

Q1Ob) Listar o produto cartesiano de empregados e departamentos<\/p>\n

SELECT *<\/p>\n

FROM employee, department<\/p>\n

Tabela x rela\u00e7\u00e3o\/\/ multset ou bags x set \/\/ Cl\u00e1usula DISTINCT<\/span><\/p>\n

Q11a) Listar todos os sal\u00e1rios distintos<\/p>\n

SELECT DISTINCT salary<\/p>\n

FROM employee<\/p>\n

Cl\u00e1usula UNION<\/span><\/p>\n

Q4) Listar todos os n\u00fameros de projetos que envolvam um<\/p>\n

empregado cujo \u00faltimo nome \u00e9 \u2019Smith\u2019 sendo que o <\/p>\n

empregado deve sertrabalhador ou gerente do <\/p>\n

departamento que controla o projeto.<\/p>\n

(SELECT DISTINCT pnumber<\/p>\n

FROM project, department, employee<\/p>\n

WHERE dnum=dnumber AND msgrssn=ssn AND lname=\u2019Smith\u2019)<\/p>\n

UNION<\/p>\n

(SELECT DISTINCT pnumber<\/p>\n

FROM works_on, employee<\/p>\n

WHERE essn=ssn AND lname=\u2019Smith\u2019)<\/p>\n

Cl\u00e1usula LIKE<\/span><\/p>\n

Q12) Listar o nome de todo empregado cujo endere\u00e7o est\u00e1 <\/p>\n

em Houston, Texas<\/p>\n

SELECT fname, minit, lname<\/p>\n

FROM employee<\/p>\n

WHERE address LIKE \u2019%Houston%TX%\u2019<\/p>\n

Like usando underline _<\/span><\/p>\n

Q12a) Listar o nome de todos os empregados nascidos na <\/p>\n

d\u00e9cada de 50<\/p>\n

SELECT fname, minit, lname<\/p>\n

FROM employee<\/p>\n

WHERE bdate LIKE \u2019_ _ 5%\u2019<\/p>\n","protected":false},"excerpt":{"rendered":"

Os comandos da Linguagem de Modelagem de Dados do SQL s\u00e3o respons\u00e1veis por: \u2013 Comandos de inser\u00e7\u00e3o de tuplas em tabelas \u2013 Comandos de altera\u00e7\u00e3o e supress\u00e3o de tuplas \u2013 Comandos de consulta (b\u00e1sicos e complexos) Def. A SQL\/DML(Data Manipulation Language) \u00e9 um subconjunto da SQL usada para recuperar, inserir, atualizar e suprimir dados de […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4],"tags":[],"class_list":["post-141","post","type-post","status-publish","format-standard","hentry","category-bd"],"_links":{"self":[{"href":"https:\/\/www.uniessa.hiperlogic.com.br\/index.php?rest_route=\/wp\/v2\/posts\/141","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.uniessa.hiperlogic.com.br\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.uniessa.hiperlogic.com.br\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.uniessa.hiperlogic.com.br\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.uniessa.hiperlogic.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=141"}],"version-history":[{"count":1,"href":"https:\/\/www.uniessa.hiperlogic.com.br\/index.php?rest_route=\/wp\/v2\/posts\/141\/revisions"}],"predecessor-version":[{"id":142,"href":"https:\/\/www.uniessa.hiperlogic.com.br\/index.php?rest_route=\/wp\/v2\/posts\/141\/revisions\/142"}],"wp:attachment":[{"href":"https:\/\/www.uniessa.hiperlogic.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=141"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.uniessa.hiperlogic.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=141"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.uniessa.hiperlogic.com.br\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}