# SQL
-------

### 1. Fundamental statement
#### 1.1 select

Retrieve data from table

select column_name1, column_name2,... from table_name

Retrieve unique value data in columns from table

select distinct(column1, column2) from table_name

#### 1.2 count()

Return number of input row that match specific condition

select count(name) from table; 

This line return number of rows.

select count(distinct name) from table

Return num of unique name in tables

#### 1.3 select where
where allow pecifying condition on rows to be returned

select col1,col2 from table where conditions

where clause appear after from clase of select statement, condition

Types of conditions
1. Comparision (=,>,<,>=,<=,<>,!=)
2. Logical operators (and, or not)

#### 1.4 order by, limit, between, in
Order by is used to sort rows based on column value

select col1, col2 from table order by col1 asc/desc

Limit use to limit number of rows for a query to get top rows

select * from payment where amount !=0 order by payment_date desc limit 5

Between is the same as combine of >= and <=, can also combine with not, often use with dates (ISO 8601)

select count(*) from payment where amount not between 8 and 9;

In is use to check if values in number od options, can combined with not

select color from table where color in ('red','blue',...)

#### 1.5 like, ilike
often use to match with string pattern with wildcard characters.

Wildcard character
1. % - match any sequence
2. _ - match any single character

All names begin with 'A'

where name like 'A%'

All names end with 'a'

where name like '%a'

like use for case-sensitive, if case-insenitive can use ilike

Each _ can matach a character

where value like 'Version#__'

Can combine % and _

where name like '_her%'

Cheryl, Theresa, Sherri,...

### 2. group by
#### 2.1 Aggregate function
Take mul inputs and return single output
Most commom aggregate functions:
1. avg()
2. count()
3. max()
4. min()
5. sum()

Happen only in select or having clause

select max(replacement_cost) min (replacement_cost) from film;

select round(avg(replacement_cost),2) from film;

select sum(replacement_cost) from film;

#### 2.2 group by

select category_col, agg(data_col) from table where category_col!='A' group by category_col

Note:
1. group by must after right after from or where statement.
2. In the select statment, col mus have agg function or be in group by call.
3. where statement should not refer to agg result, should use where before group by, use having if want to perform thhis case
4. If sort result based on aggregate, make sure to reference the entire funtion.

#### 2.3 having

Use to filter after group by call.
Cannot use where to filter based off aggregate becuz it happend after where statement -> use having

select company, sum(sales) from financial_table where company != 'Google' group by company having sum(sales) > 1000

### 3. JOINS
#### 3.1 AS statement
create alias for a column or result

select colum as new_name from table
select sum(col) as new_name from table

Colummn As gets executed at the end of the query -> cannot use alias inside a where/having operator but table alias can be use.

#### 3.2 Inner join
Return record that match in both tables

select * from table1 innner join table2 on table1.col_match = table2.col_match

select reg_id, Logins.name, log_id from registrations inner join Logins on registration.name = Logins.name

Note
1. table order dont matter
2. just JOIN will be treated as Inner join
3. .name if name is in both table

#### 3.3 Full Outer join
Outer join allow us to specify how to deal with values only present in one of table being joined

Types:
1. full outer join
2. left outer join
3. right outer join

Full outer join grab everything from both table

select * from table1 full outer join table2 on table1.col_match = table2.col_match

Value that match will appear first, null value appear if a value only in one table.

Full outer join with where null to excluse all match value

select * from table1 full outer join table2 on table1.col_match = table2.col_match where table1.id is null or table2.id is null

#### 3.4 Left/Right Outer join
Order matters

select * from table1 left outer join table2 on table1.col_match = table2.col_match

The result contains table 1 content which os matched with table2, if table1 content not match it filled with null.

Exclusive match value by where null

select * from table1 left outer join table2 on table1.col_match = table2.col_match where table2.id is null

Only content of table that has match in table2

JOIN can be used multiply

select title ,first_name, last_name from film_actor 
join actor on film_actor.actor_id = actor.actor_id 
join film on film.film_id = film_actor.film_id 
where first_name = 'Nick' and last_name = 'Wahlberg';

#### 3.5 Union
Use to combine two or more select statments

