GIT COMMANDS FOR SQL SERVER CODE TO PUSH ON BIT BUCKET

SQL INTERVIEW QUESTIONS FOR 15 MONTHES EXPERIANCE

 

This post have all the query and synatx which a biggner and experianced SQL users need to know and use in their daily working Life.

1 :- SQL INSERT

Type of SQL Insert

Syntax 1 :-Template for all columns update --

insert into Table_name value('ABC','ART','',.....,'Value n')
--   Note data type of each column should be match

Syntax 2:- Template for slelect columns update --

insert into Table_name(col_1,col_2,,,,,,,, col_n) value('ABC','ART','',.....,'Value n')

Syntax 3:- If all rows have to be fill up

insert Into Parameter_Blood_Group values('B0100A000004','GEN00A000001','AB-','AB-','0','2','Approved','','2019-05-04 17:27:58.913')

Syntax 4:- If select rows have to be fill up

insert Into Parameter_Blood_Group(URN_No,Co_code) values('B0100A000004','GEN00A000001')

Syntax 5:-Without index column is exists in Table

Example 1- Insert into 'table_name' (Col_1,Col_2,......,Col_n) 
                                      Values(Value_1,Value_2,.....,Value_n);

Initial Table - Table Name- User_Detail

   

    
Example 2- Insert into User_Detail (User_name,User_Mobile,User_email_id,Pin_code)
                                     Values('Programming    Logic                                                       World',1234512345,'programminglogicworld@gmail.com',123456);

See Also - SQL Query without Index

SQL LIKE Operator

Sql Query to select similar kind of data which have perticular pattern comman in their specipic data column

Basic Format

SELECT col1, col2, ...,columnN

FROM table_name

WHERE columnN LIKE pattern;

Demo-

SELECT * FROM Student

WHERE StudentName LIKE 'a%';

Note:-  Like Operator Consume Maximum Resources

SQL Like Opertaor

Like Operator Usage

Search Type 1:-

All Students with a column name 'StudentName' that start with "s" in the Second position:
SELECT * FROM Students
WHERE Student_Name LIKE '_s%';

Search Type 2:-

All Students with a column name 'StudentName' that have "s" in the Third position:
SELECT * FROM Students
WHERE Student_Name LIKE '__s%';

Search Type 3:-

All Students with a column name 'StudentName' that have "s" in the Fourth position:
SELECT * FROM Students
WHERE Student_Name LIKE '___s%';

Search Type 4:-

All Students with a column name 'StudentName' that start with "s" 

SELECT * FROM Students
WHERE Student_Name LIKE 's%';

Search Type 5:-

All Students with a column name 'StudentName' that start with "sa" 
SELECT * FROM Students
WHERE Student_Name LIKE 'sa%';

Search Type 6:-

All Students with a column name 'StudentName' that start with "saw" 
SELECT * FROM Students
WHERE Student_Name LIKE 'saw%';

Search Type 7:-

All Students with a column name 'StudentName' that end with "s" 
SELECT * FROM Students
WHERE Student_Name LIKE '%s';

Search Type 8:-

All Students with a column name 'StudentName' that end with "sa" 
SELECT * FROM Students
WHERE Student_Name LIKE '%sa';

Search Type 9:-

All Students with a column name 'StudentName' that end with "saw" 
SELECT * FROM Students
WHERE Student_Name LIKE '%saw';

SQL Distinct Opeartor

SELECT DISTINCT column1, column2, ...
FROM table_name;

Query:- 

Select Distinct it_name,it_code from Item_Master


Note- we use Distinct keyword to find unique data of columns.



SELECT DISTINCT column1, column2, ...
FROM table_name;

DEmo Table Data Rows- 10000

Query-

Select Distinct it_name from Item_Master
Result- 10

Note- we use Distinct keyword to find unique data of column.



Select Distinct it_name,it_code from Item_Master
Result- 159

Note- we use Distinct keyword to find unique data of two columns. 



Select Distinct it_name,it_code,uom from Item_Master
Result-1547

Note- we use Distinct keyword to find unique data of three Columns. 

Select Distinct it_name,* from Item_Master
Result- 5000

