General Commands

1. Connection

psql -h localhost -d johto -U silver

Prompt: Password for user silver: (Enter your password and press enter)

2. List Databases

\l

Prompt:

                              List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 johto     | myuser   | UTF8     | C.UTF-8 | C.UTF-8 |
 hoenn     | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
           |          |          |         |         | postgres=CTc/postgres
(4 rows)

3. List Tables

\dt

Prompt:

          List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | user | table | silver
(1 row)

---

User and Role Management:

1. Create User

CREATE USER silver WITH PASSWORD 'totodileRulez';

Prompt: CREATE ROLE

2. Grant Privileges

GRANT ALL PRIVILEGES ON DATABASE johto TO silver;

Prompt: GRANT

3. Create Role

CREATE ROLE trainer WITH LOGIN PASSWORD 'totodileRulez';

Prompt: CREATE ROLE

---

Database Actions:

1. Create Database

CREATE DATABASE johto;

Prompt: CREATE DATABASE

2. Drop Database

DROP DATABASE johto;

Prompt: DROP DATABASE

---

Query Methods:

1. Select All

SELECT * FROM pokemon;

Prompt:

 id |   name
----+----------
  1 | Teddiursa
  2 | Sunkern
(2 rows)

2. Where Clause

SELECT * FROM pokemon WHERE name = 'Espeon';

Prompt:

 id |   name
----+----------
  1 | Espeon
(1 row)

---

Complex Commands:

1. Join Tables

SELECT * FROM pokemon INNER JOIN moves ON pokemon.id = moves.pokemon_id;

Prompt:

 pokemon_id | name    | move_id | move
------------+---------+---------+----------
       1 | Teddiursa|        1 | Tackle
       2 | Sunkern |        2 | Vine Whip
(2 rows)

---

More Query Methods:

3. Max

SELECT MAX(level) FROM pokemon;

Prompt:

 max
-----
  100
(1 row)

4. Min

SELECT MIN(level) FROM pokemon;

Prompt:

 min
-----
  1
(1 row)

5. Avg

SELECT AVG(level) FROM pokemon;

Prompt:

        avg
---------------------
 50.5000000000000000
(1 row)

6. Sum

SELECT SUM(level) FROM pokemon;

Prompt:

 sum
-----
 101
(1 row)

7. Group By

SELECT COUNT(*), type FROM pokemon GROUP BY type;

Prompt:

 count |   type
-------+-------------
    2 | Dark
    3 | Steel
(2 rows)

8. Order By

SELECT * FROM pokemon ORDER BY level DESC;

Prompt:

 id |   name    | level | type
----+-----------+-----+---------
  1 | Unown|  100 | Psychic
  3 | Snubbul|  33 | Fairy
  2 | Ursaring|  1 | Normal
(3 rows)

---

More Complex Commands:

2. Subquery

SELECT * FROM (SELECT * FROM pokemon ORDER BY level DESC LIMIT 2) AS subquery;

Prompt:

 id |   name    | level | type
----+-----------+-----+---------
  1 | Chikorita |  100 | Grass
  3 | Cyndaquil |  33 | Fire
(2 rows)

3. Create Index

CREATE INDEX idx_pokemon_level ON pokemon(level);

Prompt: CREATE INDEX

4. Create View

CREATE VIEW high_level_pokemon AS SELECT * FROM pokemon WHERE level > 50;

Prompt: CREATE VIEW

5. Select from View

SELECT * FROM high_level_pokemon;

Prompt:

 id |   name    | level | type
----+-----------+-----+---------
  1 | Totodile  |  100 | Water
(1 row)

---

More Query Methods:

9. Select Distinct

SELECT DISTINCT type FROM pokemon;

Prompt:

  type
-----------
 Grass
 Fire
(2 rows)

10. Like Operator

SELECT * FROM pokemon WHERE name LIKE '%quil%';

Prompt:

 id |   name    | level | type
----+-----------+-----+---------
  1 | Cyndaquil |  100 | Fire
  2 | Quilava |  1 | Fire
(2 rows)

---

Advanced Complex Commands:

6. Right Join

SELECT * FROM moves RIGHT JOIN pokemon ON pokemon.id = moves.pokemon_id;

Prompt:

 pokemon_id | name  | move_id | move
------------+-------+---------+----------
       1 | Chikorita|        1 | Tackle
       2 | Bayleef |        2 | Vine Whip
       3 | Cyndaquil |     NULL | NULL
(3 rows)

7. Full Outer Join

SELECT * FROM moves FULL OUTER JOIN pokemon ON pokemon.id = moves.pokemon_id;

Prompt:

 pokemon_id | name  | move_id | move
------------+-------+---------+----------
       1 | Chikorita|        1 | Tackle
       2 | Bayleef |        2 | Vine Whip
       3 | Cyndaquil |     NULL | NULL
(3 rows)

8. Self Join

SELECT A.name, B.name FROM pokemon A, pokemon B WHERE A.type = B.type AND A.name != B.name;

   name    |   name
-----------+----------
 Chikorita| Bayleef
 Bayleef | Chikorita
(2 rows)

---

Function and Procedures:

1. Creating Functions

CREATE FUNCTION increase_level(integer, integer) RETURNS integer AS $$
BEGIN
   RETURN $1 + $2;
END;
$$ LANGUAGE plpgsql;

Prompt: CREATE FUNCTION

2. Calling Functions

SELECT increase_level(1, 2);

Prompt:

 increase_level
----------------
              3
(1 row)

3. Creating Procedures

CREATE PROCEDURE archive_old_pokemon() AS $$
BEGIN
   DELETE FROM pokemon WHERE level < 10;
END;
$$ LANGUAGE plpgsql;

Prompt: CREATE PROCEDURE

4. Calling Procedures

CALL archive_old_pokemon();

Prompt: CALL

5. Creating Triggers

CREATE TRIGGER check_level_before_insert
BEFORE INSERT ON pokemon
FOR EACH ROW
WHEN (NEW.level > 100)
DO
$$
BEGIN
   RAISE EXCEPTION 'Level cannot be more than 100';
END;
$$ LANGUAGE plpgsql;

Prompt: CREATE TRIGGER

---

Error Handling:

PostgreSQL returns errors with specific SQLSTATE values. When an error occurs, an exception is thrown with an associated SQLSTATE value.

For example, if you attempt to create a table that already exists:

CREATE TABLE pokemon(id SERIAL PRIMARY KEY, name VARCHAR(100));

You might get an error like:

ERROR:  relation "pokemon" already exists

In this case, you would need to either drop the existing table or change the name of the table you are trying to create.