SQL Reminder / Main Knowledge

In this example create the following tables under MySQL trough PhpMyAdmin tool :

Create your columns to respect the following examples below.

Tables :

The table Store_Informartion (yes there is an error in the name) :

Type in your SQL terminal :

CREATE TABLE `Store_Informartion` (`Store_Name` VARCHAR(50), `Sales` INTEGER, `Txn_Date` DATE, `Region_Name` VARCHAR(50));

You have now created this table and no data row in it.

Now insert data in rows and type in your SQL terminal :

INSERT INTO `Store_Informartion_BIS` (`Store_Name`,`Sales`,`Txn_Date`,`Region_Name`) VALUES ('San Diego',250, '2017-01-07','WEST'), ('San Diego',250, '2017-01-07','WEST'), ('Los Angeles', 1500,  '2017-01-05','WEST'), ('Los Angeles', 1500, '2017-01-05','WEST'), ('Los Angeles', 300, '2017-01-08', 'WEST'), ('Boston', 700, '2017-01-08', 'EAST');

Result :

and the table Store_2 :

Type in your SQL terminal :

CREATE TABLE `Store_2` (`Store_Name` VARCHAR(50), `Region_NickName` VARCHAR(50));

You have now created this table and no data row in it.

Now insert the data in rows and type in your SQL terminal :

INSERT INTO `Store_2_BIS` (`Store_Name`,`Region_NickName`) VALUES ('Los Angeles','W1'), ('San Diego','W2'), ('Boston', 'E1'), ('New York', 'E2');

1 – SELECT :

Select all stores :

SELECT `Store_Name` FROM `Store_Informartion`;

Result :

2 – DISTINCT :

Select all different stores :

SELECT DISTINCT `Store_Name` FROM `Store_Informartion`;

Result :

3 – WHERE :

Select all stores where sells are greater than 1000 :

SELECT `Store_Name` FROM `Store_Informartion` WHERE `Sales` > 1000 ;

Result :

4 – AND / OR :

Select all stores where sells are greater than 1000 are less than 500 but greater to 275 :

SELECT `Store_Name` FROM `Store_Informartion` WHERE `Sales` > 1000 OR `Sales` < 500 AND `Sales` > 275 ;

Note : AND and OR can be used indeferently before or after we can change their order when using them. But we need to use () to express the order of the condition.

Result :

5 – IN :

Select all records from LOs Angeles Stores and San Diaego :

SELECT `Store_Name`, `Sales`, `Txn_Date` FROM `Store_Informartion` WHERE `Store_Name` IN ('Los Angeles', 'San Diego');

Note : This is equivalent to write the following query.

SELECT `Store_Name`, `Sales`, `Txn_Date` FROM `Store_Informartion` WHERE `Store_Name` ='Los Angeles' AND `Store_Name` = 'San Diego';

Result :

6 – BETWEEN :

Select the sells between 01-01-2017 and 01-10-2017 :

SELECT `Store_Name`, `Sales`, `Txn_Date` FROM `Store_Informartion` WHERE `Txn_Date` BETWEEN '2017-01-01' AND '2017-10-01';

Result :

7 – LIKE :

Select stores where the name get ‘AN’ characters :

SELECT `Store_Name`, `Sales`, `Txn_Date` FROM `Store_Informartion` WHERE `Store_Name` LIKE '%ANG%';

Result :

8 – ORDER BY :

Sort amount by ascending (=Select and sort all column and sort by ascending values).

Note : Sorting can be done ascending or descending but if not notified in the query, the default mode will be ascending.

SELECT `Store_Name`, `Sales`, `Txn_Date` FROM `Store_Informartion` ORDER BY `Sales` DESC;

Result :

9 – FUNCTIONS :

Note : There are in SQL arithmetics calculus like the following :

AVG
COUNT
MAX
MIN
SUM
+
–

You can also do simple operations like additions and substractions for number variables or concatenation for characters variables. Each database trademark has its own sets of functions available. On this point, you should have a look on their documentation.

a – AVG :

Select the average of all sells :

SELECT AVG(`Sales`) FROM `Store_Informartion`;

Result :

b – COUNT :

Count the number of sells (=the number of line in the table, because here a line = 1 sell).

SELECT COUNT(`Store_Name`) FROM `Store_Informartion`;

Result :

Note : We can use COUNT with DISTINCT to get the distinct records.

For example Count the number of distinct sells :

SELECT COUNT(DISTINCT(`Store_Name`)) FROM `Store_Informartion`;

Result :

c – MAX :

Select the maximum amount in sells :

SELECT MAX(`Sales`) FROM `Store_Informartion`;

Result :

d – MIN :

Select the minimum amount in sells :