Note- we use Distinct keyword to find unique data of column.



Select Distinct it_name,it_code,* from Item_Master
Result- 5000

Note- we use Distinct keyword to find unique data of two columns. 


Select Distinct it_name,it_code,uom,* from Item_Master
Result- 5000



Note- we use Distinct keyword to find unique data of three Columns. 


   As all programming language have operator for conditions in the same way SQL have following operator
 1- AND
 2- OR
 3- NOT

1- AND
    Operator is used when we want both the conditions must be true.
Syntax- Select * from table_name
where condition1 and condition2 and condition3


2- OR
    Operator is used when we want only single the conditions must be true.
Syntax- Select * from table_name
where condition1 or condition2 or condition3

3- NOT
    Operator is used when we want  conditions must not be true.
Syntax- Select * from table_name

where  Not condition1 

JOIN

SQL Joins is used to Compose the data of two table into a single view-

SQL Joins are Five Types-

      1- Inner Joins
      2- Left Outer Joins
      3- Right outer Joins
      4- Full Joins
      5- Self Joins

Inner Joins syntax

   select * from table A
   INNER join table B on A.ID=B.ID

Result- Only the common data from both table which have ID in both table

Left Joins syntax

   select * from table A
   LEFT outer join table B on A.ID=B.ID

Result- All data from table A and matched data from table B where id of a Exists in B

Rights Joins Syntax

   select * from table A
   RIGHT outer join table B on A.ID=B.ID

Result- All data from table B and matched data from table A where id of a Exists in A

Full joins Joins Syntax

   select * from table A
   FULL OUTER join table B on A.ID=B.ID

Result- All data from table A and B and All A data whose ID not in B and A data whose ID not in B and Common of A& B no duplicate ID

Self  JoinsSyntax

   select * from Sawanchauhan A
   left outer join Sawanchauhan B
where A.PermanentCity=B.CurrentCity

Result- All data from table (SawanChauhan) where permanenet city and current city are same only.

Where Clause Uses

Sql where keyword is used to filter the records 

Syntax- Select Col1,Col2,Col3 from table_name where condition;

select item_name,Item_code,Item_uom from item_master where created_By_Id=12

More Questions -

1. Why Truncate is a example of DDL statement? 2. Difference between cluster index and non-cluster index ? 3. Difference between CTE and Temp. table? 4. Difference between Rank and dense rank. 5. explain type of cursor ? 6. Query to find duplicate rows in table? 7. What are the differences between Stored Procedure and the dynamic SQL? 8. What is Collation? 9. What are Magic Tables in SQL Server? 10. What is the difference between varchar and nvarchar types? 11. What are the differences between left join and outer left join ? 12. explain types of user defined functions?

SQL Error Type with Description

Error Type =6001,/n Error Description SHUTDOWN is waiting for %d process(es) to complete.. Error Severity  Level=10

Error Type =6002, Error Description SHUTDOWN is in progress. Log off.. Error Severity  Level=10

Error Type =6004, Error Description User does not have permission to perform this action.. Error Severity  Level=10

Error Type =6005, Error Description SHUTDOWN is in progress.. Error Severity  Level=10

Error Type =6006, Error Description Server shut down by request.. Error Severity  Level=10

Error Type =6007, Error Description The SHUTDOWN statement cannot be executed within a transaction or by a stored procedure.. Error Severity  Level=10

Error Type =6101, Error Description Process ID %d is not a valid process ID. Choose a number between 1 and %d.. Error Severity  Level=16

Error Type =6102, Error Description User does not have permission to use the KILL statement.. Error Severity  Level=14

Error Type =6103, Error Description Could not do cleanup for the killed process. Received message %d.. Error Severity  Level=17

Error Type =6104, Error Description Cannot use KILL to kill your own process.. Error Severity  Level=16

Error Type =6106, Error Description Process ID %d is not an active process ID.. Error Severity  Level=16

Error Type =6107, Error Description Only user processes can be killed.. Error Severity  Level=14

Error Type =6108, Error Description KILL SPID WITH COMMIT/ABORT is not supported by Microsoft SQL Server 2000. Use Microsoft Distributed Transaction Coordinator to resolve distributed transactions.. Error Severity  Level=16

