SQL Queries

DROP TABLE EMP
DROP TABLE DEPT
DROP TABLE BONUS
DROP TABLE SALGRADE
DROP TABLE DUMMY

CREATE TABLE EMP
(EMPNO NUMERIC(4) NOT NULL,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR NUMERIC(4),
HIREDATE DATETIME,
SAL NUMERIC(7, 2),
COMM NUMERIC(7, 2),
DEPTNO NUMERIC(2))

INSERT INTO EMP VALUES
(7369, ‘SMITH’, ‘CLERK’, 7902, ’17-DEC-1980′, 800, NULL, 20)
INSERT INTO EMP VALUES
(7499, ‘ALLEN’, ‘SALESMAN’, 7698, ’20-FEB-1981′, 1600, 300, 30)
INSERT INTO EMP VALUES
(7521, ‘WARD’, ‘SALESMAN’,7698, ’22-FEB-1981′, 1250, 500, 30)
INSERT INTO EMP VALUES
(7566, ‘JONES’, ‘MANAGER’, 7839, ‘2-APR-1981’, 2975, NULL, 20)
INSERT INTO EMP VALUES
(7654, ‘MARTIN’, ‘SALESMAN’, 7698, ’28-SEP-1981′, 1250, 1400, 30)
INSERT INTO EMP VALUES
(7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1-MAY-1981’, 2850, NULL, 30)
INSERT INTO EMP VALUES
(7782, ‘CLARK’, ‘MANAGER’, 7839, ‘9-JUN-1981’, 2450, NULL, 10)
INSERT INTO EMP VALUES
(7788, ‘SCOTT’, ‘ANALYST’, 7566, ’09-DEC-1982′, 3000, NULL, 20)
INSERT INTO EMP VALUES
(7839, ‘KING’, ‘PRESIDENT’, NULL, ’17-NOV-1981′, 5000, NULL, 10)
INSERT INTO EMP VALUES
(7844, ‘TURNER’, ‘SALESMAN’, 7698, ‘8-SEP-1981’, 1500, 0, 30)
INSERT INTO EMP VALUES
(7876, ‘ADAMS’, ‘CLERK’, 7788, ’12-JAN-1983′, 1100, NULL, 20)
INSERT INTO EMP VALUES
(7900, ‘JAMES’, ‘CLERK’, 7698, ‘3-DEC-1981’, 950, NULL, 30)
INSERT INTO EMP VALUES
(7902, ‘FORD’, ‘ANALYST’, 7566, ‘3-DEC-1981’, 3000, NULL, 20)
INSERT INTO EMP VALUES
(7934, ‘MILLER’, ‘CLERK’, 7782, ’23-JAN-1982′, 1300, NULL, 10)

CREATE TABLE DEPT
(DEPTNO NUMERIC(2),
DNAME VARCHAR(14),
LOC VARCHAR(13) )

INSERT INTO DEPT VALUES (10, ‘ACCOUNTING’, ‘NEW YORK’)
INSERT INTO DEPT VALUES (20, ‘RESEARCH’, ‘DALLAS’)
INSERT INTO DEPT VALUES (30, ‘SALES’, ‘CHICAGO’)

INSERT INTO DEPT VALUES (40, ‘OPERATIONS’, ‘BOSTON’)

 

CREATE TABLE BONUS
(ENAME VARCHAR(10),
JOB VARCHAR(9),
SAL NUMERIC,
COMM NUMERIC)
CREATE TABLE SALGRADE
(GRADE NUMERIC,
LOSAL NUMERIC,
HISAL NUMERIC)
INSERT INTO SALGRADE VALUES (1, 700, 1200)
INSERT INTO SALGRADE VALUES (2, 1201, 1400)
INSERT INTO SALGRADE VALUES (3, 1401, 2000)
INSERT INTO SALGRADE VALUES (4, 2001, 3000)
INSERT INTO SALGRADE VALUES (5, 3001, 9999)
CREATE TABLE DUMMY
(DUMMY NUMERIC)
INSERT INTO DUMMY VALUES (0)
——————————-

 

