Leveling Up with SQL: Advanced Techniques for Transforming Data into Insights by Mark Simon

Author:Mark Simon
Language: eng
Format: epub, pdf
ISBN: 9781484296851
Publisher: Apress

~ 1096 rows ~

As it’s a price list, we’ve filtered out the NULL prices.

There are, however, certain conditions: All columns in a view must have a name, including calculated columns.

You can’t have anonymous columns as you might in a simple SELECT statement.

Column names must be unique.

This is especially important with joined tables, where column names may be duplicated.

In other words, the virtual table must conform to the rules of a real table.

To create a view, prepend the SELECT statement with a CREATE VIEW ... AS clause: /* Notes

===================================================

MSSQL: Use + for concatenation

MSSQL: Surround the CREATE VIEW with GO

Oracle: No AS for tables:

FROM books b JOIN authors a ON ...

=================================================== */

-- GO

CREATE VIEW aupricelist AS

SELECT

b.id, b.title, b.published,

coalesce(a.givenname||' ','')

|| coalesce(othernames||' ','')

|| a.familyname AS author,

b.price, b.price*0.1 AS tax, b.price*1.1 AS inc

FROM books AS b JOIN authors AS a ON b.authorid=a.id;

-- GO

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.