Database - Udemy

1. Create database:

create database test;

2. Drop database:

drop database test;

3. Create table:

create table actors (
actor_id serial primary key,
first_name varchar(150),
last_name varchar(150) not null ,
gender char(1),
date_of_birth date,
add_date date,
update_date date
);

create table movies_actors(
movie_id int references movies(movie_id),
actor_id int references actors(actor_id),
primary key (movie_id, actor_id)
);

4. Rename column:

alter table accounts
rename add_date to adddate;

5. Add column:

alter table accounts
add column add_by varchar(100);

6. Delete column:

alter table accounts
drop column add_by;

7. Changing data type of a column:

alter table accounts
alter column is_enable type boolean;

8. Deleting tables from DB:

drop table roles;

9. Inserting into table:

insert into customers (first_name, last_name, email, age)
values ('Parvin', 'Etibarli', 'parvin@gmail.com', 100);

10. Inserting multiple records into table:

insert into customers (first_name, last_name)
values ('Narmin', 'Etibarli'),
('Test', 'Testov');

11. Inserting data that had quotes:

insert into customers (first_name)
values ('Bill''O Sullivan');

12. Use returning to get info on added rows:

insert into customers (first_name)
values ('Ali') returning *;

insert into customers (first_name)
values ('Ali') returning first_name;

13. Update data in a table:

update customers
set email = 'test@gmai.com'
where customer_id = 1;

update customers
set email = 'test@gmai.com',
age = 30
where customer_id = 1;

14. Updating a row and returning updated row:

update customers
set email = 'test'
where customer_id = 2
returning *;

15. Update all records in a table:

update customers
set is_enable = 'Y';

16. Delete data from a table:

delete from customers
where customer_id = 3;

17. Using upsert: (The combination of update or insert)

insert into t_tags (tag)
values ('Pen')
on conflict (tag)
do update set tag = excluded.tag || '1',
update_date = now();

18. || for concatenating:

select a.first_name || a.last_name from actors a;

select a.first_name || ' ' || a.last_name as full_name from actors a;

19. Order by: for sorting data

select * from movies m
order by m.release_date desc;

select a.first_name, length(a.first_name) len from actors a
order by len;

20. Distinct: selecting for unique values

select distinct m.movie_lang from movies m
order by m.movie_lang;

21. Limit: for retrieve only a few records

select * from movies m
order by m.movie_length desc
limit 5;

select * from directors d
order by d.date_of_birth
limit 5;

select * from actors a
where a.gender = 'F'
order by a.date_of_birth desc
limit 10;

select *
from movies_revenues mr
order by mr.revenues_domestic desc nulls last
limit 10;

22. Offset: is used to skip the number of records from the results

select * from movies m
order by m.movie_id
limit 5 offset 4;

select * from movies m
left join movies_revenues mr on m.movie_id = mr.movie_id
order by mr.revenues_domestic desc nulls last
limit 5 offset 4;

23. Fetch: allows a user to retrieve rows using a cursor

select * from movies
fetch first 2 row only;

select *
from movies m
order by m.movie_length desc
fetch first 5 row only;

select * from directors d
order by d.date_of_birth
fetch first 5 row only ;

24. In: replaces multiple or operator

select * from movies m
where m.movie_lang in ('English', 'Japanese');

25. Not in:

select * from movies m
where m.movie_lang not in ('English', 'Japanese');

26. Between:

select * from actors a
where a.date_of_birth between '1990-12-31' and '1995-12-31';

SELECT *
FROM actors a
WHERE EXTRACT(YEAR FROM a.date_of_birth) BETWEEN 1990 AND 1995;

27. Not between:

select * from movies m
where m.movie_length not between 100 and 300
order by m.movie_length;

28. Like: returns boolean and case sensitive

% Percent sign (%) matches any sequence of zero or more characters

_ Underscore sign (_) matches any single character

select * from actors a
where a.first_name like 'A%';

select * from actors a
where a.last_name like '%a';

select * from actors a
where a.first_name like '_____';

select * from actors a
where length(a.first_name) = 5;

select * from actors a
where a.first_name like '_l%'
order by a.first_name;

29. Ilike: returns boolean value and case insensitive

select * from actors a
where a.first_name ilike '%tim%';

30. Is null:

