SQL question - SQL Server Forums

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 question

Author

Topic

rodi
Starting Member

3 Posts

Posted - 2012-01-24 : 14:19:51

Hello i study now database and i have a assignment : i have a table:
CREATE TABLE employees(
ID INTEGER NOT NULL,
FirstName VARCHAR (25) NOT NULL,
SecondName VARCHAR (25) NOT NULL,
CNMU VARCHAR (13) NOT NULL,
Adres VARCHAR (25) NOT NULL,
Home VARCHAR (25) NOT NULL,
DateOfBirth DATE,
PlaceOfBirght VARCHAR (50),
NrTelefon VARCHAR (20),
TrainingSchool VARCHAR (20),
CivilStatus VARCHAR (20),
Sex VARCHAR (10),
SalarMax NUMERIC (10),
SalarMediu NUMERIC (10));

Write the SQL query that will presents CivilStatus at least 4 employees.

i right:

select* from test. employees where civilstatus='married' or civilstatus ='notmarried' or civilstatus ='divorced' or civilstatus ='widow' group by civilstatus;

but the answere was:
Unfortunately exercise is incorrect. Exercise can be achieved by grouping and aggregate function.

can you halp me how it should be????????
i tray : select id,firstname,civilstatus from test.employees group by civilstatus ;

and give me at lists 4 employees byt how can i coose to show me al the employees which are with civillstatus married??????(i have in my table 5 of them).i thinc it should be enought.
thank you very much.

biswajitdas
Starting Member

44 Posts

Posted - 2012-01-24 : 14:59:19

Syntax:
------------------------
SELECT fieldlist
FROM table
[ WHERE criteria ]
GROUP BY grouping_expression
HAVING criteria fieldlist

------------------------

SELECT * FROM EMPLOYEE, COUNT(CIVILSTATUS) AS NUMBER_STATUS FROM EMPLOYEE WHERE CIVILSTATUS IN ('Married','unmarried')
GROUP BY CIVILSTATUS HAVING COUNT(CIVILSTATUS) > 1;

Sr Sql server DBA/Artitech

whatamouth
Starting Member

16 Posts

Posted - 2012-01-24 : 15:01:05

Hi Rodi. Can you post your desired output?
Or what the resulting table looks like?

Neil Matias
neilmatias@yahoo.com

rodi
Starting Member

3 Posts

Posted - 2012-01-24 : 15:48:32

ID FIRST NAME LAST NAME CIVILSTATUS
2 Pitigoi Cristian married
5 Dumitru Maria married
1 Vasile George married
6 Valerian Andrea married

like this

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2012-01-24 : 18:26:03

Maybe something like this?


SELECT
    e.emp_id,
    e.FirstName,
    e.SecondName,
    e.CivilStatus
FROM employees e
WHERE e.CivilStatus IN (SELECT CivilStatus FROM employees GROUP BY CivilStatus HAVING count(*) >= 4)

rodi
Starting Member

3 Posts

Posted - 2012-01-25 : 02:23:10

thank you very very very much

it halp.the note is yours

Aleph_0
Yak Posting Veteran

79 Posts

Posted - 2012-01-25 : 13:29:45

Oops, I just realized that was homework. What's the policy here on that? Ignore? Give hints?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-01-25 : 13:35:36

quote:
Originally posted by Aleph_0

Oops, I just realized that was homework. What's the policy here on that? Ignore? Give hints?

It's up to you, generally I tell the OP that SQLTeam doesn't answer homework, exam or certification questions.

If you want to help someone, go ahead, that's what we're here for.

Kristen
Test

22859 Posts

Posted - 2012-01-25 : 13:41:01

quote:
Originally posted by Aleph_0

Oops, I just realized that was homework. What's the policy here on that? Ignore? Give hints?

Generally if the O/P just posts the question we tell them to get lost!

If the O/P posts what they have attempted to do we (as in this case) then we usually provide help. Up to you how much help you provide

, quite often it is along the lines of "You need to look into XXX ..." but someone who has posted their workings may be likely to UNDERSTAND a complete worked example at that point ... and if they don't then they are in the same camp as the first lot - they will complete their assignment but not be able to actually do the work so will fail an Exam, or if they get a job based on some coursework assessment they won't be able to keep that job!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources