SQL-Data Management Studio

Display the character constant 'Greeting' with a column heading of 'Hello.'

Hint:

· refer to pages 88 - 89 (SQL Server 2012 book)

· your SQL statement will not contain a FROM clause.

· 1 row returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

Display all data from the categories table without specifying the names of the table columns. Order the results by CategoryName.

Hint:

· Refer to pages 88 - 89 (SQL Server 2012 book)

· 8 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

Display all employees' names, last name first, and then first name, hire date and home phone number. Order the results by employee name, last name first, and then first name.

Hint:

· Refer to pages 88 - 89 (SQL Server 2012 book)

· 9 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

Modify query/task #3 so that the column headings are as follows: 'Last Name,' 'First Name', 'Date of Hire,' and 'Home Phone Number.'

Hint:

· Refer to pages 92 - 93 (SQL Server 2012 book)

· 9 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

Display the product name and quantity per unit, units in stock, and unit price for all products that have a unit price greater than $50. Order the results by unit price in descending order.

Hint:

· Refer to pages 104 - 105 (SQL Server 2012 book)

· Use the PRODUCTS table.

· 7 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

Display the Name of all U.S.-based suppliers. Order the results by Name in ascending order.

Hints:

· Refer to pages 104 - 105 (SQL Server 2012 book)

· Use SUPPLIERS table

· 4 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

Display the Order Id, ShipName, Order Date, and Freight of all orders that have:

· freight greater or equal to 50 and less than or equal to 100

· a ship country of Austria, Brazil, or France

· and a Ship City that starts with an 'S.'

Use: BETWEEN, IN, and LIKE for the WHERE clause conditions. 

Order the results by ShipName in ascending order.

Hints:

· Refer to pages 104 - 113 (SQL Server 2012 book)

· Use ORDERS table

· 13 rows returned.

· My suggestion to you is don't start off by attempting to boil the ocean (i.e. doing too much at one time). Start small and build to it.

· First get this working.......Display the Order Id, ShipName, Order Date, and Freight of all orders

· then add this and get it working....a freight >= 50 and <= 100 (Use: BETWEEN, IN, and LIKE for the WHERE clause conditions. )

· then add this and get it working....a ship country of Austria, Brazil, or France (Use: BETWEEN, IN, and LIKE for the WHERE clause conditions)

· then add this and get it working....and a Ship City that starts with an 'S'

· then add this and get it working....Order the results by ShipName in ascending order.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

Rewrite query/task 7 to remove the use of BETWEEN and IN, and replace with the equivalent use of =, <, >, </=, >/= and AND and OR expressions.

Hints:

· Refer to pages 104 - 113 (SQL Server 2012 book)

· 13 rows returned.

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

Select:

· the Employee's First Name, concatenated to the Last name, separated by a space. Call the column 'Employee Name,'

· the length of this employee name Length,

· the location of the first 'a' in the name. Call the column 'A Location,' and

· the 5–9 characters from the name 'Substring.'

Order the results by the Employee Name using the alias.

Hints:

· Refer to pages 262 - 265 (SQL Server 2012 book)

· Use EMPLOYEES table

· 9 rows returned

· See the Appendix of this document for more hints

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

For each customer:

· show the number of orders placed, and 

· the largest, smallest and total freight ordered.

Only show those customers who have placed more than 15 orders.

Hints:

· Refer to pages 160 - 167 (SQL Server 2012 book)

· Use ORDERS table

· 9 rows returned

{paste your SQL text here}

{paste your screen shot that proves your SQL successfully executed}

Refer to week 2’s chat for a discussion on query #9.