Introduction to the Structured Query Language

Besides Microsoft Visual Studio and Microsoft SQL Server Management Studio, you can use the DOS command prompt. 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.

After using Microsoft SQL Server from the command prompt, to close it, type Quit (case-insensitive) and press Enter. To close the DOS window:

• Type Exit (case-insensitive) and press Enter
• Click the system Close button

When Microsoft SQL Server 2008 is installed, it also installs the Windows PowerShell 1.0, which is a new command-based application from Microsoft. Besides the Microsoft SQL Server Management Studio and the Command Prompt, you can use PowerShell to create and manage databases. To access it, you can click Start -> (All) Programs -> Windows PowerShell 1.0 -> Windows PowerShell. A DOS window would display:

Notice that the title bar displays Windows PowerShell.

To access Microsoft SQL Server from PowerShell, type SQLCMD and press Enter:

Notice that, this time, the title bar displays SQLCMD, which indicates that the application is ready to receive commands that relate to Microsoft SQL Server.

After using the PowerShell, to exit from Microsoft SQL Server, type Quit (case-insensitive) and press Enter:

To close PowerShell and the DOS window, you can:

• Type Exit (case-insensitive) and press Enter
• Click the system Close button

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.

SQL can be pronounced Sequel or S. Q. L. In our lessons, we will consider the Sequel pronunciation. For this reason, the abbreviation will always be considered as a word, which would result in "A SQL statement"� instead of "An SQL statement". Also, we will regularly write, "The SQL" instead of "The SQL language, as the L already represents Language.

Like other non-platform specific languages such as C/C++, Pascal, or Java, the SQL you learn can be applied to various database systems. To adapt the SQL to Microsoft SQL Server, the company developed Transact-SQL as Microsoft's implementation of SQL. Transact-SQL is the language used internally by Microsoft SQL Server and MSDE. Although SQL Server highly adheres to the SQL standards, it has some internal details that may not be applied to other database systems like MySQL, Oracle, or even Microsoft Access, etc; although they too fairly conform to the standard.

The SQL we will learn and use here is Transact-SQL. In other words, we will assume that you are using Microsoft SQL Server as your platform for learning about databases. This means that, unless specified otherwise, most of the time, on this site, the word SQL refers to Transact-SQL or the way the language is implemented in Microsoft SQL Server.

As a computer language, the SQL is used to give instructions to an internal program called an interpreter. As we will learn in various sections, you must make sure you give precise instructions. SQL is not case-sensitive. This means that CREATE, create, and Create mean the same thing. It is a tradition to write SQL's own words in uppercase. This helps to distinguish SQL instructions with the words you use for your database.

As we will learn in this and the other remaining lessons, you use SQL by writing statements. In a Windows Forms Application, you can write the code and pass it to a SqlCommand object you would have created as we saw in the previous lesson. This would be done as follows:

