Content

• SQL Normal Forms
• SQL Subquery
• SQL Inbuilt String Functions
• SQL Inbuilt Date Functions
• SQL Inbuilt Mathematical Functions
• SQL User Defined Functions
• SQL Temp Tables

SQL Normal Forms

Database normalization is the process of organizing data to minimize data redundancy (data duplication), which in turn ensures data consistency.

Database normalization is a step by step process. There are 6 normal forms, First Normal form (1NF) to Sixth Normal Form (6NF). Most databases are in third normal form (3NF).

Example

Employee Name	Gender	Salary	Dept	DeptHead	DeptLocation
ASHISH	Male	50000	IT	Shree	Kolhapur
Ashutosh	Male	50000	HR	Mahesh	Mumbai
Abhishek	Male	50000	IT	Shree	Kolhapur
Swapnil	Male	50000	IT	Shree	Kolhapur

In above Table (Employees),For every employee with in the same department, we are repeating, all the 3 columns (DeptName, DeptHead and DeptLocation),this may cause data redudancy and data inconsistency

to reduce the data redundancy and inconsistency, we can divide this badly organised table into two (Employees and Departments), as shown below.

Normalized Departments Table

DeptId	DeptName	DeptHead	DeptLocation
1	IT	Shree	Kolhapur
2	HR	Mahesh	Mumbai

Normalized Employees Table

Employee Name	Gender	Salary	DeptId
ASHISH	Male	50000	1
Ashutosh	Male	50000	2
Abhishek	Male	50000	1
Swapnil	Male	50000	1

First normal form (1NF)

A table is said to be in 1NF, if

• The data in each column should be atomic. No multiple values, sepearated by comma.
• The table does not contain any repeating column groups
• Identify each record uniquely using primary key.

Second normal form (2NF)

A table is said to be in 2NF, if

• The table meets all the conditions of 1NF
• Move redundant data to a separate table
• Create relationship between these tables using foreign keys.

Third normal form

• Meets all the conditions of 1NF and 2NF
• Does not contain columns (attributes) that are not fully dependent upon the primary key

---

SQL Subquery

A subquery is simply a select statement, that returns a single value and can be nested inside a SELECT, UPDATE, INSERT, or DELETE statement.

It is also possible to nest a subquery inside another subquery.

Subqueries are always encolsed in paranthesis and are also called as inner queries, and the query containing the subquery is called as outer query.

Most of the times subqueries can be very easily replaced with joins.

 Select [Id], [Name], [Description] from tblProducts where Id not in (Select Distinct ProductId from tblProductSales)

---

SQL Inbuilt String Functions

Function	Description
ASCII	Returns the ASCII value for the specific character
CHAR	Returns the character based on the ASCII code
LEN	Returns the length of a string
LOWER	Converts a string to lower-case
REVERSE	Reverses a string and returns the result
UPPER	Converts a string to upper-case

---

SQL Inbuilt Date Functions

SQL Server comes with the following data types for storing a date or a date/time value in the database

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MI:SS
• SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
• TIMESTAMP - format: a unique number

Functions

Function	Date Time Format	Description
GETDATE()	2021-08-31 20:15:04.543	to get current datetime
CURRENT_TIMESTAMP	2021-08-31 20:15:04.543	ANSI SQL equivalent to GETDATE
SYSDATETIME()	2021-08-31 20:15:04.5380028	More fractional seconds precision
SYSDATETIMEOFFSET()	2012-08-31 20:15:04.5380028 + 01:00	More fractional seconds precision + Time zone offset
GETUTCDATE()	2021-08-31 19:15:04.543	UTC Date and Time
SYSUTCDATETIME()	2012-08-31 19:15:04.5380028	UTC Date and Time, with More fractional seconds precision

---

SQL Inbuilt Mathematical Functions

Function	Description
ABS	Returns the absolute value of a number
CEILING	Returns the smallest integer value that is >= a number
FLOOR	Returns the largest integer value that is <= to a number
POWER	Returns the value of a number raised to the power of another number
RAND	Returns a random number
SQRT	Returns the square root of a number
SQUARE	Returns the square of a number
ROUND	Rounds a number to a specified number of decimal places

---

SQL User Defined Functions

SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value.

CREATE FUNCTION [database_name.]function_name (parameters)
        RETURNS data_type AS
        BEGIN
            SQL statements
            RETURN value
        END;