SQL QUERIES

–create database TestDB

USE TestDB
CREATE TABLE dbo.Customer( ID INT, FirstName VARCHAR(100),LastName VARCHAR(100),Age INT)
GO
INSERT INTO dbo.Customer
VALUES(1,’Aamir’,’Shahzad’,34)
INSERT INTO dbo.Customer
VALUES(1,’Aamir’,’Shahzad’,34)
INSERT INTO dbo.Customer
VALUES(2,’Raza’,’M’,32)
INSERT INTO dbo.Customer
VALUES(3,’Sukhjeet’,’Singh’,28)
INSERT INTO dbo.Customer
VALUES(4,’Sukhjeet’,’Singh’,28)

select * from dbo.Customer

;WITH CTE
AS (
SELECT FirstName,
LastName,
Age,
Row_number()
OVER (
PARTITION BY FirstName, LastName,Age
ORDER BY (SELECT 1)
) AS Rn
FROM dbo.Customer)

SELECT * FROM CTE

–Delete Duplicate Records and keep only one copy

;WITH CTE
AS (
SELECT FirstName,
LastName,
Age,
Row_number()
OVER (
PARTITION BY FirstName, LastName,Age
ORDER BY (
SELECT 1) ) AS Rn
FROM dbo.Customer)

DELETE FROM CTE WHERE Rn>1

–Find duplicate records by using Group by:
SELECT FirstName,
LastName,
COUNT(*) AS RecordCnt
FROM dbo.Customer
GROUP BY FirstName,
LastName
HAVING COUNT(*) > 1

— What is @@RowCount???
–@@RowCount returns the number of row/s affected by last statment. Let’s see how it is used in real time.
–Let’s say that we want to insert new records from dbo.source table to our dbo.destination table. if IDs do not exist in destination table then insert otherwise update the record. To perform this we will write stored procedure.

–We want to insert the row counts for updated and inserted records in dbo.Audit table in Stored procedure too for audit process.

USE TestDB
GO
–> Prepare dbo.Source Table
CREATE TABLE dbo.Source
(
ID INT,
Name VARCHAR(100)
)
GO
–> Insert couple of records in dbo.Source Table
INSERT INTO dbo.Source
VALUES (1,
‘Aamir’)
INSERT INTO dbo.Source
VALUES (2,
‘Raza’)
GO
–>Create dbo.Destination Table.
CREATE TABLE dbo.Destination
(
ID INT,
Name VARCHAR(100)
)
GO
–> Insert only one records in dbo.Destination table
INSERT INTO dbo.Destination
VALUES (1,
‘Aamir Shahzad’)
GO
–> Create dbo.Aduit table that will hold SourceTable,DestinationTable,updatedRowCnt,InsertRowCnt
CREATE TABLE dbo.Audit
(
id INT IDENTITY(1, 1),
SourceTableName VARCHAR(100),
DestinationTableName VARCHAR(100),
UpdatedRecords INT,
InsertedRecords INT,
LoadDate DATETIME DEFAULT GETDATE()
)
GO
–> Create Stored Procedure for Insert/Update

CREATE PROCEDURE Dbo.Updatedestination
AS
BEGIN
DECLARE @UpdatedRecords INT
DECLARE @InsertedRecprds INT

–>Update Exisiting Records
UPDATE DST
SET DST.Name = SRC.Name
FROM dbo.Destination DST
INNER JOIN DBO.Source SRC
ON DST.ID = SRC.ID
–SET the values of @updatedRecords variable by using @@RowCount
SET @UpdatedRecords=@@ROWCOUNT

–>Insert New Records
INSERT INTO dbo.Destination
(ID,
Name)
SELECT ID,
Name
FROM dbo.Source S
WHERE NOT EXISTS (SELECT 1
FROM dbo.Destination D
WHERE S.ID = D.ID)
–> Set the value of @InsertedRecords variable by using @@ROWCOUNT
SET @InsertedRecprds=@@ROWCOUNT

