IT526-1 Unit 1 Assignment | Complete Solution

Unit 1 Assignment

Course outcomes addressed in this activity:
IT526-1: Create queries to retrieve and modify data.
Project Instructions:
Single-line-spacing is preferred to APA format.
Put your name, course, unit number, and date at the beginning of your Assignment Word document.
Part 1. Install SQL Express. (30 points)
Put the header "Part 1" in your assignment Word document.
Install SQL Express, or connect to the Toolwire desktop.
If you are installing SQL Express on your own computer, follow the instructions in the "IT526.Installation of SQL 2012 Express.Supplement" document in Doc Sharing. The referenced "Installation" document is the "IT526.Installation of SQL 2102 Express.May2013" document in Doc Sharing.
Create the pubs sample database. See the instructions in the "IT526.Installation … May2013" document, page 10, item 6. If you are using the Toolwire desktop, a copy of the instpubs.2012.sql file should already be in the U:\IT526 folder. (Creating the AdventureWorks database is optional.)
Copy the screen image per the specifications on the last page of the "IT526.Installation of SQL 2012 Express.May2013" document and paste the image into your assignment document. Be sure the Databases folder in Object Explorer is expanded to show that the pubs database has been created. [If you have created pubs but it does not show, refresh the Databases folder.] Your screen image should meet the three specifications given on the sample image.
Part 2. Questions. (30 points)
Put the header "Part 2" in your assignment document. Letter your answers to correspond to the letter of the question you are answering. (Please enter your own letters; do not use Word's bulleted lettering.)
a.    Describe the difference between the CHAR and VARCHAR data types. When would you use VARCHAR and not CHAR? If a length is not specified, what is the default length for a table column or for a variable?
b.    Explain why the following query will give an error:
SELECT empid, firstname, lastname, DATEPART(year, hiredate) AS YearHired
FROM HR.Employees
WHERE YearHired = 2004;
c.    You want to list all orders that were placed less than one month ago. Why is WHERE clause (i) better than WHERE clause (ii)? [Hint: See the first two paragraphs on pg. 65.]
 
Part 3. Queries. (40 points)
Put the header "Part 3" in your assignment Word document.
All queries use the TSQL2012 database unless otherwise indicated.
Data types of values used in the WHERE clause should match the data type of the column in the predicate. Character and date values should be delimited by single quotes; numeric values should not be delimited by single quotes.
Label each query with its letter. Copy the query from the SQL Server Management Studio query window into your assignment Word document. Then copy the results into the Word document. If the result set has more than 10 rows, you need only show the first five rows of the result set, plus an indication of how many rows were returned. (See the "Unit1.Sample Queries" document in Doc Sharing, for how to copy a result set with headers into Word.)
a.    Query the HR.Employees table. The result set column headers should be exactly these: Employee ID, Last Name, and First Name.
b.    Return the empid, firstname, lastname, and city for the employee with empid 9.
c.    Return a list of employee id's, last names, and first names for employees hired on or after January 1, 2004. Use the language-neutral date form recommended on p. 70.
d.    Retrieve the product name and unit price of all products that have unit price between $10 and $15. Use the BETWEEN keyword.
e.    Retrieve the product id, product name, supplier id and unit price of all products that are supplied by suppliers 2, 3, 16, or 19. Use the IN keyword. Return the results in order of largest to smallest unit price.
f.    List the category name and description for all product categories that contain the character string 'ee' anywhere in the description. Use the LIKE keyword.
g.    Retrieve the order id, order date, employee id, and ship (to) city of all orders for which the shipped date is unknown.
h.    [No particular database.] Write a query that returns the number of days old you are. Use the DATEDIFF function.
Review the grading rubric below before beginning this activity.
100-point Assignment grading rubric
Project Requirements / criterion    Points Possible    Points earned by student
1.Screen image of SQL Server Management Studio:
Is in restored state, not full screen (10)
Has no excessive white space; print is large enough to read easily (10)
Pubs database is created (5)
Shows the full connection string (5)    0–30    
2.Questions a, b, and c are worth 10 points each. Questions should be completely and clearly answered; answers should have correct spelling and grammar.    0–30    
3.Queries are worth 5 points each; 4 points for the query and 1 point for the result set.    0–40    
Total (Sum of all points)         
Points deducted for spelling, grammar, and/or APA errors.        
Adjusted total points