Using SQL to Select Data from Tables
select Feature, Section, Page from NEWSPAPER;
Babjee 9/2/2017 babjee@[Link]
set feedback off
set feedback 25
Show feedback
Show numwidth
Set numwidth 10
select 123456789123456789 num from DUAL;
Babjee 9/2/2017 babjee@[Link]
select, from, where, and order by
select Feature, Section, Page from NEWSPAPER
where Section = 'F';
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Equal, Greater Than, Less Than, Not Equal
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Sub Query : Query with in a Query
Babjee 9/2/2017 babjee@[Link]
select * from NEWSPAPER
where Section = (select Section from NEWSPAPER
where Page = 1);
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
Babjee 9/2/2017 babjee@[Link]
Combining data from Multiple tables
select City, Condition, Temperature from WEATHER;
select City, Longitude, EastWest, Latitude, NorthSouth from LOCATION;
Babjee 9/2/2017 babjee@[Link]
SQL Functions
SQL functions are built into Oracle Database and are available for use in
various appropriate SQL statements.i
Single Row functions : Function execute for every row
Tpes
String functions
Numeric functions
Conversion functions
Data Time Functions
Data Mining Functions
Summary Functions : function execute for group of rows
Analytic Functions
Analytic functions compute an aggregate value based on a group of rows.
They differ from aggregate functions in that they return multiple rows for
each group.
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
lower
Concatenation ( || )
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Rpad
lpad
Babjee 9/2/2017 babjee@[Link]
LTRIM, RTRIM, and TRIM
Babjee 9/2/2017 babjee@[Link]
LOWER, UPPER, and INITCAP
Babjee 9/2/2017 babjee@[Link]
length
SubString
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Instr
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
ASCII and CHR
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Soundex
Number Functions
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
nvl
Abs
Babjee 9/2/2017 babjee@[Link]
CEIL
CEIL (for ceiling) simply produces the smallest integer (or whole
number) that is greater than or
equal to a specific value
FLOOR
FLOOR returns the largest integer equal to or less than a specific
value
Babjee 9/2/2017 babjee@[Link]
MOD
MOD(100,10) = 0
MOD(22,23) = 22
MOD(10,3) = 1
MOD(-30.23,7) = -2.23
MOD(4.1,.3) = .2
POWER
POWER(3) ==3
SQRT: Square Root
SQRT(64) = 8
SQRT(66.666) = 8.16492498
SQRT(4) = 2
Babjee 9/2/2017 babjee@[Link]
ROUND and TRUNC
Babjee 9/2/2017 babjee@[Link]
Aggregate Functions
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Date Arithmetic
select SysDate from DUAL;
SYSDATE
---------
28-FEB-08
select Current_Date from DUAL;
CURRENT_D
---------
28-FEB-08
select SysTimeStamp from DUAL;
SYSTIMESTAMP
---------------------------------------------
28-FEB-08 04.49.31.718000 PM -05:00
Babjee 9/2/2017 babjee@[Link]
select ADD_MONTHS(CelebratedDate,6)
AS FeastDay
from HOLIDAY
where Holiday like 'FAST%';
Feast Day
---------
22-AUG-04
Babjee 9/2/2017 babjee@[Link]
select ADD_MONTHS(CelebratedDate,-6) - 1 AS LastDay
from HOLIDAY
where Holiday = 'COLUMBUS DAY';
Last Day
---------
10-APR-04
select Holiday, LEAST(ActualDate, CelebratedDate) AS First,
ActualDate, CelebratedDate
from HOLIDAY
where ActualDate - CelebratedDate != 0;
select LEAST('20-JAN-04','20-DEC-04') from DUAL;
LEAST('20
---------
20-DEC-04
select LEAST( TO_DATE('20-JAN-04'), TO_DATE('20-DEC-04') )
from DUAL;
LEAST(TO_
---------
20-JAN-04
Babjee 9/2/2017 babjee@[Link]
select LAST_DAY(CycleDate) AS EndMonth
from PAYDAY;
End Month
---------
31-JAN-04
MONTHS_BETWEEN Two Dates
Babjee 9/2/2017 babjee@[Link]
select SysDate AS Today,
LAST_DAY(ADD_MONTHS(SysDate,6)) + 1 Review
from DUAL;
TODAY REVIEW
--------- ---------
28-FEB-08 01-SEP-08
Babjee 9/2/2017 babjee@[Link]
TO_DATE and TO_CHAR Formatting
TO_DATE converts a character string or a number into an Oracle
date, whereas TO_CHAR converts an Oracle date into a character string
select BirthDate,
TO_CHAR(BirthDate,'MM/DD/YY') AS Formatted
from BIRTHDAY
where FirstName = 'VICTORIA';
select BirthDate, TO_CHAR(BirthDate,'YYMM>DD') Formatted
from BIRTHDAY
where FirstName = 'VICTORIA';
BIRTHDATE FORMATTED
--------- ------------------------------
20-MAY-49 4905>20
select BirthDate,
TO_CHAR(BirthDate,'Month, DDth "in, um,"
YyyY') AS Formatted
from BIRTHDAY ;
Babjee 9/2/2017 babjee@[Link]
select BirthDate, TO_CHAR(BirthDate,'Month, ddth, YyyY')
AS Formatted
from BIRTHDAY;
select BirthDate, TO_CHAR(BirthDate,'fmMonth, ddth, YyyY')
AS Formatted
from BIRTHDAY;
select FirstName, BirthDate, TO_CHAR(BirthDate,
'"Baby Girl on the" Ddspth "of" fmMonth, YYYY, "at" HH:MI')
AS Formatted
from BIRTHDAY
where FirstName = 'VICTORIA';
select TO_CHAR(SysDate,'HH:MM:SS') NowWrong
from DUAL;
NOWWRONG
--------
05:02:48
Babjee 9/2/2017 babjee@[Link]
TO_DATE
select TO_DATE('28-FEB-08','DD-MON-YY') from DUAL;
TO_DATE('
---------
28-FEB-08
select TO_DATE('02/28/08') from DUAL;
select TO_DATE('02/28/08','MM/DD/YY') from DUAL;
select TO_DATE(11051946,'MMDDYYYY') from DUAL;
TO_DATE(1
---------
05-NOV-46
Babjee 9/2/2017 babjee@[Link]
select Holiday, CelebratedDate
from HOLIDAY
where CelebratedDate IN ('01-JAN-04', '22-FEB-04');
select Holiday, CelebratedDate
from HOLIDAY
where CelebratedDate IN
(TO_DATE('01-JAN-2004','DD-MON-YYYY'),
TO_DATE('22-FEB-2004','DD-MON-YYYY'));
Babjee 9/2/2017 babjee@[Link]
EXTRACT
select BirthDate,
EXTRACT(Month from BirthDate) AS Month
from BIRTHDAY
where FirstName = 'VICTORIA';
BIRTHDATE MONTH
--------- ----------
20-MAY-49 5
select SUBSTR(TO_CHAR(948033515),1,5)||'-'||
SUBSTR(TO_CHAR(948033515),6) AS Zip
from DUAL;
Babjee 9/2/2017 babjee@[Link]
DECODE
select Feature, Section,
DECODE(Page,'1','Front Page','Turn to '||Page)
from NEWSPAPER;
The Use of group by and having
select CategoryName, COUNT(*)
from BOOKSHELF
group by CategoryName
having COUNT(*) > 5;
select CategoryName, COUNT(*), AVG(Rating)
from BOOKSHELF
group by CategoryName
having AVG(Rating) >
(select AVG(Rating) from BOOKSHELF);
Babjee 9/2/2017 babjee@[Link]
select CategoryName,
COUNT(*), AVG(Rating)
from BOOKSHELF
where Rating > 1
group by CategoryName
having CategoryName like
'A%'
order by COUNT(*) desc;
Babjee 9/2/2017 babjee@[Link]
JOINS
select distinct [Link], [Link]
from CATEGORY C, BOOKSHELF B, BOOKSHELF_CHECKOUT
BC where [Link] = [Link]
and [Link] = [Link];
OUTER JOIN
select [Link], MAX([Link] - [Link])
"Most Days Out"
from BOOKSHELF_CHECKOUT BC, BOOKSHELF B
where [Link] (+) = [Link]
group by [Link];
select [Link], MAX([Link] - [Link])
"Most Days Out"
from BOOKSHELF_CHECKOUT BC right outer join BOOKSHELF
B
on [Link] = [Link]
group by [Link];
Babjee 9/2/2017 babjee@[Link]
select [Link], MAX([Link] - [Link])
"Most Days Out"
from BOOKSHELF_CHECKOUT BC full outer join
BOOKSHELF B
on [Link] = [Link]
group by [Link];
NATUAL JOIN
select [Link]
from BOOK_ORDER BO inner join BOOKSHELF B
on [Link] = [Link];
Babjee 9/2/2017 babjee@[Link]
UNION, INTERSECT, and MINUS
select Title from BOOKSHELF
where Title < 'M%'
union
select Title from BOOK_ORDER;
select Title from BOOKSHELF
where Title < 'M%'
union all
select Title from BOOK_ORDER order by Title;
select Title from BOOKSHELF
intersect
select Title from BOOK_ORDER order by Title;
select Title from BOOK_ORDER
minus
select Title from BOOKSHELF
order by Title;
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]
Babjee 9/2/2017 babjee@[Link]