SQL QUERY EXAMPLES

examples.sql

USE almodovar;

# INSERTION

# INSERT INTO roles (name) VALUES ('admin');
# INSERT INTO roles (name) VALUES ('user');

# INSERT INTO users (email, username, password, role_id) VALUES ('marta@gmail.com', 'marta', '123456', '2');

# CHANGE STRUCTURE
# ALTER TABLE users RENAME COLUMN roles_id TO role_id;

# CHANGE DATA
# UPDATE `almodovar`.`users` SET `role_id` = '1' WHERE (`id` = '1');

# READ ALL
# SELECT * FROM users;

# INSERT INTO updates (`update`, user_id) VALUES ('I am so happy to learn SQL today', 6)

# ALTER TABLE updates RENAME COLUMN `update` TO post;


# INSERT INTO updates (post, user_id) VALUES ('I am so happy to learn SQL today', 6);

# DELETE FROM updates WHERE id = '2';

# UPDATE updates SET post = 'I am getting advanced' WHERE id = 1;

# SELECT updates.id, updates.post, users.username FROM updates, users WHERE users.id = updates.user_id;

# INSERT INTO updates (post, user_id) VALUES ('My second message', 6);
# SELECT updates.id, updates.post, users.username FROM updates LEFT JOIN users ON users.id = updates.user_id;
-- SELECT updates.id, updates.post, users.username FROM updates INNER JOIN users ON users.id = updates.user_id;
-- SELECT updates.id, updates.post, users.username FROM updates JOIN users ON users.id = updates.user_id;
-- SELECT updates.id, updates.post, users.username FROM updates RIGHT JOIN users ON users.id = updates.user_id;
# INSERT INTO updates (post, user_id) VALUES ('I am a ghost', 6);

# INSERT INTO users (email, username, password, role_id) VALUES ('ghost@gmail.com', 'ghost', '123456', '2');

SELECT updates.id, updates.post, users.username, COUNT(likes.update_id) AS 'likes'
	FROM updates 
    JOIN users ON users.id = updates.user_id
    JOIN likes ON updates.id = likes.update_id
    GROUP BY updates.id
    ORDER BY likes DESC