Introduction to SQL

-- Create the following two tables for this demo:
CREATE TABLE country ( 
	id INTEGER PRIMARY KEY, 
	country_name VARCHAR(100) 
);

CREATE TABLE person ( 
	id INTEGER PRIMARY KEY, 
	person_name VARCHAR(100), 
	income NUMERIC(7,2), 
	country_id integer,
	CONSTRAINT country_id_fk
		FOREIGN KEY(country_id) 
		REFERENCES country(id)
);

-- Insert data into the 'country' table:
INSERT INTO country (id, country_name) VALUES (1, 'Canada'), (2, 'USA'), (3, 'Mexico'), (4, 'Sweden');

-- Insert data into the 'person' table:
INSERT INTO person (id, person_name, income, country_id) VALUES (1, 'Sally', 60000, 1);
INSERT INTO person (id, person_name, income, country_id) VALUES (2, 'Bob', 70000, 1);
INSERT INTO person (id, person_name, income, country_id) VALUES (3, 'Lucy', 80000, 2);
INSERT INTO person (id, person_name, income, country_id) VALUES (4, 'Bill', 75000, NULL);

-- select all columns from person table

select 
	* 
from 
	person;

-- we can also specify the columns we want to query by replacing the asterisk with the column name

select 
	id, 
	person_name, 
	income 
from 
	person;

-- if we want to return the data and have it ordered according to a specific column(s), we can use the order by clause

select * from person
order by income asc;

select * from person
order by income desc;

-- filter data from person table by name
select * from person where person_name = 'Lucy';

-- sometimes we don't know the exact spelling or word.  In that case we can use a wildcard
select * from person where person_name like '%Lu%';

-- get the unique countries that a person lives in

select distinct country_id from person;

select 
	* 
from 
	person 
inner join country 
	on person.country_id = country.id;

select 
	* 
from 
	person 
left join 
	country 
	on person.country_id = country.id;

--now do a right join
select * 
from 
	person 
right join 
	country 
	on person.country_id = country.id;

select * 
from 
	person 
full outer join 
	country 
	on person.country_id = country.id;

select 
	country_name, 
	count(*) 
from 
	person
inner join 
	country 
	on person.country_id = country.id
group by 
	country_name;

select 
	country_name, 
	count(*) 
from 
	person
inner join 
	country 
		on person.country_id = country.id
group by 
	country_name
having 
	count(*) > 1;

select 
	country_name, 
	avg(income) 
from 
	person
inner join 
	country 
		on person.country_id = country.id
group by 
	country_name;

select 
	country_name, 
	'max' as agg_type, 
	max(income) 
from 
	person
inner join 
	country 
	on person.country_id = country.id
group by 
	country_name;

select 
	country_name, 
	'min' as agg_type, 
	min(income) 
from 
	person
inner join 
	country 
		on person.country_id = country.id
group by 
	country_name;

select 
	country_name, 
	min(income), 
	max(income), 
	avg(income) 
from 
	person
inner join 
	country 
		on person.country_id = country.id
group by 
	country_name;

select country_name, 'avg' as agg_type, avg(income) from person
inner join country on person.country_id = country.id
group by country_name

UNION

select country_name, 'max' as agg_type, max(income) from person
inner join country on person.country_id = country.id
group by country_name

UNION
select country_name, 'min' as agg_type, min(income) from person
inner join country on person.country_id = country.id
group by country_name;

select * from person
full outer join (
	select * from country
	where id = 2) country_table
on person.country_id = country_table.id;

select * from person
full outer join country
on person.country_id = country.id
where country.id = 2;

SELECT person_name, CASE 
WHEN income BETWEEN 50000 and 61000 THEN 1 
WHEN income BETWEEN 62000 and 71000 THEN 2 
WHEN income BETWEEN 72000 and 81000 THEN 3 END as income_category
FROM person;

select ('100') + ('100'); -- query fails

SELECT ('100'::INTEGER) + ('200'::Integer); -- result is successfully returned as 300
SELECT ('10.1'::DECIMAL) + ('12.2':: DECIMAL)' -- result is successfully returned as 22.3
SELECT ('-10.1'::DECIMAL) + ('12.2':: DECIMAL)' -- result is successfully returned as 2.1

select ('jan 01 2021') > ('feb 01 2021'); -- incorrectly results in true because 'j' is larger than 'f' alphabetically.

select ('jan 01 2021'::DATE) > ('feb 01 2021'::DATE); -- correctly returns false
select ('jan 01 2021 08:01:45'::TIMESTAMP) > ('jan 01 2021 08:01:46'::TIMESTAMP); -- correctly returns false
select to_date('05$DEC$2000', 'DD$MON$YYYY') -- returns 2000-12-05T00:00:00.000Z

create table test_data_conv (income_1 varchar(100), income_2 varchar(100), date_1 varchar(100), date_2 varchar(100));

INSERT INTO test_data_conv 
(income_1, income_2, date_1, date_2) 
values 
('10.1', '12.2', 'jan 01 2021', 'feb 01 2021'),
('13.1', '14.2', 'mar 01 2021', 'aug 01 2021');

SELECT sum(income_1::DECIMAL) from test_data_conv; --23.2

select * from test_data_conv where (date_1::DATE) < (date_2::DATE); -- correctly returns both rows.

ALTER TABLE person ADD column bday text;

UPDATE person
SET bday = '2000-Jan-01'
WHERE person_name = 'Lucy';

UPDATE person
SET bday = '2000-Feb-01'
WHERE person_name = 'Bob';

-- compare the results for the following queries:
select * from person
order by bday::date;

select * from person
order by bday;

-- Create the following two tables for this demo:
CREATE TABLE person ( id INTEGER PRIMARY KEY, person_name VARCHAR(100), income NUMERIC(7,2) );
CREATE TABLE country ( id INTEGER PRIMARY KEY, country_name VARCHAR(100) );

-- Insert data into the 'person' table:
INSERT INTO person (id, person_name, income) VALUES (1, 'Sally', 60000);
INSERT INTO person (id, person_name, income) VALUES (2, 'Bob', 70000);
INSERT INTO person (id, person_name, income) VALUES (3, 'Lucy', 80000);

INSERT INTO country (id, country_name) VALUES (1, 'Canada'), (2, 'USA'), (3, 'Mexico');

ALTER TABLE person ADD column country_id integer;

UPDATE person
SET country_id = 2
WHERE person_name = 'Lucy';

UPDATE person
SET country_id = 1
WHERE person_name in ('Sally', 'Bob');

-- delete row from country table where the ID of the country = 3

delete from country where id = 3;

drop table person;
drop table country;