SQL Newbie

29 Jun 2006 2:19 AM

Bulldog

Hello,

I am trying to write some basic queries and am running into issues.

The first one I am working on is this a query that determines which
products cost less than the average cost of products available from a
particular store.

The second one I am working with is writing a query that looks at a
client (Bill) and determines his location and then finds other
customers with that same location.

ANY HELP IS APPRECIATED!

29 Jun 2006 2:41 AM

Geoff N. Hiten

Since you didn't provide DDL or examples, I can only describe some generic
approaches. Both of the scenarios you describe are often solved with a
self-join. That is, referencing the same table more than once in a single
query. For your second example, you would reference the clients table
twice, once to find Bill's location, and another time to find everyone with
the same location. For your first problem, you calculate the average and
use that as a "Where" condition on a products query.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Show quote

"Bulldog" <jfranc***@woh.rr.com> wrote in message
news:1151547554.536783.106460@m73g2000cwd.googlegroups.com...
> Hello,
>
> I am trying to write some basic queries and am running into issues.
>
> The first one I am working on is this a query that determines which
> products cost less than the average cost of products available from a
> particular store.
>
> The second one I am working with is writing a query that looks at a
> client (Bill) and determines his location and then finds other
> customers with that same location.
>
> ANY HELP IS APPRECIATED!
>

29 Jun 2006 6:15 AM

Arnie Rowland

These two examples (Using the Northwind database) should serve to provide you an idea about one way to get the information you desire.

USE Northwind
GO

SELECT
     ProductID
   , ProductName
   , UnitPrice
FROM Northwind..Products
WHERE UnitPrice < ( SELECT avg( UnitPrice )
                    FROM   Northwind..Products
                  )
ORDER BY UnitPrice

SELECT
     CustomerID
   , CompanyName
   , Country
FROM Northwind..Customers
WHERE Country = ( SELECT Country
                   FROM   Northwind..Customers
                   WHERE CustomerID = 'Anton'
                )
ORDER BY CompanyName

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Bulldog" <jfranc***@woh.rr.com> wrote in message news:1151547554.536783.106460@m73g2000cwd.googlegroups.com...
> Hello,
>
> I am trying to write some basic queries and am running into issues.
>
> The first one I am working on is this a query that determines which
> products cost less than the average cost of products available from a
> particular store.
>
> The second one I am working with is writing a query that looks at a
> client (Bill) and determines his location and then finds other
> customers with that same location.
>
> ANY HELP IS APPRECIATED!
>

29 Jun 2006 6:24 AM

Roji. P. Thomas

USE Northwind
GO
-- Products with Price Less than Average Price
SELECT * --Use Columnlist
FROM Products
WHERE UnitPrice < (SELECT AVG(UnitPrice) FROM Products)

-- Get A client's location and find other customers in the same location

SELECT * --Use Columnlist
FROM Customers
WHERE City = (SELECT City FROM Customers WHERE CustomerID = 'AROUT')

--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com

Show quote

"Bulldog" <jfranc***@woh.rr.com> wrote in message
news:1151547554.536783.106460@m73g2000cwd.googlegroups.com...
> Hello,
>
> I am trying to write some basic queries and am running into issues.
>
> The first one I am working on is this a query that determines which
> products cost less than the average cost of products available from a
> particular store.
>
> The second one I am working with is writing a query that looks at a
> client (Bill) and determines his location and then finds other
> customers with that same location.
>
> ANY HELP IS APPRECIATED!
>

29 Jun 2006 10:23 PM

Bulldog

Appreciate the help!

On the first statement, I am only getting products < average of all
products. I need products < average of products from a particular
location/store.

Roji. P. Thomas wrote:
Show quote

