IMPORTANT SQL QUERIES AND ANSWERS (64) FOR BI INTERVIEWS

SOME OF THE ANSWERS MAY NOT BE CORRECT ,PLEASE READ CAREFULLY.

        

1. What is the difference between DBMS and RDBMS?

DBMS(Database management system)

RDBMS(Relational database management system) It supports Single User only It supports multiple users In DBMS no relationship concept It is used to establish the relationship concept between two database objects, i.e, tables DBMS is used for simpler business applications RDBMS is used for more complex applications. DBMS does not impose any constraints or security with regard to data manipulation RDBMS defines the integrity constraint for the purpose of holding ACID PROPERTY In DBMS Normalization process will not be present In RDBMS, normalization process will be present to check the database table consistency FoxPro, IMS are Examples SQL Server, Oracle are examples

2. What do you mean by Isolation level and explain Levels?

An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed.

Read Uncommitted

This is the lowest isolation level there is. Read uncommitted causes no shared locks to be requested which allows you to read data that is currently being modified in other transactions. It also allows other transactions to modify data that you are reading.

Read Committed

This is the default isolation level and means selects will only return committed data. Select statements will issue shared lock requests against data you’re querying this causes you to wait if another transaction already has an exclusive lock on that data. Once you have your shared lock any other transactions trying to modify that data will request an exclusive lock and be made to wait until your Read Committed transaction finishes.

Repeatable Read

This is similar to Read Committed but with the additional guarantee that if you issue the same select twice in a transaction you will get the same results both times. It does this by holding on to the shared locks it obtains on the records it reads until the end of the transaction, This means any transactions that try to modify these records are forced to wait for the read transaction to complete.

Serializable

This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.

Snapshot

This provides the same guarantees as serializable. So what’s the difference? Well it’s more in the way it works, using snapshot doesn’t block other queries from inserting or updating the data touched by the snapshot transaction. Instead row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.

3. Diff between subquery and correlated sub query?

             A subquery is introduced with the keyword EXISTS, it functions as an existence test. The WHERE clause of the outer query tests for the existence of rows returned by the subquery. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

Select Firstname+' '+Lastname from student where studentid in (select courseadminidfrom course where coursename ='Oracle')

            

            In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

Select Coursename, Courseadminid,(select Firstname+' '+Lastname from studentwhere studentid=Course.courseadminid)as CourseAdminName from course.

4. Diff between union and union all?

       UNION ALL statement does not look for duplicate rows, and UNION statement does look for duplicate rows, whether or not they exist.

5. How do you Identify Duplicates? How Many ways we have?

Select EmpCode, EmpName, Address

From Employee

Group by EmpCode, EmpName, Address

Having Count(*)>1

Select * from Employee where EmpID not in (Select Min(EmpID) from Employee Group by EmpCode)

6. How do we eliminate duplicates? Explain in Various ways?

(Write queries using normal, sub query and CTE expression)

Delete from P2 Where P2 %%Physloc%% Not in(Select Min(%%Physloc%%) from P2 Group By EID)

Select Distinct EmpCode,EmpName into #Employee from Employee

Truncate table Employee

Insert into Employee(EmpCode,EmpName)

Select EmpCode,EmpName from #Employee

Delete from Employee where EmpID not in (Select Min(EmpID) from Employee Group by EmpCode)

7. What is CTE? What is the Greatest Advantage of CTE?

A common table expression (CTE) is a temporary storage result set, which will be accessible within the next execution scope of a query.

Advantage: Common Table Expression (CTE) improve the performance of the pagination query in SQL Server as it takes only needed data first then do the calculation as above query.

8. How do you Eliminate duplicates by using CTE?

With S(EID, Ranking)

