Learn SQL | Quick SQL Tutorial | Lesson 2 | NULL, UPDATE, DELETE, SQL Functions etc

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

 

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

 

 

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:

 

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

 

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,

 

 

TOP, LIMIT, ROWNUM & PERCENT Clause

 

Check the below example query for these clause which are achieving the same result.

 

This query will return top three records from the Employee table.

 

 

This query will also return the same result.

 

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.

 

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

 

Result Set:

MinimumSalary
8500

 

 

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.

 

Result Set:

COUNT(Employee ID)
4

 

 

Result Set:

AVG(Salary)
28375

 

 

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

 

 

 

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

 

 

 

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:

 

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.

 

The above query will retrieve the list of all employees having salary between 5000 & 30000.