void InitializeComponent()
{
    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.

In the next sections and lessons, we will learn various techniques of creating SQL statements with code. If there is no error in the code, what happens when you execute a statement depends on the code and the type of statement. In a Windows Forms Application, after passing the SQL code to a command, to execute it, as we saw in the previous lesson, you can call the ExecuteNonQuery() method of your SqlCommand object. This would be done as follows:

void InitializeComponent()
{
    SqlConnection connection =
        new SqlConnection("Data Source=(local);Integrated Security=yes");
    SqlCommand command = new SqlCommand(SQL Code, connection);

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

Before using a database, you must 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 Management Studio, in Microsoft Visual Studio, or at 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:

private void btnDatabase_Click(object sender, EventArgs e)
{
    SqlConnection connection =
        new SqlConnection("Data Source=(local);Integrated Security=yes");
    SqlCommand command = new SqlCommand(Database Creation Code, connection);

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

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

The primary piece of information about a database is its name. There are rules you must follow and suggestion you should observe:

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

Because of the flexibility of SQL, it can be difficult to maintain names in a database. Based on this, there are conventions we will use for our objects. In fact, we will adopt the rules used in C/C++, C#, Pascal, Java, and Visual Basic, etc. In our databases:

• Unless stated otherwise (we will mention the exceptions, for example with variables, tables, etc), a name will 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) or an underscore
• After the first character, we will use any combination of letters, digits, or underscores
• A name will not start with two underscores
• If the name is a combination of words, at least the second word will start in uppercase. Examples are Countries Statistics, Global Survey, _RealSport, FullName, or DriversLicenseNumber

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].

To assist you with creating and managing databases, including their objects, you use a set of language tools referred to as the Data Definition Language (DDL). This language is made of commands. For example, the primary command to create a database uses the following formula:

CREATE DATABASE DatabaseName

The CREATE DATABASE (remember that SQL is not case-sensitive) expression is required. The DatabaseName is the name that the new database will have. Although SQL is not case-sensitive, you should make it a habit to be aware of the cases you use to name your objects. Every statement in SQL can be terminated with a semi-colon. Although this is a requirement in many implementations of SQL, in Microsoft SQL Server, you can omit the semi-colon. Otherwise, the above formula would be:

CREATE DATABASE DatabaseName;

Here is an example:

CREATE DATABASE NationalCensus;

This formula is used if you do not want to provide any option. A database has one or more files saved in a default folder.

As you should be aware already from your experience on using computers, every computer file must have a path. The path is where the file is located in one of the drives of the computer. This allows the operating system to know where the file is so that when you or another application calls it, the operating system would not be confused.

By default, when you create a new database, Microsoft SQL Server assumes that it would be located at Drive:C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\Data folder.

When creating a new database,if you want, you can specify the location of the files of your database. To specify where the primary file of the database will be located, you can use the following formula:

CREATE DATABASE DatabaseName
ON PRIMARY
( NAME = LogicalName, FILENAME = Path )

The only three factors whose values need to be changed from this formula are the database name, the logical name, and the path name. The logical name can be any one-word name but should be different from the database name. The path is the directory location of the file. This path ends with a name for the file with the extension .mdf. The path should be complete and included in single-quotes. Here is an example:

CREATE DATABASE NationalCensus
ON PRIMARY
( NAME = DataRepository, FILENAME = 'C:\Exercises\NationalCensus.mdf')
GO

Besides the primary file, you may want to create and store a log file. To specify where the log file of the database would be located, you can use the following formula:

CREATE DATABASE DatabaseName
ON PRIMARY
( NAME = LogicalName, FILENAME = Path.mdf )
LOG ON
( NAME = LogicalName, FILENAME = Path.ldf )

The new factor in this formula is the path of the log file. Like the primary file, the log file must be named (with a logical name). The path ends with a file name whose  extension is .ldf. Here is an example:

CREATE DATABASE NationalCensus
ON PRIMARY
( NAME = DataRepository, FILENAME = 'C:\Exercises\NationalCensus.mdf')
LOG ON
( NAME = DataLog, FILENAME = 'C:\Exercises\NationalCensus.ldf')
GO

1. Start Microsoft Visual Studio
2. To create a new application, on the main menu, click File -> New Project...
3. In the middle list, click Windows Forms Application
4. Change the Name to WorldHungerStatistics
5. Click OK
6. In the Server Explorer, right-click Data Connections and click Create New SQL Server Database...
7. In the Server Name of the Create New SQL Server Database, type (local)
8. In the New Database Name text box, type World Hunger Statistics
    
   
9. Click OK (if you receive an error that the database was not created, open the Services in the Administrative Tools, check that SQL Server (server name) and SQL Server (MSSQLSERVER) services had started)
10. Design the form as follows:
     

    Control Text Name Label Database Name:   TextBox   txtDatabaseCreate Button Create btnCreateDatabase

11. Double-click the Create button
12. Implement the Click event of the button as follows:
    

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace Exercise3a
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void btnCreateDatabase_Click(object sender, EventArgs e)
            {
    	    // Make sure the user enters the name of the database
                if (txtDatabaseCreate.Text.Length == 0)
                {
                    MessageBox.Show("You must specify the name of " +
                         "the database you want to create");
                    txtDatabaseName.Focus();
                }
    
                string strConnection =
                    "Data Source=(local);Integrated Security=yes";
    
                using (SqlConnection connection = new SqlConnection(strConnection))
                {
                    string strDatabase = txtDatabaseCreate.Text;
                    SqlCommand command =
                        new SqlCommand("CREATE DATABASE [" + strDatabase + "];",
                        connection);
    
                    connection.Open();
    
                    command.ExecuteNonQuery();
    
                    MessageBox.Show("A database named \"" +
                                    txtDatabaseName.Text +
                                    "\" has been created on the " +
                                    connection.DataSource + " server.");
    
                    txtDatabaseCreate.Text = "";
                }
            }
        }
    }

