Describe the difference between the CHAR and VARCHAR data types.

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): I have done with the Installment

Put the header “Part 1” in your assignment Word document.

I have attached the inst pubs.2012.sql file :

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.)

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?
Explain why the following query will give an error:

SELECT empid, first name, last name, DATE PART(year, hiredate) AS Year Hired

FROM HR.Employees

WHERE Year Hired = 2004;
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): a screenshot is needed.
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.)

Query the HR.Employees table. The result set column headers should be exactly these: Employee ID, Last Name, and First Name.
Return the empid, first name, last name, and city for the employee with empid 9.
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.
Retrieve the product name and unit price of all products that have unit price between $10 and $15. Use the BETWEEN keyword.
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.
List the category name and description for all product categories that contain the character string ‘ee’ anywhere in the description. Use the LIKE keyword.
Retrieve the order id, order date, employee id, and ship (to) city of all orders for which the shipped date is unknown.
[No particular database.] Write a query that returns the number of days old you are. Use the DATEDIFF function.