January 26, 2013 at 9:11 am
Hello Guys
I have table Named Information with columns Gender,car,Age,Employment Status
below are possible values above columns
In Gender : - Male , Female
Car :- Maruti, Tata, Skoda
Employment status :- full time and part time
Age :- 10,20,30,40,50,60,70,80
I have 2500 rows in table
SELECT Count(*) FROM Information WHERE Gender = 'Male' ---- 1000
SELECT Count(*) FROM Information WHERE Gender = 'Female' ---- 1500
SELECT Count(*) FROM Information WHERE Car = 'Maruti' ----- 500
SELECT Count(*) FROM Information WHERE Car = 'Tata' ----- 1000
SELECT Count(*) FROM Information WHERE Car = 'Skoda' ------ 1000
SELECT Count(*) FROM Information WHERE Car = 'Full time' ------- 1500
SELECT Count(*) FROM Information WHERE Car = 'Part time' -------- 1000
SELECT Count(*) FROM Information WHERE Age >= 50 -------- 1200
SELECT Count(*) FROM Information WHERE Age < 50 -------- 1300
I need only 10 rows in result
out of which 5 are male and 5 are female and
amongst those 3 must have Maruti , 4 must tata and 3 must have skoda
amongst those 8 are full time working and 2 are Part time working
amongst those 4 persons of age less than 50 and 6 persons of age greater than or equal 50
Below is my expected out put
GENDER CAR EMPSTATUS AGE
Male Maruti Full Time 10
Male Maruti Full Time 20
Male Maruti Full Time 20
Male Tata Full Time 30
Male Tata Full Time 50
Female Tata Full Time 60
Female Tata Full Time 60
Female skoda Full Time 70
Female skoda Part Time 70
Female skoda Part Time 80
looking forward for your kind help
feel free to ask any further details or clarification
January 26, 2013 at 12:29 pm
Thank you for trying to provide a well-formed question. It looks like homework, which is fine, I am happy to help but I need a few things first:
1. DDL to create your table, i.e. CREATE TABLE statement
2. DML to create some test data, i.e. INSERT statements
3. the expected results based on the test data
4. most importantly, the queries you have tried so far to resolve the issue yourself
edit: fix numbering
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 26, 2013 at 5:54 pm
vikrantkale18 (1/26/2013)
Hello GuysI have table Named Information with columns Gender,car,Age,Employment Status
below are possible values above columns
In Gender : - Male , Female
Car :- Maruti, Tata, Skoda
Employment status :- full time and part time
Age :- 10,20,30,40,50,60,70,80
I have 2500 rows in table
SELECT Count(*) FROM Information WHERE Gender = 'Male' ---- 1000
SELECT Count(*) FROM Information WHERE Gender = 'Female' ---- 1500
SELECT Count(*) FROM Information WHERE Car = 'Maruti' ----- 500
SELECT Count(*) FROM Information WHERE Car = 'Tata' ----- 1000
SELECT Count(*) FROM Information WHERE Car = 'Skoda' ------ 1000
SELECT Count(*) FROM Information WHERE Car = 'Full time' ------- 1500
SELECT Count(*) FROM Information WHERE Car = 'Part time' -------- 1000
SELECT Count(*) FROM Information WHERE Age >= 50 -------- 1200
SELECT Count(*) FROM Information WHERE Age < 50 -------- 1300
I need only 10 rows in result
out of which 5 are male and 5 are female and
amongst those 3 must have Maruti , 4 must tata and 3 must have skoda
amongst those 8 are full time working and 2 are Part time working
amongst those 4 persons of age less than 50 and 6 persons of age greater than or equal 50
Below is my expected out put
GENDER CAR EMPSTATUS AGE
Male Maruti Full Time 10
Male Maruti Full Time 20
Male Maruti Full Time 20
Male Tata Full Time 30
Male Tata Full Time 50
Female Tata Full Time 60
Female Tata Full Time 60
Female skoda Full Time 70
Female skoda Part Time 70
Female skoda Part Time 80
looking forward for your kind help
feel free to ask any further details or clarification
Actually, this looks like a fun problem. I've never been able to hammer these types of things out very easily and really look forward to someone taking a crack at it.
Take a look at the first link in my signature line below. If you could post the tables like that and attach a file of tab delimited data or data in the SELECT UNION ALL method, I'm sure some folks would take a crack at it.
[font="Arial Black"]{EDIT} It just dawned on me that this is likely a bit too complex for homework. It's probably a contest somewhere. [/font] Still, it's damned interesting.
--Jeff Moden
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply