SQL NULL Clause
Before start to discuss about NULL clause in SQL we must know, what is Null?
Basically we used the NULL keyword to show that the expected value field is blank or empty. Value with ‘zero’ or spaces ‘ ‘ cannot be considered as NULL value.
Sometimes it may be happening that some field in a column contains a Null value. Then we will use the following query to get the records having null values.
Let Assume we have the table
| Employee ID | Employee Name | Department | City |
| 001 | Harish Sharma | Information Technology | |
| 002 | Girish Sharma | Accounts | Gurugram |
| 003 | Vishnu Kumar | Marketing | Delhi |
| 004 | Pooja Ahuja | Information Technology | |
| 005 | Keshav Sharma | Production | Ghaziabad |
|
1 |
SELECT * FROM Employee WHERE City IS NULL; |
Result Set:
| Employee ID | Employee Name | Department | City |
| 001 | Harish Sharma | Information Technology | |
| 004 | Pooja Ahuja | Information Technology |
Additionally, we can also use NULL keyword like this
|
1 |
SELECT * FROM Employee WHERE City IS NOT NULL; |
| Employee ID | Employee Name | Department | City |
| 002 | Girish Sharma | Accounts | Gurugram |
| 003 | Vishnu Kumar | Marketing | Delhi |
| 005 | Keshav Sharma | Production | Ghaziabad |
UPDATE Clause in SQL
We use the UPDATE clause to edit the data in Database.
Let’s look the following query as an example:
|
1 2 |
UPDATE Employee SET Employee Name = 'Ekta Gupta', City= 'Meerut' WHERE Employee ID = 005; |
Our previous records was
| Employee ID | Employee Name | Department | City |
| 005 | Keshav Sharma | Production | Ghaziabad |
Now after run the above SQL Query it will changed as
| Employee ID | Employee Name | Department | City |
| 005 | Ekta Gupta | Production | Meerut |
DELETE Clause in SQL
For Delete the existing data from the database we use this DELETE clause.
Let’s take an example
|
1 |
DELETE FROM Employee WHERE Employee ID=005; |
The above query will delete the record for employee ‘Ekta Gupta’ against the Employee ID 005.
You can also delete all data from the ‘Employee’ table.
See How,
|
1 |
DELETE * FROM Employee; |
TOP, LIMIT, ROWNUM & PERCENT Clause
Check the below example query for these clause which are achieving the same result.
|
1 |
SELECT TOP 3 * FROM Employee; |
This query will return top three records from the Employee table.
|
1 |
SELECT * FROM Employee LIMIT 3; |
This query will also return the same result.
|
1 |
SELECT * FROM Employee WHERE ROWNUM <= 3; |
By using ROWNUM we already achieved the same result like TOP & LIMIT queries.
Now, by using the below query, the SQL will return the 50% records from the table.
|
1 |
SELECT TOP 50 PERCENT * FROM Employee; |
Let Assume, if we have 6 records in our Employee table then it will return top 3 records.
MIN() & MAX() SQL Functions
By using these functions, you can retrieve the minimum and maximum value of any column.
Let assume we have an another table in our database and that is ‘Salary’
| Employee ID | Salary | DOJ | Grade |
| 001 | 15000 | 02/05/2018 | 1 |
| 002 | 35000 | 08/08/2014 | 1.2 |
| 003 | 8500 | 12/01/2017 | 0.5 |
| 004 | 55000 | 04/03/2005 | 2 |
Now let’s look the below example query
|
1 |
SELECT MIN(Salary) AS MinimumSalary FROM Salary; |
Result Set:
| MinimumSalary |
| 8500 |
|
1 |
SELECT MAX(Salary) AS MaximumSalary FROM Salary; |
Result Set:
| MaximumSalary |
| 55000 |
COUNT(), AVG() & SUM() SQL Functions
We can use these functions to check count, average and sum respectively for any column from the table.
|
1 |
SELECT COUNT(Employee ID) FROM Salary; |
Result Set:
| COUNT(Employee ID) |
| 4 |
|
1 |
SELECT AVG(Salary) FROM Salary; |
Result Set:
| AVG(Salary) |
| 28375 |
|
1 |
SELECT SUM(Salary) FROM Salary; |
Result Set:
| SUM(Salary) |
| 113500 |
SQL LIKE Operator
SQL Like operator is used for search a specific pattern from the table data.
Let us explain better by the following SQL queries.
Demo Table: Employee
| EmployeeName | NickName | CityName | PostalCode | Country |
| Aakash Gupta | Aakash | Banglore | 101102 | India |
| Williams Derek | William | New Mexico | 205021 | United States |
| Harish Sharma | Harry | New Delhi | 100011 | India |
| Terry Simmons | Terry | Cape Town | 502123 | South Africa |
| Rashid Khan | Rashid | Dubai | 401289 | Saudi Arabia |
| Kumar T | Thagamoney | Kerala | 801921 | India |
| Vivek Chandra | Vivek | Dehradun | 201142 | India |
|
1 |
SELECT * FROM Employee WHERE EmployeeName LIKE 'a%'; |
The above query will return the all results where Employee name starting with “a”
Result Set:
| EmployeeName | NickName | CityName | PostalCode | Country |
| Aakash Gupta | Aakash | Banglore | 101102 | India |
| Harish Sharma | Harry | New Delhi | 100011 | India |
|
1 |
SELECT * FROM Employee WHERE EmployeeName LIKE 'a%'; |
The above query will return the all results where Employee name ending with “a”
| EmployeeName | NickName | CityName | PostalCode | Country |
| Aakash Gupta | Aakash | Banglore | 101102 | India |
| Vivek Chandra | Vivek | Dehradun | 201142 | India |
There are some more like operator manipulations. You can refer from W3Schools.com
IN & BETWEEN Clause
By using IN operator we can retrieve the filter data from the table.
Example:
|
1 |
SELECT * FROM Employee WHERE Country IN ('India', ‘South Africa'); |
The above query will return the all result for country “India” & “South Africa”.
The BETWEEN operator allows us to retrieve the data from a specific range.
|
1 |
SELECT * FROM Salary WHERE Salary BETWEEN 5000 AND 30000; |
The above query will retrieve the list of all employees having salary between 5000 & 30000.
