skip to main |
skip to sidebar
Q. 1 Define Primary Key?
Ans: The primary key is the columns used to uniquely identify each row of a table.A table can have only one primary key. No primary key value can appear in more than one row in the table.
Q.2 Define Unique Key?
Ans: Unique key is a one or more column that must be unique for each row of the table.It is similar to primary key. Primary key column will not accept a null. Whereas the unique key column will accept a null values.
Q. 3 Define Foreign Key?
Ans: A foreign Key is a combination of columns with value is based on the primary key values from another table. A foreign key constraint also known as Referential Integrity Constraint.
Q.4 How do you add a column to a existing table? Give an example.
Ans: ALTER TABLE Department ADD (AGE, NUMBER);
Q.5 Define View?
Ans: A View is a database object that is a logical representation of a table.
- It is derived from a table but has no longer of its own and often may be used in the same manner as a table.
- A view is a virtual table that has columns similar to a table.
- A view does not represent any physical data.
Q.6 Can one drop a column from a table?If yes give an example
Ans: YES, to delete a column in a table, use ALTER TABLE table_name DROP COLUMN column_name
Q.7 Compare and contrast TRUNCATE and DELETE for a table?
Ans: Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
Q.8 What is cursors?
Ans: Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
Q.9 What’s the difference between a primary key and a unique key?
Ans: Both primary key and unique enforce 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 does not allow NULLs, but unique key allows one NULL only.
Q.10 Define Joins?
Ans: A Join combines columns and data from two or more tables (and in rare cases, of one table with itself).
Q.11 Define SQL?
Ans: Structured query language is the standard command set used to communicate with the relational database management system.
Q.13 Define Dbms?
Ans: A Database Management system consists of a collection of interrelated data and set of programs to access that data.
Q.14 What is the purpose of Database systems?
Ans: A Database Management system provides a secure and survivable medium for the storage and retrieval of data.In the real world, the data is shared among several users and is persistent.
Q.15 State the different between Security and Integrity?
Ans: Security is a protection from malicious attempts to steal or modify data.Integrity constraints guard against accidental damage to the database, by ensuribg that authorized changes to the database do not result in a loss of data consistency.
Q. 16 Define Normalisation?
Ans: Normalisation is an essential part of database design. A good understanding of the semantic of data helps the designer to built efficient design using the concept of normalization.
Q.17 What are the purpose of Normalisation?
Ans:
- Minimize redundancy in data.
- Remove insert, delete and update anamoly during the database activities.
- Reduce the need to reorganize data it is modified or enhanced.
Q.18 How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?
Ans: One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table. It will be a good idea to read up a database designing fundamentals text book.
Q.19 How you will create a column alias?
Ans: The AS keyword is optional when specifying a column alias.
Q.20 Define SubQuery?
Ans: Nesting of Queries one within the other is called as a Subquery.
Q.21 Can we hide the definition of a stored procedure from a user ?
Ans: YES, while creating stored procedure we can use WITH ENCRYPTION which will convert the original text of the CREATE PROCEDURE statement to an encrypted format.
Q.22 What is a "functional dependency"? How does it relate to database table design?
Ans: Functional dependency relates to how one object depends upon the other in the database. For example, procedure/function sp2 may be called by procedure sp1. Then we say that sp1 has functional dependency on sp2.
Q.23 Can we disable a triger?, if yes HOW ?
Ans: YES, we can disable a single trigger on the database by using “DISABLE TRIGGER triggerName ON <<TableName>>” we also have an option to disable all the trigger by using, “DISABLE Trigger ALL ON ALL SERVER”
Q.24 Define Cross Join.
Ans: A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.
Q.25 Define Inner Join.
Ans: A join that displays only the rows that have a match in both joined tables is known as inner Join. This is the default type of join in the Query and View Designer.
Q.26 What are the different types of subquery?
Ans:
- Single row subquery
- Multiple row subquery
- Correlated row subquery
Q.27 What are the different types of replication?
Ans: The SQL Server 2000-supported replication types are as follows :
- Transactional
- Snapshot
- Merge
Q.28 Define Outer Join.
Ans: A join that includes rows even if they do not have related rows in the joined table is an Outer Join. You can create three different outer join to specify the unmatched rows to be included:
- Left Outer Join: In Left Outer Join all rows in the first-named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.
- Right Outer Join: In Right Outer Join all rows in the second-named table i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.
- Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.
Q.29 What is Storage Manager?
Ans: It is a program module that provides the interface between the low-level data stored in database, application programs and queries submitted to the system.
Q.30 What are the advantages of using Views ?
Ans: Advantages of view are as follows:
- Views restrict access to the data because the view can display selective columns from the table.
- Views provide data independence for ad hoc users and application programs. One view can be used to retrieve data from several tables.
- Views provides an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
- Views provide groups of users access to data according to their particular criteria.
- Hide data complexity.
- Present the data in a different perpecetive from that of the base table.
- Store complex queries.
- Simplify commands for the user.
Posted in: 
