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 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 Find Duplicate values in SQL? GROUP BY and HAVING Query Example Tutorial

Hello guys, if you are wondering how to find duplicate values in a table then you can use the GROUP BY and HAVING clause in SQL. Using group by you can create groups and if your group has more than 1 element it means it's kind of duplicate. For example, you need to write a SQL query to find all duplicate emails in a table named Person. This is a popular SQL Query interview question as well as a Leetcode problem. You can see that email a@b.com is a duplicate email as it appears twice in the table. You need to write a query to find all duplicate values, I mean emails in this case. 

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+

For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

Note: All emails are in lowercase.

Difference between CAST, CONVERT, and PARSE function in Microsoft SQL Server

Though all three, CAST, CONVERT, and PARSE are used to convert one data type into another in SQL Server, there are some subtle differences between them. The  CAST method accepts just two parameters, expression, and target type, but CONVERT() also takes a third parameter representing the format of conversion, which is supported for some conversions, like between character strings and date-time values. For example, CONVERT(DATE, '2/7/2015', 101) converts the character string '2/7/2015' to DATE using DATE format 101, representing United States standard.

Top 6 SQL Query Interview Questions for Programmers and Data Scientists - Best of Lot

SQL, a short form of Structured Query Language is one of the essential skills in today's programming world. No matter whether you are a Java developer, C++ developer or Python developer, you must know how to write SQL queries. Every programming job interview has at least one or two questions that require you to write SQL queries for a given requirement and many developers struggle there. It's easy to answer theoretical questions like what is the difference between clustered and non-clustered index (see) or what is the difference between correlated and non-correlated subqueries (see), but when it comes time to actually write SQL queries to solve problems, it's not that easy, especially if you haven't done your homework and practice.

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. 

What is difference between SQL, T-SQL and PL/SQL? Answer

Today, we are going to see another common and interesting SQL interview question, what is the difference between SQL, T-SQL, and PL/SQL? It is also one of the most common doubts among SQL beginners. It's common for programmers to think that why there are many types of SQL languages, why not just single SQL across DB? etc. Well, let's first understand the difference between SQL, T-SQL, and PL/SQL, and then we will understand the need for these dialects. SQL is standard for querying, inserting, and modifying data in a relational database. It is categorized into DDL and DML and is powerful enough to create database objects e.g. table, view, stored procedure, and can perform CRUD operation (SELECT, INSERT, UPDATE, and DELETE) query.

LEFT JOIN Example - How to Join Two Tables in a SQL query - LeetCode Solution

Hello guys, when it comes to combining two tables in SQL, many programmers don't know that they can use the JOIN clause. In fact, JOIN is there to fetch data from multiple tables together. There are mainly two types of joins, INNER Join and OUTER join. On Inner join, only those records have matching values in both tables, while in Outer join, all records from one table are selected in addition to matching records from other tables. There are two kinds of Outer join in SQL, LEFT OUTER JOIN and RIGHT OUTER JOIN. Both are actually the same thing, which means you can get the same result by using either of the outer joins by changing the table's position from left to right.

SQL Self Join Example - SQL Query to Find Employees Earning More Than Managers - LeetCode Solution

Hello guys, are you looking for a simple example of how to use SELF JOIN in SQL? If yes, then you have come to the right place. This article will show you how to use Self join in solving interesting SQL problems from LeetCode. Along the way, you will also learn this useful SQL concept. So, what are you waiting for? Let's first check the problem, and then we'll write an SQL query using SELF Join to solve this problem.

Write an SQL Query to Find Employees Earning More Than Managers
The Employee table holds all employees, including their managers. Every employee has an Id, and there is also a column for the manager Id, as shown below:

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager because Henry's Manager is Max, who earns 90000, which is more than Henry's salary of 80000.

Difference between row_number(), rank() and dense_rank() window functions in SQL

Though all three are ranking functions in SQL, also known as a 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 the same salaries? It depends on which ranking function you are using like row_number, rank, or dense_rank.

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.

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 databases like MySQL, Oracle, or PostgreSQL while modifying table schema like adding new columns, modifying columns, or dropping columns. Since it's always best to have a backup of a table that can be used in any event. I was looking for an easy way to create an exact copy or duplicate tables which must be the same in the schema as well as in data, similar to creating a copy of the folder. Luckily there is an easy SQL query "CREATE table table_name AS" which allows you to create an exact copy of the table by executing just one SQL query. Yes, you read it correctly, no tool is required to create a backup of the table you just need to execute an SQL query.

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 Compare Date in SQL Server Query? Finding All Rows Between Two Dates

It's tricky to use dates in the SQL server query, especially if you don't have good knowledge of how DateTime type works in the 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 the 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 the time part.

4 Ways to find Nth highest salary in SQL - Oracle, MSSQL and MySQL

One of the most common SQL interview questions is to find the Nth highest salary of employees, where N could be 2, 3, 4 or anything e.g. find the second highest salary in SQL. Sometimes this question is also twisted as to find the nth minimum salary in SQL. Since many Programmers only know the easy way to solve this problem e.g. by using SQL IN clause, which doesn't scale well, they struggle to write the SQL query when the Interviewer keeps asking about the 4th highest, 5th highest and so on. In order to solve this problem effectively, you need to know about some key concepts like a correlated subquery, window functions like ROW_NUMER(), RANK(), and DENSE_RANK(), etc. Once you know the generic logic to solve this problem, you can tackle all those variations by yourself.

How to find Length of String in SQL Server? LEN() Function Example

One of the most common task while writing SQL queries or stored procedure is to find the length of String. Since most of the columns are VARCHAR, you often need to find the length before taking any action. In Java, you can find the length of String by using the length() method but how about SQL Server? How will you find the length of String in Microsoft SQL Server in general and Microsoft SQL Server 2016 in particular? Well, you can use the LEN() function to find the length of a String value in SQL Server, for example, LEN(emp_name) will give you the length of values stored in the column emp_name. This method exists from SQL Server 2008 onwards which means you can use this function in SQL Server 2012, 2014, 2016 and latest version of Microsoft SQL Server i.e. SQL Server 2017.