SQL stands for Structured Query Language. It is a programming language designed to manage data stored in mostly in relational databases.
Here we are going to see some common SQL commands what are very useful for both beginners and advanced users.
1. ALTER TABLE – Altering table details .ALTER TABLE- adding a new column to existing table.
ALTER TABLE table_name
ADD column_name datatype;
2. AND – And is used to select details form a table. It is used to combine multiple conditions. Both conditions must be true for the row to be included in the result set.
SELECT column_name(s)
FROM table_name
WHERE column_1 = value_1
AND column_2 = value_2;
3. AS – As is used to rename a column or columns as alias in select statement.
SELECT column_name AS ‘Alias’
FROM table_name;
4. AVG() – Avg is a short form of average. This is one of the aggregate function in SQL. It’s outcome is the athematic mean of the values in the specified column
SELECT AVG(column_name)
FROM table_name;
5. BETWEEN – Between specifies the range of values to be chosen in a select statement. The values can be numbers, texts or dates.
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value_1 AND value_2;
6. CASE – Case is a powerful command that replace If statement.
SELECT column_name,
CASE
WHEN condition THEN ‘Result_1’
WHEN condition THEN ‘Result_2’
ELSE ‘Result_3’
END
FROM table_name;
7. COUNT() – Count is used to know how many rows are in the whole table. Count is one of aggregate functions. It does not count null values , if present.
SELECT COUNT(column_name)
FROM table_name;
8. CREATE TABLE- Create is the command to create a table. It is followed by table name and columns needed to create a table.
CREATE TABLE table_name (
column_1 datatype,
column_2 datatype,
column_3 datatype);
9. DELETE -Delete is used to remove rows from a table. If WHERE condition is supplied every row in the table is erased.
DELETE FROM table_name
WHERE some_column = some_value;
10. GROUP BY – Group by is used in aggregate function such as count, sum, avg etc. It is used to group by values given in a group by column so that identical data into group.
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name;
11. ORDER BY – Order by is used with aggregate function. It is used to sort the result obtained either alphabetically or in a numerical ascending or descending order.
SELECT column_name
FROM table_name
ORDER BY column_name ASC | DESC;
12. HAVING- Having clause is used as a filter command. The data will be selected based on the conditions in HAVING Clause. We use this when we cannot use WHERE Clause.
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > value;
13. INNER JOIN – Inner join is used to combine two or more tables based on their common columns. INNER Join is a default join in SQL.
SELECT column_name(s)
FROM table_1
JOIN table_2
ON table_1.column_name = table_2.column_name;
14. OUTER JOIN – Outer join returns all rows from the left table and any matching rows from the right table. For none matching values NULL is filled.
SELECT column_name(s)
FROM table_1
LEFT JOIN table_2
ON table_1.column_name = table_2.column_name;
15. INSERT – Insert is used to add or insert row into a table. It should specify columns and values to be inserted.
INSERT INTO table_name (column_1, column_2, column_3)
VALUES (value_1, ‘value_2’, value_3);
16. IS NULL and IS NOT NULL- IS NULL and IS NOT NULL are used with the WHERE clause to check or test if columns are empty or not.
SELECT column_name(s)
FROM table_name
WHERE column_name IS NULL;
17. LIKE – Like is used as filter command. It is used in conjunction with WHERE clause. It is used to search for a pattern in column values.
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
18. LIMIT- Limit is a simple command sue with select statement where we want to limit the number of rows to display or show.
SELECT column_name(s)
FROM table_name
LIMIT (number);
19. MAX()- Max is an aggregate function that return the largest value in the column specified.
SELECT MAX(column_name)
FROM table_name;
20. MIN()- Min is an aggregate function that return the smallest value in the column specified.
SELECT MIN(column_name)
FROM table_name;
21. OR- Or is used to select one or another value in a column. If all values exist they are all displayed. exist both. The result include there either or all of the condition are true.
SELECT column_name
FROM table_name
WHERE column_name = value_1
OR column_name = value_2;
22. ROUND()- Round is an aggregate function where the values obtained are round to the desired decimal values.
SELECT ROUND(column_name, integer)
FROM table_name;
23. SELECT – Select statement is the back bone SQL command. Any command starts and use select clause to fetch values from rows and columns.
SELECT column_name
FROM table_name;
24. SELECT DISTINCT – SELECT DISTINCT is a variety and enhanced version of select where query returns only unique values in the specified column(s).
SELECT DISTINCT column_name
FROM table_name;
25. SUM – Sum is one of the aggregate function where the additive sum of the values from the specified column is returned as a single value.
SELECT SUM(column_name)
FROM table_name;
26. UPDATE – Update is used to alter values in a given column. If Where statement is used al values in the given column is updated with the value specified.
UPDATE table_name
SET some_column = some_value
WHERE some_column = some_value;
27. WHERE – WHERE is the most useful command where specific values in a column is specified. It is used as a filtering command given the condition set is true.
