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:

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

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

update a single row based on some condition

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

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

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

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 on this here.

aggregate functions

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

some agg queries:

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;