| data-domain-sql
What are the salient features of database ?
Database follows acid property, It provides indexing and querying feature thus making retrieval process faster, It takes full advantage of normalization, We need to use reporting or intelligence tools to work out business problems, Databases are concurrent so multiple users can use them at the same time without corrupting the data and also the database from remote location, Exception handling is feasible. |
| data-domain-sql
What is difference between SQL Server 2008 and SQL Server 2012
ORDER BY Clause modified with FETCH & OFFSET Function like FORMAT, CONCAT, TRY_CONVERT introduced Column store index is used for storage It Use 48 bit precision(instead of 27 bit) for Spatial Calculation |
| data-domain-sql
What is column store index ? What is it's benefit ?
The column store index in SQL Server 2012 stores columns instead of rows.
|
| data-domain-sql
How can you create the structure of a table similar to another table TRANSPOSE ?
SELECT * INTO NEW_TABLE FROM TRANSPOSE WHERE 1 = 2
|
| data-domain-sql
Can you achieve the same without using WHERE clause ?
SELECT TOP 0 * INTO NEW_TABLE FROM TRANSPOSE
|
| data-domain-sql
How to print 0 without using select 0 or aritmetic manuplation ?
SELECT $; SELECT ISDATE('BRAINRAIN'); SELECT ISNUMERIC('BRAINRAIN'); SELECT CHARINDEX('P', 'BRAINRAIN'); SELECT CHAR(48); |
| data-domain-sql
What is difference between union and union all ?
UNION : SELECT 3 UNION SELECT 3 UNION SELECT 2 : Output : 2 3 UNION ALL : SELECT 3 UNION ALL SELECT 3 UNION ALL SELECT 2 : Outpt : 3 3 2 Union and Union all both combines the result set In addition to this, union filters duplicate element and does sorting in ascending order. |
| data-domain-sql
How to find the number of tables existing in a particular database ?
SELECT COUNT(0) FROM INFORMATION_SCHEMA.TABLES
|
| data-domain-sql
What is difference between convert and format ?
Convert is a sql server native function. Format depends on .NET CLR
|
| data-domain-sql
How to find the number of records in a table EMPLOYEE(ID, NAME, SEX, DOB , JD, SALARY, MID) without using count ?
SELECT ROWS FROM SYSINDEXES WHERE ID = OBJECT_ID('EMPLOYEE') AND STATUS = 0 SELECT SUM(1) NAME FROM EMPLOYEE |
| data-domain-sql
How will you find the name of columns in the table transpose ?
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TRANSPOSE'
|
| data-domain-sql
What is INFORMATION_SCHEMA.TABLES, SYSINDEXES, INFORMATION_SCHEMA.COLUMNS used above ?
All are system views.
|
| data-domain-sql
In the table EMPLOYEE, how will you find the name of all the employees starting with 'R' without using LIKE clause ?
SELECT NAME FROM EMPLOYEE WHERE CHARINDEX('R',NAME) = 1 SELECT NAME FROM EMPLOYEE WHERE SUBSTRING('NAME', 1 , 1) = 'R' |
| data-domain-sql
Can you write script for the same without using where clause ?
SELECT CASE WHEN LEFT(NAME,1) = 'S' THEN NAME END FROM EMPLOYEE EXCEPT SELECT NULL
|
| data-domain-sql
How to achieve Pattern Matching in SQL?
1. Using the % wildcard to perform a simple search. 2. Omitting the patterns using the NOT keyword 3. Matching a pattern anywhere using the % wildcard twice 4. Using the _ wildcard to match pattern at a specific position 5. Matching patterns for specific length |
| data-domain-sql
Can you compare null with null ?
Yes, the output depends upon ANSI_NULLS. When it is set on , the query yields false else true.
|
| data-domain-sql
SELECT CASE WHEN 1 = 1 THEN 'INDIA' WHEN 2 = 2 THEN 'COUNTRY' ELSE 'STATE' END What is output for the above query ?
INDIA
|
| data-domain-sql
SELECT ASCII(123) as [123]. What do you except from the query ?
The query will return 49, the ascii value of 1.The ASCII() function returns the ASCII code value of the leftmost character of a character expression.
|
| data-domain-sql
1. SELECT 'INDIA' 2. SELECT ''INDIA''. Both query yield same result. What can be the reason ?
In this case, QUOTED_IDENTIFIER is set to OFF
|
| data-domain-sql
How can you print a string n number of times without using loops
SELECT REPLICATE('INDIA_', 25)
|
| data-domain-sql
How can you select a random record ?
SELECT TOP 1 * FROM TRANSPOSE ORDER BY NEWID()
|
| data-domain-sql
How will you print numbers from 1 to 125 without using loop ?
It can be achieved using recursive CTE DECLARE @SEED INT = 1; WITH PRINTINGCTE AS ( SELECT @SEED AS NEXTVALUE UNION ALL SELECT NEXTVALUE + 1 FROM PRINTINGCTE WHERE NEXTVALUE < 125) SELECT * FROM PRINTINGCTE OPTION(MAXRECURSION 125) |
| data-domain-sql
How will you find the sum of numbers separated by a special character ?
Use REPLACE FUNCTION to replace special character with ADD operator + and then EXEC
|
| data-domain-sql
What is difference between ROWS and RANGE clause ?
Rows clause treats every row as distinct, But, Range clause treats duplicate row as same
|
| data-domain-sql
What is difference between ROW_NUMBER(), RANK(), DENSE_RANK()
ROW_NUMBER() function returns the sequential ranking for each record partition wise. RANK() returns returns distict ranking for each distint record and same for duplicate record with voids DENSE_RANK() returns the consecutive ranking of records and assigns same rank for duplicate values |