Tampilkan postingan dengan label SQL Interview Questions. Tampilkan semua postingan
Tampilkan postingan dengan label SQL Interview Questions. Tampilkan semua postingan

Jumat, 21 Oktober 2016

One of the common question on Microsoft SQL Server interview is, what is the difference between GETDATE(), SYSDATETIME(), and GETUTCDATE(). Even though all three SQL Sever function returns the current date time in SQL Server, there are some subtle differences between them. The main difference between GETDATE() and SYSDATETIME() is that GETDATE returns current date and time as DATETIME but SYSDATETIME returns a DATETIME2 value, which is more precise. The difference between GETDATE() and GETUTCDATE() is in timezone, the GETDATE() function return current date and time in the local timezone, the timezone where your database server is running, but GETUTCDATE() return current time and date in UTC (Universal Time Coordinate) or GMT timezone.
Read more �

Rabu, 05 Oktober 2016

It's tricky to use dates in SQL server query, especially if you don't have good knowledge of how DateTime type works in SQL server. For example, one of the frequently asked SQL queries on the interview is to "select all rows where the date is 20151007?" How would you do that? Does following SQL Query will work correctly

select * from table where date = '20151007'

It may or may not, it entirely depends on upon data in your table. When you only provide date part of a DateTime variable, it uses '00:00:00.000' for time part.
Read more �

Sabtu, 23 Juli 2016

Though all three are ranking functions in SQL, also known as window function in Microsoft SQL Server, the difference between rank(), dense_rank(), and row_number() comes when you have ties on ranking i.e. duplicate records. For example, if you are ranking employees by their salaries then what would be the rank of two employees of same salaries? It depends on upon which ranking function you are using e.g. row_number, rank, or dense_rank. The row_number() function always generates a unique ranking even with duplicate records i.e. if the ORDER BY clause cannot distinguish between two rows, it will still give them different rankings, though which record will come earlier or later is decided randomly e.g. in our example two employees Shane and Rick have the same salary and has row number 4 and 5, this is random, if you run again, Shane might come 5th.
Read more �

Kamis, 07 Juli 2016

There are a couple of ways to remove duplicate rows from a table in SQL e.g. you can use a temp tables or a window function like row_number() to generate artificial ranking and remove the duplicates. By using a temp table, you can first copy all unique records into a temp table and then delete all data from the original table and then copy unique records again to the original table. This way, all duplicate rows will be removed, but with large tables, this solution will require additional space of the same magnitude of the original table. The second approach doesn't require extra space as it removes duplicate rows directly from the table. It uses a ranking function like row_number() to assign a row number to each row.
Read more �

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 �

Kamis, 20 Desember 2012

Many times we need to create backup or copy of tables in database like MySQL, Oracle or PostgreSQL while modifying table schema like adding new columns, modifying column or dropping columns. Since its always best to have a backup of table which can be used in any event. I was looking for an easy way to create exact copy or duplicate tables which must be same in schema as well as in data, similar to creating copy of folder. Luckily there is an easy SQL query "CREATE table table_name AS" which allows you to create exact copy of table by executing just one SQL query. Yes, you read it correctly, no tool is required to create backup of table you just need to execute an SQL query. This is simply awesome given its importance and best part of this SQL query is that it works in almost all the database. I have tested it in MySQL and Oracle but t it should work perfectly find in other databases like PostgreSQL, SQL Server and DB2 as well. This SQL query tip is in continuation of my earlier SQL query examples like SQL query to find duplicate rows in a table and SQL query to join three tables in MySQL.
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.