-- Select ex)
select * from information_schema.TABLES
where table_schema = 'person';
select * from information_schema.TABLES
where table_schema = 'production';
select * from information_schema.TABLES
where table_schema = 'purchasing';
select * from information_schema.TABLES
where table_schema = 'sales';
select * from information_schema.COLUMNS
where table_schema = 'person' AND table_name = 'address';
-- SELECT DISTINCT : used to return only distinct (different) values.
select DISTINCT * from person.address;
-- MIN and MAX : returns the smallest, largest value of the selected columns.
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
-- WHERE : used to filter records
SELECT column1, column2 FROM tablename WHERE condition;
-- WHERE clause can contain one or many operators below.
-- AND : is used to filter records based on more than one condition.
SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';
-- OR : is used to filter records based on more than one condition.
SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';
-- IN : is a shorthand for multiple OR conditions
-- return all customers from them.
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
-- NOT IN
-- retunr all customers NOT from them.
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
-- NOT : is used in combination with other operators to give the opposite result, also called the negative result.
SELECT * FROM Customers
WHERE NOT Country = 'Spain';
-- NULL VALUE : A field with NULL value is a field wiht no value, you can sort this out.
-- NULL Syntax
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
-- IS NOT NULL syntax
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
-- COUNT : returns the number of rows that matches a specifed criterion.
SELECT COUNT(ProductID)
FROM Products
WHERE Price > 20;
-- SUM : returns the total sum of a numeric columns.
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductId = 11;
-- AVG : returns the average value of a numeric columns.
SELECT AVG(Price)
FROM Products
WHERE CategoryID = 1;
SELECT * FROM Products
WHERE price > (SELECT AVG(price) FROM Products);
-- LIKE : is used in a WHERE clause to search for a specified pattern in a columns.
-- % : represents zero, one, or multiple characters.
-- _ : underscore sign represents one, single character.
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
SELECT * FROM Customers
WHERE city LIKE 'L_nd__';
-- return all city that contains "L"
SELECT * FROM Customers
WHERE city LIKE '%L%';
SELECT * FROM Customers
WHERE CustomerName LIKE 'La%';
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%' OR CustomerName LIKE 'b%';
SELECT * FROM Customers
WHERE CustomerName LIKE '%a';
-- retunr all customers that starts with "b" and ends with "s"
SELECT * FROM Customers
WHERE CustomerName LIKE 'b%s';
-- return all customers that starts with "a" and are at least 3 characters in length
SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';
-- return all customers that have "r" in the second position
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';
-- [] : return all customers starting with either "b", "s", or "p"
SELECT * FROM Customers
WHERE CustomerName LIKE '[bsp]%';
-- Return all customers starting with "a", "b", "c", "d", "e" or "f"
SELECT * FROM Customers
WHERE CustomerName LIKE '[a-f]%';
-- BETWEEN : selects values within a given range. the values can be numbers, text, or dates.
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
-- BETWEEN with IN
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND CategoryID IN (1,2,3);
-- BETWEEN Text Values
-- selects all products with a ProductName alphabetically between Carnarvon Tigers and Mozzarellla di Giovanni
SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;
-- BETWEEN dates.
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
-- ORDER BY : used to sort the result-set in ascending or descending order
SELECT column1, column2 FROM tablename ORDER BY column1 ASC/DESC; -- can use them at the same time with different columns.
-- GROUP BY : used to group the result-set by one or more columns
SELECT column1, column2, COUNT(*) FROM tablename GROUP BY column1;
-- EX)
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
-- HAVING : is used with the HAVING clause to specify a filtering criteria for
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
--EX)
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
-- LIMIT : is used to specify the number of records to return.
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
-- Aliases : are used to give a table, or column in a table, a temporary name.
SELECT CustomerID AS ID
FROM Customers;
-- AS is optional.
SELECT CustomerID ID
FROM Customers;
-- JOINS : is used to combine rows from two or more tables, based on a related column between them.
-- INNER JOIN : selects records that have matching values in both tables.
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
-- INNER JOIN = JOIN
-- LEFT JOIN : returns all records from the left table, and the matching records from the right table. the result is 0 records from the right side, if there is no match.
-- No matter what LEFT table is matched or not, all data will be shown.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
-- RiGHT JOIN : the opposite of LEFT JOIN.
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
-- FULL JOIN : matched row, not matched row will be shown.
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
-- SELF JOIN : is a regular join, but the table is joined with itself.
-- matches customers that are from the same city.
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.City
ORDER BY A.City;
-- UNION : is used to combine the result-set of two or more select statements.
-- Every SELECT statement within UNION must have the same number of COLUMNS
-- the columns must also have similar data types.
-- the columns in every SELECT statement must also be in the same order
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
-- UNION ALL : if some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values.
-- UNION ALL selects duplicate values.
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
-- EXISTS : is used to test for the exstence of any record in a subquery.
-- returns TRUE if the subquery returns one or more records.
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
SELECT SupplierName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);
-- ANY and ALL : to perform a comparison between a single column vaue and a range of other values.
-- Any means at least one value has to match. All means all values have to match.
-- The keyword IN can also be used instead of ANY or ALL.
-- it finds ANY records in the OrderDetails table has Quantity equal to 10
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
-- it finds ANY records in the OrderDetails table has Quantity larger than 99
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity > 99);
-- SELECT INTO : copies data from one table into a new table.
SELECT * INTO CustomersBackup2017
FROM Customers;
-- IN : to copy the table into a new table in another database.
SELECT * INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;
-- INSERT INTO : insert a new data in the table.
insert into person.address (id, street1, city)
values (203, "456 ELM", "Des Plaines");
-- INSERT INTO SELECT : copies dat from one table and inserts it into another table.
-- copy only the German suppliers into "Customers"
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';
-- CASE : expression goes through conditions and returns a value when the first condition is met(like an if-then-else statement).
-- so, once a condition is true, it will stop reading and return the result. if no conditions are true, it returns the value in the ELSE clause.
-- it will add a column for the result.
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;
-- order the customers by city. however, if city is NULL, then order by country.
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);
--IFNULL, NULLIF, ISNULL, COALESCE, NVL : if the data is NULL value, it returns an alternative value.
-- the function depends on SQLs
-- MYSQL
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;
--SQL Server
SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products;
--PostgreSQL
SELECT ProductName, UnitPrice * (UnitsInStock + NULLIF(UnitsOnOrder, 0))
FROM Products;
--Oracle
SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0))
FROM Products;
-- Stored Procedures : a prepared SQL code that you can save, so the code can be resued over and over again.
-- PostgreSQL
CREATE OR REPLACE PROCEDURE create_invoice(
IN customer_name TEXT,
IN total_amount NUMERIC,
IN invoice_date DATE
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO invoices (customer_name, total_amount, invoice_date)
VALUES (customer_name, total_amount, invoice_date);
END;
$$;
-- \df : can list them using it in psql.
-- Caliing it.
CALL create_invoice('Customer 1', 100, '2023-07-20');
--Verify it.
select * FROM invoices;
-- Delete
delete from person.address
where id = 203;
-- UPDATE
update person.address set city="Waukegan"
where id=789;