Thursday, November 7, 2013

ITCO331 1301A 01 ITCO331 P2 Unit 2 NorthWind Database SQL2008

Assignment Name: Unit 2 Individual Project

Using the Northwind database, write a SQL SELECT statement that will retrieve 
the data for the following questions:


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

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

8 rows returned.
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. 

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

9 rows returned.
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. 

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

4 rows returned.
Display the Order Id, ShipName, Order Date, and Freight of all orders that 
have a freight >/= 50 and 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. 

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

13 rows returned.
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 'A Location,' and the 5–9 
characters from the name 'Substring.' Order the results by the Employee Name 
using the alias. 

9 rows returned.
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. 

9 rows returned.

Preview Screen Shots:
Total Freight Ordered
                               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. 

  • This tutorial was purchased  3 time and rated A+ by students like you.
  • Posted on Feb. 24, 2013 at 04:54:58AM 
Asked: $20
Email: syedjahangirb@gmail.com to get assignment

Files in Solution:
 Preview.jpg (74K) (Preview)
 ITC0331_NorthWindDatabase_SQL2008.zip (1K)
[ 
 ITC0331_NorthWindDatabase_SQL2008.sql
 ]
Posted by at No comments:

ITCO331-1301A-01 ITCO331_P3 Unit 3 NorthWind Database SQL2008

Assignment Name: Unit 3 Individual Project

Deliverable Length: 10 SQL Statements


Details:


Write SQL statements that will retrieve the following data from a database, using Subqueries and Joins. Using the Northwind database, write a SQL SELECT statement that will retrieve the data for the following questions:
1.   For each order, display the Company Name, City, and the Country for the customer who placed the order.
o    Include the Order Date and the Required Date. Order the results by Company Name in ascending order.
o    830 rows returned.
2.   For each order, list the OrderID, Orderdate, Product Name, UnitPrice, Quantity Ordered, and Total Cost. Label the column 'Product Cost.'
o    For each product (including the discount), only show those orders that were placed in March of 1997.
o    Sort the data by the OrderID, then the Product Name.
o    77 rows returned.
3.   For each order, display the OrderID, ShipName, and the Employee First and Last Name.
o    Order the results by Employee Last Name in descending order, then by Employee First Name in ascending Order, then by OrderID in ascending order.
o    830 rows returned.
4.   Modify the first query to list the orders with customer details, but include all customers even if they have not placed an order.
o    832 rows returned.
5.   List all customers (include CustomerId and Company Name) who have placed less than 5 orders.
o    Include those customers who have placed 0 orders. Name the count field "OrderCount."
o    Order by number of placed orders in reverse order.
o    18 rows returned.
6.   Display the ProductName, and UnitPrice of all products that have a unit price larger than Tarte au Sucre.
o    Order the results by UnitPrice in descending order.
o    7 rows returned.
7.   Display the Customer Name of all customers who have placed orders in 1996.
o    Order the results by Company Name in ascending order.
o    67 rows returned.
8.   Display the OrderID of all orders that where placed after all orders placed by Bottom-Dollar Markets.
o    Order the result by OrderID in ascending order.
o    28 rows returned.
9.   List the Company Name of all U.S.-based customers who are NOT located in the same state (or region) as any of the employees.
o    Order the results by Company Name.
o    10 rows returned.
10. Display the Product Names of all products that were placed by customers in CA.
o    Order the result by Product Name in ascending order.
o    Eliminate duplicate rows in the results.
o    10 Rows Returned.


Features:
ITCO331, NorthWind, SQL Server , Alternate Queries Also Given 

Preview Screen Shots:
ITCO331-1301A-01 Unit 3 SQL Queries
Preview SQL Script.


                                                           Query 5 with 18 rows
Query 10 with 10 rows




Asked: $20

Email: syedjahangirb@gmail.com to get assignment

Files in Solution:
 Northwind_DB_Queries.zip (1K)
[ 
 Northwind_DB_Queries.sql
 ]
 ITCO331 IP 3.doc (31K) (Preview)
Posted by at No comments:

ITCO331-1301A-01 ITCO331_P4 Unit 4 NorthWind Database SQL2008