–> Insert Row Counts into dbo.Audit Table
INSERT INTO dbo.Audit
(SourceTableName,
DestinationTableName,
InsertedRecords,
UpdatedRecords)
SELECT ‘dbo.Source’,
‘dbo.Destination’,
@InsertedRecprds,
@UpdatedRecords
END

–>Execute Stored Procedure couple of times
EXEC dbo.Updatedestination

–> Check the Audit Table
SELECT * FROM dbo.Audit
–Drop All object which created for this scenario
–drop table dbo.Source
–drop table dbo.Destination
–drop table dbo.Audit
–drop procedure dbo.Updatedestination

—————————————————————————————————————-

–TSQL – How To Execute Stored Procedure In View
–There could be scenarios where you want to use a Stored Procedure in view and Join the result set returned by a Stored procedure with some other tables/views.

–Step 1:
–Let s create a table that we want to use for this example

USE TestDB
go
CREATE TABLE dbo.Customer
(
Id INT,
FirstName VARCHAR(50),
LASTName VARCHAR(50)
)GO
INSERT INTO dbo.Customer
VALUES (1,
‘Aamir’,
‘Shahzad’),
(2,
‘Robert’,
‘Ladson’),
(3,
‘John’,
‘Rivers’)

–Step 2:
–Create Stored Procedure that will return all results. In real time scenario you might have multiple SQL statements in SP such as create temp table, common table expressions, updates, deletes , and finally Select statement that is going to return us the results. All depends upon requirement for which the Stored Procedure is written for.

CREATE PROCEDURE dbo.Usp_customer
AS
BEGIN
SELECT ID,
FirstName,
LASTName
FROM dbo.Customer
END

–Step 3:
–Let s create view and execute our dbo.usp_Customer stored procedure by using OpenQuery.

CREATE VIEW dbo.vw_Customer
AS
SELECT *
FROM OPENQUERY([SON15915], ‘EXEC Testdb.dbo.usp_Customer’)

–When we try to execute create view statement , we will get below error if Data Access is not enabled on SQL server.

–Msg 7411, Level 16, State 1, Procedure vw_Customer, Line 3
–Server ‘SERVERNAME\MSSQL2008’ is not configured for DATA ACCESS.
sp_helpserver
–Let s enable DATA ACCESS on SQL Server Instance –SERVERNAME\MSSQL2008
EXEC sp_serveroption ‘SON15915’, ‘DATA ACCESS’, TRUE

–Once DATA ACCESS is enabled , Run the Create View statement. It should run fine.

–Use created view in Query to confirm if all working fine.

SELECT * FROM dbo.vw_Customer
—————————————————————————————————————————-
–To find records which are not available in the destination and which of them are available in the source.

select id,name from source
except
select id,name from Destination

select id,name from Destination
except
select id,name from source

——————————————————————————————————————————
SELECT * FROM EMP — 11 ROWS
SELECT * FROM DEPT — 4

— INNER JOIN –11
SELECT * FROM EMP E
INNER JOIN DEPT D
ON D.DEPTNO=E.DEPTNO
–11
SELECT * FROM EMP E
LEFT JOIN DEPT D
ON D.DEPTNO=E.DEPTNO

–12
SELECT * FROM EMP E
RIGHT JOIN DEPT D
ON D.DEPTNO=E.DEPTNO
–12
SELECT * FROM EMP E
FULL OUTER JOIN DEPT D
ON D.DEPTNO=E.DEPTNO

————————————————

SELECT * FROM CUSTOMER

— BranchID is a FOREIGN KEY in this table
CREATE TABLE Loan
(
LoanID INT IDENTITY ( 1, 1 ) UNIQUE,
CUSTOMERID INT,
BranchID INT,
LoanNumber CHAR(20) NOT NULL UNIQUE,
LoanType VARCHAR(30) NOT NULL,
Amount MONEY NOT NULL,
ModifiedDate DATETIME DEFAULT (getdate()),
PRIMARY KEY ( LoanID )
);

