Difference between revisions of "SQL SELECT"

// Select all rows, including duplicates, from an encrypted table
EXEC SQL
SELECT ALL *
  FROM enctab<key_1,key2,key_3>;
 
// Select "last_name" column from rows with a unique "last_name"
EXEC SQL
SELECT DISTINCT last_name FROM customer;
 
// Select "last_name" column from unique rows
EXEC SQL
SELECT DISTINCTROW last_name FROM customer;
 
// Select first 10 rows
EXEC SQL
SELECT TOP 10 * FROM accounts;
 
// Select first 50% of the rows
EXEC SQL
SELECT TOP 50 PERCENT * FROM accounts;
 
// Crystal Reports / ODBC style JOINS: LEFT OUTER, RIGHT OUTER, FULL OUTER
EXEC SQL
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};
 
EXEC SQL
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};
 
EXEC SQL
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};
 
// JOINS: INNER, LEFT OUTER, RIGHT OUTER, CROSS, FULL
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
  FROM customer
  INNER JOIN accounts
  ON customer.account_no = accounts.account_no;
 
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
  FROM customer
  LEFT OUTER JOIN accounts
  ON customer.account_no = accounts.account_no;
 
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
  FROM customer
  RIGHT OUTER JOIN accounts
  ON customer.account_no = accounts.account_no;
 
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
  FROM customer
  CROSS JOIN accounts;
 
EXEC SQL
SELECT customer.account_no, customer.last_name, accounts.ord_value
  FROM customer
  FULL OUTER JOIN accounts;
 
// JOINs with nested SELECTs
EXEC SQL
SELECT contactname FROM customers WHERE customerid IN (SELECT customerid FROM orders WHERE orderdate = {07/19/1996}); 
 
EXEC SQL
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;
 
EXEC SQL
SELECT contactname FROM customers WHERE ctod("07/19/1996") > ANY (SELECT orderdate FROM orders WHERE customers.customerid = orders.customerid);
 
EXEC SQL
SELECT orders.customerid FROM orders WHERE 30 > ALL (SELECT sum(quantity) FROM order_details WHERE orders.orderid = order_details.orderid);
 
EXEC SQL
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); 
 
EXEC SQL
SELECT lastname FROM employees WHERE exists
  (SELECT * FROM orders WHERE employees.employeeid = orders.employeeid AND orderdate = CTOD("11/11/1996")) ;
 
EXEC SQL
SELECT lastname FROM employees WHERE not exists
  (SELECT * FROM orders WHERE employees.employeeid = orders.employeeid AND orderdate = CTOD("11/11/1996")) ;
 
EXEC SQL
SELECT companyname,(select MAX(orderid) FROM orders as o1 WHERE o1.customerid = c1.customerid) FROM customers as c1;
 
// Multiple JOINs
EXEC SQL
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;
 
// Select account number and order value details for Massachusetts customers 
EXEC SQL
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.
EXEC SQL
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"
EXEC SQL
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
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
  FROM customer
  WHERE balance > 0
  GROUP BY limit;
 
// Select total and average balance for all overdue accounts, grouped by "limit" with column aliases
// For Massachusetts customers only
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
  FROM customer
  WHERE balance > 0
  GROUP BY limit
  HAVING state = "MA";
 
// Save into an array
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
  FROM customer
  WHERE balance > 0
  INTO ARRAY temp;
 
// Create a cursor
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
  FROM customer
  WHERE balance > 0
  INTO CURSOR temp;
 
// Save as a table
EXEC SQL
SELECT SUM(balance) AS Total, AVG(balance) AS Average
  FROM customer
  WHERE balance > 0
  INTO TABLE temp DATABASE mydbc;
 
//or
 
EXEC SQL
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. 
EXEC SQL
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"
 
// Save in text file format
EXEC SQL
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
EXEC SQL
SELECT account_no
  FROM customer
  WHERE state = 'MA'
  UNION SELECT account_no
  FROM accounts
  WHERE balance > 0
  ORDER BY account_no;