Dot Net Developers Only: September 2014

SQL-Server [2012]-Beginner and Advanced

Basic-1

Q1.How to Restore and Backup the Existing Database ???
Ans:
SQL Database Restore in Sql Server(Attach the Database in Sql Server)-->
1)*To Restore Database (.bak file only) My database name=hcl
restore database hcl from disk='D:\hcl.bak'
*To Backup database
backup database hcl to disk='D:\hcl.bak'

2)*To create Backup of Database from SqlServer:
Right click on hcl->Task->Backup->
Backup Type=Full
In Destinatio click on Add -> Browse -> (Select location And Give File name(as hcl)) -> ok,ok.

..............................................................................................................................................................

Paul Tutorial

Language Commands—

My Database Name is=test1

My table name is =learn

1 )DDL(Data Definition Language)—Create ,Alter ,Drop;Truncate.

2) DML(Data Manipulation Language)—Insert ,Update,Delete.

3) DQL(Data Query Language) – Select.

4) TCL(Transation Control Language)—Commit,Rollback,Savepoint.

5) DCL(Data Control Language)—Grant,Revoke

1] DDL Commands:

Database information View---

*To see all details of My Database-

Sp_help databasename (it will display name,db_size,created_date and status)

*To Rename my database-

Sp_renamedb OldDatabaseName,NewdatabaseName

a) create –

create database test1

use test1

create table learn(id int,name varchar(20),address varchar(20),mobile int,country varchar(20),job varchar(30) )

insert learn values(100,'ujjwal','chas',99999,'india','seo')

insert learn values(101,'scott','bksc',11111,'america','dev')

insert learn values(102,'ram','ndls',22222,'india','mgr')

insert learn values(103,'sini','sc',33333,'india','clrk')

insert learn values(104,'allu','hyd',44444,'uk','EEE')

select * from learn

id name address mobile country job

100 ujjwal chas 99999 india SEO

101 scott bksc 11111 america Dev.

102 ram ndls 22222 india java dev

103 sini sc 33333 india Hr

104 allu hyd 44444 uk mgr

b) Alter command-

properties:

* Adding a new column in the existing table.

*delete(removing) the column from the table.

*changing the datatype of the column.

*changing the size of the datatype of type column.

* adding the constraint after creating the table.

Q. To delete a column permanently from a existing table.

alter table learn drop column job

output:

id name address mobile country

100 ujjwal chas 99999 india

101 scott bksc 11111 america

102 ram ndls 22222 india

103 sini sc 33333 india

104 allu hyd 44444 uk

Q. Write a query to change the datatype from varchar(20) to varchar(50) on address column in existing table.

alter table learn alter column address varchar(50)

Output:you will get, the column name address has changed varchar(20) to varchar(50) see your table.

Q. To add a new column as qualification in existing table learn.

alter table learn add qualification varchar(20)

output:

id name address mobile country qualification

100 ujjwal chas 99999 india null

101 scott bksc 11111 america null

102 ram ndls 22222 india null

103 sini sc 33333 india null

104 allu hyd 44444 uk null

Q Q. To change(or Re-name) any one column Name.

sp_rename 'learn.job','work'

output:

id name address mobile country work

100 ujjwal chas 99999 india null

101 scott bksc 11111 america null

102 ram ndls 22222 india null

103 sini sc 33333 india null

104 allu hyd 44444 uk null

Q. To Re-Name the Table Name:

sp_rename old_table_name, new_table_name

C) Drop command— (Full delete the table with table structure)

drop table learn

output:

nothing, table fully deleted.

D) Truncate command—(It will delete the whole table data but table structure is still available means all columns names remained)

truncate table learn

output:

id name address mobile country work

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

2) DML Commands-(database name-test1,,table name-learn)

(Main Table)

id name address mobile country work

100 ujjwal chas 99999 india seo

101 scott bks 11111 america dev

102 ram ndls 22222 india mgr

103 sini sc 33333 india clrk

104 allu hyd 44444 uk EEE

a) Inset command-

Q. Insert data into the table.

create table learn(id int,name varchar(20),address varchar(20),mobile int,country varchar(20),job varchar(30) )

insert learn values(100,'ujjwal','chas',99999,'india','seo')

insert learn values(101,'scott','bksc',11111,'america','dev')

insert learn values(102,'ram','ndls',22222,'india','mgr')

insert learn values(103,'sini','sc',33333,'india','clrk')

insert learn values(104,'allu','hyd',44444,'uk','EEE')

select * from learn

id name address mobile country work

100 ujjwal chas 99999 india SEO

101 scott bksc 11111 america Dev.

102 ram ndls 22222 india java dev

103 sini sc 33333 india Hr

104 allu hyd 44444 uk mgr

Q. insert multiple records in the table.

insert learn values(105,'allu','hyd',44444,'uk','EEE'),

(106,'scg','gwl',55555,'ind','sw'),

(107,'vik','chs',66666,'ind1','SE'),

(108,'ganesh','chs2',77777,'ind2','php'),

(109,'raju','janamore',88888,'ind3','JrDev'),

(110,'guddu','up',12345,'silimore','JrSupp')

Output:

id name address mobile country work

100 ujjwal chas 99999 india seo

101 scott bksc 11111 america dev

102 ram ndls 22222 india mgr

103 sini sc 33333 india clrk

104 allu hyd 44444 uk EEE

105 allu hyd 44444 uk EEE

106 scg gwl 55555 ind sw

107 vik chs 66666 ind1 SE

108 ganesh chs2 77777 ind2 php

109 raju janamore 88888 ind3 JrDev

110 guddu up 12345 silimore JrSupp