SELECT * FROM CUSTOMER
SELECT * FROM LOAN

INSERT INTO LOAN VALUES(1,10,101,’HOME’,5000000,00)
INSERT INTO LOAN VALUES(2,10,102,’HOME’,6000000,00)
INSERT INTO LOAN VALUES(3,10,103,’PERSONAL’,8000000,00)
INSERT INTO LOAN VALUES(4,10,104,’CAR’,7000000,00)
INSERT INTO LOAN VALUES(5,10,105,’BIKE’,9000000,00)
INSERT INTO LOAN VALUES(6,10,106,’BIKE’,2000000,00)

–TRUNCATE TABLE LOAN

SELECT B.ID,B.FIRSTNAME,B.LASTNAME, A.AMT FROM CUSTOMER B
JOIN
(
SELECT C.ID,SUM(L.AMOUNT) AMT FROM CUSTOMER C
JOIN
LOAN L
ON C.ID = L.CUSTOMERID
GROUP BY C.ID
HAVING SUM(L.AMOUNT)>5000000
) A
ON B.ID = A.ID
ORDER BY A.AMT DESC

———————————-

SELECT B.ID,B.FIRSTNAME,B.LASTNAME FROM CUSTOMER B
WHERE EXISTS(
SELECT C.ID,SUM(L.AMOUNT) AMT FROM CUSTOMER C
JOIN
LOAN L
ON C.ID = L.CUSTOMERID
GROUP BY C.ID
HAVING SUM(L.AMOUNT)>5000000
)

To find specific field value from the XML file.

select Message,
CAST (Message as XMl).value(‘(/Metadossier/BookedBy/Booker/@PerCode)[1]’, ‘varchar(15)’)
from [dbo].[Travel_Tracking] (nolock) WHERE
[Sent] = 0
and [MetaDossier]=’193290722′

What is the difference between Float and Numeric/Decimal in SQL Server – SQL Server / T-SQL

 Float and Real are approximate data types.  Float and Real data types do not store exact values for many numbers.The value can be extremely closed.

Numeric/Decimal are fixed precision data types. It will stored the values with exact precision and scale what you have defined.
If you need to store data where small difference does not matter, you can use Float or Real. But if you need to have exact data such as for financial application. You should be using Numeric/Decimal or Money data type to save exact data, as small difference can impact in calculations.

Example:

Let’s declare two variables and save same data and see what happen to output due to data type of them.

DECLARE @DecimalVariable DECIMAL(8, 2)

SET @DecimalVariable = 213429.95

DECLARE @FloatVariable FLOAT(24)

SET @FloatVariable = 213429.95

SELECT @DecimalVariable AS DecimalVariable
    ,@FloatVariable AS FloatVariable



Some of important SQL Server Questions for Interview.We can say more but I’m writing few things. Let me know if anything is missing/concerns.

1.       What is a View? Can we update with the View or not?
Ans: we can say view is nothing but subset of a table .No need to give all the permissions on the base table so create view and give all the right to different users/vendors.
Yes , we can update with view ,but some exception on view like no aggregation and no joins in a view. Then we can update with the views.

2.       Difference between Delete and Truncate? I have 10 rows in a table and then if I deleted 5 rows from the table , what’s the next continuing number? How can u get the data after Deleting? Explain?
Ans: Delete means Deletes the data from the table and Truncate means total rows truncated from the table. if I roll back after rollback we can get the data whereas after truncate we cannot get the data.
Reason is while creating the database , Database created two files .mdf and  .ldf. mdf contains all the information about data and it has file groups ,default is Primary file group . In this file group have files and every file have Extents , every Extent contains 8 pages(1Extent = 8 pages).All the data store into the pages . Coming to the log file it contains all the information related to who is the user ,when the database is created ,architecture about database.
If I deleted rows from the table , data deleted from the mdf files(pages) on that time only log file maintains the data which is deleted from the mdf. So if we roll back then we can get the data from log file. Coming to the Truncate , truncates the data from mdf and no storage in ldf. So we cannot get the data from ldf after rollback.

