Top 5 Courses to learn PostgreSQL Database in 2022 - Best of Lot

PostgreSQL is one of the most popular databases after the big three - Oracle, SQL Server, and MySQL. PostgreSQL is commonly known as Postgres and is often referred to as the world's most advanced open source database. If you are looking to learn PostgreSQL in 2022 and looking for some useful resources like books, tutorials, and courses then you have come to the right place. In this article, I am going to share some of the best PostgreSQL online courses for beginners. These courses will cover topics ranging from installations to writing basic queries and retrieving data from tables. you will also explore the logic of SQL Joins, and a few best practices which are essential while working in a real-world, production PostgreSQL database.

Top 5 Online Courses to Learn MongoDB NoSQL Database in 2022 - Best of Lot

MongoDB is one of the leading NoSQL databases and it was on my radar for a long time but I never get a chance to learn or work on it, but Recently I got an opportunity to use MongoDB for one of our projects and I learned a lot on the way. In this article, I will introduce MongoDB and how to learn MongoDB so that you can use it in your project. In this article, I will share some of the online courses which I have taken to understand everything up to the basic CRUD (Create, Retrieve, Update, Delete) operations on MongoDB. I have also read a couple of books and followed some tutorials but these courses were my main source to learn Mongo DB.

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.

Top 30 Examples of MySQL Commands in Linux and UNIX

Hello guys, if you are working with MySQL database in Linux and looking for MySQL commands to perform common tasks like starting and stopping a MySQL server then you have come to the right place. I have been working with MySQL since last 15 years as Java developer and it was actually the first database I used in a real-world project. Since I need to work with the MySQL database daily, I compiled a list of MySQL commands which I keep handy. This saves me a lot of time while doing development and support and that's what I am going to share with you today. 

How to Remove Leading/Trailing White Space from a String in SQL Server? LTRIM, RTRIM Example

Unlike Java, Microsoft SQL Server 2008, 2012, 2014,  and even the latest version don't have a built-in trim() function, which can remove both leading and trailing space from the given String. But, SQL Server does have two built-in functions LTRIM() and RTRIM() to remove leading and trailing space. The LTRIM() function removes space from the left side of String so you can use it to get rid of leading space, while RTRIM() removes white-space from the right side of String so you can use it to delete trailing space. You can even combine these two methods to create your own TRIM() method in SQL SERVER e.g. LTRIM(RTRIM(column)) will act as a TRIM() method because it removes both leading and trailing space.

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.

Top 5 Websites to Learn SQL Online for FREE - Best of Lot

SQL is one of the most important skills for any programmer be it a Java, C++, Python, JavaScript, or Ruby developer. Almost 95% of the Java applications use a relational database in their back-end and almost all web applications use the database. In recent years, one of the most common ways to learn any programming skill is online, at your comfort of the office or home and SQL is no different. Learning SQL online has another advantage of a quick head start because you don't need to install a database and create tables to write some SELECT queries. The installation and setup are definitely a tough part for beginners and I have gone through that pain every time I have to learn a new database.

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. 

Different Types of JDBC Drivers in Java - Quick Overview

How many types of JDBC drivers in Java is a classical JDBC interview question, though I have not seen this question recently it was very popular during the last decade and still asked mostly on Junior programmer level interviews. There are mainly 4 types of JDBC drivers in Java, those are referred to as type 1 to type 4 jdbc drivers. I agree it's easy to remember them by type rather than with their actual name, Which I have yet to get in-memory except for plain old JDBC-ODBC bridge driver.

How to delete from a table using JOIN in SQL Server

It's a little bit tricky to delete from a table while using any type of JOIN in SQL like Inner Join, Left Outer Join, or Right Outer Join. The obvious syntax doesn't work as shown below:

delete from #Expired e INNER JOIN 
Deals d ON e.DealId = d.DealId
Where d.Brand = 'Sony'

here I have a table with a list of expired deals that I want to delete from the Deals tables, but only for Sony.

How to add new columns to an existing table in SQL Server database

Hello guys, adding a new column to an existing table with data is always tricky and if you don't pay enough due diligence then you risk of corrupting or deleting existing data. You need to know what data is there, how much data is there, to gauge how long your query is gonna take to complete in production. Also, you cannot add NOT NULL columns into an existing table if they are not empty and you don't have a default value specified. If you know SQL then you probably know that you can add columns to an existing table in SQL Server using the ALTER command. It not only allows you to add a column but to drop columns as well.

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.

Top 5 Advanced SQL Books for Experienced Programmers - Best of lot, Must read

If you an experienced programmer and know how to write SQL queries and database fundamentals but want to take your SQL and database skills to the next level then you have a come to the right place. In this blog, I have shared a lot of free SQL books and courses you can use to start your SQL journey. This is also the second article about SQL books, In the first part, I have shared some of the best SQL books which are essential to learning SQL queries and fundamentals of database like normalization, indexing, and other design stuff, if you haven't read it yet, I suggest to do it now. You will find some amazing books to start learning SQL.

10 Things to Remember while doing Database Server Migration

Hello guys, recently, I have to work on a high profile project which involves migrating a live database from one server to another server as part of their data center exit program. This was one of the critical projects to pull off because we can't afford any mishap or data loss or production outage. There are a lot of things that I learned and would like to share with you guys. All these lessons not just apply to migrate databases like Microsoft SQL Server, Oracle, or MySQL database from one server to another but to any production process, you are migrating from another server. These are the things we learn from experience but as I have said in the past, you can only learn a few things