Interview Questions - SQL

What is Stored Procedure?
A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and
improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed;the DBMS automatically fires the trigger as a result of a data modification to the associated table.
Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored
procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

What is View?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes, they are just used to speed
up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.
Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

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 is difference between DELETE & TRUNCATE commands?
Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

What types of Joins are possible with Sql Server?
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.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Foreign keys are both a method of ensuring data integrity and a manifestation of the relationship between tables.

What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers,the value of this cannot be controled. Identity/GUID columns do not need to be indexed.

What is Self Join?
This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same. A self join is rather unique in that it involves a relationship with only one table. The common example is when company have a hierarchal reporting structure whereby one member of staff reports to another.

What is Cross Join?
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

List few advantages of Stored Procedure.· Stored procedure can reduced network traffic and latency, boosting application performance.
· Stored procedure execution plans can be reused, staying cached in SQL Server's memory,reducing server overhead.
· Stored procedures help promote code reuse.
· Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
· Stored procedures provide better security to your data.

Know how to connect Microsoft Access Database using JDBC..!!!

Interested in connecting to Ms Access using JDBC..!!! The following post will guide you achieve this.

Microsoft has developed a data access method called ODBC, and MS Access databases understand this method. We cannot make a connection directly to an ODBC data source from Java, but Sun has provided a bridge from JDBC to ODBC. This bridge gives the DriverManager the understanding of how to communicate with an ODBC (ie a MS Access) data source.

As we know that we can get the connection object of java.sql.Connection with the use of DriverManager class. We tell the DriverManager what type of driver to use to handle the connections to databases, and from there, ask it to give us a connection to a particular database of that type.

So the first thing we'll do is set up our DriverManager and let it know that we want to communicate with ODBC data sources via the JDBC:ODBC bridge. We do this by calling the static forName() method of the Class class. Here is an entire program that accomplishes what we're after:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Now their are two ways to get Connection Ms Acess :

1)Get a connection by direct access: I will not suggest you to go for this approach, but still for your understanding below is the code for this approach. Here get a connection is to go directly after the MS Access database file

try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String filename = "c:/java/customerInfo.mdb";
String database = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=";
database+= filename.trim() + ";DriverID=22;READONLY=true}"; // add on to the end
// now we can get the connection from the DriverManager
Connection con = DriverManager.getConnection( database ,"","");
}

catch (Exception e) {
System.out.println("Error: " + e);
}

2)Set up a DSN and get a connection through that: This approach is the recommended one.

Microsoft has provided a method to build a quick Jet-Engine database on your computer without the need for any specific database software (it comes standard with Windows). Using this method, we can even create a blank Microsoft Access database without having MS Access installed!

As we learned earlier, MS Access data bases can be connected to via ODBC. Instead of accessing the database directly, we can access it via a Data Source Name (DSN). Here's how to set up a DSN on your system:

1. Open Windows' ODBC Data Source Administrator as follows:
* In Windows 95, 98, or NT, choose Start > Settings > Control Panel, then double-click the ODBC Data Sources icon. Depending on your system, the icon could also be called ODBC or 32bit ODBC.
* In Windows 2000, choose Start > Settings > Control Panel > Administrative Tools > Data Sources.
2. In the ODBC Data Source Administrator dialog box, click the System DSN tab.
3. Click Add to add a new DSN to the list.
4. Scroll down and select the Microsoft Access (.MDB) driver
5. Type in the name "mdbTEST" (no quotes, but leave the cases the same) for the Data Source Name
6. Click CREATE and select a file to save the database to (I chose "d:\java\mdbTEST.mdb") - this creates a new blank MS Access database!
7. Click "ok" all the way out

Now our data source is done! Here's a complete program showing how to access your new DSN data source:

String dataSourceName = "customerInfo";
String dbURL = "jdbc:odbc:" + dataSourceName;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(dbURL, "","");
}
catch (Exception err) {
System.out.println( "Error: " + err );
}


Now you have established connection with your database successfully.

Know how to retrieve components of a date using SQL...!!!

You can retrieve different components of date from timestamp using SQL functionDATEPART. You can extract date components like YEAR, MONTH, DAY, HOUR, MINUTE, SECOND using this Datepart function. The syntax for using this function is

datepart(date component to retrieve, date)

Example: Suppose you want to retrieve year component from customer table's DOB column having timestamp type, the query will be like

Select datepart(year, DOB) from customer where cust_name='xyz'

ResultSet:- 1984

Same way you can use:
datepart(day, DOB) for day
datepart(minute, DOB) for minute
datepart(month, DOB) for month
datepart(hour, DOB) for HOUR
datepart(second, DOB) for second

The above function works fine with SQLServer but with Oracle and MySql I guess it wont work. In that case you can use extract(year from DOB).

Know how to get current date timestamp in SQL..!!!

In my recent project i was looking for a built-In SQL function that returns me the current date as timestamp. As we know Date values are stored in Tables as timestamp.A SQL timestamp is a record containing date/time data, such as the month, day, year, hour, and minutes/seconds.
SQL offers a built-in function called GETDATE() that returns the current date in the form of a SQL timestamp.
Hence to get current date run the below query:
SELECT GETDATE();

and the timestamp result will be
2009-05-20 01:23:45.150000.(Current Date)