Kognitio Training Demo

This demo is creted from a SQL script. The tables in the script are intentionally small for training purposes, but the same SQL syntax applies whether there are tens of rows or tens of billions. The demo has been developed to explore the capabilities of SQL and to show how commands can be linked together.

Create the Tables and insert data

The script uses five tables that are based on the TPC-D data (plus some timestamps).

This SQL script creates and inserts a small number of rows in to each table:

    -- The part table
    CREATE TABLE part(P_PARTKEY int not NULL,
                      P_NAME varchar(55) not NULL,
                      P_MFGR char(25) not NULL,
                      P_BRAND char(10) not NULL,
                      P_TYPE varchar(25) not NULL,
                      P_SIZE integer not NULL,
                      P_CONTAINER char(10) not NULL,
                      P_RETAILPRICE decimal(12,2) not NULL,
                      P_COMMENT varchar(23) not NULL,
                     PRIMARY KEY (P_PARTKEY));

    -- Insert dows into part table
    INSERT INTO part VALUES(1, 'VIN plate', 'DirtRover', 'MudPlugger','19J', 1, 'HQ', 17.50,'Check reason for order!');
    INSERT INTO part VALUES(2, 'Clutch fluid reservoir', 'DirtRover', 'MudPlugger','19J', 2, 'Clutch', 49.50,'Fluid as well?');
    INSERT INTO part VALUES(3, 'Fuel lift pump', 'DirtRover', 'MudPlugger','19J', 2, 'Fuel', 57.50, '');
    INSERT INTO part VALUES(4, 'Fuel filter', 'DirtRover', 'MudPlugger','19J', 1, 'Fuel', 7.50, '');
    INSERT INTO part VALUES(5, 'Brake vacuum pump', 'DirtRover', 'MudPlugger', '19J',2, 'Brakes', 37.50,'Long lead times');
    INSERT INTO part VALUES(6, 'Engine oil filler cap', 'DirtRover', 'MudPlugger','19J', 1, 'Lub''tion', 10.50,'');
    INSERT INTO part VALUES(7, 'Turbocharger', 'DirtRover', 'MudPlugger','19J', 5, 'Turbo', 417.50,'Exchange item');
    INSERT INTO part VALUES(8, 'Heater', 'DirtRover', 'MudPlugger', '19J',5, 'Cooling', 117.50, '');
    INSERT INTO part VALUES(9, 'Air filter element condition indicator', 'DirtRover','MudPlugger', '19J', 1,'Air intake', 7.50, '');
    INSERT INTO part VALUES(10, 'Air cleaner', 'DirtRover', 'MudPlugger','19J', 5, 'Air intake', 117.50, '');
    INSERT INTO part VALUES(11, 'Fuel injection pump', 'DirtRover', 'MudPlugger','19J', 2, 'Fuel', 97.50,'Difficult to obtain');

    -- The supplier table
    CREATE TABLE supplier(S_SUPPKEY int not NULL,
                                              S_NAME char(25) not NULL,
                                              S_ADDRESS varchar(40) not NULL,
                                              S_NATIONKEY integer not NULL,
                                              S_PHONE char(15) not NULL,
                                              S_URL varchar(30) not NULL,
                                              S_TELNET varchar(15) not NULL,
                                              S_ACCTBAL decimal(12,2) not NULL,
                                              S_COMMENT varchar(101) not NULL,
                         PRIMARY KEY (S_SUPPKEY));

    -- Insert data into the supplier table
    INSERT INTO supplier VALUES(1001, 'Petes parts', 'Unit 1, Smalltown, ST1 4RU.', 1, '44(0)1234577777', 'www.petes-parts.com', '111.22.3.44', 1072.20,'Just around the corner');
    INSERT INTO supplier VALUES(1002, 'Daves deliveries','14 Some place, Smalltown, ST7 4PX.', 1, '44(0)1234574444', 'www.davesdeliveries.co.uk', '11.222.33.4', -52.30,'Good for clutch parts');
    INSERT INTO supplier VALUES(1003, 'Sams supplies','54444 Long drive, BigTown, BT07458.',2, '01444455555', 'www.sams-supplies.com', '1.22.3.44', 10472.0,'US supplier of fuel systems');

    -- The partsupp table
    CREATE TABLE partsupp(PS_PARTKEY int not NULL,
                                              PS_SUPPKEY int not NULL,
                                              PS_AVAILQTY integer not NULL,
                                              PS_SUPPLYCOST decimal(12,2) not NULL,
                                              PS_COMMENT varchar(199) not NULL,
                             PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY));

    --Insert data into partsupp
    INSERT INTO partsupp VALUES(1, 1001, 10, 10.00, '');
    INSERT INTO partsupp VALUES(2, 1001, 5, 40.00, '');
    INSERT INTO partsupp VALUES(8, 1001, 1, 100.00, '');
    INSERT INTO partsupp VALUES(9, 1001, 10, 5.00, '');
    INSERT INTO partsupp VALUES(10, 1001, 7, 100.00, '');
    INSERT INTO partsupp VALUES(5, 1002, 10, 30.00, '');
    INSERT INTO partsupp VALUES(6, 1002, 100, 7.00, '');
    INSERT INTO partsupp VALUES(7, 1002, 1, 259.00, '');
    INSERT INTO partsupp VALUES(8, 1002, 5, 100.00, '');
    INSERT INTO partsupp VALUES(9, 1002, 100, 4.00, '');
    INSERT INTO partsupp VALUES(10, 1002, 10, 100.00, '');
    INSERT INTO partsupp VALUES(3, 1003, 10, 50.00, '');
    INSERT INTO partsupp VALUES(4, 1003, 1000, 2.50, '');
    INSERT INTO partsupp VALUES(7, 1003, 1, 259.00, '');
    INSERT INTO partsupp VALUES(9, 1003, 10, 7.50, '');


    -- The customer table
    CREATE TABLE customer(C_CUSTKEY int not NULL,
                          C_NAME varchar(25),
                          C_ADDRESS varchar(40),
                          C_POSTCODE char(9),
                          C_NATIONKEY integer,
                          C_PHONE char(15),
                          C_ACCTBAL decimal(12,2),
                          C_FLAGS int,
                          PRIMARY KEY (C_CUSTKEY));

