Advanced SQL

...the real stuff

[Josk]

Hi,

Does anyone know of any resources on line for more advanced sql. Eg, producing the following in online of sql, using subqueries...

id product num sales num return
1 foo 12 132
2 foo2 123 232

etc...

So far I've got this, but is very inefficient!

[aspdaddy]

Hi josk,

I have found microsoft.public.access.queries
quite helpful in the past.

If you post some info I will try & help.

aspdaddy

[Josk]

Um...not using Access (Oracle and Postgres) Its reporting for a scalable application...

[aspdaddy]

But if it is SQL, not PLSQL specific it should not matter.

I use Access2000 build complex joins visually and paste it into sql navigator for oracle 8i - gets all the brackets in right places:)

[Dreamquick]

You might want to explore the "group by" statement as that allows a result-set to be grouped so that you could then use things like min(), max() and sum() on sub-item within each group. This would allow you to produce a simple report similar to the example you gave (assuming you can make a query to assemble the base data).

To be honest the best way to learn advanced SQL is to actually be using it day-in day-out to solve real problems, use the online help and of course google - sooner or later you will the answer or something you can modify to make the answer. As time goes on you find that you can work out the answers yourself without needing to lean on other sources for help.

- Tony

[PaulPaul]

But if it is SQL, not PLSQL specific it should not matter.
I use Access2000 build complex joins visually and paste it into sql navigator for oracle 8i - gets all the brackets in right places

Yuk. Now that is ugly code.

I hate it, I can barely use that access sql builder, reminds me of the talking paperclip.

To answer Josk's question: There are countless SQL tutorials and tips pages all over the net use Google and type in SQL tips, or SQL subquery. That should do the trick.

Paul

[aspdaddy]

Yuk. Now that is ugly code.

pls explain, this took a couple of seconds, i belive its eficient sql:

SELECT products.product_name, count (sales.qty), count (returns.qty)
FROM (products INNER JOIN returns ON products.id = returns.id) INNER JOIN sales ON products.id = sales.id
group by products.product_name;

[aspdaddy]

PaulPaul,

I use to hate that too :) Once I got skilled with sql by hand-coding it, i decided to use it more, its one of those tools thats proibably bad for a beginner but actually quite handy if you know sql well and need a quick way of joining lots of related tables.

[PaulPaul]

PaulPaul,
I use to hate that too Once I got skilled with sql by hand-coding it, i decided to use it more, its one of those tools thats proibably bad for a beginner but actually quite handy if you know sql well and need a quick way of joining lots of related tables.

There is no way the SQL code above was made by Access, it is missing the notorous [ ]'s.

I actually am extreamly skilled in advanced sql, and advanced indexing techniques. In my experience, only people who dont want to learn real SQL, use Access SQL-Builder. Then they call us, the professionals, to sort out to mess that Access made. Like I said before, I hate it.

IMHO, I dont think any self-respecting professional developer would dare let Access create a production SQL Statement for them.

Paul

[aspdaddy]

There is no way the SQL code above was made by Access, it is missing the notorous [ ]'s.

<added> Reson for missing notorious []'s : I use no spaces in fieldnames</added>

Im not gonna get in a flame here, but you are wrong. If you dont belive me open up access build the 3 tables, add them to qry view right click, view sql source

[edited by: aspdaddy at 1:06 pm (utc) on Aug. 29, 2002]

[Josk]

>You might want to explore the "group by" statement as that allows a >result-set to be grouped so that you could then use things like min(), >max() and sum() on sub-item within each group. This would allow you to >produce a simple report similar to the example you gave (assuming you >can make a query to assemble the base data)

Yes, been there, done, that. My current output is something like:

id fooname foocatory1 foocatory(n+1) < 12
# name 12 132

So, far this is using a subquery foreach category, and then removing un needed results. (this is inefficient)

I'm just looking for solid pointers to any advanced sql tutorials. I work in seo, I'm looking for reccomendations of actual sites, or books.

Access:
whereis access
access: /usr/bin/access /usr/man/man1/access.1.gz /usr/man/man2/access.2.gz

access - determine whether a file can be accessed

I have pgAccess though...

[PaulPaul]

aspdaddy,

I wouldnt post that, if I hadnt first checked it. I used the exact method you describe. Using OfficeXP. And I get [ ]'s

[celerityfm]

Josk-

Ahhh your not using a windows based os-- Access is a Micrsoft Product for windows, so nevermind!

If you could post an example of the tables involved in the query with some sample data we could probably come up with some SQL that you could use, but its tough to make an example without it--

As far as resources for advanced SQL, I suggest you download the Microsoft SQL Server Books Online-- you'll need a windows box to view it but it is a great reference for anyone making SQL regardless of SQL Server.

Download it from:

[download.microsoft.com...]

Or search on Google for: microsoft sql bol

Good luck!

[aspdaddy]

I have Oracle8 - PL/SQL Programming The essential Guide - Oracle Press
Good for PL/SQL8.0. Lots of examples on the cd