In our last post “Interview questions with anwers on SQL VOL 1” we focused on basic of SQL server. In this post we will continue it. Here are few more common question on SQL.
Q. 1 Define Normalisation?
Ans: Normalisation is an essential part of database design. A good knowledge of the semantic of data helps the designer in designing an efficient design using all concept of normalization.
Q.2 What is the different between Security and Integrity? Explain it.
Ans: Security is a protection against malicious attempts to steal or modify data. On the other hand integrity constraints guard against accidental damage to database. It ensure that authorized changes to database may not result in a loss of any data consistency.
Q. 3 What are the purpose of Normalisation?
Ans: Main purpose of Normalisation are as follows :
Minimize redundancy in data.
Remove insert, delete and update anomaly during the database activities.
Reducing the need to reorganize data it is enhanced or modified.
Q.4 How will you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
Ans: One-to-One relationship is implemented as a single table. Sometimes is is also implemented as two tables with foreign and primary key relationships. One-to-Many relationships can be implemented by splitting data into two different tables with primary and foreign key relationships. Many-to-Many relationships can be implemented using a junction table with keys from both tables forming composite primary key of junction table.
Q. 5 How can you create a column alias?
Ans: The ‘AS’ keyword is optional when specifying a column alias.
Q. 6 What is SubQuery?
Ans: Subquery is the nesting of Queries within other query.
Q. 7 How can you hide definition of a stored procedure from a user ?
Ans: When we create stored procedure we use WITH ENCRYPTION. It will convert original statement of CREATE PROCEDURE into an encrypted format.
Q. 8 How can you disable a triger? Explain how.
Ans: You can disable a single trigger in database by using “DISABLE TRIGGER triggerName ON <<TableName>>” you can disable all the trigger with the use of “DISABLE Trigger ALL ON ALL SERVER”
Q. 9 What is functional dependency.
Ans: Functional dependency tells that how one object depends upon the other object in the database.
Q. 10 Define Inner Join.
Ans: An inner Join is the default type of join in Query and View Designer.An inner Join is join that displays only rows that have common in both joined tables.
Q. 11 What are the different types of subquery?
- Single row subquery
- Multiple row subquery
- Correlated row subquery
Q. 12 What are the different types of replication?
Ans: The SQL Server 2000-supported replication types are as follows :
Q. 13 Explain the advantages of using Views ?
Ans: Advantages of using views are as follows:
- View provide simplify commands for the users.
- View hide data complexity from user.
- View stores complex queries.
- View presents data in a different from that of the base table.
- Views provides an additional level for table security.
- View restrict access to a predetermined set of rows and columns of table.
- View display only selective columns from a table.
- Views provide groups of users access to data according to selected criteria.
- Views provide data independence for application programs and ad hoc users.
- A view can be used for retrieving data from multiple tables.