Uma's Blog

Chapter 1: Introduction

Data: Collection of unprocessed items.Which can include text, numbers,images,audio and video which are later processed into information.
Information: Result of processed data.
Examples:
Data--input,raw material,(2,6,4,3,9,7,5,1,9,8).
Information--output,product,(1,2,3,4,5,6,7,8,9).

Database:
1) It is a systematic collection of data.
2) Support storage and manipulation of data.
3) Data management easily.

DBMS:
1) It is a System software for creating and managing databases.
2) It provides users & programmers with a systematic way to create, retrieve,update and manage data.

Types of DBMS:
1) Hierarchical DBMS: Parent-child relationship of storing data.
2) Network DBMS: many to many relations.
3) Relational DBMS: Database relations in the form of tables(MySQL,Oracle).
4) Object oriented DBMS: Information is stored in the form of objects(PostgreSQL).

Relational DBMS:
1) RDBMS stands for Relational Database Management Systems.
2) Data is represented in the form of tables.
3) Due to collection of organized set of tables,data can be accessed easily.
4) RDBMS uses table to store data.

Different Types of RDBMS:
MySQL:
1) MySQL is an open source relational database management system.
2) Written in C and C++.
3) It follows a client-server architecture.
4) It can handle the large amount of data. The default size of file is 4GB.
5) It is compatible to run on many operating systems.
6) Easy to use.

Oracle:
1) Oracle is a relational database management system.
2) It is marketed by Oracle corporation.
3) It is designed for enterprise grid computing.
4) In grid computing, the computers on the network can work on a task together
i.e supercomputer. Grid works on various tasks with in a network.

Sybase:
1) Sybase is a database server.
2) Sybase has released their sql database product for Linux.
3) It is a client server database engine.

MS Access:
1) Microsoft Access is a relational database management system for windows.
2) Organize data into manageable related units.
3) We can enter,modify and locate data.
4) create custom forms and reports.

IBM DB2:
1) DB2 is database product from IBM.
2) It is a relational database management system.
3) It is designed to store, analyze and retrieve data efficiently.
4) This is extended with the support of Object oriented features.

MS Sql Server:
1) Sql Server is software developed by Microsoft.
2) It is implemented based on RDBMS.
3) It is a platform independent.
4) It can run on single laptop or network of servers.
5) It is both GUI and command based software.

Chapter 2: Download and install SQL Server

Chapter 4: Database:

Database: Collection of related data.

Creating Database using management studio:

Databases-->New Database-->

Creating Database using Command Statement:

New Query-->

Drop/Delete the created database from command and MS:

Using command:

Using MS:

Chapter 5: SQL Datatypes

Number:

tinyint: 1 byte storage.0 to 255 possible values.
smallint: 2 bytes storage. -32768 to 32767.
int: 4 bytes storage.
bigint: 8 bytes storage.
decimal(precision,scale): 5 to 17 bytes depending on precision.
numeric(precision,scale): functionally equivalent to decimal.
real: 4 bytes storage.
float: 8 bytes storage.
smallmoney: 4 bytes storage.(to represent currency values)
money: 8 bytes storage.

String:

char(n): fixed length string data (1 to 8000 bytes).
varchar(n/max): variable length string data. used to store non-Unicode characters.
binary(n): fixed length binary data with length of n bytes(1 to 8000 bytes).
varbinary(n/max): variable length binary data (1 to 8000 bytes).
nchar(n): fixed length string data.n defines the string length in byte-pairs and must be a value from 1 to 4000.
nvarchar(n/max): variable length string data.used to store Unicode characters.
text: variable length non Unicode data.
ntext: variable length Unicode data.
image: variable length binary data.

Date:

date(): gives date as YYYY-MM-DD (3 bytes).
getdate(): returns date.
getutcdate(): returns date and time.

XML:

xml datatype can store either a complete xml document or fragment of xml data.

Chapter 6: Create tables for students and teachers

Table:

A table is a organized grouping of fields, either it stores permanent data or frequently updated data.

Using command statement:

Step 1: Select new query.

Step 2: Write sql command

Step 3: Output

Using MS:

Step 1: Expand database folder and select table.

Step 2: Enter column names and datatypes.