As(Select EID, Ranking=Dense_Rank()

Over(Partition by EID orderby(NewID() Asc) from P2);

Delete from S where Ranking >1

9. How do you identify Top N salaries? ways?(Top 2/5/10)

Select Top 2 Max(EmpSal) EmpSal from EmpSalary order by EmpSal

Select Max(PartyIncode) from Party where PartyIncome not in (Select Max(PartyIncome) from party)

Select Distinct (P.PartyIncome) from (Select PartyID, PartyIncome, Dense_Rank() Over (Order by PartyIncome Desc) R from Party) P where R<= <<<Nth Value>>>

10. How do you find N the Maximum Salary? Ways?(2nd/3rd)

SubQuery: Select Max(PartyIncome) from Party where PartyIncome Not in (Selet Max(PartyIncome) from party)

Co-related Subquery: Select Distinct (A.partyIncome) from party where <<Nth Max>> =(Select Count(Distinct b.PartyIncome) from Party b where a.PartyIncome < b.PartyIncome)

Note: Nth Max salary use (n-1) inplace of 1

Top N Salaries  Use N > in Place of 1

By the using Dense_Rank() :

Select Distinct (P.PartyIncome) from (Select PartyID, PartyIncome, Dense_Rank() Over (Order by PartyIncome Desc) R from Party) P where R= <<<Nth Max>>>

11. Diff between Cross join and Full Join?

Cross Join : No join conditions are specified. Results in pairs of rows. Results in Cartesian product of two tables. No. of Rows in left table *No. of rows in Right table

Full Outer Join: A combination of both left and right outer joins. Results in every row from both of the tables , at least once. Assigns NULL for unmatched fields.

Matched rows + Unmatched rows(left tbl)+ Unmatched rows(right tbl)

12. Diff between Procedure and Function?

Functions can be used in a select statement were as procedures cannot

Procedure takes both input and output parameters but Functions takes only input parameters

Functions cannot return values of type text, ntext, image & timestamps were as procedures can

Functions can be used as user defined datatypes in create table but procedures cannot

13. What is cursor and Explain Types of Cursors?

Cursors allow row-by-row processing of the result sets. Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [ FORWARD_ONLY | SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ LOCAL | GLOBAL ] – Use LOCAL, cursor will live only in scope of batch. GLOBAL cursor lives in whole connection, I can’t imagine many scenarios where you really need it. [ FORWARD_ONLY | SCROLL ] – Use FORWARD_ONLY, cursor will enable only sequential forward only reading. SCROLL cursor enables to move freely forward and backward which require more resources. [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] – Use FAST_FORWARD, it is the combination of READ_ONLY and FORWARD_ONLY cursor. FAST_FORWARD cursor enables only forward and read only movement which makes it most efficient compared to the other types. STATIC cursor loops over copy of data, not over original data hence doesn’t support any updates. Creating copy of data is very resource-intensive. KEYSET cursor requires unique key identifying rows, these keys are copied in tempdb when cursor is opened. It loops then based on those keys. You must have very good reason to use KEYSET cursor. DYNAMIC cursor immediately reflects any changes made to underlying tables. [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] -READ_ONLY is good but doesn’t support any updates. SCROLL_LOCKS option tells cursor to lock every read row to ensure that updates through cursor will succeed.

14. What are File Group and File Table?

File groups are named collections of files and are used to simplify data placement and administrative tasks such as backup and restore operations. All data files are stored in the file groups listed in the following table. Filegroup Description Primary The filegroup that contains the primary file. All system tables are allocated to the primary filegroup. User-defined Any filegroup that is specifically created by the user when the user first creates or later modifies the database.

15. How do we identify Dept. wise Top 2 Salaries?

Select top 2 D.Name, Max(s.Salary) as Salary

From Dept d join Employee e on d.DeptID=e.DeptID

Join EmpSal s on E.EmpID=s.EmpID

Group by D.Name

Order by Salary Desc

With CT as (

Select *, Row_Number() over(Partition by Did order Sal desc) as R from Party) Select * from Ct where R<=2 Order by Did

Select Did,Max(Sal) from Party Sal Not in (Select Max(Sal from Party Group by Did) Group by Did

Union

Select Did, Max(Sal) from Party Group by Did

16. Display Employee and His manager name from Emp Table?

Select a. EmployeeName, b. EmployeeName as ManagerNames

From Employee a left join Employee b on a. EmpId=b. EmpID

17. First Table Having 2 columns and 2nd Table having 2 columns.

How do we get two tables of data in to another table?

If there is not matching condition.

 By union:

Select Col1, Col2 from Table1

Union all

Select Col3, Col4 from Table2

Cross Join(Because UnCondition)

Select Col1, Col2, Col3, Col4 from Table1

Cross Join Table2

18. There are two tables, I want to identify the people who are available in First table and not available in 2nd table if the cols and tables are line below:

(Note: without using sub query set operation and inequality operators’ use only joins)

Customer

Calls

CID

CNM

CID

CallID

1

X

1

001

2

Y

3

002

Select A.* from Customer a left join Calls b on a.CID=b.CID where b.CID is NULL

Select A.* from Customer a where CID not in (Select CID from Calls)

19. What is Trigger? Diff between after and before triggers?

Triggers are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view

AFTER (default) - The trigger executes after the statement that triggered it completes. If the statement fails with an error, such as a constraint violation or syntax error, the trigger is not executed. AFTER triggers cannot be specified for views.

INSTEAD OF -The trigger executes in place of the triggering action. INSTEAD OF triggers can be specified on both tables and views. You can define only one INSTEAD OF trigger for each triggering action (INSERT, UPDATE, and DELETE). INSTEAD OF triggers can be used to perform enhance integrity checks on the data values supplied in INSERT and UPDATE statements. INSTEAD OF triggers also let you specify actions that allow views, which would normally not support updates, to be updatable.

20. Tell me Trigger Limitations?

Trigger Limitations:- 1. Does not accept arguments or parameters 2. Cannot perform commit

21. What is join? Explain Types of Joins?

Sometimes we have to select data from two or more tables to make our result complete. We have to perform a join.

Joins can be categorized as:

Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include eqi-joins and natural joins. Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

Outer joins. Outer joins can be a left, a right, or full outer join. Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

LEFT JOIN or LEFT OUTER JOIN -The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

RIGHT JOIN or RIGHT OUTER JOIN - A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

FULL JOIN or FULL OUTER JOIN - A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Cross joins - Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.

22. Identify Names starting with 'v' without using like?

Select * from Employee where LEFT(LTRIM(EmployeeName),1)=’V’

Select * from employee where ename between ‘Va’ and ‘Vz’

23. Write Query for the below situation.

PID,SalComp,SalAmount,PID

HRA

DA

TA

1

HRA

1000

1

1000

2000

3000

1

TA

3000

1

DA

2000

DECLARE @SQL VARCHAR(MAX)

DECLARE @ColumnList VARCHAR(MAX)

SELECT @ColumnList = COALESCE(@ColumnList+',','')+'['+Col+']' FROM (select Distinct SalComp from EmployeeSalary) as dtbl ORDER BY ORDERBY

SET @SQL = 'Select PID, '+@ColumnList+' from (Select PID, SalComp, SalAmount from EmployeeSalary) as ST

PIVOT (Sum(SalAmount) for SalComp in ( '+@ColumnList+') )as PT ORDER BY PID'

EXEC(@SQl)

24. Diff between key and index?

Key: It is a logical operation for the storage works on Columns values

Index: It is physical mechanism for storage. There is an effect on data retrieval process.

25. Diff between Clustered & Non Clustered indexes?

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a

clustered index contain the data pages.

 Only one for the table

 Data and index pages at one level

 Always physical representation of rows

 Unique values recommended(if do not pass unique values it will take unifier internally)

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a nonclustered index do not consist of the data pages. Instead, the leaf nodes contain index rows.

 999 on a table(From 2008 onwords)

 Data and index’s are different level.

 No order

 No Physical representation

 Index will store in separate table

 Composition is 1023 columns

 There are 2 Subtypes

o Unique

o Non Unique

26. Diff between Delete and Truncate?

Delete

Truncate

DML

DDL

Locks at row level

Table level

Support rollback

No rollback

Note: Rollback will happened when Transaction at Parent Level

Ex: Transaction at procedure level then Rollback will apply on inside Trigger DMLs

Activate trigger

No Trigger Activations

Keep object statistics and left empty pages

Drop object statistics and no empty pages

Identity sequence values are Continue from existing number

Reset identity values

User can add Where condition

No Where condition

TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. (1) But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log. (2) Because TRUNCATE TABLE is not logged, it cannot activate a trigger. (3) The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. Of course, TRUNCATE TABLE can be rolled back.

27. How do you Generate Sequence in SQL Server?

By the using identity

--- Creating a Table Named Customer

CREATE TABLE Customer

(

Id INT identity(1,1) NOT NULL ,

Name VARCHAR(100) NOT NULL

)

go

----Populating Customer table, using TEST_Sequence to generate the Id column:

INSERT Customer (Name)

VALUES ('Ram'), ('Rita'), ('Ron')

By the using Sequence(from 2012 onwards)

CREATE SEQUENCE TEST_Sequence

AS INT

START WITH 1

INCREMENT BY 1

MINVALUE 0

NO MAXVALUE

--- Creating a Table Named Customer

CREATE TABLE Customer

(

Id INT NOT NULL,

Name VARCHAR(100) NOT NULL

)

go

----Populating Customer table, using TEST_Sequence to generate the Id column:

INSERT Customer (Id, Name)

VALUES

(NEXT VALUE FOR TEST_Sequence, 'Ram'),

(NEXT VALUE FOR TEST_Sequence, 'Rita'),

(NEXT VALUE FOR TEST_Sequence, 'Ron')

28. What is surrogate key and how do we use it? 

Surrogate key is a substitution for the natural primary key. It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table. Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys. They can use Infa sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key.

Create in 3 ways

a. By using identity

b. By using sequence

c. By using custom logic with algorithm

29. Diff Between index scan and index seek? 

With an Index Scan, all rows in the index are being scanned to find a matching row. With an Index Seek, it only needs to touch the rows that actually meet the criteria

30. Diff between nvarchar and varchar?

In principle they are the same and are handled the same by your application.

The only difference is that NVARCHAR can handle unicode characters allowing you to use multiple languages in the database.

Nvarchar: Unicode Variable length data types. Every char occupy 2 bytes

Varchar: Non Unicode Variable length. Every char occupy 1 byte.

31. What is Execution Plan? How do we get it? 

Execution plans can tell you how a query will be executed, or how a query was executed.

32. Tell me date & string Functions in SQL Server?

Date Function:

SYSDATETIME () - Returns a datetime2(7) value. The time zone offset is not included

SYSDATETIMEOFFSET () - Returns a datetimeoffset(7) value. The time zone offset is included

SYSUTCDATETIME () - Returns a datetime2(7) value. The date and time is returned as UTC time (Coordinated Universal Time).

CURRENT_TIMESTAMP - Returns a datetime value. The time zone offset is not included.

GETDATE () - Returns a datetime value. The time zone offset is not included.

GETUTCDATE ()- Returns a datetime value. The date and time is returned as UTC time (Coordinated Universal Time).

DATENAME (datepart, date)- Returns a character string that represents the specified datepart of the specified date.

DATEPART (datepart, date) - Returns an integer that represents the specified datepart of the specified date.

DAY (date) - Returns an integer that represents the day day part of the specified date.

MONTH (date) - Returns an integer that represents the month part of a specified date.

YEAR (date) - Returns an integer that represents the year part of a specified date.

DATEFROMPARTS (year, month, day) - Returns a date value for the specified year, month, and day.

DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)- Returns a datetime2 value for the specified date and time and with the specified precision.

DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)- Returns a datetime value for the specified date and time.

DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)- Returns a datetimeoffset value for the specified date and time and with the specified offsets and precision.

SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)- Returns a smalldatetime value for the specified date and time.

TIMEFROMPARTS (hour, minute, seconds, fractions, precision)- Returns a time value for the specified time and with the specified precision.

DATEDIFF (datepart, startdate, enddate) - Returns the number of date or time datepart boundaries that are crossed between two specified dates.

DATEADD (datepart, number, date) - Returns a new datetime value by adding an interval to the specified datepart of the specified date.

EOMONTH (start_date [,month_to_add])- Returns the last day of the month that contains the specified date, with an optional offset.

SWITCHOFFSET (DATETIMEOFFSET , time_zone)- SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value.

TODATETIMEOFFSET (expression , time_zone) - TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. The datetime2 value is interpreted in local time for the specified time_zone.

ISDATE (expression)- Determines whether a datetime or smalldatetime input expression is a valid date or time value.

String Functions:

ASCII ( character_expression )- Returns the ASCII code value of the leftmost character of a character expression.

CHAR ( integer_expression ) - Converts an int ASCII code to a character.

CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] ) - Searches an expression for another expression and returns its starting position if found.

CONCAT ( string_value1, string_value2 [, string_valueN ] ) - Returns a string that is the result of concatenating two or more string values.