select * from actors a
where a.date_of_birth is not null;

31. Is not null:

select * from actors a
where a.date_of_birth is not null
and a.first_name is not null;

32. || : for concatenation

select a.first_name || ' ' || a.last_name as full_name from actors a;

33. Concat:

select concat(a.first_name, a.last_name) as "full name" from actors a;

34. Concat_ws: concatenating with specific separator

select concat_ws(',', a.first_name, a.last_name, a.date_of_birth)
as "full name" from actors a;

35. Count: returns the number of rows that matches a specified criterion

select count(*) from movies;

Using count with distinct:

select count(distinct m.movie_lang) from movies m;

select count(distinct d) from directors d;

36. Sum: 

select sum(mr.revenues_domestic) as sum_of_revenues_domestic,
sum(mr.revenues_international) as sum_of_revenues_international,
sum(mr.revenues_international + mr.revenues_domestic) from movies_revenues mr;

select sum(mr.revenues_domestic) as sum_of_revenues_domestic,
sum(mr.revenues_international) as sum_of_revenues_international,
sum(mr.revenues_international + mr.revenues_domestic) as total_sum,
sum(mr.revenues_international) + sum(mr.revenues_domestic) as total_sum2 from movies_revenues mr;

select sum(m.movie_length) from movies m
where m.movie_lang = 'English';

select sum(length(m.movie_name)) from movies m;

Using distinct with sum:

select sum(distinct mr.revenues_domestic) from movies_revenues mr;

37. Max:

select max(m.movie_length) from movies m;

select m.* as name from movies m
where m.movie_length = (select max(m.movie_length) from movies m);

select m.* as name
from movies m
where m.movie_length = (select max(m.movie_length)
from movies m
where m.movie_lang = 'Spanish');

38. Min:

select m.*
from movies m
where m.release_date = (select min(m.release_date)
from movies m
where m.movie_lang = 'Chinese');

select max(m.movie_name), min(m.movie_name)
from movies m;

39. Greatest, Least: these functions can compare multiple values at once, making it easy to find the maximum and minimum value among a group of values. 

select mr.*, greatest(mr.revenues_domestic, mr.revenues_international) as greatest,
least(mr.revenues_domestic, mr.revenues_international) as least from movies_revenues mr;

40. Avg:

select avg(m.movie_length) from movies m;

41. Group by: is used to group together those rows in a table that have identical data

select m.movie_lang from movies m
group by m.movie_lang;

it is similar to:

select distinct (m.movie_lang) from movies m;

Although there are some similarities between group by and distinct, there are also difference between them:

select m.movie_lang, count(m.movie_lang) from movies m
group by m.movie_lang;

select m.movie_lang, count(distinct(m.movie_lang)) from movies m
group by m.movie_lang;

select m.movie_lang, avg(m.movie_length) from
movies m group by m.movie_lang;

select m.movie_lang, max(m.movie_length),
min(m.movie_length) from movies m
group by m.movie_lang;

select m.age_certificate, avg(m.movie_length)
from movies m
where m.age_certificate = 'PG'
group by m.age_certificate;

select d.nationality, count(d.nationality) from directors d
group by d.nationality;

select m.age_certificate, m.movie_lang, sum(m.movie_length)
from movies m group by m.age_certificate, m.movie_lang
order by m.age_certificate;

! Aggregate functions are not allowed in group by.

42. Having: is used to specify search condition for a group or aggregate function

select m.movie_lang, sum(m.movie_length) from movies m
group by m.movie_lang
having sum(m.movie_length) > 200
order by 2;

select * from directors d
where d.director_id = (select m.director_id from movies m
group by m.director_id
order by sum(m.movie_length) desc
limit 1);

! Having works on result group.

! Where works on select columns and not on result group

43. Coalesce: return the first non-null value it encounters when reading from left to right

select coalesce(mr.revenues_domestic, 0) domestic,
coalesce(mr.revenues_international, 0) international
from movies_revenues mr;

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

                                                          Joining Multiple Tables

44. Inner join: Inner join = join

select m.movie_name, d.first_name, d.last_name from movies m
inner join directors d on m.director_id = d.director_id;

Using inner join with USING:

select * from movies m
inner join directors d using (director_id);

