Msc(IT) DBMS Important Questions of SQL Server and Oracle

SQL SERVER QUESTIONS

1). Explain 12 rule defined by dr. E.F.Codd?

2). What is T-sql ? Explain T-sql Statements ?

3). Explain Database Architecture (physical Architecture, logical architecture, pages and extents, database file and file groups)?

4). Explain Components of SQl Server 2000.

5). Explain Features of SQl Server 2000?

6). What Stored Procedure? List and Explain category of stored Procedure.

7). Explain Six Layer Security Available In SQL server?

9). What is function? List and explain types of SQL Server function?

10). What is index? Explain index Architecture in detail? Give syntax to create a index using t-sql statement.

11). What is cursor? Give Example to create a cursor?

12). What is trigger? Explain types of trigger?

13). Explain BULK INSERT in detail?

14). Explain types of transactions in SQL Server?

15). Explain SQL Profiler, Query analyzer, Enterprise Manager, View in SQL Server 2000.

DIFFERENCES

1). Primary Key Vs Foreign Key Vs Unique Key

2). Truncate Vs Delete

3). SmallDatetime Vs DateTime

4). User Defined Datatype Vs System Defined Datatype

5). Where Vs Having

6). Store Procedure Vs Functions

7). Rollup Vs Cube

8). Charindex() Vs Patindex()

9). Implicit Transaction Vs Explicit Transaction

10). Query Analyzer Vs Enterprise Manager

ORACLE

1). List and Explain Various Startup and Shutdown Methods?

2). Explain the difference between ArchiveLog Mode and NoArchiveLog mode and the benefits and disadvantages of each.

3). Explain difference between Hot Backup and Cold Backup?

4). What is Tablespace? List and Explain different types of Tablespace available in Oracle.

5). Explain types of Backup available in Oracle?

6). Write a steps to Create new Database Manually in Oracle?

7). What is Oracle Instance? Explain Background Processes and SGA?

8). What is tuning? Explain various types of tuning?

9). What is Contention? Explain Contention Issues?

10). What is Auditing? Explain different types of Auditing?

11). Explain Resource Management in detail? (Profile)

12). Explain Database Role in detail. Write a steps to create a role?

13). Explain following terms :

Segments	RowID	Auditing
Extents	Tablespace	Oracle Database
Profile	Init.Ora	PMON
SMON	LGWR	DBWR
CHECKPOINT	Redolog	Control File

 14). What is tuning? Explain tuning at Database Level?

 15). Differentiate Manual Archiving Vs Automatic Archiving?

How to remove Duplicate records from table

This is the frequently asked question in an interview. How to remove or select distinct all data with the unique value. You all are knows that whenever we write distinct keyword with column it will returns unique combination of all columns.

In following query if you use distinct then it will return all rows then how to eliminate particular column duplication.You can not use distinct at this moment. I have a solution for this.

Just copy and paste following code in your SQL Server 2005 query window and then run it.

 
DECLARE @EMPDATA TABLE(EMPNO BIGINT PRIMARY KEY,EMAIL VARCHAR(50))

INSERT INTO @EMPDATA VALUES(1,'parag@gmail.com')

INSERT INTO @EMPDATA VALUES(2,'deven@gmail.com')

INSERT INTO @EMPDATA VALUES(3,'parag@gmail.com')

INSERT INTO @EMPDATA VALUES(4,'jig@gmail.com')

INSERT INTO @EMPDATA VALUES(5,'dharmik@gmail.com')

INSERT INTO @EMPDATA VALUES(6,'jig@gmail.com')

INSERT INTO @EMPDATA VALUES(7,'sandy@gmail.com')

INSERT INTO @EMPDATA VALUES(8,'dharmik@gmail.com')

;WITH CTE

AS

(

   SELECT ROW_NUMBER()OVER(PARTITION BY EMAIL ORDER BY  EMPNO)RANK,*

     FROM @EMPDATA

)

SELECT EMPNO,EMAIL FROM CTE

WHERE RANK=1

ORDER BY EMPNO

In above query it will just select the unique value if you want to remove then write DELETE statement where rank = 1.

Difference between DENSE_RANK() and ROW_NUMBER()

The difference between DENSE_RANK() and Row_number() is that Row_Number will generate a sequence of record one by one while DENSE_RANK() will also generate a sequence, but it is depend upon the field if field value is repeated then Rank of Dense will also repeat.




Following is the example that demonstrate a difference between both of them.

