These labs create and then work with a database called BookShopDB. This database holds data on sales and employees. The labs also use the AdventureWorks, Northwind and Pubs sample databases. If you do not have these installed, just adapt the labs follow the steps with a database that is installed. Alternatively, you can download and install the sample databases.Lab 1. Create An Empty Database

Create an empty database structure, identifying the sizes and names of the primary and log files.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script.Then paste all of the script into the query window and have a good look at it.
   CREATE DATABASE BookShopDB
ON PRIMARY
(
NAME = Bookshop_dat,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Bookshop.mdf',
SIZE = 4,
MAXSIZE = 10,
FILEGROWTH = 1
)
3. Run the CREATE DATABASE statement only by selecting it then pressing function key F5. Thenrun the script to create the Log file.
4. Save the script file locally
5. What units are the file size parameters measured in?

Lab 2. Create Table Structures

Create table structures in the Bookshop database, identifying the column names, data types and defaults. Also identify if null column values are allowed.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script.
   CREATE TABLE Authors
(AuthorID SMALLINT IDENTITY(101,1) NOT NULL,
FirstName VARCHAR(30) NOT NULL DEFAULT 'unknown',
LastName VARCHAR(30) NOT NULL,
YearBorn CHAR(4) NOT NULL DEFAULT ' N/A ',
YearDied CHAR(4) NOT NULL DEFAULT ' N/A ',
Description VARCHAR(200) NOT NULL DEFAULT 'N/A')
3. Run the CREATE TABLE statement and save this script.
4. Run the other 10 CREATE TABLE statements to build all the remaining tables.
5. What is the lowest possible value for the AuthorID column?

Lab 3. Add Column Constraints

Add column constraints to indicate primary and foreign keys. Also add check constraints to validate values entered.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script.Then paste all of the script into the query window and have a good look at it.
   ALTER TABLE Books
ADD CONSTRAINT books_pk PRIMARY KEY (TitleID)
3. Run the first ALTER TABLE statement. Then run the other ALTER TABLE statements that add primary key constraints.
4. Scroll down the script to find this statement that add a foreign key constraints.
   ALTER TABLE BookAuthors
ADD CONSTRAINT authorid_fk FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID)
5. Run this ALTER TABLE statement. Also run the other ALTER TABLE statements that add a foreign key constraint.
6. Scroll further down the script to find this statement that adds a check constraints.
   ALTER TABLE Customers --Must supply either First or Last Name
ADD CONSTRAINT checknames_ck CHECK
(FirstName NOT LIKE 'unknown' OR LastName NOT LIKE 'unknown')
7. Run this ALTER TABLE statement and save this script. Also run the other ALTER TABLE statements that add more check constraints.
8. Describe the relationship between the Books and BooksAuthors Tables?

Lab 4. Query The Tables

Select columns and rows from a table depending on a search criteria. Also sort and group rows. Use the Pubs sample database.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script.Then paste all of the script into the query window and have a good look at it.
   SELECT Type, AVG(Price) AS AvgPrice
FROM Titles
WHERE Price > 10
GROUP BY Type
ORDER BY AvgPrice DESC
3. Run and the first SELECT statement in the script. Look at the data returned in the Results panel. Then run the remaining SELECT statements.
4. Write a SELECT query to give the total price for Titles priced more than £15 grouped by Type.

Lab 5. Advanced Queries

Select columns from two joined tables using join criteria. Use different join types to select rows. Use the Northwind sample database.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script.Then paste all of the script into the query window and have a good look at it.
   SELECT o.OrderID, o.CustomerID, c.ContactName, c.City
FROM Orders o LEFT JOIN Customers c
ON o.CustomerID = c.CustomerID
AND o.ShipCity = c.City
ORDER BY o.OrderID
3. Describe in “normal English” what this query does.
4. Run the first SELECT statement. Then run the remaining SELECT statements that use joins. Look at the data returned in the Results panel..
5. Take a close look at this SELECT statement:
   SELECT OrderID, EmployeeID AS EmpID
FROM Orders
WHERE EmployeeID IN --Look in a different table
(
SELECT EmployeeID
FROM Employees
WHERE City = 'Seattle'
)
ORDER BY OrderID
6. Again, describe in “normal English” what this query does.
7. Run all the SELECT statemens including those using sub-queriend. Look at the data returned in the Results panel. Save the script.

Lab 6. Insert And Update Rows

Insert a new row into the table. Update a column value in certain rows of a table.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script.Then paste all of the script into the query window and have a good look at it.
   INSERT INTO Test1 (Title, Type, Cost)
