Oracle tutorial

[Aesop]

Does anyone have a link to a good basic Oracle SQL tutorial?  I'm conversant in Sybase SQL and MS-SQL, but I'm new to Oracle SQL and some of the differences are puzzling to me .   For example, I'd like to find the meaning behind a normal outside join and an outside join created by something like 'table1 . ID = table2 . ID(+)' .

Any good sources for me to look at?

TIA .

[KGIII]

Wow. The last time I'd used Oracle was version 9 and resulted in a pretty much failed effort to understand ANSI because the stuffed shirts felt it wise to convert all of HR to Access. It might have been an even older version.

Anyhow, here's a good source:
http://www.db.cs....hing/sqltutorial/

You might also try here:
http://www.oracle...nology/index.html

This is not so complete and parts of it seem as if they might be below your level?
http://w2.syronex.com/jmr/edu/db/

In addition I'll go ahead and bump your post count up over 10 so that you can post with punctuation not being messed up.

[Aesop]

Excellent.  Thanks very much.

[KGIII]

Ah thank you for the first excellent question to this new section of the forum. This is a hobby site more than anything, kind of my own personal place to give to the community of geeks and budding geeks. It also makes a nice spot for me to nest down in during the harsh winter months.

I haven't done ANYTHING with Oracle since 2002 I think? It has been a number of years now, I was with a telecommunications company which is a polite way of saying that I was a Jr. Systems Administrator for a company that provided outsourced phone support and telemarketing. Ah the evils we humans will do for money.

Since reading your post an hour ago I keep going on mental trips back in time. We were state of the art, the bleeding edge, and even had as AS/400. I think it might have had the name changed to iSeries by then though.

As I recall one of the stuffed shirts had gotten it into their head that the mainframe was dead (yet this was the same suit that thought the thin-client was a bad idea) and as I was leaving they were converting the remainder of the 750 seats to (really) Windows ME. I kept up with an older friend who remained with the company for a while and I understand that it didn't go quite as bad as I'd expected it would but it is a moot point as just about a year later they shut the shop down, sold off the property, and moved to India and one of the South Pacific islands.

But I digress.

Somewhere in this slightly rusted brain of mine I may have more information available.

Let me see if I can remember - do NOT take this as correct but rather verify it. Please? It has been a LONG time.

In your particular question the (+) means an outer join?

Code:

TEAMNO    TNAME
---------- ----------
         1 Team1
         2 Team2

 PLAYERNO  TEAMNO    PNAME
---------- ---------- ----------
111 KGIII

The query:

Code:

select d.*,t.teamno,t.pname
from dept d,team t
where d.teamno=t.teamno(+);

That would return no values for Team2 even though there were no entries in Team2 and the returned data would be useful for someone who wants to get the complete roster listings. However simply dropping the (+) sign will return will give the values for Team2, which hasn't any entries in this case, and would be useful for seeing what teams needed additional players.

The outer join was included so that the stuffed suits could ask the managers questions about all the departments and the managers wanting to be evil and give them a returned value for all the information in a table regardless of it having any data. An outer join returns information about all the rows in a table even if they don't contain any information.

I'm pretty sure I buggered the code up though. You'll really want to verify that.

[Aesop]

Heh.  I remember my days dealing with an AS/400.  I wouldn't go back to that for all the gold in the world...

Code:

select d.*,t.teamno,t.pname
from dept d,team t
where d.teamno=t.teamno(+);

That would return no values for Team2 even though there were no entries in Team2 and the returned data would be useful for someone who wants to get the complete roster listings. However simply dropping the (+) sign will return will give the values for Team2, which hasn't any entries in this case, and would be useful for seeing what teams needed additional players.

I think you have it exactly opposite here.  Let's assume that the team table has no entries in it, or no entries where  t.teamno will match d.teamno.  If the two table are joined normally (aka an inside join),

Code:

select d.*,t.teamno,t.pname
from dept d,team t
where d.teamno=t.teamno;

there will be no results at all:  the query will return an empty set.

With an outside join of dept to team, the query results will show all the rows in dept AND any row in team where the teamno matches.

I do know that Oracle SQL allows for explicit outside joins.  Apparently the "(+)" construction acts as an implicit outside join.  However, this is a bit of nomenclature I've never seen in Sybase SQL or MS-SQL, which is why I'm curious.

I should note here that I've only been in this new job dealing with Oracle SQL for two weeks, so there's much I don't know.  But I'm learning...

[KGIII]

