Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Programming problem

Author  Topic 

theboxmike
Starting Member

4 Posts

Posted - 2012-12-20 : 05:16:48
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

354 Posts

Posted - 2012-12-20 : 06:03:29
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 - 2012-12-20 : 06:09:35
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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-20 : 06:20:29
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 - 2012-12-20 : 06:25:17
Sex condition is ether 'M' or 'F'
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-20 : 06:42:31
[code]
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')
[/code]

--
Chandu
Go to Top of Page

theboxmike
Starting Member

4 Posts

Posted - 2012-12-22 : 18:41:18
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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-24 : 00:09:11
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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-12-26 : 12:17:29
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
   

- Advertisement -