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. 

How to store and Query Hierarchical Data in Database using SQL? Example Tutorial

What methods do you use to detect hierarchical data and query it? We're about to get answers to two popular SQL queries. When working with SQL and relational databases, querying hierarchical data is a typical occurrence. It's not that hierarchical data is mysterious or uncommon; on the contrary, it's all around us. So, why is hierarchical data so difficult to work within relational databases? The issues arise mostly when converting hierarchical data to relational database concepts.

Top 30 Database Interview Questions and Answers

 Hello guys, if you are preparing for programming or technical interview then you must prepare for Database and SQL, these two are the most important skill for any programming or software development job be it a Java developer, C++ developer or a web developer. Earlier, I have shared 15 SQL Query Interview Questions and in this article, I am going to share 30 common database interview questions. I am not sharing the answer though as I expect you to already know the answers of these basic Database questions but if you struggle with any questions, ping me on comments section and I might just add the answers as well, depending upon what you guys like. 

How to create and Rollback Transaction in SQL query? Example Tutorial

A transaction in SQL Server is a consecutive gathering of explanations or queries to perform single or numerous undertakings in a data set. Every transaction might have single perused, compose, update, or delete tasks or a blend of this large number of tasks. Every transaction should happen two things in SQL Server:

• Either all alteration is effective when the transaction is committed.
• Or on the other hand, all alterations are scattered when the transaction is rollback.

Difference between DELETE vs DROP vs TRUNCATE in SQL

What the differences are between the commands Delete, Drop, and Truncate has to be one of the most popular queries during SQL interviews. We are here to answer any of your questions regarding this interview question. In this article, we will go over this concept and learn the difference between them in a very practical manner. So, what's the wait? let's start!

How to find top 10 records from a table in Oracle, MySQL, and SQL Server? Examples

Finding top 10 records is common need for programmers working with tables and database. Be it courses, cars, cricket, players, etc. The list is never-ending. But how do select TOP records from the database? As this is what happens in the back-end. So, if you are also looking for the answer to this question, check out this article and know more. We will see how to select the TOP 10 records from different databases with practical examples.

How to convert String to Date in SQL Server and T-SQL? Example tutorial

While working with crude information, you may oftentimes confront date values put away as text. Changing these qualities over completely to a date information type is vital since dates might be more significant during examination. In SQL Server, switching a string over completely to date can be accomplished in various methodologies.  In this article, we will cover various techniques to convert String to Date in all databases. 

10 Example of ALTER Clause in SQL

ALTER clause is one of the DDL commands which allows you to change object structure  in database like  table, stored procedure, trigger, or any other object in the database. By using ALTER command you can add or remove columns in a table, you can add and remove index from a table as well as you can update a stored procedure.  ALTER command is an essential SQL commands for programmers similar to SELECT, UPDATE, and DELETE. If you know how to create a table or index then you should also know how to modify a table or index using ALTER command. In this article, we'll see some common ALTER table examples in SQL to learn this command better and become a better database developer. 

How to use Stored Procedure in SQL or Database? Pros and Cons with Example

Hello folks if you want to learn about Stored Procedures in SQL and how it can help you to create a layer of abstraction around functionality then you have come to the right place.  With the help of many examples, we will learn how to construct stored procedures in SQL Server.

Difference between UNION and UNION ALL in SQL

Hello guys, if you are wondering how to combine data from multiple tables into one result set in SQL then there are multiple options. Earlier, we have looked at JOIN which allows you to fetch data from multiple table in single query, other option is UNION and UNION ALL which you can use to combine the data from multiple tables. We saved data in SQL tables in the relational database. We may need to choose data from various tables and aggregate the results of all Select statements on occasion. The SQL Union and versus Union All operators in SQL Server are explained in this article. We'll also look at the differences between these two operators, as well as some applications.

How to use WHERE and HAVING clause in SQL? Example Tutorial

Hello guys, if you want to learn about WHERE and HAVING clause in SQL then you have come to the right place. This blog shall be explaining how to use WHERE and HAVING clauses in SQL. but it won't be fine if we just jump to it. We need a good understanding of what SQL is and what it is used for. SQL stands for structured queried language, as it sounds it is a language or syntax that is used to query the database.

What are T-SQL window functions? How to use it and Performance Analysis

What are T-SQL window functions?

 T-SQL window functions make creating a lot of queries easier, and they typically outperform previous approaches. Using the LAG function, for example, is far superior to doing a self-join. To improve overall performance, you must first comprehend the notion of framing and how window functions rely on sorting to provide results.

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.

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. 

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. 

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.

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.

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.

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.

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.

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.

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.

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.

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. 

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.

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. 

What is Primary key in SQL? How to choose Primary key in table? Example

If you've ever dealt with databases, you've almost certainly come across the term - primary key (PK). And if you're following this series to learn about databases, this piece should help you understand what the PK is all about. Even if you're a database specialist, you could learn something new or improve your skills. So sit back, relax, and let's get started with the PKs.

How to use WHERE , LIKE, and BETWEEN in SQL for Filtering Rows? Example Tutorial

You may be thinking why is the filtering of data needed? Because you utilize the WHERE clause when changing data with DELETE and UPDATE statements, if you've been following these Database Fundamentals posts, you've already seen it. It's also been used to restrict the values returned from a SELECT operation multiple times in this series.

How to concatenate columns in SQL Server? Example Tutorial

Hello guys, welcome to my new blog SQLrevisited. I have long back created this blog to exclusively share SQL and Database articles but never get time to start it until today. This is the first article on this blog and in this post, you will learn how to contact multiple columns in an SQL server. 

Like Java and many other programming languages and databases, you can use the + operator to concatenate multiple columns in SQL Server. You can join as many columns as you want with the + operator but you must remember that concatenating with NULL will produce NULL, which means you can lose the information from other columns if one of them is NULL.