# SQL Basics
## Instructions
### My Coding "basics" Guide
- Code
- General order of a query:
1. SELECT (columns) also - column functions (max,distinct...)
2. AS (new column names, relevant only for the printed outcome, not the code)
3. FROM (table)
4. AS (table names, change is retroactive)
5. JOINS (if needed) also includes the ON clause
6. WHERE (filter rows), also - text/logical functions (LIKE, BETWEEN, NOT...)
7. GROUP BY (group results)
8. HAVING (filter grouped results)
9. ORDER BY (sort results)
10. LIMIT (similar to HEAD)
- Working with strings
```sql
/*
Text filtering (LIKE and ILIKE)
using [] allows for matching patterns.
for example, if i want to check a column that starts with a,b,c or d i can do:
*/
SELECT * FROM TABLE WHERE COLUMN LIKE '[a-d]%'
--text substring:
SELECT right(Name,4)
--common patterns:
-- % - any amount of wild letters
-- _ - 1 specific wild card
```
#### Working with NULLS
```sql
/*
merging redundant columns using COALESCE. this function returns the first value that isnt null.
meaning if you have multile columns that arent overlapping, you can use:
*/
COALESCE(s.this_coulmn,p.that_coulmn) AS united_column
/*
and it will return only one column which is essentialy both columns united.
notice that the order matters if they do overlap.
other option is working with is_null, which replaces null values with default value, such as:
*/
is_null(my_city,"none found")
--this replaces null cases in my_city with a text (could also be numeric) value.
```
#### Joins
```sql
-- unions and joins
--joins: left/inner/right according to what you want to keep. adds tables side by side.
SELECT * FROM A
LEFT JOIN B
ON A.ID = B ID
--unions are more simple, they add table under table (more rows). requires same amount of columns and data type.
```
#### Creating/modifying Tables
```sql
--Creating/Modifing tables
--basic syntax:
CREATE TABLE table_name (
column_name DATA_TYPE CONSTRAINT,...)
--example data types: VARCHAR(num_of_chars),smallint,INTEGER
--example CONSTRAINTS: NOT NULL, UNIQUE, PRIMARY KEY, FORIGN KEY (REFERENCES table_name(column_name))
--adding a column
ALTER TABLE table_name
ADD column_name column_type constraint
--adding values to a column
INSERT INTO table_name (column_name)
VALUES (value1,value2,value3...)
```
#### Window Functions
```sql
--summaries vs aggregate without summarising
--group by allows us to create a summary table by groups
--while partition by allows us to add aggregated columns as a new columns without summarising (reducing amount of rows)
--see example:
SELECT MAX(price) FROM table_name GROUP BY CITY
SELECT MAX(price) FROM table_name OVER (PARTITION BY CITY)
--leaving the OVER empty means that it will run for each row without grouping.
--thus creating window function.
--over can also contain other functions, not just grouping vars.
SELECT RANK() OVER(ORDER BY grade) FROM table_name
--this for exmp will create a rank by grades.
```
#### Calculated Columns
```sql
/*
you can always add a column through the "alter table" column.
another option is just to show a calculated column in the query.
for exmp, if you have a table with a,b,c as sizes of shape sizes,
you can add a column which gives back what type of shape it is:
*/
SELECT CASE WHEN
A+B<=C OR A+C<=B OR B+C<=A THEN 'Not A Triangle'
WHEN
A=B AND B=C THEN 'Equilateral'
WHEN A=B OR B=C OR A=C THEN 'Isosceles'
ELSE 'Scalene'
END
FROM TRIANGLES
```
#### CTE and Views
```sql
--there is something called CTE, which is similar to view and is an alternative to nested queries, saves a certain table/summary, so you dont
--have to run it again and again. example of defining a CTE:
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear) -- WITH table_name (column_names)
AS
-- Define the CTE query.
(
SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
FROM Sales.SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
)
--creating view, is similar, you just add "create view" before the select
CREATE VIEW view_name AS
SELECT...;
```
#### Generating Variables outside of a Table
```sql
SET @row := 0;
select repeat('* ', @row := @row + 1) from information_schema.tables where @row < 20
```
#### Misc
```sql
--alternitavly you can use create procedure which is like saving a view as a function, that you can also use function arguments
-- to modify the results each time without writing the whole query again.
CREATE PROCEDURE procedure_name(IN var_name data_type)
CALL procedure_name
--triggers: you can define triggers, which are functions that will run each time based on the trigger. the trigger can be INSERT, UPDATE, DELETE TABLE
--general code examples
--this is how to select lowest and highest entry of a specific column (in this case - city length)
--where there are multiple rows matching the criteria (thus - the LIMIT). the "as s" is needed. not sure why.'
SELECT s.* FROM
((SELECT city, LENGTH(city) from Station ORDER BY LENGTH(city),city LIMIT 1)
UNION ALL (SELECT city, LENGTH(city) from Station ORDER BY LENGTH(city) DESC,city LIMIT 1))
AS s;
```
## Overview
🔼Topic:: [[SQL (MOC)]]
◀Origin::
🔗Link::