SQL Basic

= Nonrelational database stystems =
  * hierarchical database stystems
  * network database ssystem


= Relational model =
1970 Codd proposed that data be represented as sets of tables

Each table in a relational database includes information
that uniquely identifies a row in that table - known as primary key

Some of the tables also include infomarmation used to navigate to another
data - known as foreign key

The process of refining a database design to ensure that each independent
piece of information in in only one place
(except fo foreign keys) is knows as noramalization

SQL is divided into several distinct parts:
  * Schema statements - used to define the data structures stored in the
    database
  * Data statements - used to manipulate the data structures  previosly
    defined using SQL schema statements
  * Transaction statements - used to begin, end, and roll back
    transactions

With SQL, however, you will need to give up some of the control you are used to,
because SQL statements define the necessary inputs and outputs, but the manner in
which a statement is executed is left to a component of your database engine known
as the optimizer.

= Character Data =
Char - use when all strings to be stored in the columns of the same length
Examples:
State abrevation: CA, NY
Yes/No flags: Y/N
Sex: M/F

Varchar - varying length

If you need to store longer strings(such as emails, XML) then you
will want to use ne of text types - mediumtext,
longtext

= Numeric Data =
Integer types - tinyint, smallint, mediumint, int, bigint
Floating-point types - float, double

Decimal calculaion is exact
Float = ~ 7 digits approximate
Double = ~ 15 digits approximate

= Temporal Data =
Date types - Date, Datetime, Timestamp, Year, Time

Date - 'YYYY-MM-DD' stroring date, but no the time
Time - 'HH:MM:SS' stroring time, but no the date
Datetime - data + time, format: 'YYYY-MM-DD HH:MM:SS'

select curdate();
select curtime();
select now();
select date_format(curdate(), '%D %M %Y'); // 7th February 2018

= Timestamp =
Timestamp is like datetime but have smaller range and takes less memory space

create table comments(
  content varchar(100),
  created_at timestamp default now()
);

-------------------------------------------------------------
= Basic operations =
show databases;

create database shirts_db;

create table shirts (
shirts_id int not null auto_increment primary key,
article varchar(100),
color varchar(100),
shirts_size char(1),
last_worn int
);

insert into shirts (article, color, shirts_size,
last_worn)
values ("t-shirt", "white", "S", 10);

select article, color, shirt_size, last_worn from shirts
where shirt_size="M";

update shirts set shirt_size="L" where article="polo
shirt";

delete from shirts where last_worn=200;

drop table shirts;

drop database shirts_db;

select database();

-----------------------------------------------------------

= String functions =
select book_id, concat(author_fname, " ", author_lname)
as "full name" from books;
select substring("Hello world", 1, 7);
select replace(title, "The", "Changed") as title from books;
select reverse(title) as title from books;
select char_length(title) as "title length" from books;
select concat(author_lname, " is ", char_length(author_lname), " characters
long") from books;

select distinct author_lname from books; - without duplicates

= Refining =
select * from books order by author_fname;
select * from books order by released_year desc limit 10;
select * from books where author_fname like "%Dan%";
select author_fname from books where author_fname like "Da%"; - search that
starts from "Da"
select author_fname from books where author_fname like "_____";- author name is
5 character long
Example:
select concat(title, "-", released_year) as summary from books order by
released_year desc limit 3;

= Aggregate functions =
select count(*) from books;
select author_lname, count(*) from books group by author_lname;
select min(released_year) from books;
select max(released_year) from books;

subquery:
  - select * from books where pages = (select min(pages) from books);

select author_fname, author_lname, min(released_year) from books group by author_lname, author_fname;

select author_fname, author_lname, sum(stock_quantity) from books group by author_lname, author_fname;

select released_year, avg(stock_quantity) from books group by released_year;

= Logical operators =

select * from books where released_year != 2017;
select title from books where title not like 'C%';

>= > < <=

&& AND

Between / Not between
select * from books where released_year between 2004 and 2015;
select * from books where released_year not between 2004 and 2015;

In / Not in
select * from books where author_lname in ("Carver", "Lahiri", "Harris");
select * from books where author_lname not in ("Carver", "Lahiri", "Harris");

Modulo
select * from books where released_year % 2 != 0;

= JOINS =

Relationships
 * One to one: customer -> customer-details
 * One to many: book -> reviews
 * Many to many: books -> authors

 create table orders(
  id int auto_increment primary key,
  order_date date,
  customer_id int,
  foreign key(cutomer_id) references customers(id)
 );

= Inner Join =
-- Implicity inner join
select * from orders, customers where customers.id = orders.customer_id;

-- Explicit
select * from cutomers join orders on customers.id = orders.customer_id;

select first_name, last_name, sum(amount) as total_spent
  from customers
  join orders on customers.id = orders.customer_id
  group by orders.customer_id order by amount desc;

= Left join =
select
  first_name,
  last_name,
  ifnull(sum(amount), 0) as total_spent
  from customers
  left join orders on customers.id = orders.customer_id
group by customers.id order by amount desc;