select m.movie_name,
m.movie_lang,
d.first_name || ' ' || d.last_name as full_name,
mr.revenues_international, mr.revenues_domestic,
(mr.revenues_domestic + mr.revenues_international) as totoal
from movies m
inner join directors d using (director_id)
inner join movies_revenues mr using (movie_id)
order by totoal desc nulls last limit 5;

select m.movie_name,
m.movie_lang,
m.release_date,
d.first_name || ' ' || d.last_name,
mr.revenues_international,
mr.revenues_domestic,
(mr.revenues_international + mr.revenues_domestic) as totoal
from movies m
inner join movies_revenues mr using (movie_id)
inner join directors d using (director_id)
where extract(year from m.release_date) BETWEEN 2005 AND 2008;

45. Left Joins:

Returns every row from the Left table plus rows that match values in the joined column from the right table. 

Lets create some sample tables for our join exercises.

create table left_products (
product_id serial primary key,
product_name varchar(100)
);

create table right_products (
product_id serial primary key,
product_name varchar(100)
);

insert into left_products(product_id, product_name)
values
(1, 'Computers'),
(2, 'Laptops'),
(3, 'Monitors'),
(5, 'Mics');

insert into right_products(product_id, product_name)
values
(1, 'Computers'),
(2, 'Laptops'),
(3, 'Monitors'),
(4, 'Pen'),
(7, 'Papers');

select * from left_products
left join right_products rp on left_products.product_id = rp.product_id;

select d.first_name, d.last_name, m.movie_name from directors d
left join movies m using (director_id);

-- Lets add a record in our directors table

insert into directors (first_name, last_name, date_of_birth, nationality)
values ('James', 'David', '2010-01-01', 'American');

Now if we run above query again we will get 54 records.

 -- Lets reverse the tables directors and movies. What is the impact on the result?

select d.first_name, d.last_name, m.movie_name from movies m
left join directors d using (director_id);

select d.first_name, d.last_name, m.movie_name from directors d
left join movies m using (director_id)
where m.movie_lang in ('English', 'Chinese');

-- Count all movies for each directors

select d.*, count(m) from directors d
left join movies m using (director_id)
group by d.director_id;

select * from directors d
left join movies m on d.director_id = m.director_id
where d.nationality in ('American', 'Chinese', 'Japanese');

-- Get all the total revenues done by each films for each directors

select d.director_id,
d.first_name,
d.last_name,
d.nationality,
sum(mr.revenues_domestic + mr.revenues_international) as "total revenue"
from directors d
left join movies m on d.director_id = m.director_id
left join movies_revenues mr on m.movie_id = mr.movie_id
group by d.director_id;

select d.director_id,
d.first_name,
d.last_name,
d.nationality,
sum(mr.revenues_domestic + mr.revenues_international) as "total revenue"
from directors d
left join movies m on d.director_id = m.director_id
left join movies_revenues mr on m.movie_id = mr.movie_id
group by d.director_id
having sum(mr.revenues_domestic + mr.revenues_international) is not null;

-- Get total revenue according each director nationality.

select d.nationality,
sum(mr.revenues_domestic + mr.revenues_international) as "total revenue"
from directors d
left join movies m on d.director_id = m.director_id
left join movies_revenues mr on m.movie_id = mr.movie_id
group by d.nationality;

46. Right join. 

select * from left_products lp
right join right_products rp on lp.product_id = rp.product_id;

select * from directors d
right join movies m on m.director_id = d.director_id;

47. Full join. Returns every row from all the join table

select * from left_products lp
full join right_products rp on lp.product_id = rp.product_id;

48. Self join. Allows to compare rows within the same table. 

select m1.movie_name,m2.movie_name, m2.movie_length from movies m1
inner join movies m2 on m1.movie_length = m2.movie_length
where m1.movie_name <> m2.movie_name;

49. Cross join. Each row in the left with each row in the right table to present all possible combinations of

rows.

 select * from left_products lp

cross join right_products rp;

-- Equivalent for cross join:

select * from left_products, right_products;

50. Natural join. Natural join creates an implicit join based on the same column names in the joined tables.

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

                                                            Combining queries together

51. Union: combines result sets from two or more select statements into a single result set.

select * from left_products
union
select * from right_products;

Union does not duplicate records.

What if we need duplicate records too?

select * from left_products
union all
select * from right_products;

