select ...
from table1
  join lateral (
	select ...
	from table2
	where table1.colonne1 = table2.colonne2)
  on true
where ...

select
  mem.firstname,
  mem.surname,
  memref.firstname,
  memref.surname
 
from cd.members as mem
  /* La jointure latérale permet de récupérer le parrain pour chaque membre
     du club*/
  left join lateral
	(
  	  select
    	    firstname,
    	    surname
  	  from cd.members as mentors
  	  where
    	    mentors.memid = mem.recommendedby
	) as memref
  on true

#! /usr/bin/env python3
 
import sys
import psycopg2
 
CONNSTRING = "service=pgexercises"
 
def get_member_names(id):
 #Fetch the recommender's name and last name from his id
  sql = """
	select
  	  firstname,
  	  surname
	from cd.members
	where memid = %s
  """
  pgconn = psycopg2.connect(CONNSTRING)
  cursor = pgconn.cursor()
  cursor.execute(sql, (id,))
 
  # Id is a primary key -> only one result expected
  (firstname, surname) = cursor.fetchone()
 
  result = '{0}, {1}'.format(firstname, surname)
 
  return result

def list_members_and_recommenders():
  "Fetch the list of members"
 
  sql = """
	select
  	  mem.firstname,
  	  mem.surname,
  	  mem.recommendedby
	from cd.members as mem
  """
 
  pgconn = psycopg2.connect(CONNSTRING)
  cursor = pgconn.cursor()
  cursor.execute(sql)
 
  for (firstname, surname, recommendedby) in cursor.fetchall():
 
	if not recommendedby:
  	  print('{0}, {1}, , '.format(firstname, surname))
	else:
  	  mentor = get_member_names(recommendedby)
  	  print('{0}, {1}, {2}'.format(firstname, surname, mentor))
 
if __name__ == '__main__':
  list_members_and_recommenders()

select
  mem.firstname,
  mem.surname
 
from cd.members as mem

select
  mem.firstname,
  mem2.surname
 
from cd.members as mem
  join lateral
	(
  	select surname
  	from cd.members as memBis
  	where mem.memid = memBis.memid
	) as mem2
  on true

(
  select
	mem.firstname,
	mem.surname
 
  from cd.members as mem
)
except
(
  select
	mem.firstname,
	mem2.surname
 
  from cd.members as mem
	join lateral
  	(
    	select surname
    	from cd.members as memBis
    	where mem.memid = memBis.memid
  	) as mem2
	on true
)

select
  firstname,
  surname,
  (
	select count(*)
	from cd.members
  )
 
from cd.members

select
  firstname,
  surname,
  count(*) over()
 
from cd.members

#! /usr/bin/env python3
 
import sys
import psycopg2
 
CONNSTRING = "service=pgexercises"
 
