SQL Boot Camp:

What is Databases?
A collection of data with accessible interface.

Use of groom online IDE:

Commands:
mysql-ctl cli- use the MySQL terminal
Create database; - to create a database
Use database; - to use a database
Show databases; - shows all the databases
Drop database <name of database>; - deletes a database.
Select database(); - shows the database I use. Shows null instead.
Show tables; - shows the number of tables available in current database.
Show cloumns from <table_name>; - describes the columns and their
datatypes.
OR
Desc <table_name>;
Drop table <table_name>; -drops the table you are using in the current
database
Insert into <table_name> ( column_1, column_2,…)<optional unless you have a
different way of inserting the data> values(value1, value2, value3);
Tables= rows x columns
-
String Functions:
CONCAT- concatenates two or more columns into one single column
CONCAT_WS ( concatenate with separator)- concatenates the columns with
separator in between.

Ex: select concat(Cloumn_1, column_2, column_3) from books;

Seslect concat_ws(‘seperator’, column_1, column_2,column_3);
SUBSTRINGS:
Gets substring from a string
Ex: select substring( column_name, starting point, ending point);
Or
select substring( column_name, -from ending point);
select substring( title,-7);

AS operator:
As operator helps in creating alias and it also works on functions.
The AS operator gets executed at the end of the query so we cannot
use AS operator inside WHERE and GROUP BY operator.
 EX:
SELECT COUNT(*)

AS transaction

FROM payment

Where Clause:
- Appears right after from clause of the select statement
- A query with where clause evaluates where condition and only
returns those rows that satisfy the condition
- It is evaluated after from and before select and order by
- Can be used with UPDATE AND DELETE STATEMENTS

REPLACE:
The REPLACE() function, as well as the other string functions, only
change the query output, they don't affect the actual data in the
database.

SELECT REPLACE(title, 'e ', '3') FROM books;

REVERSE:
reverses a string in SQL

EX: SELECT CONCAT(author_fname, REVERSE(author_fname)) FROM books;

CHAR_LENGTH: tells the length of a string passed to the function

1. Ex: SELECT author_lname, CHAR_LENGTH(author_lname) AS 'length' FROM books;

OR

2. SELECT CONCAT(author_lname, ' is ', CHAR_LENGTH(author_lname), ' characters long')

FROM books;

UPPER AND LOWER:

1. SELECT CONCAT('MY FAVORITE BOOK IS ', UPPER(title)) FROM books;
2.  

3. SELECT CONCAT('MY FAVORITE BOOK IS ', LOWER(title)) FROM books;

Lowers or uppers the case of the string used in command.

DISTINCT:
1. SELECT DISTINCT CONCAT(author_fname,' ', author_lname) FROM books;

SELECT DISTINCT author_fname, author_lname FROM books;

ORDER BY:
1. SELECT title FROM books ORDER BY title;

2. SELECT author_lname FROM books ORDER BY author_lname DESC;

LIMIT:
SELECT
   *
FROM
   table
LIMIT n OFFSET m;

The statement first skips m rows before returning n rows generated by the query. If m is
zero, the statement will work like without the OFFSET clause.

LIKE & NOT LIKE:

Works more of like a regex expression to find out a field whose some
part matches with the query.

EX: SELECT
   first_name,
        last_name
FROM
   customer
WHERE
   first_name LIKE '%er%'

EX: SELECT

   first_name,
   last_name
FROM
   customer
WHERE
   first_name NOT LIKE 'Jen%';

ILIKE & NOT ILIKE:

Same as like but case insensitive.

EX: SELECT
   first_name,
   last_name
FROM
   customer
WHERE
   first_name ILIKE 'BAR%';

wildcard characters used with ‘LIKE’:

‘%aaa%’- looks for any field which has aaa in it with any
number of letters in front or behind it.

‘_’ – the number of underscores tells about the number of

cases allowed for in the search query . Here it is only
one underscore.

BETWEEN:
Between operator matches value from a rang of values

Ex:
SELECT
customer_id,
payment_id,
amount
FROM
payment
WHERE
amount NOT BETWEEN 8 AND 9;
EX:
SELECT
customer_id,
payment_id,
amount,
 payment_date
FROM
payment
WHERE
payment_date BETWEEN '2007-02-07' AND '2007-02-15';

IN:
In operator is used with Where clause to check if a value matches
any value in the list of values.
SELECT customer_id,
rental_id,
return_date
FROM
rental
WHERE
customer_id IN (1, 2)

NOT IN:
NOT combined with IN operator gives all the value which are not in
the list.
EX:
SELECT
customer_id,
rental_id,
return_date
FROM
rental
WHERE
customer_id NOT IN (1, 2);

Aggregate functions:
STRING_AGG():The PostgreSQL STRING_AGG() function is an
aggregate function that concatenates a list of strings and places a
separator between them. The function does not add the separator at
the end of the string.
STRING_AGG ( expression, separator [order_by_clause] ):
expression is any valid expression that can resolve to a character
string. If you use other types than character string type, you need
to explicitly cast these values of that type to the character string
type.
separator is the separator for concatenated strings.
The order_by_clause is an optional clause that specifies the order
of concatenated results
EX:
SELECT

country,
STRING_AGG (email, ';') email_list
FROM
customer

SUM: returns the sum of values or distinct values. It ignores

null values.
Ex: SELECT

SUM (amount) AS total

FROM
payment
WHERE
customer_id = 2000;

AVG: it returns a floating-point average value to many decimal

places.
EX: SELECT
to_char(
AVG (amount),
'99999999999999999D99'
) AS average_amount
FROM
payment;

