Introduction to Relational Data Model

UNIT I

Introduction: Database system, Characteristics (Database Vs File System), Database Users(Actors on

Scene, Workers behind the scene), Advantages of Database systems, Database applications. Brief

introduction of different Data Models; Concepts of Schema, Instance and data independence; Three

tier schema architecture for data independence; Database system structure, environment, Centralized

and Client Server architecture for the database.

UNIT II

Relational Model: Introduction to relational model, concepts of domain, attribute, tuple, relation,

importance of null values, constraints (Domain, Key constraints, integrity constraints) and their

importance BASIC SQL: Simple Database schema, data types, table definitions (create, alter),

different DML operations (insert, delete, update), basic SQL querying (select and project) using

where clause, arithmetic & logical operations, SQL functions(Date and Time, Numeric, String

conversion).

UNIT III

Entity Relationship Model: Introduction, Representation of entities, attributes, entity set, relationship,

relationship set, constraints, sub classes, super class, inheritance, specialization, generalization using

ER Diagrams. SQL: Creating tables with relationship, implementation of key and integrity

constraints, nested queries, sub queries, grouping, aggregation, ordering, implementation of different

types of joins, view(updatable and non-updatable), relational set operations.

UNIT IV

Schema Refinement (Normalization): Purpose of Normalization or schema refinement, concept of

functional dependency, normal forms based on functional dependency(1NF, 2NF and 3 NF), concept

of surrogate key, Boyce-codd normal form(BCNF), Lossless join and dependency preserving

decomposition, Fourth normal form(4NF), Fifth Normal Form (5NF).

UNIT V

Transaction Concept: Transaction State, Implementation of Atomicity and Durability, Concurrent Executions, Serializability,

Recoverability, Implementation of Isolation, Testing for Serializability,Failure Classification, Storage, Recovery and Atomicity,

Recovery algorithm.Indexing Techniques: B+ Trees: Search, Insert, Delete algorithms, File Organization and Indexing,

Cluster Indexes, Primary and Secondary Indexes , Index data Structures, Hash Based Indexing: Tree

base Indexing ,Comparison of File Organizations, Indexes and Performance Tuning

Text Books:

1) Database Management Systems, 3/e, Raghurama Krishnan, Johannes Gehrke, TMH

2) Database System Concepts,5/e, Silberschatz, Korth, TMH

Reference Books:

1) Introduction to Database Systems, 8/e C J Date, PEA.

2) Database Management System, 6/e Ramez Elmasri, Shamkant B. Navathe, PEA

3) Database Principles Fundamentals of Design Implementation and Management, Corlos

Coronel, Steven Morris, Peter Robb, Cengage Learning.

e-Resources:

1) https://nptel.ac.in/courses/106/105/106105175/

2) https://www.geeksforgeeks.org/introduction-to-nosql/

Slide 5- 19

Example of a Relation

Slide 5- 20

Primary Key – A primary is a column or set of columns in a table that uniquely identifies tuples (rows) in that table.

Super Key – A super key is a set of one of more columns (attributes) to uniquely identify rows in a table.

Candidate Key – A super key with no redundant attribute is known as candidate key

Alternate Key – Out of all candidate keys, only one gets selected as primary key, remaining keys are known as alternate or secondary keys.

Composite Key – A key that consists of more than one attribute to uniquely identify rows (also known as records & tuples) in a table is called composite key.

Foreign Key – Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

Relational algebra and relational calculus

• We have designed the database, we need to store and retrieve data from the database, for this purpose we need to understand the concept of Relational algebra and relational calculus.

​

Query Language�

In simple words, a Language which is used to store and retrieve data from database is known as query language. For example – SQL

There are two types of query language:�1.Procedural Query language�2.Non-procedural query language

​

1. Procedural Query language:

In procedural query language, user instructs the system to perform a series of operations to produce the desired results. Here users tells what data to be retrieved from database and how to retrieve it.

2. Non-procedural query language:

In Non-procedural query language, user instructs the system to produce the desired result without telling the step by step process. Here users tells what data to be retrieved from database but doesn’t tell how to retrieve it.

For example – You are asking your younger brother to make a cup of tea, if you are just telling him to make a tea and not telling the process then it is a non-procedural language, however if you are telling the step by step process like switch on the stove, boil the water, add milk etc. then it is a procedural language.

​

Relational Algebra:

Relational algebra is a conceptual procedural query language used on relational model.

Relational Calculus:

