Tampilkan postingan dengan label database interview questions. Tampilkan semua postingan
Tampilkan postingan dengan label database interview questions. Tampilkan semua postingan

Senin, 31 Desember 2012

These are some interview question and answer asked during my recent interview. Oracle interview questions are very important during any programming job interview. Interviewer always want to check how comfortable we are with any database either we go for Java developer position or C, C++ programmer position .So here I have discussed some basic question related with oracle database. Apart from these questions which is very specific to Oracle database you may find some general questions related to database fundamentals and SQL e.g. Difference between correlated and noncorrelated subquery in database or truncate vs delete in SQL etc. Some of the most important topics in Oracle Interview questions are SQL, date, inbuilt function, stored procedure and less used features like cursor, trigger and views. These questions also gives an idea about formats of questions asked during Oracle Interview.
Read more �

Sabtu, 29 Desember 2012

The main difference between Primary key and Foreign key in a table is that it�s the same column which behaves as primary key in the parent table and as a foreign key in a child table. For example in Customer and Order relationship, customer_id is the primary key in Customer table but a foreign key in Order table. By the way, what is a foreign key in a table and difference between Primary and Foreign key are some of the popular SQL interview questions, much like truncate vs delete in SQL or difference between correlated and noncorrelated subquery? We have been learning key SQL concepts along with these frequently asked SQL questions and in this SQL tutorial, we will discuss what is a foreign key in SQL and purpose of the foreign key in any table. By the way, this is the third article related to a primary key in SQL, other being difference between primary and unique key and How to find second highest salary in SQL. If you are preparing for any technical job interview where you expect some SQL questions, check out these questions, they are worth preparing.
Read more �

Jumat, 28 Desember 2012

Referential Integrity is set of constraints applied to foreign key which prevents entering a row in child table (where you have foreign key) for which you don't have any corresponding row in parent table i.e. entering NULL or invalid foreign keys. Referential Integrity prevents your table from having incorrect or incomplete relationship e.g. If you have two tables Order and Customer where Customer is parent table with primary key customer_id and Order is child table with foreign key customer_id. Since as per business rules you can not have an Order without a Customer and this business rule can be implemented using referential integrity in SQL on relational database. Referential Integrity will cause failure on any INSERT or UPDATE SQL statement changing value of customer_id in child table, If value of customer_id is not present in Customer table. By the way What is Referential Integrity in SQL is also an important SQL question similar to finding second highest salary in SQL or difference between truncate and delete and should be prepared well before going for any job interview, where knowledge of SQL is one of the requirement.
Read more �

Kamis, 27 Desember 2012

Many time we need SQL query which return data page by page i.e. 30 or 40 records at a time, which can be specified as page size. In fact Database pagination is common requirement of Java web developers, especially dealing with largest data sets. In this article we will see how to query Oracle 10g database for pagination or how to retrieve data using paging from Oracle. Many Java programmer also use display tag for paging in JSP which supports both internal and external paging. In case of internal paging all data is loaded in to memory in one shot and display tag handles pagination based upon page size but it only suitable for small data where you can afford those many objects in memory. If you have hundreds of row to display than its best to use external pagination by asking databaseto do pagination. In pagination, ordering is another important aspect which can not be missed. It�s virtually impossible to sort large collection in Java using Comparator or Comparablebecause of limited memory available to Java program, sorting data in database using ORDER BY clause itself is good solution while doing paging in web application.
Read more �

Rabu, 26 Desember 2012

How to add column in existing table with default value is another popular SQL interview question asked for Junior level programming job interviews. Though syntax of SQL query to add column with default value varies little bit from database to database, it always been performed using ALTER keyword of ANSI SQL. Adding column in existing table in MySQL database is rather easy and straight forward and we will see example of SQL query for MySQL database which adds a column with default value. You can also provide constraints like NULL or NOT NULL while adding new column in table. In this SQL tutorial we are adding third column in a table called Contacts which contains name and phone of contacts. Now we want to add another column email with default value "abc@yahoo.com". We will use ALTER command in SQL to do that. By the way this is next in our SQL tutorials e.g. How to join three tables in SQL and SQL query to find duplicate records in table. If you haven't read them yet, then you may find them useful.