I have 10 rows in a table ,if I deleted 5 rows from the table then next number is 11.

3.       Tell me about Indexes? When we need to  go for Indexes? Difference between Clustered Indexes and Non-clustered Indexes?
Ans: Indexes means pointing to the data for the fast retrieving . if we have huge data then need to create Indexes. Otherwise no need to create Indexes.
Coming to the Clustered indexes , we can create Only one .Non-clustered indexes up to 256(not exactly).
Differences are Clustered Indexes have leaf nodes, in that we have data pages .Data pages contains the data. By this we can retrieve the data very fastly. In Non-clustered indexes have leaf nodes ,
In that we have data pages ,Data pages contains addressing of the clustered Indexes data pages. If we use Non-clustered indexes its retrieve from the clustered Indexes data pages.
If we create primary key on a table , automatically clustered index created.

4.Difference between SP and Function?
Ans : Differences are stored proc takes inputs and return Output parameters but in the Functions takes input parameters and it will not give any output parameters, its returns some scalar varibles and table valued variables.

Stored proc is a predefined compiled code no need of execution everytime when initiating from the front end. Its already rendered, thats the reason performance wise good, write a single proc and give to several business users they will give different inputs simaltaneously single strored proc respond accordingly.

4.       Explain about constraints and Triggers? We have constraints ,what is the need of triggers? What are the magic tables?
Ans: Constraints means manipulating the data automatically based on business requirements and rules. Explicitly no need to call  to manipulate the data every time for both constraints and Triggers.
Difference is simple rules we can write in constraints whereas in Triggers any logic(single shot update/insert/delete) we can write for manipulating the data. Triggers means set of sql statements.
Magic tables are Inserted and Deleted. While inserting /updating/deleted , magic tables will created. From this tables data manipulating will happen.

5.       Difference between Rank and Dense_Rank?
Ans : Rank means gap , dense_rank means no gap.
EX : Ranks order : 1 1 3 4 5 5 7
Dense_Rank: 1 1 2 3 4 5 5 6

6.Tell me about Joins?
Ans : Inner Joins
Outer joins (left, right, fullouter join)
Self join
Cross join

6.       Table A contains 5 rows and Table B contains ‘0’ rows ,  select * from A,B? what’s the output?
Ans :  0 rows.

7.       Difference between except and Intersect?
8.       Primary key and foreign key?
9.       What is the identity?
10.   Difference between Temp variables and Global variables?
11.   Write a query  the second highest salary?

Select max(salary) as salary from emp
where salary < select max(salary) from emp

5th highest Salary:

select min(salary)from

(select distinct top 5 salary from emp

order by salary desc

)a

12.   How do you add a column?
Alter table tablename
Add columnname datatype
Ex: alter table emp
Add gender varchar(5)

13.   Tell me default databases in Sql server?
Ans: Master, model, msdb, tempdb.

14.   How do improve performance while executing queries or sp’s?
15.   What is a subquery?
16.   What is a derived table?
17.   Types of constraints?
Ans; primary key, check, foreign key…
18.   Explain about primary key  and foreign key?
19.   Difference between union and union all?
20.   Difference between char , varchar and nchar or nvarchar?

Scripts:

—————————————————–

To Restore the Database:

use master

exec master.dbo.xp_restore_database @database = N’msdb’ ,

@filename = N’G:\Rambabu\BackUp\msdb.lsb’,

@filenumber = 1,

@with = N’REPLACE’,

@with = N’STATS = 10′,

@affinity = 0,

@logging = 0

GO   — similarly we can restore any database.

——————————————————-

To view the who is logging into the server:

