-- Showing all databases
SHOW DATABASES;

-- creating a database
CREATE DATABASE comrades;

-- switching current database
USE comrades;

-- showing all tables in the current database
SHOW TABLES;

-- delete/drop a DATABASE
DROP DATABASE comrades_x;

-- delete/drop a TABLE
DROP TABLE members_x;

-- creating a table
CREATE TABLE members (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    firstName VARCHAR(255) NOT NULL,
    lastName VARCHAR(255) NOT NULL,
    age INT,
    gender VARCHAR(255) NOT NULL,
    interestedCourseId INT
)

-- creating a table
CREATE TABLE courses (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description VARCHAR(255)
);

-- creating a table
CREATE TABLE sessions (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    day VARCHAR(255) NOT NULL
);

-- adding a new record to a table
INSERT INTO members ( firstName, age, lastName, gender)
VALUES ("Elvis", 35, "Feda", "male");

-- adding multiple records to a table
INSERT INTO members ( firstName, lastName, age, gender)
VALUES
    ("Francis", "Osei", 72, "male"),
    ("Henry", "Antwi", 34, "male"),
    ("Emefa", "Attikpo", 44, "female"),
    ("Bernice", "Yevugah", 55, "male"),
    ("Frank", "Oppong", 88, "male"),
    ("Manuel", "Baafi", 23, "male");

-- selecting all records from a table
SELECT * FROM members;

-- selecting all records but specifying what columns to be shown
SELECT id, firstName FROM members;

-- updating a record and specifying conditions
UPDATE members
SET gender = "female"
WHERE id=5 AND gender="male";

-- inserting another record
INSERT INTO members ( firstName, age, lastName, gender)
VALUES ("Kofi", 35, "Ghana", "male");

-- deleting a record
DELETE FROM members
WHERE id=8;

-- Joining tables
SELECT * FROM members
LEFT JOIN courses
ON members.interestedCourseId=courses.id;