SELECT MIN(`Sales`) FROM `Store_Informartion`;

Result :

e – SUM :

Select the sum of all sells :

SELECT SUM(`Sales`) FROM `Store_Informartion`;

Result :

f – Addition :

Select the sum of all sells and add it to the sum of all sells :

SELECT SUM(`Sales`) + SUM(`Sales`) FROM `Store_Informartion`;

Result :

g – Substraction :

Select the sum of all sells and substract it to the sum of all sells :

SELECT SUM(`Sales`) - SUM(`Sales`) FROM `Store_Informartion`;

Result :

h – Concatenate :

Note : The function allows to add characters to other characters.

Select Boston stores and concatenate Boston “Store_Name” with “Region_Name” :

SELECT CONCAT(`Region_Name`,`Store_Name`) FROM `Store_Informartion` WHERE `Store_Name` = 'Boston';

Result :

i – Substring :

Note : The function allows to select characters parts in characters variable giving 3 arguments like the following :

SUBSTR('Name_of_the_variable',  'the_position_where_you_want_to_start', 'the_length_of_characters_you_want_to_get')

Select ‘Los Angeles’ record lines and get the 3 last characters :

SELECT SUBSTR(`Store_Name`,9,3) FROM `Store_Informartion` WHERE `Store_Name` = 'Los Angeles';

Result :

j – Trim / LTrim / RTrim :

Note : The function allows to cut blank space in characters chain respectively on both sides of the chain, on the left side and on the rigth side.

We use the precedent result and modify it to fit our example using LTRIM function :

SELECT LTRIM(SUBSTR(`Store_Name`,4,8)) FROM `Store_Informartion` WHERE `Store_Name` = 'Los Angeles';

Result :

10 – GROUP BY :

Select the sum of all sells for each store :

SELECT `Store_Name`, SUM(`Sales`), `Txn_Date` FROM `Store_Informartion` GROUP BY `Store_Name`;

Result :

11 – HAVING :

Note : HAVING is only used with aggregation function like SUM() for example. It an equivalent of WHERE().

Example :

Select the sum of all sells for each store greater than 1500 :

SELECT `Store_Name`, SUM(`Sales`), `Txn_Date` FROM `Store_Informartion` GROUP BY `Store_Name` HAVING SUM(`Sales`) > 1500;

Result :

12 – ALIAS :

Note : Alias are used to replace uggly or complex column names or names you simply want to change.

example of use : select all sells by store and rename the column Store_Name “Store” and the column where the function SUM() is applied “Total_Sales”.

SELECT `A1`.`Store_Name` `Store`, SUM(`A1`.`Sales`) `Total Sales` FROM `Store_Informartion` `A1` GROUP BY `A1`.`Store_Name`;

Result :

13 – JOINS :

INNER JOIN: Returns all rows when there is at least one match in BOTH tables
LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
FULL JOIN: Return all rows when there is a match in ONE of the tables.

a – INNER JOIN :

Select the variables Store_Name (coming from the first table you want to add information to with table “Store_Informartion”), Sales, Txn_Date, Store_Name (coming from the second table to join “Store_2”), Region_NickName, Region_Name

SELECT `Store_Informartion`.`Store_Name`, `Store_Informartion`.`Sales`, `Store_Informartion`.`Txn_Date`, `Store_2`.`Store_Name`, `Store_2`.`Region_NickName`, `Store_Informartion`.`Region_Name` FROM `Store_Informartion` INNER JOIN `Store_2` ON `Store_Informartion`.`Store_Name`=`Store_2`.`Store_Name`;

Result :

b – LEFT JOIN :

Note : The returned table looks the same as in INNER JOIN example, but the result is a little different because with LEFT JOIN you want all rows from first table and rows from second table when keys are equal. It means some rows of the second table might not be in the result table if keys are not matched. If you had a line for ‘Washington’ in table 1 for example, you will see this row in the result table but with other variable ‘NULL’.

SELECT `Store_Informartion`.`Store_Name`, `Store_Informartion`.`Sales`, `Store_Informartion`.`Txn_Date`, `Store_2`.`Store_Name`, `Store_2`.`Region_NickName`, `Store_Informartion`.`Region_Name` FROM `Store_Informartion` LEFT JOIN `Store_2` ON `Store_Informartion`.`Store_Name`=`Store_2`.`Store_Name`;

Result :

c – RIGHT JOIN :

Note : The returned table is different because with RIGHT JOIN you want all rows from second table and rows from first table when keys are equal. It means some rows of the first table might not be in the result table if keys are not matched. If you had a line for ‘Houston’ in table 1 for example, you will not have this row in the result table.