VALUES ('Test Title', 'business', 27.00)
3. Run the CREATE TABLE statement then the INSERT statement.
4. What determines where the row will be inserted?
5. Scroll down to this UPDATE this statement and run it:
   UPDATE Test1
SET Cost = Cost * 2
WHERE Type = 'business'
6. Write and test an UPDATE statement to increase all thecosts in the Test1 table by 10%.
7. Run the DELETE statements.

Lab 7. Import Data Into Tables

Import data from text files into the rows of the tables.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script.Then paste all of the script into the query window and have a good look at it.
   BULK INSERT Authors
FROM 'C:\authors.txt'
WITH (DATAFILETYPE = 'CHAR')
3. Click to download text files. Unzip the folder then copy its files to the root directory of your computer: C:\ .
4. Run the BULK INSERT statements to import data into tables and save the script. Check the tables now contain data.
5. Does the table Authors have to exist before the BULK INSERT statement is run?

Lab 8. Format Data As XML

Write the results of a query to an XML document. Open an XML document for a query.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script.Paste this into the query window and have a good look at it.
   SELECT CustomerID, ContactName, CompanyName
FROM Customers
WHERE (CustomerID = N'ALFKI'
OR CustomerID = N'XYZAA')
ORDER BY CustomerID
FOR XML RAW
3. Run the first SELECT statement only. Then run the sp_xml_preparedocument and the other two SELECT statements. When selecting script to run include the integer variable declaration.
4. Which element starts the XML document that is generated by the SELECT statement?

Lab 9. Create A View

Create a view on tables in the Bookstore database. Modify data using the view. A view exposes only a subset of table data.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script.Then paste all of the script into the query window and have a good look at it.
   CREATE VIEW BookAuthorView
AS
SELECT a.FirstName, a.LastName, b.Title
FROM Authors a JOIN BookAuthors ba
ON a.AuthorID = ba.AuthorID
JOIN Books b
ON ba.TitleID = b.TitleID
3. Run the first CREATE VIEW statement. Then run the other statements to use, alter and delete the view.

Lab 10. Create A Function

Create a scalar and a table valued inline function in the Pubs database. Call both functions. A function contains TSQL statements to execute a defined task. After its execution, a function can return a result. The result can be a value or a rowset.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script.Then paste all of the script into the query window and have a good look at it.
   CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
FROM sales s, titles t
WHERE s.stor_id = @storeid and
t.title_id = s.title_id)
3. Run the CREATE FUNCTION first statement. Call the function. Then run the other statements to create and call a second function.

Lab 11. Create A Trigger

Create a trigger that is run after data is inserted into the Authors table. A trigger contains TSQL statements to execute a defined task. But the trigger is only executed after data in a table is modified.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script. Then paste all of the script into the query window and have a good look at it.
   CREATE TRIGGER dbo.insertindicator
ON dbo.authors
AFTER INSERT
AS
PRINT 'The insert trigger fired.'
3. Run the first CREATE TRIGGER statement. Then run the other CREATE TRIGGER statements to create an update and a delete trigger.
4. Why is the IF @@ROWCOUNT <> 0 statement needed in the delete trigger?

Lab 12. Create A Stored Procedure

Create a stored procedure to find customer details from an ID. Call the stored procedure. A stored procedure contains TSQL statements to execute a defined task. The task can be adapted by including parameters in the stored procedure.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script.Then paste all of the script into the query window and have a good look at it.
   CREATE PROCEDURE dbo.CustOrderHistRep
@CustomerID char(5)
AS --return a customers name and title
SELECT ContactName, ContactTitle
FROM Customers WHERE CustomerID = @CustomerID
--return products with total ordered by that customer
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE
C.CustomerID = @CustomerID AND C.CustomerID = O.CustomerID
AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
GO
3. Run the first CREATE PROCEDURE statement. Then run the script to execute the stored procedure.
4. Which special character is used at the start of a parameter?

Lab 13. Create Indexes

Create indexes on the Northwind database. Inspect the execution plan to evaluate their performance. Indexes enhance the performance of database. Indexes can be added to tables. When SQL Server accesses the tables, it will use the available indexes.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Show the execution plan for queries by selecting the
   Query>Include Actual Execution Plan menu
3. Click to view the script. It includes SELECT queries on the Northwind database. Then paste all of the script into the query window and have a good look at it. Run the first three SELECT statements only.
4. Now run only this script to create an index on the Contact table.
   CREATE UNIQUE NONCLUSTERED INDEX Contact
ON customers(city, companyname, contactname, country, phone)
5. Now run the remaining two SELECT queries to test the index. Compare the performance of the queries by looking at their execution plans.Has the index made a difference.

Lab 14. Secure the Bookshop database.

