Interacting With the Database

docker compose start

[+] Running 2/2
 ⠿ Container section-9-adminer-1  Started                                                                                                                 0.4s
 ⠿ Container section-9-db-1       Started   

docker compose exec -it db psql -U baloo -d lenslocked
psql (15.2 (Debian 15.2-1.pgdg110+1))
Type "help" for help.

lenslocked=#
lenslocked=#

lenslocked=# DROP TABLE IF EXISTS users;
DROP TABLE
lenslocked=#

lenslocked'#
lenslocked'# -- Creating the "users" table
lenslocked'#

lenslocked=# CREATE TABLE users (
lenslocked(# id SERIAL PRIMARY KEY,
lenslocked(# age INT,
lenslocked(# first_name TEXT,
lenslocked(# last_name TEXT,
lenslocked(# email TEXT UNIQUE NOT NULL
lenslocked(# );
CREATE TABLE
lenslocked=#

lenslocked=# SELECT * FROM users;
 id | age | first_name | last_name | email
----+-----+------------+-----------+-------
(0 rows)

lenslocked=#

lenslocked=# INSERT INTO users VALUES (1, 22, 'Alex', 'Rabocse', 'alex@email.com');
INSERT 0 1
lenslocked=#
lenslocked=# SELECT * FROM users;
 id | age | first_name | last_name |     email
----+-----+------------+-----------+----------------
  1 |  22 | Alex       | Rabocse   | alex@email.com
(1 row)

lenslocked=#

lenslocked=# INSERT INTO users (age, email, first_name, last_name)
VALUES (33, 'dario2@email.com', 'dario', 'Bastian');
INSERT 0 1
lenslocked=#
lenslocked=#
lenslocked=# SELECT * FROM users;
 id | age | first_name | last_name |      email
----+-----+------------+-----------+------------------
  1 |  22 | Alex       | Rabocse   | alex@email.com
  2 |  33 | dario      | Bastian   | dario2@email.com
(2 rows)

lenslocked=# SELECT id, email FROM users;
 id |      email
----+------------------
  1 | alex@email.com
  2 | dario2@email.com
(2 rows)

lenslocked=#
lenslocked=# SELECT first_name, last_name FROM users;
 first_name | last_name
------------+-----------
 Alex       | Rabocse
 dario      | Bastian
(2 rows)

lenslocked=#
lenslocked=#
lenslocked=#
lenslocked=# SELECT id, email FROM users;
 id |      email
----+------------------
  1 | alex@email.com
  2 | dario2@email.com
(2 rows)

lenslocked=# SELECT * FROM users WHERE age=22;
 id | age | first_name | last_name |     email
----+-----+------------+-----------+----------------
  1 |  22 | Alex       | Rabocse   | alex@email.com
(1 row)

lenslocked=#
lenslocked=# SELECT * FROM users WHERE age=33;
 id | age | first_name | last_name |      email
----+-----+------------+-----------+------------------
  2 |  33 | dario      | Bastian   | dario2@email.com
(1 row)

lenslocked=#
lenslocked=# SELECT * FROM users WHERE age=34;
 id | age | first_name | last_name | email
----+-----+------------+-----------+-------
(0 rows)

lenslocked=#
lenslocked=# SELECT * FROM users WHERE first_name=Alex;
ERROR:  column "alex" does not exist
LINE 1: SELECT * FROM users WHERE first_name=Alex;
                                             ^
HINT:  Perhaps you meant to reference the column "users.age".
lenslocked=# SELECT * FROM users WHERE first_name='Alex';
 id | age | first_name | last_name |     email
----+-----+------------+-----------+----------------
  1 |  22 | Alex       | Rabocse   | alex@email.com
(1 row)

lenslocked=#
lenslocked=#
lenslocked=# SELECT * FROM users WHERE age>22;
 id | age | first_name | last_name |      email
----+-----+------------+-----------+------------------
  2 |  33 | dario      | Bastian   | dario2@email.com
(1 row)

lenslocked=# SELECT * FROM users WHERE age>20;
 id | age | first_name | last_name |      email
----+-----+------------+-----------+------------------
  1 |  22 | Alex       | Rabocse   | alex@email.com
  2 |  33 | dario      | Bastian   | dario2@email.com
(2 rows)

lenslocked=#
lenslocked=#
lenslocked=# SELECT * FROM users WHERE age>20 AND last_name='Bastian';
 id | age | first_name | last_name |      email
----+-----+------------+-----------+------------------
  2 |  33 | dario      | Bastian   | dario2@email.com
(1 row)

lenslocked=# SELECT * FROM users;
 id | age | first_name | last_name |      email
----+-----+------------+-----------+------------------
  1 |  22 | Alex       | Rabocse   | alex@email.com
  2 |  33 | dario      | Bastian   | dario2@email.com
(2 rows)

lenslocked=#
lenslocked=# UPDATE users SET first_name='Dario', last_name='Salazar' WHERE id=2;
UPDATE 1
lenslocked=#
lenslocked=# SELECT * FROM users;
 id | age | first_name | last_name |      email
----+-----+------------+-----------+------------------
  1 |  22 | Alex       | Rabocse   | alex@email.com
  2 |  33 | Dario      | Salazar   | dario2@email.com
(2 rows)

lenslocked=# SELECT * FROM users;
 id | age | first_name | last_name |      email
----+-----+------------+-----------+------------------
  1 |  22 | Alex       | Rabocse   | alex@email.com
  2 |  33 | Dario      | Salazar   | dario2@email.com
(2 rows)

lenslocked=#
lenslocked=# DELETE FROM users WHERE id=2;
DELETE 1
lenslocked=#
lenslocked=# SELECT * FROM users;
 id | age | first_name | last_name |     email
----+-----+------------+-----------+----------------
  1 |  22 | Alex       | Rabocse   | alex@email.com
(1 row)

lenslocked=#