Sculptor SQL engine examples

The following examples illustrate the format of an SQL query:

  1. Select all fields from all records:

    select * from demo.staff;
    
  2. Select specified records and fields:

    select s_firstname, s_surname, s_dept
    from demo.staff
    where s_dept = 'A1' or s_dept = 'A2';
    
  3. Include mathematical expressions and headings:

    select s_surname, s_dept,
           s_income                        As "Old",
           s_income * 1.08                 As "New",
           s_income * 1.08 - s_income      As "Diff."
    from demo.staff where s_dept = 'A1';
    
  4. Ordering the results:

    select * from demo.staff order by s_head, s_surname;
    
  5. Grouping the results:

    select count(*) from demo.staff where s_dept = 'A1';
    select avg(s_income) As "Average" from demo.staff;
    select avg(s_income) As "Average" from demo.staff;
    
  6. Getting distinct results only:

    select distinct s_dept from demo.staff;
    
  7. A “join” - fields are included from more than one file:

    select X.s_firstname, X.s_surname, X.s_dept, Y.d_name
    from demo.staff X, demo.dept Y
    where X.s_dept = Y.d_code;
    
  8. A simple nested sub-query:

    select * from demo.staff
    where s_income > ( select avg(s_income) from demo.staff );
    
  9. Complex join query with ODBC escape markers:

    select ID, SHOP_NAME, PRODUCT_NAME
    FROM {oj sqltest.employees LEFT OUTER JOIN sqltest.shops ON ID = shops.SHOP_ID
       LEFT OUTER JOIN sqltest.products ON PRODUCT_ID = shops.SHOP_ID}
    WHERE  WORKING_SINCE < {d '2010-01-20'};
    
  10. Inserting rows:

    INSERT INTO simpletable SELECT NAME FROM employees;
    
    INSERT INTO prodcopy VALUES
            (2000001, 'InsertValuesRow1', 'Row 1 desc', 20.00, 22.00, 201),
            (2000002, 'InsertValuesRow2', 'Row 2 desc', 20.00, 22.00, 202),
            (2000003, 'InsertValuesRow3', 'Row 3 desc', 20.00, 22.00, 203),
            (2000004, 'InsertValuesRow4', 'Row 4 desc', 20.00, 22.00, 204),
            (2000005, 'InsertValuesRow5', 'Row 5 desc', 20.00, 22.00, 205),
            (2000006, 'InsertValuesRow6', 'Row 6 desc', 20.00, 22.00, 206),
            (2000007, 'InsertValuesRow7', 'Row 7 desc', 20.00, 22.00, 207),
            (2000008, 'InsertValuesRow8', 'Row 8 desc', 20.00, 22.00, 208),
            (2000009, 'InsertValuesRow9', 'Row 9 desc', 20.00, 22.00, 209);
    
  11. Updating rows:

    UPDATE employees SET SALARY = SALARY * 1.05 WHERE ID = 2;
    
    UPDATE employees SET ID = ID - 10 WHERE ID > 3;
    
  12. Deleting rows:

    DELETE FROM prodcopy;
    
    DELETE FROM simpletable WHERE datacolumn <> 'AA';
    

Other example SQl queries, together with the files required to execute them, are located in the directory $SCULPTOR/demo/sql. Each query file has a comment line at the top that briefly explains its function.


RELATED TOPICS

Sculptor SQL engine introduction

Getting started with Sculptor SQL engine

Sculptor SQL engine features

Sculptor SQL command