SELECT `Store_Informartion`.`Store_Name`, `Store_Informartion`.`Sales`, `Store_Informartion`.`Txn_Date`, `Store_2`.`Store_Name`, `Store_2`.`Region_NickName`, `Store_Informartion`.`Region_Name` FROM `Store_Informartion` RIGHT JOIN `Store_2` ON `Store_Informartion`.`Store_Name`=`Store_2`.`Store_Name`;

Result :

14 – CONSTRAINT :

Most used constraints are :

NOT NULL
UNIQUE
CHECK
PRIMARY KEY
FOREIGN KEY

a – NOT NULL :

You don’t want for your variable a NULL value.

example :

CREATE TABLE `Store_Informartion` (`Store_Name` VARCHAR(50) NOT NULL, `Sales` INTEGER NOT NULL, `Txn_Date` DATE, `Region_Name` VARCHAR(50));

b – UNIQUE :

You use it to have the garanty of not have duplicates value.

For example let say you don’t want duplicates with “Sales” variable.

Note : A primary key that has no duplicate, the variable could have been a primary key.

CREATE TABLE `Store_Informartion` (`Store_Name` VARCHAR(50) NOT NULL, `Sales` INTEGER NOT NULL UNIQUE, `Txn_Date` DATE, `Region_Name` VARCHAR(50));

c – CHECK :

This is used when you want that values of a column respect a specified condition. For example let say you only accept that “Sales” values to be with a positive sign.

CREATE TABLE `Store_Informartion` (`Store_Name` VARCHAR(50), `Sales` INTEGER CHECK(`Sales`>0), `Txn_Date` DATE, `Region_Name` VARCHAR(50));

d – PRIMARY KEY :

Primary key is used when you want to identify each row of a table. If we have added an “ID” variable we could have the folowing query :

CREATE TABLE `Store_Informartion_4` (`ID` INTEGER, PRIMARY KEY (ID), `Store_Name` VARCHAR(50), `Sales` INTEGER CHECK(`Sales`>0), `Txn_Date` DATE, `Region_Name` VARCHAR(50));

you can also do

CREATE TABLE `Store_Informartion_5` (`ID` INTEGER PRIMARY KEY, `Store_Name` VARCHAR(50), `Sales` INTEGER CHECK(`Sales`>0), `Txn_Date` DATE, `Region_Name` VARCHAR(50));

Note : Both queries work on MySQL phpMyAdmin tool. Also there’s a specific use when you modify a table that didn’t have a primary key before, you must see if there is no ‘NULL’ value and define to ‘NOT NULL’ the variable you have choosen to be a primary key. You will do this :

ALTER TABLE  `Store_Informartion` ADD PRIMARY KEY (`ID`);

e – FOREIGN KEY :

They foreign key is for a variable linked to a primary key of an other table. Let say you have in the table “Store_2” with “ID” (primarykey) and “S_ID” (foreignkey)

CREATE TABLE `Store_Informartion_4` (`ID` INTEGER, PRIMARY KEY (`ID`),`S_ID` INTEGER, FOREIGN KEY(`SID`) REFERENCES CUSTOMER(`SID`), `Store_Name` VARCHAR(50), `Sales` INTEGER CHECK(`Sales`>0), `Txn_Date` DATE, `Region_Name` VARCHAR(50));

Note : There’s also a specific use when you modify a table with ALTER TABLE.

ALTER TABLE  `Store_2` ADD FOREIGN KEY (`S_ID`) REFERENCE `Store_Informartion` (`S_ID`);

15 – CREATE VIEW :

A view is the same thing as a table but it do not have any data on the hard drive. For example irf we build on previous query you have :

CREATE VIEW `TEST` AS SELECT  `Store_Informartion`.`Store_Name`, `Store_Informartion`.`Sales`, `Store_Informartion`.`Txn_Date`,  `Store_2`.`Region_NickName`, `Store_Informartion`.`Region_Name` FROM `Store_Informartion` INNER JOIN `Store_2` ON `Store_Informartion`.`Store_Name`=`Store_2`.`Store_Name`;

16 – CREATE INDEX :

Index are useful when you want to know the configuration of a table. Still working from previous queries example we can have :

CREATE INDEX `TEST_4` ON `Store_Informartion`(`Sales`);

17 – ALTER TABLE :

This command is to modify the structure in any table. For example there are main cases

add a “TEST_COLUMN” like that :

ALTER TABLE  `Store_Informartion` ADD `TEST_COLUMN` VARCHAR(1);

Result :

rename a column “TEST_COLUMN” in “TEST_C” :

ALTER TABLE  `Store_Informartion` CHANGE `TEST_COLUMN` `TEST_C` VARCHAR(1);

to modify the type of data :

ALTER TABLE  `Store_Informartion` MODIFY `TEST_C` VARCHAR(50);

