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 asselect sex from cscustomerwhere sex like '%M'unionselect orderdatefrom csorderwhere orderdatebetween '31-jul-11' and '30-nov-11';orselect sex from cscustomerwhere sex like '%M'unionselect orderdatefrom csorderwhere orderdatebetween '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. |
|
|
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. |
|
|
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 exampleWHERE 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. |
|
|
theboxmike
Starting Member
4 Posts |
Posted - 2012-12-20 : 06:25:17
|
Sex condition is ether 'M' or 'F' |
|
|
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.ORDERIDJOIN CSPRODUCT cp ON cp.PRODCODE = col.PRODCODE AND PRICE < 50AND col.CATCODE = (SELECT CATCODE FROM CSCATEGORY WHERE CATNAME = 'Printer')[/code]--Chandu |
|
|
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.ORDERIDJOIN CSPRODUCT cp ON cp.PRODCODE = col.PRODCODE AND PRICE < 50AND 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 csorderwhere 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? |
|
|
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 |
|
|
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.aspxAs 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' |
|
|
|