Error Type =6109, Error Description SPID %d: transaction rollback in progress. Estimated rollback completion: %d%%. Estimated time remaining: %d seconds.. Error Severity  Level=10

Error Type =6110, Error Description The distributed transaction with UOW %s does not exist.. Error Severity  Level=16

Error Type =6111, Error Description Another user has decided a different outcome for the distributed transaction associated with UOW %s.. Error Severity  Level=16

Error Type =6112, Error Description Distributed transaction with UOW %s is in prepared state. Only Microsoft Distributed Transaction Coordinator can resolve this transaction. KILL command failed.. Error Severity  Level=16

Error Type =6113, Error Description The distributed transaction associated with UOW %s is in PREPARE state. Use KILL UOW WITH COMMIT/ABORT syntax to kill the transaction instead.. Error Severity  Level=16

Error Type =6114, Error Description Distributed transaction with UOW %s is being used by another user. KILL command failed.. Error Severity  Level=16

Error Type =6115, Error Description KILL command cannot be used inside user transactions.. Error Severity  Level=16

Error Type =6116, Error Description KILL command failed.. Error Severity  Level=16

Error Type =6117, Error Description There is a connection associated with the distributed transaction with UOW %s. First, kill the connection using KILL SPID syntax.. Error Severity  Level=16

Error Type =6118, Error Description The distributed transaction associated with UOW %s is not in PREPARED state. Use KILL UOW to kill the transaction instead.. Error Severity  Level=16

Error Type =6119, Error Description Distributed transaction with UOW %s is rolling back: estimated rollback completion: %d%%, estimated time left %d seconds.. Error Severity  Level=10

Error Type =6120, Error Description Status report cannot be obtained. Rollback operation for Process ID %d is not in progress.. Error Severity  Level=16

Error Type =6401, Error Description Cannot roll back %.*ls. No transaction or savepoint of that name was found.. Error Severity  Level=16

Error Type =6600, Error Description XML error: %.*ls. Error Severity  Level=16

Error Type =6601, Error Description XML parser returned the error code %d from line number %d, source '%.*ls'.. Error Severity  Level=10

Error Type =6602, Error Description The error description is '%.*ls'.. Error Severity  Level=16

Error Type =6603, Error Description XML parsing error: %.*ls. Error Severity  Level=16

Error Type =6604, Error Description XML stored procedures are not supported in fibers mode.. Error Severity  Level=25

Error Type =6605, Error Description %.*ls: Failed to obtain an IPersistStream interface on the XML text.. Error Severity  Level=16

Error Type =6606, Error Description %.*ls: Failed to save the XML text stream. The server resources may be too low.. Error Severity  Level=17

Error Type =6607, Error Description %.*ls: The value supplied for parameter number %d is invalid.. Error Severity  Level=16

Error Type =6608, Error Description Failed to instantiate class '%ls'. Make sure Msxml2.dll exists in the SQL Server installation.. Error Severity  Level=16

Error Type =6609, Error Description Column '%ls' contains an invalid data type. Valid data types are char, varchar, nchar, nvarchar, text, and ntext.. Error Severity  Level=16

Error Type =6610, Error Description Failed to load Msxml2.dll.. Error Severity  Level=17

Error Type =6612, Error Description Invalid data type for the column indicated by the parameter '%ls'. Valid data types are int, bigint, smallint, and tinyint.. Error Severity  Level=16

Error Type =6613, Error Description Specified value '%ls' already exists.. Error Severity  Level=16

Error Type =6614, Error Description Value specified for column '%ls' is the same for column '%ls'. An element cannot be its own parent.. Error Severity  Level=16

Error Type =6615, Error Description Invalid data type is specified for column '%ls'. Valid data types are int, bigint, smallint, and tinyint.. Error Severity  Level=16

Error Type =6616, Error Description Parameter '%ls' is required when the parent of the element to be added is missing and must be inserted.. Error Severity  Level=16

Error Type =6617, Error Description The specified edge table has an invalid format. Column '%ls' is missing or has an invalid data type.. Error Severity  Level=16

