Problem with some SQL queries

[Vince]

I have several questions about queries I make into my database. I will
explain you. Could you please help me?

1)In my people table, persons have a title (Mr, Miss, or whatever).

" select distinct person, title from people where title is null or
title is not null "

give:

PERSON TITLE
0000029 Miss
0000465 Mr
0000469 <-- null
0000624 <-- null
0000900 Miss

But when I calculate the number of titles (a lot of possibles types),
the title = null is not took into account. For that, I use " select
count(distinct title) from people where title is null or title is not
null ". How to make it successful?

2)Parameter between ' characters

My query is:

select distinct people.surname, people.forenames, sessions.stage,
sessions.course,
(select distinct title from shared.courses
where sessions.course=shared.courses.course) as titleofcourse
from people, sessions
where people.person=sessions.student and sessions.status='C'

I get:

SURNAME FORENAMES STAGE COURSE TITLEOFCOURSE
Aggett Stephen Peter James 2 V700 Philosophy <--
'Philosophy'

How to put the title of course (getting with a select) between '
characters?
I try without succes to use case or decode(). Have you got an idea?

3)A query on a same table

My people table is as following:

PERSON SURNAME FORENAMES TITLE KNOWNAS USERNAME EMAIL

I would like to check if there is any duplicates, ie two people with
the same surname and the same forenames.

I don't want to use a view containing the people table. I think it is
better to make it in a single query. Any idea please?

Something like " select * from people as p, people as pp where
p.surname=pp.surname " but which works?

Maybe it is something as:
"select p.surname, p.forenames
from people p, (select pp.surname, pp.forenames from people pp)
where p.surname = pp.surname and p.forenames = pp.forenames "

[Hans Forbrich]

Vince wrote:

1)In my people table, persons have a title (Mr, Miss, or whatever).

" select distinct person, title from people where title is null or
title is not null "

give:

PERSON TITLE
0000029 Miss
0000465 Mr
0000469 <-- null
0000624 <-- null
0000900 Miss

But when I calculate the number of titles (a lot of possibles types),
the title = null is not took into account. For that, I use " select
count(distinct title) from people where title is null or title is not
null ". How to make it successful?
Nulls have no defined value - not even 'nothing'. Therefore they can
not be counted. To work around this, assign a value to replace the null
using a function like NVL (although you really want to look it up to
make sure you are using the right function) OR count a non-null column
or pseudo-column such as rowid.

2)Parameter between ' characters

My query is:

select distinct people.surname, people.forenames, sessions.stage,
sessions.course,
(select distinct title from shared.courses
where sessions.course=shared.courses.course) as titleofcourse
from people, sessions
where people.person=sessions.student and sessions.status='C'

I get:

SURNAME FORENAMES STAGE COURSE TITLEOFCOURSE
Aggett Stephen Peter James 2 V700 Philosophy <--
'Philosophy'

How to put the title of course (getting with a select) between '
characters?
I try without succes to use case or decode(). Have you got an idea?

Lean about the CONCAT operator.
3)A query on a same table

My people table is as following:

PERSON SURNAME FORENAMES TITLE KNOWNAS USERNAME EMAIL

I would like to check if there is any duplicates, ie two people with
the same surname and the same forenames.

I don't want to use a view containing the people table. I think it is
better to make it in a single query. Any idea please?

Something like " select * from people as p, people as pp where
p.surname=pp.surname " but which works?

Maybe it is something as:
"select p.surname, p.forenames
from people p, (select pp.surname, pp.forenames from people pp)
where p.surname = pp.surname and p.forenames = pp.forenames "

Think about using a grouping-count and eliminate the non-duplicate rows
using a 'having' clause.

HTH
/Hans