Step 3: When we are going to close current window then it will opens new window to give table name.

Step 4: Output

Truncating a table:

Truncate operation is like delete operation but we can't rollback.

Command:

Output:

Chapter 7: Altering the Table

Adding new column to the table:

Command:

Output:

Drop Column:

Command:

Output:

Change the Column Datatype:

Command:

Output:

Change the Column Size:

Command:

Change the Column Name:

Command:

Output:

Chapter 8: Constraints

1) Constraints are to specify the rules to data.
2) Constraints can be column level or table level.
3) Default value of cell is "Null".

Not Null Constraint:

It does't allow null values.

Command:

Unique Constraint:

Each value should be unique (different).

Command:

Check Constraint:

It will check the condition at the time of inserting data.

Command:

Violating Check Constraint:

Output:

Primary Key Constraint:

This constraint is used to identify each row uniquely in a table.

Command:

For Single Column:

For Multiple Columns:

Foreign Key Constraint:

Foreign Key is a column that references a column of another table.

It is useful to link the tables and to retrieve data from multiple tables.

Command:

Default Constraint:

Assigns default value to the column.

Command:

Output:

Candidate Key Vs Composite Key:
A Candidate key is a set of one or more columns that can identify a record uniquely in the table.It can't be null and the values must be unique.

A Composite key is a key of two or more attributes that uniquely identifies a row.

For example, In student table Sid and SrefId are columns. We can make those as primary key.This key is known as Composite key.

Example:
Student Table(Sid,Fname,Lname,CourseId)
Primary key-Sid.
Foreign Key-CourseId.
Candidate Keys-Sid,Fname+Lname.
Composite Key-Fname+Lname.

Auto Increment:
MS SQL Server uses identity keyword to perform auto increment.
Syntax: IDENTITY(starting_value,increment value).
Example: IDENTITY(1,1).

Code:

Output:

Foreign key relation between STUDENT table and TEACHERS table:
Code:

Output:

Chapter 9:

Inserting values to the table without using VALUE keyword:
Code:

Updating student table:
Code:

Output:

Inserting only specific values and null values:
Code:

Output:

Insert values with random order:
Code:

Output:

Delete the record from table:
1) Delete from table_name, deletes whole data from table.
2) Using where condition ,we can delete particular row.
Code:

Output:

Chapter 10:

Display data from table:

1) To retrieve all columns in the table

Syntax: select * from table_name.

2) Selecting specific columns from the table

Code:

Output:

3) Selecting with where condition

Code:

Output:

4)Select using IN

It is used to specify multiple values in where condition.

Code:

Output:

5) Select using NOT IN

Code:

Output:

6) Select using LIKE Operator

This operator is used in where clause to identify specific pattern in a column.
The '%' symbol represents zero, single or multiple characters.
The '_' symbol represents single character.
'a%' - return values that start with 'a'.
'%a' - return values that ends with 'a'.
'%a%' - return values that contains 'a' in any position.
'_a%' - return values that have 'a' in second position.
'a_%_%' - return values that start with 'a' and contains 3 characters.
'a%i' - return values that start with 'a' and ends with 'i'.

Examples:

7) Selecting using NOT LIKE operator
This operator is used in where clause to return values that not contains the given pattern.

8) Select using between operator
This operator returns the values between the given range.

9) Select distinct
Select distinct is used to return only different values.
Examples:

10) Select clause
Select clause is used to return specific number of records.
Code:

Output:

11) Select by order

The ORDER BY keyword is used to sort the result set in ascending order or descending order
By default , It sorts in ascending order.
ASC for ascending order.
DESC for descending order.

Examples:

12) SQL Aliases

Aliases are used to give temporary name to columns or table at the time of retrieving data.

Example:

Chapter 11:SQL Operators

1) Arithmetic Operators

To perform arithmetic operations on operands.

Code:

Output:

2) Comparison Operators

To compare two values

Code:

Output:

3) Logical Operators:

ALL Operator:

Examples:

ANY Operator:

Examples:

EXISTS Operator:

If the sub query returns at least one row then the EXISTS clause will be evaluated to true otherwise false.
Syntax: where EXISTS (condition).

Examples:

