1) Перечислите виды джойнов?

2) В таблице "Города" 10 записей, в таблице "Люди" 100 записей.
Таблица "Люди" ссылается на "Города" и город известен для каждого человека.
Сколько записей будет в "города лефт джойн люди по правильному условию"

1) Таблицы t1 и t2 заполнены следующими данными (тут нарисованы таблицы данных 1..5, 1..10):

a) Сколько строк будет выбрано в результате следующих запросов:

select * from t1, t2
select distinct * from t1, t2

2) Есть таблица городов и таблица клиентов. В таблице клиентов есть поле, ссылающееся на таблицу городов. Эта ссылка необязательная, т.е. не для каждого клиента известен его город. Также, не каждый город имеет ссылки из таблицы клиентов, т.е. не в каждом городе есть клиенты. Известно, что в базе на данный момент 100 городов, из них 20 городов не имеют клиентов. Также, в базе 1000 клиентов, и для 500 из них город неизвестен. Сколько строк вернут следущие запросы:

select * from City inner join Client on City.id = Client.city_id
select * from City left outer join Client on City.id = Client.city_id
select * from City right outer join Client on City.id = Client.city_id
select * from City full outer join Client on City.id = Client.city_id
select * from City, Client where City.id = Client.city_id

U>TableLink genderLink = new TableLink(dbConnection, "Select code From gender", 
U>  new FieldBlank[] { GenderType.Code }, new IndexBlank[] { GenderType.ByCode });
U>TableLink ethnicLink = new TableLink(dbConnection, "Select code From ethnic",
U>  new FieldBlank[] { EthnicType.Code }, new IndexBlank[] { EthnicType.ByCode });
U>TableLink peopleLink = new TableLink(dbConnection, 
U>  "Select gender, ethnic, count(*) From people group by gender, ethnic",
U>  new FieldBlank[] { PeopleType.Gender, PeopleType.Ethnic, TempType.Count },
U>  new IndexBlank[] { PeopleType.ByGenderAndEthnic });

U>IEnumerable<RowLink> allGenders = genderLink.AllRows.Concat(null);
U>IEnumerable<RowLink> allEthnics = ethnicLink.AllRows.Concat(null);

U>foreach (RowLink gender in allGenders)
U>{
U>  foreach (RowLink ethnic in allEthnics)
U>  {
U>    int? genderCode = gender != null ? gender.Get(GenderType.Code) : null;
U>    int? ethnicCode = ethnic != null ? ethnic.Get(EthnicType.Code) : null;
U>    RowLink people = people.FindRow(PeopleType.ByGenderAndEthnic, genderCode, ethnicCode);
U>    int count = people != null ? people.Get(TempType.Count) : 0;
U>  }
U>}
U>

select
 breakdown.*
 ,count(*)
from
  (
    select gender.code gender, ethnic.code ethnic from gender,ethnic
  ) breakdown
  left join people on people.gender = breakdown.gender and people.ethnic = breakdown.ethnic
group by
  breakdown.gender
  ,breakdown.ethnic

A>select
A> breakdown.*
A> ,count(*)
A>from
A>  (
A>    select gender.code gender, ethnic.code ethnic from gender,ethnic
A>  ) breakdown
A>  left join people on people.gender = breakdown.gender and people.ethnic = breakdown.ethnic
A>group by
A>  breakdown.gender
A>  ,breakdown.ethnic
A>

select g.code as gender, e.code as ethnic, count(*)
from people p
right join gender g on g.code=p.gender
right join ethnic e on e.code=p.ethnic
group by g.code, e.code

A>ну, например, есть люди. У них 2 признака — пол и национальность. Построить отчёт с группировкой по полу и национальности, включая нулевые значения.

select g.code as gender, e.code as ethnic, count(p.id)
from 
(
 select 1 id, 'M' gender, 'W' ethnic 
)p
right join (select 'M' code union select 'F' code) g on g.code=p.gender
right join (select 'W' code union select 'B' code) e on e.code=p.ethnic
group by g.code, e.code

--------------------------------
gender    ethnic    (No column name)
NULL    B    0
M    W    1
--------------------------------

select
 breakdown.*
 ,count(people.id)
from
  (
    select gender.code gender, ethnic.code ethnic from (select 'M' code union select 'F' code) gender, (select 'W' code union select 'B' code) ethnic
  ) breakdown
  left join (select 1 id, 'M' gender, 'W' ethnic) people on people.gender = breakdown.gender and people.ethnic = breakdown.ethnic
group by
  breakdown.gender
  ,breakdown.ethnic

--------------------------------
gender    ethnic    (No column name)
F    B    0
M    B    0
F    W    0
M    W    1
--------------------------------

select
 breakdown.*
 ,count(*)
from
  (
    select gender.code gender, ethnic.code ethnic from gender,ethnic
  ) breakdown
  left join people on people.gender = breakdown.gender and people.ethnic = breakdown.ethnic
group by
  breakdown.gender
  ,breakdown.ethnic

select gender, ethnic, count(*) from people group by gender, ethnic

TableLink genderLink = new TableLink(dbConnection, "Select code From gender", 
  new FieldBlank[] { GenderType.Code }, new IndexBlank[] { GenderType.ByCode });
TableLink ethnicLink = new TableLink(dbConnection, "Select code From ethnic",
  new FieldBlank[] { EthnicType.Code }, new IndexBlank[] { EthnicType.ByCode });
TableLink peopleLink = new TableLink(dbConnection, 
  "Select gender, ethnic, count(*) From people group by gender, ethnic",
  new FieldBlank[] { PeopleType.Gender, PeopleType.Ethnic, TempType.Count },
  new IndexBlank[] { PeopleType.ByGenderAndEthnic });

IEnumerable<RowLink> allGenders = genderLink.AllRows.Concat(null);
IEnumerable<RowLink> allEthnics = ethnicLink.AllRows.Concat(null);

foreach (RowLink gender in allGenders)
{
  foreach (RowLink ethnic in allEthnics)
  {
    int? genderCode = gender != null ? gender.Get(GenderType.Code) : null;
    int? ethnicCode = ethnic != null ? ethnic.Get(EthnicType.Code) : null;
    RowLink people = people.FindRow(PeopleType.ByGenderAndEthnic, genderCode, ethnicCode);
    int count = people != null ? people.Get(TempType.Count) : 0;
  }
}

(Select * From people Where ...) selectPeople;
(Select * From people Where people_id in (select spouseId From selectPeople)) spousePeople;

MultiIndexBlank byGenderAndEthnic = new MultiIndexBlank(PeopleType.Gender, PeopleType.Ethnic);

U>(Select * From people Where ...) selectPeople;
U>(Select * From people Where people_id in (select spouseId From selectPeople)) spousePeople;
U>

select people.Name, spouse.Name
from 
 people 
 left join people spouse on spouse.id = people.spouse_id