Error Type =6618, Error Description Column '%ls' in the specified edge table has an invalid or null value.. Error Severity  Level=16

Error Type =6619, Error Description XML node of type %d named '%ls' cannot be created .. Error Severity  Level=16

Error Type =6620, Error Description XML attribute or element cannot be created for column '%ls'.. Error Severity  Level=16

Error Type =6621, Error Description XML encoding or decoding error occurred with object name '%.*ls'.. Error Severity  Level=16

Error Type =6622, Error Description Invalid data type for column '%ls'. Data type cannot be text, ntext, image, or binary.. Error Severity  Level=16

Error Type =6623, Error Description Column '%ls' contains an invalid data type. Valid data types are char, varchar, nchar, and nvarchar.. Error Severity  Level=16

Error Type =6624, Error Description XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents.. Error Severity  Level=16

Error Type =, Error Description The query and the views or functions in it exceed the limit of %d tables.. Error Severity  Level=19

Error Type =Error, Error Description Description. Error Severity  Level=Severity

Error Type =6800, Error Description FOR XML AUTO requires at least one table for generating XML tags. Use FOR XML RAW or add a FROM clause with a table name.. Error Severity  Level=16

Error Type =6801, Error Description FOR XML EXPLICIT requires at least three columns, including the tag column, the parent column, and at least one data column.. Error Severity  Level=16

Error Type =6802, Error Description FOR XML EXPLICIT query contains the invalid column name '%.*ls'. Use the TAGNAME!TAGID!ATTRIBUTENAME[!..] format where TAGID is a positive integer.. Error Severity  Level=16

Error Type =6803, Error Description FOR XML EXPLICIT requires the first column to hold positive integers that represent XML tag IDs.. Error Severity  Level=16

Error Type =6804, Error Description FOR XML EXPLICIT requires the second column to hold NULL or nonnegative integers that represent XML parent tag IDs.. Error Severity  Level=16

Error Type =6805, Error Description FOR XML EXPLICIT stack overflow occurred. Circular parent tag relationships are not allowed.. Error Severity  Level=16

Error Type =6806, Error Description Undeclared tag ID %d is used in a FOR XML EXPLICIT query.. Error Severity  Level=16

Error Type =6807, Error Description Undeclared parent tag ID %d is used in a FOR XML EXPLICIT query.. Error Severity  Level=16

Error Type =6808, Error Description XML tag ID %d could not be added. The server memory resources may be low.. Error Severity  Level=16

Error Type =6809, Error Description Unnamed column or table names cannot be used as XML identifiers. Name unnamed columns using AS in the SELECT statement.. Error Severity  Level=16

Error Type =6810, Error Description Column name '%.*ls' is repeated. The same attribute cannot be generated more than once on the same XML tag.. Error Severity  Level=16

Error Type =6811, Error Description FOR XML is incompatible with COMPUTE expressions. Remove the COMPUTE expression.. Error Severity  Level=16

Error Type =6812, Error Description XML tag ID %d that was originally declared as '%.*ls' is being redeclared as '%.*ls'.. Error Severity  Level=16

Error Type =6813, Error Description FOR XML EXPLICIT cannot combine multiple occurrences of ID, IDREF, IDREFS, NMTOKEN, and/or NMTOKENS in column name '%.*ls'.. Error Severity  Level=16

Error Type =6814, Error Description In the FOR XML EXPLICIT clause, ID, IDREF, IDREFS, NMTOKEN, and NMTOKENS require attribute names in '%.*ls'.. Error Severity  Level=16

Error Type =6815, Error Description In the FOR XML EXPLICIT clause, ID, IDREF, IDREFS, NMTOKEN, and NMTOKENS attributes cannot be hidden in '%.*ls'.. Error Severity  Level=16

Error Type =6816, Error Description In the FOR XML EXPLICIT clause, ID, IDREF, IDREFS, NMTOKEN, and NMTOKENS attributes cannot be generated as CDATA, XML, or XMLTEXT in '%.*ls'.. Error Severity  Level=16

Error Type =6817, Error Description FOR XML EXPLICIT cannot combine multiple occurrences of ELEMENT, XML, XMLTEXT, and CDATA in column name '%.*ls'.. Error Severity  Level=16