select col(s) from table1 union select col(s) from table2

#### 3.6 Offset-fetch
Offset use to define the the starting point to return rows from a result set.
Basically, it exclude the first set of records
OFFSET can only be used with ORDER BY clause. It cannot be used on its own.
OFFSET value must be greater than or equal to zero. It cannot be negative, else return error.

select * from payment order by payment_id offset 5 rows

Fetch use to define next row, can only use in conjunction woth offset

SELECT column_name(s)
FROM table_name
ORDER BY column_name
OFFSET rows_to_skip
FETCH NEXT number_of_rows ROWS ONLY;

select * from payment order by payment_id offset 5 fetch next 5 rows only

#### 3.7 except

SELECT Name
       FROM Students
EXCEPT
SELECT NAME
       FROM TA;

return the records in first select but not in second select not support by mySQL

#### 3.8 over
Use to determin which rows will be affect by previous funtion

select Score, dense_rank() over(order by Score desc) as Rank from Scores;

dense_rank() use to rank row with no gap

### 4. Advance SQL commands
#### 4.1 Timestamps and extract
date and time datatype
1. Time - only time (H/M/s)
2. Date - only date (m/d/y)
3. timestamp - date and time
4. timestamptz - date, time, timezone

Some funtion and opearations related to this data types:
1. timezone()
2. now
3. timeofday
4. current_time
5. current_date
6. Datediff(date1, date2)

show timezone
select now()
select timeofday()
select current_time
select current_date

extract() use to extract a sub-component of a date value

extract(year from date_col)

age() cal and return the current age given a timestamp

age(date_col)

to_char() convert datatypes to text

to_char(date_col, 'mm-dd-yyyy')

use trim() or 'FM' to exclusive the space or extract(dow)
#### 4.2 Mathematical function and operaators
operator: +,-,*,/,@

func: floor(), ceil(), abs(), pi(), round(), sqrt(), sign(), pow()

#### 4.3 String funtion
1. string || string : concatnate
2. string || non-string : concatnate
3. char_length(): num of char in string
4. lower()
5. overlay(string placing string from int [for int]) - replace substring
6. position(sub in string) - position of substring
7. substring(string [from int] [for int]) - extract substring
8. upper
9. trim()
10. concat()
11. length()
12. left(text, int)

select upper(first_name) || ' ' || upper(last_name) as full_name from customer

#### 4.4 subquery

select student, grade from test_score where grade > (select avg(grade) from test_scores)

Subquery run first (inside out)

Can operate on a separate table

select student, grade from test_scores where student in (select student from honor_roll_table)

exist() use to test for existance of rows in a subquery, can be use with not

select col from table where exists (select col form table where condition)

AS can be use for table alias in subquery even in where statement

select first_name, last_name from customer as c where exists(select * from payment as p where p.customer_id = c.customer_id and amount > 11)

alias of outer query table can be use in subquery

#### 4.5 self-join
Table is join on itself, use to compare calues in columns od rows within the same value

select tableA.col, tableB.col from table as tableA join table as tableB on tableA.some_col=tableB.other_col

### 5. Creating database and table
#### 5.1 Data types
1. Boolean: True/False
2. Character: char, varchar, text
3. Numeric: integer, floating-point number
4. temporal: date, time, timestamp, tnterval
5. uuid - universally unique identify
6. Array - array of string, num, etc
7. JSON
8. Hstore key-value pair
9. network address, geometric data

#### 5.2 Primary keys and foreign key

Primary key is a col or group of cols used to identify a row uniquely in a table, 1 table can only have 1 PK
Allow easily discern to what cols should be use in join
Value in PK col are unique and not null

Foreign key is field or group of field uniquely refer to PK of another table
Table contains FK calls referenceing table or child table
Table which FK refer is parent table
Each table can have multiple foreign key

Use constraint to define col as being PK, FK or attaching FK relationship to another table

#### 5.3 Constraints
Rules enforce on data col table
Used to prevent invslid data to be entered in table
Ensure accuracy and reliability

2 categories
1. Column constraint
2. Table constraint

