Created
January 22, 2022 13:31
-
-
Save midorikocak/d20497480d9e862c25d7a8675a8c81c7 to your computer and use it in GitHub Desktop.
SQL QUERY EXAMPLES
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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