13. Execute the application
14. In the Database text box, type Red Oak High School1
15. Click the Create button
     
    
16. Close the form and return to your programming environment

When originally creating a database, you may or may not know how many lists, files, or objects the project would have. Still, as a user of computer memory, the database must use a certain portion, at least in the beginning. The amount of space that a database is using is referred to as its size. When you create a new database, the SQL interpreter  automatically gives it a default size. This is enough for a starting database. Of course, you can either change this default later on or you can increase it when necessary.

Once a database exists on the server, to use it, as we saw in the previous lesson, you must first establish a connection to it. We saw that, to programmatically connection 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:

void CreateConnection()
{
    SqlConnection connection =
	new SqlConnection("Data Source=(local);Database='Exercise';Integrated Security=yes;");
}

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

using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : Form
{
    public Exercise()
    {
	InitializeComponent();
    }

    void InitializeComponent()
    {
	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();
    }
}

public class Program
{
    static int Main()
    {
	System.Windows.Forms.Application.Run(new Exercise());
	return 0;
    }
}

Database maintenance consists of renaming one database or removing another. To change the name of a database, Transact-SQL provides sp_renamedb. (Notice that the name starts with sp_. This is called a stored procedure. We will learn how to create them. For now, you can trust that they work and do what they are supposed to do). The formula used would be:

EXEC sp_renamedb 'ExistingName', 'NewName'

The EXEC sp_renamedb expression is required. The ExistingName factor is the name of the database that you want to rename. The NewName factor is the name you want the database to have after renaming it.

Here is an example of renaming a database:

EXEC sp_renamedb 'RentalCars', 'BethesdaCarRental
GO

To rename a table in a C# code, pass the EXEC sp_renamedb code as string to a SqlCommand object and call the SqlCommand.ExecuteNonQuery() method.

If you have created a database but don't need it anymore, you can delete it. It is important to know, regardless of how you create a database, whether using Microsoft SQL Server Management Studio, the Command Prompt, or Microsoft Visual Studio, every database can be accessed by any of these tools and you can delete any of the databases using any of these tools.

As done with creating a database, every tool provides its own means.

To delete a database in Microsoft SQL Server Management Studio, locate it in the left frame, right-click it and click Delete:

The Delete Object dialog box would come up. If you still want to delete the database, you can click OK.

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.

If you are in the Microsoft SQL Server Management Studio, you can delete a database using a code template. To get that code, display the Template Explorer. From the Template Explorer, expand the Databases node, then drag the Create Database node and drop it in the query window. You can then customize the generated code:

-- =============================================
-- Create database template
-- =============================================
USE master
GO

-- Drop the database if it already exists
IF  EXISTS (
	SELECT name 
		FROM sys.databases 
		WHERE name = N'<Database_Name, sysname, Database_Name>'
)
DROP DATABASE <Database_Name, sysname, Database_Name>
GO

1. To allow the user to delete a database, change the design of the form as follows:
     

   Control Text Name Label Database Name:   TextBox   txtDatabaseCreate Button Create btnCreateDatabase Label Database Name:   TextBox   txtDatabaseDelete Button Create btnDeleteDatabase

2. Double-click the Delete button
3. Implement the Click event of the button as follows:
   

   private void btnDeleteDatabase_Click(object sender, EventArgs e)
   {
       // Make sure the user enters a database
       if( txtDeleteDatabase.Text.Length == 0)
       {
           MessageBox.Show("You must specify the name of " +
                           "the database you want to delete");
           txtDatabaseCreate.Focus();
       }
   
       string strConnection =
               "Data Source=(local);Integrated Security=yes";
   
       using (SqlConnection connection = new SqlConnection(strConnection))
       {
           string strDatabase = txtDeleteDatabase.Text;
           SqlCommand command =
                   new SqlCommand("DROP DATABASE [" + strDatabase + "];",
                   connection);
   
           connection.Open();
   
           command.ExecuteNonQuery();
   
           MessageBox.Show("A database named \"" +
                           txtDeleteDatabase.Text +
                           "\" has been deleted from the " +
                           connection.DataSource + " server.");
   
           txtDeleteDatabase.Text = "";
       }
   }

