1.Difference between Correlated subquery and Nested subquery
S.No Correlated subquery Nested subquery 1Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row. 2 Correlated subquery follows down to top approach i.e., main query is executed first(even though parenthesis are present) and then child query.
Nested subquery follows top-down approach i.e., child query is executed first and then parent .We can also say:In a subquery
Outer query condition is used in the the inner query.
4Example:select e1.empname, e1.basicsal, e1.deptno from emp e1
where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)Example:select empname, basicsal, deptno from emp
where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)
S.No Weak Entity Set Strong Entity Set 1 An entity set which does not possess sufficient attributes to form a primary key is known as a weak entity set. An entity set which does have a primary key is called a strong entity set. 2 Member of a weak entity set is a subordinate entity. Member of a strong entity set is a dominant entity. 3 Example:
Set of all Persons,Companies,Trees,Holidays
S.No Char Varchar 1Fixed length memory storageVariable length memory storage(Changeable) 2CHAR takes up 1 byte per characterVARCHAR takes up 1 byte per character, + 2 bytes to hold length information 3Use Char when the data entries in a column are expected to be the same size like phone numberUse Varchar when the data entries in a column are expected to vary considerably in size like address 4Ex:
Declare test Char(100);
Then "test" occupies 100 bytes first four bytes with values and rest with blank dataEx:
Declare test VarChar(100);
Then "test" occupies only 4+2=6 bytes. first four bytes for value and other two bytes for variable length information.
S.No Sql Server 2005 Sql Server 2008 1XML datatype is introduced.XML datatype is used. 2Cannot encrypt the entire database.Can encrypt the entire database introduced in 2008. 3Datetime is used for both date and time.Date and time are seperately used for date and time 4No table datatype is included.Table datatype introduced. 5SSIS is started using.SSIS avails in this version. 6CMS is not available.Central Management Server(CMS) is Introduced. 7PBM is not availablePolicy based management(PBM) server is Introduced.