Working with Multiple Databases: A SQL Learning Series Guide. (15)

In this post, we will learn how to work with multiple databases using SQL. This is a useful skill for data analysts, DBA, developers, and anyone who wants to manipulate data from different sources. We will cover the following topics:

• Database Management Systems (DBMS)
• Connecting to Multiple Databases
• Data Migration

This post is part of the SQL Learning Series, a collection of articles that will teach you the basics and advanced features of SQL. If you want to learn more about SQL, you can check out the previous posts in the series here.

Database Management Systems (DBMS):

A database is a collection of data that is organized in a structured way. A database can store information such as tables, records, fields, indexes, views, etc. A database can also have rules and constraints that define how the data can be accessed and manipulated.

A database management system (DBMS) is a software that allows users to create, manage, and interact with databases. A DBMS provides features such as data security, data integrity, data backup, data recovery, data concurrency, data consistency, etc. A DBMS also supports various operations such as data definition, data manipulation, data query, data administration, etc.

There are many types of DBMS, such as relational, hierarchical, network, object-oriented, document, graph, etc. Each type of DBMS has its own advantages and disadvantages, depending on the nature and complexity of the data. Some of the most popular DBMS are MySQL, Oracle, SQL Server, PostgreSQL, MongoDB, Neo4j, etc.

Connecting to Multiple Databases:

Sometimes, we may need to work with data from more than one database. For example, we may have a database that stores customer information, and another database that stores product information. We may want to join the data from both databases to perform some analysis or reporting.

To connect to multiple databases, we need to use a feature called database link. A database link is a connection between two databases that allows us to access data from one database in another database. A database link can be created using the CREATE DATABASE LINK statement in SQL. For example, the following statement creates a database link named prod_link that connects to the product database using the username prod_user and the password prod_pass.

SQL

CREATE DATABASE LINK prod_link
CONNECT TO prod_user IDENTIFIED BY prod_pass
USING 'product_db';

Once we have created a database link, we can use it to access data from the linked database using the @ operator. For example, the following query selects the product name and price from the product table in the product database.

SQL

SELECT product_name, product_price
FROM product@prod_link;

We can also join data from multiple databases using database links. For example, the following query joins the customer table from the current database and the product table from the product database, and returns the customer name, product name, and product price for each purchase.

SQL

SELECT c.customer_name, p.product_name, p.product_price
FROM customer c
JOIN product@prod_link p
ON c.product_id = p.product_id;

Data Migration:

Another scenario where we may need to work with multiple databases is when we want to move or copy data from one database to another. This is called data migration. Data migration can be done for various reasons, such as data backup, data integration, data transformation, data archiving, data warehousing, etc.

To migrate data from one database to another, we need to use a feature called data pump. A data pump is a tool that allows us to export and import data between databases using files. A data pump consists of two components: data pump export and data pump import.

Data pump export is a utility that allows us to export data from a database to a file. The file can be in binary or text format, and can contain data, metadata, or both. Data pump export can be invoked using the expdp command in SQL. For example, the following command exports the customer table from the current database to a file named customer.dmp in the directory named data_dir.

SQL

expdp customer_table DIRECTORY=data_dir DUMPFILE=customer.dmp;

Data pump import is a utility that allows us to import data from a file to a database. The file can be the same or different from the one created by data pump export. Data pump import can be invoked using the impdp command in SQL. For example, the following command imports the customer table from the file named customer.dmp in the directory named data_dir to the product database using the database link named prod_link.

SQL

impdp customer_table DIRECTORY=data_dir DUMPFILE=customer.dmp REMAP_SCHEMA=prod_user@prod_link;

Conclusion:

In this post, we learned how to work with multiple databases using SQL. We learned how to create and use database links to access and join data from different databases. We also learned how to use data pump to export and import data between databases. Working with multiple databases is a valuable skill that can help us handle complex and diverse data sources.

I hope you enjoyed this post and learned something new. If you did, please 👍, share, and clap 👋 for this post. You can also follow me on Medium and LinkedIn for more posts like this. This post is part of the SQL Learning Series, a collection of articles that will teach you the basics and advanced features of SQL. If you want to learn more about SQL, you can check out the previous posts in the series here.

“Data is the new oil. It’s valuable, but if unrefined it cannot be used. It has to be changed into gas, plastic, chemicals, etc. to create a valuable entity that drives profitable activity; so, must data be broken down, analyzed for it to have value.” — Clive Humby

If you want to learn more about SQL, you can check out the following resources Online💻🌐 and Books 📚.

• SQL Tutorial 💻🌐
• SQLZOO💻🌐
• SQLBolt 💻🌐
• DBMS 📘📚
• Data Migration 📘📚

Thank you for reading and happy learning! 😊.