Automatic incremented ID, Sequence or Identity columns are those columns in any table whose value is automatically incremented by database based upon predefined rule. Almost all databases e.g. Microsoft SQL Server, MySQL, Oracleor Sybase supports auto incremented identity columns but in different ways like Oracle provides SEQUENCE object which can be used to generate automatic numbers, Microsoft SQL Server upto 2008 version provides IDENTITY() functions for similar purpose. Sybase also has IDENTITY function but little different than SQL Server and MySQL uses auto_incremented keyword to make any numeric column auto incremented. As first normal form advised about primary keys which is used to uniquely identity row and if there is no natural column or combination of column exists to act as primary key, mostly database developer use auto incremented surrogate keys which is used to uniquely identify each row. In this SQL tutorial we will see how to generate auto incremented ID column in Microsoft SQL Server, Oracle 11g, MySQL and Sybase ASE Server. By the way this SQL article is continuation of my earlier post on SQL and database like difference between truncate and delete in SQL and Finding second highest salary in MySQL and SQL Server. If you haven't got chance to read them than I suggest they are worth looking.
Read more �

Selasa, 25 Desember 2012

Some time we need to split a long comma separated String in Stored procedure e.g. Sybase or SQL Server stored procedures. Its quite common to pass comma delimited or delimiter separated String as input parameter to Stored procedure and than later split comma separated String into multiple values inside stored proc. This is not just case of input parameter but you can also have comma separated string in any table data. Unfortunately there is no split() function in Sybase or SQL Server 2005 or 2008 which can directly split string based on delimiter just like in Java string split method. Fortunately Sybase Adaptive Server and Microsoft SQL server has functions like CHARINDEX and PATINDEX which can be used to split comma separated String. This is next on our SQL tutorials after seeing SQL query to find duplicate records in table and How to find 2nd and Nth maximum salary in SQL.
Read more �

Minggu, 23 Desember 2012

How to find second highest or second maximum salary of an Employee is one of the most frequently asked SQL interview question similar to finding duplicate records in table and when to use truncate vs delete. There are many ways to find second highest salary based upon which database you are using as different database provides different feature which can be used to find second maximum or Nth maximum salary of employee. Well this question can also be generalized with other scenario like finding second maximum age etc. In this SQL tutorial we will see different example of SELECT SQL query to find second highest salary independent of databases or you may call in ANSI SQL and other SQL queries which uses database specific feature to find second maximum salary.
Read more �

Jumat, 14 Desember 2012

How to find duplicate records in table is a popular SQL interview question which has been asked as many times as difference between truncate and delete in SQL or finding second highest salary of employee. Both of these SQL queries are must know for any one who is appearing on any programming interview where some questions on database and SQL are expected. In order to find duplicate records in database table you need to confirm definition of duplicates, for example in below contact table which is suppose to store name and phone number of contact, a record is considered to be duplicate if both name and phone number is same but unique if either of them varies. Problem of duplicates in database arise when you don't have a primary key or unique key on database and that's why its recommended to have a key column in table. Anyway its easy to find duplicate records in table by using group by clause of ANSI SQL. Group by clause is used to group data based upon any column or a number of columns. Here in order to locate duplicate records we need to use group by clause on both name and phone as shown in second SQL SELECT query example. You can see in first query that it listed Ruby as duplicate record even though both Ruby have different phone number because we only performed group by on name. Once you have grouped data you can filter out duplicates by using having clause. Having clause is counter part of where clause for aggregation queries. Just remember to provide temporary name to count() data in order to use them in having clause.
Read more �

Kamis, 22 November 2012

Three table JOIN Example SQL

Joining three tables in single SQL query can be very tricky if you are not good with the concept of SQL Join. SQL Joins have always been tricky not only for new programmers but for many others, who are in programming and SQL for more than 2 to 3 years. There are enough to confuse someone on SQL JOIN ranging from various types of SQL JOIN like INNER and OUTER join, LEFT and RIGHT outer join, CROSS join etc. Between all of these fundamentals, What is most important about Join is, combining multiple tables. If you need data from multiple tables in one SELECT query you need to use either subquery or JOIN. Most of the times we only join two tables like Employee and Department but sometimes you may require joining more than two tables and a popular case is joining three tables in SQL.

In the case of joining three tables table, 1 relates to table 2 and then table 2 relates to table 3. If you look at closely you find that table 2 is a joining table which contains primary key from both table 1 and table 2. As I said it can be extremely confusing to understand join of three or more tables.

I have found that understanding table relationship as the primary key and foreign key helps to alleviate confusion than the classical matching row paradigm.

SQL Join is also a very popular topic in SQL interviews and there are always been some questions from Joins, like the difference between INNER and OUTER JOIN, SQL query with JOIN e.g. Employee Department relationship and Difference between LEFT and RIGHT OUTER JOIN etc. In short this is one of the most important topics in SQL both from experience and interview point of view.