Solved

trying to learn sql join and having trouble...

Posted on 2008-10-19
6
452 Views
Last Modified: 2013-12-18
Greetings all

     I bought the book "Oracle 10g:sql" and am trying to figure out SQL.  I was able to set up the books example tables I am attaching the table's schema in hopes that it helps you help me (I have a sql file that sets up these tables if that would help?).  I had some trouble reading the sqlplus output in a windows terminal window so I am now trying isql*plus.  

OK, question time...
I am having trouble with some of the queries beyond the basic stuff (like looking at a whole table, or basic join of tables, creating tables, add, drop and the like).  The examples in the book are ok but I am having trouble seeing how they translate to real world questions.  The book seems to answer questions made to show how a specific function or clause works.

for example I understand how to use min to see the oldest/newest order but all I see is the one column, how do I get the whole row or record to show?  There are 3 orders with the same date (looking at oldest) how do I get all three records to show or just one?
the error i seem to get most with this example is : not a single-group group function when I try to find a way to display more than just the oldest/newest date.  
"select customer#, min(orderdate)"


I think I understand how to join two tables but once again I am lost on how to see a useful information from that join....like how would I see how many customers don't have an order?  I think the count function would be used but not sure how?  or what if I wanted to see a table with records (like those in customers) of customers that didn't have an order?

I have read about the functions but I guess I am not understanding their use in queries, like the avg: how would I go about finding the avg total cost of a customer's orders?


Thank you for your time and any effort put forth to help me understand.  This is my first real post here, if I have done anything wrong in my post I am sorry.  
tom
JustLeeBooks-Relational-Schema.doc
0
Comment
Question by:cybercookie72
  • 3
  • 3
6 Comments
 

Author Comment

by:cybercookie72
ID: 22751630
I searching through the forums here (doing my best to form a search that has something to do with my issues).  I found a post that showed me that you can nest a query and I was able to make one that I think returns the info Im looking for...the thing I was looking for was how to get more than just one column when looking for something like the oldest order date....

select * from orders
where orders.orderdate = (Select min(orders.orderdate) from orders);

now I think this returns all records where the orderdate is less than others(lowest), and this seems to return more than one record (I added and dropped records to see)

Am I on track?
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22751659

this subquery gives you  minimum orderdate within orders and in the where cluase it will pass that date and will retreive that order;

Select min(orders.orderdate) from orders
0
 

Author Comment

by:cybercookie72
ID: 22751980
OK, still working on trying to understand the join...
but first thank you chaituu..that query the lowest orderdate from orders, Im trying to find way to get the whole record to display..like the query I posted..give 3 records of lowest date, I guess I cant get one record unless I use more constraints to make said record distinct from the others.

OK ...back to join stuffs.  I was joining orders and customers and when looking at each table noticed some customers do not have orders and want to count how many there are.  I am can join them and get a table with customer# that have orders

SELECT COUNT(DISTINCT customer#) "# with orders"
FROM orders JOIN customers USING(customer#);

putput
# with orders
14

there are 20 customers and 21 orders, 14 customers with orders; some have multiple orders.

so how can I get some math in to the query?  how many customers do not have orders?

thanks again all
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 20

Accepted Solution

by:
chaitu chaitu earned 200 total points
ID: 22752002
Below query retrieves customers do not have orders.

SELECT * FROM CUSTOMERS C
WHERE
NOT EXISTS(    
SELECT 'X'
FROM ORDERS O
WHERE
O.CUST_ID=C.ID);
0
 
LVL 20

Expert Comment

by:chaitu chaitu
ID: 22752004
if you find count put count(*) instead of *;anyway check with ur tables.
0
 

Author Closing Comment

by:cybercookie72
ID: 31507561
Thank you for your help, your code seems to be right on.  I have not seen the EXISTS before and it took me a while to figure out what was going on.  I think I got a handle on the code you posted.  Thank you again
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now