b) Update Command- This command use to modify(or Changes) the existing data of table.

Syntax:update tableName set columnName=value where <condition>

Q. writes a query to update scott address as Chicago from Main Table.

Update learn set address='Chicago' where id=101

Output:

id name address mobile country work

100 ujjwal chas 99999 india seo

101 scott Chicago 11111 america dev

102 ram ndls 22222 india mgr

103 sini sc 33333 india clrk

104 allu hyd 44444 uk EEE

Q. write a query to update(or Change) ujjwal”s country name(as london) and work (as Dotnet).

Output:

id name address mobile country work

100 ujjwal chas 99999 london dotnet

101 scott Chicago 11111 america dev

102 ram ndls 22222 india mgr

103 sini sc 33333 india clrk

104 allu hyd 44444 uk EEE

C)Delete Command-This command is used to delete all the records or the specific column record.[structure of the table not deleted only data deleted]

Syntax1: delete from tableName

Syntax2: delete from tableName where id=value.

Q. Delete the table learn(Main Table).

delete learn

or, delete from learn

output:

id name address mobile country work

Q. Delete the 1 row whose id=104

Or, delete the row

104 allu hyd 44444 uk EEE

delete learn where id=104

Q. Delete 1 row whose name is ujjwal.

delete learn1 where name='ujjwal'

id name address mobile country work

101 scott bks 11111 america dev

102 ram ndls 22222 india mgr

103 sini sc 33333 india clrk

104 allu hyd 44444 uk EEE

Question1) What is difference between truncate and delete and drop.

Ans:

*Truncate will delete all the record at a time.Delete also will remove(delete) all the record.

*Truncate can not delete data of a single row or column But Delete command can delete data of a single row or column.

*Truncate works fast But delete command works slow.

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

3)DQL(Data Query Language):

Select command:

Main Table(employee)

eno ename salary

101 Ujjwal 11000.00

102 Raju 20000.00

103 sukhdeo 30000.00

104 Rj 10000.00

105 Sumit 70000.00

106 arun 5000.00

107 disha 2000.00

108 scott 15000.00

109 kirti 18000.00

It is used for retrieving all the records or specific records from the table.

Ex1: write a query to display all the table records.

select * from employee

output:

eno ename salary

101 Ujjwal 11000.00

102 Raju 20000.00

103 sukhdeo 30000.00

104 Rj 10000.00

105 Sumit 70000.00

106 arun 5000.00

107 disha 2000.00

108 scott 15000.00

109 kirti 18000.00

Ex:2 write a query to display name and mobile numbers from employee table.

select ename ,salary from employee

output:

ename salary

ujjwal 11000.00

Raju 20000.00

sukhdeo 30000.00

Rj 10000.00

Sumit 70000.00

arun 5000.00

disha 2000.00

scott 15000.00

kirti 18000.00

select command use in Operator—it is used to perform operation on two or more operands.

Ex: 10+3. Here 10,3 are called as operand and + is called as operator.

Different operators in sql are—

1. Arithmetic operator- (+,-,*,/,%)

2. logical operator- (And ,or,!,!=,<>)

3. Comparision operator- (<,>,<=,>=,)

4. string Operator- (Like,Not Like).

5. Range Operator- (Between,Not Between)

1.Arithmatic operations-

Table(student)

sno sname m1 m2 m3

101 Rj 30 35 40

102 Sumit 44 46 47

103 arun 50 51 52

104 disha 55 56 57

105 scott 60 66 67

106 kirti 70 71 72

Ex1:WQA. To display student details along with marks.

select *,(m1+m2+m3) from student or,

select *,(m1+m2+m3) as ‘Total’ from student

output(total is as alias name temporary name)

sno sname m1 m2 m3 Total

101 Rj 30 35 40 105

102 Sumit 44 46 47 137

103 arun 50 51 52 153

104 disha 55 56 57 168

105 scott 60 66 67 193

106 kirti 70 71 72 213

Ex2:WQA. To display student details along with total marks and Average of total marks.

select *,(m1+m2+m3) as ‘Total’,(m1+m2+m3)/3 as ‘Average’ from student

output(total and Average are as alias name or temporary name)

sno sname m1 m2 m3 Total Average

101 Rj 30 35 40 105 35

102 Sumit 44 46 47 137 45

103 arun 50 51 52 153 51

104 disha 55 56 57 168 56

105 scott 60 66 67 193 64

106 kirti 70 71 72 213 71

Note: so all performance can be esily done by alias name

2. Logical operator- (Use main table-employee)

Ex1:Write a query to display employee details whose name is ujjwal and salary>10000.

select * from employee where ename='ujjwal' and salary>10000

output:

eno ename salary

101 Ujjwal 11000.00

3.Comparision operator-

Ex1:Write a query to display employee details whose salary<5000.

select * from employee where salary<5000

output:

eno ename salary

107 disha 2000.00

Ex2:Write a query to display employee details whose salary!=2000.

select * from employee where salary!=2000

Output:

eno ename salary

101 Ujjwal 11000.00

102 Raju 20000.00

103 sukhdeo 30000.00

104 Rj 10000.00

105 Sumit 70000.00

106 arun 5000.00

108 scott 15000.00

109 kirti 18000.00

4.String Operator- [Like operator]

These operator are used to search a specific pattern.

1) a% means start with a

2) %a means ending with a.

3) %a% means in-between a.

Ex1:Write a query to display employee details whose name start with u.

select * from employee where ename like 'u%'

Output:

Eno ename salary

101 Ujjwal 11000.00

Ex2:Write a query to display employee details whose name not start with u.