AND Operator & OR Operator:

If all the conditions are true then AND condition is true.
If any of the conditions are true then OR condition is true.

Examples:

Chapter 13: SQL Predefined Functions

1) Numeric Functions:

Code:

Output:

2) Date Functions:

Current_Timestamp: Returns current date and time. Syntax: CURRENT_TIMESTAMP.
DateAdd: Adds time/date interval to a date.Syntax: DATE_ADD(interval,number,date);
DateDff: Returns the difference between two dates. Syntax: DATEDIFF(interval,date1,date2);
GetDate: Returns the current database system date and time. Syntax: GETDATE();
Month: Returns month from date.Syntax: MONTH(date);
SysDateTime: returns date and time of computer where sql server runs.Syntax: SYSDATETIME();
IsDate: Check the expression, returns 1 if it is valid date otherwise 0. Syntax: ISDATE();

Examples:

3) String Functions:

ASCII: Returns the ASCII value for a character. Syntax: ASCII(character);
UNICODE: Returns the UNICODE value for a character. Syntax: UNICODE(character);
CHAR: Returns character based on ASCII value. Syntax: Char(code);
NCHAR: Returns character based on UNICODE value. Syntax: Char(code);
CHARINDEX: Returns position of substring in a string.If the substring is not found then it returns 0. Syntax: CHARINDEX(substring,string);
CONCAT: To add two or more strings together. Syntax: CONCAT(string1,string2,..stringn).
CONCAT with +: Using + operator, we can add two or more strings. Syntax: 'string1' + 'string2'.
CONCAT_WS: This function adds two or more strings together with a separator. Syntax: CONCAT(separator,string1,string2,..stringn).
LEFT: Extracts number of characters from a string (starting from left). Syntax: LEFT(string,num_of_characters);
RIGHT: Extracts number of characters from a string (starting from right). Syntax: RIGHT(string,num_of_characters);
LOWER: Converts a string to lower case. Syntax: LOWER(string);
UPPER: Converts a string to upper case. Syntax: UPPER(string);
LTRIM: Removes space from left side of string. Syntax: LTRIM(string);
RTRIM: Removes space from right side of string. Syntax: RTRIM(string);
REPLACE: Replace of all occurrences of substring with in a string with new string.Syntax: REPLACE(string,old_string,new_string);
STR: This returns a number as a string. Syntax: STR(number).
SUBSTRING: Extracts a character from a string. Syntax: SUBSTRING(string,start,length).
REVERSE: To reverse a string. Syntax: REVERSE(string);

Examples:

Chapter 14: SQL JOINS

Join clause is used to combine rows from two or more rows based on common column in the tables.
The main advantage of join is that it executes faster ,retrieve data efficiently from multiple tables.
The disadvantages of join is that there is no unique data means data gets repeated that leads to delete/update anomalies.
2 types of joins are in SQL. Those are inner join,outer join.
In outer join,there are 3 types of joins.Those are left join,right join,full join.
Default join is inner join.

1) Inner Join:

2) Left Join:

3) Right Join:

4) Full Join:

Creating Supplier, Customer,Product,Orders,OrderItems Tables:

Supplier Tables:

Product Table:

Customer Table:

Order Table:

OrderItems Table:

Chapter 15: InnerJoin

The inner join keyword selects the records that have matching values in both tables.

Syntax:

select columns from table1 inner join table2 on table1.column_name=table2.column_name;

Display orders with customer names:

Output:

Chapter 16: Left Join

The left join keyword returns all the records from left table and matched records from the right table.If there is no match it returns NULL.

Syntax:

select columns from table1 left join table2 on table1.column_name=table2.column_name;

Display orders with customer names:

Output:

Chapter 17: Right Join

The right join keyword returns all the records from right table and matched records from the left table.If there is no match it returns NULL.

Syntax:

select columns from table1 left join table2 on table1.column_name=table2.column_name;

Display all customer with orders:

Output:

Chapter 18: Full Join

The full join keyword returns all the records when there is match in either left table or right table records.

Syntax:

select columns from table1 full join table2 on table1.column_name=table2.column_name;

Display all the Customers and all the Orders:

Output:

Chapter 19: Self Join

