Seputar Ilmu

Did you ever face a situation where some of your innocuous looking action has cause production issue and that too big time? Well, I hope you have not because it's certainly, not a pleasant experience. One of such innocuous looking action is running SQL queries on production databases. I had that in past very early in my career where I removed some config as duplicates only to find after a week that it stopped publishing messages to one of the downstream. When you work in complex systems which has so many components, millions of lines of code, thousands of configuration and many databases with hundreds of tables, you have to be really careful with anything you do. Often there is on the real way to perform a production-like testing, hence the best bet is to keep your change as much isolated and limited as possible. Read more �

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 �

The GETDATE is one of the most popular built-in methods of Microsoft SQL Server, but unlike its name suggest, it doesn't return just date, instead it returns date with time information e.g. 2015-07-31 15:42:54.470 , quite similar to our own java.util.Date from Java world. If you want just date like 2015-07-31 , or just time like 15:42:54.470 then you need to either CAST or CONVERT output of GETDATE function into DATE or TIME data type. From SQL Server 2008 onward, apart from DATETIME , which is used to store both date and time, You also have a DATE data type to store date without time e.g. 2015-07-31 , and a TIME data type to store time without any date information e.g. 15:42:54.470 . Since GETDATE() function return a DATETIME value, You have to use either CAST or CONVERT method to convert a DATETIME value to DATE or TIME in SQL Server. Read more �

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 �

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 �

Since a primary key is nothing but a constraint you can use ALTER clause of SQL to add a primary key into existing table. Though it's an SQL and database best practice to always have a primary key in a table, many times you will find tables which don't have a primary key. Sometimes, this is due to lack of a column which is both NOT NULL and UNIQUE (constraint require to be a primary key ) but other times purely due to lack of knowledge or lack of energy. If you don't have a column which can serve as primary key you can use identity columns for that purpose. Alternatively, you can also combine multiple columns to create a composite primary keys e.g. you can combine firstname and lastname to create a primary key name etc. Read more �

Cursor in a database is used to retrieve data from the result set, mostly one row at a time. You can use Cursor to update records and perform an operation on a row by row. Given its importance on SQL and Stored procedure, Cursor is also very popular on SQL interviews . One of the popular SQL question on Cursor is close vs deallocate. Since both of them sounds to close the cursor, once the job is done, What is a real difference between close and deallocate of Cursor in SQL? Well, there is some subtle difference e.g. closing a cursor doesn't change its definition. In Sybase particular, you can reopen a closed cursor and when you reopen it, it creates a new cursor based upon the same SELECT query . On the other hand, deallocation a cursor frees up all the resources associated with the cursor, including cursor name. You just cannot reuse a cursor name by closing it, you need to deallocate it. By the way, if you deallocate an open cursor, it's get closed automatically. Similarly te...

Sometimes, you need the result of SQL SELECT clause as a comma separated String e.g. if you are outputting ids of white-listed products. By default, the SELECT command will print each row in one line and you get a column of names or ids. If you need a comma separated String then you probably need to use a text editor like Notepad++, Edit Plus, or a tool like Microsoft Excel to convert that column of values into a big CSV String. I often use a text editor like edit plus to replace the \n to comma (,) to create such CSV string because it support regular expression based find and replace operation. Suddenly, a thought came to my mind, why not do this in T-SQL in the SQL Server. It would not only save the time but also give you options like create a sorted list of comma separated String using order by clause. It's a nice trick and useful in many scenarios especially if you are working with data drive application. Read more �

You can increase the length of a VARCHAR column without losing existing data in SQL Server. All you need to do is that execute following ALTER TABLE statements. Though, you need to specify NULL or NOT NULL constraint explicitly, depending upon your data. Here is the SQL command you can use to increase the length of a VARCHAR column in SQL Server: ALTER TABLE Books ALTER COLUMN title VARCHAR (432) This command increases the length of title column of Books table to 432 characters. You can use the same command to increase the length of CHAR, NCHAR or NVARCHAR columns as well. Read more >>

It's a little bit tricky to delete from a table while using any type of JOIN in SQL e.g. Inner Join, Left Outer Join , or Right Outer Join. The obvious syntax doesn't work as shown below: delete from #Expired e INNER JOIN Deals d ON e.DealId = d.DealId Where d.Brand = 'Sony' here I have a table with a list of expired deals which I want to delete from the Deals tables, but only for Sony. When I run this SQL Query in Microsoft SQL Server 2008, it gave me following error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'e'. Now, I am puzzled, how to delete from a table while using INNER JOIN in SQL Server? Read more �

It's hard to remember exact syntax of, how to create stored procedure in MySQL, until you are creating and working on stored procedure frequently, simply because syntax is not a one liner. You need to remember exact syntax, if you are using MySQL database from command line. What help in this case is, quick examples. In last couple of MySQL tutorial we have seen How to find second highest salary and How to join 3 tables in one query ; In this MySQL tutorial we will see couple of examples of creating stored procedure and calling stored procedure using IN and OUT parameters . All these MySQL examples are simple and help you to understand syntax of creating stored procedure with parameters in MySQL. These examples are tested in MySQL 5.5 database. We will also use following employee table to create and test these stored procedures : Read more �

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 �

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 e...

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 database to 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 Comparable bec ause of limite...

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 ye...

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 2 nd and Nth maximum salary in SQL . Read more �

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 . T here 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 �

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 execu ting 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 m...

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 nu...

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 whi...