Common column constraint
1. not null - ensure col cannot have null value
2. unique - all value in col are different
3. Primary key - uniquely identify each rows in a table
4. Foreign key - constraint data based on cols in other table
5. check constrain - ensure all value in a col satisfy certain condition
6. exclusion constrain - ensure that if any 2 roes are compare on specified col or exxpression using operator, not all of these comp return True

Table constraint
1. check - check a condition when insert or upfdate data
2. reference - value store in the column must exist in another table
3. unique(col list) - forces the calues stored in the col lists inside () to be unique
4. PK(col list) - allow to define PK consist of multiple cols

#### 5.4 Create

create table table_name (col_name1 Type col1_constraint, col_name2 typr col2_constraint, table_constraint table_constraint) inherits existing_table_name;

simple create

create table table_name (col_name1 Type col1_constraint, col_name2 typr col2_constraint);

In postgre, sequence is a special object that generate a sequence of int, often use as PK
Serial data type, create sequence of object, nit reformat if a row removed

create table players (player_id serial primary key, age smallint not null)

Example

create table account (
	user_id serial primary key,
	username varchar(50) unique not null,
	password varchar(50) not null,
	email varchar(250) unique not null,
	created_on timestamp not null,
	last_login timestamp
)

create table job(
	job_id serial primary key,
	job_name varchar(200) unique not null
)

create table account_job(
	user_id integer references account(user_id),
	job_id integer references job(job_id),
	hire_date timestamp
)

#### 5.5 Insert
Insert used to add in rows to a table

insert into table(col1, col2,...) [values(val1, val2,...)/ select col1, col2,.. from another_table where condition];

Insert row value must match up for table, including constraint
Serial cols do not need to be provide a value

insert into account(
	username, password, email, created_on
)
values
('Jose','password','[e-mail]',current_timestamp)

insert into job(job_name)
values
('Astronaut')

insert into account_job(user_id, job_id, hire_date)
values
(1,1,current_timestamp)

#### 5.6 Update
alllow chagne the values of col in tablle

update table set co1 = val1 col2=val2 where condition;

update account set last_login = current_timestamp where last_login is null

Set can used base on other cols

update account set last_login = created_on

Use another table's values (update join)

update table1 set org_col = table2.new_col from table2 where table1.id = table2.id

Return affect row with returning

update account set last_login = creted_on returning account_id, last_login

Example

update account 
set last_login = current_timestamp

update account 
set last_login = created_on

update account_job set hire_date = account.created_on from account where account_job.user_id = account.user_id

#### 5.6 Delete

delete rows

delete from table where row_id =1

can delete row base on presense of other table with using

delete from table1 using table2 where table1.id=table2.id

delete all rows

delete from table

returning can also be used with delete

#### 5.7 Alter
change existing table structure
1. add, drop, rename colums
2. change col data type
3. set default values for a column
4. add check constraint
5. rename table

alter table table_name action

Ex: add cols

alter table table_name 
add column new_col type

Remove column

alter table table_name 
drop column col_name

alter constraint

alter table table_name 
alter column col_name
add constraint constraint_name

alter table table_name 
alter column col_name
set default value

alter table table_name 
alter column col_name
drop default

alter table table_name 
alter column col_name
drop not null

alter table table_name 
alter column col_name
drop set not null

alter table information
rename to new_info

alter table new_info
rename column person to people

alter table new_info
alter column people drop not null

#### 5.8 drop
drop column col in a table, remove all indexes and constraint
not remove cols used in views, trigger or store procedure without addition cascade clause

alter table table_name
drop column col_name cascade

alter table table_name
drop column col_name if exist col_name

alter table table_name
drop column col1 ,
drop colum col2

#### 5.9 check constraint

create table example(
    ex_id serial primary key,
    age smallint check(age>21),
    parent_age smallint check(parent_age>age)
)

### 6. Conditional expression and Procedure
#### 6.1 Case
General case syntax

case
    when cond1 then res1
    when cond2 then res2
    else some_other_res
end

Insert the case inside select call

select a,
case 
    when a=1 then 'one'
    when a=2 then 'two'
    else 'other' 
    as label
end
from test;

Additional column name label(default is case if not use as) appear with value 'one', 'two' , 'other'
Seems quite like if-else in programming