Error Type =6818, Error Description In the FOR XML EXPLICIT clause, CDATA attributes must be unnamed in '%.*ls'.. Error Severity  Level=16

Error Type =6819, Error Description The FOR XML clause is not allowed in a %ls statement.. Error Severity  Level=16

Error Type =6820, Error Description FOR XML EXPLICIT requires column %d to be named '%ls' instead of '%.*ls'.. Error Severity  Level=16

Error Type =6821, Error Description GROUP BY and aggregate functions are currently not supported with FOR XML AUTO.. Error Severity  Level=16

Error Type =6824, Error Description In the FOR XML EXPLICIT clause, mode '%.*ls' in a column name is invalid.. Error Severity  Level=16

Error Type =6825, Error Description ELEMENTS mode requires FOR XML AUTO.. Error Severity  Level=16

Error Type =6826, Error Description Every IDREFS or NMTOKENS column in a FOR XML EXPLICIT query must appear in a separate SELECT clause, and the instances must be ordered directly after the element to which they belong.. Error Severity  Level=16

Error Type =6827, Error Description FOR XML EXPLICIT queries allow only one XMLTEXT column per tag. Column '%.*ls' declares another XMLTEXT column that is not permitted.. Error Severity  Level=16

Error Type =6828, Error Description XMLTEXT column '%.*ls' must be of a string data type.. Error Severity  Level=16

Error Type =6829, Error Description FOR XML EXPLICIT and RAW modes currently do not support addressing binary data as URLs in column '%.*ls'. Remove the column, or use the BINARY BASE64 mode, or create the URL directly using the 'dbobject/TABLE[@PK1="V1"]/@COLUMN' syntax.. Error Severity  Level=16

Error Type =6830, Error Description FOR XML AUTO could not find the table owning the following column '%.*ls' to create a URL address for it. Remove the column, or use the BINARY BASE64 mode, or create the URL directly using the 'dbobject/TABLE[@PK1="V1"]/@COLUMN' syntax.. Error Severity  Level=16

Error Type =6831, Error Description FOR XML AUTO requires primary keys to create references for '%.*ls'. Select primary keys, or use BINARY BASE64 to obtain binary data in encoded form if no primary keys exist.. Error Severity  Level=16

Error Type =6832, Error Description FOR XML AUTO cannot generate a URL address for binary data if a primary key is also binary.. Error Severity  Level=16

Error Type =6833, Error Description Parent tag ID %d is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.. Error Severity  Level=16

Error Type =6834, Error Description XMLTEXT field '%.*ls' contains an invalid XML document. Check the root tag and its attributes.. Error Severity  Level=16

Error Type =6835, Error Description FOR XML EXPLICIT field '%.*ls' can specify the directive HIDE only once.. Error Severity  Level=16

Error Type =6836, Error Description FOR XML EXPLICIT requires attribute-centric IDREFS or NMTOKENS field '%.*ls' to precede element-centric IDREFS/NMTOKEN fields.. Error Severity  Level=16

Error Type =6837, Error Description The XMLTEXT document attribute that starts with '%.*ls' is too long. Maximum length is %d.. Error Severity  Level=16

Error Type =6838, Error Description Attribute-centric IDREFS or NMTOKENS field not supported on tags having element-centric field '%.*ls' of type TEXT/NTEXT or IMAGE. Either specify ELEMENT on IDREFS/NMTOKENS field or remove the ELEMENT directive.. Error Severity  Level=16

Error Type =6839, Error Description FOR XML EXPLICIT does not support XMLTEXT field on tag '%.*ls' that has IDREFS or NMTOKENS fields.. Error Severity  Level=16

Error Type =6840, Error Description XMLDATA does not support namespace elements or attributes such as '%.*ls'. Run the SELECT FOR XML statement without XMLDATA or remove the namespace prefix declaration.. Error Severity  Level=16

Error Type =7000, Error Description OPENXML document handle parameter must be of data type int.. Error Severity  Level=16

Error Type =4506, Error Description Column names in each view or function must be unique. Column name '<Column Name>' in view or function '<View or Function Name>' is specified more than once.. Error Severity  Level=10