Home

Introduction to SQL

 

The Structured Query Language

 

Introduction

There are many tools you will use to create and manage your databases. One of them is Microsoft SQL server. The Structured Query Language, known as SQL, is a universal language used on various computer systems to create and manage databases. The SQL we will learn and use here is Transact-SQL.

The SQL Interpreter

As a computer language, the SQL is used to give instructions to an internal program called an interpreter. You must make sure you give precise instructions. SQL is not case-sensitive.

If you are creating a web site, you can write the necessary code. Once your code is ready, you can pass it to a SqlCommand object you would have created. This would be done as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection connection =
	        new SqlConnection("Data Source=(local);Integrated Security=yes");
        SqlCommand command = new SqlCommand(SQL Code, connection);
    }
}

In this example, the SQL Code factor represents a SQL statement you would write and pass it as a string.

Executing a Statement

After passing the SQL code to a command, to execute it, you can call the ExecuteNonQuery() method of your SqlCommand object. This would be done as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection connection =
        	new SqlConnection("Data Source=(local);Integrated Security=yes");
    	SqlCommand command = new SqlCommand(SQL Code, connection);

    	connection.Open();
    	command.ExecuteNonQuery();
    	connection.Close();
    }
}

Database Creation

 

Introduction

Before using a database, you must first have one. A database is primarily a group of computer files that each has a name and a location. Just as there are different ways to connect to a server, there are also different ways to create a database. You can create a new database in Microsoft SQL Server, in Microsoft Visual Studio, or on the Command Prompt.

To programmatically create a database, pass the necessary SQL code as the command text of the SqlCommand object.

CREATE a DATABASE

The command used to create a database in SQL uses the following formula:

CREATE DATABASE DatabaseName

The Name of a Database

Probably the most important requirement of creating a database is to give it a name:

  • A name can start with either a letter (a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, or Z), a digit (0, 1, 2, 3, 4, 5, 6, 7, 8, or 9), an underscore (_) or a non-readable character. Examples are _n, act, %783, Second
  • After the first character (letter, digit, underscore, or symbol), the name can have combinations of underscores, letters, digits, or symbols. Examples are _n24 or act_52_t
  • A name can include spaces. Example are c0untries st@ts, govmnt (records), or gl0b# $urvey||

After creating an object whose name includes space, whenever you use that object, include its name between [ and ]. Examples are [Countries Statistics], [Global Survey], or [Date of Birth]. Even if you had created an object with a name that doesn't include space, when using that name, you can still include it in square brackets. Examples are [UnitedStations], [FullName], [DriversLicenseNumber], and [Country].

 
 
 

Database Maintenance

 

Connecting to a Database

Once a database exists on the server, to use it, you must first establish a connection to it. To programmatically connect to a Microsoft SQL Server database, you could use a SqlConnection variable. In the connection string, to specify the database, assign its name to the Database attribute.

Once you have established a connection, you can then open it and perform the desired actions:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
 
 

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        SqlConnection connection =
		new SqlConnection("Data Source=(local);" +
				  "Database='Exercise';" +
				  "Integrated Security=yes;");
	SqlCommand command = new SqlCommand(SQL Code, connection);

	connection.Open();
	command.ExecuteNonQuery();
	connection.Close();
    }
}

Deleting a Database

If you have created a database but don't need it anymore, you can delete it. To delete a database in SQL, you use the DROP DATABASE instruction followed by the name of the database. The formula used is:

DROP DATABASE DatabaseName

Before deleting a database in SQL, you must make sure the database is not being used or accessed by someone else or by another object.

Microsoft SQL Server Primary Settings

 

Introduction

When you install Microsoft SQL Server, it also installs 5 databases named master, model, msdb, and tempdb. These databases will be for internal use. This means that you should avoid directly using them, unless you know exactly what you are doing.

The System Databases

One of the databases installed with Microsoft SQL Server is named master. This database holds all the information about the server on which your Microsoft SQL Server is installed. For example, we saw earlier that, to perform any operation on the server, you must login. The master database identifies any person, called a user, who accesses the database, about when and how.

Besides identifying who accesses the system, the master database also keeps track of everything you do on the server, including creating and managing databases.

You should not play with the master database; otherwise you may corrupt the system. For example, if the master database is not functioning right, the system would not work.

The Database Owner

You can establish a connection to the server using a user account that can use the server. In Microsoft SQL Server, the user who creates a database is referred to as the database owner. To identify this user, when Microsoft SQL Server is installed, it also creates a special user account named dbo. This account is automatically granted various permissions on the databases of the server.

Because the dbo account has default access to all databases, to refer to an object of a database, you can qualify it by typing dbo, followed by the period operator, followed by the name of the object.

 

 

   
 

Previous Copyright © 2009-2015, FunctionX, Inc. Next