use msdb

sp_who2

——————————————————– 

 To kill the user in the database:

DECLARE @cmdKill VARCHAR(50)

DECLARE killCursor CURSOR FOR

SELECT ‘KILL ‘ + Convert(VARCHAR(5), p.spid)

FROM master.dbo.sysprocesses AS p

WHERE p.dbid = db_id(‘msdb’)

OPEN killCursor

FETCH killCursor INTO @cmdKill

WHILE 0 = @@fetch_status

BEGIN

EXECUTE (@cmdKill)

FETCH killCursor INTO @cmdKill

END

CLOSE killCursor

DEALLOCATE killCursor

——————————————————————-

Use the following scripts for changing the job owner information and also changing the server name in the packages:

SELECT SL.name, ‘|’, SJ.[name], SJ.[job_id], SJ.[owner_sid] AS old_owner_sid

FROM msdb..sysjobs SJ

       INNER JOIN MASTER..[syslogins] SL ON SJ.[owner_sid] = SL.[sid]

WHERE SL.[isntname] = 1 AND SJ.enabled =0

and SJ.[owner_sid]=0x01

       AND SL.[name] = ‘REDMOND\prwprod’

DECLARE @new_owner_sid VARBINARY(200)

SELECT @new_owner_sid = [sid] FROM MASTER..syslogins WHERE [name] = ‘REDMOND\bizopsdw’;

WITH jobs_to_change(old_owner_name, job_id, name, old_owner_sid) AS

       (SELECT SL.name, SJ.[job_id], SJ.[name], SJ.[owner_sid] AS old_owner_sid

       FROM msdb..sysjobs SJ

              INNER JOIN MASTER..[syslogins] SL ON SJ.[owner_sid] = SL.[sid]

       WHERE SL.[isntname] = 1

              AND SL.[name] like ‘REDMOND\prwprod’)

UPDATE msdb..sysjobs

SET owner_sid = @new_owner_sid

FROM msdb..sysjobs SJ INNER JOIN jobs_to_change JTC ON SJ.job_id = JTC.job_id;

—————————————————————————–

Replace command:

• To view:

select command, replace(command, ‘AdventureWorks_org’,’AdventureWorks_replace’) as newcmd, * from sysjobsteps

where command like ‘%AdventureWorks_org%’

• for Update:

update sysjobsteps set command =replace(command, ‘AdventureWorks_org’,’AdventureWorks_replace’) where command like ‘%AdventureWorks_org%’

———————————————————————————————–

Exists function:

IF NOTEXISTS(SELECT*FROMINFORMATION_SCHEMA.TABLESWHERE TABLE_NAME =‘BulkInsertData_Source’)  

CREATE TABLE [dbo].[BulkInsertData_Source](
[PRID] [uniqueidentifier] NOTNULL, [PRCode] [int] NOTNULL, [PRDesc] [varchar] (100)NOTNULL, [PRTest1] [varchar] (100)NULL, DateField DATETIMe)

For example, you can use the sysobjects system table to drop a table as shown in the following Transact-SQL statement.

1. if exists (select * from sysobjects where name = ‘tblTest1908’)
drop table tblTest1908

Instead, you can use the following Transact-SQL statement.

2. if object_id(‘tblTest1908’) is not null

 drop table tblTest1908

————————————————————————————————-

Add a constraint to the table:

ALTER TABLE [dbo].[CustomerTable] ADD  CONSTRAINT [DF_CustomerTable_AxaptaProcessDateTime]
DEFAULT (‘1900-01-01’) FOR [AxaptaProcessDateTime]

—————————————————————————————————

Types of SQL Keys:

We have following types of keys in SQL which are used to fetch records from tables and to make relationship among tables or views.

Super Key:

Super key is a set of one or more than one keys that can be used to identify a record uniquely in a table.Example : Primary key, Unique key, Alternate key are subset of Super Keys.

Candidate Key:

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