Case expression syntax, often used if need check only for equality

case expression
    when value1 then res1
    when value2 then res2
    else other
end

select a,
case a
    when 1 then 'one'
    when 2 then 'two'
    else 'other'
end
from test

Seems quite like switch-case in programming

select customer_id,
case
	when (customer_id<=100) then 'Premium'
	when (customer_id between 100 and 200) then 'Plus'
	else 'Normal'
end as customer_class
from customer

select customer_id,
case customer_id
	when 2 then 'Winner'
	when 5 then 'Runner Up'
	else 'Normal'
end as raffle_result
from customer

case consider as cloumn in return -> can use with sum, avg

select
sum(case rental_rate
	when 0.99 then 1
	else 0
end) as numner_of_bargains
from film

This perfoem group by action under the hood.

select
sum(case rental_rate
	when 0.99 then 1
	else 0
end) as bargains,
sum(case rental_rate
	when 2.99 then 1
	else 0
end) as regular
from film

select
sum(case rental_rate
	when 0.99 then 1
	else 0
end) as bargains,
sum(case rental_rate
	when 2.99 then 1
	else 0
end) as regular,
sum(case rental_rate
	when 4.99 then 1
	else 0
end) as premium
from film

Use to count how many of some sepcific value.

#### 6.2 coalelse
Accept unlimited num of argument, return the first not null

select coalesle(1,2)

return 1

select coalesle(null,2,3)

return 2
If all null -> return null

Use when need to execute math among cols that may have null.

select item, (price-coalesle(discount,0)) as final from table

#### 6.3 cast
Convert dattype to another

select cast('5' as integer)

Postgre cast op

select '5'::integer

Often use in select query

select cast(date as timestamp) from table

select char_length(cast(inventory_id as varchar) from rental)

#### 6.4 nullif
Take 2 input, return null if both eual, otherwise return forst arg passed
Use qhen know a case may have unwanted error

select(
sum(case when department = 'A' the 1 else 0 end)/
nullif(sum(case when department = 'B' the 1 else 0 end),0)
) as department_ratio
from depts

avoid division by 0.

#### 6.5 views
A db object that store query
View can be accessed as a virtual table in PostgreSQL
View does not store value, it store the query
View can also be update and alter

create view as query

create view customer_info as
select first_name, last_name, address from customer
inner join address 
on customer.address_id = address.address_id

select * from customer_info

Replace view

create or replace view customer_info as
select first_name, last_name, address, district from customer
inner join address 
on customer.address_id = address.address_id

Remove views

drop view if exist customer_info 

Change name

alter view customer_info rename to c_info

#### 6.6 Import/Export data

Import
1. Create table

create table simple(a integer,b integer,c integer)

2. Grab info from files (Postgre)
1. Import the file , pick table to import
2. Fix things in column tab

Export, pick table to export

### 7. Postgre with Python

import psycopg2 as pg2
# In[ ]:
# Create a connection with PostgreSQL
# 'password' is whatever password you set, we set password in the install video
conn = pg2.connect(database='dvdrental', user='postgres',password='viet1234')
# In[ ]:
# Establish connection and start cursor to be ready to query
cur = conn.cursor()
# In[ ]:
# Pass in a PostgreSQL query as a string
cur.execute("SELECT * FROM payment")
# In[ ]:
# Return a tuple of the first row as Python objects
dt1 = cur.fetchone()
print(dt1)
dt2 = cur.fetchone()
print(dt2)
# In[ ]:
# Return N number of rows
dt10 = cur.fetchmany(10)
print(dt10)
# In[ ]:
# Return All rows at once
cur.fetchall()
# In[ ]:
# To save and index results, assign it to a variable
cur.fetchmany(10)
# because use fetch all before so there no data left
# **Inserting Information**
# In[2]:
query1 = '''
        CREATE TABLE if not exists new_table (
            userid integer
            , tmstmp timestamp
            , type varchar(10)
        );
        '''
# In[ ]:
cur.execute(query1)
# In[ ]:
# commit the changes to the database
conn.commit()
# In[ ]:
# Don't forget to close the connection!
# killing the kernel or shutting down juptyer will also close it
conn.close()