1.5. Conceptos básicos SQL Server
 
Indice.
  • Qué es una BD? 
  • Qué es un Sistema de BD? 
  • DDL 
  • DML 
  • Atributos Claves
  • Cláusula CREATE 
  • Restricciones de integridad
  • Not null 
  • Unique 
  • Check 
  • Primary Key 
  • Foreign Key 
  • Alter table
  • Eliminación de objetos
  • Creación de índices
  • DML
  • INSERT
  • UPDATE
  • DELETE
  • SELECT (básico)
  • DISTINCT
  • ORDER BY
  • JOIN
  • OUTER JOIN
  • Renombre de atributos
  • Funciones de agregación
  • Sub-consultas 


El objetivo es reconocer términos y condiciones de "Base de datos, sistemas de base de datos, dml, ddl". Entender como se crea una base de datos, tablas y restricciones de integridad en SQL server.


 Introducción. 

Comenzamos por preguntar ¿Que es una base de datos?

  • Es un conjunto de datos que se relacionan entre si
  • Representan una realidad o por lo menos una parte de ella
  • Tienen un propósito especifico
  • Pueden ser de cualquier tamaño
  • Pueden tener distinto grado de complejidad

Dicho a la ligera, es un lugar en donde nos permite guardar grandes volúmenes de información de una forma organizada para que luego la podamos encontrar fácilmente y utilizarla.


Pero entonces ¿Que es un sistema de base de datos?

Es un conjunto de datos organizados entre si, organizado alrededor de un modelo de datos y un conjunto de programas que mantienen esos datos.

USUARIO => PROGRAMA/S => DB


Hablemos de los objetivos del sistema de base de datos.

Los objetivos del sistema de base de datos son controlador redundancia de los datos, evitar inconsistencias entre datos, facilitar la accesibilidad a los datos, seguridad de los datos, evitar anomalías en el acceso concurrente y recuperación de datos.

Pero su objetivo principal es crear un ambiente en el que sea posible guardar y recuperar información de la base de datos de la forma mas eficaz y eficiente posible.


Comenzamos a mirar sus lenguajes, ¿Que lenguajes utiliza? 

  • Lenguaje de definición de datos o LDD
  • Lenguaje de manipulación de datos o LMD

El lenguaje de definición de datos permite definir los objetos y relaciones, es decir la estructura que tiene para almacenar de datos. Cuando se compila las instrucciones en LDD es un conjunto de tablas que se llaman diccionarios de datos o directorio de datos. (tablas).

Ademas de las especificaciones mencionadas anteriormente permite definir :

  • Dominio de relaciones con cada atributo
  • Restricciones de integridad
  • Indices
  • Permisos para acceder a los objetos

El lenguaje de manipulación de datos permite la recuperación de datos, insertar datos, eliminar datos o modificarlos.


Atributos y claves en las bases de datos

Clave primaria: Es un atributo o grupo de atributos que identifican únicamente a una entidad.

Calve alterna: Es un atributo o grupo de atributos que identifican únicamente a una entidad. Pero esta no forma parte de la clave primaria.

Clave foránea: Una clave primaria de una entidad padre que es aportada a una entidad hijo, a través de una relación.


La clausula Create

Voy a comenzar por explicar como crear una base de datos a traves de esta clausula.

Create DATABASE :

Crea una base de datos, ejemplo : "Create DATABASE NombreDeLaBaseDeDatos".

Create TABLE :

Crea una table en la base de datos, los parámetros que posee son "Nombre de la tabla, Nombre de la columna, Tipo de datos de cada columna, restricciones de la clave primaria y clave foránea sobre las demás tablas".

Ejemplo :  

CREATE TABLE Piccioti

(

Id_persona INT NOT NULL REFERENCES Persona(Id_persona),

Id_piccioti INT NOT NULL,

Fecha_iniciacion DATETIME,

Constraint PK_Piccioti PRIMARY KEY (Id_piccioti)

);

Info:  Id_persona es una clave foranea,  Id_piccioti  es la clave principal, la clave primaria se define a lo ultimo con una constraint.


