1.DBMS Keys 1.Primary 2.Foreign 3.Unique 4.Composit
5.Candidate 6.Alternate 7.Super
2.Constrants 1.Not Null 2.Unique 3.Primary Key 4.Foreign Key.5.Check 6.Default
3.Indexes 1.Clustered 2.Non-Clustered .3.Unique 4.Full-text 5.Xml. 6.Spatial
4.Operators 1.Arithmatic 2.Bitwise 3.Comparison 4.Logical
5.Compund
5.Function 1.System Define 1)String
2)Numeric 3)Date 4)Aggregate 5)Advance
2.User Defined 1)Scaler Value 2)Table Value
6.Procedure 1.Procedure without
parameter 2.Procedure with Input
Parameter
3.Procedure with output Parameter 4.
Procedure with Optional Parameter
5. Procedure with Return Value
7.Trigger And Magic Table 1)For Trigger
2)Instead Of Trigger
8.Join 1.Inner
Join/Join 2.Outer Join 1.Left Join/Left
Outer Join 3.Right Join/Righ Outer Join 4.Full Join/Full
Outer Join 3.Cross Join
9.Union And Union All
10.View 1.System Defined
View a)Information Schema b)Catalog View
2.User Defined View a)Single Table View b)Two Table View
11.Common Table Expression(CTE)
12.Temporary Table
1.Local Temp Table 2.Global Tamp
Table
13.Sql Variable
Keys:- Keys
in Database Management System(DBMS) is an attribute or set of attributes which
helps you to identify a row(tuple) in a relation(table). They allow you to find
the relation between two tables. Keys help you uniquely identify a row in a
table by a combination of one or more columns in that table.
Database Keys are used to establish and
identify relationships between tables and within a table.
Different Types of Keys available in
SQL-Server.
1.Primary Key:- Primary key is a field that is uniquely identify each
record in a database table. Primary key cannot be null. A table can have only
one primary key.
Note:-
1.A key can be
primary key and also foreign key at the same time like in One to One Relationship.
2.A table can have more than one
primary key in the form of Composite key.
Ex:-In bridge table used for
Many to Many Relationship.
2.Foreign Key:- A field that is a primary key in a database table called
foreign key in another table. Foreign key can be null or
duplicate.
OR
A foreign key (FK) is a column or
combination of columns that is used to establish and enforce a link between the
data in two tables to control the data that can be stored in the foreign key
table.
Note:-
1.The table with the foreign key is
called the child table, and the table with the primary key is called the
referenced or parent table.
Ex:- Department and Employee are with
one-to-many relationship. Department can have many Employee so Department is
Parent Table and Employee will be child table. And DepartmentId will be Primary
key in Department Table and Foreign key in Employee Table.
Other example are Customer and Order. A
Customer can place many order at a time. So Customer will be primary table and
Order will be child table. And Customer and Order make a one-to-many
relationship.
2 Foreign key Column can be Identity
Column but never do that because:-
First remember a Foreign key simply
requires that the value in that field must exist first in a different table
(the parent table). That is all an FK is by definition. Null by definition is
not a value. Null means that we do not yet know what the value is.
3.Unique Key:- Unique key is a set of one or more than one field in a
database table that is used to prevent duplicate record in a field. A unique
key can accept only one null value.
4.Candidate Key:- Those keys that fulfill the requirement of primary key
is called candidate key. Every table must have at least one primary key but at
the same time can have several candidate key.
5.Alternate or
Secondary Key :- All keys
accepts primary key are Alternate keys. Alternate keys are candidate keys that
is not selected as primary key.
Note:- In a database table for retrieve
data we need a key and select some rows as key with some condition and these
keys are called candidate key. Candidate key is a single key or a group
of multiple keys that uniquely identify rows in a table then select primary key
after that secondary key or alternate key which is left from making primary
key.
6.Composite
Key:- A key that is
composed of more than one column. When primary key consists of multiple fields
then it theoretically called Composite Key.
7.Super Key:- Subset of Primary key, Unique key, Alternate key.
Super Key can contain multiple attributes that might not be able to
independently identify tuples in a table, but when grouped with certain keys,
they can identify tuples uniquely.
Constraints:- Constraint can
be used to specify the limit on the data type of table. Constraint can be
specified while creating or altering the table statement. Example of constraint
are.
- NOT NULL.
- CHECK.
- DEFAULT.
- UNIQUE.
- PRIMARY KEY.
- FOREIGN KEY.
Indexes:- Indexes are used to speed-up query process
in SQL Server, resulting in high performance. They are similar to textbook
indexes. In textbooks, if you need to go to a particular chapter, you go to the
index, find the page number of the chapter and go directly to that page.
Without indexes, the process of finding your desired chapter would have been
very slow.
The same applies to indexes in databases. Without
indexes, a DBMS has to go through all the records in the table in order to
retrieve the desired results. This process is called table-scanning and is
extremely slow. On the other hand, if you create indexes, the database goes to
that index first and then retrieves the corresponding table records directly.
There are two types of Indexes in SQL Server:-
- Clustered
Index:- A clustered index
defines the order in which data is physically stored in a table. Table
data can be sorted in only way, therefore, there can be only one clustered
index per table. In SQL Server, the primary key constraint automatically
creates a clustered index on that particular column.
- Non-Clustered
Index:- A non-clustered index
doesn’t sort the physical data inside the table. In fact, a non-clustered
index is stored at one place and table data is stored in another place.
This is similar to a textbook where the book content is located in one
place and the index is located in another. This allows for more than one
non-clustered index per table.
It is important to mention here that inside the table
the data will be sorted by a clustered index. However, inside the non-clustered
index data is stored in the specified order. The index contains column values
on which the index is created and the address of the record that the column
value belongs to.
Sql
Operators:-
1.Arithmetic
Operators(+,-,*,/,%)
2.Bitwise
Operators(&,I,^)
3.Comparison
Operators(=,<,>,>=,<=,<>)
4.Logical
Operators(AND,ANY,BETWEEN,EXIsTS,IN,LIKE,NOT,OR,ALL)
5.Compound
Operators()
Function:- In Sql-Server
function are database object accept the input parameter and return a value.
Functions are of two types
1.System Defined
- String Functions
- Numeric
Functions(CEILING,ROUND,FLOOR,RAND)
- DateFunctions(GetDate,DatePart,DateAdd,DateDiff,Convert,IsDate(),Day(),Month(),Year(),)
- Aggregate
function(Min(),Max(),Avg(),Sum(),Count())
- Advanced Functions
Windows Function(ROW_NUMBER(),RANK()
and DENSE_RANK()):-Like aggregation functions, window functions perform
calculations based on a set of records – e.g. finding the average salary across
a group of employees.
2.User Defined
1.Scaler
Function:- Only return a single value.
CREATE FUNCTION GetStudent
(
@Rno INT
)
RETURNS VARCHAR(50)
AS
BEGIN
RETURN (SELECT Name FROM Student WHERE Rno=@Rno)
END
-- To Execute Procedure
PRINT dbo.GetStudent(1)
Output: Ram
2.Tabular
Function:- Return a table
create function fnreturnmarks
(
@sname varchar(50)
)
returns @tempmarks table
(
sname varchar(50),
subname varchar(50),
marks int
)
as
begin
Insert @tempmarks select sname,subname,marks from Student where Name=@sname
return
end
Procedure:- Procedure are
precompile Sql statement that is stored in sql-server database and can be
reused.
OR
A stored procedure is a group of
Transact-SQL statements compiled into a single execution plan or in other words
saved collection of Transact-SQL statements.
In Sql server every query requires a
query plan before it is executed. When we run a query first time, query gets
compiled and a query plan is generated. This query plan is then saved in sql
server query plan cache.
An execution plan in SQL Server is a
simple graphical representation of the operations that the query optimizer
generates to calculate the most efficient way to return a set of results.
Advantages of Stored
Procedure:-
- Execution plan retention and
Reusability.
- Better Security:-Limit direct
access of table.
- Portability:-Can be used in
multiple client application,written in any language.
- Reduced network traffic because
procedure are fast.
- Avoid Sql Injection Attacks.
Types Of
Procedure:-
System Defined:- sp_help, sp_helptext, sp_rename, sp_helpdb, sp_who,
sp_helpindex.
User Defined:-
1.Procedure without parameters :-
create procedure spGetallemployee
as
begin
select * from Employee
end
2.Procedure with
optional parameters :- A
parameter is considered optional if the parameter has a default value specified
when it is declared. It is not necessary to provide a value for an optional
parameter in a procedure call.
Ex-
create proc usp_SearchEmploye
(
@name varchar(50)=NULL,
@gender varchar(50)=NULL
)
as
begin
select * from tblEmployee where (Name=@name or @name is NULL ) AND(Gender=@gender or @gender is NULL)
end
3.Procedure with
input parameter:- Input parameter is a
parameter whose value is passed into a stored procedure/function module. The
value of an IN parameter is a constant; it can't be changed or reassigned
within the module.
create procedure spSearchemployee
(
@firstname nvarchar(50),
@gender nvarchar(50)
)
as
begin
select * from Employee where firstName=@firstname and Gender=@gender
end
4.Procedure with
output parameter:- The
Output Parameters in Stored Procedures are used to return some value or values.
A Stored Procedure can have any number of output parameters.
Note:- In output parameter we define a
parameter for output value so that it's called procedure with output parameter.
We can use input and output parameter at a time in procedure.
Ex-
@No1 INT,
@No2 INT,
@Result INT OUTPUT
)
AS
BEGIN
SET @Result = @No1 + @No2
END
-- To Execute Procedure
DECLARE @Result INT
EXECUTE spGetResult 10, 20, @Result OUT
PRINT @Result
Ex-
CREATE PROCEDURE spGetResult2
(
@No1 INT,
@No2 INT
)
AS
BEGIN
return (@No1 + @No2)
end
-- To Execute Procedure
Execute @Result=spGetResult2 20,20
Select @Result
5.Procedure with
Return Value :- This stored procedure
does not use a variable to return the return code, simply use return
keyword.
create proc spGetTotalEmployeeCount
as
begin
return (select count(employeeId) from Employee)
end
-- To Execute Procedure
Declare @TotalEmployees int
Execute @TotalEmployees=spGetTotalEmployeeCount
Select @TotalEmployees
Note:-
1. Output vs Return
- If it is a single integer value,
you can use the return statement.
- If you have a non-integer value,
or a number of scalar values, you can use output parameters.
- output parameter use a variable
for return a value while return procedure do not use a variable it simple
return the value with return keyword.
2. There are many tricks that can be
used when you write T-SQL code. One of these is to reduce the amount of
network data for each statement that occurs within your stored
procedures. Every time a SQL statement is executed it returns the number
of rows that were affected. By using "SET NOCOUNT ON" within
your stored procedure you can shut off these messages and reduce some of the
traffic.
You can still use @@ROWCOUNT to get the
number of rows impacted by a SQL statement, so turning SET NOCOUNT ON will not
change that behavior.
Differences between
function-and-procedure:-
- Function must return a value while
in Procedure it is not necessary.
- Function accept only input
parameter while Procedure accept input and output parameter.
- you can invoke functions inside a
stored procedure and you cannot invoke a stored procedure inside a
function.
- An exception can be handled by
try-catch block in a Procedure whereas try-catch block cannot be used in a
Function.
- The schema name is mandatory to
invoke a function, You can invoke using exec or execute and even you can
invoke the stored procedure without the execute statement. You do not
necessarily need to specify the schema name.
Trigger:- Triggers
can be define on view, schema, or database with which the event is associated.
We can disable ,enable and drop a trigger manually and trigger can be applied
on table level and column level.
Triggers can be written for the
following purpose.
- Gain strong control over security.
- Enforcing referential integrity.
- Event logging and storing information
on table access.
- Auditing.
- Synchronous replication of table.
- Preventing invalid transactions.
- Trigger use for Data Integrity.
- Trigger have database scope and
server scope.
Trigger is a special type of stored
procedure that is fired automatically when an event executed in the database
server .
There are Three Types Of Trigger
For Trigger(After), Instead Of(Before)
Trigger ,Logon Trigger
1.For
Trigger(After):- User action and
trigger action will be executed. But trigger action will be fired after the
execution of user action.
OR
These kinds of triggers fire after the
execution of an user action that can be either DDL statements like Create,
Alter and Drop or DML statements like Insert, Update and Delete.
1.Ex-
create
trigger saftey
on
database
for
create_table,alter_table,drop_table
as
print
'you can not create ,drop and alter table in this
database'
rollback;
2.Ex-
create trigger deep
on emp
for
insert,update,delete
as
print 'you can not insert,update and delete this
table i'
rollback;
3.Ex-
create
trigger insertt
on
emp
after
insert
as
begin
insert
into empstatus values('active')
end
4. Disable all Trigger on a
Table Or a Single Table
Disable
trigger all on Customer
Disable
trigger Phantom
on Customer
5. Enable all Trigger on a Database
Enable
trigger all on Customer
Enable trigger Phantom on
Customer
6. Drop Trigger
Drop
trigger Phantom
2.Instead of(Before):-
Trigger action will be executed but user action not executed. These kinds of
triggers fire before the execution of user action that can only be DML
statements like Insert, Update, Delete.
INSTEAD OF triggers, as their name
implies, run in place of the DML action which caused them to fire.
create
trigger deep
on
emp
instead
of
insert,update,delete
as
print
'you can not insert,update and delete this table i'
rollback;
Note:- In Instead of trigger we can
apply condition but not in for trigger.
3.Longon Events:-
Logon trigger is fired when a LOGON event occurs i.e. when a user session is
being established
4.Magic Tables:-
Usually, inserted and deleted tables are called Magic Tables in the context of
a trigger. These tables are automatically created and managed by SQL Server
internally to hold recently inserted, deleted and updated values during DML
operations (Insert, Update and Delete) on a database table.
Note:- INSERTED magic table is
populated with INSERT and UPDATE operations and DELETED magic table is
populated with UPDATE and DELETE operations.
Create trigger Trigger_FroInserMagic
on Customer
For Insert,Update
As
Begin
Select * from INSERTED
End
Create trigger Trigger_FroDeleteMagic
on Customer
For Delete,Update
As
Begin
Select * from DELETED
End
5.Procedure vs Trigger:-
·
A stored procedure is
a user defined piece of code written in the local version of PL/SQL, which may
return a value (making it a function) that is invoked by calling it explicitly.
·
A trigger is a stored
procedure that runs automatically when various events happen (eg update,
insert, delete).
Join:- Join retrive the data from two or multiple table in a
combine form.
Syntax:-
Select ColumnList
From
LeftTable JoinType RightTable On JoinCondition
1)Inner Join(Join):-
Return all the matching record from both table.
2)Left Join(Left
outer join):- Return all the
record form left table and match record from right table. The result is NULL
from the right side, if there is no match.
3)Right join(Right outer join):- Return all the record form right table and matched record
form the left table. The result is NULL from the left side, when there is no
match.
4)Full Join(Full Outer Join):- Return all the record from both table even if only one
match in right table or left table. Combination of right join and left join.
5)Cross Join:-
Combine each row of the first table with each row of the second table. It is
also known as the Cartesian join since it returns the Cartesian product of the
sets of rows from the joined tables.
6)Self Join :-
Joining a table with itself is called self-join. Self-join can be classified
as
- Inner Self Join
- Outer Self Join (Left, Right, Full)
- Cross Join
Union
and Union All:- Union and Union all operator retrieve the data from
two or multiple table but the difference is union remove duplicate data while
union all not remove duplicate data. And Union performing a sorting over data.
SELECT
* FROM Employee
UNION SELECT * FROM Customer
Difference between Join-and-Union:-
·
JOIN combines data
from many tables based on a matched condition between them.SQL combines the
result-set of two or more SELECT statements.
·
In Join datatypes of corresponding columns
selected from each table can be different. In Union datatypes of corresponding
columns selected from each table should be same.
Difference between Union-and-Union All :-
Union command and Union All command retrieve
the data from two or more table .Union command remove the duplicate value from
record while Union All cannot remove duplicate record.
View:- Views
are virtual tables that hold data from one or more tables. It is stored in the
database. A view does not contain any data itself, it is a set of queries that
are applied to one or more tables that are stored within the database as an
object.
Views are used for security purposes in
databases. Views restrict the user from viewing certain columns and rows. In
other words, using a view we can apply the restriction on accessing specific
rows and columns for a specific user. A view can be created using the tables of
the same database or different databases. It is used to implement the security
mechanism in the SQL Server.
A. Types of View
1.User Defined View:- User
Defined view defined by a programmer.
2.System Defined View:-
SQL Server also contains various predefined databases like Tempdb, Master,
temp. Each database has their own properties and responsibility. Master data is
a template database for all other user-defined databases. A Master database
contains many Predefine View that work as templates for other databases and
tables. Master databases contain nearly 230 predefined views.
These predefined views are very useful
to us. Mainly we divide system views into the following two parts.
·
Information Schema
·
Catalog View
B. Security Feature of View
1.With option Check:-
It is used that insert and delete command must satisfy the condition defined in
View
2.SchemaBinding:-
When we used schema binding option in view we cannot change the structure of
real table.
3.Encription:-
Prevent running of sphelp_text command on view
C. Uses of View
· Security: Restrict
access to specific row or column in a table.
· Combine similar data
from different server.
· Partition Data.
· Reporting.
D. Example Of View
1.Single table view:-
CREATE VIEW View_EmpDetails
AS
SELECT
NAME,AGE,SALARY
FROM Employees
2. Two table View:
CREATE VIEW View_EmpDetails1
AS
SELECT
Employee.name,Employee.age,Contact.number FROM Employee,Contact
CTE:- Common
Table Expression is a temporary result set that can be referenced within DML(Select,
Insert, Update, Delete) statement. CTE is not stored as object in database
because their lifespan is equal to the execution time of query. As soon as
query remove from database they are remove from database memory. CTE can be referenced
in a query as many times we wants and they can be self-referencing.
Ex:
WITH Student_CTE
As
(
SELECT * FROM
student WHERE DOB <'05-25-1989'
)
SELECT * FROM
Student_CTE
Advantage of CTE:- Common table expressions operate similarly to
subqueries. CTE can be more readable: Another advantage of CTE is CTE are more
readable than Subqueries. Since CTE can be reusable, you can write less code
using CTE than using subquery. Also, people tend to follow the logic and ideas
easier in sequence than in a nested fashion.
Temporary Table:- Temp
or temporary table store a subset of data from a normal table for a certain
period of time(whenever session is open).Temp table are useful when we have
large number of records in a table.
A.There are two types of Temp Tamp Table
1.Local Temp table:-
Only be accessed locally inside the connection that created it.There is one
exception to that rule. When you are creating stored procedures, you can access
temporary tables in other connections as well.
2.Global Temp table:-
Accessible to all the open connections. The name of the global temporary table
starts with a double hash symbol (##).
Example:-
SELECT NAME,AGE,GENDER INTO #MALESTUDENT FROM
STUDENT WHERE GENDER='MALE';
SELECT * FROM #MALESTUDENT;
B. Difference between View and Tempt able
Each time you query a view, it executes
the underlying select statement and retrieves the data from the base tables.
(Indexed Views operate slightly differently, but their relevance seems to be
outside the scope of your question).A view, in general, is just a short-cut for
a select statement.
A temp table is a completely separate
database object. It gets populated with rows when you insert them into it. It
has no ongoing relationship to any query that may have been used to insert data
into the temp table. A temporary table actually stores the results.
So, when you query a view, the results
will show data from the underlying base table even if they've been updated
recently. When you query a temp table, the results will show data as it was put
into the temp table; if the table originally used to populate the temp table
changes, you will not see those changes in the temp table
SQL Variable declaration:- Firstly, if we want to use a variable in SQL Server, we have
to declare it. The DECLARE statement is used to declare a variable in SQL
Server. In the second step, we have to specify the name of the variable. Local
variable names have to start with an at (@) sign because this rule is a syntax
necessity. Finally, we defined the data type of the variable. The value argument
which is indicated in the syntax is an optional parameter that helps to assign
an initial value to a variable during the declaration. On the other hand, we
can assign or replace the value of the variable on the next steps of the batch.
If we don’t make any initial value assigned to a variable, it is initialized as
NULL.
Syntax:-
DECLARE
{ @LOCAL_VARIABLE data_type [ = value ] }
Example:-
DECLARE
@TestVariable AS VARCHAR(100)='Save Our Planet'
Print the value of variable:-
PRINT
@TestVariable
Assigning a value to SQL Variable
SQL Server offers two different methods
to assign values into variables except for initial value assignment. The first
option is to use the SET statement and the second one is to use the SELECT
statement. In the following example, we will declare a variable and then assign
a value with the help of the SET statement:
DECLARE
@TestVariable AS VARCHAR(100)
SET
@TestVariable = 'One
Planet One Life'
PRINT
@TestVariable
In the following example, we will use
the SELECT statement in order to assign a value to a variable
DECLARE
@TestVariable AS VARCHAR(100)
SELECT
@TestVariable = 'Save
the Nature'
PRINT
@TestVariable
Note:- you cannot SET the variable until you
DECLARE it.
No comments:
Post a Comment