Apply Your Knowledge

Exercises

1.1 Using the Enterprise Manager

In Exercise 1.1, you explore the Enterprise Manager interface to become comfortable with its layout.

Estimated Time: 5 minutes.

Open the Enterprise Manager and view the structure of the interface by choosing Start, Programs, Microsoft SQL Server.
Expand the SQL Server Group and Computer name by depressing the plus sign to the left of the name .
Expand databases and you should see the listing of system databases: Master , Model , Msdb , Tempdb , and the sample databases Northwind and Pubs .

You may also see other user databases that have been previously created and the system's Distribution database if replication has been enabled.
Expand the Northwind database and view the variety of database object storage containers.
Click on tables to show the user and system tables in the right view pane.
Click on the Type column to sort the tables according to type. (You may have to click twice so that the user tables are all located at the top of the list).

Notice how easy it is to adjust the objects and place them into a desired sequence by name, owner, type, or creation date.

1.2 Using the Query Analyzer

In Exercise 1.2 you will run a couple of basic queries from the Query Analyzer and explore its interface.

Estimated Time: 5 minutes.

Open the Query Analyzer by choosing Start, Programs, Microsoft SQL Server.
Select OK to connect to the local server and supply login credentials if necessary.
On the left side of the screen is the object browser that enables the user to quickly look up names and syntax while creating and editing scripts.
Select the Northwind database from the Database drop-down list box in the toolbar.
Enter the following T-SQL query: SELECT * FROM Customers .
Run the query by pressing F5. (Alternatively, you can also select the Execute Query button from the toolbar.)

You can save the query if you want. It is also possible to save the query results or run the results straight to an output file, which you can do by selecting Results to Filein the Execute Mode drop-down list box on the toolbar, prior to running the query.

1.3 Creating a Database Diagram

In Exercise 1.3, you will draw a database diagram from the sample database, Northwind .

Estimated Time: 5 minutes.

Open the Enterprise Manager and view the structure of the interface by choosing Start, Programs, Microsoft SQL Server.
Expand the SQL Server Group, Server, Databases, and Northwind database and select the Diagrams container.
Right-click and select New Database Diagram. Select Next to bypass the introductory screen.
Select the Add Related Tables Automatically check box and leave the number of levels at the default of 1.
Select the Customers table and select Add. Select Next and Finish to draw the diagram.

Database diagrams are useful when you want to provide documentation about a database system. The diagrams show relationships and have a number of other useful features you can use to augment a diagram.

1.4 Using Books Online

In Exercise 1.4, you can explore Books Online and get used to using it as a learning tool.

Estimated Time: 5 minutes.

Open Books Online by choosing Start, Programs, Microsoft SQL Server.
Select the Contents tab and expand the Getting Started book.
Select the Getting Started book and read through the table to review the hyperlinks available.
Select the Index tab and enter the word SELECT .
Double-click Examples from the list of subheadings .
Select the Favorites tab and click Add to place SELECT Examples into the listing of Favorites.

You are sure to add and remove topics from your Favorites list on a regular basis as you study the product.

Review Questions

1:	How is data queried from SQL Server?
A1:	Data can be read from a SQL Server database using a variety of techniques. The most common would be a front-end application designed with a Windows or Internet interface. Visual Basic, FrontPage, or Visual Interdev could be used to design such a front-end application. You can also query data using the OSQL command-line tool or use the Query Analyzer.
2:	Which versions of SQL Server have operating system requirements? What are these requirements?
A2:	It probably goes without saying that each version has a specific focus. The CE version, of course, is for use on handheld computers that use Windows CE version 2.11 or later. The Developer Edition installs on Windows 2000 Professional and Windows NT Workstation 4.0, as well as on Microsoft Windows NT 4.0 servers and Windows 2000 servers. Personal Edition runs on non-server operating systems including Windows 2000 Professional, Windows NT Workstation 4.0, Windows Millennium Edition, and Windows 98, as well as servers.
3:	What percentage of answers to potential exam questions can be obtained from within Books Online?
A3:	The answer here is likely to be very close to 100%. SQL Server Books Online is an immense and complete SQL Server resource. Just about any question you have or database technology you would like to use or learn can be found within the application.
4:	How is the `Master` database used?
A4:	The `Master` database represents a given SQL Server installation. The `Master` database should be considered as important or more important than the most mission-critical database on the server. The `Master` database contains information for all databases and other objects stored on the server.
5:	What is the purpose of the `Model` database?
A5:	The `Model` database acts as a template for each newly created database. Every time you create a database, a copy of the objects and settings stored in the `Model` is used as a basis for the newly created database.

