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