# Postgresql notes: the query basics


## standard vs non-standard way to create auto-incrementing int values in create table

non-standard: using `serial`:  
`id int primary key serial -- in create table`

standard: using `generated as identity`, which has 2 options: `generated always` and `generated by default`. use latter:  
`id int primary key generated by default as identity`

---

## single vs double quotes
- double is used for table/column names
- double is optional if the table/column names are all lowercase and with no space
- single is used for quoting the data. used in insert and select's where clause

---

## delete all data from one or more tables

`truncate tname1, tname1;`

---

## primary key
primary key on a col ensures it's value is uniq and non-null

in create table, making a single col as primary key:  
`id int primary key`

making multiple cols as composite primary key:  
`primary key (student_id, course_id)  -- in a separate line`

can also add it as a constraint with a name to it:  
`constraint pk_tablename primary key (colname) -- in separate line`

---

## foreign key

foreign key on a col ensures that col to have value that's only already there in another table

in create table:  
`constraint fk_customer foreign key (customer_id) references customers(id) -- separate line`

or inline along with col in create table:  
`customer_id int references customer(id)`

`references` is lazy way, can't name fk. sivers uses this.

adding foreign key in alter table:  

`alter table tname add constraint fk_tname_reftname_colname foreign key (colname) references ref_tname(ref_col);`

---

## create table

create table general syntax:  
`create table if not exists table_name (...);`

some examples:

```sql
create table galaxy(
  galaxy_id serial primary key,
  name varchar(30) not null unique,
  is_milky boolean not null default false,
  age int,
  distance numeric
);

create table star(
  star_id serial primary key,
  name varchar(30) not null unique,
  galaxy_id int references galaxy(galaxy_id),
  is_dying boolean not null default false,
  age int
);
```

---

## batch insert rows

```sql
insert into table_name (col1, col2) values
(row1v1, row1v2),
(row2v1, row2v2);
```

---

## update a single row based on some condition

```sql
update table_name
set
  col1 = 'new_value'
where
  col2 = 'blah';
```

---

## data types

numeric and decimal are same.

so if u need a decimal datatype, use `numeric(6, 2)`. this would allow 4 digit nums before decimal and upto 2 digits after decimal.  
eg: 3423.58  
it's full range: -9999.99 to 9999.99

for whole numbers, use int, bigint or smallint.

---

## adding a unique constraint to a column

`alter table tname add constraint constraint_name unique(colname);`

adding a primary key and a unique constraint on a col creates a btree index.
adding a foreign key does not.

---

## relationship among tables

- one to many: implemented by adding a foreign key on the second table
- one to one: ditto, but additionally, a unique constraint on that foreign key to prevent another row from having same value 
- many to many: have to have a third table to represent a many to many asso between table 1 and table 2. this 3rd table is a join table. it'll have 2 foreign keys, each pointing to respective tables.

---

## full join - shows all rows from both table

```sql
select * from t1 full join t2 on t1.id = t2.blah_id;
```

---

## running one-off sql queries (select) from bash script

```sh
psql="psql -X -U username -d dbname --no-align --tuples-only -c"
qry="select * from tname;"
result=$($psql $qry)
# $result is a mulitline string of data from the table, each rows separated by '|'
```

But I wrote [detailed notes](/bash_sql) on this here.

---

## aggregate functions

- avg, min, max, sum
- floor(x), ceil(x), round(x), round(x, num of decimal digits)

some agg queries:

```sh
echo -e "\nTotal number of goals in all games from winning teams:"
echo "$($PSQL "SELECT SUM(winner_goals) FROM games;")"

echo -e "\nTotal number of goals in all games from both teams combined:"
echo "$($PSQL "SELECT SUM(winner_goals + opponent_goals) FROM games;")"

echo -e "\nAverage number of goals in all games from the winning teams:"
echo "$($PSQL "SELECT AVG(winner_goals) FROM games;")"

echo -e "\nAverage number of goals in all games from the winning teams rounded to two decimal places:"
echo "$($PSQL "SELECT ROUND(AVG(winner_goals), 2) FROM games;")"

echo -e "\nAverage number of goals in all games from both teams:"
echo "$($PSQL "SELECT AVG(winner_goals + opponent_goals) FROM games;")"

echo -e "\nMost goals scored in a single game by one team:"
echo "$($PSQL "SELECT greatest(MAX(winner_goals), MAX(opponent_goals)) FROM games;")"

echo -e "\nNumber of games where the winning team scored more than two goals:"
echo "$($PSQL "select count(*) from games where winner_goals > 2;")"

echo -e "\nWinner of the 2018 tournament team name:"
echo "$($PSQL "select name from teams inner join games on games.winner_id = teams.team_id where year = 2018 and round = 'Final';")"

echo -e "\nList of teams who played in the 2014 'Eighth-Final' round:"
echo "$($PSQL "select distinct name from teams inner join games on team_id in (winner_id, opponent_id) where year = 2014 and round = 'Eighth-Final' order by name;")"

echo -e "\nList of unique winning team names in the whole data set:"
echo "$($PSQL "select distinct name from teams inner join games on team_id = winner_id order by name;")"

echo -e "\nYear and team name of all the champions:"
echo "$($PSQL "select year, name from teams inner join games on team_id = winner_id where round = 'Final' order by year;")"

echo -e "\nList of teams that start with 'Co':"
echo "$($PSQL "select name from teams where name ilike 'Co%' order by name;")"
```

---

## group by, having

find row count based on specific col's values:

`select colname, count(*) from tname group by colname;`

---