Common SQL Commands

Most Common SQL Commands


Structured Query Language, or SQL, stands as the backbone of relational database management systems (RDBMS). SQL commands are the tools that empower developers, data analysts, and database administrators to interact seamlessly with databases. In this blog post, we’ll explore the most commonly used SQL commands that for database manipulation and querying.

The following SQL Statements are included in this article:
1. SELECT – Retrieving Data
2. INSERT – Adding Data
3. UPDATE – Modifying Data
4. DELETE – Removing Data
5. CREATE – Creating Tables and Databases
6. ALTER – Modifying Database Structure
7. DROP – Deleting Tables and Databases
8. ALTER TABLE – Modifying Table Structure
9. CREATE INDEX – Enhancing Query Performance
10. GRANT and REVOKE – Managing Permissions

Top 10 Common SQL Commands List

1. SELECT – Retrieving Data

The SELECT statement is the core of SQL, enabling the retrieval of data from one or more tables. Users can specify desired columns and apply filtering conditions using the WHERE clause. JOIN operations can also be added to merge data from multiple tables.

-- Example SELECT query 
SELECT column1, column2 
FROM table 
WHERE condition;

-- Example SELECT with JOIN
SELECT t1.column1, t2.column2 
FROM table1 t1 
JOIN table2 t2 ON t1.id = t2.id 
WHERE condition;

2. INSERT – Adding Data

The INSERT SQL command adds new records to a table. Values can be inserted explicitly or selected from another table.

-- Example INSERT query
INSERT INTO table (column1, column2) VALUES (value1, value2);

3. UPDATE – Modifying Data

When it comes to modifying existing records we use the UPDATE SQL command. It allows the alteration of values in specific columns based on specified conditions.

-- Example UPDATE query 
UPDATE table 
SET column1 = value1, column2 = value2 
WHERE condition;

4. DELETE – Removing Data

The DELETE command is utilized to remove records from a table based on certain conditions. Caution should be exercised, as this command permanently deletes data.

-- Example DELETE query 
DELETE 
FROM table
WHERE condition;

5. CREATE – Creating Tables and Databases:

The CREATE command is used to create new databases, tables, and other database objects. It allows the definition of the database structure, including columns, data types, and constraints.

-- Example CREATE TABLE query 
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );

6. ALTER – Modifying Database Structure:

The ALTER command is used to modify the structure of an existing database or table. It facilitates actions such as adding, deleting, or modifying columns, as well as making changes to data types and constraints.

-- Example ALTER TABLE query 
ALTER TABLE table_name ADD column_name datatype;

7. DROP – Deleting Tables and Databases

The DROP command is the counterpart of CREATE and is used to delete databases, tables, and other database objects. Caution is advised, as it permanently removes the specified object and its data.

-- Example DROP TABLE query 
DROP TABLE table_name;

8. ALTER TABLE – Modifying Table Structure

The ALTER TABLE command allows for a variety of structural changes to an existing table. You can add or drop columns, modify data types, and even rename columns.

-- Example ALTER TABLE query 
ALTER TABLE table_name ADD column_name datatype;

9. CREATE INDEX – Enhancing Query Performance

The CREATE INDEX command is essential for optimizing database query performance. By creating an index on one or more columns of a table, you enable the database engine to retrieve data more efficiently, reducing query response times.

-- Example CREATE INDEX query 
CREATE INDEX index_name ON table_name (column1, column2);

10. GRANT and REVOKE – Managing Permissions

The GRANT and REVOKE commands allow us to control database security and access. Database Administrators will use this to control access. While not universally applicable to all RDBMS, these commands are commonly supported in major systems like MySQL, PostgreSQL, and Microsoft SQL Server.

-- Example GRANT query 
GRANT SELECT, INSERT ON table_name TO user_name; 

-- Example REVOKE query 
REVOKE DELETE ON table_name FROM user_name;

Conclusion

I may add more SQL queries and information to this post in future. If you think I can update this page with particular info to improve the post please do add a comment below. Also, feel free to check out my SQL Queries tag on this site for more tips on running SQL commands from a SQL DBA.


Leave a Reply

Your email address will not be published. Required fields are marked *