База данных / sampledb / ИС_примеры селектов
.doc/*простой синтаксис оператора select*/
select * from students
/*вертикальный срез*/
select rn,lname,fname,mname,datar from students
/*горизонтальный срез предложение where*/
select * from students where grupa='321'
/*вертикально-горизонтальный срез*/
select rn,lname,fname,mname,datar from students where grupa='321'
/*использование псевдонимов таблиц*/
select s.rn,s.lname,s.fname,s.mname,s.datar from students s where s.grupa='321'
/*использование псевдонимов столбцов*/
select rn as "Номер зачетной книжки",
lname as "Фамилия",
fname as "Имя",
mname as "Отчество",
datar as "Дата рождения"
from students where grupa='321'
/*BETWEEN - находится между*/
select rn,lname,fname,mname,datar from students
where datar between '01.01.1983' and '31.12.1983'
/*IN - находится в*/
select rn,lname,fname,mname,grupa from students where grupa in ('321','322')
/*STARTING - начинается с*/
select rn,lname,fname,mname,grupa from students where lname starting 'Ар'
/*CONTAINING - содержит*/
select rn,lname,fname,mname,grupa from students where lname containing 'кола'
/*LIKE - шаблон строки Значение _ и %*/
select rn,lname,fname,mname,grupa from students where lname like 'Ив%ва'
/*функция UPPER*/
select rn,upper(lname) as lname,upper(fname) as fname,upper(mname) as mname,grupa from students
/*сочетание UPPER с предложениями вхождения*/
select rn,lname,fname,mname,grupa from students
where upper(lname) containing 'КОЛ'
/*функция CAST*/
select rn,lname,fname,mname,cast(datar as char(10)) from students
/*сортировка по фамилии*/
select rn,lname,fname,mname,datar from students where grupa='321' order by lname
/*сложная сортировка по нескольким полям фамилия, имя, отчество*/
select rn,lname,fname,mname,datar from students order by lname,fname,mname
/*использование при сортировке номеров полей*/
select rn,lname,fname,mname,datar from students order by 2,3,4
/*сортировка в обратном порядке DESC*/
select rn,lname,fname,mname,datar from students order by datar desc
/*внутреннее соединение таблиц - стандарт SQL-92*/
select
s.rn,
s.lname,
s.fname,
s.mname,
f.dforma,
sp.dspec
from students s,
forma f,
spec sp
where s.forma=f.forma and s.spec=sp.spec
/*устранение повторяющихся значений DISTINCT*/
select distinct s.grupa from students s where s.fac=3
/*вычисляемые поля*/
select rn,lname||' '||fname||' '||mname as fio,datar from students order by 2
/*агрегатные функции - количество студентов*/
select count(s.rn) from students s
/*агрегатные функции - количество студентов в группе*/
select count(s.rn) from students s where fac=3 and state=1
/*агрегатные функции - минимальная дата рождения*/
select min(s.datar) from students s
/*агрегатные функции - максимальная дата*/
select max(s.datar) from students s
/*агрегатные функции - средний возраст студентов*/
select avg(cast(current_date-s.datar as numeric(10,2))/365) from students s
/*агрегатные функции - общее количество прожитых дней*/
select sum(current_date-s.datar) from students s
where s.datar between '01.01.1970' and '01.01.1990'
/*группировка записей - количество студентов в каждой группе*/
select s.grupa,count(s.rn) from students s where s.state=1 group by s.grupa
/*наложение ограничений на группировку записей - список групп в которых меньше 15 человек*/
select s.grupa,count(s.rn) from students s
where s.state=1
group by s.grupa
having count(s.rn)<15
/*использование подзапросов найти самого старого студента на матфаке*/
select rn,lname,fname,mname,grupa,datar from students
where datar=(select min(datar) from students where fac=3 and state=1)
/*использование подзапросов Предложение exists
находим студентов имеющих одну и более задолженностей*/
select rn,lname,fname,mname,grupa from students s
where exists (select a.rn from
academybook a where a.rn=s.rn and ((a.ocenka='2') or (a.ocenka='-') or (a.ocenka is null)))
and fac=3 and state=1
/*использование подзапросов Предложение singular
находим студентов имеющих только одну задолженность*/
select rn,lname,fname,mname,grupa from students s
where singular (select a.rn from
academybook a where a.rn=s.rn and ((a.ocenka='2') or (a.ocenka='-') or (a.ocenka is null)))
and fac=3 and state=1
/*использование подзапросов Предложение ALL
находим студентов имеющих только отличные оценки*/
select rn,lname,fname,mname,grupa from students s
where '5' = all (select a.ocenka from
academybook a where a.rn=s.rn and a.typeotch=1)
and fac=3 and state=1 and exists (select a1.rn from academybook a1 where a1.rn=s.rn)
/*использование подзапросов Предложение ALL
находим студентов-хорошистов*/
select rn,lname,fname,mname,grupa from students s
where '3' < all (select a.ocenka from
academybook a where a.rn=s.rn and a.typeotch=1)
and fac=3 and state=1 and exists (select a1.rn from academybook a1 where a1.rn=s.rn)
/*использование подзапросов Предложение SOME
находим студентов имеющих хотя бы одну тройку*/
select rn,lname,fname,mname,grupa from students s
where '3' = some (select a.ocenka from
academybook a where a.rn=s.rn and a.typeotch=1)
and fac=3 and state=1
/*внутренние соединения INNER JOIN*/
select
s.rn,
s.lname,
s.fname,
s.mname,
scl.dschool
from students s
inner join school scl on scl.school=s.school
/*левое внешнее соединение LEFT JOIN*/
select
s.rn,
s.lname,
s.fname,
s.mname,
scl.dschool
from students s
left join school scl on scl.school=s.school
/*внутренние соединения INNER JOIN средний балл зачетной книжки*/
select
s.rn, s.lname, s.fname, s.mname, s.grupa,
avg(cast(a.ocenka as numeric(2,1))) as avg_ball
from students s
join academybook a on a.rn=s.rn and ((a.typeotch=1) or (a.typeotch=2))
where s.fac=3 and s.state=1
group by
s.rn,s.lname,s.fname,s.mname,s.grupa
/*внутренние соединения INNER JOIN средний балл зачетной книжки пятикурсников
с ограничением*/
select
s.rn, s.lname, s.fname, s.mname, s.grupa,
avg(cast(a.ocenka as numeric(2,1))) as avg_ball
from students s
join academybook a on a.rn=s.rn and ((a.typeotch=1) or (a.typeotch=2))
where s.fac=3 and s.state=1 and s.kurs=5
group by
s.rn,s.lname,s.fname,s.mname,s.grupa
having avg(cast(a.ocenka as numeric(2,1)))>4.5
/*внутренние соединения INNER JOIN
Студенты средний балл которых выше чем средний балл группы*/
select
s.rn, s.lname, s.fname, s.mname, s.grupa,
avg(cast(a.ocenka as numeric(2,1))) as avg_ball
from students s
join academybook a on a.rn=s.rn and ((a.typeotch=1) or (a.typeotch=2))
where s.fac=3 and s.state=1
group by
s.rn,s.lname,s.fname,s.mname,s.grupa
having avg(cast(a.ocenka as numeric(2,1)))>
(select avg(cast(a1.ocenka as numeric(2,1)))
from academybook a1
join students s1 on s1.rn=a1.rn
where s1.grupa=s.grupa and ((a1.typeotch=1) or (a1.typeotch=2)))