-- Insert data into customer table
-- note 3rd insert is different syntax so that C_NATIONKEY is null for use in examples
    INSERT INTO customer VALUES(1, 'Andys autos', 'White Cross, Smalltown', 'ST1 4RX', 1, '44(0)1234571111', 0.0, 255);
    INSERT INTO customer VALUES(2, 'Gordons garage', 'Develgate drive, Smalltown','ST3 7XX', 1, '44(0)1234571221', 1230.50, 63);
    INSERT INTO customer VALUES(C_CUSTKEY, C_NAME, C_ADDRESS, C_POSTCODE, C_PHONE, C_ACCTBAL, C_FLAGS) VALUES(
                                3, 'MikeTheMechanic', 'Back street, Smalltown', 'ST4 4RR', '44(0)1234571666', -983.0, 8);
    INSERT INTO customer VALUES(4, 'Bert Brown', '4 Brown Cross, Smalltown', 'ST13 4RX', 1, '44(0)1234571122', 0.0, 255);
    INSERT INTO customer VALUES(5, 'Bert Browne', '17 Brown Place, Smalltown', 'ST13 7BN', 1, '44(0)1234571133', 0.0, 7);
    INSERT INTO customer VALUES(6, 'Burt Brown', '111 Brown Road, Smalltown', 'ST13 9BB', 1, '44(0)1234571144', 0.0, 63);
    INSERT INTO customer VALUES(7, 'Burt Browne', '22 Brown Road, Smalltown', 'ST13 9BC', 1, '44(0)1234571155', 0.0, 128);
    INSERT INTO customer VALUES(8, 'John Brown', '24 Brown Road, Smalltown', 'ST13 9BC', 1, '44(0)1234571155', 0.0, 8);
    INSERT INTO customer VALUES(9, 'David Brown', '26 Brown Road, Smalltown', 'ST13 9BC', 1, '44(0)1234571155', 0.0, 0);

    -- The ordertab table
    CREATE TABLE ordertab(O_ORDERKEY int not NULL,
                      O_CUSTKEY int,
                          O_ORDERSTATUS char(1),
                          O_TOTALPRICE decimal(12,2),
                          O_ORDERDATE date,
                          O_ORDERTIME time,
                          O_ORDERPRIORITY char(15),
                          O_DELIVERYTIME timestamp,
                 PRIMARY KEY (O_ORDERKEY));

    --Insert data into ordertab table
    INSERT INTO ordertab VALUES(66601, 1, '0', 100.00, date '2017-10-13', time '12:32:00', 'high', timestamp '2017-11-07 12:00:00');
    INSERT INTO ordertab VALUES(66602, 1, '0', 1000.00, date '2017-10-14', time '09:12:00', 'med', timestamp '2018-01-01 09:00:00');
    INSERT INTO ordertab VALUES(66603, 4, '1', 500.00, date '2017-10-14', time '17:30:00', 'low', timestamp '2017-12-17 17:00:00');
    INSERT INTO ordertab VALUES(66604, 5, '0', 1100.00, date '2017-10-15', time '12:30:00', 'low', timestamp '2018-02-01 09:00:00');
    INSERT INTO ordertab VALUES(66605, 5, '0', 250.00, date '2017-10-15', time '12:32:00', 'low', timestamp '2018-02-01 09:00:00');
    INSERT INTO ordertab VALUES(66606, 5, '0', 100.00, date '2017-10-15', time '12:35:00', 'high', timestamp '2017-10-17 17:00:00');
    INSERT INTO ordertab VALUES(66607, 2, '0', 100.00, date '2017-11-15', time '12:35:00', 'high', timestamp '2017-12-17 17:00:00');
    INSERT INTO ordertab VALUES(66608, 2, '0', 100.00, date '2017-11-15', time '12:35:00', 'high', timestamp '2017-12-17 17:00:00');
    INSERT INTO ordertab VALUES(66609, 7, '0', 100.00, date '2017-12-15', time '12:35:00', 'high', timestamp '2018-01-17 17:00:00');