create table department(
    dept_name     char(20)     not null,
    emp_cnt       int          not null,
    budget        float,
    date_month    datetime);
  GO

  insert into department values('Research', 5, 50000, '01.01.2002');
  insert into department values('Research', 10, 70000, '01.02.2002');
  insert into department values('Research', 5, 65000, '01.07.2002');
  insert into department values('Accounting', 5, 10000, '01.07.2002');
  insert into department values('Accounting', 10, 40000, '01.02.2002');
  insert into department values('Accounting', 6, 30000, '01.01.2002');
  insert into department values('Accounting', 6, 40000, '01.02.2003');
  insert into department values('Marketing', 6, 10000, '01.01.2003');
  insert into department values('Marketing', 10, 40000, '01.02.2003');
  insert into department values('Marketing', 3, 30000, '01.07.2003');
  insert into department values('Marketing', 5, 40000, '01.01.2003');
  GO

  SELECT DENSE_RANK() OVER(ORDER BY budget DESC) AS rank_budget, 
ROW_NUMBER() OVER(ORDER BY budget DESC) AS row_number,dept_name, emp_cnt, budget
  FROM department
  WHERE budget <= 50000;
  GO


rank_budget          row_number           dept_name          emp_cnt     budget
---------------- -------------------- -------------------- ---------- ------------
     1                    1               Research                 5     50000
     2                    2               Accounting              10     40000
     2                    3               Accounting               6     40000
     2                    4               Marketing               10     40000
     2                    5               Marketing                5     40000
     3                    6               Marketing                3     30000        3                    7               Accounting               6     30000
     4                    8               Accounting               5     10000
     4                    9               Marketing                6     10000


SQL SERVER – 2005 – Find Nth Highest Record from Table – Using CTE

This question is quite a popular question and it is frequently asked question in interview that retrieve Nth highest record from table you can use top keyword and distinct and temp table. But, it will degrade your performance so in practice, you can easily use New features of SQL Server 2005 and that is CTE. Let us see....

USE AdventureWorks
GO


WITH SALCTE

AS

(

      SELECT e1.*,

      Row_number() OVER(ORDER BY e1.Rate DESC) AS Rank
      FROM HumanResources.EmployeePayHistory AS e1

)
SELECT * FROM SALCTE

WHERE Rank = 4

It will give you 4th highest record. Suppose if you want 5th, 6th or nth highest record then write 5th, 6th or nth instead of Rank = 4. 

SQL Challenge for Beginners - Find Factorial

The Challenge for Beginners

This challenge is not a real time problem directly, but it measures about logical thinking. The problem is all about finding the factorial of numbers. Though it is known to most of us what a factorial is, but to recall the concept here is an example:

Factorial of 3 is 1*2*3 = 6 i.e. the factorial of a non-negative integer n, denoted by n!, is the product of all positive integers less than or equal to n.

Sample Input Data

Nums
-----------
0 
1 
3 
5 
10

Expected Output

Nums        Factorial
----------- -----------
0                    1
1                    1
3                    6
5                  120
10             3628800

Script

Here is the script to generate the sample data

DECLARE @Fact TABLE(Nums INT)
INSERT INTO @Fact 
SELECT 0 UNION ALL  
SELECT 1 UNION ALL  
SELECT 3 UNION ALL  
SELECT 5 UNION ALL  
SELECT 10

SELECT * FROM @Fact

Solution :

DECLARE @Fact TABLE(Nums INT)
INSERT INTO @Fact 
SELECT 0 UNION ALL 
SELECT 1 UNION ALL 
SELECT 3 UNION ALL 
SELECT 5 UNION ALL 
SELECT 10

DECLARE @no INT,@f BIGINT,@nums INT

DECLARE C1 CURSOR FOR SELECT nums FROM @fact
OPEN C1
FETCH NEXT FROM C1 INTO @no
WHILE @@FETCH_STATUS=0
BEGIN
        SELECT @f=1,@nums=@no
        WHILE @no>0
        BEGIN
             SET @f=@f*@no
             SET @no=@no-1 
        END
        DECLARE @t TABLE(nums INT,Fact BIGINT)
        INSERT INTO @T SELECT @Nums,@F
        FETCH NEXT FROM C1 INTO @no
END
CLOSE C1
DEALLOCATE C1 

SELECT * FROM @T ORDER BY 1

Just Paste Above solution in your SQL Server You will get a

Output that really you need.

Basic of Join

INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.


RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.


FULL OUTER JOIN
This join combines left outer join and right after join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Additional Notes related to JOIN:

The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.

SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
GO

The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.

