81.Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.
82.What are the Advantages of SQL ?
1. SQL is not a proprietary language used by specific database vendors. Almost every major DBMS supports SQL, so learning this one language will enable programmers to interact with any database like ORACLE, SQL ,MYSQL etc.
2. SQL is easy to learn. The statements are all made up of descriptive English words, and there aren’t that many of them.
3. SQL is actually a very powerful language and by using its language elements you can perform very complex and sophisticated database operations.
83.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 nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.
84.When is the use of UPDATE_STATISTICS command?
This command is basically used when a large processing of data has occurred. If a large amount of deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
84.What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
They specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
85.What are the properties and different Types of Sub-Queries?
Properties of Sub-Query:
A sub-query must be enclosed in the parenthesis.
A sub-query must be put in the right hand of the comparison operator, and
A sub-query cannot contain an ORDER-BY clause.
A query can contain more than one sub-query.
Types of Sub-Query:
Single-row sub-query, where the sub-query returns only one row.
Multiple-row sub-query, where the sub-query returns multiple rows,. and
Multiple column sub-query, where the sub-query returns multiple columns.