Top 26 SQL Commands for Everyday Use
Tags: sql, sqlserver, programming, database
Published: 2024-05-30 05:38:03

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 ](https://www.mltut.com/best-free-sql-courses/)**

## 1. SELECT Statement

- **Basic SELECT:** Retrieve data from a database table.
  ```sql
  SELECT column1, column2 FROM table_name;
  ```

- **SELECT DISTINCT:** Retrieve unique values from a column.
  ```sql
  SELECT DISTINCT column_name FROM table_name;
  ```

- **WHERE Clause:** Filter data based on specified conditions.
  ```sql
  SELECT * FROM table_name WHERE condition;
  ```

- **ORDER BY Clause:** Sort the result set in ascending or descending order.
  ```sql
  SELECT * FROM table_name ORDER BY column_name ASC|DESC;
  ```

- **LIMIT Clause:** Limit the number of rows returned by a query.
  ```sql
  SELECT * FROM table_name LIMIT number_of_rows;
  ```

## 2. INSERT Statement

- **INSERT INTO:** Insert new records into a table.
  ```sql
  INSERT INTO table_name (column1, column2) VALUES (value1, value2);
  ```

## 3. UPDATE Statement

- **UPDATE Table:** Modify existing records in a table.
  ```sql
  UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
  ```

## 4. DELETE Statement

- **DELETE FROM:** Remove records from a table.
  ```sql
  DELETE FROM table_name WHERE condition;
  ```

## 5. CREATE TABLE Statement

- **CREATE TABLE:** Create a new table in the database.
  ```sql
  CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      ...
  );
  ```

## 6. ALTER TABLE Statement

- **ADD Column:** Add a new column to an existing table.
  ```sql
  ALTER TABLE table_name ADD column_name datatype;
  ```

- **MODIFY Column:** Modify the data type of an existing column.
  ```sql
  ALTER TABLE table_name MODIFY column_name new_datatype;
  ```

- **DROP Column:** Remove a column from an existing table.
  ```sql
  ALTER TABLE table_name DROP column_name;
  ```

## 7. DROP TABLE Statement

- **DROP TABLE:** Delete an existing table from the database.
  ```sql
  DROP TABLE table_name;
  ```

## 8. CREATE INDEX Statement

- **CREATE INDEX:** Create an index on a table.
  ```sql
  CREATE INDEX index_name ON table_name (column_name);
  ```

## 9. DROP INDEX Statement

- **DROP INDEX:** Remove an index from the database.
  ```sql
  DROP INDEX index_name;
  ```

## 10. TRUNCATE TABLE Statement

- **TRUNCATE TABLE:** Remove all records from a table, but keep the table structure intact.
  ```sql
  TRUNCATE TABLE table_name;
  ```

**Check- [15 Best FREE SQL Courses ](https://www.mltut.com/best-free-sql-courses/)**

## 11. GROUP BY Clause

- **GROUP BY:** Group rows that have the same values into summary rows.
  ```sql
  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.
  ```sql
  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.
  ```sql
  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.
  ```sql
  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.
  ```sql
  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.
  ```sql
  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.
  ```sql
  SELECT column1 FROM table1 UNION SELECT column1 FROM table2;
  ```

## 15. INTERSECT Operator

- **INTERSECT:** Return common rows between two SELECT statements.
  ```sql
  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.
  ```sql
  SELECT column1 FROM table1 EXCEPT SELECT column1 FROM table2;
  ```

## 17. LIKE Operator

- **LIKE:** Search for a specified pattern in a column.
  ```sql
  SELECT column1 FROM table_name WHERE column1 LIKE pattern;
  ```

## 18. IN Operator

- **IN:** Specify multiple values in a WHERE clause.
  ```sql
  SELECT column1 FROM table_name WHERE column1 IN (value1, value2, ...);
  ```

## 19. BETWEEN Operator

- **BETWEEN:** Select values within a given range.
  ```sql
  SELECT column1 FROM table_name WHERE column1 BETWEEN value1 AND value2;
  ```

## 20. NULL Values

- **IS NULL:** Check if a column contains NULL values.
  ```sql
  SELECT column1 FROM table_name WHERE column1 IS NULL;
  ```

- **IS NOT NULL:** Check if a column does not contain NULL values.
  ```sql
  SELECT column1 FROM table_name WHERE column1 IS NOT NULL;
  ```

## 21. Aggregate Functions

- **COUNT():** Count the number of rows in a result set.
  ```sql
  SELECT COUNT(column1) FROM table_name;
  ```

- **SUM():** Calculate the sum of values in a column.
  ```sql
  SELECT SUM(column1) FROM table_name;
  ```

- **AVG():** Calculate the average of values in a column.
  ```sql
  SELECT AVG(column1) FROM table_name;
  ```

- **MIN():** Find the minimum value in a column.
  ```sql
  SELECT MIN(column1) FROM table_name;
  ```

- **MAX():** Find the maximum value in a column.
  ```sql
  SELECT MAX(column1) FROM table_name;
  ```

## 22. CASE Statement

- **CASE:** Perform conditional logic within a query.
  ```sql
  SELECT column1, CASE WHEN condition THEN result1 ELSE result2 END FROM table_name;
  ```



## 23. Subqueries

- **Single Row Subquery:** Return one row of results.
  ```sql
  SELECT column1 FROM table_name WHERE column2 = (SELECT column2 FROM table2 WHERE condition);
  ```

- **Multiple Row Subquery:** Return multiple rows of results.
  ```sql
  SELECT column1 FROM table_name WHERE column2 IN (SELECT column2 FROM table2 WHERE condition);
  ```

- **Correlated Subquery:** Reference columns from the outer query.
  ```sql
  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.
  ```sql
  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.
  ```sql
  CREATE PROCEDURE procedure_name AS SQL_statements;
  ```

## 26. Transactions

- **BEGIN TRANSACTION:** Start a new transaction.
  ```sql
  BEGIN TRANSACTION;
  ```

- **COMMIT:** Save the changes made during the current transaction.
  ```sql
  COMMIT;
  ```

- **ROLLBACK:** Undo the changes made during the current transaction.
  ```sql
  ROLLBACK;
  ```
**Check- [15 Best FREE SQL Courses ](https://www.mltut.com/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!