select * from employee where ename not like 'u%'

Output:

eno ename salary

102 Raju 20000.00

103 sukhdeo 30000.00

104 Rj 10000.00

105 Sumit 70000.00

106 arun 5000.00

108 scott 15000.00

109 kirti 18000.00

Ex3:Write a query to display employee details whose name not start with s and.

select * from employee where ename like 's%' and salary>20000

eno ename salary

103 sukhdeo 30000.00

105 Sumit 70000.00

Ex4:Write a query to display employee details whose name end with n.

select * from employee where ename like '%n'

Output:

eno enmae salary

106 arun 5000.00

Ex5:Write a query to display employee details whose name’s second character is start with a.

select * from employee where ename like '_a%'

Ex6:Write a query to display employee details whose name’s third character is start with k.

select * from employee where ename like '_ _k%' (here 2 underscore used no space between)

eno ename salary

103 sukhdeo 30000.00

Ex7:Write a query to display employee details whose name’s second last character is start with e.

select * from employee where ename like '%e_' (here 2 underscore used no space between)

eno ename salary

103 sukhdeo 30000.00

4.Range Operators-

Ex1:Write a query to display employee details whose salary range between 1000 to 10000.

select * from employee where salary between 1000 and 10000

output:

104 Rj 10000.00

106 arun 5000.00

107 disha 2000.00

Ex2:Write a query to display employee details whose salary range not between 1000 to 10000.

select * from employee where salary not between 1000 and 10000

eno ename salary

101 Ujjwal 11000.00

102 Raju 20000.00

103 sukhdeo 30000.00

105 Sumit 70000.00

108 scott 15000.00

109 kirti 18000.00

Ex3:Write a query to display employee details whose salary range between 1000 to 10000.

4)TCL(Transaction Control Language)Commands:

[Commit,Rollback,Savepoint]

This is my existing emp table:

emp-table

eno ename salary

101 Ujjwal 11000.00

102 Raju 20000.00

103 sukhdeo 30000.00

104 Rj 10000.00

105 Sumit 70000.00

Commit- it used save the changes permanently.

Now To, delete row 105

begin transaction

delete employee where eno=105

output:

eno ename salary

101 Ujjwal 11000.00

102 Raju 20000.00

103 sukhdeo 30000.00

104 Rj 10000.00

So, row 105 is deleted.

Now, to insert(or back) the deleted item we use rollback. It will back your deleted row 105.

begin transaction rollback (Run This)

After the rollback command the output :

output:

eno ename salary

101 Ujjwal 11000.00

102 Raju 20000.00

103 sukhdeo 30000.00

104 Rj 10000.00

Note:After the rollback the row 105 is not appears because it is permanently deleted,so here rollback is not possible.

Rollback:

This is my existing emp table:

Emp-table

eno ename salary

101 Ujjwal 11000.00

102 Raju 20000.00

103 sukhdeo 30000.00

104 Rj 10000.00

105 Sumit 70000.00

Now To, delete row 105

begin transaction

delete employee where eno=105

To see it is deleted or not:

select * from emp

output:

eno ename salary

101 Ujjwal 11000.00

102 Raju 20000.00

103 sukhdeo 30000.00

104 Rj 10000.00

Here the row 105 is deleted.

Now, to insert(or back) the deleted item we use rollback. It will back your deleted row 105.

begin transaction rollback (Run This)

After the rollback command the output :

eno ename salary

101 Ujjwal 11000.00

102 Raju 20000.00

103 sukhdeo 30000.00

104 Rj 10000.00

105 Sumit 70000.00

Here 105 row is back.

Note:Here the data is deleted from Buffer database not main database.so the rollback is possible.but once we commit the delete query it will not possible to rollback.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

Constraints

Constraints are used to apply condition or restriction of the table.

We can apply the constraints at the time of table creation or after created the table.

There are 9 types of constraints are available---

1) Null

2) Not Null

3) Primary key

4) Composite primary key

5) Foreign key

6) Unique key

7) Candidate key

8) Checked

9) Default

10)Identity column. (very important-autogenerate column)

1) Null-This constrains is used to allow null values. And by default every column accept null values.

Syntax: create table tableName(ename varchar(20) null)

2) Not Null-This constraints not allow the null value.

Syntax: create table tableName(eno int not null,ename varchar(20))

3) Primary key-

1)primary key not accept null value,it bydefault Not Null.

2)primary key will not accept duplicate values.

3)primary key can apply only one time in a single table or we can

apply 1 primary key in a table.means 1 table=multiple primary key

not possible.

Syntax: create table emp(eno int primary key,ename varchar(20))

4)Composite primary key-

It is used to apply primary key in two or more columns. See table--

eid eno salary

101 1111 1 13000.00

102 22222 20000.00

103 33333 30000.00

104 44444 10000.00

105 55555 70000.00

Here I want to apply primary key in two columns at eid and eno, so this concept is called as composite primary key.As we know before that the we can not apply 2 primary key in a table so this concept we named separetly as composite primary key. composite primary key holds all the feature of primary key.

Ex1:Create a table with name employee with columns eid,eno and salary and apply composite primary key in columns eid and eno.

Ex1

5)Foreign key-

*It is used to establish the relation between two or more

Tables.

* Foreigh key must always referencing to primary key table,so foreign key is also called as referencial constraint.

* Foreign key accept duplicate value

* Foreign key accept null values.

* we can apply more than one foreign key in a single table.

*foreign key constraint will have constraint name.

*we can not insert the record in foreign key column until unless the record is available in primary key column.

* we can not delete the primary key column record untill unless the

Record is deleted in foreign key column.