Note

For data of any significant volume single row inserts is not the optimal way to get data into Kognitio. See Data in Kognitio for alternative methods.

Simple SQL Querying

For simple retrieval of data use SELECT *. To get the full details of all suppliers:

SELECT *
FROM supplier

To get part numbers for all parts supplied using ORDER BY ensures we get them in a specified order:

SELECT ps_partkey
FROM  partsupp
ORDER BY ps_partkey

The previous query returned duplicates, to eliminate these we can use DISTINCT. We order this time on column number rather than name, alias the column and sort in descending order:

SELECT ps_partkey AS part
FROM partsupp
ORDER BY 1 DESC

Rather than eliminate the duplicates, lets count how many suppliers of each part there are. We group on the part, do a COUNT(\*) and display the rows in decending order of suppliers:

SELECT ps_partkey AS part,
       COUNT(*) AS suppliers
FROM partsupp
GROUP BY ps_partkey
ORDER BY suppliers DESC, 1

Qualifying results retrieval is done using WHERE clauses. Get all parts plus their type and price that cost less than 100.00 dollars and ORDER BY the price and part key:

SELECT p_partkey, p_type, p_retailprice
FROM part
WHERE p_retailprice < 100
ORDER BY 3, 1

Joining tables together

Now let’s join in the part details to the query that counted the number of suppliers:

SELECT ps_partkey,
         COUNT(*),
         p_name
FROM part
JOIN partsupp
ON p_partkey = ps_partkey
GROUP BY 1, 3
ORDER BY 2 DESC, 1;

-- Alternative syntax using WHERE
SELECT ps_partkey,
         COUNT(*),
         p_name
