Question12

Question 12

Create a view Acc_View that has the attributes ordno, customer_name, customer_city, agent_name, product_name, qty, and dollars. These values should be ordno, cname, city, aname, pname, qty, and dollars respectively for a specific order. Test this view with a query for all orders whose ordno is greater than or equal to 1020.

SQL:

    create view Acc_View
         ( ordno, customer_name, customer_city, agent_name, product_name,qty, dollars)
          as select ordno,cname,customer.city, aname,pname,qty,dollars
             from ord_order_line, customer,agent,product
             where ord_order_line.cid = customer.cid
               and ord_order_line.aid = agent.aid
               and ord_order_line.pid=product.pid;

Output:

    ORDNO CUSTOMER_NAME   CUSTOMER_CITY        AGENT_NAME      PRODUCT_NAME    QTY         DOLLARS  
    1011  TipTop          Duluth               Smith            comb                  1000    450.00
    1011  TipTop          Duluth               Smith           razor                   800    700.00
    1011  TipTop          Duluth               Smith           pencil                 1200   1100.00
    1011  TipTop          Duluth               Smith           case                    600    600.00
    1012  TipTop          Duluth               Smith            comb                  1000    450.00
    1019  TipTop          Duluth               Jones           brush                   400    180.00
    1019  TipTop          Duluth               Jones           folder                  400    720.00
    1018  TipTop          Duluth               Brown           pen                     600    540.00
    1018  TipTop          Duluth               Brown           folder                  400    720.00
    1018  TipTop          Duluth               Brown           case                    600    600.00
    1023  TipTop          Duluth               Gray            pencil                  500    450.00
    1022  TipTop          Duluth               Otasi           pencil                 1200   1200.00
    1022  TipTop          Duluth               Otasi           folder                  400    720.00
    1025  TipTop          Duluth               Otasi           case                    800    720.00
    1017  TipTop          Duluth               Smith            comb                   101      0.50
    1017  TipTop          Duluth               Smith            razor                   600    540.00
    1013  Basic           Dallas               Brown           razor                  1000    880.00
    1026  Basic           Dallas               Otasi           razor                   800    704.00
    1014  Allied          Dallas               Brown           pencil                 1200   1104.00
    1015  Allied          Dallas               Brown           pencil                 1200   1104.00
    1021  ACME            Duluth               Smith            comb                  1000    460.00
    1021  ACME            Duluth               Smith           folder                  400    700.00
    1016  ACME            Kyoto                Smith            comb                  1000    500.00
    1050  ACME            Kyoto                Smith            comb                   101      0.50
    1020  ACME            Kyoto                Brown           case                    600    600.00
    1024  ACME            Kyoto                Smith            comb                   800    400.00
    1035  ACME            Kyoto                Smith            comb                   101      0.55
    1035  ACME            Kyoto                Smith            folder                  400    725.00
    1036  ACME            Kyoto                Smith            comb                   101      0.55
    1036  ACME            Kyoto                Smith           pencil                  400    400.00

    30 record(s) selected

SQL:

    select * from acc_view_t2
       where cast(ordno as integer)>=1020;

Output:

    ORDNO CUSTOMER_NAME   CUSTOMER_CITY        AGENT_NAME      PRODUCT_NAME    QTY         DOLLARS  
    1023  TipTop          Duluth               Gray            pencil                  500    450.00
    1022  TipTop          Duluth               Otasi           pencil                 1200   1200.00
    1022  TipTop          Duluth               Otasi           folder                  400    720.00
    1025  TipTop          Duluth               Otasi           case                    800    720.00
    1026  Basic           Dallas               Otasi           razor                   800    704.00
    1021  ACME            Duluth               Smith            comb                  1000    460.00
    1021  ACME            Duluth               Smith           folder                  400    700.00
    1050  ACME            Kyoto                Smith            comb                   101      0.50
    1020  ACME            Kyoto                Brown           case                    600    600.00
    1024  ACME            Kyoto                Smith            comb                   800    400.00
    1035  ACME            Kyoto                Smith            comb                   101      0.55
    1036  ACME            Kyoto                Smith            comb                   101      0.55
    1036  ACME            Kyoto                Smith           pencil                  400    400.00
    1035  ACME            Kyoto                Smith           folder                  400    725.00

    14 record(s) selected.

john_knight (Sep 15, 2001 7:47 pm; Comment #1)

I have failed to format output correctly for Wiki, so have left it in plain text for now. Too late to do more tonight!!

mj (Sep 15, 2001 11:08 pm; Comment #2)

I fixed the layout.