-
Create the database:
psql --command='CREATE DATABASE many_to_many;' -
Open an interactive
psqlsession with the database:psql many_to_many -
Create the tables by executing the included SQL file
many_to_many=# \include create-tables.sql -
Populate the tables with the sample data
many_to_many=# \include seed-data.sql
-
Start by selecting all the data from the
linkandtagtables separatelymany_to_many=# SELECT * FROM link; many_to_many=# SELECT * FROM tag; -
Then join the
link_tagtable to thelink_tableusing the foreign key(Queries can span multiple lines for increased readability)
many_to_many=# SELECT * many_to_many-# FROM many_to_many-# link many_to_many-# JOIN link_tag ON link_tag.link_id = link.id;Notice all the duplicate entries since most links have multiple tags
-
Now we can join the
tagtable using thetag_idcolumn from thelink_tagtablemany_to_many=# SELECT * many_to_many-# FROM many_to_many-# link many_to_many-# JOIN link_tag ON link_tag.link_id = link.id many_to_many-# JOIN tag ON tag.id = link_tag.tag_id; -
Finally we can collapse those duplicate rows using the
GROUP BYclause and thearray_aggaggregate functionmany_to_many=# SELECT many_to_many-# link.id, many_to_many-# link.name, many_to_many-# link.url, many_to_many-# array_agg(tag.name) AS tags many_to_many-# FROM many_to_many-# link many_to_many-# JOIN link_tag ON link_tag.link_id = link.id many_to_many-# JOIN tag ON tag.id = link_tag.tag_id many_to_many-# GROUP BY link.id;
-
List the tags ordered by the number of links that reference them
-
List the links ordered by the number of tags they have
-
List all links that have a specific tag
-
Remove all the data from the tables
many_to_many=# DELETE FROM link_tag; many_to_many=# DELETE FROM tag; many_to_many=# DELETE FROM link; -
Drop the tables
many_to_many=# DROP TABLE link_tag; many_to_many=# DROP TABLE tag; many_to_many=# DROP TABLE link; -
Drop the database
Quit the interactive session
many_to_many=# \quitDrop the databse
psql --command='DROP DATABASE many_to_many;'