3.5 Cláusulas básicas de SQL

3.5.1 Lectura

• Seleccionar todas las columnas de una tabla:

  SELECT * FROM Track;  

• Seleccionar columnas específicas:

  SELECT name, composer FROM Track; 

• Alias de columna y tabla:

  SELECT name AS Canción FROM Track; 

  SELECT T.name FROM Track AS T;

• Funciones de agregación:

  SELECT COUNT(*), SUM(UnitPrice), MIN(UnitPrice), MAX(UnitPrice) FROM Track; 

  SELECT AVG(milliseconds) AS 'Duración Media' FROM Track; 

• Filtrado de duplicados:

  SELECT DISTINCT FirstName FROM Customer; 

  SELECT COUNT(DISTINCT FirstName) FROM Customer; 

• Formato:

  SELECT CONCAT(FirstName, ' ', LastName) AS Nombre FROM Employee; 

  SELECT (FirstName || ' ' || LastName) AS Nombre FROM Employee; 

  SELECT ROUND(AVG(Total), 2) AS 'Facturacion Media' FROM Invoice; 

3.5.1.1 Filtrado de Resultados

• Seleccionar filas con condiciones:

SELECT name FROM Track
WHERE UnitPrice < 2.0; 

• Múltiples condiciones:

  • Operadores: ‘AND’, ‘OR’, ‘LIKE’, ‘NOT’, ‘IS NULL’, ‘IS NOT NULL’ ‘BETWEEN x AND y’, ‘IN (lista)’

  SELECT name FROM Track
  WHERE milliseconds > 120000 AND UnitPrice < 2.0; 

  SELECT name FROM Track
  WHERE composer LIKE 'Metallica' OR composer LIKE 'Ulrich'; 

• Coincidencias parciales:

  SELECT * FROM Track
  WHERE name LIKE '%Love%'; 

• ‘%’ : Reemplazo por un conjunto de caracteres

• ’_’ : Reemplazo por un caracter

• Rangos:

  SELECT * FROM Track
  WHERE UnitPrice BETWEEN 0.5 AND 1.5; 

• Valores en una lista:

  SELECT * FROM Track
  WHERE composer IN ('Metallica', 'Ulrich'); 

3.5.1.2 Ordenar Resultados

• Ordenar por una columna:

  SELECT * FROM Track
  ORDER BY title ASC; 

  SELECT * FROM Track
  ORDER BY title DESC; 

• Ordenar por múltiples columnas:

  SELECT * FROM Track
  ORDER BY composer ASC, title DESC; 

3.5.1.3 Número de filas (paginación)

• Obtener las N primeras:

  SELECT * FROM Track
  ORDER BY title ASC
  LIMIT 5;

• Obtener las N filas siguientes:

  SELECT * FROM Track
  ORDER BY title ASC
  OFFSET 5 LIMIT 5;