SQL Examples
(name of table in examples is classmates) (columns in table are lname, fname, miles, cats, dogs, brand) To select (view) records from a table: to select (view) all records from a table: select * from classmates; to select (view) all records but only a few columns from a table: select lname, miles, brand from classmates; to select (view) records that meet a certain condition from a table: select * from classmates where miles > 5; (Numerics no quotes) select * from classmates where miles >- 5; (greater than or equal to) select * from classmates where lname = ‘Smith’; (Alphas need quotes) select * from classmates where fname like ‘J%’; (fname begins with J) select * from classmates where fname like ‘%e’; (fname ends with e) select * from classmates where fname like ‘%rie%’; (fname has ‘rie’ in it) select * from classmates where fname like ‘J%’ and cats < 1; (both true) select * from classmates where miles < 2 or miles > 20 (Either True) select * from classmates where brand in (‘HP’, ‘IBM’); (for set notation) To sort (and view) records from a table: (ascending order is default) select * from classmates order by lname; (Ascending order) select * from classmates order by lname asc; (Ascending order) select * from classmates order by lname desc; (Descending order) select * from classmates order by lname, fname; (lname then fname) select * from classmates order by lname desc, fname; (lname Descending, fname Ascending order) select * from classmates where fname like ‘J%’ order by fname; To insert (add) a record into a table: insert into classmates values (‘John’, ‘Student’, 1.5, 2, 1, ‘IBM’); (list a value for every field in proper order) or list fields then their values insert into classmates (fname, lname, miles, cats, dogs, brand ) values (‘John’, ‘Student’, 1.5, 2, 1, ‘IBM’); To update (modify) records within a table: (there must be unique identification in/of record) (or all lname = ‘Student’ will be changed) update classmates set cats = 1 where lname = ‘Student’; update classmates set fname = ‘Jon’ where lname = ‘Student’; (Must update each column of record one at a time) To delete (remove) a record within a table: delete from classmates where lname = ‘Student’; (Caution ‘delete’ can remove all the table’s data!!!) delete from classmates; will remove everything in table & it will have to re-entered ! To alter (add column) a table; alter table classmates add idnum int; (adds column ‘idnum’, as an integer field) alter table classmates add idnum int auto_increment not null primary key; (auto_increment numbers consecutively at 1) (not null prevents a blank being inserted into table) (primary key prevents duplicate ID numbers) To alter (data type/size of column) in a table; alter table classmates modify (miles number(3,1), cats number(1), dogs int); To join two tables: Select * from classmates cm inner join teammembers tm on cm.lname = tm.lname; (only names on both tables) Select * from classmates cm left outer join teammembers tm on cm.lname = tm.lname; (all names on either table) Select * from classmates cm right outer join teammembers tm on cm.lname = tm.lname; Select cm.lname || ', ' || cm.fname as fullname, from classmates cm join teammembers tm on cm.lname = tm.lname; {oracle}(concatenates last name with first and adds comma between) To join two Oracle tables: Select * from classmates cm, teammembers tm where cm.lname = tm.lname; (only names on both tables) Select * from classmates cm, teammembers tm where cm.lname(+) = tm.lname; (names plus blanks from classmates) Select * from classmates cm, teammembers tm where cm.lname = tm.lname(+); (names plus blanks from teammembers) A "Recursive Join" joining a column in a table to another (must alias the table names) Select e.emp_id, e.name as Emp, e.mgr_id, m.name as Mgr from TableName e, TableName m Where e.mgr_id = m.emp_id Order by e.emp_id; (TableName contains fields: emp_id, name, mgr_id) (Results will display: emp_id, Emp(name), mgr_id, Mgr(name)) To find one of each kind of record: Select distinct lname from classmates order by lname; (no duplicate lnames will be listed) To parse (or read only part of) a table field: Select Left((Right(fieldname_13digitslong,10)),3) from tablename; (xxx000xxxxxxx) Select Left((Right(fieldname_13digitslong,7)),4) from tablename; (xxxxxx0000xxx) Select Left((Right(fieldname_9digitslong,4)),4) from tablename; (xxxxx0000) To Count the number of "lnames" in a table: Select count (lname) from TableName; To find Minimum or Maximum values for a column: Select min (miles) as minMiles from TableName; (the smallest value will return with the alias of minMiles) Select max (miles) as maxMiles from TableName; (the largest value will return with the alias of maxMiles) To find between oracle dates... Select * from TableName where RecordDate between sysdate-365 and sysdate; Record is within the last 365 days Select * from TableName where RecordDate + 365 > sysdate; Record is within the last 365 days Select * from TableName where RecordDate + 60 < sysdate; Record is more than 60 days old Select * from TableName where sysdate - 60 > RecordDate; Record is more than 60 days old An oracle timestamped date... "Select TO_CHAR(CreateDate,'"+TIMESTAMP_FORMAT+"')CreateDate, TO_CHAR(LastUpdateDate,'"+TIMESTAMP_FORMAT+"')LastUpdateDate where... " Select * from TableName where someDate between to_date('1/1/2007', 'mm/dd/yyyy') and to_date('1/1/2008', 'mm/dd/yyyy') Select * from TableName where someDate between TO_TIMESTAMP('01/01/2008 16:00','MM/DD/YYYY HH24:MI') and TO_TIMESTAMP('01/01/2008 16:30','MM/DD/YYYY HH24:MI') Find duplicated oracle records... Select lname, count(*) from TableName group by lname having count(*) > 1 To change the size or decimal places in a table... Alter table TableName modify (IsActive number(1), BasePay number(10,2)); To combine two tables (outer join) in Oracle... Select * from Tablename1 t1, Tablename2 t2, where t1.sameField = t1.sameField (+); the (+) will include null values for that table To find someField with data or values - that are not blank... Select * from Tablename where someField is not null; To find someField with values - that are equal to one in a list...(or not in list) Select * from TableName where someField in ('value1', 'value2', 'value3'); Select * from TableName where someField not in ('value1', 'value2', 'value3'); To combine two tables, using aliases for different or empty fields into one result set Select ID, ASSET_ID, NAME_LAST, MODEL_ID, MODEL_DESC, DEVICE_TYPE, INSTALL_DATE from TABLE_ONE where MODEL_ID = 270 UNION ALL Select ID, ASSET_ID, NAME_LAST, MODEL_ID, MODEL_DESC, DEVICE_TYPE, INSTALL_DATE from (SELECT ID, ID as ASSET_ID, (Select distinct ee.NAMELAST from TABLE_TWO tk, TABLE_EE ee where tk.EMPLOYEE_ID = ee.EMPLOYEEID) as NAME_LAST, MODEL_ID, (Select DESCRIPTION from TABLE_LOV lov where lov.ID = MODEL_ID) as MODEL_DESC, 'New Type' as DEVICE_TYPE, EMPTY_DATE as INSTALL_DATE from TABLE_TWO) where MODEL_ID = 270 order by NAME_LAST, INSTALL_DATE DESC To copy a column of data from one table into another table (with a common field) UPDATE tableOne one SET one.tableField = (SELECT two.tableField FROM tableTwo two WHERE two.commonField = one.commonField) To find differences between two tables: SELECT T1 . * , T2 . * FROM `table_one` T1, `table_two` T2 WHERE T1.email <> T2.email AND T1.id = T2.id To update one table from another: UPDATE `table_one` T1, `table_two` L2 set T1.fieldname = T2.fieldname, T1.email = T2.email, WHERE T1.email <> T2.email and T1.id = T2.id
Links to more: sql zoo , sql-syntax , Tech on the Net , Developer IQ , MySQL reference manual
