While writing code in a Query Window, 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, type 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 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 windows, 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 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.

A namespace is a technique of creating a series 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:

Notice that, just like a namespace can contain objects (schemas), a schema can contain objects also (the objects we will create throughout our lessons).

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.

A schema is an object that contains other objects. Before using it, you must create it or you can use an existing schema. 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.

To access the schemas of a database, in the Object Explorer, expand the Databases node, expand the database that will hold or own the schema, and expand the Security node.

To create a schema, right-click Schemas and click New Schema...

This would open the Schema - New dialog box. In the Schema Name text box, enter a one-word name. Here is an example:

After providing a name, you can click OK.

We will see a practical example of creating a schema in Lesson 7.

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

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 two first broad categories of permissions: physical and virtual. The physical permission has to do with who has physical access to the computer or the room where it is located (who can open it, who can shut it down, etc). We are not concerned with physical permissions in these lessons. For the rest of our lessons, the permissions have to do with how to electronically connect to the server and what a user can do with it.

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. You can grant permissions visually or with code.

To visually grant one or more permissions on the server, in the Object Explorer, 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 Grants 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

1. In the Object Explorer, right-click the top node (the name of the computer) and click Properties...
2. In the left frame, click Permissions
3. In the Logins or Roles list, click pkatts
4. In the Permissions list, in the Grant column, click the check box that corresponds to Create Any Database.
   Make sure Connect SQL is selected
    
   
5. Still in the Permissions for pkatts section, in the Grant column, click the check boxes that correspond to Alter Any Connection, Alter Any Database, and Alter Any Login
    
   
6. Click OK
7. On the task bar, click Start -> Switch User
8. Log in with the pkatts account
9. On the task bar, click Start -> (All) Programs -> Microsoft SQL Server -> SQL Server Management Studio
10. Make sure the Authentication is set to Windows Authentication and that the pkatts account is selected as as the User Name.
    Click Connect
11. In the Object Explorer, expand the Databases node
12. Right-click Databases and click New Database...
13. In the Name, type Beauty Salon and click OK.
    Notice that the database has been created.
14. On the task bar, click Start -> Log off
15. Re-log in as the account you were using before

To primary permission a person needs in Microsoft SQL Server is to be able to connect to the server. 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, 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

1. In the Object Explorer, right-click the name of the computer and click Properties...
2. In the left frame, click Permissions
3. In the Logins or Roles list, click operez
4. In the Permissions list, in the Deny column, click the check box that corresponds to Create Any Database
    
   
5. Click OK
6. In the Object Explorer, right-click RealEstate1 and click Properties
7. In the left frame, click Permissions
8. In the Users or Roles section, click Orlando
9. In the Permissions for Orlando section, in the Deny column, click the check box that corresponds to Connect
    
   
10. Click OK
11. On the task bar, click Start -> (All) Programs -> Microsoft SQL Server -> SQL Server Management Studio
12. Set the Authentication to SQL Server Authentication
13. In the Login name, type operez and press Tab
14. In the password, type P@ssword1
15. Click Connect
16. In the Object Explorer, expand the Databases node
17. Click the + button of MotorVehicleAdministration. Notice that you can expand it
18. Still in the Object Explorer, click the + button of RealEstate1.
    Notice that you receive an error
     
    
19. Click OK on the message box
20. Close Microsoft SQL Server
21. Restart it and login with an account that has administrative rights using the Windows Authentication
22. Click Connect
23. In the Object Explorer, right-click MotorVehicleAdministration and click Delete
     
    
24. In the Delete Object dialog box, click OK
25. On the Standard toolbar, click the New Query button
26. To delete a database, type:
    

    DROP DATABASE RealEstate1;
    GO
    drop database [beauty salon];
    GO

27. Press F5 to execute the statement
28. Close Microsoft SQL Server
29. When asked whether you want to save, click No

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 denied to the same user.

There are so many permissions in Microsoft SQL Server that we cannot explain all of them. Instead, you can check the documentation to see a list of all of them. Still, 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 another account. 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 has been executed, 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 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.

1. Start Microsoft SQL Server
2. In the Authentication combo box, select Windows Authentication and make sure the Administrator account is selected in the User Name (or the account you used when you installed Microsoft SQL Server)
    
   
3. Click Connect

Probably the most important requirement of creating a database is to give it a name. The SQL is very flexible when it comes to names. In fact, it is very less restrictive than most other computer languages. Still, 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, x3, 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].

1. In the Object Explorer, right-click Databases and click New Database...
    
   
2. In the Name text box, type MotorVehicleAdministration

Whenever a new database is created, the server wants to keep track of who created that database. This is known as the database owner. By default, Microsoft SQL Server creates a special account named dbo (for database owner). When you create a database but do not specify the owner, this account is used. The dbo account is also given rights to all types of operations that can be performed on the database. This is convenient in most cases. Still, if you want, you can specify another user as the owner of the database. Of course, the account must exist, which means you should have previously created it or you can use an existing one.

To visually specify the owner of a database you are creating, you can click <default> in the Owner text box, type the name of the domain, followed by the back slash, and followed by the user name who will own the database. Alternatively, you can click the ellipsis button on the right side of the Owner text box. This would open the Select Database Owner dialog box:

In the Enter the Object Names to Select dialog box, enter the full name or the username of the user to whom you want to assign the database. After doing that, click Check Names. If the name is right, the dialog box would accept it. If the name is not right, you would receive an error. You can click the Browse button. This would open the Browse For Objects dialog box. If you see the user object you want to use, click its check box and click OK.

• In the Owner dialog box, click <default> and type DomainNamepkatts (replace DomainName with the name of your domain; otherwise, skip this step) (you can also click the browser button on the right side of Owner to locate and select the desired username)

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. If you use the New Database dialog box, after specifying the name of the database and clicking OK, the interpreter automatically specifies that the database would primarily use 2MB. This is enough for a starting database. Of course, you can either change this default later on or you can increase it when necessary.

If you want to specify a size different from the default, if you are using the New Database to create your database, in the Database Files section and under the Initial Size column, change the size as you wish.

• In the Database Files section, click the box under the Initial Size column header, click the up arrow of the spin button and increase its value to 5

As you should be aware of 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:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA folder. If you use the New Database dialog box of the SQL Server Management Studio, if you specify the name of the database and click OK, the interpreter automatically creates a new file, and appends the .MDF extension to the file: this is the (main) primary data file of your database.

If you do not want to use the default path, you can change it. If you are using the New Database dialog box, to change the path, under the Path header, select the current string:

Replace it with an appropriate path of your choice.

1. Scroll to the right side and, under the Path header, notice the location of the file
2. Start Windows Explorer
3. In the left frame, click the C: drive
4. Right-click a blank area in the right frame -> New -> Folder
5. Type Microsoft SQL Server Database Development as the name of the new folder
6. Return to the New Database dialog box.
   Under Path, click the browse button
7. Locate the Microsoft SQL Server Database Development folder you created and select it
8. Do the same for the other path
9. Click OK

When you install Microsoft SQL Server, it also installs 4 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 MS SQL Server is installed. For example, We know 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.

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 most includes commands. For example, the primary command to create a database uses the following formula:

CREATE DATABASE DatabaseName

To assist you with writing code, in the previous lessons, we saw that you could use the query window.

The CREATE DATABASE (remember that SQL is not case-sensitive) expression is required. The DatabaseName factor 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. We saw previously that a database has one or more files and we saw where they are located by defauft. We also saw that you could specify the location of files if you want. 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 that we saw already, 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:ExercisesNationalCensus.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:ExercisesNationalCensus.mdf')
LOG ON
( NAME = DataLog, FILENAME = 'C:ExercisesNationalCensus.ldf')
GO

1. To open the code editor, in the Object Explorer, right-click the name of the server and click New Query
    
   
2. In the empty window, type:
   

   CREATE DATABASE RealEstate1
   ON PRIMARY
   ( NAME = DataRepository, FILENAME = 'C:Microsoft SQL Server Database DevelopmentRealEstate1.mdf')
   LOG ON
   ( NAME = DataLog, FILENAME = 'C:Microsoft SQL Server Database DevelopmentRealEstate1.ldf')
   GO

