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.

Example

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, vehicle having turbo manufactured by dodge company is listed above by using and operator.
This example, vehicle having turbo or vehicle is being produced by dodge are listed above by using or operator.
Between operator is used with numeric data and in above example between operator causes to cluster the horsepower between 160 and 170, keep in mind that written numbers are included the cluster. If you check it out, some of horsepower is 160.

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”

For these 2 examples, the column of symbolling is sorted order by function with asc and desc operators.

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);

This example is a little bit complex, aspiration column has two different types, like turbo and std. These two types of them is listed with the help of order by function. When it comes to order by in question example, I used asc operator to be sorted 1 to 3. If desc operator were to be used, the list would commence 3.

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);

In this example, I tried to demonstrate the vehicle types by using group by function and having is used as aggregate function. Namely, rows having more than 55 height is sorted with the help of having function. The other types of code block is select * from auto group by make having height between 50 and 60; What is the difference between them ? There is no difference, except above code block has between function.

NOT

The NOT operator displays a record if the condition(s) is NOT TRUE.

SELECT * FROM AUTO WHERE NOT ASPIRATION = ‘STD’;

In this example, aspiration have more than one value, like turbo, std and so on. The block of “not aspiration = ‘std’ “ is caused to eliminate in question values, which is ‘std’. Note: The other operators, — like “AND”, “OR” and “BETWEEN” -, were being mentioned above.

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’);

This is a decent example to learn both ‘IN’ and ‘AND’ operators. AS you can see that, ‘std’ and ‘turbo’ were selected from the aspiration column. In the second one, which is make, ‘toyota’ was selected. Moreover, these two columns were combined with the help of ‘AND’ operator. If ‘OR’ operator were to be used, rather than ‘AND’, the rows number would be more and more. This is the difference between them.

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%’;

In this example, we selected the rows in the make column getting started with the letter of “a”. Such as, alfa-romeo and audi.
This example is similar with the first one, the only difference is rows ending with the letter of “a”, such as HONDA.
In this example, the word being the second letter is “u” is listed. Such as, Audi and Subaru.
In the last example, we listed the word, the second letter is “u” and the last letter is ‘i’. Note: using of “%” is not necessary. If you do not use it, you can get the same result.

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;

AS you can see that, the rows being between 165 and 179 is filtered as OKAY, the other is classified as NOT OKAY. This statement likes if and else in the programming languages.

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;

REFERENCES

https://en.wikipedia.org/wiki/SQL

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
material data science

material data science

I am material engineer and I have been interested in data science, I have strived to combine between material and data science on this web site. Thank you.