Dentro de la base de datos existe lo que se le llama la integridad de datos, un ejemplo de esto son las columnas que poseen claves primarias, valores nulos, columnas con valores únicos, chequeo de integridades mas complejas, integridad referencia (como por ejemplo las claves foráneas) .

Ejemplos : PRIMARY KEY, NOT NULL, UNIQUE, CHECK FOREIGNKEY /REFERENCES.


Ejemplo 1 en codigo: 

CREATE TABLE NegociaComoProveedor

(

Id_associati INT NOT NULL,

Id_miembro INT NOT NULL,

Fecha_desde DATETIME,

Fecha_hasta DATETIME,

Ganancia DECIMAL (10),

Constraint FK_NegociaComoProveedor1 FOREIGN KEY (Id_associati) REFERENCES Associati (Id_associati),

Constraint FK_NegociaComoProveedor2 FOREIGN KEY (Id_miembro) REFERENCES Miembro (Id_miembro)

);

Info:  Esta tabla no posee clave primaria, pero posee 2 claves foráneas ( Id_associati e  Id_miembro), se agregan constraint para indicar que es una clave foránea y referencia a la tabla de donde proviene.


 Ejemplo 2 en codigo:  

CREATE TABLE Productos (

prodCod INTEGER PRIMARY KEY,

nombre VARCHAR(50) UNIQUE,

peso DECIMAL(9,2),

descripcion VARCHAR(200) NOT NULL

)

Info: La restricción de integridad "UNIQUE" hace referencia a que ese valor debe ser único. En este caso se indica que la columna  prodCod  es un entero y que esa va a ser la clave principal de la tabla. Cambien se puede indicar creando una constraint por separado, los dos términos tiene le mismo resultado


Ejemplo 3 en codigo:   

CREATE TABLE Productos (

prodCod INTEGER PRIMARY KEY CHECK(prodCod between 0 and 100),

nombre VARCHAR(50) UNIQUE,

peso DECIMAL(9,2),

descripcion VARCHAR(200) NOT NULL

)

Info: Se agrega una restricción de integridad a la columna prodCod la cual indica que el valor tiene que estar entre 0 y 100.


Ejemplo 4 en codigo:

CREATE TABLE Productos (

prodCod INTEGER,

nombre VARCHAR(50),

peso DECIMAL(9,2),

descripcion VARCHAR(200) NOT NULL,

CONSTRAINT prodCod_pk PRIMARY KEY(prodCod) ,

CONSTRAINT prodCod_entre CHECK(prodCod between 0 and 100),

CONSTRAINT nombre_u UNIQUE(nombre)

);


Crear índices, Insertar, modificar y eliminar datos de tablas. (LDD) 

Identity

Cualquier campos puede tener un atributo "Identity", este genera valores secuenciales que por defecto se inician en 1 y se incrementan en 1. Lo mas común es que se utilicen en campos de identificación para crear valores únicos, por defectos el campo se incrementa de 1 en 1, hay que tener en cuenta que solo puede haber un campos identity solo en la tabla, cuando existe un campo con este atributo no se le puede ingresar valores, este se incrementa automáticamente.

Ejemplo en codigo:

CREATE TABLE Productos (

prodCod INTEGER PRIMARY KEY IDENTITY(50,1),

nombre VARCHAR(50),

peso DECIMAL(9,2),

descripcion VARCHAR(200)

)


 Alter

Cuando se crea una tabla a veces tenemos la necesidad de cambiarla, añadir columnas o modificar una columna existente, estas operaciones se puede realizar con el comando "Alter"

Para realizar cualquier accion sobre una tabla hay que tener en cuenta algunos puntos como por ejemplo, no es posible disminuir el tamaño de una columna, cuando se modifica una columna los tipos de datos que poseen deben ser compatibles con la modificacion simplemente trabajar sobre una tabla vacia, cuando se intenta realizar una acción como por ejemplo ALTER COLUMN ..... NOT NULL solo se podra realizar si la tabla no contiene valores nulos.


Ejemplo en codigo para modificar una columna:

ALTER TABLE Paises ALTER COLUMN codPais nvarchar(3) not null;