> USE Northwind
> GO
> -- Products with Price Less than Average Price
> SELECT * --Use Columnlist
> FROM Products
> WHERE UnitPrice < (SELECT AVG(UnitPrice) FROM Products)
>
> -- Get A client's location and find other customers in the same location
>
> SELECT * --Use Columnlist
> FROM Customers
> WHERE City = (SELECT City FROM Customers WHERE CustomerID = 'AROUT')
>
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "Bulldog" <jfranc***@woh.rr.com> wrote in message
> news:1151547554.536783.106460@m73g2000cwd.googlegroups.com...
> > Hello,
> >
> > I am trying to write some basic queries and am running into issues.
> >
> > The first one I am working on is this a query that determines which
> > products cost less than the average cost of products available from a
> > particular store.
> >
> > The second one I am working with is writing a query that looks at a
> > client (Bill) and determines his location and then finds other
> > customers with that same location.
> >
> > ANY HELP IS APPRECIATED!
> >

29 Jun 2006 10:43 PM

Chris Lim

Bulldog wrote:

> Appreciate the help!
>
> On the first statement, I am only getting products < average of all
> products. I need products < average of products from a particular
> location/store.

How about something like:

USE Northwind
GO
-- Products with Price Less than Average Price
SELECT p.* --Use Columnlist
FROM Products P
INNER JOIN Suppliers S
      ON S.SupplierID = P.SupplierID
WHERE UnitPrice < (SELECT AVG(UnitPrice)
                           FROM Products P2
                           INNER JOIN Suppliers S2
                                ON S2.SupplierID = P2.SupplierID
                           WHERE S2.City = S.City
                           AND IsNull(S2.Region, '') = IsNull(S.Region,
'')
                           AND S2.Country = S.Country)

29 Jun 2006 11:00 PM

Bulldog

Here is the actual scenario because I am kind of confused using the
northwind.

Tables    Inv                 Product          Store

Fields    S_Num          Prod_Code     S_Num
             Prod_Code    Desc              Name
            Quantity         Price              Location
                                                       Rep
Chris Lim wrote:
Show quote

> Bulldog wrote:
> > Appreciate the help!
> >
> > On the first statement, I am only getting products < average of all
> > products. I need products < average of products from a particular
> > location/store.
>
> How about something like:
>
> USE Northwind
> GO
> -- Products with Price Less than Average Price
> SELECT p.* --Use Columnlist
> FROM Products P
> INNER JOIN Suppliers S
>       ON S.SupplierID = P.SupplierID
> WHERE UnitPrice < (SELECT AVG(UnitPrice)
>                            FROM Products P2
>                            INNER JOIN Suppliers S2
>                                 ON S2.SupplierID = P2.SupplierID
>                            WHERE S2.City = S.City
>                            AND IsNull(S2.Region, '') = IsNull(S.Region,
> '')
>                            AND S2.Country = S.Country)

29 Jun 2006 11:09 PM

Chris Lim

Bulldog wrote:

> Here is the actual scenario because I am kind of confused using the
> northwind.
>
> Tables    Inv                 Product          Store
>
> Fields    S_Num          Prod_Code     S_Num
>              Prod_Code    Desc              Name
>             Quantity         Price              Location
>                                                        Rep

Now I'm confused! How can you have the same price for a product across
all stores, and then want to work out the average price per store?

29 Jun 2006 11:15 PM

Chris Lim

Chris Lim wrote:

> Bulldog wrote:
> > Here is the actual scenario because I am kind of confused using the
> > northwind.
> >
> > Tables    Inv                 Product          Store
> >
> > Fields    S_Num          Prod_Code     S_Num
> >              Prod_Code    Desc              Name
> >             Quantity         Price              Location
> >                                                        Rep
>
> Now I'm confused! How can you have the same price for a product across
> all stores, and then want to work out the average price per store?

Oh I think I get it now. How about:

SELECT I.*
FROM    Inv I
INNER JOIN Product P
    ON P.Prod_Code = I.Prod_Code
WHERE P.Price < (SELECT AVG(P2.Price)
                             FROM    Inv I2
                             INNER JOIN Product P2
                                 ON P2.Prod_Code = I2.Prod_Code
                             WHERE I2.S_Num = I.S_Num)

29 Jun 2006 11:21 PM

Bulldog