*we can not drop primary key constraint untill unless we drop foreign

We drop foreign key constraint.

Syntax: We taken 2 tables(e1 and d1) to apply foreign key.

*Create a table name e1 with columns(eid,ename)

create table e1(eid int primary key,ename varchar(20))

insert e1 values(6,'rama'),(2,'raju'),(3,'sukhdeo'),(4,'ganesha'),(5,'vik')

eid ename

1 ujjwal

2 raju

3 sukhdeo

4 ganesha

5 vik

* Create a table name d1 with columns(did,dname,dincome and eid as set foreign key).

create table d1(did int primary key,dname varchar(20),dincome money,eid int foreign key references e1(eid))

did dname dincome eid

some important:

1)truncate table e1 (it is not possible now)—error

2)insert d1 values(11,'arun',1000,1),(22,'uma',2000,2)

did dname dincome eid

11 arun 1000.00 1

22 uma 2000.00 2

3) insert d1 values(33,'suraj',3000,1111),(44,'krish',4000,2222) -Error occurred.

Error message is----

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__d1__eid__3B75D760". The conflict occurred in database "test", table "dbo.e1", column 'eid'.

The statement has been terminated.

6) Unique key-

*unique key does not accept duplicate values.

*unique key accept null value once only.

*unique key can apply more than one in a single

Table.

*unique key constrain will have constraint_Name.

Syntax1:create table emp(eno int unique,ename varchar(20))

Syntax2:create table emp(eno int constraint con unique,ename varchar(20))

Here con is the constraint name.

Ex1:

create table e2(eid int unique,ename varchar(20))

insert e2 values(33,'suraj'),(44,'krish')

7) Candidate key- (unique key+ Not null=Candidate key).

Syntax:

create table e4(eid int unique not null,ename varchar(20))

we can not apply not null constraint after creating the table for unique key column. But we can apply unique key for not null column after creating the table.

8)Checked-It is used to apply condition on the table.

Syntax:create a table with name e5 with columns eno,ename,salary and apply checked constraint for eno based on the condition eno>200.

create table e5(eno int ,ename varchar(20), salary money,check(eno>200))

output:

eno ename salary

Now insert the value:

insert e5 values(101,'suraj',1000),(102,'krish',2000)

output: Error occurred.

Error message is-

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CK__e5__eno__412EB0B6". The conflict occurred in database "test", table "dbo.e5", column 'eno'.

The statement has been terminated.

Now Again insert the value with followed by condition as eno>200:

insert e5 values(201,'suraj',1000),(202,'krish',2000)

output : No error it inserted.

eno ename salary

201 suraj 1000.00

202 krish 2000.00

9)default-it is used to insert the default value instead of null value.

Ex1:

create table e(eno int ,ename varchar(20), satatus char(1) default('A'))

insert e6 values(201,'rama2',''),(202,'krish','b')

output:

eno ename status

201 suraj a

Ex2:

create table e9(eno int ,ename varchar(20), satatus char(6) default('ujjwal'))

insert e9 values(202,'rama1','ujjwal')

insert e9 values(203,'rama1','Ramesh')

eno ename status

202 rama1 ujjwal

203 rama1 Ramesh

10)Identity-It is used to apply autogenerate the number for a column .

Ex1:

create table e10(eno int identity(101,1) ,ename varchar(20))

select * from e10

output:

eno ename

Now insert values: error occured

insert e10 values(101,'rama1') --Not possible to insert

insert e10 values(102,'rama1') --Not possible to insert

Now insert values: currect formate to insert.

insert e10 values('rama1')

insert e10 values('rama2')

output:

eno ename

101 rama1

102 rama2

Here no need to give eno during inserting,it will automatic generate the eno.

Normalization

Normalization is the technique to design a table for the database.which is used to reduce-

1)Redundancy(duplicate values) and

2)Inconsistency are avoided ,inconsistency means-Data which is not meaningful(or incurrect) is removed.

3)Dependebcy reduce.

Un-Managed Table/de-Normalized Table

The above table is not managed because data duplication and repetition.

The most commonly used normal form are—

1)First Normal form.(1 NF)

2)Second normal form.

3)Third form.

4)BCNF(bi)

1)First NF—A table is called in 1 NF if it follow 2 rules-

Rule1:single cell consist of single value(means-each columns in the table should be single value column.)

Rule2:Each record must be unique(means-no rows contain same data or each row has different data)

Now applying First NF in the above table. After applying 1NF the table appears as—

Here:last two rows are identical(same),means no unique. So remove one row to make in 1NF form,and rest all are same.

2) Second NF—To make in second NF some rules should be follow-

*our table must be in 1NF form.

*Now to apply the 2 NF ,we need to identify the key

attribute and non-key attributes.

*Every non-key attribute must fully dependent on key

Attribute,that is in 2NF we have to identify the funtional

Dependency.

*if any non-key attributes is not fully dependent on the key

Attribute then remove that column and place it in separate

Table.

Ex:

*In second NF we have to apply primary key and foreign key in another table for that primary key.

*Here the primary key in first table applied in column ITYID and the second table same column taken ITYID so this column is called as foreign key.

3) Third NF-

A table is said to third NF if It is in second NF, and all the columns in the table should depends upon primary key column only.The transitive dependency are not allowed in 3 NF. Transitive dependency means non-key attributes columns will depends upon non-key column in the table.

Now,,

In the below table student observe that the Total column

Is not depends upon Sno column,,Total is fully depends

Upon Math,phy,chem columns. So Total column should be

Remove from the studend table and make a different table

For the column Total.

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

JOINS

Join are used to retrieve the data from two or more than two tables.