DIFFERENCE ( character_expression , character_expression ) - Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions.

FORMAT (value, format [, culture ]) - Returns a value formatted with the specified format and optional culture in SQL Server 2012. Use the FORMAT function for locale-aware formatting of date/time and number values as strings.

LEFT (character_expression, integer_expression) - Returns the left part of a character string with the specified number of characters.

LEN (string_expression) - To return the number of bytes used to represent an expression

DATALENGTH ( expression ) - Returns the number of bytes used to represent any expression.

LOWER ( character_expression ) - Returns a character expression after converting uppercase character data to lowercase.

LTRIM ( character_expression ) - Returns a character expression after it removes leading blanks.

NCHAR ( integer_expression ) - Returns the Unicode character with the specified integer code, as defined by the Unicode standard.

PATINDEX ( '%pattern%' , expression ) - Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

QUOTENAME ( 'character_string' [ , 'quote_character' ] ) - Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

REPLACE ( string_expression , string_pattern , string_replacement ) - Replaces all occurrences of a specified string value with another string value.

REPLICATE ( string_expression ,integer_expression ) - Repeats a string value a specified number of times.

REVERSE ( string_expression ) - Returns the reverse order of a string value.

RIGHT ( character_expression , integer_expression ) - Returns the right part of a character string with the specified number of characters.

