Difference between char, varchar, nchar and nvarchar data types in SQL Server? Example

What is the difference between char and varchar in SQL, followed by nchar and nvarchar, is one of the popular SQL interview questions, and surprisingly not every programmer knows this basic difference. If you go with the name, which you should, then you can figure out that char is a fixed-length data type while varchar should be a variable-length data type. Though all char, varchar, nchar, and nvarchar are used to store text or String data there are subtle differences between them. As I said char is fixed length, which means a variable or a column like Zipcode char(10) will take only 10 bytes to store data, including space.

How to use EXISTS and NOT Exists in SQL? Example Query and Tutorial

Hello Guys, you might have heard about how useful the EXISTS clause is helpful in writing sophisticated queries. Still, at the same time, I have also seen that many programmers struggle to understand and use EXISTS and NOT EXISTS clauses while writing SQL queries.  If you are one of them, then you have come to the right place. Today you will learn how to use the EXISTS clause in SQL by picking up a real-world example and an excellent SQL exercise from the LeetCode. Suppose that a website contains two tables, the Customers table, and the Orders table. Can you write an SQL query to find all customers who have never ordered anything?

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. The main difference between RIGHT OUTER joins and LEFT OUTER join, as their name suggests, is the inclusion of non-matched rows. Sine INNER join only include matching rows, where the value of the joining column is the same, in the final result set, but OUTER join extends that functionality and also include unmatched rows in the final result. LEFT outer join includes unmatched rows from the table written on the left of the join predicate. 

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

How to find duplicate records in a table is a popular SQL interview questions which have 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 an interview where some questions on database and SQL are expected. In order to find duplicate records in the database table you need to confirm the definition of duplicates, for example in below contact table which is suppose to store name and phone number of the contact, a record is considered to be duplicate if both name and phone number is the same but unique if either of them varies.

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

How to find the second highest or second maximum salary of an Employee is one of the most frequently asked SQL interview questions 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 the 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.

Difference between Truncate and Delete in SQL? Example

Truncate and delete in SQL are two commands which are used to remove or delete data from a table. Though quite basic in nature both SQL commands can create a lot of trouble until you are familiar with details before using it. The difference between Truncate and delete are not just important to understand perspective but also a very popular SQL interview topic which in my opinion a definite worthy topic. What makes them tricky is the amount of data. Since most Electronic trading system stores, large amounts of transactional data, and some even maintain historical data, a good understanding of delete and the truncate command is required to effectively work in that environment.

Difference between Clustered Index and Non Clustered Index in SQL - Example

In the SQL Server database, there are mainly two types of indexes, Clustered index, and the Non-Clustered index and difference between Clustered and Non-Clustered index are very important from an SQL performance perspective. It is also one of the most common SQL Interview questions, similar to the difference between truncate and delete,  primary key or unique key, or correlated vs non-correlated subquery. For those, who are not aware of the benefits of Index or why we use an index in the database, they help in making your SELECT query faster. 

Difference between Primary key vs Foreign key in table – SQL Tutorial Example

The main difference between the Primary key and the Foreign key in a table is that it’s the same column that behaves as the primary key in the parent table and as a foreign key in a child table. For example in the Customer and Order relationship, customer_id is the primary key in the Customer table but a foreign key in the Order table. By the way, what is a foreign key in a table and the 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 the purpose of the foreign key in any table. 

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

Referential Integrity is a set of constraints applied to foreign keys which prevents entering a row in the child table (where you have the foreign key) for which you don't have any corresponding row in the parent table i.e. entering NULL or invalid foreign keys. Referential Integrity prevents your table from having incorrect or incomplete relationships 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 a relational database.

How to Add, Modify and Drop Column with Default Value, NOT NULL Constraint – MySQL Example

How to add a column in the existing table with a default value is another popular SQL interview question asked for Junior level programming job interviews. Though syntax of SQL query to add a column with default value varies a little bit from database to database, it has always been performed using ALTER keyword of ANSI SQL. Adding columns in the existing table in the MySQL database is rather easy and straight forward and we will see an example of SQL query for MySQL database which adds a column with a default value. You can also provide constraints like NULL or NOT NULL while adding a new column in the table.

How to Create Auto Incremented Identity Column in SQL Server, MySQL, and Oracle? Example

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, Oracle or Sybase supports auto-incremented identity columns but in different ways like Oracle provides a SEQUENCE object which can be used to generate automatic numbers, Microsoft SQL Server up to 2008 version provides IDENTITY() functions for a 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.

Difference between WHERE vs HAVING clause in SQL - GROUP BY Comparison with Example

What is the difference between WHERE and  HAVING clause in SQL is one of the most popular questions asked on SQL and database interviews, especially to beginners? Since programming jobs, required more than one skill, it’s quite common to see a couple of SQL Interview questions in Java and .NET interviews. By the way unlike any other question, not many Java programmers or dot net developers, who are supposed to have knowledge of basic SQL, fail to answer this question. Though almost half of the programmer says that WHERE is used in any SELECT query, while HAVING clause is only used in SELECT queries, which contains aggregate function or group by clause, which is correct.

How to Split String in SQL Server and Sybase? Example Tutorial

Sometimes we need to split a long comma-separated String in a Stored procedure e.g. Sybase or SQL Server stored procedures. It's quite common to pass comma delimited or delimiter separated String as an input parameter to Stored procedure and then later split comma separated String into multiple values inside stored proc. This is not just the case of the input parameter but you can also have a 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 a string based on delimiter just like in the Java string split method. 

How to join three tables in SQL query – MySQL Example

Three tables JOIN Example SQL

Joining three tables in a 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.

SQL Query to Find All Table Names on a Database With MySQL and SQL Server Examples

How do you find names of all tables in a database is a recent  SQL interview question asked to one of my friends. There are many ways to find all table names from 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 candidates to prepare well but this is quite common if you are working on any database like MySQL.

How to write SubQuery in SQL? Correlated vs Noncorrelated Sub Query Example

Hello guys, if you are struggling to write a sub query in SQL and need some guidance then you have come to the right place. In this article, I will not only teach you what is subquery, how it works, and which problem it solves but also teach you how to write both correlated and non-correlated sub-queries with real-world examples. SubQuery in SQL is a query inside another query. Some time to get particular information from a database you may need to fire two separate SQL queries, subQuery is a way to combine or join them in a single query. SQL query which is on the inner part of the main query is called the inner query while the outer part of the main query is called the outer query.