ITCO331_P4 Unit 4 IP Northwind Database SQL Statements

Download Now Here:
http://www.askassignment.com/product/unit-4-itco331-1301a-01-northwind-database

Features
7 DML statements (3 INSERT, 2 UPDATE, 2 DELETE); 7 SELECT statements
Details:           

Question
Write SQL DML statements that will modify data from within a database.

Using the Northwind database, write a SQL DML statement that will modify the data. Base it on the following requirements:

    Using the Northwind database, write a script to insert the following data:
        1 new Category
        Category Name = Fast Food
        Category Description = Quick and Easy Meals
        Provide a SELECT statement to show that the data was inserted.
    Add the following products for your new Fast-Food category.
        Product Name = French Fries
        Supplier Name = Ma Maison
        Category Name = Fast Food
        QuantityPerUnit = 20 Sticks
        UnitPrice = 4.99
        UnitsInStock = 10
        UnitsOnOrder = 25
        ReorderLevel = 10
        Disabled = 0
        Product Name = Hamburger
        Supplier Name = Grandma Kelly's Homestead
        Category Name = Fast Food
        QuantityPerUnit = 1 Patty
        Unit Price 6.99
        UnitsInStock = 0
        UnitsOnOrder = 50
        ReorderLevel = 5
        Disabled = 0
        Provide a SELECT statement to show that the data was inserted.
    In an attempt to liquidate some products, provide a script to set all products in the Confections category that have more than 50 units in stock to a price of $10, and disable them.
        2 records should be affected.
    Change the discount in the Order Details table on all orders of products of an ordered quantity of more than 50 to that of the largest discount ever given on any product.
        159 records should be affected
    You have decided against the new Fast-Food category. Provide a SQL statement to delete the Fast-Food category as well as any products assigned to that category.


*Describe optimization and partitioning, and distributed databases with screenshots

Preview Solution:

Sql Script Preview

  • This tutorial was purchased  1 time and rated no rating by students like you.
  • Posted on Mar. 10, 2013 at 09:37:50AM 

Asked: $20

Email: syedjahangirb@gmail.com to get assignment

Files in Solution:

Files in Solution:
ITCO331-1301A-01_7_DML_7_SELECT_SOLUTION.sql



Posted by at No comments:

ITCO331_P2, ITCO331_P3, ITCO331_P4 Consolidated NorthWind Database

ITCO331-1301A-01 ITCO331_P2 Unit 2 NorthWind Database SQL2008


TCO331-1301A-01 ITCO331_P3 Unit 3 NorthWind Database SQL2008


ITCO331-1301A-01 ITCO331_P4 Unit 4 NorthWind Database SQL2008


Download Consolidated Assignment from below link:

  • This tutorial was purchased 5 time and rated A+ by students like you.
  • Posted on Mar 10, 2013 at 1:23:58PM 
Preview Screen Shot:

                                                  Query 10 with 10 rows,  ITCO331_P3 Unit 3


ITCO331_P2 Unit 2 Preview SQL Script 


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

Execution Result Output


  Query 5 with 18 rows,  ITCO331_P3 Unit 3


Asked: $55

Email: syedjahangirb@gmail.com to get assignment

Files in Solution:
 Consolidate.zip (467K)
[ 
 Consolidate/ITCO331_P2/ITC0331_P2.zip
 Consolidate/ITCO331_P2/ITCO331 Unit 2 IP Doc.docx
 Consolidate/ITCO331_P2/Preview.jpg
 Consolidate/ITCO331_P2/Preview_09_09_Rows.jpg
 Consolidate/ITCO331_P2/Preview_10_09_Rows.jpg
 Consolidate/ITCO331_P3/ITCO331 Unit 3 IP.docx
 Consolidate/ITCO331_P3/Northwind_DB_Queries.zip
 Consolidate/ITCO331_P3/Preview_10_10rows.jpg
 Consolidate/ITCO331_P3/Preview_5_18rows.jpg
 Consolidate/ITCO331_P4/ITCO Unit 4.docx
 Consolidate/ITCO331_P4/ITCO331-1301A-01_7_DML_7_SELECT_SOLUTION.zip
 ]
Posted by at No comments:
Subscribe to: Posts (Atom)