The relationship for the store to product price is all done in the Inv
table. Does that help?
Chris Lim wrote:
Show quote

> Bulldog wrote:
> > Here is the actual scenario because I am kind of confused using the
> > northwind.
> >
> > Tables    Inv                 Product          Store
> >
> > Fields    S_Num          Prod_Code     S_Num
> >              Prod_Code    Desc              Name
> >             Quantity         Price              Location
> >                                                        Rep
>
> Now I'm confused! How can you have the same price for a product across
> all stores, and then want to work out the average price per store?

30 Jun 2006 8:56 PM

Bulldog

Worked like a charm! Thank you!

Any chance you can assist with a stored procedure. I've got it started
but am running into issues. I will give you the actual whole scenario
so you can see the big picture.

Stored Procedure with a Single input parameter that allows the user to
supply a product code. I need a SELECT that retrieves the description
and price of the product from the PRODUCT table with a code matching
the one supplied in the parameter. I also need an exception section
that traps the NO_DATA_FOUND error and indicates to the user that the
product code is invalid. I then want to be able to print a line
displaying the product code, description, and price.

Bulldog wrote:
Show quote

> The relationship for the store to product price is all done in the Inv
> table. Does that help?
> Chris Lim wrote:
> > Bulldog wrote:
> > > Here is the actual scenario because I am kind of confused using the
> > > northwind.
> > >
> > > Tables    Inv                 Product          Store
> > >
> > > Fields    S_Num          Prod_Code     S_Num
> > >              Prod_Code    Desc              Name
> > >             Quantity         Price              Location
> > >                                                        Rep
> >
> > Now I'm confused! How can you have the same price for a product across
> > all stores, and then want to work out the average price per store?

30 Jun 2006 11:40 PM

Chris Lim

Bulldog wrote:

> Stored Procedure with a Single input parameter that allows the user to
> supply a product code. I need a SELECT that retrieves the description
> and price of the product from the PRODUCT table with a code matching
> the one supplied in the parameter. I also need an exception section
> that traps the NO_DATA_FOUND error and indicates to the user that the
> product code is invalid. I then want to be able to print a line
> displaying the product code, description, and price.

Something like this would work, but how you handle the error and print
the results depends on how you are going to be calling the stored
procedure (I assume from a front-end client such as VB or a web page?)

IF OBJECT_ID('get_product_info') is not NULL
DROP PROCEDURE get_product_info
go

CREATE PROCEDURE get_product_info @Prod_Code varchar(20) -- or whatever
the correct type is
AS
BEGIN
IF NOT EXISTS( SELECT 1 FROM Product WHERE Prod_Code = @Prod_Code)
BEGIN
      RAISERROR('No Data Found.', 16, 1)
      RETURN 1
END

SELECT Prod_code, Desc, Price
FROM    Product
WHERE Prod_Code = @Prod_Code

END
go

EXEC get_product_info @Prod_Code = 'XXXX'

1 Jul 2006 3:08 AM

Geoff N. Hiten

Chruis,

Thanks for picking this one up. I was unable to connect during the day.

--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP

Show quote

"Chris Lim" <blackca***@hotmail.com> wrote in message
news:1151710844.781199.114350@75g2000cwc.googlegroups.com...
> Bulldog wrote:
>> Stored Procedure with a Single input parameter that allows the user to
>> supply a product code. I need a SELECT that retrieves the description
>> and price of the product from the PRODUCT table with a code matching
>> the one supplied in the parameter. I also need an exception section
>> that traps the NO_DATA_FOUND error and indicates to the user that the
>> product code is invalid. I then want to be able to print a line
>> displaying the product code, description, and price.
>
> Something like this would work, but how you handle the error and print
> the results depends on how you are going to be calling the stored
> procedure (I assume from a front-end client such as VB or a web page?)
>
> IF OBJECT_ID('get_product_info') is not NULL
> DROP PROCEDURE get_product_info
> go
>
> CREATE PROCEDURE get_product_info @Prod_Code varchar(20) -- or whatever
> the correct type is
> AS
> BEGIN
> IF NOT EXISTS( SELECT 1 FROM Product WHERE Prod_Code = @Prod_Code)
> BEGIN
>      RAISERROR('No Data Found.', 16, 1)
>      RETURN 1
> END
>
> SELECT Prod_code, Desc, Price
> FROM    Product
> WHERE Prod_Code = @Prod_Code
>
> END
> go
>
>
> EXEC get_product_info @Prod_Code = 'XXXX'
>

1 Jul 2006 5:42 PM

Bulldog

When I run this procedure, it never ends. I don't get prompted for a
number or anything. I enter a number and another SQL line comes up.

CREATE PROCEDURE DISPLAY_PRODUCT AT @PRODUCT_CODE VARCHAR(5)
AS
BEGIN
IF NOT EXITS(SELECT 1 FROM CIS269_PRODUCT WHERE
PRODUCT_CODE=@PRODUCT_CODE)
BEGINRAISEERROR('NO DATA FOUND.',16,1)
RETURN1
END

SELECT PRODUCT_CODE, PRODUCT_DESCRIPTION, PRODUCT_PRICE
FROM CIS268_PRODUCT
WHERE PRODUCT_CODE=@PRODUCT_CODE
END
GO
EXEC DISPLAY_PRODUCT @PRODUCT_CODE='AAAAA'

Chris Lim wrote:
Show quote

> Bulldog wrote:
> > Stored Procedure with a Single input parameter that allows the user to
> > supply a product code. I need a SELECT that retrieves the description
> > and price of the product from the PRODUCT table with a code matching
> > the one supplied in the parameter. I also need an exception section
> > that traps the NO_DATA_FOUND error and indicates to the user that the
> > product code is invalid. I then want to be able to print a line
> > displaying the product code, description, and price.
>
> Something like this would work, but how you handle the error and print
> the results depends on how you are going to be calling the stored
> procedure (I assume from a front-end client such as VB or a web page?)
>
> IF OBJECT_ID('get_product_info') is not NULL
>   DROP PROCEDURE get_product_info
> go
>
> CREATE PROCEDURE get_product_info @Prod_Code varchar(20) -- or whatever
> the correct type is
> AS
> BEGIN
>   IF NOT EXISTS( SELECT 1 FROM Product WHERE Prod_Code = @Prod_Code)
>   BEGIN
>       RAISERROR('No Data Found.', 16, 1)
>       RETURN 1
>   END
>
>   SELECT Prod_code, Desc, Price
>   FROM    Product
>   WHERE Prod_Code = @Prod_Code
>
> END
> go
>
>
> EXEC get_product_info @Prod_Code = 'XXXX'

1 Jul 2006 10:18 PM

Chris Lim

Bulldog wrote:

> When I run this procedure, it never ends. I don't get prompted for a
> number or anything. I enter a number and another SQL line comes up.

Are you running this via Query Analyser? Or ISQL? If ISQL then you need
to enter a GO to execute the command.

The first part of that code was to create the stored procedure. The
last line (EXEC....) is to execute it. You only have to create the
stored procedure once, and then execute it as many times as you like.

Chris

2 Jul 2006 12:07 AM

Bulldog

This is happening even when I try to create the procedure. I am simply
using ORACLE SQL Plus to perform this.

Chris Lim wrote:
Show quote

> Bulldog wrote:
> > When I run this procedure, it never ends. I don't get prompted for a
> > number or anything. I enter a number and another SQL line comes up.
>
> Are you running this via Query Analyser? Or ISQL? If ISQL then you need
> to enter a GO to execute the command.
>
> The first part of that code was to create the stored procedure. The
> last line (EXEC....) is to execute it. You only have to create the
> stored procedure once, and then execute it as many times as you like.
>
> Chris

2 Jul 2006 7:44 AM

Chris Lim

Bulldog wrote:

> This is happening even when I try to create the procedure. I am simply
> using ORACLE SQL Plus to perform this.

Hmmm, you might want to post to an Oracle newsgroup...