Category: SQL Server Tutorial

Encryption in SQL Server  :-

• If  view created with encryption then view defination will be hidden in information_schema views table.

Example  :-  create view v8 with encryption as select empno, ename, sal from emp

                                                             ↓

                         Select * from information_schema.views.

Drop views  in SQL:

Drop view v8

• If drop the views ,it’s not effected in the base table,only deleted from information_schema views

Note  :–      (I) If Base table is dropped, so views is not dropped. Because view is an independent object.

                      (II) But in case, the view is not accessed.

Schema Binding  in SQL:-

• When view created by schema binding then view becomes dependent object on base table.
• The base table can’t be dropped without dropping view.

Example  :- Create view v8 with schema binding as select empno,ename,sal,from dbo.emp

Note  :–   When schema binding is used then table name prefixed by user name. Example :- dbo.emp

Example  :-

•  Drop table emp
•          ↓
• Invalid because view is dependent obj,so we first drop the view in this Case.
•          ↓
• Drop view v8
•           ↓
• Drop table emp.

Synonym  in SQL  :–

• A synonym is another name(or) alternative name for a table.

• Synonyms are created

(i) If table name is lengthy.

(ii) To access table without owner name.

Syntax  :-

•                  Create synonyms <name> for <tabname>

Example  :-

•                  Create synonym s1 for emp.
•                   Select * from s1.

DBO user                                                  NIT   user

      ↓                                                                             ↓

 Table name                                                  Table name

       ↓                                                                            ↓

    Emp                                                                  Emp

       ↓                                                                            ↓

Grant select,insert on emp to NIT  (→)  Select * from dbo.emp

                                                                                       ↓

                                                                         Create synonym s1 for dbo.emp

                                                                                        ↓

{here,to access table without owner name}  ←   select * from s1

Difference between View and Synonym :-

                   View                                                                                  Synonym

View is a subset of a table.                                         Synonym is a mirror of a table.

View can be based on more than one table        Synonym is always based on one table.                       

Dropping Synonym in SQL :-

• Drop synonym s1.

Complex Views in SQL Server:-

Views are based on more than one table and also based on

• Group by clause
• Having clause
• Distinct clause
• Aggregate   function
• Expression.

Then view is said to be “complex view”.

Difference between simple view and complex view in sql:

Simple view

• It is updatable  {updatable means allow DML operations}

complex view

• but complex views are not updatable.    

Example : 

Create view v5 as select e.empno, e.ename, e.sal, d.deptno ,d.dname from  e.emp , d.dept.

• Query fired on view takes more times than query fired on base table.

                      Views degrades performance

• Select * from information_schema.views ( This maintains information about the views created by user)

Simple and Complex Views in SQL:-

Grant & Revoke Using grant & revoke we can restrict user operations.

Views Using views we can restrict data [particular column]

A view is based on one table is called “simple views”.

Syntax :-

Create view <name> [<with options>] as select statement [where<condition>]

Example :-

Select user_name –> Dbo

Example of simple view   :-

Create view  v1 As Select empno, ename, sal from emp.

When the above view is created,the select statement is stored in database that is why view is also called  “stored-query”.

• Grant select,insert,update,delete on v1 to NIT.
• Go to nit user
• Select * from v1

Once permission are granted to user the user can perform DML operators on base table through view.

{  Note  :-What ever changes the user, it is effected on base table {i:e.dbo}  }

Example  :-

• Create view v2 as (it can be created on base table only) select * from emp where dept no=10.
• Grant select, insert, update, delete on v2 to NIT. 
• Create  view v3 as Select empno, ename, sal, deptno, from emp where deptno=10.

with check option in view in SQL:-

• If view created with check option any DML operation through that view violates that where condition than DML operation is not accepted.

Example  :-  create view v4 As Select empno,ename,sal,deptno From  emp Where deptno =10 With check option

• Insert into v4 values(8787,’abc’,6000,10)
• Insert into v4 values (8788,’xyz’,9000,20) {invalid,because view created by with check option}

 DATABASE OBJECTS :-

 The different database objects are:

• Tables
• Views
• Synonyms
• Indexes
• Indexed views
• procedures
• Functions
• DB triggers

VIEWS   :-

•  A view is a “ subset of a table” and also called “virtual table” (or) “stored query”.

• Views  are mainly created for Security and Simplicity.

• By creating views, specific columns & specific rows can be granted to users.
• Views are two types  :-

