COMENTING OUT:

COMENTING DESCREPTIVE STATEMENTS

SELECT Column_name, Column _name
Or , Column_name
FROM table_name
WHERE Colum_name operator value;
ORDER BY Characteristic

EXAMPLE:
SELECT ProductName
, UnitName
, SupplierName
FROM Products
WHERE ProductName = ‘Tofu’;

WHERE suplierID IN (9,10,11);

WHERE ProductName= ‘Tofu’ OR ‘sushi’ ‘;

PARA PRIORIZAR LO DE AND SE PONE EN PARENTESIS LO DE OR

WHERE (SupplierID = 9 OR SupplierID=10)

AND UnitPrice >= 15

NOT clause
WHERE NOT City=’London’ AND NOT City= ‘Seattle’;

WILDCARDS:

% recupera más de 1 caracter

LIKE
_ recupera un solo carácter
WHERE Size LIKE ‘_pizza’
MATH OPERATORS AND AGGREGATE FUNCTIONS ARE USE LIKE A SELECT
STATEMENT
Se usa AS para darle nombre al resultado de la operación que se haga con la
información recuperada

SELECT ProductID
, UnitOnOrder
, UnitPrice
, UnitOnOrder * UnitPrice AS Total_Order_Cost
FROM Products

Con funciones se debe usar paréntesis

USAR THE WORD DISTINCT WITHIN THE PARENTHEISS IF WE WANT TO
AVOID DUPLICATE INFORMATION

WHERE Solo filtra por filas mas no por grupos. Por lo que al Usar GROUP BY se
necesita the Having clause

SELECT
CustomerID
,Count (*) AS orders
FROM Orders
GROUP BY CustomerID
HAVING COUNT (*) >=2;

ORDER BY clause para ordenar la información. SIEMPRE VA DE ULTIMO

ESTO PUEDE SER POR EL NOMBRE DE LAS COLUMNAS O LA POSICIÓN DE
ESTAS.
TAMBIEN POR ASCENDENTE (ASC) O DESCENDENTE (DESC)

SUBQUERIES
Son incrustaciones de una segunda estructura (orden) dentro de otra. Sirve para
trabajar con varios recursos “tables” a la vez, pero solo pueden recuperar una
columna. Para filtrar.

COMBINED:
SELECT
Customer_ID
,companyName
,Region
FROM Customers
WHERE CustomerID in (SELECT customerI
FROM orders
WHERE freight >100);

SUBQUERY IN SUBQUERY
Escribirlas con sangría para poder leerlas mejor

SELECT Customer_name, Customer_contact

FROM Customers
WHERE Cust_id IN
SELECT customer_id
FROM orders
WHERE order_number IN (SELECT order_number
FROM orderItems
WHERE prod_name = ‘toothbrush’);

SUBQUERY FOR CALCULATIONS

SELECT Customer_name
, Customer_state
(SELECT COUNT (*) AS orders
FROM orders
WHERE orders.customer_id = customer.customer_id) AS orders
FROM customers
ORDER BY Customer_name;

SELECT
DISTINCT City
,(SELECT COUNT (DISTINCT CustomerId) AS number_of_customers
FROM customers a
WHERE a.City = b.City) AS number_of_customers
FROM customers b
ORDER BY City DESC;

CARTISIAN (CROSS) JOIN : es usado solo para multiplicar cada record de la

prmera tabla por todos de la segunda
SELECT product_name
, unit_price
, company_name
FROM suppliers CROSS JOIN products;

INNER JOIN: recuperaría aquellos datos que estan relacionados o asociados en

ambas tablas

SELECT Suppliers.CompnyName
,productName
,UnitPrice
FROM suppliers INNER JOIN products
ON suppliers.supplierid=
Products.supplierid

INNER JOIN WITH MULTIPLE TABLES

Se puede cambiar la forma en que nos referimos a una tabla poniendo “pre-
qualifiers”

SELECT o. OrdersID, c.CompanyName, e. LastName

FROM ((Orders o INNER JOIN Customer c ON
o.CustomerID= c. CustomerID)
INNER JOIN Employees e ON o.EmployeeID=
e. EmployeeID);

Aliases:
SELECT vendor_name
,product_name
,product_price
FROM Vendors AS v, products AS p
WHERE v. vendor_id= p. vendor_id;

SELF JOIN
Sirve para unir una tabla con ella misma como cuando uno quiere ver todos los
clientes que están en la misma ciudad.

SELECT A.CustomerName AS
CustomerName1, B.CustomerName AS
CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID= B.CustomerID
AND A.City=B.CustomerID
ORDER BY A.City;