The self join is a regular join, but the table is joined with itself.

Syntax:

select columns from table t1, table t2 where condition;

Display Customers from Same State:

Output:

Chapter 20: Union

The union operator is used to combine the result set of two or more select statements.
Each select statement have same number of columns with similar data type.
The columns in each select statement also be in the same order.
By default, union operator returns distinct values.
Union all allows duplicate values also.

Union Syntax:

Select column(s) from table1 union Select column(s) from table2;

Union All Syntax:

Select column(s) from table1 union all Select column(s) from table2;

Displaying city values from customer and supplier table using union:

Output:

Displaying city values from customer and supplier table using union all:

Output:

Chapter 21: Group By

The group by statement is used with aggregate functions(count,avg,max,min,sum) to group the result set by one or more columns.

Syntax:

Select column(s) from table_name

where condition

group by column_name(s)

order by column_name(s);

Group By single fields:

Output:

Group By multiple fields:

Output:

Group with having:

Output:

Chapter 22: Sql Keywords

Add: To add column in existing table.
Add Constraint: To add constraint ,if table is already created.
Alter: To add,delete,modify columns.
Alter Column: To change data type of column.
Alter Table: To add,delete or modify columns data in a table.
All: Returns true if the sub query value meets the condition.
Any: Returns true if any of the sub query value meets the condition.
And: return values when both conditions are true.
As: To give temporary name while retrieving.
Asc: To sort the result set in ascending order.
Backup Database: To create back up of an existing database.
Between: To retrieve values between given range.
Case: To create different outputs based on conditions.Syntax: case when condition1 then output1 when condition2 then output2 else output3.
Check: To check values at the time of inserting.
Column: To change the data type of column and to delete column in a table.
Constraint: To add constraint.
Create: To create database,table,index,view,procedure.
Database: To create or drop database.
Default: To add default value for column.
Delete: To delete rows from table.
Desc: To sort the result set in descending order.
Drop: To delete a column,constraint,table,view,database.
Exec: To execute stored procedure.
Exists: To test the existence of record in sub query.
Foreign key: A constraint used to link the tables.
From: Represents a table.
Full Join: To retrieve all the matched records from either left or right table
Group By:To group the result values.
Having: To check condition,instead of where with aggregate functions.
In: To specify multiple values in a where condition.
Inner Join: To retrieve matched records from both tables.
Is Null: To check empty values.
Is Not Null: To check non empty values.
Left Join: To retrieve all records from left table and matched records from right table.
Like: To search specific pattern.
Order By: To sort the result in ascending order or descending order.
Primary Key: To uniquely identify records in a table.
Select: Select data from tables.
Select into: To copy values from one table to another table.
Set: To set values at the time of updating record.
Table: To create a table or delete a table.
Truncate: To delete data in table instead of table itself.
Union: To combine the result set of two or more select statements(only distinct values).
Union All: Like union but it allows duplicate values.
Unique: To ensure all values to be unique.
Update: To update records in a table.
Values: To insert values into table.
Where: To retrieve values based on condition.

Chapter 23:

1) Order by: To sort data in ascending order or descending order.

Examples:

2) Select into: To copy data from one table and inserting into another table. Here select into statement creates new table.

Syntax:

select columns into new_table from old_table.

Code:

Output:

3) Insert into select: To copy data from one table and insert into another table. Here insert into statement inserts data into existing table.
Syntax:
insert into table2 select columns from table1.
Code:

Output:

4) Backup and its importance

Backup means a copy of sql server data that can be used to restore and recover data after a failure.
Copies the data or log records form sql server database.
MS SQL Server backups are mainly 3 types.

Full or Database.
Differential or Incremental(data that is new or has changed since that is full backup).
Transactional Log or Log(committed and uncommitted transactions).

We can backup data using T-SQL or SSMS(SQL Server Management Studio).

Steps to backup database:

Step1: Select database which is we want to backup and expand database folder, select Tasks and then Backup.. option.

Step2: After selecting Back up option, it will open new window. Select destination path (or) we can add new destination path.

Step3: Adding new destination path

Step4: Selecting new destination and click on ok button.

Step5: Shows success message.

Restoring Database:

