SQL_interview_questions.txt

1) What is the difference between inner and outer join? Explain with example.
	- Inner join returns rows when there is at least one match in both tables
	- Outer join will return matching rows from both tables as well as any unmatched rows from one or both the tables 
	
2) What is the difference between JOIN and UNION?

	- SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables. 
	- UNION operation allows us to add 2 similar data sets to create resulting data set that contains
          all the data from the source data sets. Union does not require any condition for joining.
	
3) What is the difference between UNION and UNION ALL?	
	- UNION operation returns only the unique records from the resulting data set  
	- UNION ALL will return all the rows, even if one or more rows are duplicated to each other.

4) What is the difference between WHERE clause and HAVING clause?
	- WHERE clause can only be applied on a static non-aggregated column 
	- we will need to use HAVING for aggregated columns.
	
5) What is the difference among UNION, MINUS and INTERSECT?
	- UNION combines the results from 2 tables and eliminates duplicate records from the result set.
	- MINUS operator when used between 2 tables, gives us all the rows from the first table except 
          the rows which are present in the second table.
	- INTERSECT operator returns us only the matching or common rows between 2 result sets.
	
6) How can we transpose a table using SQL (changing rows to column or vice-versa) ?
	- The usual way to do it in SQL is to use CASE statement or DECODE statement
	- also we can use pivot to do so.
	
7) How to generate row number in SQL Without ROWNUM
	- SELECT name, sal, (SELECT COUNT(*)  FROM EMPLOYEE i WHERE o.name >= i.name) row_num
		FROM EMPLOYEE o 
		order by row_num
	
8) How to select first 5 records from a table?
	sql server -> SELECT TOP 5 * FROM EMP;
	Oracle -> SELECT * FROM EMP WHERE ROWNUM <= 5;
	Generic -> SELECT  name  FROM EMPLOYEE o
			   WHERE (SELECT count(*) FROM EMPLOYEE i WHERE i.name < o.name) < 5
			   
======================= SQL server questions ==========================================
9) What are DMVs? - 
	Dynamic Management Views (DMVs), are functions that give you information on the state of the server. DMVs, for the most part, are used to monitor the health of a server. They really just give you a snapshot of what’s going on inside the server. They let you monitor the health of a server instance, troubleshoot major problems and tune the server to increase performance			   

10) Define a temp table 
	In a nutshell, a temp table is a temporary storage structure. What does that mean? Basically, you can use a temp table to store data temporarily so you can manipulate and change it before it reaches its destination format.	
	
11) What’s the difference between a local  temp table and a global temp table? 
	Local tables are accessible to a current user connected to the server. These tables disappear once the user has disconnected from the server. Global temp tables, on the other hand, are available to all users regardless of the connection. These tables stay active until all the global connections are closed.
	
12) Describe the difference between truncate and delete 
	Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. 
	Truncate will actually remove all the rows from a table and there will be no data in the table after 
        we run the truncate command.
        Where clause can be use in Delete statement whereas we cannot use where clause in Truncate.
        Truncate delete all the rows in the table which can't be revoked whereas Delete will remove rows based on where       
        conditions.
        If there is no where condition in delete statement it will remove all the records in the table.
	
13) What is a view?
	A view is simply a virtual table that is made up of elements of multiple physical or “real” tables. Views are most commonly used to join multiple tables together, or control access to any tables existing in background server processes.	
	
14) What is the default port number for SQL Server? -
	Basically, when SQL Server is enabled the server instant listens to the TCP port 1433.	It can be changed from the Network Utility TCP/IP properties.

15) What are the difference between clustered and a non-clustered index?
	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.
	
	A non clustered 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 node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
	
16) What is PRIMARY KEY?
	A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.
	
17) What is FOREIGN KEY?
	A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
	
16) What's the difference between a primary key and a unique key?
	Both primary key and unique key enforces uniqueness of the column on which they are defined. 
	But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. 
	Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.	

18) What are the advantages of using Stored Procedures?
	Stored procedure can reduced network traffic and latency, boosting application performance.
	Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
	Stored procedures help promote code reuse.
	Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
	Stored procedures provide better security to your data.	
	
19) What is SQL?
        •SQL stands for Structured Query Language.
        •Basically SQL is like commands in simple English which provides you with ways to communicate with 
         relational databases like MS SQL Server, Oracle server and MySQL.

20) What is the order of execution of group by, order by, where and having?
         •The order of execution is where, group by, having and then order by.

21) Difference between SQL and PL/SQL?
        SQL is not a programming language but PL/SQL is the procedural language. PL/SQL is the enhanced form of SQL. 
        In PL/SQL, we can perform loop and conditional statements. PL/SQL was developed by Oracle