Primary Key:

Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table. It cannot accept null, duplicate values. Only one Candidate Key can be Primary Key.

Alternate key:

A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.

Example: In below diagram RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.

Composite/Compound Key:

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

Unique Key:

Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values. For more help refer the article Difference between primary key and unique key.

Foreign Key:

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. For more help refer the article Difference between primary key and foreign key.

Example : We can have a DeptID column in the Employee table which is pointing to DeptID column in a department table where it a primary key.

Defining Keys in SQL Server

–Department Table

CREATE TABLE Department(

DeptID int PRIMARY KEY, –primary key

Name varchar (50) NOT NULL,

Address varchar (200) NOT NULL

)

–Student Table

CREATE TABLE Student

(

ID int PRIMARY KEY, –primary key

RollNo varchar(10) NOT NULL,

Name varchar(50) NOT NULL,

EnrollNo varchar(50) UNIQUE, –unique key

Address varchar(200) NOT NULL,

DeptID int FOREIGN KEY REFERENCES Department(DeptID) –foreign key

)

————————————————————————————————

 

To create a linked server (SQL Server 2005 and SQL Server 2008):

1. Click Start, click All Programs, click Microsoft SQL Server 2005 or Microsoft SQL Server 2008, and then click SQL Server Management Studio.
2. In the Connect to Server dialog box, specify the name of the appropriate SQL Server, and then click Connect.
3. In SQL Server Management Studio, double-click Server Objects, right-click Linked Servers, and then click New Linked Server.
4. In the New Linked Server dialog box, on the General page, in Linked server, enter the full network name of the SQL Serveryou want to link to.
5. Under Server type, click SQL Server.
6. In the left pane of the New Linked Server dialog, under Select a page, choose Security.
7. You will need to map a local server login to a remote server login. On the right side of the Security page, click the Add button.
8. Under Local Login, select a local login account to connect to the remote server. Check Impersonate if the local login also exists on the remote server. Alternatively, if the local login will be mapped to a remote SQL Server login you must supply the Remote User name and Remote Passwordfor the remote server login.
9. In the left pane of the New Linked Server dialog, under Select a page, choose Server Options. Set the Rpc and Rpc Out parameters to True, and then click OK.

——————————————————————————————————————————————–

create table emp1

(

Empno

intidentity(100,1)primarykey,

Ename nvarchar(50),

Salary money,

Deptno int

)

create table Manager

(

Mgrno intprimarykey,–constraint fk_mgr foreign key ([mgrno]) references emp(mgrno),

Mgrname nvarchar(50),

)

drop table manager

select *from emp

alter table [dbo].[emp] withcheckaddconstraint fk_mgr foreignkey ([mgrno])references [dbo].[emp](mgrno)

drop constraint fk_mgr

ALTER TABLE [dbo].[emp]  WITHCHECKADD  CONSTRAINT [FK_mgr] FOREIGNKEY([mgrno])

REFERENCES [dbo].[Manager]([mgrno])

alter table emp

add  mgrno int

select *from emp

To Drop a column:

alter table emp

drop column location

To copy the data into another table:

select * into emp1 from emp

insert into dbOpsmetrics_TestAdminUI2..emp1 values (ename,salary,Deptno)

select * from dbOpsmetrics_TestAdminUI2..emp

select *from emp1

drop table emp1

create table Dept(Deptno intprimarykey,Deptname nvarchar(50),Location nvarchar(50),)

ALTER TABLE [dbo].[emp]  WITHCHECKADD  CONSTRAINT [FK_emp] FOREIGNKEY([deptno]) REFERENCES [dbo].[dept]([deptno])

insert into emp values(‘Raj’,20000,20)

insert into emp values(‘Rakesh’,10000,30)

insert into emp values(‘Navin’,30000,40)

select *from emp

select *from Dept

–1) Find the dept which department has no employees…

select deptname,Deptno from Dept where Deptno not in(select Deptno from emp)

–2) Dept wise salary…