Relational calculus is a conceptual non-procedural query language used on relational model.

Types of operations in relational algebra

We have divided these operations in two categories:�1. Basic Operations�2. Derived Operations

Basic/Fundamental Operations:

1. Select (σ)�2. Project (∏)�3. Union (∪)�4. Set Difference (-)�5. Cartesian product (X)�6. Rename (ρ)

Derived Operations:

1. Natural Join (⋈)�2. Left, Right, Full outer join (⟕, ⟖, ⟗)�3. Intersection (∩)�4. Division (÷)

RELATIONAL ALGEBRA

​

• Relational algebra is one of the two formal query languages associated with the relational model.
• Queries in algebra are composed using a collection of operators.
• A fundamental property is that every operator in the algebra accepts (one or two) relation instances as arguments and returns a relation instance as the result.
• A sequence of relational algebra operations forms a relational algebra expression whose relation will also be a relation that represents the result of a database query.
• The inputs and outputs of a query are relations.
• A query is evaluated using instances of each input relation and it produces an instance of the output relation.
• Consider the following schema:

Sailors(sid: integer, sname: string, rating: integer, age: real)

Boats( bid: integer, bname: string, color: string)

Reserves(sid: integer, bid: integer, day: date)

2. Set Operations

​

• The following standard operations on sets are also available in relational algebra:
• Union (U)
• Intersection (n)
• Set-difference (-) and
• Cross-product (x)
• The union, intersection, and set-difference (also called as minus) are binary operations.
• The two relations on which any of these operations are applied must have the same type of tuples.
• This condition is called union compatibility.
• Two relations R(A1,A2,…..An) and S(B1,B2,……Bn) are said to be union compatible if they have the same degree n and if Dom(Aᵢ) = Dom(Bᵢ) for 1≤i≤n.

• This means that the two relations have the same number of attributes and each corresponding pair of attributes has the same domain.
• We can define the three operations UNION, INTERSECTION and SET DIFFERENCE on two union compatible relation instance R and S as follows:
• UNION: The result of this operation denoted by RUS is a relation instance that includes all tuples that are either in R or in S or in both R and S. Duplicate tuples are eliminated.
• INTERSECTION: The result of this operation denoted by RnS is a relation instance that includes all tuples that are both R and S.
• SET DIFFERENCE (MINUS): The result of this operation denoted by R-S is a relation instance that includes all tuples that are in R but not in S.

Consider the relation instances S1 and S2. S1 and S2 are union compatible . S1 and R1 are not union compatible.

S1US2

S1nS2

S1-S2

iv) Cross-product:

• The cross product is denoted by “X”.
• This is also a binary operation.
• The relation instances on which it is applied do not have to be union compatible.
• The cross product operation produces a new element by combining every member (tuple) from one relation instance with every member (tuple) from other relation instance.
• In general, the result of R(A1,A2,……,An) X S(B1,B2,……,Bm) is a relation instance Q with degree n+m attributes (A1,A2,……,An,B1,B2,……,Bm).
• Consider the relation instances S1&R1. the Cartesian product S1 & R1 is S1XR1.

3. Rename

• The RENAME operation is used to rename the output of a relation.
• Sometimes it is simple and suitable to break a complicated sequence of operations and rename it as a relation with different names. Reasons to rename a relation can be many, like –
• We may want to save the result of a relational algebra expression as a relation so that we can use it later.
• We may want to join a relation with itself, in that case, it becomes too confusing to specify which one of the tables

we are talking about, in that case, we rename one of the tables and perform join operations on them.

• The renaming operator ρ is used for this purpose.
• Consider the expression ρ (R(F͞ ),E)
• E is an expression
• Returns an instance of a new relation R
• F͞ is the renaming list, which is in the form of oldname->newname or position->newname

Example: The expression ρ(C(1->SID1,5->SID2),S1XR1)

4. Joins

The join operation is one of the most useful operations in relational algebra and the most commonly used way to combine information from two or more relations.

Join operation denoted by ⋈.

It is used to combine tuples from two relations into single tuple.

1. Condition Join
2. Equi Join
3. Natural Join

​

• Condition Join

​

• The most general version of the join operation accepts a join condition c and a pair of relation instances as arguments and returns a relation instance.
• The join condition is identical to a selection condition in form.
• The operation is defined as follows:

R ⋈_c S = σ_c (R X S)

• The ⋈ is defined to be a cross-product followed by a selection.
• The condition c can refer to attributes of both Rand S.
• Example: Consider S1 ⋈ _{S1.SID<R1.SID} R1

