Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

September 2016

Subqueries

Submitted by JonathanGennick on Fri, 2016-09-30 19:18

Subqueries can take the place of column and table references, helping you
to formulate queries that otherwise would be more difficult or less
efficient to express using join operations. 



Read the full post at www.gennick.com/database.

Subqueries

Submitted by JonathanGennick on Fri, 2016-09-30 19:18

Ninth in a series of posts in response to Tim Ford's #EntryLevel Challenge.

Subqueries are queries within a query – one SELECT nested within another. They can take the place of column and table references, helping you to formulate queries that otherwise would be more difficult or less efficient to express using join operations. 

As Columns

Following is an example of a subquery filling a position typically occupied by a column name or expression. The goal of the query is to list the number of products per subcategory.

SELECT ps.name,
    (SELECT COUNT(*)
    FROM Production.Product p
    WHERE p.ProductSubcategoryID = ps.ProductSubcategoryID) AS sub_count
FROM production.ProductSubcategory ps

The output looks as  follows:

Subqueries

Submitted by JonathanGennick on Fri, 2016-09-30 19:18

Ninth in a series of posts in response to Tim Ford's #EntryLevel Challenge.

Subqueries are queries within a query – one SELECT nested within another. They can take the place of column and table references, helping you to formulate queries that otherwise would be more difficult or less efficient to express using join operations. 

As Columns

Following is an example of a subquery filling a position typically occupied by a column name or expression. The goal of the query is to list the number of products per subcategory.

SELECT ps.name,
    (SELECT COUNT(*)
    FROM Production.Product p
    WHERE p.ProductSubcategoryID = ps.ProductSubcategoryID) AS sub_count
FROM production.ProductSubcategory ps

The output looks as  follows:

DevOps and Source Control

Submitted by dbakevlar on Fri, 2016-09-30 18:21

The topic of DevOps and and Agile are everywhere, but how often do you hear Source Control thrown into the mix?  Not so much in public, but behind the closed doors of technical and development meetings when Agile is in place, it’s a common theme.  When source control isn’t part of the combination, havoc ensues and a lot of DBAs working nights on production with broken hearts.

Digital archive of the NoCOUG Journal—The best little Oracle users group magazine in the world

Submitted by iggy_fernandez on Fri, 2016-09-30 14:00

All issues since May 2001 (Vol. 15 No. 2)(read more)

NoCOUG Journal: A Few of My Favorite Things

Submitted by iggy_fernandez on Fri, 2016-09-30 14:00

As the editor of the NoCOUG Journal for ten years and counting, I’ve had the interview to interview some of the best minds in the Oracle world. These are a few of my favorite things they said.(read more)

SQL pretzels created while experimenting with hallucinogens?

Submitted by iggy_fernandez on Fri, 2016-09-30 14:00

In an interview for the NoCOUG Journal (http://www.nocoug.org/Journal/NoCOUG_Journal_200608.pdf#page=4), Steven Feuerstein was asked: “SQL is a set-oriented non-procedural language; i.e., it works on sets and does not specify access paths. PL/SQL on the other hand is a record-oriented procedural language, as is very clear from the name. What is the place of a record-oriented procedural language in the relational world?”

Steven replied: “Its place is proven: SQL is not a complete language. Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens. We need more than SQL to build our applications, whether it is the implementation of business rules or application logic.

How to rebuild a 12 TB index that you accidentally dropped from a 55 TB table

Submitted by iggy_fernandez on Fri, 2016-09-30 14:00

Once upon a time there was a very experienced database administrator who accidentally dropped a 12 TB index from a 55 TB table. The question I had was “How did he fix it?”(read more)

The Little Oracle Users Group That Could

Submitted by iggy_fernandez on Fri, 2016-09-30 14:00

It requires a vast amount of work to organize a tech­nical conference and publish a printed journal every quarter. No sooner has a conference ended and a journal mailed than it is time to start work on the next conference and the next journal. NoCOUG has no funding from Oracle Corporation and a miniscule budget compared to the national and inter­national user groups, but the NoCOUG volunteers have always managed to pull it off, quarter after quarter for 30 long years.(read more)

Nice Social Media Profile Picture! Oh… Err…

Submitted by mwidlake on Fri, 2016-09-30 10:54

A few (months/weeks/days/hours)* ago I saw a friend request on Facebook. I looked at their profile which indicated that they were kind-of in my technical arena and the profile picture made me think “Wow – that’s an attractive person!” and I was about to click on the accept button.

And stopped.

I only friend people on Facebook who I know. By that I mean I have either met in real life and liked or have had a LOT of contact with through social media and liked. People who, if they were delayed at Stansted airport at midnight and needed a place to sleep, I’d be happy to go pick them up and bring them home to stay in my spare room.