Amen for the "you really want to verify that clause." It has been WAY too long and I /think/ (+) is Oracle only though (I'm cheating and looking now) it appears in the UK version of the Access glossary at Microsoft though I've not dug any deeper. Link follows in a second.

Hmm... Again, you'll need to double check, but I think the (+) is Oracle's proprietary syntax.

http://www.google...fine%3Aouter+join
(A few links. Odd to see it at an Access site as I could have sworn it was Oracle only...)

Ah, here's a good code sample:

http://www.1keyda...sqlouterjoin.html

In fact, taking a look at the whole tutorial:
http://www.1keyda....com/sql/sql.html

That looks worth reading or, more accurately, worth bookmarking in my case.

Why the (+) is used I am not sure though I once understood it to be due to ambiguity in the OUTER JOIN command though that was about all I understood.

Can I ask a dumb question? With Larry Ellison's quest to buy the world, free database alternatives, and SQL Server 2k5 being reasonably priced, scalable, and solid...

For the company:

Why Oracle? Oracle DBA's are pretty rare, costly on American shores, and ouch... It's one tough row to hoe.

For you:

Just needed the job or wanted it? Just looking to gain a larger skillset? It is going to be a tough path I suspect.

*shivers*

Ah and to your signature... There are 11 kinds of people in the world: Those who can count, and those who can't.

[Aesop]

Thanks for the additional links.   FWIW I also found this one:  http://www.techonthenet.com/oracle/functions/, which has proven essential by showing me what functions I have access to.

Amen for the "you really want to verify that clause. " It has been WAY too long and I /think/ (+) is Oracle only though (I'm cheating and looking now) it appears in the UK version of the Access glossary at Microsoft though I've not dug any deeper.  Link follows in a second.

Hmm. . .  Again, you'll need to double check, but I think the (+) is Oracle's proprietary syntax.

I'm guessing you're correct.

Why the (+) is used I am not sure though I once understood it to be due to ambiguity in the OUTER JOIN command though that was about all I understood.

This also has the ring of truth to it.   If this isn't the real reason, it probably should be.

Can I ask a dumb question? With Larry Ellison's quest to buy the world, free database alternatives, and SQL Server 2k5 being reasonably priced, scalable, and solid. . . 

For the company:

Why Oracle? Oracle DBA's are pretty rare, costly on American shores, and ouch. . .  It's one tough row to hoe.

Well, the "company" I now work for is the University of Massachusetts, and the student information system they use is Peoplesoft, which has an Oracle back end.   Actually, that information is out-of-date since Oracle took over Peoplesoft; so now I guess it's more accurate to say that our student information system is all Oracle, all the time.   Is this the cheapest/easiest/most reasonable/whatever system for the University?  Silly question:  the University is a huge bureaucratic organization, and the database is huge, and these decisions are made by politicians in the Statehouse.   So Oracle it is, and Oracle it is likely to remain.

For you:

Just needed the job or wanted it? Just looking to gain a larger skillset? It is going to be a tough path I suspect.

Yes and yes.   I've been "underemployed" for a few years now, and I've wanted to work for the University for sometime:  the pay and the benefits are good.   And I'm always happy to pick up a few new skills.   

[KGIII]

Hmm... Something moved you back to your real post count. *sighs* I'll modify your post to make the link work, sorry 'bout that. Anyhow...

That makes sense. I understand that the benefits package is fantastic at most colleges and universities. That being Massachusetts who knows what sort of decisions they'll make? At least you'll be sure to have an interesting time there and gain a decent skillset.

Then again, MA did manage to recover mostly from the ODF fiasco fairly well as I understand.

Ah well. I used to live in Boston, I sometimes miss the place but mostly I am glad I don't have any traffic to deal with. I like the moose and deer better.

[KGIII]

I am not sure if you are responsible for such or if you know that this forum has this:

http://forum.kgii...jaHwnfG9yYWNsZQ==

(No, I didn't code the search links... *sighs* Though I am working to see if we can change that with a future version of SMF.)

[Tags:]

Jump to: Please select a destination: ----------------------------- News and Updates ----------------------------- => G³ Solutions => News => Sites of Interest => Suggestion Box => MyWebPeople - Blogs for Normal People ===> Updates and News ===> Bug Reports ===> Feature Requests ===> Support Requests ===> MyWebPeople Blogs Feedback ----------------------------- Microsoft Windows Help ----------------------------- => Windows 9x/ME Help => Windows 2k/NT Help => Windows XP Help => Windows Vista => Windows as a Server ----------------------------- Applications - Questions, Comments, or Discussion ----------------------------- => Office Products => General Security => Help with Other Software => Favorite Software ===> Freeware Downloads => Anything Else ----------------------------- Hardware ----------------------------- => General/Advice => Diagnostics => Installing/Upgrading ----------------------------- Web Development ----------------------------- => Web Development - General ===> Web Development - Resources => Databases => Dynamic Languages => Markup Languages ----------------------------- General ----------------------------- => General and Off Topic Banter. ===> Movies and Television ===> Music ===> Games => Humor => Test => Bit Bucket