RTRIM ( character_expression ) - Returns a character string after truncating all trailing blanks.

SOUNDEX ( character_expression ) - Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.

SPACE ( integer_expression ) - Returns a string of repeated spaces.

STR ( float_expression [ , length [ , decimal ] ] ) - Returns character data converted from numeric data.

STUFF ( character_expression , start , length , replaceWith_expression ) - The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

SUBSTRING (expression ,start , length ) - Returns part of a character, binary, text, or image expression in SQL Server 2012.

UNICODE ('ncharacter_expression') - Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.

UPPER (character_expression) - Returns a character expression with lowercase character data converted to uppercase.

Logical Functions:

CHOOSE (index, val_1, val_2 [, val_n ]) - Returns the item at the specified index from a list of values in SQL Server 2012.

IIF (boolean_expression, true_value, false_value) - Returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server 2012.

Conversion Functions:

CAST ( expression AS data_type [ ( length ) ] )

CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) - Converts an expression of one data type to another in SQL Server 2012.

PARSE ( string_value AS data_type [ USING culture ] ) - Returns the result of an expression, translated to the requested data type in SQL Server 2012.

TRY_CAST ( expression AS data_type [ ( length ) ] ) - Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] ) - Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

TRY_PARSE ( string_value AS data_type [ USING culture ] ) - Returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server 2012. Use TRY_PARSE only for converting from string to date/time and number types.

33. We have First name and Last Name separated with ":" in a column then how do we get separately two columns?

declare @Name varchar(1000)='Koteswara:Kishore'

select left(@Name,charindex(':',@name)-1) as FirstName,

substring(@Name,charindex(':',@name)+1,len(@Name)) as LastName

34. Diff between Rollup and Cube in SQL Server?

The ROLLUP, CUBE, and GROUPING SETS operators are extensions of the GROUP BY clause.

The ROLLUP and CUBE aggregate functions generate subtotals and grand totals as separate rows, and supply a null in the GROUP BY column to indicate the grand total.

ROLLUP generates subtotal and total rows for the GROUP BY columns.

CUBE extends the capabilities by generating subtotal rows for every GROUP BY column.

ROLLUP and CUBE queries also automatically generate a grand total row.

A special GROUPING () function is true when the row is a subtotal or grand total row for the group.

SELECT LName,GROUPING(Class) AS 'Grouping',

Class,

CASE GROUPING(Class)

WHEN 0 THEN Class

WHEN 1 THEN 'All Classes'

END AS ClassRollup,

SUM(Salary) AS Salary

FROM Students

GROUP BY ROLLUP(Class,LName)

SELECT

CASE GROUPING(Class)

WHEN 0 THEN Class

WHEN 1 THEN 'All Classes'

END AS Class,

CASE GROUPING(LName)

WHEN 0 THEN LName

WHEN 1 THEN 'All Names'

END AS LName,

SUM(Salary) AS Salary

FROM Students

GROUP BY CUBE(LName,Class)

35. Diff between B-Tree index and BIT Map index?

B-Tree

BIT

OLTP sys

OLAP sys

Simply more distinct values recommended

Less distinct values recommended(Low Cordiality)

Mostly used on unique and near unique columns

Supports insert/delete/update operations

Recommended to use select query

36. Diff between Unique & Primary Keys?

Primary Key:

i) Can be only one in a table

ii) It never allows null values

iii) Primary Key is unique key identifier and can not be null and must be unique.

iv) by default Cluster index will create

Unique Key:

i) Can be more than one unique key in one table.

ii) Unique key can have null values

iii) It can’t be candidate key

iv) Unique key can be null and may not be unique.

v) by default Non cluster index will create

37. Diff between Normalization and DE normalization?

Normalization:

It is the process of organizing data into related table.

To normalize database, we divide database into tables and establish relationships between the tables.

