The LIKE operator is used to list all rows in a table whose column values match a specified pattern. It is useful when you want to search rows to match a specific pattern, or when you do not know the entire value. For this purpose we use a wildcard character ‘%’.
For example: To select all the students whose name begins with ‘S’
SELECT first_name, last_name
FROM student_details
WHERE first_name LIKE ‘S%’;
The output would be similar to:
first_name last_name
————- ————-
Stephen Fleming
Shekar Gowda
The above select statement searches for all the rows where the first letter of the column first_name is ‘S’ and rest of the letters in the name can be any character.
A relation is in second normal form if it is in 1NF and every non key attribute is fully functionally dependent on the primary key.
It is in first normal form All non-key attributes are fully functional dependent on the primary key
The First Normal form deals with the atomicity whereas the Second Normal Form deals with the relationship between the composite key columns and non-key columns.
To achieve the next progressive level your table should satisfy the requirement of First Normal Form then move towards the Second Normal Form.
Let see the example from above 1st Normal From
ITEM
COLORS
PRICES
TAX
Pen
Red
3.0
0.40
Pen
Blue
3.0
0.40
Scale
Red
3.0
0.40
Scale
Yellow
3.0
0.40
Bag
Blue
120.0
12.52
Bag
Black
120.0
12.52
Table is not in Second Normal Form because the price and tax depends on the item, but not color.
The inventor of the relational model Edgar Codd proposed the theory of normalization with the introduction of First Normal Form and he continued to extend theory with Second and Third Normal Form. Later he joined with Raymond F. Boyce to develop the theory of Boyce-Codd Normal Form.
Theory of Data Normalization in Sql is still being developed further. For example there are discussions even on 6th Normal Form. But in most practical applications normalization achieves its best in 3rd Normal Form. The evolution of Normalization theories is illustrated below
1st Normal Form >> 2nd Normal Form >> 3rd Normal Form >>4th Normal Form >> 5th Normal Form >>6th Normal Form
Database Normalization Examples –
Assume a video library maintains a database of movies rented out. Without any normalization all information is stored in one table as shown below.
Further We shall discuss on different normal forms in detailed:
The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.
The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.
Syntax: The basic syntax of INNER JOIN is as follows:
SELECT table1.column1, table2.column2…
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
Image Representation:
The INNER JOIN in SQL joins two tables according to the matching of a certain criteria using a comparison operator.
Sample Two Table Data:
FOODS:
ITEM_ID
ITEM_NAME
ITEM_UNIT
COMPANY_ID
1
Bread
PCS
25
2
Sliced Bread
PCS
16
3
Roasted Bread
PCS
32
4
Garlic Bread
PCS
48
5
Cheese Bread
PCS
18
6
Fruit Bread
PCS
22
7
Milk Bread
PCS
28
Company:
COMPANY_ID
COMPANY_NAME
COMPANY_CITY
25
SUBWAY
IRLAND
16
BURGER KING
UNITED STATES
32
KFC
UK
18
ROLLZONE
AUSTRALIA
28
DOMINOS
INDIA
Example
Here is an example of inner join in SQL between two tables.
To join item name, item unit columns from foods table and company name, company city columns from company table, with the following condition –
1. company_id of foods and company_id company table must be same, the following SQL statement can be used :
SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city
FROM foods
INNER JOIN company
ON foods.company_id =company.company_id;
Example of SQL INNER JOIN using JOIN keyword
To get item name, item unit columns from foods table and company name, company city columns from company table, after joining these mentioned tables, with the following condition –
1. company id of foods and company id of company table must be same,
the following SQL statement can be used :
SELECT foods.item_name,foods.item_unit,
company.company_name,company.company_city
FROM foods
JOIN company
ON foods.company_id =company.company_id;
SQL DBA Training with Real-time Project, Job Support
SQL DBA Online Training (LIVE, Instructor-Led)
Real-time Practical SQL DBA LIVE Online Training designed to work with all major SQL Database Administration activities including DB Design, Normalized Views, Stored Procedures, Indexes, Joins, DB Maintenance, Query Tuning, HA/DR, AAG and SQL Cluster Issues. Study Material, 24×7 Online Lab, Certification, Job Support with One Real-time Project included in the course. Versions: SQL Server 2012, SQL Server 2014 & 2016.