3. To execute the statement, press F5

To specify more options with code, Microsoft SQL Server ships with various sample codes you can use for different assignments. For example, you can use sample code to create a database. The sample codes that Microsoft SQL Server are accessible from the Template Explorer.

To access the Template Explorer, on the main menu, you can click View -> Template Explorer. Before creating a database, open a new query window. Then:

• To create a new database using sample code, in the Template Explorer, expand the Databases node, then drag the Create Database node and drop it in the query window. The new database would be created in the server that holds the current connection
• If you have access to more than one server, to create a database in another server or using a different connection, in the Template Explorer, expand the Databases node, right-click Create Database and click Open. In the Connect to Database Engine dialog box, select the appropriate options, and can click OK

With any of these actions, Microsoft SQL Server would generate sample code for you:

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

CREATE DATABASE <Database_Name, sysname, Database_Name>
GO

You would then need to edit the code and execute it to create the database. From the previous lessons and sections, we have reviewed some characters uch as the comments -- and some words or expressions such as GO, CREATE DATABASE, and SELECT. We will study the other words or expressions in future lessons and sections.

A user of a computer, or a user of an application, simply called a user, is a person who has been given the right to use either the computer or an application. For a person to use Microsoft SQL Server, an account must be created for him or her. 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 for 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 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 names, you can select other options in the check boxes, options we will ignore at this time. 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 ]

If you want Microsoft SQL Server to generate code for you, open a new Query window. In the Template Explorer, expand the User node. Drag Create User As DBO and drop it in the text editor:

-- ==============================
-- Create User as DBO template
-- ==============================

USE <database_name, sysname, AdventureWorks>
GO

-- For login <login_name, sysname, login_name>, create a user in the database
CREATE USER <user_name, sysname, user_name>
FOR LOGIN <login_name, sysname, login_name>
WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'<user_name, sysname, user_name>'
GO

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.

1. In the Object Explorer, right-click Databases and click Refresh
2. Click the + button of MotorVehicleAdministration to expand it
3. Click the + button of Security to expand it
4. Right-click Users and click New User...
5. In the User Name, type Orlando Perez
6. On the right side of the Login Name text box, click the button
7. In text box, type pkatts
8. Click Check Names
9. When the name has been found, click OK
10. Don't change the other options and click OK
11. Right-click MotorVehicleAdministration and click New Query
12. To create another user, type the following:

    CREATE USER [Gertrude Danielle Monay]
    FOR LOGIN gdmonay;
    GO

13. To execute, press F5
14. Click inside the Query window and press Ctrl + A
15. To create a user for a different database, type the following:

    USE RealEstate1;
    GO
    CREATE USER Orlando
    FOR LOGIN operez;
    GO

16. Press F5 to execute

A role is an action or a set of actions that are allowed to a security principal. For example a person A can be allowed to create and use a database. The ability to perform such an action is referred to as a role. Another person B can be allowed only to use an existing database without being able to create a new one. This is another type of role.

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 SQL Server Management Studio, code in the query window, or the Command Prompt, 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 SQL Server Management Studio, in the Object Explorer, expand the Databases node, right-click the undesired database, and click Delete. A dialog box would prompt you to confirm your intention. If you still want to delete the database, you can click OK. If you change your mind, you can click Cancel.

To delete a database in SQL Query Analyzer, you use the DROP DATABASE expression 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 some one else or by another object.

The Object Explorer displays a list of items as a tree-style. One of the most regularly used items will be the name of the server you are using. If you are just starting to learn database development or you are a junior database developer, you may use or see only one server. In some cases, you may be dealing with many servers. Regardless, you should always know what server you are currently connecting to. This is easy to check  with the first node of the Object Explorer. In the following example, the server is named Central:

The name of the server is followed by parentheses.

