In this hands-on project, you will install a new SQL Server named instance, called SQLSERVERHOA, on your computer. You may complete the installation using either the installation wizard or the command prompt.
1.Your installation should include only the database engine and Integration Services components. Add your local user account to the SQL System Administrators group for the SQLSERVERHOA named instance so you can administer it after the installation is completed. Keep all other default settings during the installation process. Once you have successfully completed the installation, save a copy (screenshot) of the summary.txt located in C:Program FilesMicrosoft SQL Server110Setup BootstrapLog.
2.Verify that the Windows services for the database engine and Integration Services have been successfully installed using the SQL Server Configuration Manager. Take a screen shot of this step.
For this hands-on project, you will use the SQL Server named instance SQLSERVERHOA you created for the Module 1 Critical Thinking Assignment. The objective of this assignment is to configure and test client/server connectivity using a variety of network protocols that are supported by SQL Server 2012. Document your work by taking a screen shot at the end of each step.
1.Begin by ensuring that the Shared Memory and TCP/IP protocols are enabled for your instance using the SQL Server Configuration Manager. Configure the TCP/IP server listener to use the static TCP port 4459.
2.Use SQLCMD to test connecting to the SQLSERVERHOA named instance using Shared Memory.
3.Use SQLCMD to test connecting to the SQLSERVERHOA TCP/IP listener on TCP port 4459. Use the 127.0.0.1 loopback address to simulate the network.
For this Critical Thinking Assignment, you will use the SQL Server named instance SQLSERVERHOA you created in the Module 1 Critical Thinking Assignment. The objectives of this activity are to configure two new user databases and to create new tables and a foreign key relationship.
1.Use SQL Server Management Studio to connect to the SQLSERVERHOA instance. Create a new user database with the name HandsOnOne using the New Database dialog box from Object Explorer. Use the default database settings with the following exceptions:
2.Use Object Explorer to generate a CREATE DATABASE script from the HandsOnOne database in a new Query Editor window. Change the name of the database in the SQL script to HandsOnTwo and modify the data file initial size to 35 MB. Execute the SQL script to create the database named HandsOnTwo. Document this step by saving a copy of the SQL Script, and after executing the command, take a screenshot of the Query Editor window, showing that the query completed successfully.
3.Using either Object Explorer or Query Editor, set the recovery mode for the HandsOnOne database to full. Set the recovery mode for the HandsOnTwo database to simple. Document this step by taking a screenshot of the options page in the database properties window for each database.
4.Using Object Exploreror by running a SQL command in the Query Editor windowrename the HandsOnTwo database to HandsOnTwo_Delete.
5.Execute a SQL command to DROP the HandsOnTwo_Delete database. After executing the SQL command, document this step by taking a screen shot of the Query Editor window to show that it completed successfully.
6.Using Object Explorer, create a new table named Customer with the following column names, associated data types, and constraints:
Column name Data type Constraint
CustomerID Int Primary key
CustomerName nvarchar(40) Not null
CustomerAddressID Int Not null
Take a screenshot of the Table Designer window to document this step.
7.Using Query Editor, construct and execute a SQL command to create a new table named Address with the following column names, associated data types, and constraints:
Column name Data type Constraint
AddressID Int Primary key
Street nvarchar(50) Not null
City nvarchar(40) Not null
State char(2) Not null
ZipCode nvarchar(10) Not null
After executing the SQL command, document this step by taking a screenshot of the Query Editor window to show that the query completed successfully.
8.Using Object Explorer, create a foreign key relationship between the Customer and Address tables. The AddressID of the Address table is the primary key, and the CustomerAddressID of the Customer table is the foreign key. After creating the key, view the dependencies of the Address table in Object Explorer and take a screenshot to document this step.
This Critical Thinking Assignment requires you use the SQL Server instance named SQLSERVERHOA, and the HandsOnOne database and tables you created in Modules 1 and 3, respectively. The objective of this activity is to hone your skills at manipulating data using SQL DML statements. Use the Query Editor throughout this activity.
1.Construct and execute INSERT SQL statements to add the sample data in the following tables to the Customer and Address tables in the HandsOnOne database:
Additional Sample Data for Customer Table
CustomerID CustomerName CustomerAddressID
1 Western Supply Company 1
2 Nick Harper 3
3 Alice Harper 3
4 Abacus Consulting 4
Additional Sample Data for the Address Table
AddressID Street City State Zip Code
1 2400 Broadway Drive Missoula MT 59802
2 320 21st Street Billings MT 59101
3 439 Skyline Blvd Denver CO 80002
4 56 Park Avenue New York NY 10001
2.Construct and execute a SQL query to list all customers with their corresponding cities and states. The list should be sorted in ascending numerical order by ZIP code, followed by customer name alphabetically. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
3.Construct and execute a SQL Query to list the Street, City, State, and Zip Code of all addresses that do not have a customer associated with them. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
4.Construct and execute a SQL query to count the number of customers in each state. The list should be ordered by the number of customers in descending order, then by zip code in ascending order. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
5.Construct and execute a SQL query to change Alice Harpers address to 320 21st St, Billing, MT 59101. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.
6.Construct and execute a SQL query to list the full names of all customers who have Harper in their name. Take a screenshot of the Query Editor after you have executed this SQL command to show that it was completed successfully.