SQL Day 2: Creating, Altering and Dropping a Database

LO1: Creating, Altering and Dropping a Database

After connecting to the database through SSMS first we need to create the database on which different queries will run.

Let’s start with creating a Database.
A SQL Server Database can be Created, Altered or Dropped in two ways:
  • Graphically using SQL Server Management Studio or
  • Using SQL Query

Graphically using SQL Server Management Studio:

  • Within the Object Explorer, right-click on Database folder and select the New Database.

  • When clicked on New Database a new dialog box will open. Here you need to provide the database name and when clicking on OK, SQL Server run the query internally and will create the new database for us.
  • When you expand the database folder then you will see the Sample database here


Creating Database Using Query:

We can also create the database by executing the query in the query editor. For creating any SQL database object we need to issue the Create statement. For example creating the database, tables, triggers, store procedure etc which will be cover in later sessions.

For now, we will focus on creating the database, to do that we need to execute the following query.

Syntax: Create Database [Database_Name]
Example: Create Database Sample1

Write above query and click on execute.


Refresh the database folder and you will see the Sample1 database created in Object Explorer.



When you create a database these are called User Defined Databases whereas there are some system databases which you can see when you expand the System Databases folder.


These system databases are required for proper functioning of SQL Server.

Property of Database:

Whenever a database is created two files are also get generated. These are MDF and LDF files. You can see these file by right click on the database and select Properties.


When clicked on Properties another dialog box will open and select Files that will show the MDF and LDF files location. So

.MDF File: Contains Actual Data (Full Form: Master Database File)

.LDF File: Transaction Log file which is used to recover database (Full Form: Log Database File)

Renaming (Altering) the Database:

This can be done by two ways, one is graphically and another is running a query.

  • To rename the database graphically just right click on the database and click on rename. Now give the database a new name and press enter. Now you should see that your database gets renamed.


  • To rename the database using query, you can do it in two ways
    • Using Alter command: By running following query the database name gets renamed.
      • Syntax: Alter Database [Database_Name] Modify Name = [New_Database_Name]
      • Example: Alter Database Sample1 Modify Name = Sample2


    • Another way is by using System Store Procedure (Store Procedure are the group of command that are executing together)
      • Syntax: sp_renameDB ‘OldDatabaseName’, ‘NewDatabaseName’
      • Example 1: sp_renameDB 'Sample1', 'Sample2'
      • Example 2: Execute sp_renameDB 'Sample1', 'Sample2'

Dropping the Database

A database can be dropped using graphically or by executing the query.
  • To drop the database graphically just right click on the database and click on Delete. This will delete MDF and LDF files associated with the database.
  • To drop the database using the query we need to execute the following query. Before dropping the database we need to make sure the database is not in use.
    • Syntax: Drop Database [DatabaseName]
    • Example: Drop Database Sample2
    • If the database is in Multi-User mode then we need to first switch to Single User mode then execute the drop statement.
    • Example: Alter Database Sample Set SINGLE_USER With Rollback Immediate
Roll back immediate will rollback all the incomplete transaction and close the connection to the database.

Note: System databases cannot be dropped.

That’s all for today. Thank You.

Comments

Post a Comment

Popular posts from this blog

SQL Day 11: Group By in SQL Server

Image
LO1: Grouping rows using Group By LO2: Filtering Groups LO3: Difference between Where and Having Clause Group By clause is used to group a selected set of rows into a set of summary rows by the values of one or more columns or expression. It is always used in conjunction with one or more aggregate functions. Let’s say we have a table tblEmployee which contains all the salary of employees including the cities. So if we need to find the maximum salary of employee we can use the MAX(Column_Name) aggregate function to find the highest salary of employees. select MAX(salary) from tblemployee In the same way, we can find the minimum salary and total salary of all the employees using different aggregate functions. select MIN(salary) as [Minimum Salary] from tblemployee select SUM(salary) as [Total Salary] from tblemployee Let’s say we have to find out the employee salary as per the different cities, to achieve this we need to use the Group B...
Keep reading

JAVA Script Executor Code to Click on an Element

Click on an Element in Selenium WebDriver Sometimes click() method does not work because two views are loaded at the same time on a page. In this case, we can use the JavascriptExecutor to get the work done. Below is code to click on an element using Javascript Executor. 1 2 3 4 5 6 7 @FindBy(id = Checkbox. financialAssistanceAccountEmailId ) public WebElement financialAssistanceAccountEmail; //Click to element JavascriptExecutor executor = (JavascriptExecutor) driver; executor. executeScript ( "arguments[0].click()" , financialAssistanceAccountEmail);
Keep reading