Los mejores ejemplos de SQL

Original article: The Best SQL Examples

SQL son las siglas en inglés para "Structured Query Language" (lenguaje de consulta estructurado). Se utiliza con todo tipo de bases de datos relacionales.

Ejemplo de sintaxis básica de SQL

Esta guía proporciona una descripción básica y de alto nivel de la sintaxis de las declaraciones SQL.

SQL es un estándar internacional (ISO) pero encontrarás muchas diferencias entre las implementaciones. Esta guía utiliza MySQL como ejemplo. Si utilizas uno de los muchos otros Gestores de Bases de Datos Relacionales (DBMS por sus siglas en inglés) tendrás que consultar el manual de ese DBMS si es necesario.

Lo que cubriremos

• Uso (establece qué base de datos utilizarán las instrucciones)
• Cláusulas Select y From
• Cláusula Where (and/or, IN, Between, LIKE)
• Order by (ASC, DESC)
• Group by y Having

Cómo se utiliza

Se utiliza para seleccionar la base de datos que contiene las tablas para tus declaraciones SQL:

use fcc_sql_guides_database; --selecciona la base de datos de muestras guía

Cláusulas Select y From

La cláusula Select se utiliza normalmente para determinar qué columnas de los datos se quieren mostrar en los resultados. También hay opciones que puedes usar para mostrar datos que no son una columna de la tabla.

Este ejemplo muestra dos columnas seleccionadas de la tabla "student", y dos columnas calculadas. La primera de las columnas calculadas es un número sin sentido, y la otra es la fecha del sistema.

SELECT studentID, FullName, 3+2 as five, now() as currentDate
FROM student;

Cláusula Where (and / or, IN, Between y LIKE)

La cláusula Where se utiliza para limitar el número de filas devueltas.

En este caso se utilizarán las cinco condiciones en un ejemplo un tanto ridículo de la cláusula Where.

Compara este resultado con las declaraciones SQL anteriores para seguir esta lógica.

Se mostrarán las filas que:

• Tengan el ID del estudiante (studentID) entre 1 y 5 (inclusivo)
• o studentID = 8
• o tengan "Maximo" en el nombre

El siguiente ejemplo es similar, pero especifica además que si alguno de los estudiantes tiene determinadas puntuaciones de SAT (1000, 1400), no se mostrarán:

SELECT studentID, FullName, sat_score, recordUpdated
FROM student
WHERE (
	studentID between 1 and 5
    	or studentID = 8
    	or FullName like '%Maximo%'
    	)
    	and sat_score NOT in (1000,1400);

Order By (ASC, DESC)

Order By (ordenar por) nos da la opción de ordenar los datos resultantes por uno o más elementos de la sección SELECT. Aquí está la misma lista de arriba, pero ordenada por el nombre completo (FullName) de los estudiantes. El orden por defecto es ascendente (ASC), pero para ordenar en el orden opuesto (descendente) se utiliza DESC, como en el ejemplo siguiente:

