Creating a Sample Database and Table
We will create a database containing a table with several records that we will use in the following lessons. This lesson presents two ways of creating a database and table. If you don’t have SQL Management Studio installed, then we can use the command prompt and the sqlcmd Utility. You also need to use the SQL commands we have discussed. If you would like to use SQL Management studio instead, then you can skip to the next section.
Connecting to Microsoft SQL Server Using sqlcmd Utility
Let’s start by opening command prompt. In Windows XP, click Start > Run and then type cmd. In Vista and Windows 7, open up start and type cmd in the search box. Press enter to open up command prompt.
Microsoft Windows [Version 6.1.7601] Copyright (c) 2009 Microsoft Corporation. All rights reserved. C:\Users\YourName>
The command that will allow us to connect to SQL Server is the sqlcmd. This will execute the sqlcmd Utility which allows you to feed SQL commands to a connected server instance. There are also a lot of options you can specify when using this command. The following table enumerates the useful ones that we can use.
|-U||The user login ID. If this is not specified SQL uses Windows Authentication mode.|
|-P||The user password. If -P is not specified and -U is specified, then sqlcmd will prompt you for the password.|
|-S||Specifies the name of the instance of a server that we will be connecting to.|
Figure 1 – sqlcmd Options
If you followed the tutorial on how to install Microsoft SQL Server 2014 Express, note that we specified a name for the server instance. We used SQLEXPRESS as the name of the instance. This is important to know when connecting to a database. We have also indicated a password for the SA account. We will be using that password to enter the server using the sa account. The following shows how to connect to the SQLEXPRESS server using the sa account.
C:\Users\YourName>sqlcmd -S .\SQLEXPRESS -U sa -P mypassword
We used the -S option to specify that we will use the SQLEXPRESS server instance. When specifying server instances, we also need to specify the computer name, or the ip address of where the actual server containing the server instance resides. If you don’t know a lot of networking and the concept of IP addresses, then we can connect to the SQLEXPRESS instance located in your own computer using the name of your machine, or by simply typing a dot. It is then followed by a backslash and then the name of the server instance.
We also indicated the username and password using the -U and -P options. We used the SA account and the password that we have provided when we installed SQL server. Alternatively, if you can’t remember the password, simply omit the -U and -P options and the sqlcmd will use Windows Authentication. You may receive some issues regarding limited permissions if you are not logged in with a windows administrator account.
C:\Users\YourName>sqlcmd -S .\SQLEXPRESS
This will use the credentials of the currently logged in windows user. Logging in as SA gives you full control with the database. In a real world scenario, you will have to create multiple accounts with limited privileges to prevent malicious users from compromising the contents of the database.
After you have typed the command, press enter and if everything went well, you can see a prompt waiting for you to type an SQL command.
C:\Users\YourName>sqlcmd -S .\SQLEXPRESS 1>
Creating a Database Using sqlcmd Utility
We will be creating a database named University which will be consisting of the Students table. To create the database, type the SQL command shown below.
1> CREATE DATABASE University
If you press enter, nothing will happen, and sqlcmd will wait for another line of SQL command to be typed. To execute the command we need to type GO and press enter.
1> CREATE DATABASE University 2> GO
You need to wait for few seconds for the command to take effect. If everything is successful, no error messages will show up and the prompt will reset back to 1.
1> CREATE DATABASE University 2> GO 1>
Creating a Table Using sqlcmd Utility
We will now create the Students table which will contain the details about several students in the university. First, we need to ensure that the database we are using is the University database that we recently created. Type the following commands.
1> USE University 2> GO
Press enter to change the database that we are currently working to the University database. Also, a message will show up that will confirm the change.
Let us now create the Students table. Type the following SQL commands.
1> CREATE TABLE Students 2> ( 3> StudentID int identity primary key, 4> FirstName varchar(50), 5> LastName varchar(50), 6> Gender varchar(10), 7> Age int, 8> Address varchar(50) 9> ) 10> GO 1>
The created table has 6 columns. The first one is the StudentID which we declared as the primary key and an identity column and having a type of int. We also declared FirstName, LastName, Gender, Age and Address with their appropriate SQL data types. Executing this command creates our new table.
Adding Records Using sqlcmd Utility
Now that we created our table, let’s add several rows or records of students. The following table shows some fictional student data that we will add in our table.
|2||Jimmie||Vargas||Male||18||Blue Bay Avenue|
|7||Zack||Norris||Male||19||Blue Bay Avenue|
Figure 2 – Records of Students Table
Now that we have 10 records, let’s now add them to the Students table using SQL commands.
1> INSERT INTO Students (FirstName, LastName, Gender, Age, Address) VALUES 2> ('Edward', 'Lyons', 'Male', 17, 'Spencer Street'), 3> ('Jimmie', 'Vargas', 'Male', 18, 'Blue Bay Avenue'), 4> ('Monica', 'Ward', 'Female', 16, 'Mapple Street'), 5> ('Joann', 'Jordan', 'Female', 17, 'Spencer Street'), 6> ('Cheryl', 'Swanson', 'Female', 17, 'Wacky Street'), 7> ('Clara', 'Webb', 'Female', 18, 'Spooner Street'), 8> ('Zack', 'Norris', 'Male', 19, 'Blue Bay Avenue'), 9> ('Randall', 'May', 'Male', 18, 'Golden Street'), 10> ('Jessica', 'Cole', 'Female', 17, 'Mapple Street'), 11> ('Oscar', 'Manning', 'Male', 18, 'Mapple Street') 12> GO
We used a modified version of INSERT INTO Statement which allows inserting of multiple records at once. Each record is enclosed with parentheses and the records are separated with commas. After executing these SQL commands, your Students table should now be filled with 10 records. If everything went fine, a message will show up telling the number of rows affected. You can now skip the following section and proceed to the next lesson.
Querying Records With sqlcmd Utility
To perform a simple query in the sqlcmd Utility, you can use a simple SELECT statement.
1> SELECT FirstName, LastName FROM Students 2> GO FirstName LastName ------------------------- ------------------------- Edward Lyons Jimmie Vargas Monica Ward Joann Jordan Cheryl Swanson Clara Webb Zack Norris Randall May Jessica Cole Oscar Manning (10 rows affected) 1>
Connecting to a Server Using SQL Server Management Studio
In this part of the lesson we will do the exact same thing done in the previous section using the SQL Server Management Studio. If you have already done the previous section, then you should skip this part. Using SQL Server Management Studio is easier and you won’t have to make use of SQL commands. SQL Server Management Studio offers tools that would be helpful to you. Open SQL Server Management Studio by going to Start > All Programs > Microsoft SQL Server 2008 or Microsoft SQL Server 2008 R2. Once executed wait for the program to initialize some settings.
After that, the splash screen will show up while the program is loading.
You will then be prompted to choose the server name, the type of the authentication, and the login and password details. Leave the Server type as is. If you followed the tutorial on how to install Microsof SQL Server 2008 Express, note that we specified a name for the server instance. We used SQLEXPRESS as the name of the instance. This is important to know when connecting to a database. We will be using that name in the Server name field. Note that it is preceded by a dot and backslash which simply means the SQLEXPRESS server instance located in your own machine. You can change the dot to, for example, an ip address of a remote server where the server instance is located. You can choose Windows Authentication as the Authentication to use the credentials of your windows account. You may need to be logged in as the administrator of your computer. Choosing SQL Server Authentication allows you to log in using a SQL Server account. We can log in using the sa account, in which the password is what we have indicated when we installed Microsoft SQL Server. The Remember password checkbox remembers your password so you wont have to type it again when you open SQL Server 2008. Click Connect to connect to SQLEXPRESS server.
You are now connected to the SQLEXPRESS server.
Creating a Database with SQL Server Management Studio
To the left of the interface is the Object Explorer which contains pretty much everything inside the server such as databases, tables, stored procedures, users, and many more. You can open up the Database folder to show databases that are already installed in your server. (You may not see any databases yet if this is your first time creating a database). To create a database, right click the Database folder and choose New Database.
The New Database Window will show up.
We will be creating a database named University which will contain a table with records of students. Type the name of the database which is University in the Database name field. You can leave the owner to default. The Database files section allows you to customize the location where the database files (the files that contain the actual database) will be located in your disk. You can also leave it to default as you will need enough permissions to change the location of the database files. Two database files will be created, a .mdf file which contains the database itself, and a .ldf file which contains logging information and transactions made to the database. Also make sure to change the Initial sizes to 3 MB and 2MB respectively so the files can acccomodate the contents of the database. Press OK to create the database.
Your created database should now appear inside the Database node of the Object Explorer.
Creating a Table in SQL Server Management Studio
Creating a table is as simple as creating a database. In the Database node, find your created University database and expand it. Find the tables node and right click. Choose the New Table option.
The SQL Server’s Table Designer will show up (Center) together with the Properties Window(Right) and the Column Properties Window Bottom.
You can type the columns and their corresponding type in the Table Designer. The Properties window allows you to change certain properties of the table. The Column Properties window allows you to change certain properties of a column. In the Table Designer, type the following fields and their corresponding datatype.
Allow Nulls simply means that a column can be left blank for a single record. A good practice when designing tables is to uncheck this for a column that should always be required to have values. But for now, we are more concerned on creating a sample table that we can use in the following lessons.
We should now set the primary key. The StudentID is the best candidate for a primary key and no two records should have the same StudentID. Select a particular column by clicking the blank square to the left of the column’s name then find in the toolbar the key icon which sets the selected column as the primary key.
The StudentID column will now have a key icon to its left to indicate that it is now the primary key. Also note that the its Allow Nulls check box was unchecked because primary keys should always have values.
We can also create the StudentID as the identity column. To do that, select the StudentID column and go to the Properties window. Find IdentityColumn and choose StudentID.
Save the table by pressing Ctrl + S. You will then be prompted for the name of the table. Name it Students and press OK to create our table with the name Students inside the University database.
You created table will appear in the Object Explorer inside the Tables node of the University database.
Adding Records to a Table Using SQL Server Management Studio
We will now add some records in the Students ttable that we created. To add records, right click the Student table in the Object Explorer and choose Edit Top 200 Rows.
A new window will show up that allows you to add records simply by typing each value to each field. The records that we will add is shown in Figure 2. Type each column data for every row. Note that StudentID is an identity column so you wont be able to type on it as SQL Server will auto generate a value for it.
Viewing Records in SQL Server Management Studio
To View the records you create using SQL Server Management Studio, you can use the New Query command located in the toolbar.
The SQL Editor will show up. Type the following command.
SELECT FirstName, LastName FROM Students;
If you read the first section of this tutorial, note that we used semicolon instead of the word GO. To Execute the SQL command, click the Execute button in the toolbar (The one with red exclamation point).
The query will then be executed and the results will be presented to you.
Close SQL Server Managment Studio. We are now ready to create an application that will connect to the University database, and query or modify the contents of the Students table.