Use scripts to authorise security accounts and grant permissions. Access to a database can be restricted to specific roles and users. Also the actions users can perform can be restricted by using permissions.

1. In Management Studio Use the File>New>Query with Current Connection menu to open a new query window.
2. Click to view the script. Then paste all of the script into the query window and have a good look at it.
3. Add the following users to your PC from Control Panel > Users and Passwords
   • manager01
   • manager02
   • staff01
   • staff02
   • staff03
4. Using Computer Management Console > Local User and Groups add a group called Managers, then add the users manager01 & manager01 to group.
5. Look at this GRANT statement:
   GRANT SELECT, INSERT, UPDATE, DELETE
ON orders TO public
GRANT SELECT, INSERT, UPDATE, DELETE
ON bookorders TO public
GRANT SELECT, INSERT, UPDATE, DELETE
ON orderstatus TO public
6. Describe what exactly this statement does?
   EXEC sp_grantdbaccess @loginame = 'devuser'
7. Run the script to secure the database.

LAB 15. Using .NET Code In SQL Server 2008

Create and test a function in C# using Visual Studio. The C# language and the framework classes can be used in functions, stored procedures and triggers. These can be an effective alternative to using just TSQL statements.

1. Open Visual Studio 2010 and create a new Database project of type Visual C# SQL CLR Database Project.
2. When prompted to Add Database Reference, create a connection to the Adventure Works database.
3. Right-click the project in Solution Explorer and Add a New Item, select a User-Defined Function.
4. Edit the code in the Function1.cs to include the declaration:
   public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString HelloSQLCLRVS()
{
return new SqlString("Hello SQLCLR, from Visual Studio!");
}
};
5. Test the function by adding this script to Test.sql, in the Test Scripts folder:
   select dbo.HelloSQLCLRVS()
6. Run the solution and check the results that are shown in the Output window.
7. Experiment with creating & testing stored procedures & triggers in C#.

LAB 16. Using Replication

Create a replication publication and subscription in Management Studio. Replication allows for copies of a database to be distributed to users. These copies can then be updated, and then merged back in to the original database.

1. Create a new local publication in Management Studio’s Object Explorer, by right-clicking the Replication folder.
2. Use the New Publication Wizard to create a Merge publication on the Adventure Works database. Select several tables to include in the publication’s article.
3. Create a new local subscription to the publication you have just created, again by right-clicking the Replication folder.
4. Explore the options for creating publications and subscriptions in Object Explorer.

LAB 17. Using Integration Services

Use Import and Export Wizard to copy data between databases. Integration services allow the integration of data from various sources. It can also copy parts of a database to another database, with the option of modifying the data.

1. Launch the Import & Export Wizard from Management Studio’s Object Explorer, by right-clicking the Adventure Works database, then select the Tasks>Export Data menu options.
2. Use the wizard to export selected tables to a new database created on the same server.
3. Look at the new database you have just created to confirm that it contains the exported tables.
4. Experiment with using the Import and Export Wizard to move data between databases. Try, for example, importing an Excel spreadsheet in to a database.

LAB 18. Introduction to Reporting Services

Create a report from a table in the Adventure Works database. Reporting services allow the creation of reports from databases. The reports can be held in different formats. There are features to manage the storage and distribution of reports.

1. Open Visual Studio 2010 and create a new reporting project. From the New Project dialog box select Visual C#>Reporting>Report Application.
2. In the Reporting Wizard select Database at the first step, then Dataset at the second.
3. In the Choose Data Connection step, select or create a connection to the Adventure Works database. Save the connection.
4. In the Choose Data Objects step select the Production.Product table.
5. Continue to complete and view the report.

Answers.

• Lab 1. The file size parameters measured in Megabytes (Mb).
• Lab 2. The lowest possible value for the AuthorID column is 101.
• Lab 3. The relationship between the Books and BooksAuthors tables is a one-to-many relationship.
• Lab 4 A SELECT query to give the total price for Titles priced more than £15 grouped by Type.
  SELECT Type, SUM(Price) AS AvgPrice
FROM Titles
WHERE Price > 15
GROUP BY Type
ORDER BY AvgPrice DESC
• Lab 5. Show order details including a customer name and city. All orders shown even those without a customer.Show Orders taken by Seattle employees.
• Lab 6. The primary key on the table determines the row will be inserted at the end of the table.
• Lab 7. The table Authors have to exist before the BULK INSERT statement is run.
• Lab 8. The row element starts the XML document that is generated by the SELECT statement.
• Lab 9. The IF @@ROWCOUNT <> 0 statement needed in the delete trigger to ensure at least one row is deleted before the trigger fires.
• Lab 10. The @ character is used at the start of a parameter.

Back to beginning