SQL SELECT Basic form of the SQL SELECT

SQL SELECT • Basic form of the SQL SELECT statement is called a mapping or a SELECT-FROM-WHERE block SELECT FROM WHERE <attribute list> <table list> <condition> • <attribute list> is a list of attribute names whose values are to be retrieved by the query • <table list> is a list of the relation names required to process the query • <condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query

Query-by-example (QBE) • An alternative to SQL • A variety of versions

Querying a table List all data in share. SELECT * FROM SHR

Project • Choosing columns • A vertical slice

Project Report a firm’s name and price–earnings ratio. SELECT SHRFIRM, SHRPE FROM SHR

Project

Restrict • Choosing rows • A horizontal slice

Restrict Get all shares with a price-earnings ratio less than 12. SELECT * FROM SHR WHERE SHRPE < 12

Restrict

Project and restrict combo • Choosing rows and columns List the firm’s name, price, quantity, and dividend where share holding is at least 100, 000. SELECT SHRFIRM, SHRPRICE, SHRQTY, SHRDIV FROM SHR WHERE SHRQTY >= 100000

Primary key retrieval • A query using the primary key returns at most one row Report shares with code = ’AR’. SELECT * FROM SHR WHERE SHRCODE = 'AR'

Primary key retrieval • A query not using the primary key can return more than one row Report shares with a dividend of 2. 50. SELECT * FROM SHR WHERE SHRDIV = 2. 5

IN • Used with a list of values Report data on shares with codes of FC, AR, or SLG. SELECT * FROM SHR WHERE SHRCODE IN ('FC', 'AR', 'SLG') is equivalent to: SELECT * FROM SHR WHERE SHRCODE = 'FC' or SHRCODE = 'AR' SHRCODE = 'SLG' or

IN • Results:

NOT IN • Not in a list of values Report all shares other than those with the code CS or PT. SELECT * FROM SHR WHERE SHRCODE NOT IN ('CS', 'PT') is equivalent to: SELECT * FROM SHR WHERE SHRCODE <> 'CS' AND SHRCODE <> 'PT'

NOT IN • Results:

Ordering output • Ordering columns – Columns are reported in the order specified in the SQL command • Ordering rows – Rows are ordered using the ORDER BY clause

Ordering columns SELECT SHRCODE, SHRFIRM FROM SHR WHERE SHRPE = 10 SELECT SHRFIRM, SHRCODE FROM SHR WHERE SHRPE = 10

Ordering rows List all shares where PE is at least 10, and order the report in descending PE. Where PE ratios are identical, list shares in alphabetical order. SELECT * FROM SHR WHERE SHRPE >= 10 ORDER BY SHRPE DESC, SHRFIRM

Calculating Get firm name, price, quantity, and share yield. SELECT SHRFIRM, SHRPRICE, SHRQTY, SHRDIV/SHRPRICE*100 FROM SHR

Built-in functions • COUNT, AVG, SUM, MIN, and MAX Find the average share dividend. SELECT AVG(SHRDIV) FROM SHR What is each share’s average yield? SELECT AVG(SHRDIV/SHRPRICE*100) FROM SHR

Subqueries • A query within a query Report all shares with a PE ratio greater than the average for the portfolio. SELECT SHRFIRM, SHRPE FROM SHR WHERE SHRPE > (SELECT AVG(SHRPE) FROM SHR)

LIKE–Pattern matching List all firms with a name starting with ‘F. ’ SELECT SHRFIRM FROM SHR WHERE SHRFIRM LIKE “F*” List all firms containing ‘Ruby’ in their name. SELECT SHRFIRM FROM SHR WHERE SHRFIRM LIKE “*Ruby*”

LIKE–Pattern matching Find firms with ‘t’ as the third letter of their name. SELECT SHRFIRM FROM SHR WHERE SHRFIRM LIKE “? ? t*” Find firms not containing an ‘s’ in their name. SELECT SHRFIRM FROM SHR WHERE SHRFIRM NOT LIKE “*S*” AND SHRFIRM NOT LIKE “*S*”

DISTINCT • Eliminating duplicate rows Report the different values of the PE ratio SELECT DISTINCT SHRPE FROM SHR