DB2 - CREATE, INSERT, SELECT



Desenvolvido por DORNELLES Carlos Alberto - Analista de Sistemas - Brasília DF. - cad_cobol@hotmail.com

DB2 - CREATE, INSERT, SELECT
   CREATE TABLE TBL_PriceList
        ( PLID       INTEGER NOT NULL
              GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
   ,      PLNAME     VARCHAR(45)
   ,      PLCATEGORY VARCHAR(20)
   ,      PLPRICE    DECIMAL(16,4)
   ,      PRIMARY    KEY(PLID)
        );






















   INSERT INTO TBL_PriceList
        ( PLNAME, PLCATEGORY, PLPRICE)
   VALUES
        ('Lady''s Choice Mayonaisse 250g','Spread','75')
   ,    ('Nescafe Original 100g','Drinks','72')
   ,    ('Cream-O 12''s','Biscuits','52')
   ,    ('Cookies n Cream','Biscuits','55')
   ,    ('Ultra Fire 18650','Electronics','60')
   ,    ('Seagate External HDD','Electronics','3000')
   ,    ('Safeguard White','Hygiene','135')
   ,    ('Safeguard Yellow','Hygiene','43')
   ,    ('safeguard Beige','Hygiene','42')
   ,    ('Safeguard Blue','Hygiene','41')
   ,    ('Swiss Miss Dark Chocolate Brown','Drinks','12')
   ,    ('Swiss Miss Milk Chocolate Blue','Drinks','11')
   ,    ('Swiss Miss Chocolate Hazelnut Green','Drinks','11')
   ,    ('Milo Chocolate','Drinks','9')
   ,    ('Ovaltine Chocolate','Drinks','9')
   ,    ('Pandesal Mate Chocolate','Drinks','9')
   ,    ('Mik Mik Chocolate','Snacks','32')
   ,    ('Marby''s Hotdog Bun','Bread','45')
   ,    ('Cuevas Banana Bread','Bread','15')
   ,    ('Julie''s Coffee Bun','Bread','15')
   ,    ('Gardenia Tasty Bread','Bread','45')
   ,    ('Piatos Green Big','Snacks','24.25')
   ,    ('Mang Juan Green Big','Snacks','19.75')
   ,    ('Oishi Mobster','Snacks','22.5')
   ,    ('Fita','Biscuits','35')
   ,    ('Presto Peanut Butter','Biscuits','48')
   ,    ('Hansel Crackers','Biscuits','45')
   ,    ('Reno Liver Spread','Spread','32');

   SELECT * FROM TBL_PRICELIST        OU        SELECT PLID
                                                ,      PLNAME
                                                ,      PLCATEGORY
                                                ,      PLPRICE
                                                  FROM TBL_PRICELIST

PLID PLNAME PLCATEGORY PLPRICE
1 Lady's Choice Mayonaisse 250g Spread 75.0000
2 Nescafe Original 100g Drinks 72.0000
3 Cream-O 12's Biscuits 52.0000
4 Cookies n Cream Biscuits 55.0000
5 Ultra Fire 18650 Electronics 60.0000
6 Seagate External HDD Electronics 3000.0000
7 Safeguard White Hygiene 135.0000
8 Safeguard Yellow Hygiene 43.0000
9 safeguard Beige Hygiene 42.0000
10 Safeguard Blue Hygiene 41.0000
11 Swiss Miss Dark Chocolate Brown Drinks 12.0000
12 Swiss Miss Milk Chocolate Blue Drinks 11.0000
13 Swiss Miss Chocolate Hazelnut Green Drinks 11.0000
14 Milo Chocolate Drinks 9.0000
PLID PLNAME PLCATEGORY PLPRICE
15 Ovaltine Chocolate Drinks 9.0000
16 Pandesal Mate Chocolate Drinks 9.0000
17 Mik Mik Chocolate Snacks 32.0000
18 Marby's Hotdog Bun Bread 45.0000
19 Cuevas Banana Bread Bread 15.0000
20 Julie's Coffee Bun Bread 15.0000
21 Gardenia Tasty Bread Bread 45.0000
22 Piatos Green Big Snacks 24.2500
23 Mang Juan Green Big Snacks 19.7500
24 Oishi Mobster Snacks 22.5000
25 Fita Biscuits 35.0000
26 Presto Peanut Butter Biscuits 48.0000
27 Hansel Crackers Biscuits 45.0000
28 Reno Liver Spread Spread 32.0000

     SELECT * FROM TBL_PRICELIST
      WHERE PLCATEGORY = 'Snacks'
      ORDER BY PLCATEGORY
      ,     PLNAME;
     OU
     SELECT PLID
     ,      PLNAME
     , 	    PLCATEGORY
     ,      PLPRICE
       FROM TBL_PRICELIST
      WHERE PLCATEGORY = 'Snacks'
      ORDER BY PLCATEGORY
      ,     PLNAME;