(A)  SIMPLE VIEWS

(B)   COMPLEX VIEWS.

GRANT COMMAND :–   

Grant command is used to be permission on a table to users.

Syntax:-

Grant <privilages> on <tabname> To <users> [with grant option]

The privileges are: Select, insert, update, delete.

Grant select,insert on emp to NIT. (This command is dbo is give permission to NIT table for select & insert)

go to NIT table                     

Select * from emp

• user         user
•  dbo            nit 
•  granted        granted

Grant select on emp to NIT,X,Y -> This command is grant to more tables.

WITH  GRANT OPTION  :-

If  permissions are granted with grant option then grantee can also grant permission to other users.

Example :-   grant  select, insert on emp to nit with grant option.

User                 user           user      

 dbo                  nit                X

    ↓                    ↑                  ↑           → with grant option

——–emp—————emp—-

           Table                     (select,insert)

      (select,insert)

REVOKE COMMAND  :-

Revoke command is used to take back the permission from user.

Syntax:-

Revoke <privilage> on <tabname>from <users>

In dbo user  : 

Example :-  revoke select,insert on emp from nit cascade

Note :-  

When you give the with grant option,you must specify the cascade.

When you give the grant option only,you mentioned only revoke.

Revoke can be used to particular privileges take back the permission from user.

                           ↓

Revoke      insert  on  emp     from      nit

  Dbo                              NIT

  Emp                              emp

   ↑                                    ↑

   ———-emp————–  
                               ↓

If  you want to see the date in NIT table

                               ↓

Select * from dbo_emp

DATABASE SECURITY  :-

The different facilities in SQL Server to provide security :-

1. Logins  and users
2. Great & revoke commands
3. Views.

(I) Logins & users  :-

Creating logins :-

In sql server the users are two types  :

1. Window user
2. Sql Server user

(i) Creating windows user :-

• Open control panel
•               ↓
• Open user accounts
•                 ↓
• click create a new A/C – option
•                 ↓
• Enter user name
•                 ↓
• Click next
•                 ↓
•  Select limited
•                 ↓
• Click create A/C button

If this user wants to connect to sql server,then first the user has login to the operating system & open sql server & select window Authentication.

(2)  SQL SERVER   :-

Only system adminstrator[sa] is having permission to create logins & users
to create a new login,

•               In object explorer
•                         ↓
•               Select properties
•                          ↓
•               Select server roles
•                           ↓
•                And select system admin

Creating Users  :-

After connecting to sql server to access database are user must created in th database.
Only sa can create users.

• To create a user,expanded database.
•           ↓
•  Expanding  searify folder,
•            ↓
• Select users
•             ↓
•  Select new user
•              ↓
• Give user name:NSI
•               ↓
•  Login name :scott
•                ↓
•    Click   ok

Select user_name ( ).
To give the permission to user,so

•       Select the User
•           ↓
• Right click select properties

•            ↓
• Database role membership
•            ↓
• Select the permission given to user
•            ↓
• Click ok

Example:  Db_ddladmsn

                          ↓

                         Ok

DATABASE TRANSACTIONS :-

A transaction is a unit of work,mainly consist of DML operations which must be made permanent as a whole and must be under as a whole.

SQL server runs in 3 types of transaction modes :-

• Auto commit mode [default]
• Implicit transaction mode.
• Explicit transaction mode.

(I) Auto commit mode :- {default}

• In auto commit mode,the transaction are starts with “DML operations”.
• After executing dml command the transaction ends automatically with “commit”.
• In auto commit mode,user can’t control transaction.

(II) Implicit transaction mode :–

• In implicit transaction mode a transaction starts with DML operation & A transaction ends with “commit/rollback”.

 Example  :–

Insert → transaction starts

Update

Commit→ transaction ends

• If transaction ends with “commit” then the operators performed in transaction(or)recorded in the database.

Example :-

Insert → transaction starts

Update

Rollback → transaction ends

• If transaction ends with rollback the changes mode to the database one
• To run SQL Server in implicit transaction mode executing the following command :-

Set implicit_transaction on

(III) Explicit transaction mode :-

• To run sql server in implicit transaction mode executing the following command.

Set implicit_transaction on

• If u want to run on auto commit mode.

Set implicit_transaction off

• In this trasanction starts with begin tansaction statement and ends with commit/rollback transaction.

Example   :-

(1)  Begin transaction