Step1: Select database folder and select Tasks and then Restore and then Database option.

Step2: Select the backup file

6) SQL Case: To create different outputs based on conditions.

Syntax: case when condition1 then output1 when condition2 then output2 else output3.

Code:

Output:

7) NULL Function:

a) ISNULL():

In SQL server, ISNULL() function is used to replace null values.

Syntax:

Select columns, ISNULL(column_name,value_to_replace) from table_name.

Example:

b) NULLIF: This function takes two arguments, If the two arguments are equal then it will return NULL otherwise first argument is returned.

Syntax:

Select columns, NULLIF(argument1,argument2) from table_name.

c) COALESCE: It returns the first non-null expression among its arguments. If all the expressions are evaluate to null then the coalesce function return null.

Syntax:

Select columns, COALESCE(argument1,argument2,...argumentN) from table_name.

Example:

d) PATINDEX():

Returns the position of pattern in a string.
The first position in a string is 1.
If the pattern is not found then function returns 0.
The search is case insensitive.

Syntax: PATINDEX(%pattern%,string).

Example:

e) SUFF():

This function deletes a part of string and then inserts another part into the string, Starting at a specified position.
Syntax: SUFF(string,start,length,new_string).

Example:

f) Comments

Comments are used to explain the sql statements.
The statements in the comment section that won't execute.
Single line comments starts with --.
Multi-line comments starts with /* and ends with */.

Examples:

Single line comment:

Multi line comment:

Chapter 24

1) Exception Handling:

The exception is a problem that prevents the continuation of program.
We can handle the exceptions by using try and catch blocks.

Syntax:

Begin TRY

//Sql statements

End TRY

Begin CATCH

//Handling exception details

End CATCH

Types of Exceptions:

SQL Server contains following two exceptions.

1) System defined: The exceptions or errors are generated by system(divide by zero error)

2) User defined: This type of exception is user generated not system generated.

The following are the system functions and keywords used with in the catch block.

1) @@ERROR (Global variable)

2) ERROR_NUMBER()

3) ERROR_STATE()

4) ERROR_LINE()

5) ERROR_MESSAGE()

6) ERROR_PROCEDURE()

7) ERROR_SEVERITY()

2) Begin and End:

Begin and end blocks are used to group several statements to be executed together.
Control statements such as IF ELSE which affect the performance of only single sql statement.
But the statement block can affect the performance of whole group.

Syntax:

BEGIN

{ sql statement | statement block }

END

3) Commit and Rollback:

Commit is used for permanent changes.
We can't roll back after the commit.
Rollback is used to undo the changes made by ant command but only before a commit is done.

Rollback Syntax:

begin tran tranName

command for operation

rollback tran tranName

Commit Syntax:

begin tran tranName

command for operation

commit tran tranName

4)T-SQL:

Transact sql is an extension to sql.
T-Sql is used to declare variables,functions,views,triggers,stored procedures and transactions.
The extended features includes improved performance, increased functionality.

Difference between SQL and T-SQL

5) Print Command:

Print statement can be used to return messages to the client.
It takes input as string and returns string message to the application.
Basically print statement is used for troubleshooting the code by displaying message or displaying variable value.
Syntax: PRINT string|function.

Examples:

6) Variables:

The T-Sql local variable is an object that can hold single data value for specific datatype.
Local variable is available only in the batch(group of sql statements) created.
T-Sql does't support global values.
To declare a variable, we have to use declare statement.
To assign a values to a variable, we have to use set statement.
Syntax: declare <@var_name> datatype.
Ex: declare @i int (or) declare @i as int.

Code:

Output:

7) IF-ELSE & ELSE-IF:

Syntax:

if boolean_expression

{ sql statements | statement block }

else if boolean_expression

{ sql statements | statement block }

[ else

{ sql statements | statement block } ]

Example:

Output:

8) while:

Example code:

Output:

9) Stored Procedure: To save code in the database.

Syntax:

create procedure proc_name

begin

< sql statement >

end

Chapter 25: user defined functions

1) Why and its use and importance

We can create functions to perform an action with parameters.
We can create a function once and store it in database and call it any number of types.
It is similar to stored procedure.

Types of functions:

