Top 26 SQL Commands for Everyday Use

SQL (Structured Query Language) is a powerful tool used to manage and manipulate databases. Whether you're a beginner or an experienced developer, having a strong grasp of SQL commands can greatly enhance your ability to work with data. In this comprehensive guide, we'll explore the top 26 SQL commands for everyday use, covering everything from querying data to modifying database structures.

Check- 15 Best FREE SQL Courses

1. SELECT Statement

• Basic SELECT: Retrieve data from a database table.

  SELECT column1, column2 FROM table_name;

• SELECT DISTINCT: Retrieve unique values from a column.

  SELECT DISTINCT column_name FROM table_name;

• WHERE Clause: Filter data based on specified conditions.

  SELECT * FROM table_name WHERE condition;

• ORDER BY Clause: Sort the result set in ascending or descending order.

  SELECT * FROM table_name ORDER BY column_name ASC|DESC;

• LIMIT Clause: Limit the number of rows returned by a query.

  SELECT * FROM table_name LIMIT number_of_rows;

2. INSERT Statement

• INSERT INTO: Insert new records into a table.

  INSERT INTO table_name (column1, column2) VALUES (value1, value2);

3. UPDATE Statement

• UPDATE Table: Modify existing records in a table.

  UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

4. DELETE Statement

• DELETE FROM: Remove records from a table.

  DELETE FROM table_name WHERE condition;

5. CREATE TABLE Statement

• CREATE TABLE: Create a new table in the database.

  CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      ...
  );

6. ALTER TABLE Statement

• ADD Column: Add a new column to an existing table.

  ALTER TABLE table_name ADD column_name datatype;

• MODIFY Column: Modify the data type of an existing column.

  ALTER TABLE table_name MODIFY column_name new_datatype;

• DROP Column: Remove a column from an existing table.

  ALTER TABLE table_name DROP column_name;

7. DROP TABLE Statement

• DROP TABLE: Delete an existing table from the database.

  DROP TABLE table_name;

8. CREATE INDEX Statement

• CREATE INDEX: Create an index on a table.

  CREATE INDEX index_name ON table_name (column_name);

9. DROP INDEX Statement

• DROP INDEX: Remove an index from the database.

  DROP INDEX index_name;

10. TRUNCATE TABLE Statement

• TRUNCATE TABLE: Remove all records from a table, but keep the table structure intact.

  TRUNCATE TABLE table_name;

Check- 15 Best FREE SQL Courses

11. GROUP BY Clause

• GROUP BY: Group rows that have the same values into summary rows.

  SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;

12. HAVING Clause

• HAVING: Filter the results of a GROUP BY clause based on specified conditions.

  SELECT column1, COUNT(column2) FROM table_name GROUP BY column1 HAVING condition;

13. JOIN Clause

• INNER JOIN: Return rows when there is a match in both tables.

  SELECT column1, column2 FROM table1 INNER JOIN table2 ON table1.column = table2.column;

• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table.

  SELECT column1, column2 FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table.

  SELECT column1, column2 FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

• FULL OUTER JOIN: Return all rows when there is a match in either table.

  SELECT column1, column2 FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;

14. UNION Operator

• UNION: Combine the result sets of two or more SELECT statements.

  SELECT column1 FROM table1 UNION SELECT column1 FROM table2;

15. INTERSECT Operator

• INTERSECT: Return common rows between two SELECT statements.

  SELECT column1 FROM table1 INTERSECT SELECT column1 FROM table2;

16. EXCEPT Operator

• EXCEPT: Return rows from the first SELECT statement that are not in the second SELECT statement.

  SELECT column1 FROM table1 EXCEPT SELECT column1 FROM table2;

17. LIKE Operator

• LIKE: Search for a specified pattern in a column.

  SELECT column1 FROM table_name WHERE column1 LIKE pattern;

18. IN Operator

• IN: Specify multiple values in a WHERE clause.

  SELECT column1 FROM table_name WHERE column1 IN (value1, value2, ...);

19. BETWEEN Operator

• BETWEEN: Select values within a given range.

  SELECT column1 FROM table_name WHERE column1 BETWEEN value1 AND value2;

20. NULL Values

• IS NULL: Check if a column contains NULL values.

  SELECT column1 FROM table_name WHERE column1 IS NULL;

• IS NOT NULL: Check if a column does not contain NULL values.

  SELECT column1 FROM table_name WHERE column1 IS NOT NULL;

21. Aggregate Functions

• COUNT(): Count the number of rows in a result set.

  SELECT COUNT(column1) FROM table_name;

• SUM(): Calculate the sum of values in a column.

  SELECT SUM(column1) FROM table_name;

• AVG(): Calculate the average of values in a column.

  SELECT AVG(column1) FROM table_name;

• MIN(): Find the minimum value in a column.

  SELECT MIN(column1) FROM table_name;

• MAX(): Find the maximum value in a column.

  SELECT MAX(column1) FROM table_name;

22. CASE Statement

• CASE: Perform conditional logic within a query.

  SELECT column1, CASE WHEN condition THEN result1 ELSE result2 END FROM table_name;

23. Subqueries

• Single Row Subquery: Return one row of results.

  SELECT column1 FROM table_name WHERE column2 = (SELECT column2 FROM table2 WHERE condition);

• Multiple Row Subquery: Return multiple rows of results.

  SELECT column1 FROM table_name WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);

• Correlated Subquery: Reference columns from the outer query.

  SELECT column1 FROM table_name t1 WHERE condition = (SELECT column2 FROM table2 WHERE condition = t1.condition);

24. Views

• Views: Virtual tables created from a SELECT query.

  CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

25. Stored Procedures

• Stored Procedures: Precompiled SQL statements stored in the database.

  CREATE PROCEDURE procedure_name AS SQL_statements;

26. Transactions

• BEGIN TRANSACTION: Start a new transaction.

  BEGIN TRANSACTION;

• COMMIT: Save the changes made during the current transaction.

  COMMIT;

• ROLLBACK: Undo the changes made during the current transaction.

  ROLLBACK;

Check- 15 Best FREE SQL Courses

These are just a few of the many SQL commands available for everyday use. By mastering these commands, you'll be well-equipped to handle a wide range of database tasks efficiently and effectively. Keep practicing and exploring, and you'll become a SQL expert in no time!