SQL refers to Structured Query Language.
It is primarily used in Relational Database Management Systems including MS Access, Oracle, Postgres, MySQL and SQL Server.
We will be using MySQL.
- Creating a Database
CREATE DATABASE mydb;
- Using the created database mydb
- Creating a table
CREATE TABLE mytable ( id int unsigned NOT NULL auto_increment, username varchar(100) NOT NULL, email varchar(100) NOT NULL, PRIMARY KEY (id) );
- Inserting into table
INSERT INTO mytable ( username, email ) VALUES ( "myuser", "email@example.com" );
- Updating a row in the table
UPDATE mytable SET username="myuser" WHERE id=5
- Deleting a row in a table
DELETE FROM mytable WHERE id=5
- Select rows based on conditions.
SELECT * FROM mytable WHERE username = "myuser";
- returns all the columns in the table.
The power of SQL lies in its ability to use nested complex queries to provide the desired output.
Let us look at some commands that make SQL queries more robust.
These commands work similarly to how they are used as logical operators in programming languages.
They are combined with the WHERE clause to specify the conditions that must be met.
- AND returns only the records that meet all the conditions.
- OR returns a record if any of the conditions in the query are met.
- NOT displays a record if the conditions in the query are NOT TRUE.
IN returns the records based on the values supplied.
It is used together with the WHERE clause and acts as a shorthand for multiple OR conditions.
We can also use **NOT IN **to get records that are not in the specified values supplied to the clause.
BETWEEN is useful when extracting records in a given range. For instance between dates or prices.
Note: BETWEEN is inclusive; it includes the beginning and end values.
It is used with the WHERE clause and can be combined with AND,OR, NOT to specify results further.
TRUE if the subquery returns one or more records that satisfy the condition.
It returns TRUE if the operand matches the specified pattern.
This command is used to define the order in which the results of a query should be displayed.
The default is ascending order thus to display in descending order we specify using the DESC keyword.
Aggregate functions help us retrieve data by performing different operations on columns.
These functions are AVG,COUNT,MIN,MAX, SUM
- AVG returns the average of the selected values.
- COUNT counts how many rows meet the specified conditions
- MIN returns the lowest value in a particular column.
- MAX returns the highest value in a column.
- SUM adds all the values in a particular column. Here's and example of how we can use COUNT,BETWEEN and AND with WHERE clause.
SELECT COUNT(*) FROM sales.transactions WHERE order_date BETWEEN '2017-01-01' AND '2017-12-31';
This returns the number of transactions that were recorded between January and December 2017 from the transactions table.
The command is used to group rows that have the same values such that the query returns a single row for every grouped item.
GROUP BY can be used with aggregate functions as shown below.
SELECT COUNT(zone),zone FROM sales.markets GROUP BY(zone)
SELECT COUNT(zone) AS market_nums,zone FROM sales.markets GROUP BY(zone)
GROUP BY is also used with the HAVING clause which filters the record from the groups based on the specified condition.
The HAVING clause is used because the WHERE keyword cannot be used with aggregate functions.
SELECT COUNT(zone) AS market_nums,zone FROM sales.markets GROUP BY(zone) HAVING(count(zone)>3)
JOINs horizontally combine results from different tables.
A JOIN is used to combine rows from two or more tables, based on a related column between them.
There are several types of joins.
- INNER JOIN: Returns records that have matching values in both tables. Syntax:
SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name
SELECT markets.markets_name, transactions.product_code,transactions.sales_qty FROM sales.markets INNER JOIN sales.transactions ON markets.market_code = sales.transactions.market_code
- LEFT JOIN: Returns all records from the left table, and the matched records from the right table.
The "left" refers to the table that appears before the JOIN in the query and the "Right" refers to the table that is after the JOIN.
SELECT markets.markets_name, transactions.product_code,transactions.sales_qty FROM sales.markets LEFT JOIN sales.transactions ON markets.market_code = sales.transactions.market_code
- RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
- FULL JOIN: Returns all records when there is a match in either left or right table. Any row that does not have a match in both tables will have NULL entries for the missing values.
SQL UNION is used to vertically concatenate columns.
It combines the results of two or more
- The columns must also have similar data types.
UNIONmust have the same number of columns
- The columns in every
SELECTstatement must be in the same order.
SELECT *column_name(s)* FROM *table1* UNION SELECT *column_name(s)* FROM *table2*;
UNION operator selects only distinct values by default.
We use UNION ALL to include duplicate values:
SELECT *column_name(s)* FROM *table1* UNION ALL SELECT *column_name(s)* FROM *table2*;
Note: The column names in the result-set are usually equal to the column names in the first
Use UNION DISTINCT to drop duplicate values.