Difference between revisions of "SQL SELECT"

// 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
 
// 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}
 
// 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
 
// 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)
 
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
 
// 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
 
// 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])
 
// 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
 
// 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"
 
// 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
 
// 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
 
// From nested select
SELECT COUNT(*) FROM (SELECT * FROM customers WHERE country = "UK")
// or
SELECT COUNT(*) AS ukcos FROM (SELECT * FROM customers WHERE country = "UK") ukcustomers