def list_members_and_count():
  "Fetch the list of members and the total number of members"
 
  sql = """
	select
  	mem.firstname,
  	mem.surname
	from cd.members as mem
  """
  pgconn = psycopg2.connect(CONNSTRING
  cursor = pgconn.cursor()
  cursor.execute(sql)
 
  members = cursor.fetchall()
  count = len(members)
 
  for (firstname, surname) in members:
 
  	print('{0}, {1}, {2}'.format(firstname, surname, count))
 
if __name__ == '__main__':
  list_members_and_count()

select
  firstname,
  surname,
  /* Sum the number of half hours the equipment was booked by that member*/
  sum(bks.slots)*.5 as "hours",
  /* get the rank */
  rank() over (order by sum(bks.slots) desc) as rank
 
from cd.bookings bks
  inner join cd.members mems
  on bks.memid = mems.memid
 
group by mems.memid
order by rank, surname, firstname;

#! /usr/bin/env python3
 
import sys
import psycopg2
 
CONNSTRING = "service=pgexercises"
 
def list_members_hours_and_rank():
  "Fetch the list of members, the total number of hours they booked an equipment and their rank"
 
  sql = """
	select
  	firstname,
  	surname,
  	sum(bks.slots)*.5 as "hours"
	
	from cd.bookings bks
  	inner join cd.members mems
  	on bks.memid = mems.memid
	group by mems.memid
	order by hours desc, surname, firstname;
  """
 
  pgconn = psycopg2.connect(CONNSTRING)
  cursor = pgconn.cursor()
  cursor.execute(sql)
 
  members = cursor.fetchall()
  i = 1
 
  for (firstname, surname, hours) in members:
 
  	print('{0}, {1}, {2}, {3}'.format(firstname, surname, hours, i))
  	i += 1
 
if __name__ == '__main__':
  list_members_hours_and_rank()

generate_series.

select
  count(*)
from generate_series(1, 10000000) ;

select distinct
  count(*) over()
from generate_series(1, 10000000) ;

insert into
  cd.members
  (
    select
      memid+100,
      surname,
      firstname,
      address,
      zipcode,
      telephone,
      recommendedby,
      joindate
    from cd.members
    where firstname in ('David', 'Darren', 'Tim')
  );

select
  members.memid,
  members.surname,
  members.firstname,
  members.address,
  members.zipcode,
  members.telephone,
  members.recommendedby

from
  cd.members as members
inner join
  /* chaque membre avec un count(*) > 1 est un doublon /*
  (
    select
      surname,
      firstname,
      joindate
    from cd.members
    group by
      surname,
      firstname,
      joindate
    having count(*) > 1
  ) as duplicates
on members.surname = duplicates.surname
  and members.firstname = duplicates.firstname
  and members.joindate = duplicates.joindate;

order by
  members.surname,
  members.firstname,
  members.memid

with duplicates as
  /* chaque membre avec un count(*) > 1 est un doublon /*
  (
    select
      surname,
      firstname,
      joindate
    from cd.members
    group by
      surname,
      firstname,
      joindate
    having count(*) > 1
  )

select
  members.memid,
  members.surname,
  members.firstname,
  members.address,
  members.zipcode,
  members.telephone,
  members.recommendedby

from
  cd.members as members
inner join
  duplicates
on members.surname = duplicates.surname
  and members.firstname = duplicates.firstname
  and members.joindate = duplicates.joindate

order by
  members.surname,
  members.firstname,
  members.memid

with recursive [ctename] as
  (
    [first sql query]
    union all
    [recursive sql query using the first sql query]
  )
[any query using the cte]

\set myid 27

with recursive recommenders(recommender) as
  (
    /* Get the first member’s mentor */
    select recommendedby
    from cd.members
    where memid = :myid

    union all

    /* Get all the others mentors */
    select mems.recommendedby
    from recommenders recs
    inner join cd.members mems
      on mems.memid = recs.recommender
  )

/* Display the result */
select
  recs.recommender,
  mems.firstname,
  mems.surname

from recommenders recs
inner join cd.members mems
  on recs.recommender = mems.memid

order by memid desc;

#! /usr/bin/env python3

import sys
import psycopg2

CONNSTRING = "service=pgexercises"

def get_member_names_and_recommender(id):
 #Fetch the recommender's name and last name from his id
  sql = """
    select
      firstname,
      surname,
      recommendedby
    from cd.members
    where memid = %s
  """
  pgconn = psycopg2.connect(CONNSTRING)
  cursor = pgconn.cursor()
  cursor.execute(sql, (id,))

  # Id is a primary key -> only one result expected
  (firstname, surname, recommendedby) = cursor.fetchone()

  print('{0}, {1}, {2}'.format(recommendedby, firstname, surname))

  if recommendedby:
    # If recommendedby is not null, we haven't reached the end of
    # the recommendation chain
    get_member_names_and_recommender(recommendedby)

  return result

if __name__ == '__main__':
  get_member_names_and_recommender(27)