It is used to join 8 to 10 tables.

Different types of joins are—

1) Inner joins.

2) Outer join.

*Left outer join

*Right outer join

*Full outer join

3) Cross join.

4) Self join.

5) Equi-join.

*Non Equi-join.

1st Table name is Employee(eno ,ename,salary). 2nd Table Department(dno,dname,eno)

- Do not apply primary key ok.

Emp table

eno ename esal

101 anil 20000

102 sunil 22000

103 ajay 21000

104 arun 25000

Dept table

dno dname eno

10 it 101

20 math 102

30 physics 103

40 chemistry 105

……………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………

1) Inner Join-

It is used to retrieve matching records from two or more table.

Q1) Write a query to display emp details along with dname from both

Emp table and dept table.

select e.*,d.dname from emp e inner join dept d on e.eno=d.eno

select emp.eno,emp.ename,emp.esal,dept.dname from emp inner join dept on emp.eno=dept.eno

select e.eno,e.ename,e.esal,d.dname from emp e inner join dept d on e.eno=d.eno

select e.*,d.dname from emp e inner join dept d on e.eno=d.eno

select e.eno,e.ename,e.esal,d.dname from emp e inner join dept d on e.eno=d.eno

select e.*,d.dname from emp e,dept d where e.eno=d.eno [This is without using any join condition]

All 8 query are same but different type of writing.

Q2) Write a query to display emp details along with dname whose name

Start with a.

Output:

Eno ename esal dname

101 anil 20000 it

103 ajay 21000 physics

select e.*,d.dname from emp e inner join dept d on e.eno=d.eno where e.ename like 'a%'

Q3) Write a query to display emp details with dname who are working in ‘it’ Department.

Output:

Eno ename esal dname

101 anil 20000 it

select e.*,d.dname from emp e inner join dept d on e.eno=d.eno where dname='it'

or;

select e.*,d.dname from emp e inner join dept d on e.eno=d.eno where d.dname='it'

Q4) Write a query to display emp details(means-eno,ename,esal) only

who are working in ‘it’ Department.

Output:

Eno ename esal

101 anil 20000

select e.* from emp e inner join dept d on e.eno=d.eno where d.dname='it'

Q5) Write a query to display emp details(means-eno,ename,esal) only

who are not working in ‘math’ Department.

Output:

eno ename esal

101 anil 20000

103 ajay 21000

select e.* from emp e inner join dept d on e.eno=d.eno where d.dname!='math'

or,

select e.* from emp e inner join dept d on e.eno=d.eno where d.dname<>'math'

or,

select e.* from emp e inner join dept d on e.eno=d.eno where dname!='math'

Q6) Write a query to display emp details(means-eno,ename,esal) only

Whose name not start with a and who is working in ‘math’

Department.

output

eno ename esal

101 anil 20000

102 sunil 22000

select e.* from emp e inner join dept d on e.eno=d.eno where e.ename not like 'a' and dname='math'

Now very important-1

Create two Tables like this—

create table state(sid int,sname varchar(50))

insert state values(101,'AP'),(102,'Telangana'),(103,'Tamilnadu'),(104,'Karnataka'),(105,'Maharashtra')

create table city(cid int,cname varchar(50),sid int)

insert city values(1,'Ongoli',101),(2,'Madhurai',103),(3,'Guntur',101),(4,'Hydarabad',102),(5,'Bangalore',104),(6,'Chennai',103)

select * from state

select * from city

state table

sid sname

101 AP

102 Telangana

103 Tamilnadu

104 Karnataka

105 Maharashtra

city table

cid cname sid

1 Ongoli 101

2 Madhurai 103

3 Guntur 101

4 Hydarabad 102

5 Bangalore 104

6 Chennai 103

some questions regarding these two tables.

Questions:

1)Write a query to display state name.

2)Write a query to display state name along with city name.

3)Write a query to display city name belongs to AP.

4)Write a query to display city name belongs to Tamilnadu whose name

Start with c.

Ans:

1)Output:

sname

Telangana

Tamilnadu

Karnataka

Maharashtra

select sname from state

2)Output:

AP Ongoli

TN Madhurai

AP Guntur

Telangana Hydarabad

Karnataka Bangalore

TN Chennai

select s.sname,c.cname from state s inner join city c on s.sid=c.sid

3)output:

sname cname

AP Ongoli

AP Guntur

select s.sname,c.cname from state s inner join city c on s.sid=c.sid where sname='AP'

4)output:

cname

chennai

select s.sname,c.cname from state s inner join city c on s.sid=c.sid where s.sname='TN' and c.cname like 'c%'

or,

select c.cname from state s inner join city c on s.sid=c.sid where s.sname='TN' and c.cname like 'c%'

or,select c.cname from city c inner join state s on s.sid=c.sid where s.sname='TN' and c.cname like 'c%'

Now very important-2

Creating Three Tables:->

Employee table

eno ename salary

101 anil 11000

102 sunil 20000

103 ajay 30000

104 arun 10000

105 johns 70000

106 james 5000

Dept table

dno dname

10 it

20 math

30 physics

40 chemistry

Employeeinstrutor table

eno eno

101 10

102 20

103 10

104 10

105 20

106 10

Q1)write a query to display employee name and dept names?

select e.ename,d.dname from employee e inner join employeeinstructor i on e.eno=i.eno inner join dept d on i.dno=d.dno

output:

eno dname

anil it

sunil math

ajay it

arun it

johns math

james it

Q2)write a query to display employee names who are working in ‘it’

Dept?

select e.ename from dept d inner join employeeinstructor i on i.dno=d.dno inner join employee e on e.eno=i.eno where d.dname='it'

ename

anil

ajay

arun

james

Q3)write a query to display ename ,salary,dname?

select e.ename, e.salary,d.dname from employee e inner join employeeinstructor i on e.eno=i.eno inner join dept d on i.dno=d.dno

output:

ename salary dname

anil 11000.00 it

sunil 20000.00 math

ajay 30000.00 it

arun 10000.00 it

johns 70000.00 math

james 5000.00 it

Q3)write a query to display salary and ename who are working in math department?

select e.salary ,e.ename from dept d inner join employeeinstructor i on i.dno=d.dno inner join employee e on e.eno=i.eno where d.dname='math'

ename

anil

ajay

arun

james

Q3)write a query to display employee details whose salary >22000?

select e.* from employee e where e.salary>22000

eno ename salary

103 ajay 30000.00

105 johns 70000.00

Q3)write a query to display employee details along with dname whose salary >22000?

select e.*,dname from dept d inner join employeeinstructor i on d.dno=i.dno inner join employee e on i.eno=e.eno where salary>22000

eno ename salary dname

103 ajay 30000.00 it

105 johns 70000.00 math

All clauses in SQL

1)Order by clause

2)Group by clause

3)Having clause.

Employee table

eno ename salary

101 anil 11000.00

102 sunil 20000.00

103 ajay 30000.00

104 arun 10000.00

105 johns 70000.00

106 james 5000.00

1)Order by clause: it is used to display data either in ascending order or descending orde, and it is by default in ascending order(1,2,3,4,5…..).

Q.Write a query to display employee details order by name.

select * from employee order by ename (or eno ,or salary or any column name)

eno ename salary

103 ajay 30000.00

101 anil 11000.00

104 arun 10000.00

106 james 5000.00

105 johns 70000.00

102 sunil 20000.00

Q.Write a query to display employee details in ascending order by

Name.

select * from employee order by ename desc

eno ename salary

102 sunil 20000.00

105 johns 70000.00

106 james 5000.00

104 arun 10000.00

101 anil 11000.00

103 ajay 30000.00

Aggregate Function :These function will process on multiple rows and return only one value.

*sum()

*min()

*max()

*average()

*count()

Q.Write a query to display the total salary that was assigned to all

the employee?

select sum(salary) from employee

output:

(no column name)

146000.00

Or,,

select sum(salary) as mytotal from employee

output:

mytotal

70000.00

Note: Here mytotal is as a temporary column name is used.you can use a temporary column name in every query below also.

Q.Write a query to display minimum salary that was assigned to all

the employee?

select min(salary) from employee

(no column name)

5000.00

Q.Write a query to display maximum salary that was assigned to all

the employee?

select max(salary) from employee

(no column name)

70000.00

Q.Write a query to display AVERAGE salary that was assigned to all

the employee?

select avg(salary) from employee

(no column name)

24333.3333

Note: average salary means- 11000.00

(20000.00+30000.00+10000.00+70000.00+5000.00)/6= 24333.3333

Q.Write a query to count the no.of the employees working in the company?

select count(*) from employee

(no column name)

C salary of employee.

select sum(salary) as totalsal ,max(salary) as 'MaxSal', Min(salary) as MinSal, avg(salary) as AvgSal from employee

output:

totalsal MaxSal MinSal AvgSal

146000.00 70000.00 5000.00 24333.3333

Note: totalsal MaxSal MinSal AvgSal all are temporary columns name.

2)Grouping by clause:Grouping by clause is used for group common set of

Values that are available in single column. Group by clause will use

In aggregate functions.And remember that we can not apply ‘where’ condition

In group by clause.

create table emp(eno int,ename varchar(30),sal int,city varchar(50),region varchar(50),dno int,dname varchar(50),gender varchar(10))

insert emp values(102,'sunil',6000,'telangana','ameerpet',10,'It','M')

insert emp values(103,'rahul',7000,'delhi','Ashok nagar',10,'It','M')

insert emp values(104,'ajay',8000,'jamshedpur','bhalubasa',20,'HR','M')

insert emp values(105,'gopi',9000,'Ranchi','lalpur',10,'It','M')

insert emp values(106,'ramesh',10000,'dhanbad','putti',20,'HR','F')

insert emp values(107,'rama',11000,'mumbai','maharashtra',10,'It','F')

insert emp values(108,'kiran',12000,'kolkata','kestopur',10,'It','F')

insert emp values(109,'reshmi',13000,'hyderabad','sikandrabad',20,'HR','F')

insert emp values(110,'kunal',14000,'Delhi','Lakxmi Nagar',10,'It','M')

select * from emp

eno ename sal city region dno dname gender

101 ujjwal 5000 Bokaro chas 10 It M

102 sunil 6000 telangana ameerpet 10 It M

103 rahul 7000 delhi Ashok nagar 10 It M

104 ajay 8000 jamshedpur bhalubasa 20 HR M

105 gopi 9000 Ranchi lalpur 10 It M

106 ramesh 10000 dhanbad putti 20 HR F

107 rama 11000 mumbai maharashtra 10 It F

108 kiran 12000 kolkata kestopur 10 It F

109 reshmi 13000 hyderabad sikandrabad 20 HR F

110 kunal 14000 Delhi Lakxmi Nagar 10 It M

Q.Write a query to display total salary of male and female?.

select gender,sum(sal) as 'MyTotalSal' from emp group by gender

gender MyTotalSal

F 46000

M 49000

Q.Write a query to display total salary for the region

select region,sum(sal) as 'C' from emp group by region

region MyTotalSal

ameerpet 6000