1) Scalar function

User defined scalar function returns single value of the type defined in returns clause.

2) Table valued function

This function returns a table data type that can read from in the same way as we would use a table.

a)Inline table valued function

This functions have no body, The table is the result set of single select statement.

b)Multi-statement table function

Example on Scalar function:
Write a function to get the customer sales as of a particular date.
1) Retrieving based on price

2) Retrieving based on quantity

Example on Table-valued function
Write a function to print multiplication table.
Code:

Output:

Chapter 26: Sql views

View: A view is a virtual table based on the result set of an sql statement. It contains rows and columns like real table in the database.

Syntax:

create view view_name as

select column1, column2..

from table_name

where condition.

Difference between view and table:

A table contains data, view contains the result of select statement that is saved in the database.
The advantage of view is, we can join data from multiple tables.
Instead of sending complex query to database all the time, we can execute query in views.

Create a view to get the product and supplier details:

Code:

Output:

Chapter 27: Sql Commands

Sql commands are instructions, coded in sql statements, which are used to communicate with the database to perform specific tasks like create tables, add data to tables, modify data, drop the table and set permissions to users.

Data Definition Language(DDL):

These sql commands are used for creating, modifying and dropping the structure of objects.
The commands are CREATE, ALTER, DROP and TRUNCATE.

Data Manipulation Language(DML):

These sql commands are used for storing, retrieving ,modifying and deleting data.
The commands are SELECT, INSERT, UPDATE and DELETE.

Data Control Language(DCL):

These sql commands are used for providing security to database objects.
The commands are GRANT and REVOKE.

Transaction Control Language(TCL):

The commands are used for managing changes affecting the data.
The commands are COMMIT,ROLLBACK and SAVEPOINT.

Designing Bike Selling Database:

SQL Index:

Indexes are used to retrieve data from database very fast.
Simply it is a pointer to data in a table.
Users can't see indexes.
They are used to speed up the searches/Queries.

Create index syntax:

create index index_name

on table_name(column1,column2);

Creating on single column:

Creating on multiple columns:

Drop index: To delete an index in a table, we use drop statement.
Syntax:
drop index index_name on table_name;

Example:

SQL Triggers:

A trigger is a special type of stored procedure that automatically executes when an event fires or occurs in the database server.
DML triggers executes when a user tries to modify data through DML commands.
DML events are INSERT,UPDATE & DELETE statements on a table or view.
DDL triggers executes when a database object is created,altered or dropped from the database.

Syntax:

CREATE [or ALTER] TRIGGER trigger_name

ON {TABLE | VIEW}

{FOR | AFTER | INSTEAD OF}

{ [INSERT] [ , ] [DELETE] [ , ] [UPDATE] }

{ SQL STATEMENT}

DDL Trigger:

DML Triggers are two types:

After Triggers

After triggers are executed after the action of insert,delete or update statements.

Example:

Instead of Triggers

Instead of trigger tells execute the trigger instead of executing the statement.

SQL Stored Procedures:

The sql server stored procedure is used to save time and to write code again and again by storing the same in database and also get the required output by passing parameters.

Syntax: create procedure proc_name

begin

< sql statement >

end

go

Example:

Stored procedures with parameters:
Using SSMS:

Using Command:

Output:

ACID Properties in SQL:

Acid properties in sql server ensures data integrity(maintenance) during a transaction.

ACID is an acronym for Atomicity, Consistency, Isolation and Durability.

Atomicity: It means, All the operations inside a transaction is either successfully completed or they were roll backed.
Consistency: Guarantees that a transaction never leaves database in half-finished state.
Isolation: Every transaction is individual, and one transaction can't access the result of other transaction until the transaction is completed.
Durability: Once the transaction is successfully completed then the changes it has made to the database will be permanent.

SQL Server Agent:

SQL Server Agent also known as SQL Agent, is a Microsoft SQL server relational database management system(RDBMS) background tool.
SQL Agent allows the database administrator(DBA) to schedule automated execution jobs or other database tasks such as backups.
It runs as a windows service only.
It can start automatically when system boots or it can be started manually.
It allows for the handling of different tasks such as backup, database replication, job scheduling, user permissions and database monitoring.