SQL

arupnanda's picture

Role of # in SQL*Plus

The # character is for commenting in SQL*Plus, right?

The character # has been mostly used for comments in many languages, such as shell scripts and python. Interestingly # is legal syntax in SQL scripting as well; but is it considered a comment? The answer is no; it's not. The purpose of # in SQL scripts is very different. and you should be very careful using it.

Entering # tells SQL*Plus to temporarily pauses what has been entered before and execute everything after that #sign, as if in a different session. Here is a usecase. Suppose you are writing this query:

SQL> select *
  2  from v$sesstat
  3  where

connor_mc_d's picture

SUM is better than DISTINCT

There is a good chance that (based on this blog post title) that you’re expecting a post on SQL, and that’s understandable. But I’ll come clean nice and early – that was just to lure you in Smile

The post is about SUM and DISTINCT, but not in the technical sense.

connor_mc_d's picture

Pi Day, March 14

Geeks around the world will be celebrating Pi day, a shameless excuse to put the month before the day like the Americans do so we can talk Mathematics on “3.14” day Smile

So what better way to show how cool SQL can be with some expressions to approach Pi with continuous series

All we really need to know by way of background is the CONNECT BY LEVEL trick to generate arbirtary sequences of row, eg


SQL> select level from dual connect by level <= 5;

     LEVEL
----------
         1
         2
         3
         4
         5

Now we can get some approximations to Pi using the some of the standard SQL functions SQRT and POWER

connor_mc_d's picture

Partition count for interval partitioned tables

When dealing with a RANGE partitioned table, the defined partitions dictate all of the data that can be placed into the table. For example, if I have a SALES table as per below

mwidlake's picture

Friday Philosophy – Your Experience can Keep You Ignorant

This week I was in an excellent presentation by Kerry Osborne about Outlines, SQL profiles, SQL patches and SQL Baselines. I’ve used three of those features in anger but when I looked at SQL Patches I just could not understand why you would use them – they looked to me like a very limited version of SQL Profiles.

JonathanGennick's picture

The Roots of Relational

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

Special guest this month: Chris Date

JonathanGennick's picture

Subqueries

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:

JonathanGennick's picture

Surprise Attack of the Nulls: Hidden Traps in the NOT IN

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


My Fear Nothing post last month introduced some of the trouble that nulls can cause by generating results that are counterintuitive to what you would expect from a superficial reading of a query. This month we’ll go deeper into the rabbit hole, because the situation with nulls is rather worse than you might think. 

Let’s imagine for a moment that we’re interested in answering this business question:

mwidlake's picture

The Book.

I’ve just added a picture to the right side of this site. It is for a book about SQL and PL/SQL. If you look at the image of the front cover, at the bottom is a list of authors and, near the end, is my name. It’s all finished and at the printers, but it is not out yet – It should be published in the next few weeks.

The British part of me wants to mumble and say “oh, yes, hmmm, I did contribute to a book… but I think you should concentrate on the chapters by the other chaps, they are proper experts, very clever gentleman and lady… I was just involved in a couple of lesser chapters…”

The part of me that spent weeks and months of late nights and long weekends writing it wants to scream “Look! LOOK! I damn well got it done! And it was way more painful than any of my author friends told me it would be as, despite their best efforts, I did not get How Hard Writing A Book Is!
I BLED FOR THAT BOOK!”

To prevent automated spam submissions leave this field empty.
Syndicate content