Wednesday, March 30, 2022

4 Examples of CASE expression in SQL

 

What is CASE and where is it used?

When a condition is met, the CASE statement evaluates different conditions and returns a scalar value. The phrase from the ELSE block will be returned if none of the conditions are evaluated to TRUE. The ELSE block, however, is optional. Above all, the CASE statement has two formats Simple CASE & Searched CASE.

The Searched CASE statement is highlighted in this post. To get the results, you'll need to use search and pattern matching.

Tuesday, March 29, 2022

Difference between Equi Join and Non-Equi Join in SQL with Examples

Hello guys, If you want to learn about the difference between Equi and Non-Equi join in SQL then you have come to the right place. Earlier, I have shared the difference between LEFT and RIGHT join in SQL and now we will understand equi and non-equi join in SQL and database. The term "Non-Equi join" in SQL Server may sound abstract, but it's far from it. Equi joins are the same way. I think you'll agree with me after reading this essay. I'll attempt to explain what they are and when they should be used today. So, let's get this party started. 

Saturday, March 26, 2022

MySQL vs NoSQL (non relational DB) - Pros and Cons

Hello guys, if you are wondering whether to choose a NoSQL database like MongoDB, Cassandara, or MySQL for your application then you have come to the right place. In an earlier article, MySQL vs PostgreSQL I had shared the pros and cons of choosing MySQL over PostgreSQL for your application, and in this article, I am going to show the benefits and drawbacks of choosing MySQL over NoSQL or vice-versa. 

Monday, March 21, 2022

How to do Pagination in SQL Server? Query Example Tutorial

 What is pagination?

Pagination, often known as paging, is a method of breaking down huge amounts of material into smaller, distinct pages. Pagination is a popular feature of online applications, as evidenced by Google. The primary principle of pagination is that when we search for anything on Google, the results are displayed on a different page.

Saturday, March 19, 2022

Transposing data in SQL - Example Tutorial

What is Transposition?

A transposition is when data is rotated from one row or column to another in order to modify the data layout and make observations from a different perspective. Row to the column, column to column, and bidirectional transposition are examples of basic transposition algorithms. Others, such as dynamic transposition, transposition with inter-row computations, and join-based transposition, are more complicated. All are prevalent in data analytics and so worthy of investigation.

Thursday, March 17, 2022

SQL cross join Example and it's performance

Hello guys, in the past, I gave you a quick overview of SQL JOINS where we learned about different types of JOINS like right, left, inner, and outer join, and in this article, we'll learn about the SQL CROSS JOIN idea in this post, and we'll back up our knowledge with simple examples and illustrations. So, let's start with the basics.

Tuesday, March 15, 2022

Difference between DDL and DML commands in SQL with examples

What are SQL commands?

SQL commands are just that: commands. It's used to send and receive data from the database. It may also be used to carry out particular activities, functions, and data searches.
SQL can build tables, add data to tables, drop tables, change tables, and define permissions for users, among other things.

Sunday, March 13, 2022

How to Order results in SQL query? ORDER BY Example Tutorial

SQL isn't a standard programming language in the sense that it doesn't require you to write a set of instructions in a specific order. Instead, SQL is a "declarative" language, which means that when you write a SQL query, you state what data you want to get as a result of the query but not how you'll get it.

Saturday, March 12, 2022

MySQL vs PostgreSQL? Pros and Cons

Which database to choose?

When establishing a new project, especially one on the Web, choosing a database management system is frequently an afterthought. Most frameworks include an object-relational mapping (ORM) tool that hides the differences across platforms and makes them all run at the same speed. 

Using the default choice (in most situations, MySQL) is seldom a bad idea, but it's worth thinking about. Don't get caught up in familiarity and comfort — a smart developer must constantly make educated selections based on the many possibilities, their pros, and downsides.

Thursday, March 10, 2022

How to work with NULL in SQL? Example Tutorial

This article will describe the complications that might arise when working with SQL NULL values, as well as some tips for resolving them.

What are NULL in SQL?

The definition of a NULL is one of the most difficult things to grasp while studying SQL. Programming languages have traditionally had no idea of missing or unknown data. Most programmers were familiar with the 'not applicable' flags in spreadsheets, or the traditional TBD flags in printouts for uncertain teachers, places, or other items.

Tuesday, March 8, 2022

What is Virtual columns in MySQL and How to use it? Example Tutorial

 What is a Virtual Column and where is it used?

Virtual/generated columns are a new feature in MySQL 5.7. Because the data in this column is computed based on a specified expression or from other columns, it is called a generated column.

Virtual columns resemble regular table columns in appearance, but their values are generated rather than saved on a disc.

Virtual columns are one of MySQL 5.7's most useful features; they allow you to store a value in a new field that is derived from one or more other fields in the same table.

Saturday, March 5, 2022

How to use ROW_NUMBER() Function in SQL with nondeterministic order? Example Tutorial

The ROW NUMBER window function has a wide range of uses, much beyond the apparent ordering requirements. Most of the time, while computing row numbers, you need to do it in a specified order, which you provide in the function's window order clause. 

Friday, March 4, 2022

Top 5 Common Errors and Mistakes in SQL and How to Solve Them

After writing some SQL code, you're ready to query your database. You type in the code, and nothing happens. Instead, you're presented with an error notice. 

Don't give up! SQL, like any other computer language, is prone to coding mistakes. We'll go through a few frequent mistakes individuals make while creating SQL in this tutorial.

Thursday, March 3, 2022

2 Ways to remove duplicate rows in SQL? Example Tutorial

Hello guys, if you are wondering how to remove duplicate rows in SQL and looking for solutions on different databases like SQL Server, MySQL, and Oracle then you have come to the right place. In this article, I am going to share 2 ways to remove duplicate rows from SQL, first by using GROUP BY and HAVING clause and second by using the RANK function which works on most of the databases.  You can use any of the approaches to remove duplicates from tables using SQL. This is also one of the popular SQL Interview Questions so knowing how to solve this problem will also give you an edge on interviews. 

Wednesday, March 2, 2022

How to find Nth Highest Salary in SQL? Example Tutorial

The Problem description

Finding the Nth highest salary of workers, where N might be 2, 3, 4, or anything, is one of the most typical SQL interview questions. This query is sometimes rephrased as "find the nth minimal wage in SQL." Many programmers struggle to design the SQL query when the Interviewer keeps asking for the 4th highest, 5th highest, and so on since they only know the easiest approach to address this problem, like by utilizing the SQL IN clause, which doesn't scale well.