It reduces redundancy.

It is done to improve performance of query.

Recommended for OLTP

De-normalization:

The process of adding redundant data to get rid of complex join, in order to optimize database performance.

This is done to speed up database access by moving from higher to lower form of normalization.

Recommended for warehouse applications for analytical and reporting operations

38. What is Normalization and Explain Normal Forms?

First Normal Form (1NF):

Eliminate repeating groups in individual tables

Create a separate table for each set of related data.

Identify each set of related data with a primary key.

Do not use multiple fields in a single table to store similar data.

Second Normal Form (2NF):

Create separate tables for sets of values that apply to multiple records.

Relate these tables with a foreign key.

Records should not depend on anything other than a table's primary key (a compound key, if necessary).

Third Normal Form (3NF):

Eliminate fields that do not depend on the key.

Boyce-Codd Normal Form (BCNF)

A relation is in Boyce/Codd normal form if and only if the only determinants are candidate key.

4th Normal Form (4NF):

A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies. This applies primarily to key-only associative tables, and appears as a ternary relationship, but has incorrectly merged 2 distinct, independent relationships.

5th Normal Form (5NF)(PJNF):

A table is in 5NF, also called "Projection-Join Normal Form", if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.

39. What is Collation & explain Types?

The term, collation, refers to a set of rules that determine how data is sorted and compared.

Windows Collations

Windows collations are collations defined for SQL Server to support the Windows system locales available for the operating system on which SQL Server instances are installed.

SQL Server Collations

SQL Server collations are a compatibility option to match the attributes of common combinations of code-page number and sort orders that have been specified in earlier versions of SQL Server. Many of these collations support suffixes for case, accent, kana, and width sensitivity, but not always.

A collation ties together:

1) Code Page

This is a single-byte character set that represents the alphabet, punctuation & symbols of a language. Data types using a code page are char, varchar & ntext.

Code page does not apply to double-byte (Unicode) characters.

2) Sort Order for Unicode data types.

Unicode file types require double byte storage. Data types are nchar , nvarchar and ntext.

3) Sort order for code page characters

Sort order itself is affected by :

Case Sensitivity

In comparison and sorting operations, are uppercase and lowercase characters considered equal? In a Case Sensitive collation 'A' <> 'a', 'Cat' <> 'CAT' etc.

Accent Sensitivity

In comparison and sorting operations, are accented and unaccented characters considered equal? Does 'a' = '?', 'Bronte' = 'Brontë'?

Kana sensitivity

Japan has 2 types of characters Hiragana and Katakana.

In comparison and sorting operations, are Kana sensitivite collation treated them as equal?

Width sensitivity

In comparison and sorting operations, is a single byte character equal to it's double-byte representation?

Collation naming reveals all this information.

For example, the Latin1_General_CI_AS collation is a Latin code page that is Case Insensitive (CI) and Accent Sensitive (AS).

40. What is Linked server and what is the Use of it?

A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. Linked servers offer the following advantages: 1. Remote server access. 2. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise. 3. The ability to address diverse data sources similarly. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

41. Diff between Primary and Foreign Key?

 By default Primary Key will generate Clustured Index

Where as Unique Key will Generate Non-Clustured Index.

 Primary Key is a combination of Unique and NOT NULL Constraints (so it can’t have duplicate values or any NUll)

Whereas UNIQUE Key can have only one NULL

 A table can have only one PK but It can have any number of UNIQUE Key.

42. Difference between RANK and Row_Number in SQL Server?

ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>)

RANK() OVER ([<partition_by_clause>] <order_by_clause>)

DENSE_RANK() OVER ([<partition_by_clause>] <order_by_clause>)

ROW_NUMBER : Returns a unique number for each row starting with 1. For rows that have duplicate values,numbers are arbitarily assigned.

Rank : Assigns a unique number for each row starting with 1,except for rows that have duplicate values,in which case the same ranking is assigned and a gap appears in the sequence for each duplicate ranking.

WITH T(StyleID, ID)

AS (SELECT 1,1 UNION ALL

SELECT 1,1 UNION ALL

SELECT 1,1 UNION ALL

SELECT 1,2)

SELECT *,

RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS 'RANK',

ROW_NUMBER() OVER(PARTITION BY StyleID ORDER BY ID) AS 'ROW_NUMBER',

DENSE_RANK() OVER(PARTITION BY StyleID ORDER BY ID) AS 'DENSE_RANK'

FROM T