FROM part, partsupp
WHERE p_partkey = ps_partkey
GROUP BY 1, 3
ORDER BY 2 DESC, 1;

A self join, that shows us all pairs of parts that are in the same container. Note the < clause prevents (x,x) pairs as well as both combinations of (x,y) i.e. (y,x):

SELECT p1.p_partkey part1,
         p2.p_partkey part2,
         p1.p_container container
FROM part p1
JOIN part p2
ON p1.p_container = p2.p_container
AND p1.p_partkey < p2.p_partkey
ORDER BY p1.p_container, 1, 2;

A three way join listing each partkey, name, and supplier for all supplied parts:

SELECT p.p_partkey,
         s.s_name,
         p.p_name
FROM part p
JOIN partsupp ps
WHERE p.p_partkey = ps.ps_partkey
JOIN supplier s
ON s.s_suppkey = ps.ps_suppkey
ORDER BY 1, 2;

--Alternative syntax using WHERE
SELECT p.p_partkey, s.s_name, p.p_name
FROM part p, supplier s, partsupp ps
WHERE p.p_partkey = ps.ps_partkey
AND s.s_suppkey = ps.ps_suppkey
ORDER BY 1, 2;

Using Aggregate SQL Functions

For each part supplied, calculate the number of suppliers who supply the part, the total of parts available and the average supply cost:

SELECT ps_partkey part,
   COUNT(*) suppliers,
   SUM(ps_availqty) total_parts,
   AVG(ps_supplycost) avg_price
FROM partsupp
GROUP BY 1
ORDER BY 1;

Where a part is supplied by more than one supplier, display the maximum and minimum prices:

SELECT ps_partkey part,
       MIN(ps_supplycost),
       MAX(ps_supplycost)
FROM partsupp
GROUP BY 1
HAVING COUNT(*) > 1
ORDER BY 1;

Display the supplier name and the total value of their stock for those suppliers who supply at least 5 items and where the value of the stock is over 3000 dollars:

SELECT s_name,
       SUM(ps_availqty * ps_supplycost) value_of_stock
FROM partsupp, supplier
WHERE s_suppkey = ps_suppkey
GROUP BY 1
HAVING COUNT(ps_partkey) >= 5
AND SUM(ps_availqty * ps_supplycost) < 3000.00
ORDER BY 2 DESC;

Using String manipulation SQL functions

Obtain all the details of the Smalltown suppliers without an ST1 post code:

SELECT *
FROM supplier
WHERE LOWER(s_address) LIKE '%smalltown%'
AND UPPER(s_address) not LIKE '%ST1 %'
ORDER BY s_suppkey;

Obtain partkeys, containers and names of all types of ‘pumps’ in the parts table:

SELECT p_partkey, p_container, p_name
FROM part
WHERE UPPER(p_name) LIKE '%PUMP%'
ORDER BY 1;

Obtain names of suppliers that supply pumps:

-- First using sub-SELECTs.
SELECT s_name
FROM supplier
WHERE s_suppkey IN (SELECT ps_suppkey
                    FROM partsupp
                    WHERE ps_partkey IN (SELECT p_partkey
                                         FROM part
                                         WHERE UPPER(p_name) LIKE '%PUMP%')
                    )
ORDER BY 1;

-- and then using WHERE clause joins.
SELECT s_name
FROM supplier, part, partsupp
WHERE s_suppkey = ps_suppkey
AND ps_partkey = p_partkey
AND UPPER(p_name) LIKE '%PUMP%'
ORDER BY 1;

-- and it can be done with exists as well!
SELECT s_name
FROM supplier
WHERE exists (SELECT *
              FROM part, partsupp
              WHERE p_partkey = ps_partkey
              AND ps_suppkey = s_suppkey
              AND UPPER(p_name) LIKE '%PUMP%'
             )
ORDER BY 1;

Find all the customers who could have been “Mr Brown”:

SELECT c_custkey, c_name
FROM customer
WHERE UPPER(c_name) LIKE '%BROWN%'
ORDER BY 1;