Ashok nagar 7000

bhalubasa 8000

chas 5000

kestopur 12000

Lakxmi Nagar 14000

lalpur 9000

Maharashtra 11000

putti 10000

sikandrabad 13000

Q.Write a query to display total salary that are assign for each dept no

select dno,sum(sal) as 'MyTotalSal' from emp group by dno

dno MyTotalSal

10 64000

20 31000

Q.Write a query to display total salary that are assign for each dept

1)Having clause: It is used to apply where condition ,group by clause i.e-

When you apply condition on group date ,we have to gofor having clause.

Having clause can also apply in aggregate function.

Q.Write a query to display total salary that are assign for each dept no whose total sal is greater than 8000.

select dname,sum(sal) as 'TotSal' from emp group by dname having sum(sal)>8000

dname TotSal

HR 31000

It 64000

Q.Write a query to display the no of employees working in each dept

Whose no is greater than 1 order by dname

select dname,count(*) as 'No of emp' from emp group by dname having count(*)>

dname no of emp

HR 3

It 7

Store Procedure

Store procedure is a set of pre-compiled Sql statement which will get executed when we call it.It will compile once and can be executed any

No.Of times.

*It will not have return type and will not have Any return values.

*It contains DML commands.

*** Very Important(To view procedure in current database)—

Click on view->object explorer->databases icon->your database->programmability->store procedure(All stored procedure programs here).

Whenever we are passing the query to the procedure for 1^st time syntax checking will be done and Best execution strategies selected(or prepared) is called (Execution Plan) to execute the query and the result of the plan will store in Execution Plan and then it will execute.

Whenever we plan to execute the procedure in 2^nd time then the query will execute direct from the execution plan,it will not checking the syntax again,its means it will compile once and execute any no of times. SEE THE FIGURE…

Store procedure are of three types—

1)System store procedures

2)User define procedures

3)Temporary store procedures

1)System store procedures—

The stored procedure that are created by Microsoft are called as

System define stored procedure.

Ex:Click on Databases->system database->Master->programmability->

System stored procedure.

sp_help procedure_name (As sp_help emoployee)

sp_rename procedure_name (As sp_rename emoployee)

sp_helpconstraint procedure_name (As sp_helpconstraint emoployee)

sp_renamedb procedure_name (As sp_renamedb emoployee)

sp_helptext procedure_name (As sp_help emoployee)

Note:always system define stored procedure will start with sp_ so

We must give the proc_ for the user define stored procedure name.

3)Temporary store procedures—

This procedure name start with # symbol ,and it will store temporary

In memory,when Sql server is closed then the temporary stored

procedure will be deleted\

2)User define procedures—

The store procedure that was created depending on the usesr

requirement is called as user defined stored procedure.

Step to work with Stored procedure:

· Create the procedure.

· Execute the procedure.

· Call the procedure.

Syntax to create the procedure—

create procedure procedure_name(parameters)

as begin

-Sql statements-

end

store procedure will get executed when user call it.

Syntax to calling a stored procedure.

Exec procedure_name values

Q1)Write a stored procedure program to add two numbers usin input

parameters.

CREATE PROCEDURE padd(@A INT, @B INT)

AS BEGIN

PRINT @A+@B

END

//To run this-

EXEC PRC1 3,6

Output: 9

OR,,

CREATE PROCEDURE padd(@A INT,@B INT)

BEGIN

PRINT 'THE SUM IS=' +cast(@A+@B as char)

END

To execute:

EXEC PRC2 3,6 //Here called the procedure

Output: THE SUM IS=9

Or,,

CREATE PROCEDURE PRC1

@A INT, @B INT

BEGIN

PRINT @A+@B

END

//To run this-

EXEC PRC1 3,6 // Here called the procedure

Output:9

Stored procedure will have two type of parameters

1)input parameters. (as Q1. is a input parameters)

2)output parameters.

3)optional parameters.

Q2) Create a procedure to sum of two numbers Using Output parameter.

CREATE PROCEDURE PADD2(@X INT, @Y INT ,@Z INT OUTPUT)

AS BEGIN

SET @Z=@X+@Y

PRINT 'THE SUM IS:'+CAST(@Z AS CHAR)

END

To execute--

DECLARE @K INT

EXEC PADD2 10,20,@K OUTPU

OUTPUT:

THE SUM IS:30

OR,,

SIMPLE METHODS :

CREATE PROCEDURE PA2(@X INT, @Y INT )

AS BEGIN

DECLARE @Z INT

SET @Z=@X+@Y

PRINT 'THE SUM IS:'+CAST(@Z AS CHAR)

END

EXEC PA2 10,20

OUTPUT:

THE SUM IS:30

Q2) Create a procedure to sum of two numbers Using Optional

parameters.

CREATE PROCEDURE PADD3(@X INT,@Y INT=10)

AS BEGIN

PRINT 'THE SUM IS:'+CAST(@X+@Y AS VARCHAR(5)) // I have given a message here.

END

EXEC PADD3 100

EXEC PADD3 100,200

OUTPUT:

THE SUM IS:110

THE SUM IS:300

To Change something in Procedure PADD3

ALTER PROCEDURE PRC4

@X INT, @Y INT=10

BEGIN

PRINT @X+@Y

END

To Execute:

EXEC PRC5 100

EXEC PRC5 100,200

OUTPUT:

THE SUM IS:110

THE SUM IS:300

Important Questions:

Q1)Create a procedure to insert the record in employee(existing table)

Table?

Q2)Create a procedure to delete the record from employee

Table based on eno.

Q3)Create a procedure to update the record in employee table.