select first_name, last_name, 'actors' as "tablename"
from actors
union
select first_name, nationality, 'directors' as "tablename"
from directors
order by first_name desc;

! Data types in union must match.

select d.first_name, d.last_name, 'directors' as "tablename"
from directors d
where d.nationality in ('American', 'Chinese', 'Japanese')
union
select a.first_name, a.last_name, 'actors' as "tablename"
from actors a
where a.gender = 'F';

select d.first_name, d.last_name, 'directors' as "tablename"
from directors d
where d.date_of_birth >= '1990-12-31'
union
select a.first_name, a.last_name, 'actors' as "tablename"
from actors a
where a.date_of_birth >= '1990-12-31';

52. Intersect: returns any rows that are available in both result sets. 

The order and number of the columns in the select list of all queries must be the same. 

The data types must be compatible too. 

We do not get duplicate records when we use intersect.

select first_name, last_name from actors
intersect
select first_name, last_name from directors;

53. Except: returns the rows in the first query that do not appear in the output of the second query. 

The order and number of the columns in the select list of all queries must be the same. 

The data types must be compatible too. 

-- List all the directors first name, last name unless they have the same fist_name in female actors.

select first_name, last_name from directors
except
select first_name, last_name from actors
where gender = 'F';

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

                                                                    Subqueries

-- Find the movies which movie length are more than average movie length:

select * from movies m
where m.movie_length > (select avg(m.movie_length) from movies m);

-- Get first and last name of all actors who are younger than Doubles Silva:

select a.first_name, a.last_name, a.date_of_birth
from actors a
where a.date_of_birth > (select a.date_of_birth
from actors a
where a.first_name || a.last_name = 'DouglasSilva');

54. Subquery with IN operator:

-- Find all movies where domestic revenues are greater than 200:

without in:

select m.* from movies m
inner join movies_revenues mr on m.movie_id = mr.movie_id
where mr.revenues_domestic > 200;

with in:

select movie_name, movie_lang
from movies
where movie_id in
(select movie_id from movies_revenues
where revenues_domestic > 200);

-- Find all movies where domestic revenues are higher than the international revenues:

without in:

select m.*
from movies m
inner join movies_revenues mr on m.movie_id = mr.movie_id
where mr.revenues_domestic > mr.revenues_international;

with in:

select m.*
from movies m
where m.movie_id in
(select mr.revenue_id
from movies_revenues mr
where mr.revenues_domestic > mr.revenues_international);

55. Subquery with JOINs

-- List all the directors where their movies made more than the average total revenues of all english movies:

select *
from directors d
inner join movies m on d.director_id = m.director_id
inner join movies_revenues mr on m.movie_id = mr.movie_id
where (mr.revenues_domestic + mr.revenues_international) >
(select avg(mr.revenues_domestic + mr.revenues_international)
from movies m
inner join movies_revenues mr on m.movie_id = mr.movie_id
where m.movie_lang = 'English');

-- Order entires in a union without order by:

select *
from (select first_name, 0 myorder, 'actors' as "tablename"
from actors
union
select first_name, 1, 'directors' as "tablename"
from directors) t
order by myorder;

56. Correlated Subqueries:

- A correlated subquery is a subquery that contains a reference to a table (in the parent query) that also appears in the outer query. 

- PostgreSQL evaluates from inside to outside

-- List movie name, movie language and movie age certification for all movies with an above minimum length of for age certification

select m.movie_name,
m.movie_lang,
m.movie_length,
m.age_certificate
from movies m
where m.movie_length >
(select min(movie_length)
from movies m2
where m.age_certificate = m2.age_certificate);

-- List first name, last name and date of birth for the oldest actors for each gender:

select first_name, last_name, date_of_birth, gender
from actors a
where a.date_of_birth =
(select min(date_of_birth)
from actors a1
where a.gender = a1.gender);

57. In with subquery:

-- Find customers that are same 

select *
from customers c
where c.country in (select s.country from suppliers s);

58. Using ANY with subquery:

-- Find customer details where they ordered more than 20 items in a single product

select *
from customers c
where c.customer_id = any
(select o.customer_id
from orders o
inner join order_details od on o.order_id = od.order_id
where od.quantity > 20);

59. Using ALL with subquery:

-- Find all products where their order amount were lower than the average amount of all the products