Introduction to SQL

Besides the SQL Server Management Studio in Microsoft SQL Server, you can also work on the DOS command prompt or PowerShell. This is done using an application or command named SQLCMD.EXE. To use it, open the Command Prompt, type SQLCMD (case-insensitive) and press Enter. You can then write SQL code. 

After establishing a connection, you can take actions, such as creating a database and/or manipulating data. The Structured Query Language, known as SQL, is a universal language used on various computer systems to create and manage databases.

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. This means that the words CREATE, create, and Create mean the same thing. It is a tradition to write SQL's own words in uppercase.

You use SQL by writing statements. To help you with this, Microsoft SQL Server provides a window, also referred to as the Query Window, that you can use to write your SQL code. To access it, on the left side of the window, you can right-click the name of the server and click New Query.

When the Query window comes up, it display a blank child window in which you can write your code. The code you write is a document and it can be saved as a file with the extension .sql.

Once your code is ready, you can pass it to a SqlCommand object you would have created as we saw already. This would be done as follows:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim connection As SqlConnection = _
        New SqlConnection("Data Source=(local);Integrated Security=yes")
        Dim command As SqlCommand = New SqlCommand(SQL Code, connection)
    End Sub
End Class

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

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:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Dim connection As SqlConnection = _
    New SqlConnection("Data Source=(local);Integrated Security=yes")
    Dim command As SqlCommand = New SqlCommand(SQL Code, connection)

    connection.Open()
    command.ExecuteNonQuery()
    connection.Close()
    End Sub
End Class

A comment is text that the SQL interpreter would not consider as code. The style of comment that starts with /* and ends with */ can be used. To apply it, start a line with /*, include any kind of text you like, on as many lines as you want. To close the commented section, type */. Here is an example of a line of comment:

/* First find out if the database we want to create exists already */

A comment can also be spread on more than one line, like a paragraph. Here is an example:

/* First find out if the MotorVehicleDivision database we 
   want to create exists already.
   If that database exists, we don't want it anymore. So,
   delete it from the system. */

Transact-SQL also supports the double-dash comment that applies to only one line of text. To use it, start the line with --. Here is an example:

-- =============================================
-- Database: MotorVehicleDivision
-- =============================================

/* First find out if the MotorVehicleDivision database we 
   want to create exists already.
   If that database exists, we don't want it anymore. So,
   delete it from the system. */


-- Now that the database is not in the system, create it

In SQL, after writing a statement, you can end it with a semi-colon. In fact, if you plan to use many statements in one block, you should end each with a semi-colon. When many statements are used, some of them must come after others.

Before using a database, you must first have one. You can create a new database in Microsoft SQL Server Management Studio, in Microsoft Visual Studio, or on the Command Prompt.

To create a database in Microsoft SQL Server Management Studio, you can right-click the Databases node and click New Database... If you are working from Microsoft Visual Studio, to create a new database, in the Server Explorer, you can right-click Data Connections and click Create New SQL Server Database...

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

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Dim connection As SqlConnection = _
    	New SqlConnection("Data Source=(local);Integrated Security=yes")
    Dim command As SqlCommand = _
	New SqlCommand(Database Creation Code, connection)

    connection.Open()
    Command.ExecuteNonQuery()
    connection.Close()
    End Sub
End Class

To create a database from the Command Prompt, open the DOS window and use the SQLCMD program. Then write code as we will learn next.

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

CREATE DATABASE DatabaseName

The CREATE DATABASE (remember that SQL is not case-sensitive) expression is required. The DatabaseName factor is the name that the new database will carry.

A statement in SQL can be terminated with a semi-colon:

CREATE DATABASE DatabaseName;

Probably the most important requirement of creating a database is to give it a name. There are rules you must follow when naming the objects in your databases:

• 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||

Here is an example of creating a database:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim strConnection As String = _
            "Data Source=(local);Integrated Security=yes"

        Using connection As SqlConnection = New SqlConnection(strConnection)
            Dim strDatabase As String = "Exercise"
            Dim cmd As SqlCommand = _
                New SqlCommand("CREATE DATABASE [" & strDatabase & "];", _
                connection)

            connection.Open()

            cmd.ExecuteNonQuery()

            MsgBox("A database named ""Exercise"" has been created on the " & _
                            connection.DataSource & " server.")
        End Using
    End Sub
End Class

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. Here is an example:

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
    Dim connection As SqlConnection = _
    New SqlConnection("Data Source=(local);" & _
                      "Database='Exercise';" & _
                      "Integrated Security=yes;")
    End Sub
End Class

DROP DATABASE DatabaseName

Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, _
                            ByVal e As System.EventArgs) Handles Me.Load
        Dim strConnection As String = _
                "Data Source=(local);Integrated Security=yes"

        Using connection As SqlConnection = New SqlConnection(strConnection)
            Dim strDatabase As String = "Exercise"
            Dim cmd As SqlCommand = _
                    New SqlCommand("DROP DATABASE [" & strDatabase & "];", _
                    connection)

            connection.Open()

            cmd.ExecuteNonQuery()

            MsgBox("A database named """ & _
                            strDatabase & _
                            """ has been deleted from the " & _
                            connection.DataSource & " server.")
        End Using
    End Sub
End Class