Skip to content

Instantly share code, notes, and snippets.

@midorikocak
Created January 22, 2022 13:31
Show Gist options
  • Save midorikocak/d20497480d9e862c25d7a8675a8c81c7 to your computer and use it in GitHub Desktop.
Save midorikocak/d20497480d9e862c25d7a8675a8c81c7 to your computer and use it in GitHub Desktop.
SQL QUERY EXAMPLES
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment