SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Programming problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

theboxmike
Starting Member

4 Posts

Posted - 12/20/2012 :  05:16:48  Show Profile  Reply with Quote
Hey guys just a homework problem i need some help with.
So i have created the follow tables in SQL and following columns.

CSPRODUCT contains (PRODCODE, PRODNAME, CATCODE, PRICE, PARTOF_PRODCODE)
CSCATEGORY contains (CATECODE, CATNAME)
CSORDER contains (ORDERID, ORDERDATE, CUSTID, PAID)
CSORDERLINE contains (ORDERID, PRODCODE, QUANTITY)
CSCUSTOMER contains (CUSTID, SURNAME, GIVEN, DOB, SEX, HOME_PHONE, WORK_PHONE, FAX, ADDR1, ADDR2, ADD3, POSTCODE),

How do i?
find the orders made between July and December 2011, by male customers less than 20 years old, for products that cost less than 50 dollars in the Printer category?

I have tried using a union such as

select sex
from cscustomer
where sex like '%M'
union
select orderdate
from csorder
where orderdate
between '31-jul-11' and '30-nov-11';

or

select sex
from cscustomer
where sex like '%M'
union
select orderdate
from csorder
where orderdate
between '31-jul-11' and '30-nov-11';

but i really don't know where to go from here? i am i even on the right track? thanks guys.

vijays3
Constraint Violating Yak Guru

India
346 Posts

Posted - 12/20/2012 :  06:03:29  Show Profile  Reply with Quote
Please provide sample data and expected data.
Also let us know what result are u geiing by your query.

Vijay is here to learn something from you guys.
Go to Top of Page

theboxmike
Starting Member

4 Posts

Posted - 12/20/2012 :  06:09:35  Show Profile  Reply with Quote
quote:
Originally posted by vijays3

Please provide sample data and expected data.
Also let us know what result are u geiing by your query.

Vijay is here to learn something from you guys.



Sample data? I don't understand i'm just trying to programme in some code to solve my problem.

sorry i'm just confused.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/20/2012 :  06:20:29  Show Profile  Reply with Quote
Instead of UNION, you should use the various conditions with an AND condition in the WHERE clause - for example
WHERE
    orderdate between '31-jul-11' and '30-nov-11'
    AND SEX = 'M'
Also, what are the possible values in the sex column? Since there can be only two values, just use the equals condition rather than LIKE condition.
Go to Top of Page

theboxmike
Starting Member

4 Posts

Posted - 12/20/2012 :  06:25:17  Show Profile  Reply with Quote
Sex condition is ether 'M' or 'F'
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 12/20/2012 :  06:42:31  Show Profile  Reply with Quote

SELECT o.*
FROM (SELECT * FROM CSORDER WHERE MONTH(ORDERDATE) IN (7, 12) AND YEAR(ORDERDATE) = 2011) o 
JOIN CSCustomer cc ON o.custid = cc.custid AND sex = 'm' AND DATEDIFF(yy, dob, getdate()) < 20 
JOIN CSORDERLINE col ON col.ORDERID= o.ORDERID
JOIN CSPRODUCT cp ON cp.PRODCODE = col.PRODCODE AND PRICE < 50
AND col.CATCODE = (SELECT CATCODE FROM CSCATEGORY WHERE CATNAME = 'Printer')


--
Chandu
Go to Top of Page

theboxmike
Starting Member

4 Posts

Posted - 12/22/2012 :  18:41:18  Show Profile  Reply with Quote
quote:
Originally posted by bandi


SELECT o.*
FROM (SELECT * FROM CSORDER WHERE MONTH(ORDERDATE) IN (7, 12) AND YEAR(ORDERDATE) = 2011) o 
JOIN CSCustomer cc ON o.custid = cc.custid AND sex = 'm' AND DATEDIFF(yy, dob, getdate()) < 20 
JOIN CSORDERLINE col ON col.ORDERID= o.ORDERID
JOIN CSPRODUCT cp ON cp.PRODCODE = col.PRODCODE AND PRICE < 50
AND col.CATCODE = (SELECT CATCODE FROM CSCATEGORY WHERE CATNAME = 'Printer')


--
Chandu




Hey thanks for the reply my syntax is a little bit different i think. This is what i have so far.

select *
from csorder
where orderdate in 'JUL, DEC' and orderdate = '2011';

I changed the Months to characters because the months aren't set in numbers but charaters eg. DEC, FEB. But now i'm getting "A non numeric character was found where a numeric was expected" Should i change it into a Like wildcard? Or won't that work?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 12/24/2012 :  00:09:11  Show Profile  Reply with Quote
OrderDate is DATE type column ...right?
If yes, follow this code:
For orders made between July and December 2011,
WHERE (DATEPART(MONTH,orderdate) BETWEEN 7 AND 12)
AND YEAR(orderdate) = 2011



--
Chandu
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/26/2012 :  12:17:29  Show Profile  Reply with Quote
quote:
Originally posted by theboxmike


Sample data? I don't understand i'm just trying to programme in some code to solve my problem.

sorry i'm just confused.

Hi, Below are two links that will explain in greater detail what is being asked for. Generally, it is good etiquette to provide DDL (create tables and such), DML (insert statements) and expected output. Since this is a homework problem, we tend to try and help you get to a solution rather than provide it to you on a silver platter.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


As far as predicates are concerned, it's usually preferable to NOT apply any functions to the columns in your tables. So, for the date range predicate it would be better to do something like:
WHERE
    orderdate >= '20110801' 
    AND orderdate < '20121201'


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000