0.6 SQL Server Management Studio Lab

1.Download the accompanying Word document, review it, and add snips as you would through this assignment.

2.Connect to NTC's Development Server

This MUST be done on your virtual lab. Connecting to a SQL Server requires authentication, which is done through your windows account on your virtual lab.

A On your virtual desktop, type in "SSMS" in the search bar.

Search for SSMS on the virtual desktop — searchforssms.png

B A connect to server window will appear. Type in "studatadev1" for the server name and click connect.

C Check your work:

1 Upon success, SQL Server Management Studio opens up and look similar to the provided image.

SQL Server Management Studio after connecting — 2_SSMSLanding.PNG

Confirm that the blue highlighted portion contains "studatadev1"

3.Interact with SQL Server Management Studio

A Expand the "studatadev1.ntc.edu" server node.

B Expand the "Databases" folder.

C Expand the "ITDDFStudentRecord" database.

D Expand the "Tables" folder.

E At this point, your "Object Explorer" should look similar to the following.

Object Explorer with Tables expanded — Object Explorer

4.Observe the difference between math and concatenation.

A Open a new query window.

B Copy and paste the following code into the query window:

SELECT 1 + 1

Query that adds two numbers — Simple Math

C Click the Execute button (or press F5) .

The results window will show 2. This is query is performing simple arithmetic because the numbers we are using do not have single quotes surrounding them. We'll see why this matters in the next step.

Arithmetic query result of 2 — Math Result

D Click the 'New Query' button on the top menu.

It is best practice to create a new query whenever you want to execute something new.

E In the new query window, copy and paste the code below:

SELECT '1' + '1'

F Execute the query.

Concatenation query result — Concatenate Result

Now that there are single quotes surrounding the numbers the query does what is called concatenation where instead of doing arithmetic it combines the numbers. Note that although these look like numbers they are not considered numbers when there are quotes around them; they are now considered characters.

G Close these two queries without saving them.

5.Do a "read" on the Student table to retrieve information and save the query.

A Click the 'New Query' button to bring up a new query window.

B Copy and paste the code below:

USE ITDDFStudentRecord

USE ITDDFStudentRecord statement — USE Statement

USE statements tell SSMS which database we want to be using. In this case we want to use the ITDDFStudentRecord database.

C Copy and paste the code below underneath the USE statement:

SELECT
    StudentId, FirstName, LastName, GenderId

SELECT statement with four columns — SELECT Statement

SELECT statements are used to indicate what columns we want to retrieve information from. In this case we want to see data on student Id's, first names, last names and gender Id's.

You'll notice that there are red squiggles underneath the column names. This tool is called IntelliSense. IntelliSense shows us errors in our queries before the query is ever executed.

1 Hover over the error for StudentId.

IntelliSense error for StudentId — Error Description

IntelliSense will give you a hint as to what the error is. In this case, the query does not yet know what table the columns "live" in because we haven't told it that information. That will come in the next step.

2 Execute the query.

Error results for the incomplete query — Execute With Errors

If your query has errors the errors will be printed out with a description and line number in the results window.

If you would like to turn on line numbers go to Tools | Options... | Text Editor | All Languages | General and check the Line numbers box.

D Copy and paste the code below underneath the SELECT statement:

FROM dbo.Student

FROM dbo.Student statement — FROM Statement

The FROM statement tells the query what particular table to get the information from. In this case we want to get information from the Student table.

Now that the query knows where the columns are coming "from" the errors go away.

E Execute the query.

There are 5 records (rows) in the Student table that we can now read.

F Save and open the query.

1 Click the Save button.

2 Save the query to your Data folder in ITDDF.

3 Close the query window in SSMS.

4 Click the Open button, navigate to the saved .sql file and open it.

5 Execute the query.

Now that you have the query saved, if data is added to the table at a later date you don't have to rewrite the query you can open it and run it easily.

6.Filter the Student table data by Gender

Our previous query brought back all records in the Student table. While that's useful, we want to have a scenario where we only have information on the Male students.

