Sesion 3 2014-2015.pdf

Prctiques de BD. Curs 2014-15

Escola dEnginyeria. Universitat Autnoma de Barcelona.

Prctiques de Bases de Dades. Sessi 3 Curs 2014-15 Enginyeria Informtica. Escola dEnginyeria (seu Bellaterra). UAB

Professors:

Jorge Bernal ([email protected])

Oriol Jaumandreu ([email protected])

Carles Snchez ([email protected])

Antonio Esteban ([email protected])

C. Alejandro Parraga ([email protected])

Dbora Gil ([email protected])

Oriol Ramos ([email protected])

Objectius:

1. Introducci al client SQL Developer d'ORACLE.

2. Gesti de bases de dades: fer una aproximaci a la tecnologia client-servidor utilitzant ORACLE.

3. Manipulaci de dades: fer consultes simples en SQL

4. Manipulaci de dades: fer consultes complexes en SQL

5. Manipulaci de dades: fer insercions, modificacions i eliminacions

Documentaci necessria:

1. Apunts de SQL

2. Llistat de comandes i funcions descrites a la prctica.

3. Guia de SQL: Oracle Database 10g

Gui:

1. Familiaritzar-se amb lentorn SQL Developer

2. Instruccions i funcions SQL

En les properes sessions de prctiques de lassignatura treballarem amb la base de dades ESPECTACLES que ja heu fet servir a classe de problemes. Disposareu de diverses versions d'aquesta base de dades en funci dels exercicis que haureu de fer. En aquesta prctica comenarem a treballar amb SQL i ms concretament, ens enfocarem en el us de les sentencies SELECT.



1 Familiaritzar-se amb lentorn SQL Developer

Com ja hem comentat en les dues sessions anteriors, SQL Developer s una eina que es pot descarregar gratutament de la pgina web dOracle (www.oracle.com, per a poder-lo descarregar us heu de registrar abans) i que us ser dajuda per a la resta de sessions. Als laboratoris teniu installat aquest software, i en les figures 1 i 2 podeu veure un parell de pantalles per ajudar-vos a comenar a treballar en aquest entorn.

Figura 1. Com connectar-se a la base de dades.

Per a poder-vos connectar aneu a Connections>New Connection. Heu dintroduir el nom

dusuari i la contrasenya que us han donat, el Hostname s saturn.uab.es i el SID s

cassini.



Figura 2. Pantalla principal Oracle SQL developer i funcions.

La part de lesquerra us permet seleccionar la connexi amb la que voleu treballar. Desplegant els mens podeu accedir a les taules.

La part de la dreta la teniu dividida en tres sectors. El primer us permet editar consultes SQL que podeu executar fent clic al bot del triangle verd. A la part de sota us surten els resultats. Finalment, a la part inferior us sortiran els possibles warnings o errors dinterpretaci.



2 Instruccions i funcions SQL

Recordeu que el prototipus de consulta per a lobtenci de tuples de les taules s la clusula SELECT:

SELECT [atributs]

FROM [taules]

WHERE [condicions]

GROUP BY [atributs]

HAVING [condicions]

Cadascun dels atributs duna taula porta associat un nom i un tipus. Els tipus datribut d'Oracle sn els segents:

Tipus de dada Descripci

VARCHAR2(mida) Carcter de llargada variable, amb llargada mxima mida. La llargada mnima s 1 i la mxima 4000.

CHAR(mida) Carcter de llargada fixa, amb llargada mida. La llargada mnima s 1 i la mxima 2000.

NUMBER(p,s) Valor numric que t una precisi mxima p amb un rang de 1 a 38 i una escala mxima de s.

DATE Valors de data i hora entre l'1 de gener de 4712a.C. i el 31 de desembre de 9999 d.C. Oracle ho guarda com a valor numric, proporcionant funcions de conversi a format caracter.

LONG Tipus carcter de llargada variable i fins a 2 GBytes.

CLOB Tipus carcter single-byte fins a 4 GBytes

RAW Dades binries amb llargada expressada segons la mida obligatriament especificat, fins 2000.

BLOB Tipus binari de llargada variable, fins 2 GBytes.

BFILE Tipus binari emmagatzemada en un fitxer extern, fins 4 GBytes.

Per facilitar la manipulaci de les dades, SQL incorporar un seguit de funcions de tractament. Podeu trobar informaci completa de cada funci a la guia d'Oracle 10g. Aqu es presenten algunes daquestes funcions amb el seu resultat.

Funcions numriques:

Funci Resultat

ROUND(45.926,2) 45.93

TRUNC(45.926,2) 45.92

MOD(1600,300) 100



Funcions de carcter:

Funci Resultat

LOWER('Curso SQL') curso sql

UPPER('Curso SQL') CURSO SQL

INITCAP('Curso SQL') Curso Sql

CONCAT('Bases','De Dades') BasesDe Dades

SUBSTR('String',1,3) Str

INSTR('String','r') 3

LPAD(salari,10,'*') ******4500