Insert

Update

Commit transaction

(2) begin transaction

Insert

Update

Rollback transaction

• Auto commit             implicit                                  explicit 
• Update                              Update                                      begin transaction
•                                             Commit/rollback                  update
•                                                                                                   Commit transaction/rollback Transaction

 Example  :-  Insert  transaction starts

Update

Commit  transaction ends

Rollback  transaction ends → Invalid [useless]

Note:-  If commit is ends,after rollback is useless similarly if rollback is ends,after commit is useless.

SAVE TRANSACTION  :-

• The save transaction is used to break a long transaction into small parts.
• Using this we can cancel part of the transaction:

Example  :-

                  Insert

                  Update

                   Save transaction st1

                   Update  

                   Delete

                   Rollback transaction st1

Advantage   :

“Only part of the transaction can be cancelled”

SCALAR QUERIES  :-

subqueries follows select clause are called “scalar queries”

Syntax  :-

Select (select statement) , (select statement) From<tabname>

Example  :-

Select (select max(sal) from emp where deptno =10) D10, (select max(sal) from emp where deptno=20) D20, (select max(sal) from emp where deptno =30) D30

• Output:-        D10          D20               D30
•                          6000       5000           4000

Display max salary of top3 & top3 of minimum salary

Select (select distinct sal from (select ename, sal, dense_rank ( ) over (order by sal desc) rnk from emp) where rnk<=3) max, (select distinct sal from (select ename, sal, dense_rank  ( ) over(order by sal) rnk from emp) where rnk<=3) min

Output →                                   Max                              Min        

                                                        3000                           9000

                                                         2500                          800

                                                         1500                          700

Derived tables   :-

• This is introduced from SQL Server 2005.
• Sub queries follows from clause are called “Derived tables”.

Syntax :

Select * from (select statement) as alias.

• Use derived tables to simplify the complex processing.
• To use result of one query in another query.

Example  :-

Select * from (select empno,ename,sal*12 annsal from emp) as e where annsal >2000.

Display top maximum 3 salaries in emp table :-

Select distinct sal from (select ename,sal, dense_rank ( ) over (order by sal desc) rnk from emp) as e where rnk <=3

{to remove this one, this is top min 3 sal}

Display department wise top maximum 3 salaries in emp table :-

Select distinct deptno,sal from (select ename,sal,deptno,dense_rnk ( ) over (partition by dept no order by sal desc rnk from emp) as e Where rnk c=3 order by deptno,sal desc

sales 1, sales 2, cust →

Select * from (select * from sales 1 union all select * from sales 2) as s, cust c where s.cusid=c.cusid

Co-related sub-queries  :-

• In co-related sub-queries execution starts from outer query for every record of outer query table Inner query is executed once.
• In co-related sub-queries inner query also takes value from outer query.

Example :- Display employee records earning morethan avg salary of their dept

Select * from emp a where a.sal>(select avg(sal) from emp where deptno=a.deptno)

•       Where   →   1          A      5000      10    √                                      avg
•                                 2          B       4000     20    x                       5000 > (4000)
•                                 3          C       3000     10     x                      4000 > (4500)
•                                 4          D       5000     20     √                     3000 > (4000)
•                                 5          E        6000     30     x                     5000 > (4500)

Display names of the employee earning max salaries in their department

Select  ename,sal,deptno from emp X where x.sal=(select max (sal) from emp where deptno = x.deptno)

 

•  In co-related sub-queries,we can use two operations

1) exists (sub query)
2) Non exists (sub query)
3) Exists non exists returns true/false.

• Exists returns true ,if sub query returns at-least one record.
• If sub query returns no records then exists returns false.
• Exists operation is used to check whether the record is present in the table(or) not.
• Display department list which are not empty.

Select * from dept d  where exists (select * from emp where deptno=d.deptno).

• Display departments which are empty.

Select * from dept d where not exists(select  * from emp where deptno=d.deptno)

SQL server recommands use exists, not exists instead of in & not in operator.

Display list of managers 

Select * from emp  x where exists (select * from emp where mgr=x.empno)

Example :–

Empno         ename          mgr

1                     A               —

2                     B                1

3                    C                 1

4                     D                2

Here  A is manager for C & B

B is manager  for   D

C is not  manager for any one similarly D

Difference  between join and subquery  :–

• Use sub queries we got data from one table & condition based on another table

• Use join operator to get data from multiple tables.