INPUT/OUTPUT: SQL> SELECT DISTINCT COMPANY...

Odnośniki

Smutek to uczucie, jak gdyby się tonęło, jak gdyby grzebano cię w ziemi.
class ColorPick{ static void Main(){Color favorite = SelectFavoriteColor();RespondToFavoriteColor(favorite);}static Color...
To define the Dreamweaver remote folder: 1 In the Advanced tab of the Site Definition dialog box, select Remote Info from the Category list...
INTERSECT INTERSECT INTERSECT returns all the common elements of two SELECT statements...
displaying output 3-9 dim argument for cat 19-7 distance between nodes 16-22 dimensions docking windows in desktop 2-10 deleting...
EXT REF1 SELECT lub 1106 EXT REF2 SELECT – na COMM, COMM+AI1 lub COMM*AI1...
Jak burze,Strącające już!Nad dołemDrżą potępieni!-By nie paśćW tę przepaść,Wspierają się społem!Jak łańcuch z pierścieni,Łono na...
17
go zupełnie sieć czerwonych żyłek na przywiędłych policzkach, którym smutki i jakieś dolegliwości dały ton ceglasty...
22Interpretacja tych danych nie jest łatwa...
I znów garść analogii spoza literatury pięknej, fikcji: Powracający z wojska urlopnicy [...

Smutek to uczucie, jak gdyby się tonęło, jak gdyby grzebano cię w ziemi.

STATE, COUNT(BILLS.*) FROM BILLS, COMPANY
2 GROUP BY COMPANY.STATE
3 HAVING BILLS.NAME = COMPANY.NAME;
STATE COUNT(BILLS.*)
GA 2
FL 3
CA 2
TX 1
SC 1
NJ 1
6 rows selected.
Now that you have successfully returned two-thirds of the desired result, you can add
the final required return value. Use the SUM function to total the amount of money sent to each state.
INPUT/OUTPUT:
SQL> SELECT DISTINCT COMPANY.STATE, COUNT(BILLS.NAME),
SUM(BILLS.AMOUNT)
2 FROM BILLS, COMPANY
3 GROUP BY COMPANY.STATE
4 HAVING BILLS.NAME = COMPANY.NAME;
STATE COUNT(BILLS.*) SUM(BILLS.AMOUNT)
GA 2 250
FL 3 445
CA 2 275
TX 1 35
SC 1 200
NJ 1 35
6 rows selected.
As the final step, you can combine this SELECT statement with the CREATE VIEW statement you created at the beginning of this project:
INPUT/OUTPUT:
SQL> CREATE VIEW EXAMPLE (STATE, TOTAL_BILLS, TOTAL_AMOUNT) AS
2 SELECT DISTINCT COMPANY.STATE,
COUNT(BILLS.NAME),SUM(BILLS.AMOUNT)
3 FROM BILLS, COMPANY
4 GROUP BY COMPANY.STATE
5 HAVING BILLS.NAME = COMPANY.NAME;
View created.
INPUT/OUTPUT:
SQL> SELECT * FROM EXAMPLE;
STATE TOTAL_BILLS TOTAL_AMOUNT
GA 2 250
FL 3 445
CA 2 275
TX 1 35
SC 1 200
NJ 1 35
6 rows selected.
The preceding example shows you how to plan the CREATE VIEW statement and the SELECT
statements. This code tests the SELECT statements to see whether they will generate the proper results and then combines the statements to create the view.
Example 10.2
Assume that your creditors charge a 10 percent service charge for all late payments,
and unfortunately you are late on everything this month. You want to see this late
charge along with the type of accounts the payments are coming from.
This join is straightforward. (You don't need to use anything like COUNT or SUM. ) However, you will discover one of the primary benefits of using views. You can add the
10 percent service charge and present it as a field within the view. From that point on, you can select records from the view and already have the total amount calculated
for you. The statement would look like this:
INPUT:
SQL> CREATE VIEW LATE_PAYMENT (NAME, NEW_TOTAL, ACCOUNT_TYPE) AS
2 SELECT BILLS.NAME, BILLS.AMOUNT * 1.10, BANK_ACCOUNTS.TYPE
3 FROM BILLS, BANK_ACCOUNTS
4 WHERE BILLS.ACCOUNT_ID = BANK_ACCOUNTS.ACCOUNT_ID;
OUTPUT:
View created.
INPUT/OUTPUT:
SQL> SELECT * FROM LATE_PAYMENT;
NAME NEW_TOTAL ACCOUNT_TYPE
Phone Company 137.50 Checking
Power Company 82.50 Checking
Record Club 27.50 Money Market
Software Company 275 Checking
Cable TV Company 38.50 Checking
Joe's Car Palace 385 Checking
S.C. Student Loan 220 Business
Florida Water Company 22 Checking
U-O-Us Insurance Company 137.50 Business
Debtor's Credit Card 38.50 Savings
10 rows selected.
Restrictions on Using SELECT
SQL places certain restrictions on using the SELECT statement to formulate a view. The following two rules apply when using the SELECT statement:
● You cannot use the UNION operator.
● You cannot use the ORDER BY clause. However, you can use the GROUP BY clause in a view to perform the same functions as the ORDER BY clause.
Modifying Data in a View
As you have learned, by creating a view on one or more physical tables within a
database, you can create a virtual table for use throughout an SQL script or a database
application. After the view has been created using the CREATE VIEW...SELECT statement, you can update, insert, or delete view data using the UPDATE, INSERT, and DELETE
commands you learned about on Day 8, "Manipulating Data."