-- Same thing but using matching.
SELECT c_custkey, c_name
FROM customer
WHERE LOWER(c_name) matching 'brown'
ORDER BY 1;

Extract the part of the name before the ‘brown’:

SELECT c_custkey,
       c_name,
       SUBSTRING(c_name FROM 1 FOR POSITION('brown' IN LOWER(c_name)) -1)
FROM customer
WHERE LOWER(c_name) LIKE '%brown%'
ORDER BY 1;

Extract the whole name if the part of the name before the brown “sounds LIKE” bert. We are no longer interested in Mr Browne:

SELECT c_custkey,
       c_name
FROM customer
WHERE LOWER(c_name) LIKE '%brown'
AND SOUNDEX(SUBSTRING(c_name FROM 1 FOR POSITION('brown' IN LOWER(c_name)) -1)) =
    SOUNDEX('bert')
ORDER BY 1;

Find all the customers with Smalltown postcodes:

SELECT c_custkey,
       c_name,
       c_postcode
FROM customer
WHERE c_postcode MATCHING '^[sS][tT]'
ORDER BY 1;

Refine it further to the ST10 to ST13 range of postcodes:

SELECT c_custkey,
       c_name,
       c_postcode
FROM customer
WHERE LOWER(c_postcode) MATCHING '^st1[0-3]'
ORDER BY 1;

Refine it further to the ST10 to ST13 range of postcodes that end with BC:

SELECT c_custkey,
       c_name,
       c_postcode
FROM customer
WHERE LOWER(c_postcode) MATCHING '^st1[0-3].\*bc$'
ORDER BY 1;

Using SQL set functions

Get the details of any parts that nobody supplies:

SELECT *
FROM part WHERE not exists (SELECT *
                            FROM partsupp
                            WHERE p_partkey = ps_partkey);

Using a union select the partkeys of all size 1 parts plus all parts supplied by Daves deliveries. Note duplicates will be eliminated:

SELECT p_partkey
FROM part
WHERE p_size = 1
UNION
SELECT ps_partkey
FROM partsupp
WHERE ps_suppkey = (SELECT s_suppkey
                    FROM supplier
                    WHERE UPPER(s_name) LIKE 'DAVES DELIVERIES%')
ORDER BY 1;

Using a union select the partkeys of all size 1 parts plus all parts supplied by Daves deliveries. This time add a literal to indicate why it qualifies, as this makes each row unique we can see the duplicates:

SELECT p_partkey,
       CAST('size 1' AS VARCHAR) qualifier
FROM part
WHERE p_size = 1
UNION
SELECT ps_partkey,
       'from d'
FROM partsupp
WHERE ps_suppkey = (SELECT s_suppkey
                    FROM supplier
                    WHERE UPPER(s_name) LIKE 'DAVES DELIVERIES%')
ORDER BY 1;

This time use union all and a having clause to find out the size 1 parts that are supplied by Daves deliveries. Note we need to use a derived table to make the having Clause apply to the result of the entire union:

SELECT k FROM (SELECT p_partkey
               FROM part
               WHERE p_size = 1
               UNION ALL
               SELECT ps_partkey
               FROM partsupp
               WHERE ps_suppkey = (SELECT s_suppkey
                                   FROM supplier
                                   WHERE UPPER(s_name) LIKE 'DAVES DELIVERIES%')
              ) AS dt(k)
GROUP BY 1
HAVING COUNT(*) > 1
ORDER BY 1;

This is how we get all size 1 parts supplied by Dave:

SELECT p_partkey
FROM part, supplier, partsupp
WHERE p_size = 1
AND ps_partkey = p_partkey
AND ps_suppkey = s_suppkey
AND UPPER(s_name) LIKE 'DAVES DELIVERIES%'
ORDER BY 1;

Now get counts of all the sizes of the parts supplied by Dave:

SELECT p_size,
       COUNT(p_partkey)
FROM part, supplier, partsupp
WHERE ps_partkey = p_partkey
AND ps_suppkey = s_suppkey
AND UPPER(s_name) LIKE 'DAVES DELIVERIES%'
GROUP BY 1
ORDER BY 1;

