All about SQL, tutorials, lists, cheat sheets, code samples

In this article, we'll address that question, explain query classification, and demonstrate to you how to select records by multiple children attributes. This article is all about the SQL query to select records by multiple children attributes. Let's take a look at it in more detail. Parent-Child Tree Explained Example Query For the Children of a Parent Main Types of Cursors in Data Modeling Parent-Child Structure - Wrapping Up   Parent-Child Tree Explained If you're [...]

Every business scenario has hierarchical data. The reporting tool of these procedures develops reports showing hierarchical tree designs. These types of reports are consistently an interpretation bottleneck for these approaches. This article gives a resourceful method of pulling the hierarchical data from the database. Let's take a look at it in more detail. Data Setup – A Simple Hierarchy CREATE TABLE dbo.MgrDir ( MgrID INT ,EID INT ,CONSTRAINT mdir_pk1 PRIMARY KEY (MgrID, EID) ); GO [...]

Case Expression is used when we want output-based different conditions. You might think that for that purpose If-Else is there, but here's a difference. If-Else is a flow control statement, while Case is not. If-Else spans across multiple SQL queries when Case is used in a single SQL query. This statement assesses a checklist of requirements and yields one of the numerous potential result expressions. This expression has two configurations: The straightforward CASE expression corresponds [...]

In this article, we will review certain SQL functions and how they can be used. You will also check some code examples below. So here's a brief overview: COALESCE Function in SQL NULLIF Function in SQL ISNULL Function in SQL SQL Coalesce vs Isnull SQL Nullif vs IsNull Isnull TSQL T SQL Isnull vs Coalesce Nullif SQL Server Example What Does Coalesce Mean in SQL? How to Use Coalesce in SQL? COALESCE Function in SQL [...]

This is a very basic question asked by some interviewers. The Subsets are the pillars of Structured query language. Without subsets, it is not possible to manipulate the database and to create and build applications successfully. The DDL, DML, DCL, and TCL are responsible for building robust database applications and they all can be effectively utilized even in procedures and functions of SQL. We can split SQL into these five areas. Querying Data Manipulation Language [...]

SQL is one of the most effective tools. The SQL ordering of implementation specifies the decree in which the clauses of a query are considered. Some of the most typical query troubles people fly into could be effortlessly bypassed with more precise knowledge of the SQL order of performance, occasionally dubbed the SQL order of functions. Learning SQL query order can assist users to detect why a query won’t execute, and even more often will [...]

GOTO statement is used to break execution of flow and move execution directly to label specified. The GOTO statement modifies the course of performance to a tag. The T-SQL statement or messages that pursue GOTO are ignored and execution resumes at the tag or label. GOTO statements and labels can be utilized anywhere within programs such as procedures, batch, or statement blocks. GOTO can work within dependent control-of-flow information, statement obstructions, or methods, but it [...]

IIF Statement in SQL IIF statement is introduced in SQL Server 2012. We can say it's a shorthand version of CASE statement. If you come from .net world, you can relate IIF in SQL with IIF in visual basic language. The IIF in SQL Server gives one of two values, based on whether the Boolean phrase assesses to genuine or wrong in SQL Server. The important thing to remember here is that this IIF is [...]

NTILE is one of the ranking functions in SQL Server. NTILE function is used in scenarios where we have a requirement to divide the result set into some quarters, and with each row, we want to get that quarter's number. We might think of real-life situations such as suppose we have to get email addresses for campaigning from a database. And we also want that these returned emails are divided into four groups. The NTILE [...]

Both RANK and DENSE_RANK are windows functions. These functions are used to give ranking based on their order and based on their order in respective partition/group. Let's take a look in more detail. DENSE_RANK: It is an analytic query that calculates the position of a row in a positioned array of rows. The resulted rank is a number beginning from 1. DENSE_RANK is one of the most crucial analytical functions. It gives rank as sequential [...]