Q4)Create a procedure to display employee table.

Q5)Create a procedure to display employee details based on the

employee no.

Q6)Can we call one stored procedure in another stored procedure?.(YES)

Q7)Create a procedure to display employee details along with deptno

And deptName from employee table and dept table.

Q8)Create a procedure to display employee details for working in deptno 10 and whose name start with a.

Ans:At first, create a table name it as employee

This is your original table--

eno ename salary

101 ujjwal 33000

102 sunil 20000

103 ajay 30000

104 arun 10000

105 johns 70000

106 james 5000

1) Q1)Create a procedure to insert the record in employee(existing

table) table.

Ans:

create procedure pinsert(@eno int,@ename varchar(50),@salary int)

as begin

insert into employee values(@eno,@ename,@salary)

end

To execute:

exec pinsert 107,'krishna',99000

exec pinsert 108,'radhe',33000

output:

eno ename salary

101 ujjwal 33000

102 sunil 20000

103 ajay 30000

104 arun 10000

105 johns 70000

106 james 5000

107 krishna 99000

108 radhe 33000

To see table:

select * from employee

Q2)Create a procedure to delete the record from employee

Table based on eno.

Ans:

create procedure pdelete(@eno int)

as begin

delete from employee where (eno=@eno)

end

To execute:

exec pdelete 105 (Here, eno=105 deleted)

exec pdelete 106 (Here, eno=106 deleted)

outuput:

eno ename salary

101 ujjwal 33000

102 sunil 20000

103 ajay 30000

104 arun 10000

105 johns 70000

106 james 5000

Q3)Create a procedure to update the record in employee table.

Ans: This is our existing table

eno ename salary

101 ujjwal 33000

102 sunil 20000

103 ajay 30000

104 arun 10000

105 johns 70000

106 james 5000

Creating procedure:

create procedure pupdate(@eno int,@ename varchar(50),@salary int)

as begin

update employee set ename=@ename,salary=@salary where eno=@eno

end

To execute:

exec pupdatea 105,'Ram',11000

exec pupdate11 106,'Paul',12000

output:

eno ename salary

101 ujjwal 33000

102 sunil 20000

103 ajay 30000

104 arun 10000

105 Ram 11000

106 Paul 12000

Q4)Create a procedure to display employee table.

Ans: This is our existing table

eno ename salary

101 ujjwal 33000

102 sunil 20000

103 ajay 30000

104 arun 10000

105 johns 70000

106 james 5000

Now create procedure:

create procedure pdisplay

as begin

select * from employee

end

To execute:

exec pdisplay

output:

eno ename salary

101 ujjwal 33000

102 sunil 20000

103 ajay 30000

104 arun 10000

105 johns 70000

106 james 5000

Q5)Create a procedure to display employee details based on the

employee no.

Ans:

Ans: This is our existing table

eno ename salary

101 ujjwal 33000

102 sunil 20000

103 ajay 30000

104 arun 10000

105 johns 70000

106 james 5000

Now create procedure for this table:

create procedure PFindByeno(@eno int)

as begin

select * from employee where eno=@eno

end

To execute:

exec PFindByeno 103

output:

eno ename salary

103 ajay 30000

Q6)Can we call one stored procedure in another stored procedure?.(YES)

Ans: Yes,we can call one stored procedure to an another stored procedure.

Created two tables :

Emp-table

eno ename salary

101 ram 100

102 paul 150

103 rama 300

Dept-table

dno dname

10 it

20 math

30 physics

40 chemistry

Now create procedure for above two tables :

create procedure pem (Run this first)

as begin

select * from employee

end

create procedure pdep (After ran pem run this)

as begin

exec pem

select * from dept

end

To execute:

exec pdep

output:

eno ename salary

101 ram 100

102 paul 150

103 rama 300

dno dname

10 it

20 math

30 physics

40 chemistry

Note: First write pem procedure and run this ,,and then remove the full procedure of pem and write another procedure pdep and run pdep.

Now call(Execute) the pdep procedure it will display both table emp and dept as output. Here we execute pdep procedure and pdep call the pemp procedure.

Q7)Create a procedure to display employee details along with deptno

And deptName from employee table and dept table.

Ans:

Created two tables :

Emp-table

eno ename salary

101 ram 100

102 paul 150

103 rama 300

Dept-table

dno dname eno

10 it 101

20 math 102

30 physics 101

40 chemistry 103

Now Create procedure for above two tables:

errrrrrrrrrrrrrrrrrrrrrrrr

create procedure pjoin

as begin

select e.*,d.dname from employee e inner join dept d on e.eno=d.eno

end

exec pjoin

select *from employee

select *from dept

Q8)Create a procedure to display employee details for working in deptno 10 and whose name start with a.

Ans:

(Exeption Handling in Stored procedure)

Stored procedure support Exception Handling…(Function not support it)

-write the procedure

-save the procedure

-compile the procedure

-execute the query.

Two types of error in SQL—

1) Compilation error or syntax error.

2) Runtime error

Ex1:

begin try

declare @x int

set @x=10/0

print 'It is try Block'

end try

begin catch

print 'It is catch block and divided by zero not possible'

end catch

output:

Exception occurred.

Here: It will not display the Message ‘It is try Block’ because when the exception occurred in 10/0 it goes to the catch block abd print the message as user given , 'It is catch block and divided by zero not possible'

Ex2:

begin try

declare @x int

set @x=10/0

end try

begin catch

print error_message()

end catch

output:

Divide by zero error encountered.

Function

Dot Net Developers Only

Sunday 21 September 2014

FAQ--(3- Tier Project)

Pages

Search This Blog