Now use a case to expand on the meaning of size:

SELECT p_size,
       CASE WHEN p_size < 3 THEN 'Tiny/small: Can go by normal Post'
            WHEN p_size = 3 THEN 'Medium: Overnight shipment'
            WHEN p_size > 3 THEN 'Large/Heavy: Special deliveries'
       END size,
       COUNT(p_partkey)
FROM part, supplier, partsupp
WHERE ps_partkey = p_partkey
AND ps_suppkey = s_suppkey
AND UPPER(s_name) LIKE 'DAVES DELIVERIES%'
GROUP BY 1, 2
ORDER BY 1

Working with dates and times and creating views

Note

The SQL function CURRENT_DATE is used here. Results will be dependent on when you run these queries.

Display the order numbers, date of order and order priority:

SELECT o_orderkey,
       o_orderdate,
       o_orderpriority
FROM ordertab
ORDER BY o_orderkey;

Display the order numbers, date of order and when the order should be delivered based on the order priority:

SELECT o_orderkey,
       o_orderdate,
       o_orderpriority,
       CASE LOWER(o_orderpriority)
            WHEN 'high' THEN o_orderdate + interval '1' day
            WHEN 'med' THEN o_orderdate + interval '7' day
            WHEN 'low' THEN o_orderdate + interval '1' month
       END AS ShipBy
FROM ordertab
ORDER BY o_orderkey;

Add the display of how many days left to deliver, (using our measures):

SELECT o_orderkey,
       o_orderdate,
       o_orderpriority,
       CASE LOWER(o_orderpriority)
            WHEN 'high' THEN o_orderdate + interval '1' day
            WHEN 'med' THEN o_orderdate + interval '7' day
            WHEN 'low' THEN o_orderdate + interval '1' month
       END AS ShipBy,
       CASE LOWER(o_orderpriority)
            WHEN 'high' THEN o_orderdate + interval '1' day
            WHEN 'med' THEN o_orderdate + interval '7' day
            WHEN 'low' THEN o_orderdate + interval '1' month
       END - CURRENT_DATE AS DaysToGo
FROM ordertab
ORDER BY o_orderkey;

Create a view of the information with the shipping date:

CREATE VIEW ordertab_view(k, d, p, s) AS
SELECT o_orderkey,
       o_orderdate,
       o_orderpriority,
       CASE LOWER(o_orderpriority)
            WHEN 'high' THEN o_orderdate + interval '1' day
            WHEN 'med' THEN o_orderdate + interval '7' day
            WHEN 'low' THEN o_orderdate + interval '1' month
       END
FROM ordertab;

Create an image of the view in memory for fastest access:

CREATE VIEW IMAGE ordertab_view;

SELECT *
FROM ordertab_view
ORDER BY k;

We can now make the days to go calculation less repetitive:

SELECT k,
       p,
       (s - CURRENT_DATE)DAY(4) AS DaysToGo
FROM ordertab_view
ORDER BY k;

Compare when we think we should deliver with the requested delivery timestamp:

SELECT o_orderkey,
       o_orderdate,
       o_orderpriority,
       CASE LOWER(o_orderpriority)
            WHEN 'high' THEN o_orderdate + interval '1' day
            WHEN 'med' THEN o_orderdate + interval '7' day
            WHEN 'low' THEN o_orderdate + interval '1' month
       END AS OurShipBy,
       CASE LOWER(o_orderpriority)
            WHEN 'high' THEN o_orderdate + interval '1' day
            WHEN 'med' THEN o_orderdate + interval '7' day
            WHEN 'low' THEN o_orderdate + interval '1' month
       END - CURRENT_DATE AS OurDaysToGo,
       o_deliverytime,
       CAST(o_deliverytime AS DATE) - o_orderdate AS TheirDaysToGo
FROM ordertab
ORDER BY o_orderkey;