LEFT JOIN:
Se usa cuando se quiere recuperar todos los datos de una de tablas además de lo
que este asociado
SELECT c. CustomerName, o.OrderID
FROM Customers C
LEFT JOIN Orders o ON c. customerID= o.customerID
ORDER BY c. CustomerName;

UNION JOINS
Se usan para cuando la información que se necesita junta ha sido recuperada por
aparte. Por lo que necesitan juntar todos los datos.
CREATING TABLES
MODIFIERS
Applied from left to right
NNN days
NNN hours
NNN minutes
NNN.NNNN seconds
NNN years
Start of month
Start of year
Start of day
Weekday N
Unixepoch
Localtime
Utc

STRFTIME: is a function used to extract certain parts of date and time string.
Example:
SELECT Birthdate
,STRFTIME (‘%Y’, Birthdate) AS Year
,STRFTIME (‘%M’, Birthdate) AS Month
,STRFTIME (‘%D’, Birthdate) AS Day
FROM employees

To compute current date

Select Date (‘Now’)

To compute Year, Month, Day for the current Date

SELECT STRFTIME (‘%Y %M %D’, ‘Now’)

To compute Age using Birthdate

SELECT Birthdate
,STRFTIME (‘%Y’, Birthdate) AS Year
,STRFTIME (‘%M’, Birthdate) AS Month
,STRFTIME (‘%D’, Birthdate) AS Day
,Date (‘Now’)- Birthdate) AS age
FROM employees

SELECT
FirstName
,LastName
,DATE(HireDate)
,DATE('NOW')
,DATE ('Now')-HireDate AS time_worked
FROM Employees
WHERE time_worked >=15
ORDER BY LastName ASC;

CASE STATEMENTS
It´s a built-in function that mimics if-then-else statement found in most
programming languages. It is used to classify data, transform variables, or recode
data, and start bin groupings for different statements.
It can be used in SELECT, INSERT, UPDATE, and DELETE statements.
CASE
WHEN C1 THEN E1
WHEN C2 THEN E2
…
ELSE [result else]
END [new column name]
SELECT City, Country FROM
Customers
WHERE country= ‘germany’
UNION
SELECT City, country FROM
Suppliers
WHERE Country= ‘Germany’
ORDER BY city;

CONCATENATIONS:
Used to bring together different text strings
Sql uses + instead of ||

SELECT
ContactName
,CompanyName || ‘ (‘|| ContactName||’)’
FROM customers

If you want the results without parenthesis, do it like this:

SELECT
CustomerId
,FirstName
,LastName
,Country
,UPPER (City||' '||Country||' ')
FROM Customers
SELECT
FirstName
,LastName
,FirstName||LastName||Invoices.InvoiceId
FROM Invoices INNER JOIN Customers
ON Invoices.CustomerId=customers.CustomerId
ORDER BY customers.FirstName ASC;

Trimming strings
TRIM, RTRIM (from the right), and LTRIM (from the left)
To trim the leading or trailing space from a string .

EX:
SELECT TRIM (“ You the best. ”) AS TrimmedString;

Substring
Allows you to pull apart just a portion of the string that you’re looking at. So, we can
shorten a name or create a password with this function. It is required the position
number of the characters within this query.
SUBSTR (String name, string position, number of characters to be returned);

SELECT First_name, SUBSTR

(First_name,2,3)
FROM employees
WHERE department _id=60;

UPPER AND LOWER

SELECT UPPER (column_name) FROM table_name;
SELECT lower (column_name) FROM table_name;
SELECT Ucase (column_name) FROM table_name; (alternative option for
UPPER)

SELECT
FirstName
,LastName
,LOWER (SUBSTR (FirstName,1,4)||SUBSTR(lastName,1,2))
FROM Employees;

Date and Time Strings

This can be formatted in different ways, so we need to know first which it is (how
was it written?)
EX: if you say WHERE PurchaseDate= ‘2016-12-12’; and the date was written
with the time (Yyyy-mm-dd- HH:MI:SS) you will get no results.
SQLite supports 5 Time and Date functions:
DATE(timestring, modifier, modifier,…)
TIME(timestring, modifier, modifier,…)
DATETIME(timestring, modifier, modifier,…)
JULIANDAY(timestring, modifier, modifier,…)
STRFTIME(Format, timestring, modifier, modifier,…)
CASE Input_expression
WHEN when_expression THEN result_expressions […n]
[ELSE Else_result_expression]
END

EXMPLE:
SELECT
EmployeeId
,FirstName
,LastName
,City
CASE City
WHEN ‘Calgary’ THEN ‘Calgary’
ELSE ‘Other’
END Calgary
FROM Employees
ORDER BY LastName, FirstName;
VIEWS
it is a store query. it helps us create an illusion of a table.

CREATE [TEMP] VIEW [IF NOT EXISTS]

View_name(column-name-list)
AS
Select-statement;