Showing posts with label SQL. Show all posts

Using SQL JOINS

No comments
SQL JOINS are used to join the data from different tables which are related to each other with the help of some common data in their columns. For example we can have a ORDERS table which will have details abut the order made by some customer from CUSTOMER table and the common column between these two tables could be the CUSTOMER ID and with the help of this common column CUSTOMER ID we can easily get the whole details about the ORDERS.
+----+--------------+-------+---------------+------+
| ID | ITEM_ORDERED | PRICE | PURCHASE_DATE | C_ID |
+----+--------------+-------+---------------+------+
|  1 | APPLE WATCH  | 35000 | 2018-05-15    |    1 |
|  2 | IPHONE       | 55000 | 2018-05-25    |    2 |
|  3 | IPHONE       | 55000 | 2018-05-25    |    5 |
|  4 | IPHONE       | 55000 | 2018-05-25    |    3 |
|  5 | MACBOOK      | 59000 | 2018-05-25    |    4 |
|  6 | MACBOOK      | 59000 | 2018-05-25    |    1 |
|  7 | MACBOOK      | 59000 | 2018-05-25    |    6 |
+----+--------------+-------+---------------+------+
7 rows in set (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+----+--------+---------+------+-----------+---------------------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   | CREATION_DATE       |
+----+--------+---------+------+-----------+---------------------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     | 2018-05-23 00:16:35 |
|  2 | JONTY  | RHODES  |   40 | CHINA     | 2018-05-18 12:21:12 |
|  3 | JOHNY  | DEPP    |   45 | INDIA     | 2018-05-23 00:16:35 |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA | 2018-05-18 12:21:12 |
|  5 | JESSIE | JACKSON |   22 | CANADA    | 2018-05-23 00:16:35 |
|  6 | NICOLE | KIDMAN  |   50 | USA       | 2018-05-18 12:21:12 |
+----+--------+---------+------+-----------+---------------------+
6 rows in set (0.00 sec)
Now from above two tables we cannot get much information about the purchase unless we see both the tables together ans try to consolidate the data. So in order to get data from both these tables based on some common data we use SQL JOINS

SQL JOINS syntax with example -

Suppose we want to get the details of orders made by customers we can use the following SQL statement -
 SELECT C.*, O.* FROM CUSTOMER AS C INNER JOIN ORDERS AS O ON  C.ID = O.C_ID;
+----+--------+---------+------+-----------+---------------------+----+--------------+-------+---------------+------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   | CREATION_DATE       | ID | ITEM_ORDERED | PRICE | PURCHASE_DATE | C_ID |
+----+--------+---------+------+-----------+---------------------+----+--------------+-------+---------------+------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     | 2018-05-23 00:16:35 |  1 | APPLE WATCH  | 35000 | 2018-05-15    |    1 |
|  1 | AKSHAY | KUMAR   |   12 | INDIA     | 2018-05-23 00:16:35 |  6 | MACBOOK      | 59000 | 2018-05-25    |    1 |
|  2 | JONTY  | RHODES  |   40 | CHINA     | 2018-05-18 12:21:12 |  2 | IPHONE       | 55000 | 2018-05-25    |    2 |
|  3 | JOHNY  | DEPP    |   45 | INDIA     | 2018-05-23 00:16:35 |  4 | IPHONE       | 55000 | 2018-05-25    |    3 |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA | 2018-05-18 12:21:12 |  5 | MACBOOK      | 59000 | 2018-05-25    |    4 |
|  5 | JESSIE | JACKSON |   22 | CANADA    | 2018-05-23 00:16:35 |  3 | IPHONE       | 55000 | 2018-05-25    |    5 |
|  6 | NICOLE | KIDMAN  |   50 | USA       | 2018-05-18 12:21:12 |  7 | MACBOOK      | 59000 | 2018-05-25    |    6 |
+----+--------+---------+------+-----------+---------------------+----+--------------+-------+---------------+------+
7 rows in set (0.06 sec)

Here we can see that we have merged the rows of both CUSTOMERS and ORDERS tables and got the details of ORDERS made by each customer in a single table form.
You can notice that in the above example we have used INNER join, similarly there some other kind of joins that are used to combine the tables data in different scenarios.

SQL type of JOINS and their usage -

MySQl supports the following joins -
1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. CROSS JOIN
MySQl does not support OUTER JOIN. We will learn about these joins and their usages in next tutorials.


Using SQL ALIAS

No comments
SQL ALIAS is used to give a column or table some temporary name which makes these names more readable. Aliases are very helpful in the cases where we use more that one table name in the query.
mysql> SELECT * FROM CUSTOMER;
+----+--------+---------+------+-----------+---------------------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   | CREATION_DATE       |
+----+--------+---------+------+-----------+---------------------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     | 2018-05-23 00:16:35 |
|  2 | JONTY  | RHODES  |   40 | CHINA     | 2018-05-18 12:21:12 |
|  3 | JOHNY  | DEPP    |   45 | INDIA     | 2018-05-23 00:16:35 |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA | 2018-05-18 12:21:12 |
|  5 | JESSIE | JACKSON |   22 | CANADA    | 2018-05-23 00:16:35 |
|  6 | NICOLE | KIDMAN  |   50 | USA       | 2018-05-18 12:21:12 |
+----+--------+---------+------+-----------+---------------------+
6 rows in set (0.00 sec)

SQL ALIAS syntax for columns names and example -

 SELECT FNAME AS FIRST_NAME, LNAME AS LAST_NAME FROM CUSTOMER;
mysql> SELECT FNAME AS FIRST
+------------+-----------+
| FIRST_NAME | LAST_NAME |
+------------+-----------+
| AKSHAY     | KUMAR     |
| JONTY      | RHODES    |
| JOHNY      | DEPP      |
| JESSIE     | PINKMAN   |
| JESSIE     | JACKSON   |
| NICOLE     | KIDMAN    |
+------------+-----------+
6 rows in set (0.00 sec)
Using above query we have named FNAME column as FIRST_NAME and LNAME column as LAST_NAME. These names are temporary and exists only for the duration of the query.

Using MySQl ALIAS names separated with spaces -

To use space separated ALIAS names like FIRST NAME we need to use quotes around the alias name.
 SELECT FNAME AS 'FIRST NAME', LNAME AS 'LAST NAME' FROM CUSTOMER;
+------------+-----------+
| FIRST NAME | LAST NAME |
+------------+-----------+
| AKSHAY     | KUMAR     |
| JONTY      | RHODES    |
| JOHNY      | DEPP      |
| JESSIE     | PINKMAN   |
| JESSIE     | JACKSON   |
| NICOLE     | KIDMAN    |
+------------+-----------+
6 rows in set (0.00 sec)

SQL ALIAS from table names syntax and example -

  SELECT C.FNAME, C.AGE FROM CUSTOMER AS C;
 +--------+------+
| FNAME  | AGE  |
+--------+------+
| AKSHAY |   12 |
| JONTY  |   40 |
| JOHNY  |   45 |
| JESSIE |   25 |
| JESSIE |   22 |
| NICOLE |   50 |
+--------+------+
6 rows in set (0.00 sec)
In above example you won't see much use of using ALIAS but it would be very helpful when we will use more than one table in a single query which we will see in our next few tutorials specially while using JOINS.


Using SQL IN operator

No comments
SQL IN operator is used to specify multiple values in WHERE clause. Instead of using multiple OR operators we can use IN operator.

mysql> SELECT * FROM CUSTOMER;
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | USA       |
+----+--------+---------+------+-----------+
6 rows in set (0.00 sec)

SQL IN operator syntax with example -

Suppose we want to get the rows where country is INDIA, USA and CANADA, we can use it in two ways -
1. Using multiple OR with WHERE clause
SELECT * FROM CUSTOMER WHERE COUNTRY = 'INDIA' OR COUNTRY = 'USA' OR COUNTRY = 'CANADA';
1. Using IN operator with WHERE clause
SELECT * FROM CUSTOMER WHERE COUNTRY IN ('INDIA', 'USA', 'CANADA');
And we will get the same results, but IN operator is a little handy in such scenarios.
+----+--------+---------+------+---------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY |
+----+--------+---------+------+---------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA   |
|  3 | JOHNY  | DEPP    |   45 | INDIA   |
|  5 | JESSIE | JACKSON |   22 | CANADA  |
|  6 | NICOLE | KIDMAN  |   50 | USA     |
+----+--------+---------+------+---------+
4 rows in set (0.00 sec)
Also remember that using IN operator we can set restriction only for one column with one IN for setting restrictions on some other columns we will have to use OR operator again.

Using SQL LIKE operator

No comments
SQL LIKE operator is used for pattern matching with WHERE clause. For example if we want to get all the records of customers with names starting with J, we can use this LIKE operator with where clause.
This LIKE operator is used with some WILDCARDS.

% - (Percentage sign)It is used to represent zero, one or multiple characters.

_ - (underscore) It is used to represent single character.

SQL LIKE operator syntax and example -

Suppose we want to get the rows with FNAME starting with 'J', we can use the following statement -
 SELECT * FROM CUSTOMER WHERE FNAME LIKE 'J%';
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
+----+--------+---------+------+-----------+
4 rows in set (0.00 sec)
Similarly we use LIKE operator with these wildcards in different ways.

Using SQL LIMIT in MySql

No comments
LIMIT is used to specify the number of rows we want to get in the result set using SELECT statement. Most of the times we don't want to get all the records from the table but a specific number of rows, in that case we can use LIMIT.
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | USA       |
+----+--------+---------+------+-----------+
6 rows in set (0.00 sec)

MySQL LIMIT syntax and example -

Suppose from the above table we just want to get the top 4 results, we can use the following given statement -

SELECT * FROM CUSTOMER LIMIT 4;
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
+----+--------+---------+------+-----------+
4 rows in set (0.00 sec)
We can see that we have got just first 4 rows for the given query.

MySQL LIMIT with offset value syntax and example -

Suppose we don't want to get top some values but we want to get 3 values from second row, we can set offset 1 and then we will get values from 2nd row.
SELECT * FROM CUSTOMER LIMIT 1,3;
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
+----+--------+---------+------+-----------+
3 rows in set (0.00 sec)
Here we can see that we have got 3 values starting from 2nd row and we have left 1 row from the top.

Using SQL DELETE statement to delete a row

No comments
SQL DELETE statement is used to delete row (record) from the table. Usually we use WHERE clause with DELETE to specify the row which we want to delete.
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | USA       |
|  7 | GEORGE | CLOONEY | NULL | USA       |
+----+--------+---------+------+-----------+
7 rows in set (0.08 sec)

SQL DELETE statement syntax and example in MySql

Suppose we want to delete the row with ID 7 from the above table, use the following SQL statement -

DELETE FROM CUSTOMER WHERE ID = 7;
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | USA       |
+----+--------+---------+------+-----------+
6 rows in set (0.00 sec)
Now we can see that row with ID 7 has been deleted.

DELETE all table rows SQL

We need to be careful while using DELETE statement , because we we don't use the WHERE clause it will delete all the rows from the table. See the statement below.
-- Warning ! below statement will delete all the rows from table CUSTOMER
DELETE FROM CUSTOMER;

Using SQL UPDATE statement

No comments
SQL UPDATE statement is another very important statement used to update the value of an existing record in the database table.
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | CANADA    |
|  7 | GEORGE | CLOONEY | NULL | USA       |
+----+--------+---------+------+-----------+

SQL UPDATE statement syntax and example -

Suppose we want to change the country name for CUSTOMER NICOLE (ID - 6), we will write the following statement -
UPDATE CUSTOMER SET COUNTRY = 'USA' WHERE ID = 6;
mysql> SELECT * FROM CUSTOMER;
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | USA       |
|  7 | GEORGE | CLOONEY | NULL | USA       |
+----+--------+---------+------+-----------+
Here we can see that country column value has been updated to USA. Most of the time UPDATE statement is used with where clause to specify the row for which we want to update the record.

Note : If you will not use where clause you will end up updating all the records in the table.

-- Warning ! All records will get updated
UPDATE CUSTOMER SET COUNTRY = 'USA';


SQL NULL values and how to check them

No comments
In Sql NULL value means no value at all. If any column which is optional in database is inserted or updated with no value then we can say that that filed has NULL value. NULL value is different from 0 or space.
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | CANADA    |
|  7 | GEORGE | CLOONEY | NULL | USA       |
+----+--------+---------+------+-----------+
7 rows in set (0.00 sec)

Checking for NULL values in MySql - IS NULL and IS NOT NULL

NULL values cannot be checked with comparison operators with where clause.
IS NULL syntax and example in MySql -
Suppose we want to get the result set where AGE is null, we will use the following syntax.
 SELECT * FROM CUSTOMER WHERE AGE IS NULL;
+----+--------+---------+------+---------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY |
+----+--------+---------+------+---------+
|  7 | GEORGE | CLOONEY | NULL | USA     |
+----+--------+---------+------+---------+
1 row in set (0.00 sec)
IS NOT NULL syntax and example in MySql -
Suppose we want to get the result set where AGE is not null, we will use the following syntax.
 SELECT * FROM CUSTOMER WHERE AGE IS NOT NULL;
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | CANADA    |
+----+--------+---------+------+-----------+
6 rows in set (0.00 sec)

Using SQL INSERT INTO statement for inserting table values

No comments
SQL INSERT INTO statement is used to insert the values in the database tables. Table values can be inserted into all the columns of the table or we can specify some columns where for which we want to insert the values.

+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
+----+--------+---------+------+-----------+

SQL INSERT INTO syntax and example for inserting the values in all the columns in table -

 INSERT INTO CUSTOMER VALUES (6,'NICOLE', 'KIDMAN', 50, 'CANADA');

+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | CANADA    |
+----+--------+---------+------+-----------+
6 rows in set (0.00 sec)
Using the above approach we cannot have to define some value to each column and we cannot leave anycolumn.

SQL INSERT INTO syntax and example for inserting the values in some specified columns in table -

Here we can specify the columns and their corresponding values in which we want to insert the values -

  INSERT INTO CUSTOMER (FNAME, LNAME, COUNTRY) VALUES ('GEORGE', 'CLOONEY', 'USA');

mysql> SELECT * FROM CUSTOMER;
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | CANADA    |
|  7 | GEORGE | CLOONEY | NULL | USA       |
+----+--------+---------+------+-----------+
7 rows in set (0.00 sec)
Here we have inserted values in FNAME, LNAME and COUNTRY columns only. Age column is NULL. ID column got updated automatically because we have defined it as primary key in our table , we will learn about primary keys in next few tutorials. Note : order of column names names and values must be same.

Using SQL ORDER BY keyword for sorting result set in MYSQL

No comments
ORDER BY keyword is used for sorting purpose of the result set in MySQl. Using order by we can specify the sorting order for the result set based on column values. By default sorting order is ascending but we can use DESC if we want to sort the result set in descending order.

SQL syntax and example for using ORDER BY keyword in MySql -

 SELECT * FROM CUSTOMER ORDER BY AGE;

 +----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
+----+--------+---------+------+-----------+
5 rows in set (0.02 sec)
Above result set is in ascending order of AGE because we have not specified any order.
We can get the result set in descending order also by specifying DESC keyword.

  SELECT * FROM CUSTOMER ORDER BY AGE DESC;

 +----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
+----+--------+---------+------+-----------+
5 rows in set (0.00 sec)
Now the given result set is in descending order of age of the customers.

Using SQL AND, OR, NOT operator with WHERE clause

No comments
AND, OR, NOT operators are also can be used with WHERE clause to specify conditions.
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
+----+--------+---------+------+-----------+

SQL WHERE clause with AND operator

If we want to use more than one condition then we use AND operator with WHERE clause. If all these conditions are true only then we will get the result set if even one of these conditions is false , we will not get the result set.

SQL WHERE clause syntax and example with AND operator-

Suppose we want to customers from the CUSTOMER table who are from INDIA and age is less than 40.\
SELECT * FROM CUSTOMER WHERE COUNTRY = 'INDIA' AND AGE < 40;
+----+--------+-------+------+---------+
| ID | FNAME  | LNAME | AGE  | COUNTRY |
+----+--------+-------+------+---------+
|  1 | AKSHAY | KUMAR |   12 | INDIA   |
+----+--------+-------+------+---------+
1 row in set (0.00 sec)

SQL WHERE clause with OR operator

If we want to use more than one condition and want at least one of these conditions to be true, we should use OR operator. We will get the result if at least one of the given conditions separated by OR is true.

SQL WHERE clause syntax and example with OR operator-

Suppose we want to get CUSTOMERS wither from INDIA or AUSTRALIE, we will use the following syntax-
SELECT * FROM CUSTOMER WHERE COUNTRY = 'INDIA' OR COUNTRY = 'AUSTRALIA';
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
+----+--------+---------+------+-----------+
3 rows in set (0.00 sec)
We have got all the customers with COUNTRY INDIA and AUSTRALIA.

SQL WHERE clause with NOT operator

NOT operator is used to specify the condition which we want to avoid or if we want results for which some specific condition is not true we should use NOT operator.

SQL WHERE clause syntax and example with NOT operator-

Suppose we want all the customers except the customers where COUNTRY is CHINA, we will specify this condition with NOT operator.
SELECT * FROM CUSTOMER WHERE NOT COUNTRY = 'CHINA';
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
+----+--------+---------+------+-----------+
4 rows in set (0.00 sec)
Using above query we have got all the rows except the rows with country CHINA.

Using SQL WHERE clause

No comments
SQL where clause is used to get the rows from table based on some specified conditions, these conditions can be defined using WHERE clause.
+----+--------+---------+------+
| ID | FNAME  | LNAME   | AGE  |
+----+--------+---------+------+
|  1 | AKSHAY | KUMAR   |   12 |
|  2 | JONTY  | RHODES  |   40 |
|  3 | JOHNY  | DEPP    |   45 |
|  4 | JESSIE | PINKMAN |   25 |
+----+--------+---------+------+

SQL WHERE clause syntax and example in MySQl

Suppose we want to get all the rows from CUSTOMER table where country is INDIA.
SELECT * FROM CUSTOMER WHERE COUNTRY = 'INDIA';
+----+--------+-------+------+---------+
| ID | FNAME  | LNAME | AGE  | COUNTRY |
+----+--------+-------+------+---------+
|  1 | AKSHAY | KUMAR |   12 | INDIA   |
|  3 | JOHNY  | DEPP  |   45 | INDIA   |
+----+--------+-------+------+---------+
Here we can see that we have got only those rows for which condition was specified in WHERE clause. Here to specify the condition we have used = operator, but there are other operators which we can use with where clause.

Operators used with WHERE clause - SQL

Operator Details
= Equal
<> Not equal. Or some other versions of SQL we us !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column

Using SQL SELECT DISTINCT statement for getting distinct values

No comments
In MySql SELECT DISTINCT statement is used to get the distinct values from any column. For example suppose a table has a columns with country and we want to know the name of all the countries our table has, so we can use SELECT DISTINCT to get only unique names of the countries.
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
+----+--------+---------+------+-----------+

SELECT DISTINCT with syntax and example

SELECT DISTINCT COUNTRY FROM CUSTOMER;

+-----------+
| COUNTRY   |
+-----------+
| INDIA     |
| CHINA     |
| AUSTRALIA |
| CANADA    |
+-----------+
4 rows in set (0.00 sec)
Here we can see that table CUSTOMER has duplicate entries for country coluumn but with the help of DISTINCT we are able to get unique values.

Using SQL SELECT statement in MySql

No comments
SQL 'SELECT' is used to select the data from database. We get the data in a table form which we call result set.

SQL syntax for selecting all columns from MySql database

SELECT * FROM customer;
+----+--------+---------+------+
| ID | FNAME  | LNAME   | AGE  |
+----+--------+---------+------+
|  1 | AKSHAY | KUMAR   |   12 |
|  2 | JONTY  | RHODES  |   40 |
|  3 | JOHNY  | DEPP    |   45 |
|  4 | JESSIE | PINKMAN |   25 |
+----+--------+---------+------+
as you can see this statement has selected all the 4 rows of the table 'CUSTOMER';

SQL syntax for selecting specified columns from MySql database

We can also select some specified columns only from the table using the below syntax.
SELECT FNAME, AGE FROM customer;
+--------+------+
| FNAME  | AGE  |
+--------+------+
| AKSHAY |   12 |
| JONTY  |   40 |
| JOHNY  |   45 |
| JESSIE |   25 |
+--------+------+
4 rows in set (0.00 sec)
As you can see we have just selected FNAME and AGE from CUSTOMER TABLE. We can also use WHERE clause to specify some more conditions to select only some of the rows from the table instead of all the rows of the table. We will learn more about the WHERE clause in next few tutorials.