DevBrainiac Web Image
Articles
Blogs
MCQ
SQL
Q&A
Tools
Tags
Jobs
News
Community
LogIn
I don't have account.

SQL Database Queries

DevSniper

177 Views

Whether you're a beginner learning SQL or a experienced Database Administrator (DBA), understanding how to create, manage and back up databases is fundamental to maintaining a robust and reliable data infrastructure.

In this article we will walks you through the most commonly used SQL commands for handling databases in MySQL, SQL Server and other relational database systems — including how to perform full and differential backups, with real-world use cases and syntax examples.

1. How to Create a New Database in SQL

Creating a new database is the first step in any application or data-driven system. The CREATE DATABASE command is used to initialize an empty database that you can later populate with tables, views, stored procedures and other database objects.

SQL Syntax
Copy
CREATE DATABASE database_name;

database_name: This is the name you want to give your new database. It should be unique on the server and follow naming conventions (avoid spaces, special characters and SQL reserved words).

Example
Copy
CREATE DATABASE tempDB;

This command creates an empty database named tempDB. After creation, you can switch to this database using

Copy
USE tempDB;

After that you can start creating tables , views etc like

Copy
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    Position VARCHAR(50),
    HireDate DATE
);

Real-World Scenarios

  • ✅ Environment Separation : For enterprise-level applications, create separate databases for development, testing, staging and production.
  • ✅ Naming Convention : Use clear names like HR_Management, SalesDB or CustomerSupport_DB to reflect the purpose of the database.
  • ✅ Permissions : After creating a database, make sure to assign appropriate roles and access permissions using GRANT to ensure security and proper access control.

2. How to List All Databases in SQL

After creating or before creating a new database, it's often necessary to verify its existence or review all available databases on your server. This is especially useful when working on shared environments or managing multiple databases.

Copy
SHOW DATABASES;

This command displays a list of all databases currently available on your MySQL server instance, something like

Copy
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tempDB             |
+--------------------+

SQL Server Equivalent

For Microsoft SQL Server, the command is slightly different

Copy
SELECT name FROM sys.databases;

Real-World Use Cases

  • ✅ Environment Auditing : A DBA might use SHOW DATABASES; to inspect available databases on a production or staging server to ensure naming consistency or confirm proper deployment.
  • ✅ Debugging : When working with CI/CD pipelines or installation scripts, listing databases helps confirm that a database was successfully created or not accidentally dropped.
  • ✅ Security Audits : Verifying that no unauthorized or legacy databases exist on a shared server.

3. How to Delete a Database in SQL

The DROP DATABASE command is used to permanently remove a database and all its associated objects, including tables, views, stored procedures and data. This action is irreversible, so proceed with caution.

⚠️ Warning : This command deletes the entire database permanently. Once executed, all data and schema inside the database are lost and can not be recovered unless a backup exists.

SQL Syntax
Copy
DROP DATABASE database_name;
Example
Copy
DROP DATABASE tempDB;

This command deletes the tempDB database completely from your server.

Real-World Use Case

Imagine a QA team creates a temporary testing database TestFeatureX_2025. After the test cycle completes and data is no longer needed, a DBA may clean up the environment by running

Copy
DROP DATABASE TestFeatureX_2025;

Best Practices Before Dropping a Database

  • Always take a full backup before deletion using BACKUP DATABASE or mysqldump.
  • Ensure no live application or microservice is connected to the database.
  • Double-Check Name : Make sure you're deleting the correct database, typos can be disastrous.
  • Access Control : Limit DROP privileges to senior DBAs or automation scripts.

Tip : SQL Server may allow undo only if point-in-time backups or transaction log backups are enabled.

4. How to Perform a Full Database Backup in SQL

Creating full database backups is one of the most critical tasks in database administration. A full backup captures the entire state of the database — including all tables, data, indexes, stored procedures and more. Allowing you to restore the database completely if needed.

Why Full Backups Matter

Full backups protect against

  • Hardware failures
  • Data corruption
  • User errors (accidental deletes)
  • Ransomware or security breaches
  • Application bugs or failed deployments
SQL Syntax
Copy
BACKUP DATABASE databasename
TO DISK = 'filepath';
Example
Copy
BACKUP DATABASE tempDB
TO DISK = 'D:\db\backups\tempDB.bak';

This command creates a full backup of the tempDB database and saves it as a .bak file in the specified directory.

MySQL Equivalent Using Command Line

In MySQL, full backups are typically done using mysqldump:

Copy
mysqldump -u root -p tempDB > /backups/tempDB_full.sql

Note : Use cron jobs or scheduled tasks to automate MySQL dumps for daily or weekly backups.

Best Practices for Full Backups

  • Schedule Regularly : Automate full backups during off-peak hours (e.g., 2–4 AM).
  • Offsite Storage : Store copies in the cloud (AWS S3, Azure Blob) or another data center.
  • Encryption : Use encrypted storage or encrypt backup files for sensitive data.
  • Retention Policy : Maintain daily backups for 7–14 days and weekly/monthly archives.
  • Test Restores : Regularly restore backups to a staging server to ensure they're not corrupted.
  • Disk Space : Ensure target drive has enough free space for the .bak file.
  • Permissions : SQL Server/SQL Agent must have write access to the backup location.
  • Restore Test : Always test the backup before assuming it's usable.
  • Logs : Store logs or use STATS for monitoring and diagnostics.

5. How to Perform a Differential Backup in SQL Server

While full backups provide a complete snapshot of your database, differential backups are a smarter way to back up only the changes made since the last full backup. This results in smaller, faster and more frequent backups — ideal for high-transaction environments.

A differential backup stores only the data that has changed since the last full backup, not since the last differential backup. This means it grows over time until the next full backup is taken.

SQL Server Syntax
Copy
BACKUP DATABASE database_name
TO DISK = 'full_path_to_backup_file.bak'
WITH DIFFERENTIAL;
Example
Copy
BACKUP DATABASE tempDB
TO DISK = 'D:\db\backups\tempDB_diff.bak'
WITH DIFFERENTIAL;

This command backs up all changes made to tempDB since the last full backup and stores them in a .bak file.

Note : No matter how many differential backups are taken, they always relate back to the last full backup not the previous differential.

If you want to read all other backup and in depth knowledge of SQL backups read Database backup in SQL

Benefits of Differential Backups

  • Faster than full backups
  • Smaller file size → saves disk space
  • Ideal for frequent backups (e.g., hourly)
  • Reduced system load during backup windows
  • Great for large databases with moderate data changes

Real-World Use Case

An e-commerce platform takes a full backup every night at 1:00 AM and runs differential backups every 2 hours during the day. If something goes wrong at 4:00 PM, the DBA can

  • Restore the full backup from 1:00 AM.
  • Apply the latest differential backup from 2:00 PM.
  • Much faster than restoring dozens of transaction logs.

Differential Backup in MySQL

MySQL does not natively support differential backups like SQL Server. However, you can achieve similar behavior using tools like Percona XtraBackup, binary logs or custom scripts.