II. Equi Join:

• A common special case of the join operation R ⋈S is when the join condition consists only comparison operator is ‘=‘ is called equijoin.

III. Natural Join

​

• Natural join does not use any comparison operator. 
• We can perform a Natural Join only if there is at least one common attribute that exists between two relations. In addition, the attributes must have the same name and domain.
• Natural join acts on those matching attributes where the values of attributes in both the relations are same.
• It is simply denoted as S1 ⋈ R1.

5. Division:

• Consider two relation instances A & B in which A has two fields x & y. B has just one field y, with the same domain as in A.

​

• We define the division operation A/B as the set of all x values such that for every y value in B there is a tuple <x,y> in A.

​

• That is for each x value in (the first column of ) A, consider the set of y values that appears in (the second column of)tuples of A with that x value. If this set contains (all y values in) B, the x values is in the result of A/B.

​

• The division operation is shown in the below table. The relation A lists the parts supplied by suppliers and of the B relation lists the parts. A/Bi computes suppliers who supply all parts listed in relation instance Bi.

Examples of Division OPERATION

Outer Join Operations:

​

A set of operations called outer joins can be used when we want to keep all the tuples in R or all those in S or all those in both relations in the result of the JOIN, regardless of whether or not they have matching tuples in the other relation. The join operations where only matching tuples are kept in the result are called inner joins.

There are three types of outer joins:

1. Left Outer Join
2. Right Outer Join
3. Full Outer Join

Consider the relations:

1. Left Outer Join:

​

The left outer join operation keeps every tuple in the first, or left relation R in R⟕S. If no matching tuple is found in S, then the attributes of S in the join result are filled or padded with the NULL values.

Example: Retrieve a list of all employee names and also the name of the departments they manage if they happen to manage a department, if they do not manage one, we can indicate it with a NULL value.

​

EMPLOYEE⟕_{SSN=MGR_SSN}DEPARTMENT

R⟕S

2. Right Outer Join:

It is denoted by ⟖ keeps every tuple in the second or right relation S in the result of R⟖S.

EMPLOYEE ⟖ _{SSN=MGR_SSN}DEPARTMENT

​

​

​

​

​

​

​

​

3. Full Outer Join:

It is denoted by ⟗ keeps all tuples in both are left and the right relations when no matching tuples are found, padding them with NULL values are needed.

EMPLOYEE ⟗ _{SSN=MGR_SSN}DEPARTMENT

DIFFERENCES BETWEEN RELATIONAL ALGEBRA & RELATIONAL CALCULUS

RELATIONAL CALCULUS:

Relational calculus is a non-procedural query language that tells the system what data to be retrieved but doesn’t tell how to retrieve it.

1. Tuple Relational Calculus (TRC)

Tuple relational calculus is used for selecting those tuples that satisfy the given condition.

2. Domain Relational Calculus (DRC)

In domain relational calculus the records are filtered based on the domains.

​

Tuple Relational Calculus

• Tuple Relational Calculus is a non-procedural query language unlike relational algebra. Tuple Calculus provides only the description of the query but it does not provide the methods to solve it. Thus, it explains what to do but not how to do.
• In Tuple Calculus, a query is expressed as

{t| P(t)}

where t = resulting tuples,� P(t) = known as Predicate and these are the conditions that are used to fetch t

• Thus, it generates set of all tuples t, such that Predicate P(t) is true for t.

​

P(t) Conditions:

• P(t) may have various conditions logically combined with OR (∨), AND (∧), NOT(¬).
• TRC (tuple relation calculus) can be quantified. In TRC, we can use Existential (∃) and Universal Quantifiers (∀).
• ∃ t ∈ r (Q(t)) = ”there exists” a tuple in t in relation r such that predicate Q(t) is true.
• ∀ t ∈ r (Q(t)) = Q(t) is true “for all” tuples in relation r.

​

Example:

• { T.name | Author(T) AND T. article = 'database' }
• { R| ∃T ∈ Authors(T. article='database' AND R.name=T.name)} 

​

• Output: This query selects the tuples from the AUTHOR relation.
• It returns a tuple with 'name' from Author who has written an article on 'database'.

Table-1 Student

Example-1: Query to display the last name of those students where age is greater than 30

{ t.Last_Name | Student(t) AND t.age > 30 }

Output:

Last_Name

----------------

Singh

Example-2: Query to display all the details of students where Last name is ‘Singh’

