Beginners Guide: Commonly used basic SQL commands

Introduction

Ever wondered how websites and applications manage all their data? 

The answer is often SQL, a powerful and versatile language for interacting with databases. If you're new to SQL, this guide is your perfect starting point! 

We'll explore the fundamental commands you'll need to navigate databases, retrieve information, and even manipulate data. Get ready to unlock the world of SQL and empower yourself to extract insights from the vast world of data!

Sponsored

Commands

1. SELECT — It selects the data.

SELECT CURRENT_TIMESTAMP();

SELECT name, qualification FROM users;

2. FROM — It specifies the source from where to collect data.

SELECT * FROM users;

3. WHERE — It executes the condition.

SELECT * FROM users WHERE id = 10;

4. AS — It gives a nickname to the table or fields.

SELECT first_name AS name FROM users;

5. JOIN — Combines rows from 2 or more tables.

SELECT users.name, scores.percentage FROM users 
JOIN scores ON users.id = scores.user_id;

6. AND, OR— Both Combine query conditions.

AND checks for all conditions that should be true,

SELECT * FROM users WHERE id = 10 AND qualification = "B.E.";

OR checks for at least one condition to be true,

SELECT * FROM users WHERE gender="male" OR qualification = "B.E.";

7. LIMIT, OFFSET — Used to control the number of records to return, optionally by skipping a certain number of records.

LIMIT returns no. of records,

SELECT * FROM users LIMIT 10;

OFFSET skips no. of records,

SELECT * FROM users LIMIT 10 OFFSET 5;

The above query will return 5 records after skipping 10 records.

Note — OFFSET doesn’t work without LIMIT.

8. IN — It’s a shorthand method for multiple OR conditions, and selects records by matching multiple values.

SELECT * FROM users WHERE qualification IN("B.E.", "M.E.", "M.C.A.");

The above query will return all the users who have one of the listed qualifications inside In().

9. CASE — It checks for specific conditions to be met, simply on the basis of if-else.

SELECT id, name,
CASE 
  WHEN qualification = "B.E." THEN "Bechelors"
  WHEN qualification = "M.E." THEN "Masters"
  ELSE graduate 
END AS degree
FROM users;
 

It terminates the execution once it finds the condition matched, and returns the value specified in THEN.

It doesn’t get a matching condition, it returns the value from the ELSE part. If the ELSE part is also not specified, it will return a NULL.

10. IS NULL, IS NOT NULL— It checks if the value is null or the value is not null respectively.

SELECT * FROM users WHERE qualification IS NULL;

SELECT * FROM users WHERE qualification IS NOT NULL;

11. LIKE — It’s the pattern-matching operator that works the same as WHERE.

The percentage(%) sign is used to match the rest of the part from the value.

SELECT * FROM users WHERE name LIKE "al%";

The above query will select all the records where the user’s name starts with al. For example, Alex, Alis

It also allows finding records with specific letters on a certain position at the string.

SELECT * FROM users WHERE name LIKE "_a%";

The above query will search for the records where the name has the letter a in the second position. For example, Jane, Jack

With the help of percentage(%) and underscore(_) signs, one can customize the LIKE query according to requirements.

NOTE — It also works with the negation operator(NOT), as NOT LIKE.

12. DISTINCT — It’s is used to remove duplicate records from the table and fetch only the unique records.

SELECT DISTINCT name FROM users;

The above query will remove users with duplicate names from the result.

13. EXPLAIN — It’s used to obtain information about how the query is being executed on the table.

EXPLAIN SELECT * FROM users;  

NOTE — It’s majorly used for database optimization.

14. ALTER TABLE — Used to update the existing table or columns.

ALTER TABLE Customers ADD email varchar(255);

15. CREATE — Used to create a table.

CREATE TABLE scores (
    id int,
    first_name varchar(255),
    last_name varchar(255)
);

16. DELETE — Used to delete records from the table.

DELETE FROM users WHERE id = 10;

17. UPDATE — Used to update records of the table.

UPDATE users SET name = "Joe" WHERE id = 10;

18. INSERT — Used to insert records into a table.

INSERT INTO users(id, first_name, last_name) VALUES(1, "joe", "shoen");

OR

INSERT INTO users VALUES(1, "joe", "shoen");

19. TRUNCATE — Makes the table empty.

TRUNCATE TABLE users;

20. DROP — Deletes the table.

DROP TABLE users;

Clauses

1. GROUP BY — Groups the records according to given column values.

SELECT COUNT(*) as qualifications FROM users GROUP BY qualification;

The above query will return the total number of qualifications, by grouping the records based on qualification column values.

2. ORDER BY — Arranges the data in ascending or descending according to specified columns.

SELECT * FROM users ORDER BY id ASC;

SELECT * FROM users ORDER BY id, score DESC;

3. HAVING — It’s used with a GROUP BY clause, as WHERE doesn’t work with aggregate results.

SELECT COUNT(id), qualification 
FROM users 
GROUP BY qualification
HAVING COUNT(id) > 3;

The above query will return the number of users grouped by qualification, where the number of users > 3.

4. UNION — It’s used to combine the result set of two or more SELECT statements.

SELECT name, email FROM users
UNION
SELECT name, email FROM admins;

5. EXISTS — It’s used to check for the existence of any record using a subquery.

It also returns true or false, based on the result of the subquery used with it.

SELECT EXISTS 
(SELECT name FROM users 
WHERE id = 1) AS isExist;

Functions

1. COUNT() — It’s used to return the count of an expression, with or without the conditions.

SELECT COUNT(*) as total_users FROM users;

2. SUM() — It’s used to return the total value of a given set of values.

SELECT SUM(scores) FROM users;

3. AVG() — It’s used to obtain the average value of a given set of values.

SELECT AVG(scores) FROM users;

4. MIN() — It finds the minimum value from the given set of values.

SELECT MIN(scores) FROM users;

5. MAX() — It finds the maximum value from the given set of values.

SELECT MAX(scores) FROM users;

Wrap up!

We’ve learned basic SQL commands and clauses that are the basic building blocks of MySQL.

They can be used while querying the database, or sometimes they also become helpful as an individual.

Learn More