Learn Databases

A database is an organized collection of data, generally stored and accessed electronically from a computer system.

  1. Create a database
CREATE DATABASE learn_to_code;
\c learn_to_code
  1. Create a table
CREATE TABLE users(
  id serial PRIMARY KEY,
  username VARCHAR (50) NOT NULL
);
  1. Insert data into tables
INSERT INTO users (username) VALUES('alex')"
  1. Get information from the database
SELECT * FROM users WHERE id=1;

Exercise

Let's create the reality of the previous exercises on a relational database. Our reality has users that want to choose favourite items. Items can be marked as bought and can be shared with other users. Users can follow other users to check the state of their favourites items. Users can also add comments to other users' favourite items.

First, we need to create an ERD diagram to represent this reality. This diagram will allow us to have a clear idea of the database structure. Then we will create this database on Postgres.

Finally, please create these four SQL queries:

  1. Fetch all the favourite items for a given user
  2. Fetch all users together with their favourite items, even if the user doesn't have any items
  3. Get the number of users without favourite items
  4. Fetch the name of the users that have more than five followers and some non bought favourite items