Exam Questions

1:	You are the chief database administrator for a large manufacturing company. You need to install Microsoft SQL Server Enterprise for test purposes and have moderate storage capabilities. Which of the following systems suit your needs without requiring you to perform any alterations? Pentium 133, 256MB RAM, IE 5 Pentium 200, 64MB RAM, IE 4 Pentium 400, 128MB RAM, IE 3 Pentium 400, 32MB RAM, IE 5
A1:	B. To install SQL Server you need a minimum of a Pentium 166, 64MB of RAM (though more is recommended) and Internet Explorer 4 or above. See the section "Installation Requirements" for more information.
2:	You are the database administrator for a small private educational institution. You would like to use SQL Server as a gateway to a variety of data sources that have been used for a number of different applications. As a primary goal you would like to get a copy of all data stored on the SQL Server. What technologies could be used to solve this problem? Select all that apply. OLE-DB ANSI ISO ODBC Analysis Services SQL Profiler
A2:	A, D. OLE-DB and ODBC are industry-standard technologies. They supply the standards for drivers that are supplied to allow data to be read from an underlying data source. ODBC (open database connectivity) is a mature interface supported by almost all database engines. OLE-DB is a set of driver APIs that has growing usage and allows for access to data in a generic form, as well. See the section "Executing a Program" for more information.
3:	You are preparing a computer that is going to serve as a Windows 2000 domain controller. You would also like to install SQL Server for a database that will be used in your office. What is the preferred installation to use for these operations? Use one machine as a domain controller and a second as the database server. Install SQL Server first and then promote the machine to a domain controller. Promote the Windows 2000 machine to be a domain controller and then install SQL Server. A Windows 2000 domain controller cannot have SQL Server installed.
A3:	A. You should always avoid installing SQL Server on a domain controller. Although the configuration is possible, there are serious security implications in having an application server on the same machine as a network's security context. For more information, see "SQL Server 2000 as a Resource."
4:	You are implementing a database for a chain of variety stores that share a central warehouse. Each of the stores tracks sales information and places orders from the central warehouse once a week. The server will use replication, indexed views, and analysis services for a variety of procedures. What version of SQL Server should be installed? SQL Server 2000 CE SQL Server 2000 Standard SQL Server 2000 Personal SQL Server 2000 Developer SQL Server 2000 Enterprise
A4:	E. To use indexed views you must have either the Developer or Enterprise Editions. Because this server is for a production environment, not a test setup, the only choice is the Enterprise Edition. For more information see the section "SQL Server Versions."
5:	You are a SQL Server database administrator preparing to install a series of laptops that will be used by your company's traveling sales force. The laptops are already loaded with Windows 98 and need to have SQL Server installed so that the salespeople can record orders through a custom Visual Basic application. When the salespeople return to the office, they execute another process to upload the information to a primary production server. What version of SQL Server do the laptops require? SQL Server 2000 CE SQL Server 2000 Standard SQL Server 2000 Personal SQL Server 2000 Developer SQL Server 2000 Enterprise
A5:	C. The Personal Edition is probably most appropriate for this type of application because it is suited to mobile users that require SQL Server for data storage. However, if the application is using replication as a means of moving data from the laptops to the servers, it should be noted that the Personal Edition allows only for the subscriber options to be set. It cannot act as a publisher. For more information see "SQL Server Versions."
6:	A large shipping company uses a dual processor SQL Server to track load information for a fleet of transport vehicles that handle shipments throughout North America. The data being collected is shared with other vendors . This company requires a technology that will provide a data structure and formatting rules, and that will be easily transferable between applications. Which technology is best suited for this structure? HTML IIS XML Replication Triggers
A6:	C. XML, now supported through a number of new features, provides a mechanism where the data can be transmitted from one application to the other while maintaining the data structure and other formatting provided by XML schemas and style sheets. For more information, refer to "Features of SQL Server 2000."
7:	You are working on a database implementation in a production environment. You would like to perform analysis on the server hosting the database. You need to get detailed information on the types of queries being performed and the locking effects of all operations. Which tool should you use? Query Analyzer SQL Profiler Books Online Analysis Manager Enterprise Manager
A7:	B. To perform analysis of this type, you would use the Profiler to gather detailed server-wide information. The Query Analyzer is more appropriate to analyze and improve upon the performance of singular queries and the Analysis Manager is used to configure data warehousing. For more information see the section "Features of SQL Server 2000."

Exercises

1.1 Using the Enterprise Manager

1.2 Using the Query Analyzer

1.3 Creating a Database Diagram

1.4 Using Books Online

Review Questions

Exam Questions

Suggested Readings and Resources