Creating Views: Changing column names
-
I'm trying to change a name in a view that I'm creating. I can't seem to find out how to do it. I have everything set in this question except the change of the column name. Can anyone help. Here is what I have so far.
Using the Northwind database (table orders), write a single script that completes the following tasks:
1. Create a view showing every order that was shipped to
USE Northwind
Go
DROP VIEW Spain_Orders
GO
CREATE VIEW Spain_Orders
AS
SELECT *
FROM Orders
WHERE ShipCountry = '
This returns all orders that are going to
-
Dennis,
try this:
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = N'Spain_Orders')
DROP VIEW Spain_Orders
GO
CREATE VIEW Spain_Orders
AS
SELECT
[Order ID],
[Customer],
[Employee],
[Order Date],
[Required Date],
[Shipped Date],
[Ship Via],
[Freight],
[Ship Name],
[Ship Address],
[Ship City],
[Ship Region],
[Ship Postal Code],
[Ship Country] as [DestinationSpain]
FROM Orders
WHERE [Ship Country] = 'Spain'
GO
to change a colum name, you need to use an alias for that column name. The syntax is allowed in the SELECT statement: ... ColumnName As AliasName
By the way, you could this the alias as the heading of the column in the SELECT statement or by creating the view as you want to do.
Ben Lopez
-
Thanks Ben,
I'm going to get started on it right now.
-
Hey Ben,
You're the man
It works like a charm
-
I have another one that I'm trying to solve:
Create another view that returns the name of each country and the number of orders shipped to that country. Name the number of orders column "NumOrders.” Include code that checks if the view already exists. If it does, it should be dropped and re-created.
Can any one help me on this one
-
Are these questions from a homework assignment ? Use of Northwind and basic nature of the questions certainly looks that way ...
-
The request is to aggregate based on the country column, and each row in the Order table will be counted as one. We can do this readily using the GROUP BY and COUNT(*) function.
In a more generic setting, the row could be counted as more (or less) than one depending on what the result should be. Or it could be counted based on the value of some other expression derived from other tables. We could use SUM(something), etc.
Anyway, try this one.
IF EXISTS (SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = N'CountryOrderCount')
DROP VIEW CountryOrderCount
GO
CREATE VIEW CountryOrderCount
AS
select ShipCountry as Country,
Count(*) as NumOrders
from Orders
Group by ShipCountry
GO
-
Thanks Ben
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply