SQL-LEARNING DIARY2

1.NULL Value

A NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

1.Syntax

• S NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

• IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

3.Operator

• The IS NULL Operator
  test for empty values (NULL values)

• The IS NOT NULL Operator
  test for non-empty values (NOT NULL values)

2.UPDATE Statement

modify the existing records in a table.
1.UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

EXAMPLE : updates the first customer (CustomerID = 1) with a new contact person and a new city.

UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

2.UPDATE Multiple Records

• It is the WHERE clause that determines how many records will be updated.
  method1:

UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';

%%update the contactname to “Juan” for all records where country is “Mexico”

method2:

UPDATE Customers
SET ContactName='Juan', Country='Mexico';

3.Update Warning!
If you omit the WHERE clause, ALL records will be updated!

3.delete

delete existing records in a table.

DELETE FROM table_name WHERE condition;

4.SELECT TOP Clause

specify the number of records to return and useful on large tables with thousands of records.
method1:
SQL statement selects the first three records from the “Customers” table:

SELECT TOP 3 * FROM Customers;

method2: LIMIT clause

SELECT * FROM Customers
LIMIT 3;

method2: ROWNUM

SELECT * FROM Customers
WHERE ROWNUM <= 3;

5.MIN() and MAX() Functions

1. MIN() Syntax
   The MIN() function returns the smallest value of the selected column.

SELECT MIN(column_name)
FROM table_name
WHERE condition;

3. MAX() Syntax
   The MAX() function returns the largest value of the selected column.

SELECT MAX(column_name)
FROM table_name
WHERE condition;

6.COUNT(), AVG() and SUM() Functions

1. COUNT() Syntax
   The COUNT() function returns the number of rows that matches a specified criteria.

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

2. AVG() Syntax
   The AVG() function returns the average value of a numeric column.

SELECT AVG(column_name)
FROM table_name
WHERE condition;

4. SUM() Syntax
   The SUM() function returns the total sum of a numeric column.

SELECT SUM(column_name)
FROM table_name
WHERE condition;

REFERENCE:https://www.w3schools.com/sql/default.asp