ROUND: the round function rounds of the decimal average to a

certain decimal points as specified in the query.

Ex: SELECT
ROUND(AVG(replacement_cost),2)

FROM

film;
Count: The COUNT(*) function returns the number of rows returned by a
SELECT statement, including NULL and duplicates.

EX: SELECT
   COUNT(*)
FROM
   table_name
WHERE
   condition;

COUNT(DISTINCT column)
In this form, the COUNT(DISTINCT column) returns the number of unique non-null
values in the column.

GROUP BY:
It Is an aggregate function
The GROUP BY clause divides the rows returned from the SELECT
statement into groups. For each group, you can apply an aggregate
function e.g., SUM() and COUNT().
EX:
SELECT
column_1,
column_2,
aggregate_function(column_3)
FROM
table_name
GROUP BY
column_1,
column_2;

SELECT
customer_id,
SUM (amount)
FROM
payment
GROUP BY
customer_id;

Note: In select statement columns must be either have an aggregate

function or be in the GROUP BY call.
To use group by clause on time function we need to change the Time
stamp into date by using DATE() clause.
EX: Select DATE(payment_date) from payment.
HAVING:
We often use the HAVING clause in conjunction with the GROUP BY
clause to filter group rows that do not satisfy a specified
condition.
Having allows to use aggragate function result as a filter with a
group by

Ex: SELECT

column_1,
aggregate_function (column_2)
FROM
tbl_name
GROUP BY
column_1
HAVING
condition;

SELECT
customer_id,
SUM (amount)
FROM
payment
GROUP BY
customer_id
HAVING
SUM (amount) > 200;

Relationships and Joins:

A SQL join is a query which joins/ combines two sets of data.
Allows to combine multiple tables together.
Different joins help us to decide how to deal with information
Types of joins/relationship:

One to One relationship

One to many relationships: Relation between Customers and orders

is a classic example of one to many relationship where each customer
can have many orders but each orders has only one customer.
Many to One relationship
Many to many relationships

Cross joins:
A CROSS JOIN clause allows you to produce a Cartesian Product of
rows in two or more tables.
Select * from customers, orders is a typical example of cross join
where each row of a each table is multiplied to each row of other
table.

EX:
SELECT *
FROM T1
CROSS JOIN T2;
OR
SELECT *
FROM T1, T2;

Inner Joins:
SELECT
   customer.customer_id,
   first_name,
   last_name,
   email,
   amount,
   payment_date
FROM
   customer
INNER JOIN payment ON payment.customer_id = customer.customer_id;

We can also use order by with joins.

Ex: SELECT

   customer.customer_id,
   first_name,
   last_name,
   email,
   amount,
   payment_date
FROM
   customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
ORDER BY
   customer.customer_id;

SELF JOINS:
A self-join is a query in which a table is joined to itself. Self-joins are useful for
comparing values in a column of rows within the same table.

SQL triggers:
SQL statements that are AUTOMATICALLY RUN when a
specific table is changed.

EX:
CREATE TRIGGER trigger_name
trigger_time trigger_event ON table_name FOR EACH
ROW
BEGIN
...
END;

Outer Joins:

Full Outer Joins:

A full outer join gives data from n=both the table that are being
joined.
If the rows in the joined table do not match, the full outer join
sets NULL values for every column of the table that does not have
the matching row.
A full outer join with where clause can be used to select all the
rows that are unique to each table, opposite of inner join,

EX:
LEFT OUTER JOIN:
Here the order of mentioning the table is necessary.

EX:

The left outer join with where clause can be used to get all the
rows that are unique to only Table A.
EX:
SELECT * FROM
Registrations LEFT OUTER JOIN Logins
WHERE
Logins.log_id is null

Right Outer Join:

It is like Left outer join only the table order is switched.

UNIONS:
It is used to combine the result of two or more SELCT statements.

It pastes result of one query over another query.

Primary Key: A primary key is a column, or a group of columns

used to identify a row uniquely in a table.

You define primary keys through primary key constraints.

Technically, a primary key constraint is the combination of a not-
null constraint and a UNIQUE constraint.
EX:
CREATE TABLE po_headers (

po_no INTEGER PRIMARY KEY,

vendor_no INTEGER,
description TEXT,
shipping_address TEXT
);

We can add two columns as a primary key by passing the column names
to primary key constraints as shown below
PRIMARY KEY (column_1, Column_2)
We can also define primary key while altering the current table
structure.
EX:
CREATE TABLE products (
product_no INTEGER,
description TEXT,
product_cost NUMERIC
);

ALTER TABLE products

ADD PRIMARY KEY (product_no);

We can also drop the primary key constraint by using the command of
DROP CONSTRAINT.
ALTER TABLE products
DROP CONSTRAINT product_no;

Foreign Key:

Advanced SQLs:

Recursive Query:
With statement allows to construct auxiliary statements for the use
in a query.
These statements are often referred to as CTE (common table
expressions). A recursive CTE refers to itself, and it repeatedly
executes and returns subset of data, until it returns the complete
result set.
It is mostly used to query hierarchical data such as organization
charts and components where each one itself has further more
components.
WITH expression_name (column_list)
AS
(
-- Anchor member
initial_query
UNION ALL
-- Recursive member that references expression_name.
recursive_query
)
-- references expression name
SELECT *
FROM expression_name

Parts of the recursive CTE:

Anchor member: an initial query that returns the base result of the CTE.
Recursive query: a query that resembles the common table expressions. It is
unioned with anchor members using all the UNION ALL operator

A termination condition specified in the recursive member that terminates

the execution of the recursive member.