/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The above example can also be created using Right Outer Join.


NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.

Get Date Time in Any Format – UDF – User Defined Functions

Following User Defined Function will give you date in Any format Whichever you want. For the output of this function see the end of User Defined Function.

CREATE FUNCTION [dbo].[UfDateFormat]
(
@Date datetime,
@fORMAT VARCHAR(80)
)
RETURNS NVARCHAR(80)
AS
BEGIN
DECLARE @Dateformat INT
DECLARE @ReturnedDate VARCHAR(80)
DECLARE @TwelveHourClock INT
DECLARE @Before INT
DECLARE @pos INT
DECLARE @Escape INT

SELECT @ReturnedDate='error! unrecognised format '+@format
SELECT @DateFormat=CASE @format
WHEN 'mmm dd yyyy hh:mm AM/PM' THEN 100
WHEN 'mm/dd/yy' THEN 1
WHEN 'mm/dd/yyyy' THEN 101
WHEN 'yy.mm.dd' THEN 2
WHEN 'dd/mm/yy' THEN 3
WHEN 'dd.mm.yy' THEN 4
WHEN 'dd-mm-yy' THEN 5
WHEN 'dd Mmm yy' THEN 6
WHEN 'Mmm dd, yy' THEN 7
WHEN 'hh:mm:ss' THEN 8
WHEN 'yyyy.mm.dd' THEN 102
WHEN 'dd/mm/yyyy' THEN 103
WHEN 'dd.mm.yyyy' THEN 104
WHEN 'dd-mm-yyyy' THEN 105
WHEN 'dd Mmm yyyy' THEN 106
WHEN 'Mmm dd, yyyy' THEN 107
WHEN 'Mmm dd yyyy hh:mm:ss:ms AM/PM' THEN 9
WHEN 'Mmm dd yyyy hh:mi:ss:mmm AM/PM' THEN 9
WHEN 'Mmm dd yy hh:mm:ss:ms AM/PM' THEN 109
WHEN 'mm-dd-yy' THEN 10
WHEN 'mm-dd-yyyy' THEN 110
WHEN 'yy/mm/dd' THEN 11
WHEN 'yyyy/mm/dd' THEN 111
WHEN 'yymmdd' THEN 12
WHEN 'yyyymmdd' THEN 112
WHEN 'dd Mmm yyyy hh:mm:ss:Ms' THEN 113
WHEN 'hh:mm:ss:Ms' THEN 14
WHEN 'yyyy-mm-dd hh:mm:ss' THEN 120
WHEN 'yyyy-mm-dd hh:mm:ss.Ms' THEN 121
WHEN 'yyyy-mm-ddThh:mm:ss.Ms' THEN 126
WHEN 'dd Mmm yyyy hh:mm:ss:ms AM/PM' THEN 130
WHEN 'dd/mm/yy hh:mm:ss:ms AM/PM' THEN 131
WHEN 'RFC822' THEN 2
WHEN 'dd Mmm yyyy hh:mm' THEN 4
ELSE 1 END
SELECT @ReturnedDate='error! unrecognised format ' +@format+CONVERT(VARCHAR(10),@DateFormat)
IF @DateFormat>=0
SELECT @ReturnedDate=CONVERT(VARCHAR(80),@Date,@DateFormat)
--check for favourite and custom formats that can be done quickly
ELSE IF @DateFormat=-2--then it is RFC822 format
SELECT @ReturnedDate=LEFT(DATENAME(dw, @Date),3) + ', ' + STUFF(CONVERT(NVARCHAR,@Date,113),21,4,' GMT')
ELSE IF @DateFormat=-4--then it is european day format with minutes
SELECT @ReturnedDate=CONVERT(CHAR(17),@Date,113)
ELSE
BEGIN
SELECT @Before=LEN(@format)
SELECT @Format=REPLACE(REPLACE(REPLACE( @Format,'AM/PM','#'),'AM','#'),'PM','#')
SELECT @TwelveHourClock=CASE WHEN @Before >LEN(@format) THEN 109 ELSE 113 END, @ReturnedDate=''
WHILE (1=1)--forever
BEGIN
SELECT @pos=PATINDEX('%[yqmidwhs:#]%',@format+' ')
IF @pos=0--no more date format strings
BEGIN
SELECT @ReturnedDate=@ReturnedDate+@format
BREAK
END
IF @pos>1--some stuff to pass through first
BEGIN
SELECT @escape=CHARINDEX ('\',@Format+'\') --is it a literal character that is escaped?
IF @escape<@pos BEGIN
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@escape-1) +SUBSTRING(@format,@escape+1,1)
SET @format=RTRIM(SUBSTRING(@Format,@Escape+2,80))
CONTINUE
END
SET @ReturnedDate=@ReturnedDate+SUBSTRING(@Format,1,@pos-1)
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END
SELECT @pos=PATINDEX('%[^yqmidwhs:#]%',@format+' ')--get the end
SELECT @ReturnedDate=@ReturnedDate+--'('+substring(@Format,1,@pos-1)+')'+
CASE SUBSTRING(@Format,1,@pos-1)
--Mmmths as 1--12
WHEN 'M' THEN CONVERT(VARCHAR(2),DATEPART(MONTH,@Date))
--Mmmths as 01--12
WHEN 'Mm' THEN CONVERT(CHAR(2),@Date,101)
--Mmmths as Jan--Dec
WHEN 'Mmm' THEN CONVERT(CHAR(3),DATENAME(MONTH,@Date))
--Mmmths as January--December
WHEN 'Mmmm' THEN DATENAME(MONTH,@Date)
--Mmmths as the first letter of the Mmmth
WHEN 'Mmmmm' THEN CONVERT(CHAR(1),DATENAME(MONTH,@Date))
--Days as 1--31
WHEN 'D' THEN CONVERT(VARCHAR(2),DATEPART(DAY,@Date))
--Days as 01--31
WHEN 'Dd' THEN CONVERT(CHAR(2),@date,103)
--Days as Sun--Sat
WHEN 'Ddd' THEN CONVERT(CHAR(3),DATENAME(weekday,@Date))
--Days as Sunday--Saturday
WHEN 'Dddd' THEN DATENAME(weekday,@Date)
--Years as 00--99
WHEN 'Yy' THEN CONVERT(CHAR(2),@Date,12)
--Years as 1900--9999
WHEN 'Yyyy' THEN DATENAME(YEAR,@Date)
WHEN 'hh:mm:ss' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8)
WHEN 'hh:mm:ss:ms' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'h:mm:ss' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,8)
--tthe SQL Server BOL syntax, for compatibility
WHEN 'hh:mi:ss:mmm' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'h:mm:ss:ms' THEN SUBSTRING(CONVERT(CHAR(30),@date,@TwelveHourClock),13,12)
WHEN 'H:m:s' THEN SUBSTRING(REPLACE(':'+SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,8),':0',':'),2,30)
WHEN 'H:m:s:ms' THEN SUBSTRING(REPLACE(':'+SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,12),':0',':'),2,30)
--Hours as 00--23
WHEN 'hh' THEN REPLACE(SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,2),' ','0')
--Hours as 0--23
WHEN 'h' THEN LTRIM(SUBSTRING(CONVERT(CHAR(30), @Date,@TwelveHourClock),13,2))
--Minutes as 00--59
WHEN 'Mi' THEN DATENAME(minute,@date)
WHEN 'mm' THEN DATENAME(minute,@date)
WHEN 'm' THEN CONVERT(VARCHAR(2),DATEPART(minute,@date))
--Seconds as 0--59
WHEN 'ss' THEN DATENAME(second,@date)
--Seconds as 0--59
WHEN 'S' THEN CONVERT(VARCHAR(2),DATEPART(second,@date))
--AM/PM
WHEN 'ms' THEN DATENAME(millisecond,@date)
WHEN 'mmm' THEN DATENAME(millisecond,@date)
WHEN 'dy' THEN DATENAME(dy,@date)
WHEN 'qq' THEN DATENAME(qq,@date)
WHEN 'ww' THEN DATENAME(ww,@date)
WHEN '#' THEN REVERSE(SUBSTRING(REVERSE(CONVERT(CHAR(26), @date,109)),1,2))
ELSE
SUBSTRING(@Format,1,@pos-1)
END
SET @format=RTRIM(SUBSTRING(@Format,@pos,80))
END
END
RETURN @ReturnedDate
END
GO

-- Output of User Defined Function
SELECT [dbo].[UFDateFormat] ('8/7/2008', 'mm/dd/yy')
GO
SELECT [dbo].[UFDateFormat] ('8/7/2008', 'hh:mm:ss')
GO
SELECT [dbo].[UFDateFormat] ('8/7/2008', 'mmm')
GO
SELECT [dbo].[UFDateFormat] ('8/7/2008', 'Mmm dd yyyy hh:mm:ss:ms AM/PM')
GO
SELECT [dbo].[UFDateFormat] ('8/7/2008', '#')
GO