Mar 5
SQL: Zero to Hero
What is the SQL ?
SQL (Structured Query Language) is a domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS)
SQL Basics
SELECT
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.
SELECT DISTINCT * FROM TABLE_NAME;
OR
SELECT DISTINCT COLUMN1, COLUMN2, ….. FROM TABLE_NAME;
SELECT WITH NO DISTINCT
SELECT * FROM TABLE_NAME;
OR
SELECT COLUMN1, COLUMN2, …. FROM TABLE_NAME;
NOTE: The letter of “ * “ means to select all of columns and the code block in SQL must be ended with the letter of “ ; “.
NOTE2: You can write your code in upper and/or lower case.
WHERE
WHERE clause, which is extract only those records that fulfill a specified condition, is a filter records.
SELECT * FROM TABLE_NAME
WHERE CONDITION;
As you can see that, we can select multiple columns with the help of and operator, and the other operators listed below.
=
Equal
>
Greater than
<
Less than
>=
Greater than or equal
<=
Less than or equal
<>
Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN
Between a certain range
LIKE
Search for a pattern
IN
To specify multiple possible values for a column
ORDER BY
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
It is used to sort the result-set in ascending or descending order. In order to descending order , “DESC” and to be ascend “ASC”
GROUP BY
The statement groups rows that have the same values into summary rows, like “find the number of customers in each country”.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
HAVING
The HAVING clause was added to SQL because the WHERE keyword cannot be used with aggregate functions.
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
NOT
The NOT operator displays a record if the condition(s) is NOT TRUE.
SELECT * FROM AUTO WHERE NOT ASPIRATION = ‘STD’;
IN
The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);
select * from auto where aspiration in (‘std’, ‘turbo’) and make in (‘toyota’);
LIKE
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
- The percent sign (%) represents zero, one, or multiple characters
- The underscore sign (_) represents one, single character
The percent sign and the underscore can also be used in combinations!
select * from auto where make like ‘a%’;
select * from auto where make like ‘%a’;
select * from auto where make like ‘_u%’;
select * from auto where make like ‘_u_i%’;
CASE
The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
IS NULL
The IS NULL operator is used to test for empty values (NULL values).
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
IS NOT NULL
SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
LIMIT
The LIMIT clause is used to specify the number of records to return.
The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
MIN
SELECT MIN(column_name)
FROM table_name
WHERE condition;
MAX
SELECT MAX(column_name)
FROM table_name
WHERE condition;
COUNT
The COUNT() function returns the number of rows that matches a specified criterion.
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
AVG
The AVG() function returns the average value of a numeric column.
SELECT AVG(column_name)
FROM table_name
WHERE condition;
SUM
The SUM() function returns the total sum of a numeric column.
SELECT SUM(column_name)
FROM table_name
WHERE condition;
INSERT INTO
There is two ways to use it.
1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, …)
VALUES (value1, value2, value3, …);
2. If you are adding values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, …);
INSERT INTO SELECT
This statement copies data from one table and inserts it into another table and it requires that the data types in source and target tables matches.
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
OR
INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;
NULL
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.
EXIST
The EXISTS operator ,- which returns TRUE if the subquery returns one or more records -, is used to test for the existence of any record in a subquery.
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
UPDATE and DELETE
The UPDATE statement is used to modify the existing records in a table.
UPDATE table_name
SET column1 = value1, column2 = value2, …
WHERE condition;
Note: If you do not use where clause, all records will be updated.
DELETE FROM table_name WHERE condition;
Note: If you do not use where clause, all records will be deleted.
ALIASES
SELECT column_name AS alias_name
FROM table_name;
JOINS
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
- INNER JOIN: Returns records that have matching values in both tables
- LEFT JOIN: Returns all records from the left table, and the matched records from the right table
- RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
- CROSS JOIN: Returns all records from both tables
Inner Joins
The INNER JOIN keyword selects records that have matching values in both tables.
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the "Orders" table that do not have matches in "Customers", these orders will not be shown!
Left Join
The LEFT JOIN keyword returns all records from the left table (table1), and the matching records (if any) from the right table.
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Right Join
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records (if any) from the left table.
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Cross Join
The CROSS JOIN keyword returns all records from both tables.
SELECT column_name(s)
FROM table1
CROSS JOIN table2;
Note: CROSS JOIN can potentially return very large result-sets!
Self Join
A self join is a regular join, but the table is joined with itself.
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