A Read the Gender table by copying and pasting the code below and executing the query.

USE ITDDFStudentRecord
SELECT
    GenderId, Gender
FROM dbo.Gender

Gender table query results — Read Gender Table

What this information tells us is that the Male gender is related to the Id of 1 and the Female gender is related to the Id of 2.

B Close this query and create a new query.

C Copy and paste the code below into the query window:

USE ITDDFStudentRecord
SELECT
    StudentId, FirstName, LastName, GenderId
FROM dbo.Student
WHERE GenderId = 1

The query is the same as the previous query that read all the data from the Student table, but we have added a WHERE statement. A WHERE statement filters the data according to a certain condition. In this case we want to see all the Male students, but our table only has GenderId's of 1 or 2 not strings like "Male" or "Female" so we will use the number related to the "Male" gender we saw when we read the Gender table.

D Execute the query.

Student rows filtered by GenderId — Student Table Results with WHERE Statement

Now instead of bringing back every record in the Student table the query only brings back those records with the GenderId of 1.

E Save the query.

7.Filter the Student table data by LastName

A Open a new query and copy and paste the code below:

USE ITDDFStudentRecord
SELECT
    StudentId, FirstName, LastName, GenderId
FROM dbo.Student
WHERE LastName = 'Miller'

Since we are filtering by a string value, the value in the WHERE statement needs to be in single quotes.

B Execute the query.

Students with the last name Miller — Student Result LastName

The result returns the two students with the last names of Miller.

C Save the query.

8.Filter the Student table data by LastName and Gender

We've been able to filter by Gender and LastName, but what if we want to find only students with the last name of Miller that are male? To do this by using multiple conditions in our WHERE statement.

A Copy and paste the code below into a new query window:

USE ITDDFStudentRecord
SELECT
    StudentId, FirstName, LastName, GenderId
FROM dbo.Student
WHERE LastName = 'Miller' AND GenderId = 1

The AND keyword is what allows both conditions to be applied to the WHERE statement.

B Execute the query.

Only Chad Miller appears in the query results — LastName and Gender

The results now only show Chad Miller because he is the only record in the Student table that has the last name of Miller and is also a male.

9.Read a range from the Course table.

A Open a new query and copy and paste the code below:

USE ITDDFStudentRecord
SELECT
    CourseId, CourseNumber, Name, Credit
FROM dbo.Course

B Note that the Name column is blue. This is because Name is a keyword in SSMS. In order to tell SSMS that this isn't a keyword of theirs that we want to use, we need to 'delimit' it by surrounding it in square brackets [ ].

Name column highlighted in SQL editor — Name Column

Name column enclosed in square brackets — Name Column Delimited

C We want to find all courses that are less than 3 credits. Add the WHERE statement below to the query:

WHERE Credit < 3

The < operator brings back anything less than 3. If we used the <= operator it would bring back anything less than or equal to 3.

D Execute the query.

Courses with fewer than three credits — Course Credit Result

E Save the query.

10.Find courses that begin with 'Web'

We have found classes with a credit value less than 3, but we are also interested in classes that relate to the Web in some way regardless of credit amount.

A Create a new query and copy and paste the code below:

USE ITDDFStudentRecord
SELECT
    CourseId, CourseNumber, [Name], Credit
FROM dbo.Course
WHERE Credit < 3 OR [Name] LIKE 'Web%'

This WHERE statement brings back results that are either one of the conditions OR the other. We are also using a LIKE operator followed by a string with a wildcard (%) in it that says we want any records that start with Web and we don't care what's after it.

If we used an AND operator instead of an OR operator here no records would be returned because there are no records that are both 1 credit and start with Web.

B Execute the query.

Course results with the OR and LIKE operators — Or/LIKE Operator

Now we can see Web Design 1 and Web Development in addition to the Programming Concepts courses even though they are named differently and are 3 credit courses.

C Save the query

11.Fill out the final questions in the Word document and submit to Canvas.