4. Execute the application
5. In the Database text box, type Red Oak High School1
6. Click the Delete button
    
   
7. Click OK
8. Close the form and return to your programming environment

While writing SQL code, you should always know what database you are working on, otherwise you may add code to the wrong database. To programmatically specify the current database, use the USE keyword followed by the name of the database. The formula to use is:

USE DatabaseName;

Here is an example:

USE GovernmentStatistics;

Some of the windows that display databases, like the Microsoft SQL Server Management Studio, don't update their list immediately if an operation occurred outside their confinement. For example, if you create a database in the query window or in a Windows Application, its name would not be updated in the Object Explorer. To view such external changes, you can refresh the window that holds the list.

In Microsoft SQL Server Management Studio, to update a list, you can right-click its category in the Object Explorer and click Refresh. Only that category may be refreshed. For example, to refresh the list of databases, in the Object Explorer, you can right-click the Databases node and click Refresh.

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.

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.

As you should know from your learning C#, a namespace is a group of items that each has a unique name. For example, if you start creating many databases, there is a possibility that you may risk having various databases with the same name. If using a namespace, you can isolate the databases in various namespaces. In reality, to manage many other aspects of your database server, you use namespaces and you put objects, other than databases, within those namespaces. Therefore, a namespace and its content can be illustrated as follows:

Notice that there are various types of objects within a namespace.

Within a namespace, you can create objects as you wish. To further control and manage the objects inside of a namespace, you can put them in sub-groups called schemas. Therefore, a schema is a group of objects within a namespace. This also means that, within a namespace, you can have as many schemas as you want:

To manage the schemas in a namespace, you need a way to identify each schema. Based on this, each schema must have a name. In our illustration, one schema is named Schema1. Another schema is named Schema2. Yet another schema is named Schema_n.

Inside of a schema, two objects cannot have the same name, but an object in one schema can have the same name as an object in another schema. Based on this, if you are accessing an object within its schema, you can simply use its name, since that name would be unique. On the other hand, because of the implied possibility of dealing with objects with similar names in your server, when accessing an object outside of its schema, you must qualify it. To do this, you would type the name of the schema that contains the object you want to use, followed by the period operator, followed by the name of the object you want to use. From our illustration, to access the Something1 object that belongs to Schema1, you would type:

Schema1.Something1

There are two types of schemas you can use, those built-in and those you create. When Microsoft SQL Server is installed, it also creates a few schemas. One of the schemas is called sys.

