Top 10 SQL Commands

Structured Query Language (SQL) is a widely-used programming language for managing and interacting with databases. It allows users to create, manipulate, and retrieve data efficiently. Mastering basic SQL commands is essential for anyone working with databases, whether they are a beginner or an experienced professional. In this blog post, the top 10 SQL commands needed to make database work more efficient and productive will be discussed.

1. SELECT

The SELECT command is the foundation of SQL and is used to retrieve data from a table. It allows the display of all the rows and columns from a specified table. For example, if there is a table named “city” containing information about different cities, the following command can be used to display all the data:

SELECT * 
FROM city;

2. WHERE

The WHERE clause allows filtering the results based on specific conditions. It is used in conjunction with the SELECT command. For example, to display cities with ZIP codes greater than 1345, the following command can be used:

SELECT *
FROM city
WHERE zip > 1345;

3. BETWEEN

The BETWEEN command is used to display results within a specified range. For example, to view cities with ZIP codes between 1345 and 1996, the following command can be used:

SELECT * 
FROM city 
WHERE zip BETWEEN 1345 AND 1996;

4. LIMIT

The LIMIT command allows the definition of the number of results to be displayed. For example, to display only the first 10 cities, the following command can be used:

SELECT * 
FROM city 
LIMIT 10;

5. GROUP BY

The GROUP BY command is used to group rows with similar values and is often used with aggregate functions like COUNT(), SUM(), or AVG(). It is typically used in conjunction with the SELECT statement. For example, to count the number of students in each city, the following command can be used:

SELECT COUNT(studentID), 
city FROM student 
GROUP BY city;

6. ORDER BY DESC/ASC

The ORDER BY command is used to sort the results in ascending (ASC) or descending (DESC) order based on one or more columns. For instance, to display the number of students in each city, sorted in descending order, the following command can be used:

SELECT COUNT(studentID), 
city FROM student 
GROUP BY city
ORDER BY COUNT(studentID) DESC;

7. INSERT INTO

The INSERT INTO command is used to add new rows to a table. For example, if there is a table named “student” with columns studentID, fname, and lname, a new row can be inserted with the following command:

INSERT INTO student(studentID, fname, lname)
VALUES (5055, 'Maria', 'Jones');

8. DELETE

The DELETE command is used to remove rows from a table based on a specified condition. For example, to delete all students from the city of Karachi, the following command can be used:

DELETE FROM student
WHERE city='Karachi';

9. UPDATE

The UPDATE command is used to modify existing data in a table. A single row or multiple rows can be updated at once. For example, to update the first name and last name of a student from Johannesburg, the following command can be used:

UPDATE student
SET fname='Max',
lname='Willy'
WHERE city='Johannesburg';

10. IS NULL

The IS NULL command is used to identify rows with missing or empty values in a specified column. It is helpful when trying to find incomplete or inconsistent data in a database. For example, to find students with missing marks, the following command can be used:

SELECT fname, lname
FROM student
WHERE marks IS NULL;

Mastering these essential SQL commands will empower anyone working with databases to perform a wide range of tasks, from basic data retrieval to more complex data manipulation and analysis. By incorporating these commands into daily work, database management skills can be improved, and the quality of data-driven decisions can be enhanced.