22) What is CTE (Common Table Expression)?
        CTE stands for Common Table Expression. CTE is like a temp table which will create in our current 
        working database. The major advantage of CTE is to do recursive programming. 

23) What is the difference between CTE and Temptable?
       CTE will create in current working database and temp table creates in temp DB. 
       You cannot able to do index on CTE table. You can do index in temp table.
       Temp table is preferable than CTE.  
       CTE will not be available in consecutive query but temp table will be available in entire connection.

24) Can we use user defined functions in select statements?
        yes, we can use user defined functions in select statements.

25) can we use DML statements in user defined functions?
        No, we can't able to use DML statements in functions. We can only able to use select statement in a function.

26) What is Composite key?
        Composite key is the combination of two or more column in a table which make each row in a table unique.

27) What are Triggers?
		Triggers are the special stored procedure which executes automatically when insert, update, delete operation happens on the table. Triggers will reduce the db performance.

28) Can we use a function in select statement?
	yes, we can use function in select statement. We can write some complex queries to a function and refer it in where condition of a query which will improve the performance of your query. which also reduces the duplication of te query
	
29)What is subquery?
	subquery are the query within a query.
	subquery executes first and then the outer query will executes. Based on the results of the subquery,  outer query will perform.
		ex: select empname from emp where locationid = (select locationid from location where location = 'Chennai')
		
30) what are the disadvantages of subquery?
	subquery reduces the performance of the query. It's always advisable to use joins instead of subquery.
	
31) how to retrieve data based on a pattern match?
	we can use 'like' to do pattern match
	ex: select empname from  employee where empname like 'bal%'
	
	Since percentage symbol is at-last, it will retrieve data starts with bal. If I pust '%' at first position, the query will retrieve data ends with bal.
	Similarly we can also use the percentile '%' symbol at middle.

32) difference between Truncate and Drop?
	Truncate will remove all the records in a table and the table structure remains unchanged.
	Drop will remove the table from the database. both the commands can't be roll-back.
	
33) how to add a column to an existing table?
	alter table emp add address varchar(100);

34) how to delete a column to an existing table?
	alter table emp drop column address 
	
35) what is default constraint?
	default constraint are set at column level. this constraint will set the default value to the record if we haven't provided the data for that particular column on which the default constraint sets.
	
	ex: alter table emp alter column salary set default '10000'
	
	the  above query will set the default emp salary to 10000. when you adding a new employee to table the default value to salary column in 10000.

36) what is check constraint?
	Check constraint used to check for some condition before the value get inserted into an column.
	the check constraint works like a "IF" condition in programming. It limits the data range.
	
	ex: alter table emp add constraint chk check age<40;
	the above query let you to insert data in age column only when the age is lesser than 40.
	
37) what is not null constraint?
	if the column is set to not null constraint, then the column must have value it won't accept null value.
	
38) what is unique constraint?
	unique constraint make sure that each row of a column must have a unique value that make the record unique.
	
39) how to declare a variable in sql server/
	declare @my_var varchar(10) = null
	
	in the above line, I've declared a variable called @my_var which is of type varchar which has 10 byte of memory and I set default to null.
	
40) what are the mode of authentications in SQL server?
	Windows authentications and SQL server authentications.

41) What is database lock?
	database locks are applied on table's data level we can  using a transaction to avoid such a situation of accessing same data by two different users.
		
42) What are te types of database lock?
	there are two types of database lock.
		1) Shared lock
			shared lock is also called as read lock. when two users concurrently access the same record only one user can able to edit the record 
			and other user can only able to read (which is in read only mode) the data.
		2) Exclusive lock
				when execusive lock is applied on a data. that can't be edit or view by any other transactions.
				
43) What is transaction and when to use it?
		* A transaction is a set of statements. transaction passess when all the statements are passed otherwise fails totally. 
		* if any statement in the trasactions get failed, then previously DB affected changes are get rollback to it's original position.
		* a trasaction can use when two different works are going to heppen one by one. consider an example, transferring mony from one account to another account.
		ex: 
			begin try	
				begin transaction
					--A set of statements
				commit	
			end try
			begin catch	
				rollback
			end catch
			
44) what is acid property?
	the most important interview question is ACID property in transaction.
	Atomic: the transaction should pass or fail in whole. it won't be in half done
	Consistency: 
	Isolation: one transaction should not interact / disturb another transation.
	Durability: once after the successfull transaction data in all the table should be related. once after fail, the tables data should revert ack to it's original states.
	
