dbcourse_sql_code

/* create schema */
DROP DATABASE IF EXISTS dbcourse;
CREATE SCHEMA `dbcourse` ;

USE dbcourse;

CREATE TABLE posts(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

CREATE TABLE comments(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id INT UNSIGNED NOT NULL,
    content VARCHAR(500),
    FOREIGN KEY(post_id) REFERENCES posts(id)
);

CREATE TABLE emotion_type(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(500) NOT NULL
);

CREATE TABLE emotions(
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id INT UNSIGNED NOT NULL,
    emotion_id INT UNSIGNED NOT NULL,
    FOREIGN KEY(post_id) REFERENCES posts(id),
    FOREIGN KEY(emotion_id) REFERENCES emotion_type(id)
);

/* insert data */
INSERT INTO posts
    (title)
VALUES
    ("First post"),
    ("Second post"),
    ("Tirth post");


INSERT INTO comments
    (post_id, content)
VALUES
    (1, "First comment"),
    (1, "Second comment"),
    (1, "Tirth comment"),
    (2, "Gore dolu"),
    (2, "Ala bala"),
    (3, "Bla bla");


INSERT INTO emotion_type
    (name)
VALUES
    ("Like"),
    ("Love"),
    ("Sad"),
    ("Wow"),
    ("Angry"),
    ("Haha");

INSERT INTO emotions
    (post_id, emotion_id)
VALUES
    (1, 1),
    (1, 1),
    (1, 3),
    (1, 2),
    (2, 4),
    (2, 3),
    (3, 2),
    (3, 1);

/* ---------------------------- */
/* Task 8*/
USE dbcourse;
SELECT p.title
FROM posts p
WHERE p.title like "%F%"

/* ---------------------------- */
/* Task 9*/
USE dbcourse;
SELECT et.name, count(et.id) as 'Emotion_total_count'
FROM emotions e
INNER JOIN emotion_type et
ON e.emotion_id = et.id
GROUP BY e.emotion_id

/* ---------------------------- */
/* Task 10*/
USE dbcourse;
SELECT e.post_id, et.name
FROM emotions e
LEFT JOIN emotion_type et
ON e.emotion_id = et.id
UNION
SELECT e.post_id, et.name
FROM emotions e
LEFT JOIN emotion_type et
ON e.emotion_id = et.id

/* ---------------------------- */
/* Task 11*/
USE dbcourse;
SELECT p.title, count(c.id)
FROM posts p
JOIN comments c
ON p.id = c.post_id
GROUP BY p.id

/* ---------------------------- */
/* Task 12*/
USE dbcourse;
DELIMITER //
CREATE PROCEDURE GetPostComments(post_id INT)
 BEGIN
     SELECT c.content
     FROM comments c
     WHERE c.post_id = post_id;
 END //
DELIMITER ;

/* demo call */
USE dbcourse;
CALL GetPostComments(1);