{ t | Student(t) AND t.Last_Name = 'Singh' }

Output:

First_Name Last_Name Age

-----------------------------------------------------------

Ajeet Singh 30

Chaitanya Singh 31

Table-1: Customer

Table-2: Branch

Table-3: Account

Table-4: Loan

Table-5: Borrower

Table-6: Depositor

1: Find the loan number, branch, amount of loans of greater than or equal to 10000 amount.

{t| t ∈ loan ∧ t[amount]>=10000}

2: Find the loan number for each loan of an amount greater or equal to 10000.

{t| ∃ s ∈ loan(t[loan number] = s[loan number] ∧ s[amount]>=10000)}

3: Find the names of all customers who have a loan and an account at the bank.

{t | ∃ s ∈ borrower( t[customer-name] = s[customer-name]) ∧ ∃ u ∈ depositor( t[customer-name] = u[customer-name])}

4: Find the names of all customers having a loan at the “ABC” branch.

{t | ∃ s ∈ borrower(t[customer-name] = s[customer-name] ∧ ∃ u ∈ loan(u[branch-name] = “ABC” ∧ u[loan-number] = s[loan-number]))}

DOMAIN RELATIONAL CALCULUS

​

• Domain Relational Calculus is a non-procedural query language equivalent in power to Tuple Relational Calculus.
• Domain Relational Calculus provides only the description of the query but it does not provide the methods to solve it.
• In Domain Relational Calculus, a query is expressed as,

{ < x₁, x₂, x₃, ..., x_n > | P (x₁, x₂, x₃, ..., x_n ) }

where, < x₁, x₂, x₃, …, x_n > represents resulting domains variables and

P (x₁, x₂, x₃, …, x_n ) represents the condition or formula equivalent to the Predicate calculus.

​

Predicate Calculus Formula:

• Set of all comparison operators
• Set of connectives like AND, OR, NOT
• Set of quantifiers

​

The domain variables those will be in resulting relation must appear before | within ≺ and ≻ and all the domain variables must appear in which order they are in original relation or table.

Table-1: Customer

Table-2: Loan

Table-3: Borrower

1: Find the loan number, branch, amount of loans of greater than or equal to 100 amount.

{≺l, b, a≻ | ≺l, b, a≻ ∈ loan ∧ (a ≥ 100)}

2: Find the loan number for each loan of an amount greater or equal to 150.