Ejemplo en código para agregar una columna:

ALTER TABLE Productos add fecha DateTime NOT NULL;


Drop

Para borrar una columna de una tabla vamos a utilizar:

  • Drop table <nombre de la tabla> Drop column <nombre de la columna>

Ejemplo en codigo:

 ALTER TABLE tabla DROP COLUMN columna;


Alter - para agregar restricciones

El siguiente fragmento verán ejemplos de como agregar restricciones y quitarlas de una tabla.

Por ejemplo:

ALTER TABLE Departamentos

ADD CONSTRAINT Dep_Id_Pk PRIMARY KEY (Dep_Id);


ALTER TABLE Empleados

ADD CONSTRAINT Emp_Fk_Dep FOREIGN KEY (Dep_Id)

REFERENCES Departamentos (Dep_Id);


ALTER TABLE Empleados

ADD CONSTRAINT Emp_Salario_Min CHECK (Emp_Salario > 0);


Alter - para borrar restricciones 

El siguiente fragmento verán ejemplos de como borrar restricciones y quitarlas de una tabla. 

Por ejemplo:

ALTER TABLE Empleados

DROP CONSTRAINT Emp_Fk_Dep;


ALTER TABLE Empleados

DROP CONSTRAINT Emp_Salario_Min;


Indices en la base de datos

Un indice se podría decir que es un acceso directo de una estructura de memoria secundaria que permite el acceso a las filas de una tabla.

Mejora el rendimiento en las operaciones sobre una tabla, por lo general mejoran los SELECT y dificultan en una mínima parte el rendimiento de los (INSERT, UPDATE y los DELETE).

Se puede decir que cuando identificamos con una restricción "Primary key" o "unique" a una tabla, SQL server automáticamente crea un indice sobre el campo a la cual se le aplico la restricción de integridad.

¿Cuando es recomendables crear los Indices?

Cuando una columna se sometida al uso excesivo del "WHERE" o en condiciones de tipo "JOIN".

Cuando la columna contiene un gran volumen de valores

Si la tabla es grande y se quiere filtrar para obtener un numero reducido de filas.

 ¿Cuando no es recomendables crear los Indices? 

Cuando tenemos una tabla muy chica

Si la tabla es modificada con frecuencia ya que lleva un mayor mantenimiento de los indices


Ejemplo en codigo de la sintaxis para el Indice :

CREATE INDEX INX_Producto_Prov ON Productos (codProveedor)


¿Como se borran los objetos de la base de datos?

Utilizando la sentencia DROP, los objetos a borrar pueden ser:

  • Table
  • Index
  • Procedure
  • Function
  • Trigger
  • View


Inserción de datos, eliminación de datos, modificación de datos y recuperación de datos. (LMD)

INSERT

Esta acción sirve para insertar datos dentro de una tabla.

Ejemplo de la sintaxis : 

INSERT INTO <nombre-tabla> VALUES (serie de valores)

Otro ejemplo : 

INSERT INTO Empresas_Envio VALUES ('EMP004','Test00','Ruta 8 Km.27','[email protected]')

Todos los valores se asignan por posicionamiento relativo en la clausula "VALUES", ya que estos deben corresponde a los valores de la tabla con su tipo de dato correspondiente.

Otra forma de insertar valores:

INSERT INTO <nombre-tabla> (columna1,

columna2.....) VALUES (valor1, valor2....)


UPDATE

Esta accion sirve para modificar datos dentro de una tabla que cumplan determinadas condiciones.

Ejemplo:

 UPDATE  SET columna1 = valor1 [, columna2 = valor2 ...] [WHERE condición] 

Ejemplo en codigo: 

Update Pedidos SET estadoPed = 'Cancelado'

where Pedidos.nroPed = 1


Delete

Esta acción sirve para borrar datos dentro de una tabla que cumplan determinadas condiciones o no. Depende de la condición WHERE.

Ejemplo en codigo :

Delete from Pedidos

where Pedidos.nroPed in (select nroPed from deleted)

Otro ejemplo en codigo:

Delete from Pedidos

where Pedidos.nroPed = 1


