1) What is SQL
Server?
SQL is a standard language for accessing and manipulating databases.Microsoft SQL Server is a Relational Database Management System (RSBMS) produced by Microsoft. It's primary query language Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) used by both Microsoft and Sybase and Microsoft that add several features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.
SQL is a standard language for accessing and manipulating databases.Microsoft SQL Server is a Relational Database Management System (RSBMS) produced by Microsoft. It's primary query language Transact-SQL, an implementation of the ANSI/ISO standard Structured Query Language (SQL) used by both Microsoft and Sybase and Microsoft that add several features to standard SQL, including transaction control, exception and error handling, row processing, and declared variables.
2) What is the difference between VARCHAR and NVARCHAR.
a) VarChar is a variable length string
of ASCII characters, which take one Byte of space. Where as, NVarChar is a
variable length string of UNICODE characters, which take two Bytes of space.
b) The maximum size of VarChar columns is 8,000 characters. Where as, the maximum size of NVarChar columns is 4,000 characters.
c) NVarChar requires 1 byte to represent a character. Where as, VarChar requires 2 bytes to represent a character.
c) NVarChar supports wider range of characters than VarChar.
Differences :
1 Character Data Type
Varchar - Non-Unicode Data
NVarchar - Unicode Data
2 Character Size
Varchar - 1 byte
NVarchar - 2 bytes
3 Maximum Length
Varchar - 8,000 bytes
NVarchar - 4,000 bytes
4 Storage Size
Varchar - Actual Length (in bytes)
NVarchar - 2 times Actual Length (in bytes)
* The abbreviation for Varchar is Variable Length character String.
* The abbreviation of NVarchar is unicode Variable Length character String.
b) The maximum size of VarChar columns is 8,000 characters. Where as, the maximum size of NVarChar columns is 4,000 characters.
c) NVarChar requires 1 byte to represent a character. Where as, VarChar requires 2 bytes to represent a character.
c) NVarChar supports wider range of characters than VarChar.
Differences :
1 Character Data Type
Varchar - Non-Unicode Data
NVarchar - Unicode Data
2 Character Size
Varchar - 1 byte
NVarchar - 2 bytes
3 Maximum Length
Varchar - 8,000 bytes
NVarchar - 4,000 bytes
4 Storage Size
Varchar - Actual Length (in bytes)
NVarchar - 2 times Actual Length (in bytes)
* The abbreviation for Varchar is Variable Length character String.
* The abbreviation of NVarchar is unicode Variable Length character String.
3) How to query the database to get all the Table names?
SELECT * FROM information_schema.tables where Table_type='BASE TABLE';
Or
SELECT * FROM sysobjects WHERE type='u';
4) How to query the database to get all the Stored Procedure names?
SELECT * FROM sysobjects WHERE type='p';
5) How to query all the column names of a table?
SELECT * FROM syscolumns WHERE object_name(syscolumns.id)='Tblzone';
6) How to query all the column names, data types and length of a table?
SELECT syscolumns.name "Column Name", systypes.name "Type", syscolumns.length "Length" FROM syscolumns, systypes WHERE object_name(syscolumns.id)='TblStudent' AND systypes.usertype=syscolumns.usertype;
Or
SELECT * FROM INFORMATION_SCHEMA.columns where table_name='TblSupportCall'
7) How to select all the current databases in Sql Server?
SELECT name FROM master.sysdatabases
Or
SELECT CATALOG_NAME AS DataBaseName FROM INFORMATION_SCHEMA.SCHEMATA
Or
EXEC sp_databases
Or
EXEC sp_MSForEachDB 'SELECT ''?'' AS DatabaseName'
Or
EXEC sp_MSForEachDB 'Print ''?'''
8) Write and SQL query to retrieve the 2nd highest salary from the emp table.
SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP 2 salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
Or
SELECT MIN(mark)
FROM student
WHERE mark IN(
SELECT TOP 3 mark
FROM student
ORDER BY mark DESC)
9) Write a query to get the name of the employee who is getting the highest salary from an employee table.
SELECT EmpName FROM Emp
WHERE Salary =(SELECT MAX(Salary) FROM Emp)
10) When do you get this error message "String or Binary data would be truncated"?
This error message appears when you try to insert a string with more characters than the column can maximal accommodate.
11) Which TCP/IP port does SQL Server run on?
SQL Server runs on port 1433 but we can also change it for better security.
12) What is SQL Inner Join?
Inner Join is used to retrieve only matching data from two or more table.
13) What is SQL Left Outer Join?
It retrieves all the records from left most table (irrespective of the condition specified) and retrieves only matching records from the right most table and assigns NULL for unmatched fields.
14) What is SQL Right Outer Join?
It retrieves all the records from right most table (irrespective of the condition specified) and retrieves only matching records from the left most table and assigns NULL for unmatched fields. (Opposite to LEFT OUTER JOIN).
15) What is SQL Full Outer Join?
It retrieves records from both the table (irrespective of the condition) and for unmatched fields it assigns NULL.
