Top Most Difference between Primary key vs Foreign key in table – SQL database tutorial

Main difference between Primary key and Foreign key in a table is that, it’s the same column which behaves as primary key in parent table and as foreign key in child table. For example in Customer and Orderrelationship, customer_id is primary key in Customer table but foreign key in Order table. By the way what is 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 about what is foreign key in SQL and purpose of foreign key in any table. By the way this is the third article related to 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 »

Top Most SQL query to add, modify and drop column in table with default value NOT NULL constraint – MySQL database

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.

Read more »

Top Most How to create auto incremented identity column in SQL Server, MySQL, Sybase and Oracle ?

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 »

Top Most Sybase and SQL Server PATINDEX CHARINDEX Example to split String in Stored procedure

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 »

Top Most Migrating SQL Query from Oracle to SQL Server 2008 or Sybase

Oracle and Microsoft SQL Server are very different than each other and if you are migrating SQL queries or database, tables from Oracle 11g database to Microsoft 2008 SQL server than you are bound to face some issues. Main reason of these porting issues are features, which are supported and exists in Oracle database, but not available in Microsoft SQL Server 2008 like SEQUENCE, Order by clause in sub queries and derived tables, derived table without name etc. I am sure there are few more and it will surface based upon different database objects you are using in your tables and queries. On other hand SQL Engine for SQL Server and Sybase are very much similar, at least syntactically, and if you are migrating queries from SQL Server to Sybase you can do that without much hassle, of course there will be slight changes but not as much like Oracle. So if you are migrating from Oracle to Sybase or SQL Server its most likely same job and you should first start with either SQL Server or Sybase ASE and than later migrate them from each other. In this Oracle and SQL Server tutorial we will see couple of examples, where Oracle and SQL Server are different and how to change those SQL queries so that it can run on Microsoft SQL Server. By the way I have also written couple of post on queries like 10 ways to use SQL SELECT queriesand Don’t delete, truncate it. If you like reading more on SQL queries than those are for you.

Read more »