I have put together a list of SQL statements that cover SQL basics and it is something you can use to refresh your SQL knowledge in a couple of minutes.
This file contains hidden or 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
| — Creating a table | |
| CREATE TABLE users( | |
| id INT AUTO_INCREMENT, | |
| first_name VARCHAR(100), | |
| email VARCHAR(70), | |
| date DATETIME, | |
| PRIMARY KEY(id) | |
| ); | |
| — Foreign key reference | |
| CREATE TABLE users( | |
| id INT AUTO_INCREMENT, | |
| user_id INT, | |
| first_name VARCHAR(100), | |
| email VARCHAR(70), | |
| date DATETIME, | |
| PRIMARY KEY(id), | |
| FOREIGN KEY(user_id) REFERENCE users(id) | |
| ); | |
| — Inserting into a table | |
| INSERT INTO users(column1, column2) VALUES(value1, value2); | |
| — Selecting from a table | |
| SELECT * FROM users; | |
| — Deleting from a table | |
| DELETE * FROM users WHERE id = 6; | |
| — Update one row in a table | |
| UPDARE users SET email = ‘omkar@gmail.com’ WHERE id = 2; | |
| — Alter the table | |
| ALTER TABLE users ADD age VARCHAR(3); | |
| ALTER TABLE users MODIFY COLUMN age INT(3); | |
| — More Select operations | |
| SELECT * FROM users WHERE location = ‘mumbai’; | |
| SELECT * FROM users WHERE location=’mumbai’ AND size=100; | |
| SELECT * FROM users ORDER BY last_name DESC; | |
| SELECT CONCAT(first_name, ‘ ‘, last_name) AS name FROM users; | |
| SELECT DISTINCT location from users; | |
| SELECT * FROM users WHERE age BETWEEN 20 AND 25; | |
| SELECT * FROM users WHERE dept IN (‘design’, ‘sales’); | |
| SELECT * FROM users WHERE dept LIKE ‘d%’; | |
| — A bit of Regex | |
| SELECT * FROM users WHERE REGEXP ‘^o’; | |
| — ^ starts with | |
| — $ ends with | |
| — .* any characters | |
| — [abc] any from a given list of a, b, c | |
| — For example, the line below selects everything that starts and ends with a vowel: | |
| SELECT * FROM users WHERE ‘^[aeiou].*[aeiou]$’; | |
| — Indexes | |
| CREATE INDEX LIndex ON users(location); | |
| DROP INDEX LIndex ON users; | |
| — Inner Join | |
| SELECT | |
| u.first_name | |
| u.last_name | |
| p.title | |
| p.publish_date | |
| FROM users AS u | |
| INNER JOIN posts AS p | |
| ON u.id = p.user_id | |
| ORDER BY p.title; | |
| — Outer Join | |
| SELECT | |
| comments.body | |
| posts.title | |
| FROM comments | |
| LEFT JOIN posts | |
| ON posts.id = comments.post_id | |
| ORDER BY posts.title; | |
| — Joining comments and posts table using LEFT JOIN makes sense | |
| — because it’ll fetch comments and post title of every post that has a comment. | |
| — If we instead do a RIGHT JOIN, | |
| — it’ll fetch every post even if it has no comment. That doesn’t make sense. | |
| — Processing Order | |
| — WHERE gets processed before GROUP BY | |
| — HAVING gets processed after GROUP BY | |
| SELECT store_id, COUNT(active) | |
| FROM customer | |
| WHERE active = 1 | |
| GROUP BY store_id; | |
| SELECT rating, COUNT(rating) AS number | |
| FROM film | |
| GROUP BY rating | |
| HAVING number > 80 | |
| ORDER BY number DESC; |
That’s all in this post. I may add more in a separate post later.
In case you’re interested to learn more, there are a few good SQL crash course videos on YouTube. If you ask me, I recommend this one by Traversy Media and freeCodeCamp one by Mike Dane).
CS Mumbai 