Hay que tener cuidado ya que si no se coloca una condición borra todas las filas de la tabla.

Ejemplo para borrar toda la tabla :  

DELETE FROM Inscripciones;  


Recuperacion de datos en la base (SELECT)

Simplemente consiste en elegir un conjunto o subconjunto de filas que cumplan determinada condición.

Ejemplo en codigo:

SELECT mutualista.nombre

FROM mutualista, rrhh_mutualista

WHERE mutualista.id_mutualista = 2

Info: Selecciona el nombre de la mutualista que tenga una id = 2

Otro ejemplo en codigo:

SELECT costo FROM INSCRIPCIONES WHERE idEstudiante = 5 and idCurso = 3 

Info: Selecciona el costo que tiene la inscripción del estudiante 5 en el curso 3.

Otro ejemplo en codigo:

 SELECT * FROM ESTUDIANTES WHERE ciudadNac = 'Montevideo' 

Info: Selecciona todos los estudiante mientras su ciudad de nacimiento se Montevideo.


¿Cuantos datos podemos recuperar en un SELECT?

  • Columna.
  • Columna1, columna2, columna3, … .
  •  *.
  • Tabla.* (para cuando trabajamos con más de una tabla).
  • Tabla.columna (necesario cuando trabajamos con más de una tabla y con presencia de atributos de igual nombre).

Expresiones

  • Columna.
  • Operación aritmética (/,*,+,-).
  • Constantes.

Ejemplo con operaciones aritmeticas:

SELECT estudianteCod, cursoCod, costo / 28.5, fchInsc

FROM INSCRIPCIONES


¿Como se pueden ordenar los resultados?

Para los ordenamientos poseemos la clausula OREDER BY el cual permite indicar el campo por el cual se va a ordenar, se indica en el orden ASC o DESC.

Ejemplo en código:

SELECT cursoCod FROM INSCRIPCIONES ORDER BY cursoCod ASC; 

Para realizar un resultados que no posea numeros repetidos necesitamos hacer uso de la clausula DISTINCT.

Ejemplo en código:

SELECT DISTINCT cursoCod FROM INSCRIPCIONES ORDER BY cursoCod;


Ejemplo de una consulta completa con la informacion brindada anteriormente:

SELECT [DISTINCT]

<nombre_campo> [{,<nombre_campo>}]

FROM <nombre_tabla>

[WHERE <condicion> [{ AND|OR <condicion>}]]

