Some Basic Queries:
Note: Note that SQL is not case sensitive meaning SELECT and select are the same things in SQL.
SELECT: Select keyword or statement is used to select whole data or particular column/ columns from the database.
Syntax: Select particular column/ columns from a table
SELECT column_name1, column_name2, ......
FROM table_name;
Select whole data means selecting the whole table
SELECT * FROM table_name;
Sometimes we need to select unique data from the table, not whole. For this, we can use the SELECT DISTINCT statement. SELECT DISTINCT returns only unique values from table.
SELECT DISTINCT column_name FROM table_name;
Operators and WHERE in SQL
We know how to select data from a database but what if we have to define the position or range of particular data that we want to select.
For example, if we have a salary column in a particular database and we want to select a salary greater than a particular value.
We know that to do it we have to put conditions for what we want. Let's see how to write a query for this.
SELECT column_name FROM table_name WHERE put_condition;
Different types of operators can be used to put conditions according to the problems.
| Operators |
Description |
| = |
Equal to |
| >, >= |
greater than , grater than equal to |
| <, <= |
Less than, Less than equal to |
| != |
Not equal |
| BETWEEN |
Between a certain range |
| IN |
Used to match multiple values in one column |
| AND |
Will display a record if all conditions are true |
| OR |
Will display a record if any condition is true |
| NOT |
When we don't want condition to be true/ != |
Display sorted records
In SQL, ORDER BY Keyword is used to display sorted records. You can sort two columns together. In sorting, the first column name that you mentioned first, will sorted first and then the second one.
By default ORDER BY Keyword sort in ascending order but if you want in descending you can use the DESC keyword.
SELECT * FROM table_name
ORDER BY column_name1, column_name2;
For Descending
SELECT * FROM table_name
ORDER BY column_name1, column_name2 DESC;
How to Check Null Values in Database ?
Sometimes we have missing values or null values in our database. To find out null values we use IS NULL Keyword and for not missing values IS NOT NULL.
SELECT * FROM table_name
WHERE column_name IS NULL;
For Not Missing Values
SELECT * FROM table_name
WHERE column_name IS NOT NULL;
Selecting Top Records
Selecting top records meaning, when we have large data we can write a query that returns a limited number of records (or top records).
SELECT TOP number
FROM table_name
WHERE condition;
Some Mathematical Operations (SUM(), COUNT(), AVG() etc...)
SELECT MIN/MAX(column_name)
FROM table_name
WHERE condition ; ( if required)
MIN()- Return smallest value of field (column)
MAX()- Return largest value of field (column)
SELECT AVG/COUNT/SUM(column_name)
FROM table_name
WHERE condition ; ( if required)
AVG()- Return average of column (use for numerical column)
COUNT()- Return number of data in a column (or number of rows in a column)
SUM() - Return sum of field (column)
SQL JOINS (Inner, left, right, full)
SQL join, combine two or more columns based on matching values of records/rows.
INNER JOIN or JOIN- Returns matching records of two or more tables.
SELECT table1.column1,table2.column2....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
LEFT JOIN- Select all records from left table and matched records from right table.
SELECT table1.column1,table2.column2...
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
RIGHT JOIN- Select all records from right table and matched records from left table.
SELECT table1.column1,table2.column2....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
FULL JOIN- Returns all matching records as well as non-matching records from both tables.
SELECT table1.column1,table2.column2....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
matching_column - matching column in both table.
Diagrams:
| 
| 
