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 find all table names on database in MySQL and SQL Server Examples

How do you find names of all tables in a database is a recent  SQL interview questions asked to one of my friend. There are many ways to find all table names form any database like MySQL and SQL Server. You can get table names either from INFORMATION_SCHEMA or sys.tables based upon whether you are using MySQL or Sql Server database. This is not a popular question like when to use truncate and delete or correlated vs noncorrelated subquery which you can expect almost all candidate prepare well but this is quite common if you are working on any database e.g. MySQL. In this SQL tutorial we will see examples of getting names of all tables from MySQL and SQL Server database. In MySQL there are two ways to find names of all tables, either by using "show" keyword or  by query INFORMATION_SCHEMA. In  case of SQL Server or MSSQL, You can either use sys.tables or INFORMATION_SCHEMA to get all table names for a database. By the way if you are new in MySQL server and exploring it , you may find this list of frequently used MySQL server commands handy.

Read more »

Top Most What is Referential Integrity in Database or SQL - MySQL Example Tutorial

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 »

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 Database Transaction Tutorial in SQL with Example for Beginners

Database transaction is an important concept to understand while working in database and SQL. Transaction in database is required to protect data and keep it consistent when multiple users access the database at same time.  In this database transaction tutorial we will learn what is transaction in database, why do you need transaction in database, ACID properties of database transaction and an example of database transaction along with commit and rollback.   Almost all vendors like Oracle, MySQL, SQL Server or Sybase provide transaction facility though MySQL only provide it for certain storage engine like InnoDB and BDB and not for MyISAM.

Read more »

Top Most How to find second highest or maximum salary of Employee in SQL - Interview question

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 tableand 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 »

Top Most How to join three tables in SQL query – MySQL Example

Three table JOIN Example SQL

Joining three tables in single SQL query can be very tricky if you are not good with concept of SQL Join. SQL Joins have always been tricky not only for new programmers but for many others,  who are in programmingand 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 subqueryor JOIN . Most of times we only join two tables like Employee and Department but some time you may require to join more than two tables and a popular case is joining three tables in SQL. In case of joining three tables table 1 relates to table 2 and than 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 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 is always been some questions from Join like Difference between INNER and OUTER JOIN,  SQL query with JOIN e.g. Employee Departmentrelationship and  Difference between LEFT and RIGHT OUTER JOIN etc. In short this is one of the most important topic in SQL both from experience and interview point of view.

Read more »

Top Most SQL query to copy, duplicate or backup table in MySQL, Oracle and PostgreSQL database

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 »

Top Most How to use truncate and delete command in SQL >> Tutorial and Example

Delete and truncate command in SQL
Truncate and delete in SQL are two commands which is used to remove or delete data from table. Though quite basic in nature both sql commands can create lot of trouble until you are familiar with details before using it. Truncate and delete are not just important to understand perspective but also a very popular interview topic which in my opinion a definite worthy topic. What makes them tricky is amount of data. Since most of Electronic trading system stores large amount of transactional data and some even maintain historical data, good understanding of delete and truncate command is required to effectively work on those environment.I have still seen people firing delete command just to empty a table with millions of records which eventually lock the whole table for doing anything and take ages to complete or Simply blew log segment or hang the machine.

Read more »

Top Most How to create and call stored procedure in MySQL with IN and OUT parameters

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 »

Top Most 10 Example Queries of SQL Select Command

Select command in SQL is one of the most powerful and heavily used commands. This is I guess the first command any one learn in SQL even before CREATE which is used to create table in SQL. SELECT is used in SQL to fetch records from database tables and you can do a lot many things using Select. For example you can select all records, you can select few records based on condition specified in WHERE clause, select all columns using wild card (*) or only selecting few columns by explicitly declaring them in query.

Read more »

Top Most How to find duplicate records in a table on database - SQL tips

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 »

Top Most Difference between LEFT and RIGHT OUTER Joins in SQL - MySQL Join example

There are two kinds of OUTER joins in SQL, LEFT OUTER join and RIGHT OUTER join. Main difference between RIGHT OUTER join and LEFT OUTER join, as there name suggest, is inclusion of non matched rows. Sine INNER join only include matching rows, where value of joining column is same, in final result set, but OUTER join extends that functionality and also include unmatched rows in final result. LEFT outer join includes unmatched rows from table written on left of join predicate. On the other hand RIGHT OUTER join, along with all matching rows, includes unmatched rows from right side of table. In short result of LEFT outer join is INNER JOIN + unmatched rows from LEFT table and RIGHT OUTER join is INNER JOIN + unmatched rows from right hand side table. Similar to difference between INNER join and OUTER join, difference between LEFT and RIGHT OUTER JOIN can be better understand by a simple example, which we will see in next section. By the way joins are very popular in SQL interviews, and along with classic questions like finding second highest salary of employee,Inner join vs outer join or left outer join vs right outer join is commonly asked.

Read more »

Top Most SubQuery Example in SQL – Correlated vs Noncorrelated

SubQuery in SQL is a query inside another query. Some time to get a particular information from database you may need to fire two separate sql queries, subQuery is a way to combine or join them in single query. SQL query which is on inner part of main query is called inner query while outer part of main query is called outer query. for example in below sql query

SELECT name FROM City WHERE pincode IN (SELECT pincode FROM pin WHERE zone='west')

section not highlighted is OUTER query while section highlighted with grey is INNER query. In this SQL tutorial we will see both Correlated and non correlated sub-query and there examples, some differences between correlated and noncorrelated subqueries and finally subquery vs join which is classic debatable topic in SQL. By the way this SQL tutorial is next in series of SQL and database articles in Javarevisited like truncate vs delete and 10 examples of  SELECT queries. If you are new here then you may find those examples interesting.

Read more »

Top Most MySql Tutorial : mysqldump utility in mysql

The mysqldump client is a backup program o It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both. However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.

Read more »

Top Most MySQL tutorial and commands part 3

Hi Guys , here are some more mysql commands which is useful in day 2 day work.

Method for converting current time stamp to date:
--------------------------------------------------------------
select from_unixtime(left(1201159475416, 10));
this  method is used to convert the timestamp to the date time format in mysql ,left() method will return 10 char from the specified string  if we store time stamp value in millisecond.

Read more »

Top Most MySQL tutorial and commands part 2

Here are some more mysql commands from which is very useful for anyone working with mysql database.
this are very useful for application developer which is going to use mysql database for there applications.


Increasing no of connections for mysql
---------------------------------------------
You can increase this value in main config file (e.g., /etc/my.cnf) using this syntax:

[mysqld]
set-variable=max_connections=250

Read more »

Top Most MySQL tutorial and commands Part 1

Here is first set of my basic mysql commands which I have used in my day to day life while working with mysql database.

For Checking Wheather MySQL Server is running or not
-------------------------------------------------------------
ps -auxwww | grep mysql


Starting mysql
-----------------------
go to mysql installation directory and execute below command

Read more »