In the previous section, we saw that, to establish a connection to a server, you must authenticate yourself. In some cases you may use the same account over and over again. In some other cases you may have different accounts that you use for different scenarios, such as one account for database development, one account for database management, and/or one account for database testing. When many connections have been made, each connection is represented in the Object Explorer by its own node and each connection has its own objects (sub-nodes):

As mentioned previously, to close a connection, you can right-click it and click Disconnect:

Some operations cannot be performed by some accounts. When performing some operations, you should always know what account you are using. You can check this in the parentheses of the server name. In the following connection, an account called Administrator is currently logged in to a server named Central:

1. In the Object Explorer, right-click ComputerName (SQL Server ... - operez) and click Disconnect
2. Right-click ComputerName (SQL Server ... - rkouma) and click Disconnect
3. Close Microsoft SQL Server
4. Start it again
5. Select Windows Authentication
6. Click Connect

We saw that, by default, the right area of Microsoft SQL Server Management Studio displays an empty gray window. When you select something in the Object Explorer, you can use that right area to display more detailed information about the select item. To do this, on the main menu, you can click View -> Object Explorer Details. The main are on the right side would then be filled with information:

Probably the most regular node you will be interested in, is labeled Databases. This node holds the names of databases on the server you are connected to. Also, from that node, you can perform almost any necessary operation of a database. To see most of the regularly available actions, you can expand the Databases node and some of its children. You can then right-click either Databases or one of its child nodes. For example, to start PowerShell, you can right-click  the Databases node or the server name and click PowerShell:

When the PowerShell comes up, what it displays depends on what you had right-clicked.

Although you will perform many of your database operations visually, some other operations will require that you write code. To assist with with this, Microsoft SQL Server provides a code editor and various code templates.

To open the editor:

• On the main menu, you can click File -> New -> Query With Current Connection
• On the Standard toolbar, click the New Query button
• In the Object Explorer, right-click the name of the server and click New Query

This would create a new window and position it on the right side of the interface. Whether you have already written code or not, you can save the document of the code editor at any time. To save it:

• You can press Ctrl + S
• On the main menu, you can click File -> Save SQLQueryX.sql...
• On the Standard toolbar, you can click the Save button

You will be required to provide a name for the file. After saving the file, its name would appear on the tab of the document.

After establishing a connection, you can take actions, such as creating a database and/or manipulating data. To provide the ability to create and manipulate a database, you use data manipulation language (DML). There are many of them on the market. The Structured Query Language, known as SQL, is a DML 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 of this site, 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. In the same way, you can open as many instances as the New Query as you want.

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. The file would have the extension .sql. Every time you open a new query, it is represented with a tab. To switch from one code part to another, you can click its tab. To dismiss an instance of the query, first access it (by clicking its tab), then, on the right side, click the close button . If you had written code in the query window, when you close it, you would be asked to save your code. If you want to preserve your code, then save it. If you had already executed the code in the window (we will learn how to write and execute SQL code), you don't have to save the contents of the window.

In the next sections and lessons, we will learn various techniques of creating SQL statements with code. By default, when a new query window appears, it is made of a wide white area where you write your statements:

After writing a statement, you can execute it, either to make it active or simply to test it. To execute a statement:

• You can press F5
• On the main menu, you can click Query -> Execute
• On the SQL Editor toolbar, you can click the Execute button
• You can right-click somewhere in the code editor and click Execute

When you execute code, code editor becomes divided into two horizontal sections:

Also, when you execute code, the interpreter would first analyze it. If there is an error, it would display one or more red lines of text in its bottom section. Here is an example:

If there is no error in the code, what happens when you execute a statement depends on the code and the type of statement.

A comment is text that the SQL interpreter would not consider as code. As such, a comment is written any way you like. What ever it is made of would not be read. Transact-SQL supports two types of comments. 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. This comment applies to only one line of text. To use it, start the line with --. Anything on the right side of -- is part of a comment and would not be considered as code. 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.

To separate statements, that is, to indicate when a statement ends, you can use the GO keyword (in reality and based on SQL standards, it is the semi-colon that would be required, but the Microsoft SQL Server interpreter accepts GO as the end of a statement).