{≺l≻ | ∃ b, a (≺l, b, a≻ ∈ loan ∧ (a ≥ 150)}

3: Find the names of all customers having a loan at the “Main” branch and find the loan amount .

{≺c, a≻ | ∃ l (≺c, l≻ ∈ borrower ∧ ∃ b (≺l, b, a≻ ∈ loan ∧ (b = “Main”)))}

Table-3: Borrower

Table-2: Loan

Display the customer name and city who are having the loan number L1 and L3

​

Display loan no, customer name and city who took amount greater than 100$

{l,n,c|∃s(<n,s,c>)∈customer^∃b,a((<l,b,a>)∈loan^(a>100))^∃<n,l>∈Borrower}

INTEGRITY CONSTRAINTS:

• Integrity constraints are a set of rules. It is used to maintain the quality of information.
• Integrity constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected.
• Thus, integrity constraint is used to guard against accidental damage to the database.

​

Types of Integrity Constraint:

1. Domain constraints

• Domain constraints can be defined as the definition of a valid set of values for an attribute.
• The data type of domain includes string, character, integer, time, date, currency, etc. The value of the attribute must be available in the corresponding domain.
• Example:

2. Entity integrity constraints

• The entity integrity constraint states that primary key value can't be null.
• This is because the primary key value is used to identify individual rows in relation and if the primary key has a null value, then we can't identify those rows.
• A table can contain a null value other than the primary key field

​

Example:

3. Referential Integrity Constraints

• A referential integrity constraint is specified between two tables.
• In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in Table 2.
• Example:

4. Key constraints

• Keys are the entity set that is used to identify an entity within its entity set uniquely.
• An entity set can have multiple keys, but out of which one key will be the primary key. A primary key can contain a unique and null value in the relational table.

​

Example:

VIEWS

• A view is nothing more than a SQL statement that is stored in the database with an associated name.
• A view is a result set of stored query.
• A database object that contains the results of a query
• A view is actually a composition of a table in the form of a predefined SQL query.
• A view can contain all rows of a table or select rows from a table.
• A view can be created from one or many tables which depends on the written SQL query to create a view.

​

Student Detail

Student Marks

1. Creating view

A view can be created using the CREATE VIEW statement. We can create a view from a single table or multiple tables.

Syntax:

CREATE VIEW view_name AS  

SELECT column1, column2.....  

FROM table_name  

WHERE condition;  

Creating View from a single table:

SQL>CREATE VIEW DetailsView AS  

SELECT NAME, ADDRESS  

FROM Student_Details  

WHERE STU_ID < 4;  

SQL>SELECT * FROM DetailsView;  

Creating View from multiple tables

• View from multiple tables can be created by simply include multiple tables in the SELECT statement.

Example:

SQL>CREATE VIEW MarksView AS  

SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS  

FROM Student_Detail, Student_Mark  

WHERE Student_Detail.NAME = Student_Marks.NAME;  

SQL>SELECT * FROM MarksView;  

Deleting View

• A view can be deleted using the Drop View statement.

Syntax:

DROP VIEW view_name;

​

Example:

SQl>DROP VIEW MarksView;

Updating a View

​

There are certain conditions needed to be satisfied to update a view. If any one of these conditions is not met, then we will not be allowed to update the view.

• The SELECT statement which is used to create the view should not include GROUP BY clause or ORDER BY clause.
• The SELECT statement should not have the DISTINCT keyword.
• The View should have all NOT NULL values.
• The view should not be created using nested queries or complex queries.
• The view should be created from a single table. If the view is created using multiple tables then we will not be allowed to update the view.

​

Example:

update detailsview set sname='dutt' where address='delhi';

Inserting Rows into a View

• Rows of data can be inserted into a view.
• The same rules that apply to the UPDATE command also apply to the INSERT command.
• Here, we cannot insert rows in the MarksVIEW because we have not included all the NOT NULL columns in this view, otherwise you can insert rows in a view in a similar way as you insert them in a table.

Syntax:

SQL>INSERT INTO view_name(column1, column2 , column3,..) VALUES(value1, value2, value3..);

Example:

SQL>INSERT INTO DetailsView(NAME, ADDRESS) VALUES(‘Hari’,’Gurgaon’);

​

Deleting a row from a View:

• Deleting rows from a view is also as simple as deleting rows from a table.
• We can use the DELETE statement of SQL to delete rows from a view.
• Also deleting a row from a view first delete the row from the actual table and the change is then reflected in the view.

Syntax:

DELETE FROM view_name WHERE condition;

Example:

SQL>delete from detailsview where sname='dutt';

Uses of a View :�A good database should contain views due to the given reasons:

• Restricting data access –�Views provide an additional level of table security by restricting access to a predetermined set of rows and columns of a table.
• Hiding data complexity –�A view can hide the complexity that exists in a multiple table join.
• Simplify commands for the user –�Views allows the user to select information from multiple tables without requiring the users to actually know how to perform a join.
• Store complex queries –�Views can be used to store complex queries.
• Rename Columns –�Views can also be used to rename the columns without affecting the base tables provided the number of columns in view must match the number of columns specified in select statement. Thus, renaming helps to to hide the names of the columns of the base tables.
• Multiple view facility –�Different views can be created on the same table for different users.

Aggregate Functions example

We will be using SalesOrderDetail of Adventureworks database for the below examples.

Sum()

Select sum (OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This sums up the OrderQty column value of SalesOrderDetail table.

 The output is

Avg()

select avg(OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This gives the average of OrderQty column of SalesOrderDetail table.

Max()

select max(OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This gives out the maximum value of the OrderQty column of [SalesOrderDetail] table.

Min()

select min(OrderQty) [Total Quantity] from [Sales].[SalesOrderDetail]

This gives out the minimum value of the OrderQty column of [SalesOrderDetail] table.

Count()

Select count (*) from [Person]. [Person]

Returns total number of records in the Person table

1.Min Aggregate Function :

The Min aggregate function stands for the minimum. It returns the minimum value by checking the whole column.To calculate the minimum value in the column of the table the column must be in number datatype format.

Syntax :

min(number_column)

Select Min(number_column) from tablename;

Select Min(Number-column) from tablename where condition;

​

These are above different syntax of Min function. Let say you need to find out the minimum grade for the Employees for specific course from Employee table.

​

Select Min(grade) from Employee;

The above query will give you the minimum grade for Employees. Make sure that the grade column is in numeric format. If you want to find out the Min grade for the Department name Security then following query is helpful.

​

Select Min(grade)from Employee where department=’Security’;

The above query will give you the minimum grade from Employee table where department name is security.

2. Max Aggregate Function :

The Max aggregate function stands for the Maximum. It returns the maximum value by checking the whole column.To calculate the maximum value in the column of the table the column must be in number datatype format.

Syntax :

max(number_column)

Select Max(number_column) from tablename;

Select Max(Number-column) from tablename where condition;

​

These are above different syntax of Max function. Let say you need to find out the maximum grade for the Employees for specific course from Employee table.

​

Select Max(grade) from Employee;

​

The above query will give you the maximum grade for Employees.Make sure that the grade column is in numeric format.If you want to find out the Max grade for the Department name Security then following query is helpful.

​

Select Max(grade)from Employee where department=’Security’;

The above query will give you the maximum grade from Employee table where department name is security.

3. Avg Aggregate Function :

​

The Avg aggregate function is one of the most used SQL Aggregate function.This function provides you the Average of the given column.Calculating average value of given column is useful in different purpose. The calculating average of specific column is used in different reports as well.The Avg is abbreviation of Average.The column must be in number format to calculate its Average.

Syntax :

Avg(number_column)

Select Avg(number_column) from tablename;

Select Avg(Number-column) from tablename where condition;

These are above different syntax of avg function. Lets say user needs to calculate the average salary of the employee. This expression is most used expression in HR reporting.

​

Select Avg(salary) from Employee;

Select Avg(salary) from Employee where department=’Business Intelligence’;

Select Avg(salary) from Employee where department=’Business Intelligence’ group by position;

4.Count Aggregate function:

This function is undoubtedly most used aggregate function.The count function is used to calculate the count of the rows in the specified column. There are so many scenarios where user needs to calculate the count of the rows.The Count function is most used function in reporting and analytics.

Syntax :

A) COUNT (Numeric_Column_Name)

B) COUNT (*)

C) COUNT (DISTINCT Column_Name)

​

Example 1: Calculate the count of Employees from Employee table.

Select count(*) from Employees;

Example 2 :  Calculate the count of Employees who are software Engineers

Select count(Eno) from Employees where job_title=’Software Engineer’;

Example 3 : Calculate the count of Employees who are Software Engineer by removing duplicate Employees.This is most common scenario where user needs to remove duplicates. If the Employee table does not have any constraints then the values from Employee table gets duplicated.In that case user needs to use distinct keyword before counting the column name.

Select count(distinct Eno) from Employees where job_title=’Software Engineer’;

5.Sum Aggregate Function :

There are lot of times user will confused in Avg function and Sum function.The Sum function calculates the sum of the numeric column.

​

Syntax:

Sum(number_column)

Select Sum(number_column) from tablename;

Select Sum(Number-column) from tablename where condition;

​

Lets say user wants to calculate the total salary from Employees table.

​

Select Sum(salary) from Employees;

​

There are scenarios where user wants to calculate the sum of the salary departmentwise.

​

Select Sum(salary) from Employees where deparment_name=’Software’;

​

​

String Function Example

LTRIM()

Removes space from left side of the string

select ltrim(' tes')

RTRIM()

Removes space from left side of the string

select Rtrim('tes  ')

Len()

Gives the length of the string.

select Len('Test')

LEFT()

select Left('Sanchayan',3)

This SQL Statement extracts three characters from the left side of the string.

RIGHT()

select Right('Sanchayan',3)

The above SQL Statement extracts three characters from the right side of the string

Examples of Date function

GETDATE()

Gives out the current date

select GETDATE()

DATEADD()

SELECT DATEADD (month, 1, '20060830');

Add a month with the date value 20060830

DAY()

select day('12/18/2019')

The SQL Statement gives the current day value of the date passed as parameter.

MONTH()

select MONTH('12/18/2019')

The SQL Statement gives the current month value of the date passed as parameter.

YEAR()

select YEAR('12/18/2019')

The SQL Statement gives the current year value of the date passed as parameter.

CAST()

SELECT CAST(25.65 AS int)

This converts the value 25.65 into integer.

The output is

CONVERT()

Converts a string into a different data type here integer.

SELECT CONVERT(int, 25.65);

The output is as below

CURRENT_USER

This advance function gives out the current user of the system

select CURRENT_USER

ISNUMERIC()

This function checks whether the parameter passed in it is numeric or not.

select ISNUMERIC(5)

This gives out 1 if true and 0 if false.

​