The sys schema contains a list of some of the objects that exist in your system. One of these objects is called databases (actually, it's a view). When you create a database, its name is entered in the databases object using the same name you gave it.

In the previous lesson, we saw that, before using a database, you must establish a connection with the server. You do this using a user account that can use the server. Once the connection exists, you can create a database. 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.

A user of a database, or simply called a user, is a person who has been given the right to use the database. That person must have a login account in Microsoft SQL Server. As you may remember, when you install Microsoft SQL Server, you must use an account that has administrative rights. We also mentioned that there is an existing account named sa. These two accounts allow you to perform the necessary preliminary actions on a Microsoft SQL Server. Obviously, you may need to create other accounts, for the users.

To create a user, you must give a name to the account. The name can be anything. You can even use a name that is not found anywhere in the computer or the domain. Then, and most importantly, you must specify the login name that will use that user name. This means that you must associate the user name with a login name that was created already.

To visually create a user in Microsoft SQL Server, in the Object Explorer, expand the database whose user(s) you want to create and expand its Security node. Right-click Users and click New User... This would open the Database User - New dialog box. In the User Name, type the name you want. In the Login Name, you must type a valid user name for an existing account. After specifying the login and the user name, you can select other options in the check boxes. Then click OK.

The formula to programmatically create a user is:

CREATE USER user_name 
    [ { { FOR | FROM }
      { 
        LOGIN login_name 
        | CERTIFICATE cert_name 
        | ASYMMETRIC KEY asym_key_name
      } 
      | WITHOUT LOGIN
    ] 
    [ WITH DEFAULT_SCHEMA =schema_name ]

You start with the CREATE USER expression followed by a user name. As mentioned already, it can be almost anything. After the user name, to associate a login to the user, type FOR LOGIN followed by the login name that will use it.

If the name is in one word, simply type it. Here is an example:

CREATE USER JohnYamo
FOR LOGIN rkouma;
GO

If the name is in more than one word, include it in square brackets.  Here is an example:

CREATE USER [Paul Martin Souffrance]
FOR LOGIN rkouma;
GO

The other things are optional.

A permission is an action that a user is allowed to perform, or is prevented from performing, on a database or on one of its objects.

Many server operating systems and database environments use the word "right" for permission. In our lessons, we will use both words interchangeably. That is, for the rest of our lessons, the words "right" and "permission" will mean the exact same thing.

Microsoft SQL Server provides various levels of security and therefore permissions are managed on different levels.

In order to do something on the server or one of its objects, a user must be given the permission. This is also referred to as granting a permission. To grant permissions, the account you are using must have the ability to do so. This means that, before granting permissions, you must log in with an account that has its own right permissions.

To visually grant one or more permissions on the server, in the Object Explorer of Microsoft SQL Server Management Studio, right-click the name of the server and click Properties. In the left frame of the Server Properties dialog box, click Permissions. In the Logins or Roles list, click the name of the user. In the bottom list, use the options in the Grant column.

The basic formula to programmatically grant one or more permissions on a server is:

GRANT Permission TO Login

You start with the GRANT keyword followed by the name of the permission. After the permission, type TO, followed by the login name you want to grant the permission to. Here is an example:

USE master;
GO
GRANT CREATE ANY DATABASE
TO operez;
GO

If you want to grant more than one permission, separate their names with commas. Here is an example:

GRANT CREATE ANY DATABASE, SHUTDOWN
TO operez;
GO

If you want to grant the same permission(s) to more than one account, list them, separated by commas. Here is an example:

GRANT CREATE ANY DATABASE, ALTER ANY LOGIN
TO pkatts, gdmonay;
GO

To primary permission a person needs in Microsoft SQL Server is to be able to connect to the server. This permission is called CONNECT. This is also the default permission. After all, if a person cannot establish a connection to the server, what's the point? When you create a new user account, it is automatically given the right to connect to the server. Otherwise, you can deny it if you want.

As opposed to granting rights, you can prevent a user from doing something on the server, on a database, or on an object. This is referred to as denying a permission.

To visually deny one or more permissions on the server, in the Object Explorer of Microsoft SQL Server Management Studio, right-click the name of the server and click Properties. In the left frame, click Permissions. In the Logins or Roles list, click the name of the user. Use the options in the Deny column.

The basic formula to programmatically deny one or more permissions on a server is:

DENY Permission1,Permission2, Permission_n
TO Login1, Login2, Login_n

Here is an example:

DENY CREATE ANY DATABASE
TO rkouma;
GO

There are many issues you need to keep in mind in order to rightfully manage permssions. This is because permissions are somehow interconnected. This means that granting one permission may not work if another right is not given or is denied to the same user.

There are many permissions in Microsoft SQL Server. Some permissions are used regularly and are of primary importance:

• CONNECT: Obviously the primary right you need to give a user is the ability to connect to a Microsoft SQL Server database. If you want to permanently or temporarily block access of the server to a user, you can deny the Connect permission
• CREATE ANY DATABASE: By default, users are able to create new databases on the server as long as they have access to it. The CREATE ANY DATABASE permission allows a user to create a new database. If you want a user to only be able to use existing databases created by other people such as the database administrator(s), you should deny this right
• ALTER ANY DATABASE: Even if you prevent a user from creating new databases, he can still change something in the existing databases. To prevent such actions, you should deny this right
• ALTER ANY LOGIN: This permission allows a user (the user who receives this right) to change the login account of another user. This right should be granted only to database administrators

Besides granting or denying permissions to an account, you can give an account the ability to grant or deny permissions to other accounts. To do this visually, open the Database Properties for the database you want to work on. In the Users or Roles section, select the user. In the Persmissions, use the check boxes in the With Grant column.

The formula to programmatically give an account the ability to grant or deny permissions to other accounts is:

GRANT Permission1,Permission2, Permission_n
TO Login1, Login2, Login_n
WITH GRANT OPTION

This follows the same formula as the GRANT we saw earlier. You must just add the WITH GRANT OPTION expression.

Consider the following SQL statement:

DENY CREATE ANY DATABASE
TO rkouma;
GO

When this code executes, if the TO user logs in and tries creating a database, he would receive an error. Revoking a permission consists of either denying a permission that was previously granted or granting a permission that was previously denied. To visually do this, open the Properties dialog box of the database (or the object) on which the permission was managed.

To programmatically revoke a permission, the formula to follow is:

REVOKE [ GRANT OPTION FOR ] <permission> [ ,...n ]  
    { TO | FROM } <database_principal> [ ,...n ] 
        [ CASCADE ]
    [ AS <database_principal> ]

<permission> ::=  permission | ALL [ PRIVILEGES ]

<database_principal> ::= Database_user 
    | Database_role 
    | Application_role 
    | Database_user_mapped_to_Windows_User 
    | Database_user_mapped_to_Windows_Group 
    | Database_user_mapped_to_certificate 
    | Database_user_mapped_to_asymmetric_key 
    | Database_user_with_no_login

Start with the REVOKE keyword followed by the permission(s). This is followed by either TO or FROM and the login name of the account whose permission(s) must be managed. Here is an example:

/*
DENY CREATE ANY DATABASE
TO rkouma;
GO
*/

REVOKE CREATE ANY DATABASE
TO rkouma;
GO

Revoking a permission doesn't give that same permission. Imagine a user with a newly created account didn't have the permission to create new databases. If you deny that person the ability to create new databases, that denial becomes effective. If you revoke the permission, you are asking the server to restore the status of that person with regards to that particular right. That doesn't give that user the permission. The above code doesn't give the user the right to create new databases. If you want the user to have a right, you must explicitly grant the permission. Consider the following code:

REVOKE CREATE ANY DATABASE
TO rkouma;
GO

GRANT CREATE ANY DATABASE
TO rkouma;
GO

This restores the user's denial for creating new databases, then grants the permission to that user. This time, the user has the right to create new databases.

An operation is an action performed on one or more values either to modify the value held by one or both of the variables or to produce a new value by combining values. Therefore, an operation is performed by using at least one symbol and one value. The symbol used in an operation is called an operator. A value involved in an operation is called an operand.

1. Create a new Windows Forms Application named Exercise4
2. From the Common Controls section of the Toolbox, add a Button to the form
3. Double-click the button and implement its Click event as follows:
   

   private void button1_Click(object sender, EventArgs e)
   {
       string strConnection =
   	"Data Source=(local);Integrated Security=yes";
   
       using (SqlConnection connection = new SqlConnection(strConnection))
       {
   	SqlCommand command =
   	    new SqlCommand("CREATE DATABASE Exercise4;", connection);
   
   	connection.Open();
   	command.ExecuteNonQuery();
   
   	MessageBox.Show("A database named \"Exercise4\" has been created.");
       }
   }

4. Execute the application
5. Click the button
6. Click OK
7. Close the form and return to your programming environment
8. Delete the button on the form and design it (the form) as follows:
    

   Control Text Name Other Properties Label First Name:     TextBox   txtFirstName   Label Last Name:     TextBox   txtLastName   Label Full Name:     TextBox   txtFulName   Label Date Hired:     DateTimePicker   dtpDateHired   Label Employment Status:     ComboBox   cbxEmploymentStatus Items: Full Time Part Time Contractor Seasonal Intern Label Weekly Status:     TextBox   txtWeeklyStatus TextAlign: Right Label Hourly Salary:     TextBox   txtHourlyStatus TextAlign: Right Button Operate btnOperate   Label Weekly Salary:     TextBox   txtWeeklySalary TextAlign: Right Button Close btnClose

The SELECT operator can be used, among other things, to display a value. In this case, the SELECT keyword uses the following syntax:

SELECT What

Based on this, to use it, where it is needed, type SELECT followed by a number, a word, a string, or an expression. Here is an example:

SELECT 226.75;

Based on this definition, instead of just being a value, the thing on the right side of SELECT must be able to produce a value. SELECT can be used with more than one value. The values must be separated by commas. Here is an example:

SELECT 'Hourly Salary', 24.85

1. On the form, double-click the Operate button
2. To use a SELECT statement, implement the Click event as follows:
   

   private void btnOperate_Click(object sender, EventArgs e)
   {
       SqlConnection connection =
   	new SqlConnection("Data Source=(local);" +
   			  "Database='Exercise4';" +
   			  "Integrated Security=yes;");
       SqlCommand command = new SqlCommand("SELECT 'William';",
   					connection);
       connection.Open();
       SqlDataReader rdr = command.ExecuteReader();
   
       while (rdr.Read())
   	txtFirstName.Text = rdr[0].ToString();
   
       rdr.Close();
       connection.Close();
   }

3. Return to the form and double-click the Close button
4. Implement its Click event as follows:
   

   private void btnClose_Click(object sender, EventArgs e)
   {
       Close();
   }

5. Execute the application
6. Click the Operate button
7. After checking that a first name displays, close the form and return to your programming environment
8. To select more than one value, change the code of the Operate button as follows:
   

   private void btnOperate_Click(object sender, EventArgs e)
   {
       SqlConnection connection =
   	new SqlConnection("Data Source=(local);" +
   			  "Database='Exercise4';" +
   			  "Integrated Security=yes;");
       SqlCommand command = new SqlCommand("SELECT 'William', 'Godetsky';",
   					connection);
       connection.Open();
       SqlDataReader rdr = command.ExecuteReader();
   
       while (rdr.Read())
       {
   	txtFirstName.Text = rdr[0].ToString();
   	txtLastName.Text = rdr[1].ToString();
       }
   
       rdr.Close();
       connection.Close();
   }

9. Execute the application
10. Click the Operate button
     
    
11. Close the form and return to your programming environment

Because we mentioned that the thing on the right side must produce a result, you can as well use another SELECT statement that itself evaluates to a result. To distinguish the SELECT sections, the second one should be included in parentheses. Here is an example:

SELECT (SELECT 448.25);
GO

When one SELECT statement is created after another, the second is referred to as nested.

Just as you can nest one SELECT statement inside of another, you can also nest one statement in another statement that itself is nested. Here is an example:

SELECT (SELECT (SELECT 1350.75));
GO

One of the characteristics of SELECT is that it can segment its result in different sections. SELECT presents each value in a section called a column. Each column is represented with text called a caption. By default, the caption is "(No column name)". If you want to use your own caption, on the right side of an expression, type the AS keyword followed by the desired caption. The item on the right side of the AS keyword must be considered as one word. Here is an example:

SELECT 24.85 AS HourlySalary;

You can also include the item on the right side of AS in single-quotes. Here is an example:

SELECT 24.85 AS 'HourlySalary';

If the item on the right side of AS is in different words, you should include it in single-quotes or put them in inside of an opening square bracket "[" and a closing square bracket "]". Here is an example:

SELECT 24.85 AS 'Hourly Salary';

If you create different sections, separated by commas, you can follow each with AS and a caption. Here is an example:

SELECT 'James Knight' As FullName, 20.48 AS Salary;

An alternative is:

SELECT 'James Knight' As [Full Name], 20.48 AS [Hourly Salary];

A unary operator is an operator that performs its operation on only one operand.

As you may know from C#, to express a positive number, you can write + on its left side. As a mathematical convention, when a value is positive, you do not need to express it with the + operator.

The - sign must be typed on the left side of a number to make it negative.

Like most computer languages, Transact-SQL uses parentheses to isolate a group of items that must be considered as belonging to one entity. For example, as we will learn soon, parentheses allow a function to delimit the list of its arguments. Parentheses can also be used to isolate an operation or an expression with regards to another operation or expression. For example, when studying the algebraic operations, we saw that the subtraction is not associative and can lead to unpredictable results. In the same way, if your operation involves various operators such as a mix of addition(s) and subtraction(s), you can use parentheses to specify how to proceed with the operations, that is, what operation should (must) be performed first. Here is an example:

PRINT (154 - 12) + 8
PRINT 154 - (12 + 8)

This would produce:

150
134

As you can see, using the parentheses controls how the whole operation would proceed. This difference can be even more accentuated if your operation includes 3 or more operators and 4 or more operands. Here is another example of a nested SELECT statement that uses parentheses:

SELECT 
    (SELECT 448.25 * 3) + 
    (SELECT 82.28 - 36.04);
GO