Resolved SQL Help using specific statements listed in forum

[jag250]

USing theses Statements

• • no Table SELECT
  • SELECT FROM
  • WHERE
  • AND, OR, NOT, IN, LIKE
  • GROUP BY
  • HAVING (which also implies a join and a group by)
• MC/TF portion
  • all multiple choice and True False

1. Show ship countries and a count of orders by Ship Country. Include orders placed in 1997 and shipped to Belgium, Mexico, and Poland.

2. Show year of order (YEAR(OrderDate)) and a count of orders by year of order. Only include orders after 1997. Include years with more than 40 counts of order.

3. Show ShipCountry, ShipCity, and the average Freight by by ShipCountry and then ShipCity.

4. Show a list of CustomerIDs, OrderID, ShipCountry and year of shipping (YEAR(ShippedDate)). Only include orders in 1996 and CustomerID begins with the letter E.

5. Show a list of CustomerIDs, OrderID, and Freight charges. Only include customers where their freight charges were over $30. Order by CustomerID and then OrderID. The freight charges shown in the table is in dollar. Create an additional column and show the freight charges in euros (1 Dollar = 0.86Euro).

 

[Skydiver]

As we've told you before, we are not a code writing service. If you show us what you have done so far, and tell us what problems you are running into, we can try to help you. Just dumping your assignments requirements on us and hoping for someone to just give you an answer is not a very good strategy for this particular forum.

 

[jag250]

As we've told you before, we are not a code writing service. If you show us what you have done so far, and tell us what problems you are running into, we can try to help you. Just dumping your assignments requirements on us and hoping for someone to just give you an answer is not a very good strategy for this particular forum.

This is not an assignment. I'm just using different practice sheets my teacher gave me and looking for examples based on these questions.
And for your information, I do all of my work myself and I have pieces done their just not correct. So I'm looking for someone who can give me an example based of these question and explain it.

 

[Skydiver]

The point of an exercise is for you to learn and practice your skills.

I've only put two and two together just now. If you are taking programming classes just to fulfill your MIS degree requirement, I can sort of see why you aren't really treating programming as a necessary skill that requires putting in the time to go in depth into it. My sister got her MIS degree many years ago and just did her programming courses in survival mode, rather than as a calling or vocation.

Kudos to.you for taking time to do extra exercises. Now time to work on them... Put in your best effort and present what you have. We can help guide you to a solution.

 

[jag250]

The point of an exercise is for you to learn and practice your skills.

I've only put two and two together just now. If you are taking programming classes just to fulfill your MIS degree requirement, I can sort of see why you aren't really treating programming as a necessary skill that requires putting in the time to go in depth into it. My sister got her MIS degree many years ago and just did her programming courses in survival mode, rather than as a calling or vocation.

Kudos to.you for taking the time to do extra exercises. Now time to work on them... Put in your best effort and present what you have. We can help guide you to a solution.

I am not an MIS major!!! And I'm only here for help because my teachers won't respond for days and I don't have time to sit and wait. Don't really care about your opinion or your sister's degree. And I am treating it as a skill, I spend 10 hours a day coding for 6 classes. So don't act like you know everything. There's a reason why I said an explanation or example. I just needed a deeper explanation of the question. I never was asking for answers.

 

[Skydiver]

Then show us the results of your coding for this exercise.

 

[jag250]

-- 1. Show ship countries and a count of orders by Ship Country. Include orders placed in 1997 and shipped to Belgium, Mexico, and Poland.
Select ShipCountry
From ShipCountry
Having COUNT(Orders) by ShipCountry
From ShipCountry
Where YEAR[OrderDate] = 1997
Group by Belgium
         Mexico,
      Poland


                
--2. Show year of order (YEAR(OrderDate)) and a count of orders by year of order. Only include orders after 1997. Include years with more than 40 counts of order.
SELECT (YEAR(OrderDate))


--3. Show ShipCountry, ShipCity, and the average Freight by by ShipCountry and then ShipCity.
SELECT ShipCountry AS [ShipCountry]
        ShipCity AS [ShipCity]
        AVG(Freight)  AS [AverageFreight]
From ORDER
Group by ShipCountry, ShipCity
Order by ShipCountry, ShipCity


---4. Show a list of CustomerIDs, OrderID, ShipCountry and year of shipping (YEAR(ShippedDate)). Only include orders in 1996 and CustomerID begins with the letter E.

SELECT CustomerIDs,
        OrderID,
        ShipCountry,
        Year(ShippedDate) AS [Year Shipped]
From Shippers

---5. Show a list of CustomerIDs, OrderID, and Freight charges. Only include customers where their freight charges were over $30. Order by CustomerID and then OrderID.
-- The freight charges shown in the table is in dollar. Create an additional column and show the freight charges in euros (1 Dollar = 0.86Euro).
SELect CustomerID,
        OrderId,
        Freight charges

 

[Skydiver]

For your first query, is ShipCountry a table, or a table column? You seem to be trying to use is both ways.

To get just the orders for those three countries, you want to add that as part of your WHERE clause, not as part of the GROUP BY. In the GROUP BY, you'll want to simply group by the ShipCountry since you have already filtered all the data to just those countries in the WHERE clause.

 

[Skydiver]

In the second query, where are your other filter criteria for selecting the years after 1997 and more than 40 orders?

 

[Skydiver]

In your 3rd query, looks like it'll work. You can trim it down some more to remove the redundant column renames.

 

[Skydiver]

In your fourth query you seem to be missing a WHERE clause to filter down to the required rows.

 

[Skydiver]

Looks like you still need to work on your fifth query. It is still missing a column, and some search, sorting, and grouping criteria.

 

[jag250]

In your fourth query you seem to be missing a WHERE clause to filter down to the required rows.

---4. Show a list of CustomerIDs, OrderID, ShipCountry and year of shipping (YEAR(ShippedDate)). Only include orders in 1996 and CustomerID begins with the letter E.
SELECT CustomerID,
OrderID,
ShipCountry,
ShippedDate
From Orders
Where ShippedDate = '1997' AND CustomerID = 'E%'
It wont run so I'm missing something....

 

[jag250]

For your first query, is ShipCountry a table, or a table column? You seem to be trying to use is both ways.

To get just the orders for those three countries, you want to add that as part of your WHERE clause, not as part of the GROUP BY. In the GROUP BY, you'll want to simply group by the ShipCountry since you have already filtered all the data to just those countries in the WHERE clause.

Select ShipCountry
From ShipCountry
Having COUNT(Orders) by ShipCountry
From ShipCountry
Where YEAR[OrderDate] = '1997' AND shipped = ('Belgium', 'Mexico', 'Poland')
(What I have so far)

 

[JohnH]

And this is still a C# forum, where's the C# code?