Results 1 to 1 of 1

Thread: [code examples] Table - select

  1. #1
    Lianja MVP
    Join Date
    Dec 2012
    Location
    Croatia, Zagreb
    Posts
    1,106

    [code examples] Table - select

    Retrieves data from one or more tables or views

    SELECT
    Code:
    // Specifying database name
    SELECT * FROM southwind!orders
    // or
    SELECT * FROM southwind.orders
     
    // Select all rows, including duplicates, from an encrypted table
    SELECT ALL * FROM enctab<key_1,key2,key_3>
     
    // Select "last_name" column from rows with a unique "last_name"
    SELECT DISTINCT last_name FROM customer
    Code:
     
    // Select "last_name" column from unique rows
    SELECT DISTINCTROW last_name FROM customer
     
    // Select first 10 rows
    SELECT TOP 10 * FROM accounts
     
    // Select first 50% of the rows
    SELECT TOP 50 PERCENT * FROM accounts
     
    // Crystal Reports / ODBC style JOINS: LEFT OUTER, RIGHT OUTER, FULL OUTER
    SELECT customer.account_no, customer.last_name, accounts.ord_value;
      FROM {OJ "customer" customer;
      LEFT OUTER JOIN "accounts" accounts;
      ON customer.account_no = accounts.account_no}
     
    SELECT customer.account_no, customer.last_name, accounts.ord_value;
      FROM {OJ "customer" customer;
      RIGHT OUTER JOIN "accounts" accounts;
      ON customer.account_no = accounts.account_no}
     
    SELECT customer.account_no, customer.last_name, accounts.ord_value;
      FROM {OJ "customer" customer;
      FULL OUTER JOIN "accounts" accounts;
      ON customer.account_no = accounts.account_no}
    Code:
     
    // JOINS: INNER, LEFT OUTER, RIGHT OUTER, CROSS, FULL
    SELECT customer.account_no, customer.last_name, accounts.ord_value;
      FROM customer;
      INNER JOIN accounts;
      ON customer.account_no = accounts.account_no
     
    SELECT customer.account_no, customer.last_name, accounts.ord_value;
      FROM customer;
      LEFT OUTER JOIN accounts;
      ON customer.account_no = accounts.account_no
     
    SELECT customer.account_no, customer.last_name, accounts.ord_value;
      FROM customer;
      RIGHT OUTER JOIN accounts;
      ON customer.account_no = accounts.account_no
     
    SELECT customer.account_no, customer.last_name, accounts.ord_value;
      FROM customer;
      CROSS JOIN accounts
     
    SELECT customer.account_no, customer.last_name, accounts.ord_value;
      FROM customer;
      FULL OUTER JOIN accounts
    Code:
     
    // JOINs with nested SELECTs
    SELECT contactname FROM customers;
      WHERE customerid IN;
     (SELECT customerid FROM orders WHERE orderdate = {07/19/1996}) 
     
    SELECT shipname FROM orders, customers;
      WHERE orders.customerid = customers.customerid AND;
      employeeid = (SELECT max(employeeid);
      FROM orders;
      WHERE orderdate = {07/19/1996});
      order by shipname
     
    SELECT contactname FROM customers;
      WHERE ctod("07/19/1996") > ANY;
     (SELECT orderdate FROM orders WHERE customers.customerid = orders.customerid)
     
    SELECT orders.customerid;
      FROM orders;
      WHERE 30 > ALL (SELECT sum(quantity) FROM order_details;
      WHERE orders.orderid = order_details.orderid)
    Code:
     
    SELECT orderid,customerid;
      FROM orders as o1;
      WHERE 2 < (SELECT quantity FROM order_details as i1;   WHERE o1.orderid = i1.orderid AND i1.productid = 71)    
    
    SELECT lastname FROM employees;   WHERE exists;
       (SELECT * FROM orders;   WHERE employees.employeeid = orders.employeeid;   AND orderdate = CTOD("11/11/1996"))   
    
    SELECT lastname FROM employees;   WHERE not exists;   (SELECT * FROM orders;
       WHERE employees.employeeid = orders.employeeid;
       AND orderdate = CTOD("11/11/1996"))   
    
    SELECT companyname, (select MAX(orderid);
       FROM orders as o1;
       WHERE o1.customerid = c1.customerid);
       FROM customers as c1   
    
    // Multiple JOINs 
    SELECT customer.account_no, customer.state,;   state.descript, accounts.ord_value;
       FROM customer RIGHT OUTER JOIN accounts;
       ON customer.account_no = accounts.account_no,; 
      customer INNER JOIN state;
       ON customer.state = state.state;
       ORDER BY account_no
    Code:
    // Select account number and order value details for Massachusetts customers
      SELECT account_no, ord_value;   
    FROM accounts;  
     WHERE account_no in (SELECT account_no FROM customer WHERE state = 'MA');
       ORDER BY account_no   
    
    // Select all overdue accounts with 15% commission in sorted "last_name" order. 
    SELECT last_name, zip, balance, balance*1.15;
       FROM customer; 
      WHERE balance > 0;
      ORDER BY last_name
     
    // Select total and average balance for all overdue accounts, grouped by "limit"
    SELECT SUM(balance), AVG(balance);
      FROM customer;
      WHERE balance > 0;
      GROUP BY limit
     
    // Select total and average balance for all overdue accounts, grouped by "limit" with column aliases
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      GROUP BY limit
    Code:
     
    // Select total and average balance for all overdue accounts, grouped by "limit" with column aliases
    // For Massachusetts customers only
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      GROUP BY limit;
      HAVING state = "MA"
     
    // Save into an array
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      INTO ARRAY temp
     
    // Save into an array of objects
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      INTO ARRAYOFOBJECTS temp
    // convert first selected row to JSON
    cjson = json_encode(temp[1])
    Code:
     
    // Save into an object
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      INTO object temp
     
    // Save into a JSON file
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      INTO JSON temp
     
    // Save into an HTML file
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      INTO HTML temp
     
    // Save into an XML file
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      INTO XML temp
    Code:
     
    // Create a cursor
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      INTO CURSOR temp
     
    // Save as a table
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      INTO TABLE temp DATABASE mydbc
     
    //or
     
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      SAVE AS temp
     
    // Save in Microsoft® ActiveX® Data Objects XML format
    // Any XML files created in the ADO format can be loaded
    // with the Open method of the ADO Recordset object. 
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      SAVE AS XML temp.xml FORMAT ADO
    // In Visual Basic the file can then be loaded:
    // Set adoPrimaryRS = New Recordset
    // adoPrimaryRS.Open "temp.xml"
    Code:
     
    // Save in Microsoft® Excel XML format
    SELECT * FROM customers SAVE AS xml mycustomers.xml FORMAT EXCEL
    Lianja.showDocument("mycustomers.xml")
     
    // Save in text file format
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      TO FILE temp.txt
     
    // Select all customer accounts that have an outstanding balance or are based in Massachusetts
    SELECT account_no;
      FROM customer;
      WHERE state = 'MA';
      UNION SELECT account_no;
      FROM accounts;
      WHERE balance > 0;
      ORDER BY account_no
     
    // Select a maximum of 10 rows, starting from row 6
    SELECT * FROM customer;
      LIMIT 5,10
    //or
    SELECT * FROM customer;
      LIMIT 10 OFFSET 5
    Code:
     
    // Ignore the smartquery cache
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      INTO CURSOR temp;
      nosmartquery
     
    // Force smartquery
    SELECT SUM(balance) AS Total, AVG(balance) AS Average;
      FROM customer;
      WHERE balance > 0;
      INTO CURSOR temp;
      smartquery

    https://www.lianja.com/doc/index.php/SQL_SELECT


    Code:
    // Return all columns
    select * from products
    // Return specified columns
    select 'Product Details: ',  productid, upper(productname) as Name,;
      unitprice*unitsinstock as stockholding;
      from products
    // Return aggregate function results for the whole table
    select avg(unitprice) Average, min(unitprice) Minimum,;
      max(unitprice) Maximum, count(unitprice) Count,;
      sum(unitprice) Sum;
      from products// Return one row for each group of rows with an identical contactname column
    select distinct contactname from customers
    // Return one row for each group of identical rows
    select distinctrow contactname from customers
    // Return the first 10 rows
    select top 10 * from customers
    // Return the first 50% of rows
    select top 50 percent * from customers
    Code:
    // Select a maximum of 10 rows, starting from row 6
    select * from customers limit 5,10
    // or
    select * from customers limit 10 offset 5
    // Select all rows from shippers and employees without relating the tables
    select * from shippers ship, employees as emp
    // Relate orders, order_details and shippers tables with multiple joins
    select orders.orderid, orders.shipvia,;
      shippers.companyname, order_details.productid;
      from orders left outer join order_details;
      on orders.orderid = order_details.orderid,;
         orders inner join shippers;
         on orders.shipvia = shippers.shipperid
    // Equivalent to 'select * from shippers, employees'
    select * from shippers full join employees// Return rows sorted in descending order by orderid then productid
    select orderid, productid, quantity;
      from order_details;
      order by orderid, 2 desc
    // Return the orderid and total for each order
    select orderid as Order, sum(unitprice * quantity * (1-discount)) as Total;
      from order_details;
      group by orderid// Having condition
    select sum(unitprice * quantity) as subtotal,;
      avg(unitprice * quantity) as averagetotal;
      from order_details;
      where discount = 0;
      group BY orderid;
      having quantity > 10
    Code:
     
    // Where condition with nested select
    select shipname from orders, customers;
      where orders.customerid = customers.customerid;
      and employeeid = (select max(employeeid);
      from orders where orderdate = {07/19/1996});
      order by shipnameselect productid from products into array array1
    select * from shippers into html shippers
    select employeeid, lastname, firstname from employees into table emp database newsouth
     
    select employeeid, lastname, firstname from employees save as xml emp
     
    select * from shippers to file shiptxt
    https://www.lianja.com/doc/index.php/Using_Lianja_SQL



    Number of rows in a SELECT statement query
    COUNT
    Code:
    // Get a count of all rows in the accounts table.
    SELECT COUNT(*) Total FROM accounts
     
    // Get a count of jobs
    SELECT COUNT(jobs) Jobs FROM employee
     
    // Get a count of distinct rows for jobs
    SELECT COUNT(DISTINCT jobs) Jobs FROM employee
    https://www.lianja.com/doc/index.php/COUNT()

    Returns a maximum value in a SELECT statement
    MAX
    Code:
    SELECT MAX(sal) Maximum FROM accounts
    https://www.lianja.com/doc/index.php/SQL_MAX()

    Returns a minimum value in a SELECT statement
    MIN
    Code:
    SELECT MIN(sal) Minimum FROM accounts
    https://www.lianja.com/doc/index.php/SQL_MIN()

    Unique row identifier in SELECT * statements
    SET SQLROWID
    Code:
    set sql on
    set sqlrowid on
    select * from state.rdb where state = "M";
    https://www.lianja.com/doc/index.php/SET_SQLROWID

    Return the single row result of an SQL statement
    SQLEVAL
    Code:
    open database southwind
    cTotal = sqleval("select max(available), min(available) from example")
     
    // Access multiple row results
    sqleval('select state from example group by state')
    ? astring(_sqlvalues)
    AL,AR,CA,CO,CT,FL,GA,IA,IL,KY,LA,MA,MD,ME,NH,NJ,NY,OH,PA,VA,WI
    https://www.lianja.com/doc/index.php/SQLEVAL()

    Return the single row result of an SQL statement
    SQLVALUES
    Code:
    open database southwind
    cTotal = sqlvalues("select max(available), min(available) from example")
     
    // Access multiple row results
    sqlvalues('select state from example group by state')
    ? astring(_sqlvalues)
    AL,AR,CA,CO,CT,FL,GA,IA,IL,KY,LA,MA,MD,ME,NH,NJ,NY,OH,PA,VA,WI
    https://www.lianja.com/doc/index.php/SQLVALUES()

    Return the singleton result from any Lianja expression
    Code:
    select set("EXCLUSIVE") as Excl, time() as Time from sysresultset
    https://www.lianja.com/doc/index.php/SYSRESULTSET

    Code:
    // List order table order number and associated order_details table records
    open database southwind
    use order_details order orderid in 0
    use orders order orderid in 0
    set relation to orderid into order_details
    set skip on
    set skip to order_details
    list orders.orderid,;
      order_details.unitprice, order_details.quantity,;
      order_details.discount;
      for orders.employeeid = 5;
      and orders.shipvia = 3
    // List order value totals for selected orders
    open database southwind
    use order_details order orderid in 0
    use orders order orderid in 0
    set relation to orderid into order_details
    scan for orders.employeeid = 5 and orders.shipvia = 3
        select order_details
        echo sumvalues(unitprice * quantity * (1-discount))
        echo "\n"
        select orders
    endscan
    https://www.lianja.com/doc/index.php...nds_in_Recital

    Code:
    // Return all columns
    select * from products
    // Return specified columns
    select 'Product Details: ',  productid, upper(productname) as Name,;
      unitprice*unitsinstock as stockholding;
      from products
    // Return aggregate function results for the whole table
    select avg(unitprice) Average, min(unitprice) Minimum,;
      max(unitprice) Maximum, count(unitprice) Count,;
      sum(unitprice) Sum;
      from products
    // Return one row for each group of rows with an identical contactname column
    select distinct contactname from customers
    // Return one row for each group of identical rows
    select distinctrow contactname from customers
    // Return the first 10 rows
    select top 10 * from customers
    // Return the first 50% of rows
    select top 50 percent * from customers
    // Select a maximum of 10 rows, starting from row 6
    select * from customers limit 5,10
    // or
    select * from customers limit 10 offset 5
    // Return rows sorted in descending order by orderid then productid
    select orderid, productid, quantity;
      from order_details;
      order by orderid, 2 desc
    Code:
    // Return the orderid and total for each order
    select orderid as Order, sum(unitprice * quantity * (1-discount)) as Total;
      from order_details;
      group by orderid
    select productid from products into array array1
    select * from shippers into html shippers
    select employeeid, lastname, firstname from employees into table emp database newsouth
     
    select employeeid, lastname, firstname from employees save as xml emp
     
    select * from shippers to file shiptxt
    https://www.lianja.com/doc/index.php/Using_Recital_SQL





    All topics in [code examples] alphabetically: https://www.lianja.com/community/sho...ll=1#post19067
    Last edited by josipradnik; 2018-11-16 at 04:38.

Bookmarks

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Journey into the Cloud
Join us