to delete the “COLUMN_C” :

ALTER TABLE  `Store_Informartion` DROP `TEST_C`;

Result :

18 – DROP TABLE :

You use it to delete table(s) from your database. If you have a table “TEST” that you want to delete, you will write it like that :

DROP TABLE `TEST`;

19 – TRUNCATE TABLE :

You use this command when you need to delete rows in a table (you will have no record but you will keep an empty table). If you have a table “TEST” will full of data rows, you will write your queries like that :

TRUNCATE TABLE `TEST`;

And now your table is empty.

20 – DELETE FROM :

Use this command when you want to delete records (rows / line) from your table “TEST”.

DELETE FROM `TEST` WHERE `A`=45;

Result :

21 – UNION :

Use UNION when you want to get results of two queries. Beware each similar variable must have the type of data. At last, this command get distinct result not similar ones.

For example, select all distinctthee (different) cities where there was sells, you will use this instruction :

SELECT `Store_Name` FROM `Store_2`
UNION
SELECT `Store_Name` FROM `Store_Informartion`;

Result :

22 – UNION ALL :

You use UNION ALL to get results from both tables you use. The difference with UNION is that instead of selecting all distinct results from both used tables you will get all the results from those tables.

For example using the precedent UNION command; you select all stores where there was sells.

SELECT `Store_Name` FROM `Store_2`
UNION ALL
SELECT `Store_Name` FROM `Store_Informartion`;

Results :

23 – INTERSECT :

The use of INTERSECT is different from UNION. In UNION you select a data on the condition that if it’s in your first table or in your second table the record will be inclueded in you result table. Here with INTERSECT you select data if it’s in both table. UNION is “OR” condition and INTERSECT is more like “AND” condition. It’s like in combinatory probability you get a result of the intersection of table 1 and table 2.

For example select all the cities that were in both type of store Store_2 and Store_Informartion.

If you have one field with the same data type and that your database support the INTERSECT operator you will have this command line :

SELECT `Store_Name` FROM `Store_Informartion`
INTERSECT
SELECT `Store_Name` FROM `Store_2`;

But as i am working with MySQL and if you also work with it, you’ll have to do use IN operator instead to simulate INTERSECT query.

SELECT `Store_Informartion`.`Store_Name` FROM `Store_Informartion` WHERE `Store_Informartion`.`Store_Name` IN (SELECT `Store_2`.`Store_Name` FROM `Store_2`);

Result :

You can study more in depth examples here.

24 – MINUS :

MINUS command take the results of the first query then substracte those from the second query to get the final results. If results in the second query are not included in the first one they will be ignorded. See a sketch about the two datasets.

The MINUS command only work in Oracle databases. For SQL Server, PostgreSQL and SQLite you have to use EXCEPT.

SELECT `Store_Informartion`.`Store_Name`, `Store_2`.`Store_Name` FROM `Store_Informartion`
LEFT JOIN `Store_2` ON `Store_Informartion`.`Store_Name` = `Store_2`.`Store_Name`WHERE `Store_2`.`Store_Name` IS NULL;

25 – Sub Query :

You use sub queries when if you have used WHERE or HAVING command in your query. There is two types of query, the simple ones and the correlate ones.

You have for example with the simple query, select all sells from all stores in the west region.

SELECT SUM(`Store_Informartion`.`Sales`) FROM `Store_Informartion` WHERE `Store_Informartion`.`Store_Name` IN (SELECT `Store_2`.`Store_Name` FROM `Store_2` WHERE `Store_2`.`Region_NickName` LIKE '%W%');

Result :

You have for example with the correlate query, select all sells from all stores.

SELECT SUM(`Store_Informartion`.`Sales`) FROM `Store_Informartion` WHERE `Store_Informartion`.`Store_Name` IN (SELECT `Store_2`.`Store_Name` FROM `Store_2` WHERE `Store_2`.`Store_Name`=`Store_Informartion`.`Store_Name` );

Result :

26 – EXISTS :

You can use instead of the correlate query the following query with EXISTS command. you will have :

SELECT SUM(`Store_Informartion`.`Sales`) FROM `Store_Informartion` WHERE EXISTS(SELECT `Store_2`.`Store_Name` FROM `Store_2` WHERE `Store_2`.`Store_Name` LIKE '%W%' );

Result :

Sources :

http://www.w3schools.com/sql/sql_join.asp
http://www.1keydata.com/fr/sql/sql-jointure.php (in french) (different method about the JOIN command).
See also advanced SQL command.
https://www.techonthenet.com/mysql/intersect.php
http://www.gokhanatil.com/2010/10/minus-and-intersect-in-mysql.html

SQL Reminder / Main Knowledge

Leave a Reply Cancel reply