SQL | All you need to know as a beginner
- SELECT
The Most Used syntax of SQL is Select. Select statement used to select data from the database.
To select specific column use
Select column_name from Table_name
To select all from the table
Select * from Table_name
2. SELECT DISTINCT
Distinct means different. It removes duplicate values from the column.
If there is a table that contains the same country two times it will keep one and remove others.
SELECT DISTINCT Column from Table_names
3. GROUP BY
Group by works same as Select Distinct work.
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)
4. Select Distinct vs Group By
Both work with a number of return rows and remove the duplicates.
Group by mostly used with aggregate functions such as SUM, COUNT, AVG, MAX, MIN.
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
5. WHERE clause
To filter the search result where clause us mot sued one.
SELECT country FROM table_name WHERE name = ‘Germany’
6. AND, OR, and NOT Operators
AND, OR, and NOT operators make WHERE CLAUSE more specific to filter the values.
OR — If any one of the condition is true it will work
SELECT * FROM Customers WHERE City=’Berlin’ OR City=’München’;
AND — If all the conditions work result came true it will work
SELECT * FROM Customers WHERE Country=’Germany’ AND City=’Berlin’;
NOT — If the conditions are not true NOT operator will work.
SELECT * FROM Customers WHERE NOT Country=’Germany’;
All work together.
SELECT * FROM Customers WHERE Country=’Germany’ AND (City=’Berlin’ OR City=’München’);
6. ORDER BY
To sort the result set in ascending or Descending order we use ORDER BY.
The Default sorting order is Ascending. We can pass multiple column names for sorting.
It will check the first column and then the second and so on.
SELECT * FROM Customers ORDER BY Country, City CustomerName DESC;
7. INSERT INTO
This statement used to a new record in a table.
We can add data in two ways. First, one specifies the column name and then provide value,
INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);
The second one, If you want to add values in all columns, you don't have to add provide column name.
INSERT INTO table_name VALUES (value1, value2, value3, …)
Insert data in a specific column.
INSERT INTO Customers (Country) VALUES (‘Norway’);
8. NULL
To add a field without value we use NULL. Later you can update with the original value.
IS NULL: If any field value is empty and we need to check then we use IS NULL for an empty value.
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NOT NULL;
NOT NULL: To check the non-empty value on a field we use this condition.
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NOT NULL;
9. UPDATE
To replace/update the value of the existing column we use this statement.
UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;
10. DELETE
To delete a record from a table we use this statement.
To delete a row you need to provide conditions else all records will be deleted.
For Example.
To delete a single row
DELETE FROM Customers WHERE id=10;
To delete the entire record:
DELETE FROM table_name;
11. LIMIT
To return the specific number of records we use LIMIT.
SELECT column_name(s) FROM table_name LIMIT number;
12. MIN & MAX
- MIN returns the smallest value of the selected column.
- MAX returns the largest value of the selected column.
SELECT MIN(column_name) FROM table_name
SELECT MAX(column_name) FROM table_name
13. COUNT()
COUNT() return the total number of rows.
SELECT COUNT(column_name) FROM table_name
14. AVG()
AVG() return the average of the numeric value of the selected column.
SELECT AVG(column_name) FROM table_name
15. SUM()
SUM() return the sum of the numeric value of the selected column.
SELECT SUM(column_name) FROM table_name
Part 2 is coming soon.