TRIM('B' FROM 'Bases') ases

Funcions de conversi:

Funci Resultat

TO_CHAR(valor,format) Nmero o data -> Carcter

TO_DATE(valor,format) Carcter -> Data

TO_NUMBER(valor,format) Carcter -> Nmero

Funci de manipulaci de valors NULL:

Funci Resultat

NVL(expressi, 'cadena') Si el valor de expressi s NULL, torna cadena. Si no, torna expressi.

Funci dagregaci:

Funci Resultat

COUNT(*) Nombre delements de la taula.

COUNT(nom_columna) Nombre delements de la columna.

COUNT(DISTINCT nom_columna)

Nombre de valors diferents de la columna

AVG(nom_columna) Retorna la mitjana dels valors de la columna

MAX(nom_columna) Retorna el valor ms gran de la columna

MIN(nom_columna) Retorna el valor ms petit de la columna

SUM(nom_columna) Retorna la suma dels valors de la columna

La instrucci GROUP BY sutilitza conjuntament amb les funcions dagregaci per agrupar el conjunt de resultats per una o ms columnes. Sintaxi:

SELECT col1, ..., colN, funci_agregacio(expressi)

FROM taules



WHERE predicats

GROUP BY col1, ..., colN

La instrucci HAVING sutilitza conjuntament amb la instrucci GROUP BY per filtrar els registres que retorna la instrucci GROUP BY. Sintaxi:

SELECT col1, ..., colN, funci_agregacio(expressi)

FROM taules

WHERE predicats

GROUP BY col1, ..., colN

HAVING condicio1, ..., condicioN



3 Manipulaci de dades: fer consultes simples en SQL

Exercici 1: Obriu el SQL Developer i executeu el script de creaci de la base de dades ESPECTACLES (espect-Oracle.sql) que trobareu caronte.uab.cat. Intenteu accedir a les taules i als seus continguts. A la figura 3 hi teniu un diagrama de la base de dades que es genera.

Figura 3. Diagrama de la base de dades generada.

A continuaci feu les segents consultes com a exemple (copieu-les i enganxeu-les directament a la pantalla de comandes):

SELECT DNI,Nom

FROM Espectadors;

SELECT Codi,Nom

FROM Espectacles

WHERE UPPER(Interpret) like 'EL TRICICLE';



Per practicar els formats de dates i manipular-les feu el mateix amb la segent consulta:

SELECT to_char(data,'dd/mm/yy'), to_char(hora,'HH24:MI:SS')

FROM representacions

WHERE to_char(data,'yy')='12';

Daltra banda, la taula DUAL s una taula pblica dOracle que es fa servir noms per fer comprovacions de valors. Podeu utilitzar-la per comprovar el resultat que torna alguna de les funcions anteriors, per exemple:

SELECT UPPER ('CadenA') FROM DUAL;

SELECT ROUND (3.141593,2) FROM DUAL;

Exercici 2: Feu les segents consultes en SQL:

1 Nom i cognoms dels espectadors de la ciutat de Sabadell.

2 Representacions despectacles (nom de lespectacle, data -format DD-MON-YY- i hora- format 'HH24:MI:SS'-).

3 Espectacles representats en el teatre Victria.

4 Nom dels espectacles que shan representat a Barcelona durant el mes de gener de 2012.

5 Nom i cognoms dels espectadors que han comprat entrades Terrific.

6 Qui (nom i cognom) va comprar entrades per a la representaci de lespectacle Terrific del dia 29 de mar del 2012?

7 Digues per quins espectacles la Rosa ha comprat entrades, i quantes nha comprat en cada cas.

8 Per a cadascuna de les zones on es representa La extraa pareja, nom de la zona, capacitat i preu de lentrada.

9 Nom de lespectacle, data (format DD-MON-YY), hora (format 'HH24:MI:SS') i entrades venudes dels espectacles que es van fer al febrer del 2012.

10 Nom, cognoms i DNI dels espectadors que no sn de Barcelona.

11 Nom, cognoms i DNI dels espectadors que tenen alguna entrada reservada.

12 Data (format DD-MON-YY) i hora (amb format 'HH24:MI:SS') de totes les

representacions de lespectacle Els Pastorets.

13 Nom despectacles, data (format DD-MON-YY), hora de representaci (format

'HH24:MI:SS') per a tots els espectacles que es van veure al Romea lany 2012.

Important:

Per totes les consultes farem servir la mateixa ordenaci al final de cada consulta: ORDER

BY 1,2,n on n s el nombre de camps que es demanen com a sortida.

Quan demanem a la sortida una data o una hora, heu de fer les conversions necessaris al

format corresponent fent servir la instrucci TO_CHAR.

Jorge Bernal, Antonio Esteban, Carles Snchez, Alejandro Prraga, Debora Gil y Oriol Ramos

29 Octubre del 2014

Sesion 3 2014-2015.pdf

Documents

Transcript of Sesion 3 2014-2015.pdf