Database management systems use specialized software to store the database, make necessary changes, securing the database, etc. These DBMS are categorized according to the work they do. Much of the DBMS and its applications are primarily not designed for data they are designed for concurrency, performance, integrity, and failure troubleshooting.
2. Explain about the hierarchical model of database?
If you can imagine a tree with branches then you know to work with this database. It has a downward link to describe the nesting and they are arranged in a particular order down the same level of the list. With this database model you can form a logical relationship among different types of data
3) Explain about object oriented databases?
Object oriented databases are used to store much more complex data.
They were designed to store information related to multimedia, spatial and engineering databases. Some of the databases even stored data about software’s repositories. This was adopted in late ninety’s and new features were added.
4. Explain about Network model?
Network is complex, time consuming and costly. This model of storing database has many parents and finding a logical relationship between them is extremely difficult. This model provides greater flexibility and easy access to data. Navigating through links is a easy way to search for data.
5. Explain about the process of user request and data generating?
Users request information and this request is transformed into a special language usually that would be the dialect in sql. This dialect would be given to the appropriate software for culminating the database for appropriate presentation to the end user. Usually sql queries are increasingly being embedded into software.
6. Explain the role of indexing in databases?
Many of the databases take advantage of indexing to increase the speed. This technology has increased immensely since it’s inception stages. During indexing if a row matches the query it is automatically generated and given to the user. In RDBMS indexes can be created and dropped without changing the existing application.
7. Explain about post relational databases?
Post relational databases are similar to relational database management systems but they have pre dated relational model of data representation. This model doesn`t require representation of data with relationship. A perfect example can be a tree with nodes represented in a directed graph.
8. Explain about normal forms?
Databases are categorized into different forms based upon the anomalies to which they are vulnerable. Lowest rung of database is known as normal form of first order. A first order normal form is subjected to many types of vulnerabilities. A higher database cannot be subjected to any vulnerability. You can get more info about Databases at Database Guides.
9) Explain about primary key?
A primary holds a unique key for each row designated in the table. Keys are often used to combine more than two tables, columns, or rows. Keys should be defined in advance before and it will be too late to define them later.
10. What are the three basic rules which are to be followed for relational model of database?
There are three basic rules to follow for relational database they are
1) It least bothers about order of the columns
2) No clones or identical rows allowed.
3) Each and every row will have a value for each column.
11. Explain about XML databases?
XML databases came into existence in 2000. Many of the new startups were also started. This database lets you organize data irrespective of whether it is organized or not. Arranging and complexity for storing data is significantly got reduced with the language.
12. Explain about what a database is?
Database is an important constituent for collection and storage of data. A computer database uses software for managing its huge pool of database. It also uses software to make changes automatically to the database without any outside interference.
13) Explain about relational database management systems?
Relational database systems take the model and form of relational model. Data is represented in the form of columns and rows which are later represented by relation operators. Explicit pointers will not interfere between tables.
14) State the ACID rules?
Some of the acid rules stated are described as follows.
1) Atomicity: -Either transaction should be done or they should be stopped
2) Consistency: – Data should never be in a contradictory state
3) Isolation: – Two simultaneous or preceding results should never interfere with each other.
4) Durability: -Results or Data shouldn’t be discarded even during a crash of the system.
15) Explain about the Storage and physical database design?
Databases can be stored in many forms. They are usually stored on hard disks in many different forms in the form of flat files, hash buckets, ISAM, etc. Data can be stores by partitioning the data. Normalization and de normalization is generally used for efficient storage of database and also for memory management.
What are the difference between DDL, DML and DCL commands?
3) Explain about object oriented databases?
They were designed to store information related to multimedia, spatial and engineering databases. Some of the databases even stored data about software’s repositories. This was adopted in late ninety’s and new features were added.
1) It least bothers about order of the columns
2) No clones or identical rows allowed.
3) Each and every row will have a value for each column.
1) Atomicity: -Either transaction should be done or they should be stopped
2) Consistency: – Data should never be in a contradictory state
3) Isolation: – Two simultaneous or preceding results should never interfere with each other.
4) Durability: -Results or Data shouldn’t be discarded even during a crash of the system.
What are the difference between DDL, DML and DCL commands?
DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
* CREATE – to create objects in the database
* ALTER – alters the structure of the database
* DROP – delete objects from the database
* TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
* COMMENT – add comments to the data dictionary
* RENAME – rename an object
DML
Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
* SELECT – retrieve data from the a database
* INSERT – insert data into a table
* UPDATE – updates existing data within a table
* DELETE – deletes all records from a table, the space for the records remain
* MERGE – UPSERT operation (insert or update)
* CALL – call a PL/SQL or Java subprogram
* EXPLAIN PLAN – explain access path to data
* LOCK TABLE – control concurrency
DCL
Data Control Language (DCL) statements. Some examples:
* GRANT – gives user’s access privileges to database
* REVOKE – withdraw access privileges given with the GRANT command
* ALTER – alters the structure of the database
* DROP – delete objects from the database
* TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
* COMMENT – add comments to the data dictionary
* RENAME – rename an object
* INSERT – insert data into a table
* UPDATE – updates existing data within a table
* DELETE – deletes all records from a table, the space for the records remain
* MERGE – UPSERT operation (insert or update)
* CALL – call a PL/SQL or Java subprogram
* EXPLAIN PLAN – explain access path to data
* LOCK TABLE – control concurrency
* REVOKE – withdraw access privileges given with the GRANT command
SQL interview questions and answers
- What are two methods of retrieving SQL?
- What cursor type do you use to retrieve multiple recordsets?
- What is the difference between a "where" clause and a "having" clause? - "Where" is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.
- What is the basic form of a SQL statement to read data out of a table? The basic form to read data out of table is ‘SELECT * FROM table_name; ‘ An answer: ‘SELECT * FROM table_name WHERE xyz= ‘whatever’;’ cannot be called basic form because of WHERE clause.
- What structure can you implement for the database to speed up table reads?- Follow the rules of DB tuning we have to: 1] properly use indexes ( different types of indexes) 2] properly locate different DB objects across different tablespaces, files and so on.3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …)
- What are the tradeoffs with having indexes? - 1. Faster selects, slower updates. 2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.
- What is a "join"? - ‘join’ used to connect two or more tables logically with or without common field.
- What is "normalization"? "Denormalization"? Why do you sometimes want to denormalize? - Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.
- What is a "constraint"? - A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server: PRIMARY/UNIQUE - enforces uniqueness of a particular table column. DEFAULT - specifies a default value for a column in case an insert operation does not provide one. FOREIGN KEY - validates that every value in a column exists in a column of another table. CHECK - checks that every value stored in a column is in some specified list. Each type of constraint performs a specific type of action. Default is not a constraint. NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint.
- What types of index data structures can you have? - An index helps to faster search values in tables. The three most commonly used index-types are: - B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases. - Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD) - Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
- What is a "primary key"? - A PRIMARY INDEX or PRIMARY KEY is something which comes mainly from
database theory. From its behavior is almost the same as an UNIQUE INDEX, i.e. there may only be one of each value in this column. If you call such an INDEX PRIMARY instead of UNIQUE, you say something about
your table design, which I am not able to explain in few words. Primary Key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must possess the NOT NULL property. - What is a "functional dependency"? How does it relate to database table design? - 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.
- What is a "trigger"? - Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO columns as criteria where a trigger can. 2. A database constraint cannot refer old and new values for a row where a trigger can.
- Why can a "group by" or "order by" clause be expensive to process? - Processing of "group by" or "order by" clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.
- What is "index covering" of a query? - Index covering means that "Data can be found only using indexes, without touching the tables"
- What types of join algorithms can you have?
- What is a SQL view? - An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View is a good way to present data in a particular format if you use that query quite often. View can also be used to restrict users from accessing the tables directly.l
- http://vyaskn.tripod.com/iq.htm
| What is normalization? Explain different levels of normalization? |
Check out the article Q100139 from Microsoft knowledge base and of course, there's much more information available in the net. It'll be a good idea to get a hold of any RDBMS fundamentals text book, especially the one by C. J. Date. Most of the times, it will be okay if you can explain till third normal form.
| What is denormalization and when would you go for it? |
As the name indicates, denormalization is the reverse process of normalization. It's the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.
| How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables? |
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.
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.
| What's the difference between a primary key and a unique key? |
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 nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.
| What are user defined datatypes and when you should go for them? |
User defined datatypes let you extend the base SQL Server datatypes by providing a descriptive name, and format to the database. Take for example, in your database, there is a column called Flight_Num which appears in many tables. In all these tables it should be varchar(8). In this case you could create a user defined datatype called Flight_num_type of varchar(8) and use it across all your tables.
See sp_addtype, sp_droptype in books online.
See sp_addtype, sp_droptype in books online.
| What is bit datatype and what's the information that can be stored inside a bit column? |
Bit datatype is used to store boolean information like 1 or 0 (true or false). Untill SQL Server 6.5 bit datatype could hold either a 1 or 0 and there was no support for NULL. But from SQL Server 7.0 onwards, bit datatype can represent a third state, which is NULL.
| Define candidate key, alternate key, composite key. |
A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.
A key formed by combining at least two or more columns is called composite key.
A key formed by combining at least two or more columns is called composite key.
| What are defaults? Is there a column to which a default can't be bound? |
A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them. See CREATE DEFUALT in books online.
| What is a transaction and what are ACID properties? |
A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction. For more information and explanation of these properties, see SQL Server books online or any RDBMS fundamentals text book.
| Explain different isolation levels |
An isolation level determines the degree of isolation of data between concurrent transactions. The default SQL Server isolation level is Read Committed. Here are the other isolation levels (in the ascending order of isolation): Read Uncommitted, Read Committed, Repeatable Read, Serializable. See SQL Server books online for an explanation of the isolation levels. Be sure to read about SET TRANSACTION ISOLATION LEVEL, which lets you customize the isolation level at the connection level.
| CREATE INDEX myIndex ON myTable(myColumn) What type of Index will get created after executing the above statement? |
Non-clustered index. Important thing to note: By default a clustered index gets created on the primary key, unless specified otherwise.
| What's the maximum size of a row? |
8060 bytes. Don't be surprised with questions like 'what is the maximum number of columns per table'. Check out SQL Server books online for the page titled: "Maximum Capacity Specifications".
| Explain Active/Active and Active/Passive cluster configurations |
Hopefully you have experience setting up cluster servers. But if you don't, at least be familiar with the way clustering works and the two clusterning configurations Active/Active and Active/Passive. SQL Server books online has enough information on this topic and there is a good white paper available on Microsoft site.
| Explain the architecture of SQL Server |
This is a very important question and you better be able to answer it if consider yourself a DBA. SQL Server books online is the best place to read about SQL Server architecture. Read up the chapter dedicated to SQL Server Architecture.
| What is lock escalation? |
Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards it's dynamically managed by SQL Server.
| What's the difference between DELETE TABLE and TRUNCATE TABLE commands? |
DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
| Explain the storage models of OLAP |
Check out MOLAP, ROLAP and HOLAP in SQL Server books online for more infomation.
| What are the new features introduced in SQL Server 2000 (or the latest release of SQL Server at the time of your interview)? What changed between the previous version of SQL Server and the current version? |
This question is generally asked to see how current is your knowledge. Generally there is a section in the beginning of the books online titled "What's New", which has all such information. Of course, reading just that is not enough, you should have tried those things to better answer the questions. Also check out the section titled "Backward Compatibility" in books online which talks about the changes that have taken place in the new version.
| What are constraints? Explain different types of constraints. |
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
For an explanation of these constraints see books online for the pages titled: "Constraints" and "CREATE TABLE", "ALTER TABLE"
| Whar is an index? What are the types of indexes? How many clustered indexes can be created on a table? I create a separate index on each column of a table. what are the advantages and disadvantages of this approach? |
Indexes in SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.
Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
Indexes are of two types. Clustered indexes and non-clustered indexes. When you craete a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table. Non-clustered indexes have their own storage separate from the table data storage. Non-clustered indexes are stored as B-tree structures (so do clustered indexes), with the leaf level nodes having the index key and it's row locater. The row located could be the RID or the Clustered index key, depending up on the absence or presence of clustered index on the table.
If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. At the same t ime, data modification operations (such as INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated. Another disadvantage is that, indexes need disk space, the more indexes you have, more disk space is used.
| What is RAID and what are different types of RAID configurations? |
RAID stands for Redundant Array of Inexpensive Disks, used to provide fault tolerance to database servers. There are six RAID levels 0 through 5 offering different levels of performance, fault tolerance. MSDN has some information about RAID levels and for detailed information, check out the RAID advisory board's homepage
| What are the steps you will take to improve performance of a poor performing query? |
This is a very open ended question and there could be a lot of reasons behind the poor performance of a query. But some general issues that you could talk about would be: No indexes, table scans, missing or out of date statistics, blocking, excess recompilations of stored procedures, procedures and triggers without SET NOCOUNT ON, poorly written query with unnecessarily complicated joins, too much normalization, excess usage of cursors and temporary tables.
Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.
Download the white paper on performance tuning SQL Server from Microsoft web site. Don't forget to check out sql-server-performance.com
Some of the tools/ways that help you troubleshooting performance problems are: SET SHOWPLAN_ALL ON, SET SHOWPLAN_TEXT ON, SET STATISTICS IO ON, SQL Server Profiler, Windows NT /2000 Performance monitor, Graphical execution plan in Query Analyzer.
Download the white paper on performance tuning SQL Server from Microsoft web site. Don't forget to check out sql-server-performance.com
| What are the steps you will take, if you are tasked with securing an SQL Server? |
Again this is another open ended question. Here are some things you could talk about: Preferring NT authentication, using server, databse and application roles to control access to the data, securing the physical database files using NTFS permissions, using an unguessable SA password, restricting physical access to the SQL Server, renaming the Administrator account on the SQL Server computer, disabling the Guest account, enabling auditing, using multiprotocol encryption, setting up SSL, setting up firewalls, isolating SQL Server from the web server etc.
Read the white paper on SQL Server security from Microsoft website. Also check out My SQL Server security best practices
Read the white paper on SQL Server security from Microsoft website. Also check out My SQL Server security best practices
| What is a deadlock and what is a live lock? How will you go about resolving deadlocks? |
Deadlock is a situation when two processes, each having a lock on one piece of data, attempt to acquire a lock on the other's piece. Each process would wait indefinitely for the other to release the lock, unless one of the user processes is terminated. SQL Server detects deadlocks and terminates one user's process.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.
A livelock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A livelock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely.
Check out SET DEADLOCK_PRIORITY and "Minimizing Deadlocks" in SQL Server books online. Also check out the article Q169960 from Microsoft knowledge base.
| What is blocking and how would you troubleshoot it? |
Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock type. This forces the second connection to wait, blocked on the first.
Read up the following topics in SQL Server books online: Understanding and avoiding blocking, Coding efficient transactions.
Read up the following topics in SQL Server books online: Understanding and avoiding blocking, Coding efficient transactions.
| Explain CREATE DATABASE syntax |
Many of us are used to craeting databases from the Enterprise Manager or by just issuing the command: CREATE DATABAE MyDB. But what if you have to create a database with two filegroups, one on drive C and the other on drive D with log on drive E with an initial size of 600 MB and with a growth factor of 15%? That's why being a DBA you should be familiar with the CREATE DATABASE syntax. Check out SQL Server books online for more information.
| How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode? |
SQL Server can be started from command line, using the SQLSERVR.EXE. This EXE has some very important parameters with which a DBA should be familiar with. -m is used for starting SQL Server in single user mode and -f is used to start the SQL Server in minimal confuguration mode. Check out SQL Server books online for more parameters and their explanations.
| As a part of your job, what are the DBCC commands that you commonly use for database maintenance? |
DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKCATALOG, DBCC CHECKALLOC, DBCC SHOWCONTIG, DBCC SHRINKDATABASE, DBCC SHRINKFILE etc. But there are a whole load of DBCC commands which are very useful for DBAs. Check out SQL Server books online for more information.
| What are statistics, under what circumstances they go out of date, how do you update them? |
Statistics determine the selectivity of the indexes. If an indexed column has unique values then the selectivity of that index is more, as opposed to an index with non-unique values. Query optimizer uses these indexes in determining whether to choose an index or not while executing a query.
Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
3) Database is upgraded from a previous version
Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats
Some situations under which you should update statistics:
1) If there is significant change in the key values in the index
2) If a large amount of data in an indexed column has been added, changed, or removed (that is, if the distribution of key values has changed), or the table has been truncated using the TRUNCATE TABLE statement and then repopulated
3) Database is upgraded from a previous version
Look up SQL Server books online for the following commands: UPDATE STATISTICS, STATS_DATE, DBCC SHOW_STATISTICS, CREATE STATISTICS, DROP STATISTICS, sp_autostats, sp_createstats, sp_updatestats
| What are the different ways of moving data/databases between servers and databases in SQL Server? |
There are lots of options available, you have to choose your option depending upon your requirements. Some of the options you have are: BACKUP/RESTORE, dettaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.
| Explian different types of BACKUPs avaialabe in SQL Server? Given a particular scenario, how would you go about choosing a backup plan? |
Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. Check out the BACKUP and RESTORE commands in SQL Server books online. Be prepared to write the commands in your interview. Books online also has information on detailed backup/restore architecture and when one should go for a particular kind of backup.
| What is database replicaion? What are the different types of replication you can set up in SQL Server? |
Replication is the process of copying/moving data between databases on the same or different servers. SQL Server supports the following types of replication scenarios:
- Snapshot replication
- Transactional replication (with immediate updating subscribers, with queued updating subscribers)
- Merge replication
| How to determine the service pack currently installed on SQL Server? |
The global variable @@Version stores the build number of the sqlservr.exe, which is used to determine the service pack installed. To know more about this process visit SQL Server service packs and versions.
| What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors? |
Cursors allow row-by-row prcessing of the resultsets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors. Here is an example:
If you have to give a flat hike to your employees using the following criteria:
Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike
In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END
Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the 'My code library' section of my site or search for WHILE.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.
Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.
Most of the times, set based operations can be used instead of cursors. Here is an example:
If you have to give a flat hike to your employees using the following criteria:
Salary between 30000 and 40000 -- 5000 hike
Salary between 40000 and 55000 -- 7000 hike
Salary between 55000 and 65000 -- 9000 hike
In this situation many developers tend to use a cursor, determine each employee's salary and update his salary according to the above formula. But the same can be achieved by multiple update statements or can be combined in a single UPDATE statement as shown below:
UPDATE tbl_emp SET salary =
CASE WHEN salary BETWEEN 30000 AND 40000 THEN salary + 5000
WHEN salary BETWEEN 40000 AND 55000 THEN salary + 7000
WHEN salary BETWEEN 55000 AND 65000 THEN salary + 10000
END
Another situation in which developers tend to use cursors: You need to call a stored procedure when a column in a particular row meets certain condition. You don't have to use cursors for this. This can be achieved using WHILE loop, as long as there is a unique key to identify each row. For examples of using WHILE loop for row by row processing, check out the 'My code library' section of my site or search for WHILE.
| Write down the general syntax for a SELECT statements covering all the options. |
Here's the basic syntax: (Also checkout SELECT in books online for advanced syntax).
SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]
SELECT select_list
[INTO new_table_]
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC] ]
| What is a join and explain different types of joins. |
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
For more information see pages from books online titled: "Join Fundamentals" and "Using Joins".
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
For more information see pages from books online titled: "Join Fundamentals" and "Using Joins".
| Can you have a nested transaction? |
Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT
| What is an extended stored procedure? Can you instantiate a COM object by using T-SQL? |
An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement. See books online to learn how to create extended stored procedures and how to add them to SQL Server.
Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy. For an example of creating a COM object in VB and calling it from T-SQL, see 'My code library' section of this site.
Yes, you can instantiate a COM (written in languages like VB, VC++) object from T-SQL by using sp_OACreate stored procedure. Also see books online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy. For an example of creating a COM object in VB and calling it from T-SQL, see 'My code library' section of this site.
| What is the system function to get the current user's user id? |
USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
| What are triggers? How many triggers you can have on a table? How to invoke a trigger on demand? |
Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.
In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder
Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers.
Also check out books online for 'inserted table', 'deleted table' and COLUMNS_UPDATED()
In SQL Server 6.5 you could define only 3 triggers per table, one for INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards, this restriction is gone, and you could create multiple triggers per each action. But in 7.0 there's no way to control the order in which the triggers fire. In SQL Server 2000 you could specify which trigger fires first or fires last using sp_settriggerorder
Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.
Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
Till SQL Server 7.0, triggers fire only after the data modification operation happens. So in a way, they are called post triggers. But in SQL Server 2000 you could create pre triggers also. Search SQL Server 2000 books online for INSTEAD OF triggers.
Also check out books online for 'inserted table', 'deleted table' and COLUMNS_UPDATED()
| There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better? |
Instantiating COM objects is a time consuming process and since you are doing it from within a trigger, it slows down the data insertion process. Same is the case with sending emails from triggers. This scenario can be better implemented by logging all the necessary data into a separate table, and have a job which periodically checks this table and does the needful.
| What is a self join? Explain it with an example. |
Self join is just like any other join, except that two instances of the same table will be joined in the query. Here is an example: Employees table which contains rows for normal employees as well as managers. So, to find out the managers of all the employees, you need a self join.
CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)
INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'
SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Here's an advanced query using a LEFT OUTER JOIN that even returns the employees without managers (super bosses)CREATE TABLE emp
(
empid int,
mgrid int,
empname char(10)
)
INSERT emp SELECT 1,2,'Vyas'
INSERT emp SELECT 2,3,'Mohan'
INSERT emp SELECT 3,NULL,'Shobha'
INSERT emp SELECT 4,2,'Shridhar'
INSERT emp SELECT 5,2,'Sourabh'
SELECT t1.empname [Employee], t2.empname [Manager]
FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager') [Manager]
FROM emp t1
LEFT OUTER JOIN
emp t2
ON
t1.mgrid = t2.empid
http://faq.programmerworld.net/database/sql-interview-questions-and-answers.html
What is the advantage to use trigger in your PL?
Triggers are fired implicitly on the tables/views on which they are created. There are various advantages of using a trigger. Some of them are:
- Suppose we need to validate a DML statement(insert/Update/Delete) that modifies a table then we can write a trigger on the table that gets fired implicitly whenever DML statement is executed on that table.
- Another reason of using triggers can be for automatic updation of one or more tables whenever a DML/DDL statement is executed for the table on which the trigger is created.
- Triggers can be used to enforce constraints. For eg : Any insert/update/ Delete statements should not be allowed on a particular table after office hours. For enforcing this constraint Triggers should be used.
- Triggers can be used to publish information about database events to subscribers. Database event can be a system event like Database startup or shutdown or it can be a user even like User loggin in or user logoff.
What the difference between UNION and UNIONALL?
Union will remove the duplicate rows from the result set while Union all does'nt.
What is the difference between TRUNCATE and DELETE commands?
Both will result in deleting all the rows in the table .TRUNCATE call cannot be rolled back as it is a DDL command and all memory space for that table is released back to the server. TRUNCATE is much faster.Whereas DELETE call is an DML command and can be rolled back.
Which system table contains information on constraints on all the tables created ?
yes,
USER_CONSTRAINTS,
system table contains information on constraints on all the tables created
yes,
USER_CONSTRAINTS,
system table contains information on constraints on all the tables created
Explain normalization ?
Normalisation means refining the redundancy and maintain stablisation. there are four types of normalisation :
first normal forms, second normal forms, third normal forms and fourth Normal forms.
Normalisation means refining the redundancy and maintain stablisation. there are four types of normalisation :
first normal forms, second normal forms, third normal forms and fourth Normal forms.
How to find out the database name from SQL*PLUS command prompt?
Select * from global_name;
This will give the datbase name which u r currently connected to.....
Select * from global_name;
This will give the datbase name which u r currently connected to.....
What is the difference between SQL and SQL Server ?
SQLServer is an RDBMS just like oracle,DB2 from Microsoft
whereas
Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems. It was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard. SQL is used to perform various operations on RDBMS.
whereas
Structured Query Language (SQL), pronounced "sequel", is a language that provides an interface to relational database systems. It was developed by IBM in the 1970s for use in System R. SQL is a de facto standard, as well as an ISO and ANSI standard. SQL is used to perform various operations on RDBMS.
What is diffrence between Co-related sub query and nested sub query?
Correlated 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.
For example,
Correlated Subquery:
select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)
Nested Subquery:
select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)
WHAT OPERATOR PERFORMS PATTERN MATCHING?
Pattern matching operator is LIKE and it has to used with two attributes
Pattern matching operator is LIKE and it has to used with two attributes
1. % and
2. _ ( underscore )
% means matches zero or more characters and under score means mathing exactly one character
1)What is difference between Oracle and MS Access?
2) What are disadvantages in Oracle and MS Access?
3) What are feratures&advantages in Oracle and MS Access?
Oracle's features for distributed transactions, materialized views and replication are not available with MS Access. These features enable Oracle to efficiently store data for multinational companies across the globe. Also these features increase scalability of applications based on Oracle.
2) What are disadvantages in Oracle and MS Access?
3) What are feratures&advantages in Oracle and MS Access?
Oracle's features for distributed transactions, materialized views and replication are not available with MS Access. These features enable Oracle to efficiently store data for multinational companies across the globe. Also these features increase scalability of applications based on Oracle.
What is database?
A database is a collection of data that is organized so that itscontents can easily be accessed, managed and updated. open this url : http://www.webopedia.com/TERM/d/database.html
A database is a collection of data that is organized so that itscontents can easily be accessed, managed and updated. open this url : http://www.webopedia.com/TERM/d/database.html
What is cluster.cluster index and non cluster index ?
Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.Non-Clustered Index:- 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 in the disk. The leaf nodes of a non-clustered index does not consists of the data pages. instead the leaf node contains index rows.
Clustered Index:- A Clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table may have only one clustered index.Non-Clustered Index:- 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 in the disk. The leaf nodes of a non-clustered index does not consists of the data pages. instead the leaf node contains index rows.
How can i hide a particular table name of our schema?
you can hide the table name by creating synonyms.
you can hide the table name by creating synonyms.
e.g) you can create a synonym y for table x
create synonym y for x;
What is difference between DBMS and RDBMS?
The main difference of DBMS & RDBMS is
The main difference of DBMS & RDBMS is
RDBMS have Normalization. Normalization means to refining the redundant and maintain the stablization.
the DBMS hasn't normalization concept.
the DBMS hasn't normalization concept.
What are the advantages and disadvantages of primary key and foreign key in SQL?
Primary key
Advantages
1) It is a unique key on which all the other candidate keys are functionally dependent
Disadvantage
1) There can be more than one keys on which all the other attributes are dependent on.
Foreign Key
Advantage
1)It allows refrencing another table using the primary key for the other table
Which date function is used to find the difference between two dates?
datediff
datediff
for Eg: select datediff (dd,'2-06-2007','7-06-2007')
output is 5
why you need indexing ? where that is stroed and what you mean by schema object? For what purpose we are using view?
We cant create an Index on Index.. Index is stoed in user_index table.Every object that has been created on Schema is Schema Object like Table,View etc.If we want to share the particular data to various users we have to use the virtual table for the Base table...So tht is a view.
indexing is used for faster search or to retrieve data faster from various table. Schema containing set of tables, basically schema means logical separation of the database. View is crated for faster retrieval of data. It's customized virtual table. we can create a single view of multiple tables. Only the drawback is..view needs to be get refreshed for retrieving updated data.
Difference between Store Procedure and Trigger?
- we can call stored procedure explicitly.
- but trigger is automatically invoked when the action defined in trigger is done.
ex: create trigger after Insert on - this trigger invoked after we insert something on that table.
- Stored procedure can't be inactive but trigger can be Inactive.
- Triggers are used to initiate a particular activity after fulfilling certain condition.It need to define and can be enable and disable according to need.
Very Interesting Link
1. Q. What does SQL stand for?
A. Structured Query Language
Q. Who was E. F. Codd?
A. He was the original inventor of the relational model.
2. Q. How do you select all records from the table?
A. Select * from table_name;
Q. What do you understand by the term referential integrity?
3. Q. What is a join?
A. Join is a process of retrieve pieces of data from different sets (tables) and returns them to the user or program as one “joined†collection of data.
4. Q. What kinds of joins do you know? Give examples.
A. We have self join, outer joint (LEFT, RIGHT), , cross-join ( Cartesian product n*m rows returned)
Exp:
outer joint
SELECT Employee.Name, Department. DeptName
FROM Employee, Department
WHERE Employee.Employee_ID = Department.Employee_ID;
cross-join
SELECT * FROM table1, table2;
self join
SELECT e1.name | |’ ‘ | | e2.ename FROM emp e1, emp e2 WHERE e1. emp_no = e2.emp_no;
The following summarizes the result of the join operations:
The result of T1 INNER JOIN T2 consists of their paired rows where the join-condition is true.
The result of T1 LEFT OUTER JOIN T2 consists of their paired rows where the join-condition is true and, for each unpaired row of T1, the concatenation of that row with the null row of T2. All columns derived from T2 allow null values.
The result of T1 RIGHT OUTER JOIN T2 consists of their paired rows where the join-condition is true and, for each unpaired row of T2, the concatenation of that row with the null row of T1. All columns derived from T1 allow null values.
The result of T1 FULL OUTER JOIN T2 consists of their paired rows and, for each unpaired row of T2, the concatenation of that row with the null row of T1 and, for each unpaired row of T1, the concatenation of that row with the null row of T2. All columns derived from T1 and T2 allow null values. 5. Q. How do you add record to a table? A. INSERT into table_name VALUES (‘ALEX’ , 33 , ‘M’); 6. Q. How do you add a column to a table? A. ALTER TABLE Department ADD (AGE, NUMBER); 7. Q. How do you change value of the field? A. UPDATE EMP_table set number = 200 where item_munber = ‘CD’; update name_table set status = 'enable' where phone = '4161112222'; update SERVICE_table set REQUEST_DATE = to_date ('2006-03-04 09:29', 'yyyy-mm-dd hh24:MI') where phone = '4161112222'; 8. Q. What does COMMIT do? A. Saving all changes made by DML statements 9. Q. What is a primary key? A. The column (columns) that has completely unique data throughout the table is known as the primary key field. 10. Q. What are foreign keys? A. Foreign key field – is a field that links one table to another table’s primary or foreign key. 11. Q. What is the main role of a primary key in a table? A. The main role of a primary key in a data table is to maintain the internal integrity of a data table. 12. Q. Can a table have more than one foreign key defined? A. A table can have any number of foreign keys defined. It can have only one primary key defined. 13. Q. List all the possible values that can be stored in a BOOLEAN data field. A. There are only two values that can be stored in a BOOLEAN data field: -1(true) and 0(false). 14 Q. What is the highest value that can be stored in a BYTE data field? A. The highest value that can be stored in a BYTE field is 255. or from -128 to 127. Byte is a set of Bits that represent a single character. Usually there are 8 Bits in a Byte, sometimes more, depending on how the measurement is being made. Each Char requires one byte of memory and can have a value from 0 to 255 (or 0 to 11111111 in binary). 15. Q. How many places to the right of the decimal can be stored in a CURRENCY data field? A. The CURRENCY data type can store up to four places to the right of the decimal. Any data beyond the fourth place will be truncated by Visual Basic without reporting an error. 16. Q. What is a stored procedure? A. A procedure is a group of PL/SQL statements that can be called by a name. Procedures do not return values they perform tasks. 17. Q. Describe how NULLs work in SQL? A. The NULL is how SQL handles missing values. Arifthmetic operation with NULL in SQL will return a NULL. 18. Q. What is Normalization? A. The process of table design is called normalization. 19. Q. What is referential integrity constraints? A. Referential integrity constraints are rules that are partnof the table in a database schema. 20. Q. What is Trigger? A. Trigger will execute a block of procedural code against the database when a table event occurs. A2. A trigger defines a set of actions that are performed in response to an insert, update, or delete operation on a specified table. When such an SQL operation is executed, in this case the trigger has been activated. 21. Q. Which of the following WHERE clauses will return only rows that have a NULL in the PerDiemExpenses column? A. WHERE PerDiemExpenses <> B. WHERE PerDiemExpenses IS NULL C. WHERE PerDiemExpenses = NULL D. WHERE PerDiemExpenses NOT IN (*) A. B is correct � When searching for a NULL value in a column, you must use the keyword IS. No quotes are required around the keyword NULL. 22. Q. You issue the following query:SELECT FirstName FROM StaffListWHERE FirstName LIKE'_A%'Which names would be returned by this query? Choose all that apply. A. Allen B. CLARK C. JACKSON D. David A. C is correct � Two wildcards are used with the LIKE operator. The underscore (_) stands for any one character of any case, and the percent sign (%) stands for any number of characters of any case including none. Because this string starts with an underscore rather than a percent sign, it won't return Allen or Clark because they represent zero and two characters before the "A". If the LIKE string had been "%A%", both of these values would have been returned. David was not returned because all non-wild card characters are case sensitive. Therefore, only strings with an uppercase "A" as their second letter are returned 23. Q. Write a SQL SELECT query that only returns each city only once from Students table? Do you need to order this list with an ORDER BY clause? A. SELECT DISTINCT City FROM Students; The Distinct keyword automatically sorts all data in ascending order. However, if you want the data sorted in descending order, you have to use an ORDER BY clause 24. Q. Write a SQL SELECT sample of the concatenation operator. A. SELECT LastName ||',' || FirstName, City FROM Students; 25. Q. How to rename column in the SQL SELECT query? A. SELECT LastName ||',' || FirstName AS "Student Name", City AS "Home City" "FROM StudentsORDER BY "Student Name"
26. Q. Write SQL SELECT example how you limiting the rows returned with a WHERE clause. A. SELECT InstructorID, Salary FROM Instructors WHERE Salary > 5400 AND Salary < 6600; 27. Q. Write SQL SELECT query that returns the first and last name of each instructor, the Salary, and gives each of them a number. A. SELECT FirstName, LastName, Salary, ROWNUM FROM Instructors; 28. Q. Which of the following functions can be used only with numeric values? (Choose all that apply.) A. AVG B. MIN C. LENGTH D. SUM E. ROUND A. A and D � Only A and D are correct. The MIN function works with any character, numeric, or date datatype. The LENGTH function is a character function that returns the number of letters in a character value. The ROUND function works with both numeric and date values. 29. Q. Which function do you use to remove all padded characters to the right of a character value in a column with a char datatype? A. RTRIM B. RPAD C. TRIM A. C � The TRIM function is used to remove padded spaces. LTRIM and RTRIM functions were included in earlier versions of Oracle, but Oracle 8i has replaced them with a single TRIM function 30. Q. Which statement do you use to eliminate padded spaces between the month and day values in a function TO_CHAR(SYSDATE,'Month, DD, YYYY') ? A. To remove padded spaces, you use the "fm" prefix before the date element that contains the spaces. TO_CHAR(SYSDATE,'fmMonth DD, YYYY') 31. Q. Is the WHERE clause must appear always before the GROUP BY clause in SQL SELECT ? A. Yes. The proper order for SQL SELECT clauses is: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY. Only the SELECT and FROM clause are mandatory. 32. Q. How Oracle executes a statement with nested subqueries? A. When Oracle executes a statement with nested subqueries, it always executes the innermost query first. This query passes its results to the next query and so on until it reaches the outermost query. It is the outermost query that returns a result set. 33. Q. Which operator do you use to return all of the rows from one query except rows are returned in a second query? A. You use the MINUS operator to return all rows from one query except where duplicate rows are found in a second query. The UNION operator returns all rows from both queries minus duplicates. The UNION ALL operator returns all rows from both queries including duplicates. The INTERSECT operator returns only those rows that exist in both queries. 34. Q. How you will create a column alias? (Oracle 8i) A. The AS keyword is optional when specifying a column alias. You must enclose the column alias in double quotes when the alias contains a space or lowercase letters. If you specify an alias in l owercase letters without double quotes, the alias will appear in uppercase. 35 Q. Which of the following statements are Data Manipulation Language commands? A. INSERT B. UPDATE C. GRANT D. TRUNCATE E. CREATE A. A and B � The INSERT and UPDATE statements are Data Manipulation Language (DML) commands. GRANT is a Data Control Language (DCL) command. TRUNCATE and CREATE are Data Definition Language (DDL) commands 36. Question. What is Oracle locking? A. Oracle uses locking mechanisms to protect data from being destroyed by concurrent transactions. 37. Question. What Oracle lock modes do you know? A. Oracle has two lock modes: shared or exclusive. Shared locks are set on database resources so that many transactions can access the resource. Exclusive locks are set on resources that ensure one transaction has exclusive access to the database resource 38. Question. What is query optimization? A. Query optimization is the part of the query process in which the database system compares different query strategies and chooses the one with the least expected cost 39. Question. What are the main components of Database management systems software. A. The database management system software includes components for storage management, concurrency control, transaction processing, database manipulation interface, database definition interface, and database control interface. 40. Question. What are the main attributes of database management system? A. A database management system is composed of five elements: computer hardware, software, data, people (users), and operations procedures. 41. Question. What is transaction? A. A transaction is a collection of applications code and database manipulation code bound into an indivisible unit of execution. it consists from: BEGIN-TRANSACTION Name Code END TRANSACTION Name 42. Question. What databases do you know? Informix DB2 SQL Oracle 43. Question. Explain SQL SELECT example: select j.FILE_NUM from DB_name.job j, DB_name.address a where j.JOB_TYPE ='C' AND j.COMPANY_NAME = 'TEST6' AND j.OFFICE_ID = '101' AND j.ACTIVE_IND = 'Y' AND a.ADDRESS_STATUS_ID = 'H' AND a.OFFICE_ID = '101' AND a.FILE_NUM = j.FILE_NUM order by j.FILE_NUM; Answer: j and a aliases for table names. this is outer joint select statament from two tables. 44. Q. Describe some Conversion Functions that you know A. TO_CHAR converts a number / date to a string. TO_DATE converts a string (representing a date) to a date. TO_NUMBER converts a character string containing digits to a numeric data type, it accepts one parameter which is a column value or a string literal 45. Q. Describe some Group Functions that you know A. 1) The COUNT function tells you how many rows were in the result set. SELECT COUNT(*) FROM TESTING.QA 2) The AVG function tells you the average value of a numeric column. SELECT MAX(SALARY) FROM TESTING.QA 3) The MAX and MIN functions tell you the maximum and minimum value of a numeric column. SELECT MIN(SALARY) FROM TESTING.QA 4) The SUM function tells you the sum value of a numeric column. SELECT SUM(SALARY) FROM TESTING.QA 46. Question. What does DML stand for? A. DML is Data Manipulation Language statements. (SELECT) 47. Question. What does DDL stand for? A. DDL is Data Definition Language statements. (CREATE) 48. Question. What does DCL stand for? A. DCL is Data Control Language statements. (COMMIT) 49. Question: Describe SQL comments. A. SQL comments are introduced by two consecutive hyphens (--) and ended by the end of the line. 50. Q. In what sequence SQL statement are processed? A. The clauses of the subselect are processed in the following sequence (DB2): 1. FROM clause 2. WHERE clause 3. GROUP BY clause 4. HAVING clause 5. SELECT clause 6. ORDER BY clause 7. FETCH FIRST clause 51. Q. Describe TO_DATE function. A. The TO_DATE function returns a timestamp from a character string that has been interpreted using a character template. TO_DATE is a synonym for TIMESTAMP_FORMAT. 52. Question: In the domain table we have status as a numeric value from 01 to 04 and we have text definition of these values in the design document. Write SQL query to see the result as a text definitions that is corresponded to these values. (DB2) A. select TB1.member_id, TB1.bu_id, TB1.program, TB2.num, case TB1.status when '01' then 'Auto renew' when '02' then 'Expired' when '03' then 'Sold' when '04' then ‘Terminated’ else TB_name.status end from DB_name.TB_name1 TB1, DB_name.TB_name2 TB2 where TB1.program in ('com', 'org') and TB1.member_role = '100' order by TB1.member_id fetch first 30 rows only 53. Question: What's the logical difference, if any, between the following SQL expressions? SELECT COUNT ( * ) FROM T SELECT SUM ( 1 ) FROM T A. They're the same unless table T is empty, in which case the first yields a one-column, one-row table containing a zero and the second yields a one-column, one-row table "containing a null."
Simple SQL Interview Questions and Answers
http://testinginterviewquestionsandanswers.com/simple-sql-interview-questions.html
1. Explain how a “where” clause differs from a “having” clause?
The “where” is used as a command for restricting data from a database but a “having” clause is used as a command for filtering. For example, a “where” clause may be used to retrieve results and a “have” clause may be used to filter the results of the retrieved data.
2. How can you speed up table reads for a database?
In order to tune the database one should do the following:
a) Check for proper index use
b) Check for correct location of database objects across multiple files, table spaces, etc.
c) Create a particular area with a special data type (such as a table space) to place some of the data
3. Explain database replication.
Database replication is the practice of moving or copying data from one database to another. There are three types of replication supported by SQL Server: merge, snapshot, and transactional.
4. Name a few trade-offs for using indexes?
- Allows faster selection, but updates are slower because both the table and the index must be updated.
- Indexes require additional storage space.
5. What is the size limit for a row?
A row can be a maximum of 8060 bytes.
6. Explain how a “join” command is used.
A “join” command is used to logically connect two or more tables. This can be done either with or without a common field.
7. What are user-defined data types and give an example of how they can be used?
User-defined data types provides the database with a descriptive name and format and allows the base SQL Server data types to be extended. As an example, let’s say a database has a column named bin_num that is varchar(8) and is used in several tables. A user-defined data type called bin_num_type of varchar(8) can be created and used in all of the tables.
8. What is the difference between “normalization” and “Denormalization”?
When data is normalizing redundant information is removed from the table and it is organized to make future changes simpler. For denormalization, redundant data is allowed to remain in the table. Denormalization can be beneficial because reducing the number of joins used for data processing can make data manipulation and retrieval simpler and therefore and improvement in performance.
9. Describe the purpose of a constraint.
Constraints provide a means to check tables for referential integrity. SQL Server has four types of constraints and a default:
DEFAULT – indicates a column default value in the event that one is not provided by an insert operation
NOT NULL – does not allow null values in the specified column
PRIMARY/UNIQUE – requires values in a specific column to be unique
FOREIGN KEY – checks that each value in a particular column exists in a column in a different table.
CHECK – verifies that all stored values in particular column exist in a list that is specified.
10. What is the difference between a primary and a unique key?
While the primary and unique keys enforce uniqueness in their columns, a clustered index is created in the column of the primary key, and a non-clustered index is created in the column of the unique key. Also, NULL values are not allowed with a primary key but up to one is allowed with a unique key.
11. What is a “trigger”?
Triggers are procedures that are created for a database to enforce rules of integrity. They are executed whenever an operation to modify data such as insert or delete is performed.
12. What type of information is stored in a column that is specified with a bit data type?
The bit data type is used for Boolean data, which is either true or false, 1 or 0. SQL Server versions prior to version 6.5 only allowed the storage of 1’s or 0’s, no NULL values. However, version 7.0 and beyond allows the storage of NULL values.
13. Explain RAID.
RAID is an acronym for Redundant Array of Inexpensive Disk. RAIDs provide fault tolerance for database servers and has six (numbered 0-5) levels of different fault tolerance performance.
14. When does blocking occur?
Blocking occurs when an application connection holds a lock and a conflicting lock type if required for a second connection. In this case, the second connection is considered blocked by the first connection.
15. Explain isolation levels.
Isolation levels include the following: read uncommitted, read committed (default), repeatable read, and serializable. Isolation levels determine the extent of data isolation between simultaneous transactions.
16. What is a deadlock?
A deadlock is a condition where two processes have established a lock on an article of data and each tries to obtain a lock on the other process’ piece. In this case, unless one process is terminated, both processes will wait indefinitely for the data to be released. If SQL Server detects
a deadlock, it will kill one of the processes.
17. What is a live lock?
A live lock occurs when a request for an exclusive lock is denied over and over due to interference from shared locks that overlap. This can also occur when a read transaction dominates a page or a table and forces indefinite waiting for a write transaction. If the SQL Server detects four denials of this type, it automatically refuses subsequent shared locks.
18. Give a few SQL server options to move data and/or databases between servers and other databases.
Some options are the following:
- Replication
- DTS
- BCP
- BACKUP/RESTORE command
- Attaching/detaching databases
- Log shipping
- SELECT…INTO
- INSERT…SELECT
- Generating data with INSERT scripts
19. Explain cursors and their disadvantages.
Cursors are used for row-by-row processing of results. There are four types of coursers: dynamic, static, keyset-driven, and forward only. One of the disadvantages of using curser is that whenever a row from a cursor is fetched, it results in a network round-trip. Cursors also require more temporary storage and other resources. In addition to this, some types of cursors impose restrictions on the use of SELECT statements.
20. What is the difference between TRUNCATE TABLE and DELETE TABLE?
The DELETE TABLE command is a little slow because it is recorded in the transaction log. Like DELETE TABLE, the TRUNCATE TABLE command also deletes all the rows in a table, but it is a bit faster because this is not logged. Instead, the TRUNCATE TALE command logs the de-allocation of the table data pages.
21. What is a transaction and what are its properties?
A transaction is considered to be a unit of work with steps in which all or none must be performed. The properties of a transaction can be defined using the acronym ACID, which stands for Atomicity, Consistency, Isolation, and Durability.
SQL Interview Questions For Software Testers
http://extremesoftwaretesting.com/InterviewQuestions/SQLinterviewQuestions.html
Cool Interview
http://www.coolinterview.com/type.asp?iType=19
http://www.careerride.com/Database-concepts-interview-questions.aspx
Test your Database skills!
Define Fact tables and dimension tables.
Fact tables are central tables in data warehousing. They contain the aggregate values that are used in business process..............
Read answer
Explain the ETL process in Data warehousing.
Extraction, Transformation and loading are different stages in data warehousing................
Read answer
What is Data mining?
Data mining is a process of analyzing current data and summarizing the information in more useful manner..................
Read answer
What are indexes?
Index can be thought as index of the book that is used for fast retrieval of information.
Index uses one or more column index keys and pointers to the record to locate record...............
Read answer
Explain the types of indexes?
Clustered index
Non-clustered.....................
Read answer
Define SQL.
SQL stands for Structured Query Language. It allows access, insert/update/delete records and retrieve data from the database...................
Read answer
What is RDBMS? Explain its features.
RDBMS stands for Relational Database Management System. It organizes data into related rows and columns...................
Read answer
What is an Entity-Relationship diagram?
It is a graphical representation of tables with the relationship between them................
Read answer
Define referential integrity.
It is the rules that are applied when the relationships are created. It ensures integrity of data and prevents inconsitent data into the tables...............
Read answer
Define Primary key and Foreign key.
A column or combination of columns that identify a row of data in a table is Primary Key............
Read answer
Define alternate key.
There can be a key apart from primary key in a table that can also be a key. This key may or may not be a unique key..............
Read answer
Database interview questions- April 06, 2009, 17:40 pm by Nishant Kumar
Delete vs. Truncate table.
Delete logs the deletion of each row whereas Truncate doesn't log deleted rows in the transaction log. This makes truncate command is bit faster than Delete command.
Define constraints.
Constraints enforce integrity of the database. Constraints can be of following types
Not Null
Check
Unique
Primary key
Foreign key
Database interview questions- April 12, 2009, 14:50 pm by Nishant Kumar
Define stored procedure.
Stored procedure is a set of pre-compiled SQL statements, executed when it is called in the program.
Define Trigger.
Triggers are similar to stored procedure except it is executed automatically when any operations are occurred on the table.
Part 1 | Part 2 | Part 3 | Part 4
Next>>
Also read
What is Data warehousing?
Answer - A data warehouse can be considered as a storage area where interest specific or relevant data........
What is an OLTP system and OLAP system?
Answer - OLTP: Online Transaction and Processing helps and manages applications based........
SQL Server 2005 Analysis Services Interview Questions
What is SQL Server 2005 Analysis Services (SSAS)?
What are the new features with SQL Server 2005 Analysis Services (SSAS)?
What are SQL Server Analysis Services cubes?
Explain the purpose of synchronization feature provided in Analysis Services 2005.
Explain the new features of SQL Server 2005 Analysis Services (SSAS). [Hint - Unified Dimensional Model, Data Source View, new aggregation functions and querying tools]....................
OLAP interview questions
Explain the concepts and capabilities of OLAP.
Explain the functionality of OLAP.
What are MOLAP and ROLAP?
Explain the role of bitmap indexes to solve aggregation problems.
Explain the encoding technique used in bitmaps indexes.
What is Binning?
What is candidate check?..................
Define Truncate and Delete commands.
Answer - Truncate command is used to remove all rows of the column.The removed records are not recorded in the transaction log......
Define Primary and Unique key.
Answer - The column or columns of the table whose value uniquely identifies each row in the table is called primary key. You can define column as primary key using primary key constraint while you create table.....
What is index? Define its types.
Answer - Index can be thought as index of the book that is used for fast retrieval of information. Index uses one or more column index keys and pointers to the record to locate record.........
Define Normalization and De- Normalization.
Answer - It is the process of organizing data into related table. To normalize database, we divide database into tables.....
What is transact-SQL? Describe its types?
Answer - SQL Server Provides three types of Transact-SQL statements namely DDL, DCL, and DML
Define SQL.
Structured query language, SQL is an ANSI standard language that provides commands to access and update databases.............
Explain the difference between DBMS and RDBMS.
DBMS offers organized way of storing, managing and retrieving information..........
What are E-R diagrams?
E-R diagrams, i.e. Entity-Relationship diagram represent relationship between various tables in the database..............
Explain the types of relationships in database.
One-to-one
One to one is implemented using single table by establishing relationship between same type of columns in a table...............
What are the benifits of normalizing database?
It helps to avoid duplicate entries.
It allows saving storage space....................
What is normalization?
It is the process of organizing data into related table.
To normalize database, we divide database into tables and establish relationships between the tables............
What is denormalization?
The process of adding redundant data to get rid of complex join, in order to optimize database performance. This is done to speed up database access by moving from higher to lower form of normalization.................
Explain DML and DDL statements.
Data definition language is used to define and manage all attributes and properties of a database..................
Part 1 | Part 2 | Part 3 | Part 4
<<Previous Next>>
Also read
Define database objects.
Answer - SQL Server database stores information in a two dimensional objects of rows and columns called table......
Define data, entity, domain and referential integrity.
Answer - Data Integrity validates the data before getting stored in the columns of the table. SQL Server supports four type of data integrity.....
SQL Server Optimization Tips
Answer - Restricting query result means return of required rows instead of all rows of the table. This helps in reducing network traffic......
What are the lock types?
Answer - Shared Lock allows simultaneous access of record by multiple Select statements. Shared Lock blocks record from updating and will remain in queue waiting while record is accessed for reading......
XSLT in SQL Server 2005
Overview of XSLT and the components that make up an XSLT style sheet.
What is XSLCompiledTransform class of the .NET Framework?
What is XSLTSetting class of the .NET Framework?
What is Union and Union All operator?
Union is used to combine distinct records from two tables. Union all combines all records from two tables..............
What is cursor?
A Cursor is a database object that represents a result set and is used to manipulate data row by row. When a cursor is opened, it is positioned on a row and that row is available for processing.............
Explain the cursor types.
DYNAMIC: It reflects changes happened on the table while scrolling through the row.
STATIC: It works on snapshot of record set and disconnects from the server...............
Explain in brief the cursor optimization tips.
Close cursor when it is not required.
You shouldn’t forget to deallocate cursor after closing it................
What is sub-query?
Sub-query is a query within a Query. Example of sub-query:
Select CustId, Custname From Customer Where Cust_Id IN (Select Doct_Id from Doctor)...............
Explain the use of group by clause.
"Group By" is used to derive aggegate values by grouping similar data................
Difference between clustered and non-clustered index.
Both stored as B-tree structure. The leaf level of a clustered index is the actual data where as leaf level of a non-clustered index is pointer to data...............
Define aggregate and scalar functions.
Aggregate Functions return a single value by operating against a group of values. Scalar functions operate against a single value................
What are the restrictions applicable while creating views?
Views can be created referencing tables and views only in the current database.
A view name must not be the same as any table owned by that user.
You can build views on other views and on procedures that reference views.............
What is "correlated subqueries"?
In "correlated subqueries", the result of outer query is passed to the subquery and the subquery runs for each row...............
What is Data Warehousing?
Data Warehousing is a process of storing and accessing data from central location for some strategic decision................
What is a join and explain different types of joins.
Joins are used in queries to explain how different tables are related.
Joins also let you select data from a table depending upon data from another table...
http://www.brentozar.com/archive/2009/06/top-10-developer-interview-questions-about-sql-server/
Top 10 Developer Interview Questions About SQL Server
Knowing good SQL questions to ask during an interview with a developer can help you filter out the best candidates from the ones who aren’t the most qualified. There’s a huge difference between “It worked on my machine” and “It scales well in production.” These interview questions will help you filter out the bad apples before you hire them.
10. Explain why DBAs don’t like cursors.
I like to phrase this interview question this way because I’m not saying the DBA is right – I’m just asking the developer to explain the DBA’s point of view. I don’t have a problem with the developer rolling their eyes as they explain the answer, but I have a problem with the developer being surprised by the question.
The candidate gets bonus points if they seem even vaguely aware of the terms “set-based processing” and “row-based processing”, but that’s purely a bonus. (I wish I could say that these concepts are requirements, but in today’s economic market, companies don’t always want to pay top dollar to get the best candidates.)
9. Where do you like business logic – in the app or in the database? Why?
Personally, I like stored procedures because they’re easier for us DBAs to test, tune and tweak. On the other hand, the developer community isn’t always as fond of stored procs. For their side, see these posts by Jeff Atwood:
I don’t mind what arguments the coder candidate uses, but I want to see ‘em put some thought into it. No matter which angle they take, I’ll play the devil’s advocate and prod them with arguments just to see how they react.
8. Explain when and how transactions should be used.
Not In The Oprah Book Club, Oddly
Start with just that open-ended interview question, and if they have trouble getting started, give them a scenario.
“Say we’ve got a table for Orders, and a table for OrderDetails. Someone places an order for two books – Bacon: A Love Story and the hit bestseller Eat What You Want and Die Like A Man. Tell me what happens.”
After they’ve answered, ask them when transactions should not be used. I don’t want my developers wrapping anything inside a transaction unless it absolutely needs to be. (Unlike bacon, which should be used as often as possible for wrapping purposes.)
7. Explain referential integrity and where it can be enforced.
If they stumble on the question, circle back to the Orders and OrderDetails tables we used as examples earlier. What’s an orphan? How do we make sure that we don’t end up with OrderDetails for records with no matching Order record? Where are all the places we could enforce referential integrity? (Think foreign keys, triggers, the application, or not at all.) Have you worked in places where there was no referential integrity, and what problems did you run into?
6. What’s the fastest way to get a thousand records into the database?
I’m not looking for the best answers – I’m just looking to hear that they’ve done some work to performance tune their queries. If they’re doing fully logged individual record inserts, one at a time, into a data warehouse-size system, we’re going to have problems down the road. (Yes, I’ve actually worked with a BI developer that did millions of individual inserts per night in full recovery mode and thought the performance was the database’s fault.)
Bonus points if they link back to the previous interview question and talk about whether or not they should disable constraints or referential integrity during data loads. (I don’t care what their final answer is, but I just want them to know the pros and cons.)
5. What’s the difference between a primary key and a clustered index?
This is almost a bonus question. Most of the time, the candidate doesn’t know because it’s a function of the data modeler or architect, not the developer. However, I want to see how the candidate reacts to tough questions. Ideally, they say in a relaxed tone of voice, “I’m not sure, but I know who I’d ask.” If they don’t mention where they’d go, ask them where they go for SQL Server answers. Speaking of which…
Bonus Points for This Candidate
4. What’s your StackOverflow name?
I don’t need to see a high reputation, but I do want to see an awareness of the site. This interview question serves two purposes: it finds out if they’re serious enough to be active in the community, and it shows them that you’re okay with their community activity. Start a conversation with them about the level of internet time that you find acceptable in the office, and encourage them to share their knowledge with their peers. This sells the candidate on your shop.
3. Tell me about a time when a DBA got mad at you.
This is a spin on the classic interview question, “Tell me about a time when you failed.” Implemented a user-defined function, trigger, CLR in the database, or something else that made the DBA freak out? I want to hear that the candidate listened to what the DBA had to say, good or bad.
If they say it’s never happened, rest assured it’s going to happen soon.
2. How can you tell if a query will scale for production?
I want to hear that they do things like load tests or maybe look at execution plans.
I’m sometimes comfortable when a senior developer says things like, “I can pretty well tell when something isn’t going to scale, because I know the production boxes really well.” The key is asking a followup question about times when things didn’t scale.
1. When is the DBA right?
Always, kid. Always.
http://ripalsoni.wordpress.com/2007/09/13/basic-interviewquestions-for-plsql/
Basic InterviewQuestions for PL/SQL
September 13, 2007
1. What is PL/SQL ?
Ans. PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
2. What is the basic structure of PL/SQL ?
Ans. PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.
3. What are the components of a PL/SQL block ?
Ans. A set of related declarations and procedural statements is called block.
4. What are the components of a PL/SQL Block ?
Ans. Declarative part, Executable part and Exception part.
Datatypes PL/SQL
5. What are the datatypes a available in PL/SQL ?
Ans. Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.
Some composite data types such as RECORD & TABLE.
6. What are % TYPE and % ROWTYPE ? What are the advantages of using these over datatypes?
Ans. % TYPE provides the data type of a variable or a database column to that variable.
% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.
The advantages are : I. Need not know about variable’s data type
ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.
7. What is difference between % ROWTYPE and TYPE RECORD ?
Ans. % ROWTYPE is to be used whenever query returns a entire row of a table or view.
TYPE rec RECORD is to be used whenever query returns columns of different
table or views and variables.
E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type
);
e_rec emp% ROWTYPE
cursor c1 is select empno,deptno from emp;
e_rec c1 %ROWTYPE.
8. What is PL/SQL table ?
Ans. Objects of type TABLE are called “PL/SQL tables”, which are modeled as (but not the same as) database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.
9. What is a cursor ? Why Cursor is required ?
Ans. Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.
10. Explain the two type of Cursors ?There are two types of cursors, Implicit Cursor and Explicit Cursor.
PL/SQL uses Implicit Cursors for queries.
User defined cursors are called Explicit Cursors. They can be declared and used.
11. What are the PL/SQL Statements used in cursor processing ?
Ans. DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO or Record types, CLOSE cursor name.
12. What are the cursor attributes used in PL/SQL ?
Ans. %ISOPEN – to check whether cursor is open or not
% ROWCOUNT – number of rows fetched/updated/deleted.
% FOUND – to check whether cursor has fetched any row. True if rows are fetched.
% NOT FOUND – to check whether cursor has fetched any row. True if no rows are fetched.
These attributes are proceeded with SQL for Implicit Cursors and with Cursor name for Explicit Cursors.
13. What is a cursor for loop ?
Ans. Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes
when all the records have been processed.
eg. FOR emp_rec IN C1 LOOP
salary_total := salary_total +emp_rec sal;
END LOOP;
14. What will happen after commit statement ?
Ans. Cursor C1 is
Select empno,
ename from emp;
Begin
open C1; loop
Fetch C1 into
eno.ename;
Exit When
C1 %notfound;—–
commit;
end loop;
end; The cursor having query as SELECT …. FOR UPDATE gets closed after COMMIT/ROLLBACK.
The cursor having query as SELECT…. does not get closed even after COMMIT/ROLLBACK.
15. Explain the usage of WHERE CURRENT OF clause in cursors ?
Ans. WHERE CURRENT OF clause in an UPDATE, DELETE statement refers to the latest row fetched from a cursor.
16. What is a database trigger ? Name some usages of database trigger ?
Ans. Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are Audit data modifications, Log events transparently, Enforce complex business rules Derive column values automatically, Implement complex security authorizations. Maintain replicate tables.
17. How many types of database triggers can be specified on a table ? What are they ?
Ans. Insert Update Delete
Before Row o.k. o.k. o.k.
After Row o.k. o.k. o.k.
Before Statement o.k. o.k. o.k.
After Statement o.k. o.k. o.k.
If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement.
If WHEN clause is specified, the trigger fires according to the returned boolean value.
18. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database Trigger ? Why ?
Ans.It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger, it affects logical transaction processing.
19. What are two virtual tables available during database trigger execution ?
Ans. The table columns are referred as OLD.column_name and NEW.column_name.
For triggers related to INSERT only NEW.column_name values only available.
For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.
For triggers related to DELETE only OLD.column_name values only available.
20. What happens if a procedure that updates a column of table X is called in a database trigger of the same table ?
Ans. Mutation of table occurs.
21. What is an Exception ? What are types of Exception ?
Ans. Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined. Some of Predefined exceptions are.
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NOT_LOGGED_ON
PROGRAM-ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
VALUE_ERROR
ZERO_DIVIDE
OTHERS.
22. What is Pragma EXECPTION_INIT ? Explain the usage ?
Ans. The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To get an error message of a specific oracle error.
e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)
23. What is Raise_application_error ?
Ans. Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an user_defined error messages from stored sub-program or database trigger.
24. What are the return values of functions SQLCODE and SQLERRM ?
Ans. SQLCODE returns the latest code of the error that has occurred.
SQLERRM returns the relevant error message of the SQLCODE.
25. Where the Pre_defined_exceptions are stored ?
Ans. In the standard package.
26. What is a stored procedure ?
Ans. A stored procedure is a sequence of statements that perform specific function.
27. What is difference between a PROCEDURE & FUNCTION ?
Ans. A FUNCTION always returns a value using the return statement.
A PROCEDURE may return one or more values through parameters or may not return at all.
28. What are advantages fo Stored Procedures ?
Ans. Extensibility, Modularity, Reusability, Maintainability and one time compilation.
29. What are the modes of parameters that can be passed to a procedure ?
Ans. IN, OUT, IN-OUT parameters.
30. What are the two parts of a procedure ?
Ans. Procedure Specification and Procedure Body.
31. Give the structure of the procedure ?
Ans. PROCEDURE name (parameter list…..)
is
local variable declarations
BEGIN
Executable statements.
Exception.
exception handlers
end;
32. Give the structure of the function ?
Ans. FUNCTION name (argument list …..) Return datatype is
local variable declarations
Begin
executable statements
Exception
execution handlers
End;
33. Explain how procedures and functions are called in a PL/SQL block ?
Ans. Function is called as part of an expression.
sal := calculate_sal (‘a822′);
procedure is called as a PL/SQL statement
calculate_bonus (‘A822′);
34. What is Overloading of procedures ?
Ans. The Same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.
e.g. DBMS_OUTPUT put_line
35. What is a package ? What are the advantages of packages ?
Ans. Package is a database object that groups logically related procedures.
The advantages of packages are Modularity, Easier Application Design, Information. Hiding,. reusability and Better Performance.
36.What are two parts of package ?
Ans. The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.
Package Specification contains declarations that are global to the packages and local to the schema.
Package Body contains actual procedures and local declaration of the procedures and cursor declarations.
37. What is difference between a Cursor declared in a procedure and Cursor declared in a package specification ?
Ans. A cursor declared in a package specification is global and can be accessed by other procedures or procedures in a package.
A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.
38. Name the tables where characteristics of Package, procedure and functions are stored ?
Ans. User_objects, User_Source and User_error
Sql Queries most asked in job interviews.
http://www.codeproject.com/KB/database/SqlQueriesInterview.aspx
(i) Finding the nth highest salary of an employee.
Create a table named Employee_Test and insert some test data as:-
Collapse
CREATE TABLE Employee_Test
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)
INSERT INTO Employee_Test VALUES ('Anees',1000);
INSERT INTO Employee_Test VALUES ('Rick',1200);
INSERT INTO Employee_Test VALUES ('John',1100);
INSERT INTO Employee_Test VALUES ('Stephen',1300);
INSERT INTO Employee_Test VALUES ('Maria',1400);
It is very easy to find the highest salary as:-
Collapse
--Highest Salary
select max(Emp_Sal) from Employee_Test
Now, if you are asked to find the 3rd highest salary, then the query is as:-
Collapse
--3rd Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top 3 Emp_Sal from Employee_Test order by Emp_Sal desc)
The result is as :- 1200
To find the nth highest salary, replace the top 3 with top n (n being an integer 1,2,3 etc.)
Collapse
--nth Highest Salary
select min(Emp_Sal) from Employee_Test where Emp_Sal in
(select distinct top n Emp_Sal from Employee_Test order by Emp_Sal desc)
(ii) Finding TOP X records from each group
Create a table named photo_test and insert some test data as :-
Collapse
create table photo_test
(
pgm_main_Category_id int,
pgm_sub_category_id int,
file_path varchar(MAX)
)
insert into photo_test values
(17,15,'photo/bb1.jpg');
insert into photo_test values(17,16,'photo/cricket1.jpg');
insert into photo_test values(17,17,'photo/base1.jpg');
insert into photo_test values(18,18,'photo/forest1.jpg');
insert into photo_test values(18,19,'photo/tree1.jpg');
insert into photo_test values(18,20,'photo/flower1.jpg');
insert into photo_test values(19,21,'photo/laptop1.jpg');
insert into photo_test values(19,22,'photo/camer1.jpg');
insert into photo_test values(19,23,'photo/cybermbl1.jpg');
insert into photo_test values
(17,24,'photo/F1.jpg');
There are three groups of pgm_main_category_id each with a value of 17 (group 17 has four records),18 (group 18 has three records) and 19 (group 19 has three records).
Now, if you want to select top 2 records from each group, the query is as follows:-
Collapse
select pgm_main_category_id,pgm_sub_category_id,file_path from
(
select pgm_main_category_id,pgm_sub_category_id,file_path,
rank() over (partition by pgm_main_category_id order by pgm_sub_category_id asc) as rankid
from photo_test
) photo_test
where rankid < 3 -- replace 3 by any number 2,3 etc for top2 or top3.
order by pgm_main_category_id,pgm_sub_category_id
The result is as:-
Collapse
pgm_main_category_id pgm_sub_category_id file_path
17 15 photo/bb1.jpg
17 16 photo/cricket1.jpg
18 18 photo/forest1.jpg
18 19 photo/tree1.jpg
19 21 photo/laptop1.jpg
19 22 photocamer1.jpg
(iii) Deleting duplicate rows from a table
A table with a primary key doesn’t contain duplicates. But if due to some reason, the keys have to be disabled or when importing data from other sources, duplicates come up in the table data, it is often needed to get rid of such duplicates.
This can be achieved in tow ways :-
(a) Using a temporary table.
(b) Without using a temporary table.
(a) Using a temporary or staging table
Let the table employee_test1 contain some duplicate data like:-
Collapse
CREATE TABLE Employee_Test1
(
Emp_ID INT,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)
INSERT INTO Employee_Test1 VALUES (1,'Anees',1000);
INSERT INTO Employee_Test1 VALUES (2,'Rick',1200);
INSERT INTO Employee_Test1 VALUES (3,'John',1100);
INSERT INTO Employee_Test1 VALUES (4,'Stephen',1300);
INSERT INTO Employee_Test1 VALUES (5,'Maria',1400);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);
INSERT INTO Employee_Test1 VALUES (6,'Tim',1150);
Step 1: Create a temporary table from the main table as:-
Collapse
select top 0* into employee_test1_temp from employee_test1
Step2 : Insert the result of the GROUP BY query into the temporary table as:-
Collapse
insert into employee_test1_temp
select Emp_ID,Emp_name,Emp_Sal
from employee_test1
group by Emp_ID,Emp_name,Emp_Sal
Step3: Truncate the original table as:-
Collapse
truncate table employee_test1
Step4: Fill the original table with the rows of the temporary table as:-
Collapse
insert into employee_test1
select * from employee_test1_temp
Now, the duplicate rows from the main table have been removed.
Collapse
select * from employee_test1
gives the result as:-
Collapse
Emp_ID Emp_name Emp_Sal
1 Anees 1000
2 Rick 1200
3 John 1100
4 Stephen 1300
5 Maria 1400
6 Tim 1150
(b) Without using a temporary table
Collapse
;with T as
(
select * , row_number() over (partition by Emp_ID order by Emp_ID) as rank
from employee_test1
)
delete
from T
where rank > 1
The result is as:-
Collapse
Emp_ID Emp_name Emp_Sal
1 Anees 1000
2 Rick 1200
3 John 1100
4 Stephen 1300
5 Maria 1400
6 Tim 1150