SELECT studentID, FullName, sat_score
FROM student
WHERE (studentID between 1 and 5 -- inclusive
	or studentID = 8
    or FullName like '%Maximo%'
    and sat_score NOT in (1000, 1400)
  order by FullName DESC;

Group By y Having

Group By (agrupar) nos da una forma de combinar filas y agregar datos. La cláusula Having es como la cláusla Where anterior, excepto que actúa sobre los datos agrupados.

Estos datos provienen de los datos de las contribuciones a la campaña que hemos estado utilizando en algunas de estas guías.

Esta declaración de SQL responde a la pregunta: "¿qué candidatos recibieron el mayor número de contribuciones (no la cantidad de dólares, sino el recuento (*)) en 2016, pero sólo los que tuvieron más de 80 contribuciones?"

Al ordenar estos datos en orden descendente (DESC), los candidatos con el mayor número de contribuciones se sitúan al principio de la lista.

SELECT Candidate, Election_year, sum(Total_$), count(*)
FROM combined_party_data
WHERE Election_year = 2016
GROUP BY Candidate, Election_year
	having count(*) > 80
    order by count(*) DESC;

Como con todas estas cosas de SQL, hay MUCHO MÁS de lo que encuentras en esta guía introductoria. Espero que esto al menos te dé lo suficiente para empezar. Por favor, consulta el manual de tu gestor de base de datos y diviértete probando diferentes opciones por ti mismo.

Preguntas comunes sobre SQL en las entrevistas

¿Qué es un inner join en SQL?

Es el tipo de unión por defecto si no se especifica ninguna unión. Devuelve todas las filas en las que hay al menos una coincidencia en ambas tablas.

SELECT * FROM A x JOIN B y ON y.aId = x.Id

¿Qué es un left join en SQL?

Un left join devuelve todas las filas de la tabla a la izquierda (LEFT) y las filas coincidentes de la tabla a la derecha (RIGHT). Las filas de la tabla a la izquierda se devolverán aunque no haya una coincidencia en la tabla a la dercha. Las filas de la tabla a la izquierda sin coincidencias en la tabla derecha tendrán valores nulos (null) para la tabla a la derecha.

SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id

¿Qué es un right join en SQL?

Un right join devuelve todas las filas de la tabla a la derecha, y las filas coincidentes de la tabla a la izquierda. Contrario al left join, este devuelve todas las filas de la tabla a la derecha incluso cuando no haya ninguna coincidencia en la tabla izquierdo. Las filas de la tabla derecho que no tengan coincidencias en la tabla izquierdo tendrán valores null para las columnas de la tabla izquierdo.

SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id

¿Qué es un full join en SQL?

Un full join devuelve todas las filas en donde hay una coincidencia en cualquiera de las tablas. Es decir, si hay filas en la tabla izquierdo que no tienen coincidencias en la tabla derecho, se incluirán. Así como si hay filas en la tabla derecho que no tienen coincidencias en la tabla izquierdo, también se incluirán.

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName

¿Cuál es el resultado del siguiente comando?

DROP VIEW view_name

Aquí se producirá un error porque no podemos realizar una operación DML en una vista.

¿Se puede realizar un rollback después de utilizar el comando ALTER?

No, porque ALTER es un comando DDL y el servidor Oracle realiza un COMMIT automático cuando se ejecutan las declaraciones DDL.

¿Cuál es la única restricción que aplica reglas a nivel de columna?

NOT NULL es la única restricción que funciona a nivel de columna.

¿Cuáles son las pseudo-columnas en SQL? Da algunos ejemplos.

Una pseudo-columna es una función que devuelve un valor generado por el sistema. La razón por la que se conoce así es porque una pseudo-columna es un valor asignado por Oracle que se utiliza en el mismo contexto que una columna de la base de datos Oracle, pero que no se almacena en el disco.

ROWNUM, ROWID, USER, CURRVAL, NEXTVAL etc

Crea un usuario my723acct con contraseña kmd26pt. Utiliza los tablespaces de datos de usuario y datos temporales proporcionados por PO8 y proporciona a este usuario 10M de espacio de almacenamiento en datos de usuario (user_data) y 5M de espacio de almacenamiento en datos temporales (temporary_data).

CREATE USER my723acct IDENTIFIED BY kmd26pt
DEFAULT TABLESPACE user_data
TEMPORARY TABLESPACE temporary_data
QUOTA 10M on user_data QUOTA 5M on temporary_data

Cree el ROLE role_tables_and_views

CREATE ROLE role_tables_and_views

Concede al rol de la pregunta anterior los privilegios para conectarse a la base de datos y los privilegios para crear tablas y vistas.

El privilegio para conectarse a la base de datos es CREATE SESSION. El privilegio para crear una tabla es CREATE TABLE. El privilegio para crear una vista es CREATE VIEW.

GRANT Create session, create table, create view TO role_tables_and_views

Concede el ROLE de la pregunta anterior a los usuarios anny y rita

GRANT role_tables_and_views TO anny, rita

Escribe un comando para cambiar la contraseña del usuario rita de abcd a dfgh

ALTER USER rita IDENTIFIED BY dfgh

Los usuarios rita y anny no tienen privilegios SELECT en la tabla de inventario (INVENTORY) que fue creado por SCOTT. Escribe un comando para permitir que SCOTT le conceda a los usuarios privilegios SELECT en estas tablas.

GRANT select ON inventory TO rita, anny

El usuario rita ha sido transferida y ya no necesita el privilegio que se le concedió a través del ROLE role_tables_and_views. Escribe un comando para quitarle los privilegios que se le habían otorgado anteriormente, excepto que aún pueda conectarse a la base de datos.

REVOKE select ON scott.inventory FROM rita
REVOKE create table, create view FROM rita

El usuario rita, que había sido transferida, ahora se traslada a otra empresa. Dado que los objetos que creó ya no son útiles, escriba un comando para eliminar este usuario y todos sus objetos.

Aquí la opción CASCADE es necesaria para eliminar todos los objetos del usuario en la base de datos.

DROP USER rita CASCADE

Escribe una consulta SQL para encontrar el enésimo salario más alto de la tabla

SELECT TOP 1 Salary
FROM (
	SELECT DISTINCT TOP N Salary
    FROM Employee
    ORDER BY Salary DESC
    )
ORDER BY Salary ASC

Declaracion SQL Create View

¿Qué es una View (Vista)?

Una Vista es un objeto de la base de datos que presenta los datos existentes en uno o más tablas. Las Vistas se utilizan de manera similar a las tablas, pero no contienen ningún dato. Sólo "apuntan" a los datos que existen en otra parte (tablas o vistas, por ejemplo).

¿Por qué nos gustan?

• Vistas son una forma de limitar los datos presentados. Por ejemplo, los datos del departamento de recursos humanos filtrados para presentar sólo información no sensible. Información sensible en este caso podrían ser números de seguridad social, el sexo del empleado, tasa de pago, dirección de domicilio, etc.
• Los datos complejos de más de una tabla pueden combinarse en una única "Vista". Esto puede facilitar la vida a sus analistas de negocio y programadores.

Consejos de seguridad importantes

• Las Vistas son gestionadas por el sistema. Cuando los datos en las tablas relacionados se modifican, añaden o actualizan, la Vista es actualizada por el sistema. Queremos utilizarlas sólo cuando sea necesario gestionar el uso de los recursos del sistema.
• En MySQL, cambios en el diseño de una tabla (es decir, columnas nuevas o eliminadas) realizados DESPUÉS de la creación de una Vista no se actualizan en la propia Vista. La Vista tendría que ser actualizada o recreada.
• Las Vistas son uno de los cuatro tipos de objetos estándar de las bases de datos. Los otros son las tablas, procedimientos almacenados y funciones.
• Las Vistas usualmente pueden ser tratadas como una tabla, pero las actualizaciones están limitadas o no están disponibles cuando la Vista contiene más de una tabla.
• Hay muchos otros detalles sobre las Vistas que están fuera del alcance de esta guía introductoria. Dedique tiempo al manual de su gestor de base de datos y diviértase con este poderoso objeto SQL.

Sintaxis de la declaración Create View (MySQL)

CREATE
	[OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = {user | CURRENT_USER}]
    [SQL SECURITY {DESINGER | INVOKER}]
    VIEW view_name[(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

Esta guia cubrirá esta parte de la declaración:

CREATE
	VIEW view_name[(column_list)]
    AS select_statement

Ejemplo de creacion de una Vista (View) a partir de las tablas de estudiantes (student)

Notas:

• El nombre de la Vista tiene una "v" al final. Se recomienda que el nombre de la Vista indique que es una vista de alguna manera para facilitar la vida a los programadores y administradores de la base de datos. Tu tienda de informática debe tener sus propias reglas para nombrar los objetos.
• Las columnas de la Vista están limitadas por la cláusula SELECT y las filas de datos por la cláusula WHERE.
• El carácter "`" alrededor de los nombres de las Vistas es necesario debido al "-" en los nombres. MySQL informa de un error sin ellos.

create view `programming-students-v` as
SELECT FullName, programOfStudy
FROM student
WHERE programOfStudy = 'Programming';


SELECT * FROM `programming-students-v`; 

Ejemplo de uso de una Vista para combinar datos de más de una tabla

Se ha agregado una tabla demográfico de estudiantes a la base de datos para demostrar este uso. Esta Vista combinará estas tablas.

Notas:

• Para "unir" tablas, las tablas deben tener campos en común (normalmente claves primarias) que identifiquen de forma única cada fila. En este caso es el ID del estudiante.
• Fíjese en el "alias" dado a cada tabla ("s" para estudiante y "sc" para contacto con el estudiante). Se trata de una herramienta para acortar los nombres de las tablas y facilitar la identificación de la tabla que se está utilizando. Es más fácil que escribir repetidamente nombres de tablas largas. En este ejemplo, era necesario porque studentID es el mismo nombre de columna en ambas tablas, y el sistema presentaría un "error de nombre de columna ambiguo" sin especificar qué tabla utilizar.

Guía del operador Between

El operador BETWEEN es útil gracias al Optimizador de Consultas SQL (SQL Query Optimizer). Aunque BETWEEN es funcionalmente lo mismo que: x <= element <= y, el Oprimizador de Consultas reconocerá este comando más rápidamente, y ha optimizado el código para ejecutarlo.

Este operador se usa en una cláusula WHERE o en una cláusula GROUP BY HAVING.

Se seleccionan las filas que tienen un valor mayor que el valor mínimo y menos que el valor máximo.

Es importante tener en cuenta que los valores introducidos en el comando se excluyen del resultado. Obtenemos sólo lo que está entre ellos.

Esta es la sintaxis para utilizar la función en una cláusula WHERE:

SELECT field1, testfield
FROM table1
WHERE testField BETWEEN min and max

Y aquí un ejemplo utilizando la tabla de estudiantes y la cláusula WHERE:

-- sin clausula WHERE
SELECT studentID, FullName, studentID
FROM student;

-- clausula WHERE con BETWEEN
SELECT studentID, FullName, studentID
FROM student
WHERE studentID between 2 and 7;

Aquí hay un ejemplo usando la tabla de fondos de campaña y la cláusula HAVING. Esto devolverá las filas en las que la suma de las donaciones de un candidato esté entre 3 y 18 millones de dólares, basándose en la cláusula HAVING en la parte GROUP BY de la declaración. Más información sobre la agregación en esa guía.

SELECT Candidate, Office_Sought, Election_Year, format(sum(TOTAL_$),2)
FROM combined_party_data
WHERE Election_Year = 2016
GROUP BY Candidate, Office_Sought, Election_Year
HAVING sum(Total_$) BETWEEN 3000000 and 18000000
ORDER BY sum(Total_$) DESC;

Una tabla es un grupo de datos almacenados en una base de datos.

Para crear una tabla en una base de datos se utiliza la declaración CREATE TABLE. Se le da un nombre a la tabla y una lista de columnas con sus tipos de datos.

CREATE TABLE TABLENAME(Attribute1 Datatype, Attribute2 Datatype, ...);

Aquí hay un ejemplo de creación de una tabla llamado Persona:

CREATE TABLE Persona(
	Id int not null,
    Name varchar not null,
    DateOfBirth date not null,
    Gender bit not null,
    PRIMARY KEY(Id)
);

En el ejemplo anterior, cada Persona tiene un Nombre (Name), fecha de nacimiento (DateOfBirth), y género (Gender). La columna Id es la clave que identifica a una persona en la tabla. Se utiliza la palabra clave PRIMARY KEY para configurar una o más columnas como claves primarias.

Una columna puede ser not null o null indicando si es obligatoria o no.

Las consultas de inserción son una forma de insertar datos en una tabla. Digamos que hemos creado una tabla utilizando:

CREATE TABLE tabla_ejemplo(nombre varchar(255), edad int)

tabla_ejemplo

Nombre | Edad
  ---  |  ---

Ahora para agregar algunos datos a esta tabla, utilizaremos INSERT de la siguiente manera:

INSERT INTO tabla_ejemplo(column1, column2) VALUES ("Andrew",23)

tabla_ejemplo

Nombre | Edad
  ---  | ---
Andrew | 23

Incluso lo siguiente funciona, pero siempre es una buena práctica especificar qué datos van en cada columna.

INSERT INTO tabla_nombre VALUES("John",28)

tabla_ejemplo

Nombre | Edad
  ---  | ---
Andrew | 23
John   | 28

AND se utiliza en una cláusula WHERE o en una cláusula GROUP BY HAVING para limitar las filas devueltas por la declaración ejecutada. Se utiliza AND cuando se requiera que se cumpla más de una condición.

Utilizremos la tabla de estudiantes para presentar ejemplos.

Aquí está la tabla de estudiantes sin una cláusula WHERE:

SELECT * FROM student;

Ahora se añade la cláusula WHERE para mostrar sólo los alumnos de programación:

SELECT * 
FROM student
WHERE programsOfStudy = 'Programming';

Ahora la clausula WHERE se actualiza con AND para mostrar los resultados de los estudiantes de programación que también tienen una puntuación SAT superior a 800:

SELECT *
FROM student
WHERE programOfStudy = 'Programming'
AND sat_score > 800;

Este es un ejemplo más complejo de la tabla de contribuciones de campaña. Este ejemplo tiene una cláusula GROUP BY con una cláusula HAVING que utiliza un AND para restringir los registros devueltos a los candidatos de 2016 con contribuciones entre 3 y 18 millones de dólares en total.

SELECT Candidate, Office_Sought, Election_Year, FORMAT(sum(Total_$),2)
FROM combined_party_data
WHERE Office_sought = 'PRESIDENT / VICE PRESIDENT'
GROUP BY Candidate, Office_Sought, Election_Year
HAVING Election_Year = 2016 AND sum(Total_$) BETWEEN 3000000 and 18000000
ORDER BY sum(Total_$) DESC;

Cómo utilizar ORDER BY en SQL

Order By (ASC, DESC)

ORDER BY nos da una forma de ordenar el conjunto de resultados por uno o más elementos de la sección SELECT. A continuación se muestra un SQL que ordena a los estudiantes por nombre (FullName) en orden descendente. El orden por defecto es ascendente (ASC) por lo que para ordenar en el orden contrario se utiliza DESC.

SELECT studentId, FullName, sat_score
FROM student
ORDER BY FullName DESC;

+-----------+------------------------+-----------+
| studentID | FullName               | sat_score |
+-----------+------------------------+-----------+
|         2 | Teri Gutierrez         |       800 |
|         3 | Spencer Pautier        |      1000 |
|         6 | Sophie Freeman         |      1200 |
|         9 | Raymond F. Boyce       |      2400 |
|         1 | Monique Davis          |       400 |
|         4 | Louis Ramsey           |      1200 |
|         7 | Edgar Frank "Ted" Codd |      2400 |
|         8 | Donald D. Chamberlin   |      2400 |
|         5 | Alvin Greene           |      1200 |
+-----------+------------------------+-----------+
9 rows in set (0.00 sec)

Aquí está la lista de estudiantes actual, no ordenada, para compararla con la anterior:

SELECT studentID, FullName, sat_score, rcd_updated
FROM student;

+-----------+------------------------+-----------+---------------------+
| studentID | FullName               | sat_score | rcd_updated         |
+-----------+------------------------+-----------+---------------------+
|         1 | Monique Davis          |       400 | 2017-08-16 15:34:50 |
|         2 | Teri Gutierrez         |       800 | 2017-08-16 15:34:50 |
|         3 | Spencer Pautier        |      1000 | 2017-08-16 15:34:50 |
|         4 | Louis Ramsey           |      1200 | 2017-08-16 15:34:50 |
|         5 | Alvin Greene           |      1200 | 2017-08-16 15:34:50 |
|         6 | Sophie Freeman         |      1200 | 2017-08-16 15:34:50 |
|         7 | Edgar Frank "Ted" Codd |      2400 | 2017-08-16 15:35:33 |
|         8 | Donald D. Chamberlin   |      2400 | 2017-08-16 15:35:33 |
|         9 | Raymond F. Boyce       |      2400 | 2017-08-16 15:35:33 |
+-----------+------------------------+-----------+---------------------+
9 rows in set (0.00 sec)

Como con todas estas cosas de SQL, hay mucho más de lo que hay en esta guía introductoria.

Espero que esto al menos te dé lo suficiente para empezar.

Por favor, consulta el manual de tu gestor de base de datos y diviértete probando diferentes opciones por ti mismo.