Unlock document.
This document is partially blurred.
Unlock all pages and 1 million more documents.
Get Access
Database Concepts
8th Edition
David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder
Instructor’s Manual
Prepared by David J. Auer
Appendix A
Getting Started with Microsoft SQL Server 2016
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted,
in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior
written permission of the publisher. Printed in the United States of America.
Instructor’s Manual to accompany:
Database Concepts (8th Edition)
David M. Kroenke • David J. Auer • Scott L. Vandenberg • Robert C. Yoder
Appendix A - Getting Started with Microsoft SQL Server 2016
CHAPTER OBJECTIVES
• Learn how to install SQL Server 2016
• Learn how to install SQL Server Management Studio
• Learn how to create a database in SQL Server 2016
• Learn how to submit SQL commands to create table structures
• Learn how to submit SQL commands to insert database data
• Learn how to submit SQL commands to query a database
• Learn how to install the Microsoft SQL Server 2016 ODBC Client
• Learn how to import Microsoft Excel worksheet data into a database
CHAPTER ERRATA
These are no known errors at this time. Any errors that are discovered in the future will
be reported and corrected in the online DBC e08 Errata document, which will be
available at http://www.pearsonhighered.com/kroenke.
THE ACCESS WORKBENCH
Solutions to the Access Workbench exercises may be found in Solutions to all Sections:
The Access Workbench, which is a separate document within the Instructor’s Manual.
There is no section of The Access Workbench associated with this appendix.
NOTES ON MICROSOFT WINDOWS 10
This book uses the Microsoft Windows 10 operating system as the basis for screenshots
and step-by-step instructions. However, with Windows 10, Microsoft has introduced a
continuous update system that has already resulted in some fundamental differences in
how different versions of Windows 10 look and operate.
For example, in the original version of Microsoft Windows 10, clicking the Windows
Start button (or pressing the Windows key on the keyboard) displayed the menu shown
in Figure 1. In this menu, we need to click the All apps button in order to see the All
apps menu shown in Figure 2.
Appendix A - Getting Started with Microsoft SQL Server 2016
Figure 1 – Windows 10 Main Menu
The All apps button
The File Explorer button
The File Explorer icon
The All apps menu
Appendix A - Getting Started with Microsoft SQL Server 2016
Figure 2 – Windows 10 All Apps Menu
Figure 3 – Windows 10 Anniversary Update Main Menu with All Apps Menu Included
Microsoft then released the Windows 10 Anniversary Update (Feature update to
Windows 10, version 1607) (see the blog discussion at
https://blogs.windows.com/windowsexperience/2016/08/02/how-to-get-the-windows-10-
anniversary-update/#K1CZuiw4auiuE9A5.97 ). One of the changes introduced in the
Anniversary Update was a major change to the menu system. Now, as shown in
Figure 3, the All apps menu is immediately available when the Start button is used (or
when the keyboard Windows key is pressed).
Therefore, note that the step by step instructions in this book may need to be altered for
your use depending upon which version of Microsoft Windows 10 you or your students
are using!
We recommend that you update Windows 10 to the Windows 10 Anniversary Update
(Feature update to Windows 10, version 1607), and make sure it is patched with all
updates to that version (at a minimum patched to Windows 10 Version 1607 update for
August 23, 2016 (KB3176936), and the Windows 10 Version 1607 cumulative update for
September 29, 2016 (KB3194496). We also recommend using the 32-bit version of
Microsoft Office. This insures that all the examples discussed in this book will function
properly.
The All apps menu
The File Explorer button
The File Explorer icon
Appendix A - Getting Started with Microsoft SQL Server 2016
TEACHING SUGGESTIONS
• For individual student personal use on their own computer, we recommend (and use
in this book) Microsoft SQL Server 2016 Developer edition. Microsoft SQL Server
2016 Express Advanced edition can be used as an alternative.
• When you are using Microsoft SQL Server 2016, the best text editor to use is the text
editor built into the Microsoft SQL Server Management Studio. Take some time to
show you students how to use it.
• Make sure that your students work through Appendix A in conjunction with other
material presented in the text by working through Appendix A in the following
sequence:
❖ Before starting Chapter 3 on SQL, install Microsoft SQL Server 2016 and
Microsoft SQL Server Management Studio by working through this appendix up
to and including “How Do I Install the Microsoft SQL Server Management
Studio?”
❖ When studying the Chapter 3 sections on how to create and populate database
tables, work up to and including “How Do I Use SQL Statements to Insert
Database Data?” Both Chapter 3 and this appendix use the same WP database,
and this work will show you how to create your own copy of the WP database.
❖ When studying the Chapter 3 sections on how to use SQL Data Manipulation
Language (DML) and SQL Data Definition Language (DDL), work through the
section named “How Do I Work with SQL Queries in Microsoft SQL Server?”
Both Chapter 3 and this appendix use the same WP database, and you can run
the SQL Statements shown in Chapter 3 yourself, and see the results.
❖ When studying the Appendix E material on SQL Views, you can run the SQL
Statements shown in Appendix E yourself and see the results.
❖ When studying the Appendix E material on SQL Persistent Stored Modules
(SQL/PSM), you can run the SQL Statements shown in Appendix E yourself and
see the results.
❖ Work through the section “How Do I Import Microsoft Excel Data into a Microsoft
SQL Server Database Table?” in this appendix to understand how to import
Microsoft Excel data in a database table.
❖ Work through the section “How Do I Create an ODBC Connection from Microsoft
Access 2016 t an SQL Server 2016 Database?” in this appendix to understand
how to use Microsoft Access as a development environment for a Microsoft SQL
Server database.
Appendix A - Getting Started with Microsoft SQL Server 2016
ANSWERS TO REVIEW QUESTIONS
A.1 What is SQL Server 2016 Developer edition? What is SQL Server 2016 Express edition?
What are the differences between these two editions of SQL Server 2016?
Developer Edition is a free, single-user version of the Enterprise Edition, and it has the complete
feature set of the Enterprise Edition. It is intended, as the name implies, for use by a single user
A.2 What is the primary advantage of using SQL Server 2016 instead of Microsoft Access?
A.3 What is the set of Microsoft programs that are recommended as a necessary set of
prerequisites to the actual installation of SQL Server 2016? In what order should you
install these products?
1. Microsoft .NET Framework 3.5 SP 1
2. Microsoft .NET Framework 4.6.1
Appendix A - Getting Started with Microsoft SQL Server 2016
© 2018 Pearson Education, Inc. Page 8 of 33
Install these in the order listed. As Microsoft incorporates updates into SQL Server 2016, it may
become unnecessary to separately install the Critical update for SQL Server 2016 MSVCRT
prerequisites.
A.4 How do you install Microsoft SQL Server 2016 Developer edition?
Install Microsoft SQL Server 2016 Developer as follows (this is an outline – we will not repeat the
full set of instructions contained in the Appendix). Note that we are actually downloading SQL
Server 2016 with Service Pack 1:
1. Download SQLServer2016-SSEI-Dev.exe, which is a stub that starts the download of the
actual installation file.
A.5 What is the purpose of Microsoft SQL Server Management Studio?
A.6 How do you install the Microsoft SQL Server Management Studio?
Install the Microsoft SQL Server Management Studio as follows (this is an outline – we will not
repeat the full set of instructions contained in the Appendix). Note that SQL Server Management
Studio now requires a separate download:
Appendix A - Getting Started with Microsoft SQL Server 2016
© 2018 Pearson Education, Inc. Page 9 of 33
4. Open File Explorer and browse to the This PC | Downloads folder. Right-click the SMSS-
Setup-ENU.exe file to display the shortcut menu, and then click the Run as
administrator command. When the User Account Control dialog box for this file is
displayed, click the Yes button.
5. The Microsoft SQL Server Management Studio installation dialog box is displayed. Click
the Install button to begin the installation process.
6. When the installation is complete, the Setup Completed page is displayed. Click the
Close button to complete the installation process.
7. Close the SQL Server Installation Center Dialog Box.
A.7 If you experience a problem starting the Microsoft SQL Server Management Studio,
what steps should you take to resolve this problem?
Microsoft SQL Server 2016 may either take a long time to start the required services, or may fail
to start the at all (we do not know why this occurs, and hope that Microsoft will fix this glitch
soon!)
A.8 How do you create a new database in SQL Server 2016?
A.9 How do you specify the active database in SQL Server 2016?
A.10 What is a SQL script? What types of SQL statements and commands can you run more
efficiently as scripts?
A SQL script is a related group of SQL statements intended to be run at the same time. Scripts
are efficient for processing groups of SQL statements such as:
Appendix A - Getting Started with Microsoft SQL Server 2016
A.11 What tool(s) can be used to create a script?
A.12 What file extension should you use for SQL scripts?
A.13 How do you open and run a script in SQL Server?
A.14 How do you create database tables in SQL Server?
A.15 How do you populate database tables in SQL Server?
Appendix A - Getting Started with Microsoft SQL Server 2016
A.16 How do you create and run an SQL query in SQL Server?
To run a query, first specify the database you want to query by clicking on the database name in
the Database folder in the Object Browser to select it (alternatively, select it in the database
A.17 How do you install the Microsoft SQL Server 2016 ODBC client? Which ODBC software
is installed in the process?
A.18 How do you import Microsoft Excel data into an SQL Server table?
A.19 Why should imported data be initially stored in a temporary table and then moved into a
different SQL Server table? How do you do this?
Because the SQL Server Import and Export Wizard has some glitches, and we must use some
“work arounds.” One of these is storing data in a temporary table, creating a properly structured
table, and then moving (or copying) the data to the properly structured table. The steps are:
Appendix A - Getting Started with Microsoft SQL Server 2016
A.20 How do you create user accounts in SQL Server 2016? How do you give them
appropriate permissions to a specific database?
SQL Server user accounts are created and user permissions are managed in the SQL Server
Management Studio.
A.21 Why would you want to create an ODBC connection to link a Microsoft Access 2016 to
an SQL Server Database?
A.22 What is an ODBC DSN? Why is one needed?
A.23 How do you create an ODBC connection to link a Microsoft Access 2016 database to an
SQL Server Database?
A DSN may be created independently of a specific use by using the Microsoft ODBC Data Source
Appendix A - Getting Started with Microsoft SQL Server 2016
ANSWERS TO EXERCISES
A.24 If you haven’t already done so, download and install SQL Server 2016 Developer edition
as described in the text. Use the default settings for the installation. Be sure that the
Microsoft SQL Server 2016 Management Studio is correctly installed.
A.25 If you haven’t already done so, work through the steps described in this appendix to
create and populate the WP database.
A.26 Using SQL Server 2016 and the Microsoft SQL Server Management Studio, run the SQL
queries in Chapter 3.
SQL-Query-CH03-01 through SQL-Query-CH03-32
Appendix A - Getting Started with Microsoft SQL Server 2016
A.27 Use Microsoft SQL Server Management Studio to run one or more of the saved SQL
queries you created in question A.14:
• Open a query using the Open File button (or by selecting the File | Open File menu
command). Note that the query is opened in a tabbed query window. Run the query.
A.28 If needed, complete exercise 3.58. Complete exercise 3.59 using SQL Server 2016 and
the Microsoft SQL Server Management Studio. Start each saved query name with
MSSQL- and use the default .sql file extension. (The first saved query name should be
MSSQL-SQL-Query-AWE-3-1-A.sql.)
The solution for this Exercise is the same as the solution to Exercise 3.59. See the Instructor's
This is the
selected query
and only it will be
run when you click
the Execute
button
Appendix A - Getting Started with Microsoft SQL Server 2016
A.29 If needed, complete exercise 3.58. Complete Exercise 3.60 using SQL Server 2016 and
the Microsoft SQL Server Management Studio. Start the saved query name with
MSSQL- and use the default .sql file extension. The saved query name will be MSSQL-
SQL-Query-AWE-3-3-E.sql.
A.30 If you have not already done so, import the COMPUTER table from Microsoft Excel in
the SQL Server 2016 WP database as explained in the text.
A.31 Import the COMPUTER_ASSIGNMENT table from Microsoft Excel in the SQL Server
2016 WP database as explained in the text. How should this table be linked to the
EMPLOYEE table and the COMPUTER table by foreign keys? Be sure to include these
foreign keys in your final COMPUTER_ASSIGNMENT table structure when you create it
in SQL Server 2016.
To Import the COMPUTER_ASSIGNMENT table:
1. In the Microsoft SQL Server Management Studio, expand the WP database.
Appendix A - Getting Started with Microsoft SQL Server 2016
4. On the Welcome to SQL Server Import and Export Wizard page, click the Next button
to display the Choose a Data Source page.
5. On the Choose a Data Source page select Microsoft Excel as the data source.
6. On the Choose a Data Source page, browse to the location of the Microsoft Excel file,
select Microsoft Excel 2007-2010 as the Excel version (there is a glitch if Excel 2013 or
Excel 2016 is used) from the drop-down list, and make sure the check box for First row
has column names is checked.
Click the Next
button
Click the Next
button
Select Microsoft
Excel
Browse to the
Excel file
Select Microsoft
Excel 2007-2010
Make sure check
box is checked
Appendix A - Getting Started with Microsoft SQL Server 2016
7. Click the Next button to display the Choose a Destination page, and select SQL Server
Native Client 11 as the destination. The WP database values are automatically supplied,
and there is nothing to change.
8. Click the Next button to display the Specify Table Copy or Query page. The defaults are
correct here.
9. Click the Next button to display the Select Source Tables and Views page, and check the
‘COMPUTER_ASSIGNMENT$’ check box in the Source column. The table name
[dbo].[COMPUTER_ASSIGNMENT$] is generated and displayed in the Destination
column. This is the name we will use for the temporary table in the WP database.
Select SQL
Server Native
Client 11.0
Click the Next
button
Click the Next
button
Select SQL
Server Native
Client 11.0
Select
‘COMPUTER_ASSIGNMENT$’
