AND, OR & NOT operator

The WHERE clause can be combined with AND, OR, and NOT operators.

The AND and OR operators are used to filter records based on more than one condition:

ü  The AND operator displays a record if all the conditions separated by AND is TRUE.

ü  The OR operator displays a record if any of the conditions separated by OR is TRUE.

The NOT operator displays a record if the condition(s) is NOT TRUE.

AND Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

OR Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

NOT Syntax

SELECT column1, column2, ...
FROM table_name

WHERE condition1 AND condition2 AND condition3 ...; 

Example of AND,OR & NOT operator

 Consider the following Student table.

s_id	s_Name	age	salary
101	Dashzin	15	5000
102	Ravi	17	4000
103	Abhinav	17	7000
104	Ankit	22	4000

Example of AND operator
AND operator is used to set multiple conditions with Where clause.

SELECT * from student WHERE salary < 5000 AND age >17;

s_id	s_Name	age	salary
104	Ankit	22	4000

Example of OR operator
AND operator is used to set multiple conditions with Where clause.

SELECT * from student WHERE salary < 5000 OR age >17;

s_id	s_Name	age	salary
102	Ravi	18	4000
104	Ankit	22	4000

Example of NOT operator
AND operator is used to set multiple conditions with Where clause.

SELECT * from student WHERE salary NOT 5000;

s_id	s_Name	age	salary
102	Ravi	17	4000
103	Abhinav	17	7000
104	Ankit	22	4000

Read More

Distinct keyword

The distinct keyword is used with Select statement to retrieve unique values from the table. Distinct removes all the duplicate records while retrieving from database.

Syntax

SELECT distinct column-name from table-name;

Example of Distinct keyword

Consider the following Student table.

s_id	s_Name	age	salary
101	Dashzin	15	5000
102	Ravi	18	4000
103	Abhinav	17	7000
104	Ankit	22	4000
105	Dashrath	17	5000

SELECT distinct salary from student;

The above query will return only the unique salary from student table                                              

salary

5000

4000

7000

Read More

Database normalization

Normalization split a large table into smaller tables and define relationships between them to increases the clarity in organizing data.

Some facts about database normalization

·         The words normalization and normal form refers to the structure of database.

·         Normalization was developed by IBM researcher E.F. Codd In the 1970s.

·         Normalization increases the clarity in organizing data in Database.

Normalization of a Database is achieved by following a set of rules called ‘forms’ in creating the database.

Database normalization rules

Database normalization process are divided into following normal form:

·         First Normal Form (1NF)

·         Second Normal Form (2NF)

·         Third Normal Form (3NF)

·         Boyce-Codd Normal Form (BCNF)

·         Fourth Normal Form (4NF)

·         Fifth Normal Form (5NF)

First Normal Form (1NF)

Each column is unique in 1NF.

Example:

Sample Employee table, it displays employees are working with multiple departments.

Employee	Age	Department
Melvin	32	Marketing, Sales
Edward	45	Quality Assurance
Alex	36	Human Resource

Employee table following 1NF:

Employee	Age	Department
Melvin	32	Marketing
Melvin	32	Sales
Edward	45	Quality Assurance
Alex	36	Human Resource

Second Normal Form (2NF)

The entity should be considered already in 1NF and all attributes within the entity should depend solely on the unique identifier of the entity.

Example:

Sample Products table:

productID	product	Brand
1	Monitor	Apple
2	Monitor	Samsung
3	Scanner	HP
4	Head phone	JBL

Product table following 2NF:
Products Category table:

productID	product
1	Monitor
2	Scanner
3	Head phone

Brand table:

brandID	brand
1	Apple
2	Samsung
3	HP
4	JBL

Products Brand table:

pbID	productID	brandID
1	1	1
2	1	2
3	2	3
4	3	4

Third Normal Form (3NF)

The entity should be considered already in 2NF and no column entry should be dependent on any other entry (value) other than the key for the table.

If such an entity exists, move it outside into a new table.

3NF is achieved are considered as the database is normalized.

Boyce-Codd Normal Form (BCNF)

3NF and all tables in the database should be only one primary key.

Fourth Normal Form (4NF)

Tables cannot have multi-valued dependencies on a Primary Key.

Fifth Normal Form (5NF)

Composite key shouldn’t have any cyclic dependencies.

Well this is a highly simplified explanation for Database Normalization. One can study this process extensively though. After working with databases for some time you’ll automatically create Normalized databases. As, it’s logical and practical.

Read More