43. What is Dense Rank & When do we go for it?

Returns the rank of rows within the partition of a result set, without any gaps in the ranking DENSE_RANK() function uses, if two or more rows tie for a rank in the same partition, each tied rows receives the same rank, however leaving no gaps in the ranking where there are ties..

44. Diff between Clause and Filter (Where, on, having and Qualify Clauses)?

Clause

Filter

Select

Where

Join

On

Groupby

Having

Rank and Row_Number

Qualify

45. What is nolock and what do you mean by dirty read?

Nolock: is a Query hint to prevents LOCKING on data from being changed while its being accessed.

i.e. when stored procedures/T-Sql scripts are accessing data, the NOLOCK table hint is preventing the sql engine from locking the records, thus speeding up execution time.

Dirty Reads – reading uncommitted data is call it as Dirty read.

When doing this there is no guarantee, the data read will ever be committed meaning the data could well be bad.

46. What is a view and explain view types limitations?

View: Virtual table/window to a table

Mainly designed for getting required rows and columns

Types:
Simple: Created on single table

Complex: Created on multiple tables

Limitations: No order by clause in body

No semicolon in body

Aggregated column should have alias names

Max 32 levels of nested can allow

47. Display Employees whose salary more than Depts Average Salary?

Select * from Party P1 where pt.partyIncome>(Select Avg(P2.partyIncome) from Party P2 where P1.DID=P2.DID

48. Table 1(Col1 Varchar(10),Col2 Varchar(15));

 Table 2(Col1 Varchar(20),Col2 varchar(5));

Load table1 Data to table2 without losing Data.

Insert into Table2.

Select Col1+Substring(Col2,1,10), Substring(Col2,11,5) from Table1

49. Between IN and Exists which runs faster and why?

Exists faster than IN

Since once finds the match stop the process(True or false check)

50. Between Group by & Distinct which Runs faster & why?

Group by is faster than distinct

Group by always occupy less spool(temp space/buffer) memory than distinct

53. How do we Eliminate Duplicates at Retrieval?

By Distinct or Group by

If table having one distinct Not null integer key

Delete from Employee where EID not in (Select Min(EID) from Employee Group by EName, Sal, Address,city)

By the Using Physical Loc

Delete from P2 where P2.%%Physloc%% Not in (Select Min(%%Physloc%%) from P2 Group by EID)

By the using CTE

With S(EID,Ranking)

As(Select EID, Dense_Rank() over(Partition by EID order by (NewID() Asc) as Ranking from P2) Delete from S where Ranking>1

By the Distinct

Select distinct * into #Temp_Employee from Employee

Truncate table Employee

Insert into Employee

Select * from #Temp_Employee

 

55. Diff between Coalesce and NVL?

ISNULL

COALESCE

Takes only 2 parameters.

Takes a variable number of parameters.

A proprietary T-SQL function.

ANSI SQL standard.

Data type returned is the data type of the first parameter.

Data type returned is the expression with the highest data type precedence. If all expressions are non-nullable, the result is typed as non-nullable.

Built-in function implemented in the database engine.

Translates to a CASE expression: COALESCE (exp_1, exp_2, … exp_n)

Translates to CASE WHEN exp_1 IS NOT NULL THEN exp_1 WHEN exp_2 IS NOT NULL THEN exp_2 … ELSE exp_n END

If the data types of both parameters are not determined, the data type returned is int. ISNULL(NULL, NULL) – Returns int

At least one of the NULL values must be a typed NULL. If the data types of all parameters are not determined, the COALESCE function will throw an error: COALESCE(NULL, NULL) – Throws an error COALESCE(CAST(NULL AS INT), NULL) – Returns int

56. Diff between Instr & Substring?

The SUBSTR function returns a specific portion of a string Example: SUBSTR('DotNetFunda',6) The result will be: DotNet

The INSTR function(Charindex in sql Server) provides character position in a pattern of string. Example: CHARINDEX (expression1 ,expression2 [ , start_location ] )

CharIndex('-', 'Dot-Net-Funda',5) The result will be: 8 (Start search '-' from 5th Char)

57. Diff between Join, Subquery and set operation?

Join: Multi table operation and return multiple columns from multiple tables (get the data from more than one table, its work under the condition)

SUB QUERY: is Multi table operation, return outer query operation in the column wise operations

Set operations:

EXCEPT: returns distinct values from the left query that are not found on the right query.

INTERSECT: returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

UNION: Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries.

58. Can we perform insert & Update operations in a single statement?

(OR) What is Merge & how do we use it?

Performs insert, update, or delete operations on a target table based on the results of a join with a source table

Ex:

Declare @UnitMeasureCode nchar(3)= 'ABC',

@Name nvarchar(25)= 'New Test Value'

MERGE Production.UnitMeasure AS target

USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)

ON (target.UnitMeasureCode = source.UnitMeasureCode)

WHEN MATCHED THEN

UPDATE SET Name = source.Name

WHEN NOT MATCHED THEN

INSERT (UnitMeasureCode, Name)

VALUES (source.UnitMeasureCode, source.Name)

OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;

59. What is ACID? 

ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved. Atomicity is an all-or-none proposition. Consistency guarantees that a transaction never leaves your database in a half-finished state. Isolation keeps transactions separated from each other until they’re finished. Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination. Note: Above four rules are very important for any developers dealing with databases.

60. What are E.F.Codd rules

A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular commercial and open source databases currently in use are based on the relational model.

A short definition of an RDBMS may be a DBMS in which data is stored in the form of tables and the relationship among the data is also stored in the form of tables.

E.F. Codd, the famous mathematician has introduced 12 rules for the relational model for databases commonly known as Codd's rules. The rules mainly define what is required for a DBMS for it to be consideredrelational, i.e., an RDBMS. There is also one more rule i.e. Rule00 which specifies the relational model should use the relational way to manage the database. The rules and their description are as follows:-

Rule 0: Foundation Rule: A relational database management system should be capable of using its relational facilities (exclusively) to manage the database.

Rule 1: Information Rule: All information in the database is to be represented in one and only one way. This is achieved by values in column positions within rows of tables.

Rule 2: Guaranteed Access Rule: All data must be accessible with no ambiguity, that is, Each and every datum (atomic value) is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

Rule 3: Systematic treatment of null values: Null values (distinct from empty character string or a string of blank characters and distinct from zero or any other number) are supported in the fully relational DBMS for representing missing information in a systematic way, independent of data type.

Rule 4: Dynamic On-line Catalog Based on the Relational Model: The database description is represented at the logical level in the same way as ordinary data, so authorized users can apply the same relational language to its interrogation as they apply to regular data. The authorized users can access the database structure by using common language i.e. SQL.

Rule 5: Comprehensive Data Sublanguage Rule: A relational system may support several languages and various modes of terminal use. However, there must be at least one language whose statements are expressible, per some well-defined syntax, as character strings and whose ability to support all of the following is comprehensible:

a. data definition

b. view definition

c. data manipulation (interactive and by program)

d. integrity constraints

e. authorization

f. Transaction boundaries (begin, commit, and rollback).

Rule 6: View Updating Rule: All views that are theoretically updateable are also updateable by the system.

Rule 7: High-level Insert, Update, and Delete: The system is able to insert, update and delete operations fully. It can also perform the operations on multiple rows simultaneously.

Rule 8: Physical Data Independence: Application programs and terminal activities remain logically unimpaired whenever any changes are made in either storage representation or access methods.

Rule 9: Logical Data Independence: Application programs and terminal activities remain logically unimpaired when information preserving changes of any kind that theoretically permit unimpairment are made to the base tables.

Rule 10: Integrity Independence: Integrity constraints specific to a particular relational database must be definable in the relational data sublanguage and storable in the catalog, not in the application programs.

Rule 11: Distribution Independence: The data manipulation sublanguage of a relational DBMS must enable application programs and terminal activities to remain logically unimpaired whether and whenever data are physically centralized or distributed.

Rule 12: Nonsubversion Rule: If a relational system has or supports a low-level (single-record-at-a-time) language, that low-level language cannot be used to subvert or bypass the integrity rules or constraints expressed in the higher-level (multiple-records-at-a-time) relational language.

Note that based on these rules there is no fully relational database management system available today. In particular, rules 6, 9, 10, 11 and 12 are difficult to satisfy.

61. I am having one column named ID(non Primary Key) which contains the values like 1,2,3,4,5 etc. If I am trying to insert 4 again that column means then new 4 should insert and old 4 should replace by 5, old 5 should be replaced by 6 like this orderly

Insert a new ID which we'll call @Id UPDATE myTable SET ID = ID +1 WHERE ID > (@Id -1) INSERT INTO myTable (ID, otherColumns) VALUES (@Id, otherValues)