PLID PLNAME PLCATEGORY PLPRICE
23 Mang Juan Green Big Snacks 19.7500
17 Mik Mik Chocolate Snacks 32.0000
24 Oishi Mobster Snacks 22.5000
22 Piatos Green Big Snacks 24.2500

     SELECT DISTINCT PLCATEGORY
       FROM TBL_PRICELIST










PLCATEGORY
Biscuits
Bread
Drinks
Electronics
Hygiene
Snacks
Spread

     SELECT * 
       FROM TBL_PRICELIST
      WHERE PLPRICE >= 10 
        AND PLPRICE <= 45
      ORDER BY PLPRICE;
     OU
     SELECT PLID
     ,      PLNAME
     , 	    PLCATEGORY
     ,      PLPRICE
       FROM TBL_PRICELIST
      WHERE PLPRICE >= 10 
        AND PLPRICE <= 45
      ORDER BY PLPRICE;















PLID PLNAME PLCATEGORY PLPRICE
12 Swiss Miss Milk Chocolate Blue Drinks 11.0000
13 Swiss Miss Chocolate Hazelnut Green Drinks 11.0000
11 Swiss Miss Dark Chocolate Brown Drinks 12.0000
19 Cuevas Banana Bread Bread 15.0000
20 Julie's Coffee Bun Bread 15.0000
23 Mang Juan Green Big Snacks 19.7500
24 Oishi Mobster Snacks 22.5000
22 Piatos Green Big Snacks 24.2500
17 Mik Mik Chocolate Snacks 32.0000
28 Reno Liver Spread Spread 32.0000
25 Fita Biscuits 35.0000
10 Safeguard Blue Hygiene 41.0000
9 safeguard Beige Hygiene 42.0000
8 Safeguard Yellow Hygiene 43.0000
18 Marby's Hotdog Bun Bread 45.0000
21 Gardenia Tasty Bread Bread 45.0000
27 Hansel Crackers Biscuits 45.0000

     SELECT * 
       FROM TBL_PRICELIST
      WHERE PLPRICE BETWEEN 10 AND 45
      ORDER BY PLPRICE;
     OU
     SELECT PLID
     ,      PLNAME
     , 	    PLCATEGORY
     ,      PLPRICE
       FROM TBL_PRICELIST
      WHERE PLPRICE >= 10 
        AND PLPRICE <= 45
      ORDER BY PLPRICE;
















PLID PLNAME PLCATEGORY PLPRICE
12 Swiss Miss Milk Chocolate Blue Drinks 11.0000
13 Swiss Miss Chocolate Hazelnut Green Drinks 11.0000
11 Swiss Miss Dark Chocolate Brown Drinks 12.0000
19 Cuevas Banana Bread Bread 15.0000
20 Julie's Coffee Bun Bread 15.0000
23 Mang Juan Green Big Snacks 19.7500
24 Oishi Mobster Snacks 22.5000
22 Piatos Green Big Snacks 24.2500
17 Mik Mik Chocolate Snacks 32.0000
28 Reno Liver Spread Spread 32.0000
25 Fita Biscuits 35.0000
10 Safeguard Blue Hygiene 41.0000
9 safeguard Beige Hygiene 42.0000
8 Safeguard Yellow Hygiene 43.0000
18 Marby's Hotdog Bun Bread 45.0000
21 Gardenia Tasty Bread Bread 45.0000
27 Hansel Crackers Biscuits 45.0000