select d.Deptno, d.Deptname,e.SumSal from Dept d

JOIN

( selectsum(salary)SumSal,deptno from emp

group by deptno

having SUM(salary)> 200000 ) e

on d.Deptno = e.Deptno

–3)  5th highest salary

select min(salary)from(select distinct top 5 salary from emp

order by salary desc) a

select MAX(Salary)from emp

— 4. top second salary

select min(salary)from(selectdistincttop 2 Salary from emp order by Salary desc)a

select *from Dept

update Dept

set Location =REPLACE(location,‘A’,‘a’)

where Location like‘%Mumbai%’

GO

select *from emp where mgrno isnull

select m.mgrname,n.SumSal from manager m

join

(select mgrno,SUM(salary) SumSal from emp

group by mgrno) n

on m.mgrno = n.mgrno

select *from emp

— To find the manager of the organization

select me.empno, me.ename , e.ename Manager, e.empno MgrNo from emp e

join

emp me

on e.empno = me.mgrno

–1)create a database script

create

database raju_kerala onprimary

(

name =‘raju_kerala’,

filename=‘E:\program files\microsoft sql server\mssql.1\mssql\data\raju_kerala.mdf’,

size= 3072kb,
filegrowth= 1024kb

)

log on

(

name =‘raju_log’,

filename=‘E:\program files\microsoft sql server\mssql.1\mssql\data\raju_log.ldf’,

size= 1024kb,

filegrowth= 10%

)

–2)copying data from one database to another.

select

*into emp from dbOpsmetrics_TestAdminUI.dbo.emp

select *into dept from dbOpsmetrics_TestAdminUI.dbo.dept

–1) INFO R — HYDERABAD

select dept.deptno,dname,tempemp.noofemployees from dept

join

(select deptno,count(*)as noofemployees from emp

groupby deptno

)as tempemp

on tempemp.deptno = dept.deptno

–1)EF information — Identifying the boss

select e2.ename as emp,e1.ename as boss from emp as e1

join

emp as e2

on e1.empno = e2.mgr

–2)outputs

select 1 where 1=1

select 1 where 1=0

select 1 where 1=null

select 1 wherenull=null

–3) Ranking functions

with abc(empno,ename,salary,Rank)

as

(select empno,ename,salary,rank()over(orderby salary desc)as temp

from emp

)

select *from abc  where rank= 6

with abc(empno,ename,salary,Drank)

as

(select empno,ename,salary,dense_rank()over(orderby salary desc)as drank

from emp)

select *from abc

where drank = 6

–4) XML stroing

select *from emp

for xmlauto,root(‘xmlemp’)

–2) CSSI – HYDERABAD

declare @count int

–declare @SNO char(5)

set @count = 0

print ‘SNO’

while @count<10

Begin

set @count = @count+1

print @count

End

)

select firstname+‘ ‘+lastname as FullName  from customers order by fullname

— SNL CORPORATE – CHANDIGARH

–1)Return the First Name, Last Name, Sale Price, Recommended Sale Price,

–and the difference between the Sale Price and Recommended Sale Price for all Sales.

–The difference must be returned as a positive number.

select firstname+‘ ‘+lastname as Name,abs(sales.saleprice – products.recommendedprice)as diff from customers

inner join

sales

on sales.customerid = customers.customerid

inner join

products

on products.productid = sales.productid

–2)Return the Product Category and the average Sale Price for

–those customers who have purchased two or more products.

select category,avgsales from products

join
(select productid,avg(saleprice)as avgsales from sales

groupby productid

havingcount(*)>2

)as sal

on products.productid = sal.productid

select *from sales

–3) Update the Sale Price to the Recommended Sale Price of

–those Sales occurring between 6/10/2005 and 6/20/2005

update sales    

set saleprice =products.recommendedprice from sales as  sal

where saledate between 6/10/2005 and 6/20/2005
Update Sales

Set SalePrice = P.RecommendedPrice