45) what is nested trasaction?
	a separate transaction present inside another transaction.
	ex: 
			begin try	
				-- outer transaction
				begin transaction 
					--A set of statements
					-- transaction inside another trasaction
					begin transaction
						-- set of statements
					commit transaction
				commit	
			end try
			begin catch	
				rollback
			end catch
			
46) what is COALESCE function?
		Coalesce function return the first not null value of the column referred in select query.
		
47) how to use case in query?
		we can use case statement in select query.
		ex: select case when column_name is null then 'A' else 'B' end as some_query from sampletable
		
48) can we use case in DML statements?
		No, we can't able to use CASE statement in DML queries 
		
49) what is natural join?
		natural join will not comes under common join types. It's works based on the common column present within the two tables with same datatype.
		
50) what is cross join?
		when joining two table if we havnt provided any where condition, then it will return the product of the two tables. Cross join is also called as cartesian join.
		ex: we we join two tables called table a and table b. table a has 3 column and table b has 120 column. Then the cross join will provide output like 3 * 120  = 360 rows.
		
51) what is difference between left outer join and right outer join?
		* left outer join and right outer join are the types of outer join.
		* left join will return all the record from the left table and only the matched record from the right table. likewise, right outer join return all record from right table and only the matched record from the left table.
		
52)	will the truncate and drop table command can revoke?
		No, this operations can't be revoked.
		
53) What is full outer join?
		all the records from both the right and left table gets returned even if there is no match record.

54) what are the different types of pattern matches?
		* we have seen pattern match with like 
		* we can use '_' (underscore) for single character search.
			ex: select name from sites where sitename like '_uvi%'
		* 	we can use charlist to search multiple datas.
			ex: select city from states where statename like '[cba]%'
			
55) What is Sequel?, What is SQL?, and what is the difference/
		* SQL and Sequel are same.
		* Sequel is the earlier name of SQL. But, still we can see many people's are using this name.
		* Sequel name changed to SQL by removing the vowels. Because, an aircraft company  registered sequel as trademark.

56) How a Sql query gets executes?
		when a sql query is given to server, the parser receives it then parse the query and then te parsed query will send to optimizer to create a execution plan.
		based on the execution plan, the SQL query gets executes and the result set will produce.
		
		NOTE: the main work of the parser is to check the SQL query syntatically with the SQL grammer rules and also allocate space in memory to execute the query.
		
57) What is stored procedures?
		* stored procedure is a batch of scripts which gets executes at a time.
		* stored procedure accepts input parameters.
		* SP increases the performance. Because once the stored procedure was successfully compiled an execution plan also get prepared. each time when we call the SP, it won't compile again until and unless the SP gets changed.
		* SP nesting level is limited to 32 
		* but we can use as many number of stored procedure as you want.
		* SP will prevent the SQL injection attack.
		
58) What are the types of Stored Procedures?
		* there are four types of stored procedure
			* system defined strored procedure
			* user defined stored procedure
			* extended stored procedure
			* CLR stored Procedure
			
59) what is system stored procedure and user defined stored procedure?
		* System stored procedures are predefined which has come from SQL server itself. Below are the sample system stored procedures.
			for ex: sp_helptext, sp_depends, sp_executesql, sp_rename 
		* User defined stored procedure are written by users for their own purpose. below is the basic syntax.
			create proc or procedure sp_sampleprocedure
			 -- input parameters if any ex: @my_var int 
			 as 
			 begin
				-- your query.
			 end
			 
60) what is extended stored procedure?
		 * extended stored procedures are starts with xp_ as prefix. It provide an interface with external programs. this extended stored procedures are stored under master database. ex: program from C program.
		 
		 NOTE: Microsoft likely to remove the extended stored procedure. instead they let us to use CLR stored procedure.
		
61) what is CLR stored Procedure?
		* The CLR stored procedures are CLR supported. the procedures can be written in any of the .net supported programming languages like C#, VB, F#. 	
		* The method should be public static. if the return type is void then default return to 0.
		
62) What is recursive stored procedure?
		* a stored procedure gets called again again untill the desired output obtain. Default recursive is deactivated 
		
63) can we return null values from SP?
		* we cant able to return null values from SP instead we can return 0.
		
64) can we use output variable in SP?
		* yes, we can use.
	
65) how to handle error in SP?
		we can use try catch block to handle exceptions. SQL server itself stored error in a table called sys.messages
		
66) can we use transaction in SP?
		yes we can use transaction in SP's. it's advisable to use transaction minimally. try to avoid complex transaction which will affect the performance. It's good practive to use transaction within the try catch block.
		
67) can a SP accept both input and output parameters?
		* Sp accept input and output parameters. a max of 1024 values.