Assuming we use slower/cheaper delivery mechanism for lower priority items, work out when each order needs to be shipped to satisfy the delivery dates:

SELECT o_orderkey,
       o_orderpriority,
       CASE LOWER(o_orderpriority)
            WHEN 'high' THEN o_deliverytime - interval '12:30' hour to minute
            WHEN 'med' THEN o_deliverytime - interval '2 12' day to hour
            WHEN 'low' THEN o_deliverytime - interval '7' day
       END AS ShippingTime,
       o_deliverytime
FROM ordertab
ORDER BY o_orderkey;

Creating lookup tables and using left joins for complete reporting

Find out which months most orders are placed in:

SELECT extract(month FROM o_orderdate) ordermonth,
       COUNT(*)
FROM ordertab
GROUP BY 1
ORDER BY 2 DESC;

Find out which hour most orders are placed in:

SELECT extract(hour FROM o_ordertime) hr,
       COUNT(*)
FROM ordertab
GROUP BY 1
ORDER BY hr;

Find out which day most orders are placed on:

SELECT CASE WHEN (o_orderdate - date '1900-01-01') mod 7 = 0 THEN 'Mon'
            WHEN (o_orderdate - date '1900-01-01') mod 7 = 1 THEN 'Tue'
            WHEN (o_orderdate - date '1900-01-01') mod 7 = 2 THEN 'Wed'
            WHEN (o_orderdate - date '1900-01-01') mod 7 = 3 THEN 'Thur'
            WHEN (o_orderdate - date '1900-01-01') mod 7 = 4 THEN 'Fri'
            WHEN (o_orderdate - date '1900-01-01') mod 7 = 5 THEN 'Sat'
            WHEN (o_orderdate - date '1900-01-01') mod 7 = 6 THEN 'Sun'
       END DayOfTheWeek,
       COUNT(*)
FROM ordertab
GROUP BY 1
ORDER BY 2 DESC;

-- Alternatively we use the decode statement
SELECT DECODE( (o_orderdate - date '1900-01-01') mod 7,
                0, 'Mon',
                1, 'Tue',
                2, 'Wed',
                3, 'Thur',
                4, 'Fri',
                5, 'Sat',
                'Sun') DayOfTheWeek,
       COUNT(*)
FROM ordertab
GROUP BY 1
ORDER BY 2 DESC;

What we would really like is to represent days with no orders as well, so first create a lookup table for days of the week:

CREATE TABLE daysoftheweek (di int,
                            dc char(4));
INSERT INTO daysoftheweek VALUES(0, 'Mon');
INSERT INTO daysoftheweek VALUES(1, 'Tue');
INSERT INTO daysoftheweek VALUES(2, 'Wed');
INSERT INTO daysoftheweek VALUES(3, 'Thur');
INSERT INTO daysoftheweek VALUES(4, 'Fri');
INSERT INTO daysoftheweek VALUES(5, 'Sat');
INSERT INTO daysoftheweek VALUES(6, 'Sun');

Now do an outer join, counting the orders for each day:

SELECT dc,
       COUNT(o_orderdate) orders
FROM daysoftheweek
LEFT OUTER JOIN ordertab
ON ((o_orderdate - date '1900-01-01') mod 7) = di
GROUP BY 1
ORDER BY 2 DESC

and we can obviously use this to find out how many orders were placed on specified days:

SELECT dc,
       COUNT(o_orderdate) orders
FROM daysoftheweek
LEFT OUTER JOIN ordertab
ON ((o_orderdate - date '1900-01-01') mod 7) = di
WHERE dc IN ('Sat', 'Sun')
GROUP BY 1
ORDER BY 2 DESC

We can also use a having clause to look at or eliminate specific count values:

SELECT dc,
       COUNT(o_orderdate) orders
FROM daysoftheweek
LEFT OUTER JOIN ordertab
ON ((o_orderdate - date '1900-01-01') mod 7) = di
WHERE dc IN ('Sat', 'Sun')
GROUP BY 1
HAVING COUNT(o_orderdate) > 0
ORDER BY 2 DESC