VIEWS - HELP PLEASE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

VIEWS - HELP PLEASE

Author

Topic

anajjar
Starting Member

4 Posts

Posted - 2013-12-10 : 21:15:12

Hello, I am down to my two last questions and having a lot of troubles with, please look over:

-Create an updatable view named VendorAddress that returns the VendorID, both address columns, and the city, state, and zip code columns for each vendor. Then, write a SELECT query to examine the result set where VendorID=4. Next, write an UPDATE statement that changes the address so that the suite number (Ste 260) is stored in VendorAddress2 rather than in VendorAddress1. To verify the change, rerun your SELECT query.

CREATE VIEW VendorAddress
return VendorID, VendorAddress1, VendorAddress2, VendorState, VendorZip, VendorCity
SELECT VendorID = 4
UPDATE

I THINK THE FIRST PART IS WRONG AND SECOND PART HAVE NO CLUE ABOUT??

-Write a SELECT statement that selects all of the columns from the catalog view that returns information about foreign keys. How many foreign keys are defined in the AP
database?

SELECT * CatalogView
WHERE

NOT SURE HOW TO APPROACH THIS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-11 : 05:23:46

it should be as below


--Creating the view . i'm assuming the table name so put correct name below
CREATE VIEW VendorAddress
AS
SELECT VendorID, VendorAddress1, VendorAddress2, VendorState, VendorZip, VendorCity
FROM VendorTable
GO

--select query to examine results
SELECT * FROM VendorAddress WHERE VendorID = 4

--write an UPDATE statement that changes the address so that the suite number (Ste 260) is stored in VendorAddress2 rather than in VendorAddress1
UPDATE VendorAddress 
SET VendorAddress2 = 'Ste 260',
VendorAddress1 = REPLACE(VendorAddress1,'Ste 260','')
WHERE VendorAddress1 LIKE '% Ste 260 %'

for second part 
--Write a SELECT statement that selects all of the columns from the catalog view that returns information about foreign keys
SELECT * FROM sys.foreign_keys

http://technet.microsoft.com/en-us/library/ms189807.aspx

--How many foreign keys are defined in the AP
database?
select * from AP.sys.foreign_keys

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources