Home Tech >SQL Examples

SQL Examples

Getting info | Inserting info | Changing structure | Oracle Dates

I make no guarantees as to the suitabilities of these SQL statements for any purpose. Most of these have been used successfully with Oracle and are put here for my own reference.

To find users with a recorded last_visit within the last 10 minutes:
SELECT user_id, first_names, last_name
FROM users
WHERE last_visit > SYSDATE - 600/86400;

To show current date in specified format:
SELECT TO_CHAR(SYSDATE, 'Day Month DDth, YYYY') systemdate
FROM dual;

To search through two fields for a character string:
SELECT *
FROM information
WHERE UPPER(name) like UPPER(%query_string%)
OR UPPER(keywords) like UPPER(%query_string%)
ORDER BY name;

Nested select statement example:
SELECT fruit_name
FROM fruit
WHERE fruit_id =
(SELECT fruit_id
FROM produce
WHERE produce_id = 23);

To find an event_id with tomorrow's date:
SELECT event_id
FROM evlo
WHERE TO_CHAR(evlo_date, 'MM-DD-YYYY') = TO_CHAR(SYSDATE+1, 'MM-DD-YYYY');

To select a value which is one more than the current highest value of field1:
SELECT MAX(field1)+1
FROM table_name;

To return the first 10 rows from an SQL statement (you can also augment this by using the group clause and desc or asc options as needed):
SELECT column_name
FROM table_name
WHERE rownum < 11;

An example using table aliases to select from multiple tables:
SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc
FROM emp e, dept d WHERE e.deptno=d.deptno;

To get the names of all the fields of a table:
SELECT name from syscolumns where id = (
     SELECT id
     FROM sysobjects
     WHERE name='table_name'
);

To add a new record to a table, providing values for specific columns:
INSERT INTO table_name (field1, field2, field4)
VALUES ('value1', 'value2', 'value4');

To change the value of field1 in one or more records:
UPDATE table_name
SET field1 = 'new value'
WHERE field2 = 'specified value';

To change or add a value to one field for all records:
UPDATE table_name
SET field1 = 'new_value';

To delete all records in a table:
DELETE
FROM table_name;

To delete records whose 'person_id' value is 3:
DELETE
FROM table_name
WHERE person_id = 3;

To all data from one table to another:
INSERT INTO table1_name
SELECT * FROM table2_name;


To change the name of a column, you must first drop the old and create a new one:
ALTER table movie_theatre
DROP column movtheater_screens;

ALTER table movie_theatre
ADD (movtheatre_screens NUMBER(2));

To add a new field to the database, and have that new field be part of the primary key for that table:
You will first add the new field, then populate all instances of it. Then modify the new field to be not null. Next drop the existing primary key constraint (named pk_table_name here) and create a new one with the new field and the old primary key field.
ALTER table table_name
ADD (new_date_field date);

UPDATE table_name
SET new_date_field = '2002-05-19'
WHERE name_field = 'Jennifer';

UPDATE table_name
SET new_date_field = '2002-05-29'
WHERE name_field = 'Madelaine';

UPDATE table_name
SET new_date_field = '2002-08-01'
WHERE name_field = 'Karen';
And so on until all records have a non-null value in new_date_field

ALTER table table_name
MODIFY (new_date_field date NOT NULL);

ALTER table table_name
DROP constraint pk_table_name;

ALTER table table_name
ADD constraint pk_table_name primary key (old_pk_field,new_date_field);

To delete a table:
DROP table table_name;

To remove a not null constraint from a column:
alter table table_name modify (column_name datatype_name null);

As you may or may not know, Oracle's date format stores a date down to the very second. This leads to great use of the to_char function to present a date in a format pleasing to the eye. Date's can also be added to.

To view the current time in 24-hr format such as 13:45:32 :
SELECT to_char(SYSDATE, 'HH24:MI:SS')
FROM dual;

To view the current time in 12-hr format such as 1:45 pm:
SELECT to_char(SYSDATE, 'HH12:MI am')
FROM dual;

To add a month to today's date and display in default format:
SELECT add_months(SYSDATE,1)
FROM dual;

To add two months to today's date and display it in the format Saturday, January 1, 2005:
SELECT to_char(add_months(SYSDATE,2), 'Day, Month DD, YYYY')
FROM dual;

Here's a sampling of the formatting possibilities when using the to_char function with dates:

Element	Description
Date Format Elements
SCC or CC	Centry; S prefixes BC date with -
YYYY or SYYYY	Year; S prefixes BC date with -
YYY or YY or Y	Last 3, 2, or 1 digits of year
Y,YYY	Year with comma in this position
IYYY, IYY, IY, I	4, 3, 2, or 1 digit year based on the ISO standard
SYEAR or YEAR	Year spelled out; S prefixes BC date with -
BC or AD	BC/AD indicator
B.C. or A.D.	BC/AC indicator with periods
Q	Quarter of year
MM	Month, two-digit value
MONTH	Name of month padded iwth blanks to length of 9 characters
MON	Name of month, three-leter abbreviation
RM	Roman numeral month
WW or W	Week of year or month
DDD or DD or D	Day of year, month or week
DAY	Name of day padded iwth blanks to length of 9 characters
DY	Name of day; 3 letter abbreviation
J	Julian day; the number of days since 31 December 4713 BC
Time Format Elements
AM or PM	Meridian indicator
A.M. or P.M.	Meridian indicator with periods
HH or HH12 or HH24	Hour of day or hour(1-12) or hour(0-23)
MI	Minute (0-59)
SS	Second (0-59)
SSSSS	Seconds past midnight (0-86399)
Suffixes
TH	Ordinal number (i.e. DDTH for 5TH)
SP	Spelled-out number (i.e. DDSP for FIVE)
SPTH or THSP	Spelled-out ordinal numbers (i.e. DDSPTH for FIFTH)
Other Formatting Elements
/ , .	Punctuation is reproduced in the result
"of the"	Quoted string is reproduced in the result

Home Tech >SQL Examples