Zero To Hero In MS SQL Server - Part Two

In this article, I have shared knowledge about Database and schema and table creation in SQL. This is a continuation of Part One

Introduction

In this article, you will learn how to create a database, access the created database, and delete the database in SQL Server. This is the continuation of part one of the series of "Zero To Hero in MS SQL Server."  As I stated in my previous article, this article series is mainly for beginners who are new to MS SQL and for experienced developers who want to freshen up their SQL knowledge. To view my previous article in this series, please follow the below link.
The following are the write-ups of this series.
  • Background 
  • Creating Database
  • Use Database 
  • Rename Database
  • Drop Database 
  • Points to remember 
  • Conclusion 
Background

When I was preparing for my next job a few months ago, I started to prepare notes on SQL. And I thought to share those notes with you all since. It will be useful for beginners and experienced persons to freshen up their knowledge of SQL. Before getting into core SQL concepts, let's begin with the basic concepts.

Creating a Database

We can create the database using the SQL statement. 

Syntax
 
  1. CREATE DATABASE databasename 
 Example
  1. CREATE DATABASE DEMOWORKS  
To run the SQL Statement, simply select the statement and press F5 or else click Execute Icon from the toolbar in the studio.

In the above-given example, DEMOWORKS is the database name and we have created it using SQL Statment. The best practice method is to use SQL Statements and always give the SQL keywords in full capital letters, in the given statement, CREATE and DATABASE are the Keywords of this statement. 
 
Use Created Database
 
Now, we have created the database. We need to use or access the created one right? So here it is. 
 
Syntax
  1. USE databasename  
 Example
  1. USE DEMOWORKS  
When you run this statement, the created database will be opened. 
 
Rename Database 
 
Sometimes, we need to change the database name because we may have misspelled the database name. Instead of deleting it and creating a new database, we can rename the existing database. 
 
Syntax
  1. EXEC sp_renamedb 'OLDDATABASENAME''NEWDATABASE'  
 Example
  1. EXEC sp_renamedb 'DEMOWORKS''PracticalWorks'  
When the above statement is executed, you will get a message as  "The database name 'PracticalWorks' has been set." , which means the database name has been successfully changed. Hereafter, you have to access the database with its new name. If you try with its old name, it shows an error message as "Database 'DEMOWORKS' does not exist. Make sure that the name is entered correctly."
 
Drop Database
 
We can also delete a created database. In order to delete a database, we have to use the Keyword drop.  But you cannot drop the database when it is opened or in use. It will show an error message as "PracticalWorks cannot be deleted, it is in use". So you have to open another database and have to apply the drop database SQL statement. 
 
Syntax  
  1. DROP DATABASE databasename  
Example
  1. DROP DATABASE PracticalWorks  

When the above statement is executed, the created database will be deleted permanently. And when you try to access the deleted database, it shows an error message as "Database 'PracticalWorks' does not exist. Make sure that the name is entered correctly."

Points to Remember
  • The best practice method to learn SQL is to use the SQL Statements 
  • Always use the Keywords in Capital letters 
  • Give proper Database name
  • To run the SQL statement in MS SQL Server is by select the SQL Statement and clicking F5 or else by clicking the execute icon from the toolbar. 
Conclusion

In this article, we have learned how to create a database, access a database, rename a database and delete a database. I hope this was very useful. We will learn more in detail in my upcoming this article series. Please follow this article series and share your feedback in the comment section. Happy Learning!