[ORDER BY <nombre_campo> [ASC | DESC]


Consultas combinadas (JOIN)

Cuando necesitamos obtener información en una determinada base de datos pero esa información se encuentra separada en diferentes tablas que están referenciadas a traves de varios códigos recurrimos a las consultas combinadas o "JOINS".

Existen varios tipos de join para esto debemos dividirlos en dos definiciones, las uniones internas y las uniones externas.

Las union internta : INNER JOIN, NATURAL JOIN

Las uniones externas : LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN.

Comenzamos por explicar el INNER JOIN, es el caso mas común con el cual uno se puede encontrar, consiste en combinar dos o mas tablas.

Ejemplo en código: Mostrar el código, nombre de estudiante y código de curso, de os estudiantes que tengan inscripciones a cursos.

SELECT ESTUDIANTES.estudianteCod,

ESTUDIANTES.nombre, INSCRIPCIONES.cursoCod

FROM ESTUDIANTES, INSCRIPCIONES

WHERE ESTUDIANTES.estudianteCod =

INSCRIPCIONES.estudianteCod;


El NATURAL JOIN compara la equivalencia de columnas con el mismo nombre y tipo de dato entre dos o mas tablas para realizar combinaciones. Otro dato importante es que sql server no soporta el NATURAL JOIN.


Hablemos de la combinación externa, quiere decir que si un registro no cumple la condición de combinación no se incluye en los resultados. La combinación externa puede ser por izquiera o derecha, que quiere decir esto, que con un LEFT OUTER JOIN obtenemos todos los registros de una tabla que se encuentra a la izquierda de la clausula join y por otro lado con RIGHT OUTER JOIN obtenemos un resultado inverso.

Ejemplo en código:

Mostrar el código y nombre del estudiante, y en caso de que esté inscrito a algún curso, mostrar los códigos de esos cursos. 

Tablas del ejemplo :

Marcas (marcaCod, nombre) 

Autos (matricula, marcaCod, color, kms, plazas) 

Motos (matricula, marcaCod, color, kms) 


SELECT ESTUDIANTES.estudianteCod,

ESTUDIANTES.nombre, INSCRIPCIONES.cursoCod

FROM ESTUDIANTES LEFT OUTER JOIN INSCRIPCIONES

ON ESTUDIANTES.estudianteCod =

INSCRIPCIONES.estudianteCod;


Otro ejemplo en código:

Devolver la matrícula, nombre de marca, color, kms y plazas de todos los autos. 

SELECT AUTOS.matricula, MARCAS.nombre,

AUTOS.color, AUTOS.kms, AUTOS.plazas

FROM AUTOS LEFT JOIN MARCAS

ON AUTOS.marcaCod = MARCAS.codigo;


Ejemplo del RIGHT OUTER JOIN

SELECT AUTOS.matricula, MARCAS.nombre,

AUTOS.color, AUTOS.kms, AUTOS.plazas

FROM AUTOS RIGHT OUTER JOIN MARCAS

ON AUTOS.marcaCod = MARCAS.codigo;


Sintaxis completa:

SELECT [DISTINCT ]

<nombre_campo> [{,<nombre_campo>}]

FROM <nombre_tabla>

[{LEFT|RIGHT [OUTER] JOIN <nombre_tabla> ON

<condicion_combinacion>}]

[WHERE <condicion> [{ AND|OR <condicion>}]]

[ORDER BY <nombre_campo> [ASC|DESC]

[{,<nombre_campo> [ASC | DESC ]}]] 


¿Como dar un alias de tabla y atributos?

Para los SELECT para columnas SELECT <expreesion> [[as] Alias]

Para los FROM para tablas FROM <tabla> [[as] Alias]

El alias se puede definir con comillas para identificar un alias de mas de una palabra, pero el de tabla no.

Ejemplo de codigo:

SELECT ESTUDIANTES.nombre AS “Nombre Estudiante”, CURSOS.nombre AS “Nombre Curso”

FROM ESTUDIANTES, CURSOS, INSCRIPCIONES

WHERE ESTUDIANTES.estudianteCod = I

NSCRIPCIONES.estudianteCod AND

CURSOS.cursoCod = INSCRIPCIONES.cursoCod;


El objetivo para esta sección  es poder realizar consultas con mayor complejidad en la base de datos y comprender como se utilizan las subconsultas y ser capaces de realizarlas.

Utilizaremos estas tablas como ejemplo:

Estudiantes (estudianteCod, nombre, ciudadNac, fchNac) 

Cursos (cursoCod, nombre) 

Inscripciones (estudianteCod, cursoCod, costo, fchInsc) 


Funciones de agregación (o de sumarizacion).

Count(*)

Es un contador de tuplas (cuenta valores duplicados y nulos), haciendo uso de la clausula Distinct no tiene en cuenta valores nulos ni duplicados.

AVG

Es la media aritmética de un atributo o una expresión numérica, también puede utilizar la clausula DISTINCT.

SUM(*)

Permite sumar atributos o expresión numéricas, puede utilizar la clausula DISTINCT.

MAX

Permite identificar el valor máximo de un atributo

MIN

Permite identificar el valor mínimo de un atributo


Esquema DML

Comando DML

  • SELECT
  • INSERT
  • UPDATE
  • DELETE

Clausulas DML

  • FROM : Especifica la tabla
  • WHERE : Condición
  • GROUP BY : Separación por grupos
  • HAVING : Condición sobre grupos
  • ORDER BY : Ordenar registros

Operadores lógicos DML

  • AND
  • OR
  • NOT

Operadores de comparación

  • BETWEEN
  • LIKE
  • IN

Esquema LDD

  • CREATE
  • ALTER
  • DROP
  • RENAME
  • TRUNCATE