CREATE TABLE `Lecturers` (
`id` INT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`fio` VARCHAR( 100 ) NOT NULL ,
`rank` VARCHAR( 40 ) NOT NULL ,
`post` VARCHAR( 40 ) NOT NULL ,
PRIMARY KEY ( `id` ) 
);

CREATE TABLE `disciplines` (
  `id` int(5) unsigned NOT NULL auto_increment,
  `title` varchar(70) NOT NULL,
  `semestr` int(2) NOT NULL,
  `form` enum('exam','setoff','other') NOT NULL,
  PRIMARY KEY  (`id`)
);

CREATE TABLE `schedule` (
  `id_lector` int(5) UNSIGNED NOT NULL,
  `id_discipline` int(5) UNSIGNED NOT NULL,
  `weekday` enum('Понедельник','Вторник','Среда','Четверг','Пятница','Суббота','Воскресенье') NOT NULL,
  PRIMARY KEY  (`id_lector`,`id_discipline`),
  FOREIGN KEY (`id_lector`) REFERENCES `lecturers`(`id`),
  FOREIGN KEY (`id_discipline`) REFERENCES `disciplines`(`id`)
);

1) SELECT * FROM `lecturers` WHERE `rank`='Кандидат Наук'
----------------------
2)SELECT DISTINCT `fio`
FROM `schedule` as `sc`
JOIN `disciplines` as `d` on `sc`.`id_discipline`=`d`.`id`
JOIN `lecturers` as `l` on `l`.`id`=`sc`.`id_lector`
WHERE `d`.`form`='exam' AND (`id_lector`,`semestr`) <> ALL(
SELECT `id_lector`,`semestr`
FROM `schedule` as `sc`
JOIN `disciplines` as `d` on `sc`.`id_discipline`=`d`.`id`
WHERE `d`.`form`<>'exam'
)
----------------------
3) SELECT DISTINCT `fio`
FROM `schedule` as `sc`
JOIN `disciplines` as `d` on `sc`.`id_discipline`=`d`.`id`
JOIN `lecturers` as `l` on `l`.`id`=`sc`.`id_lector`
WHERE `d`.`form`='setoff'
----------------------
4) SELECT * FROM lecturers`
----------------------
5) SELECT DISTINCT `l`.`fio`
FROM `lecturers` as `l` 
JOIN `schedule` as `sc` on `l`.`id`=`sc`.`id_lector` 
JOIN `disciplines` as `d` on `sc`.`id_discipline`=`d`.`id`
WHERE `l`.`id` <> ALL(
SELECT `l`.`id`
FROM `lecturers` as `l` 
JOIN `schedule` as `sc` on `l`.`id`=`sc`.`id_lector` 
JOIN `disciplines` as `d` on `sc`.`id_discipline`=`d`.`id`
WHERE `d`.`semestr` NOT IN (3,4))
----------------------
6) SELECT `fio`
FROM `schedule` as `sc`
JOIN `disciplines` as `d` on `sc`.`id_discipline`=`d`.`id`
JOIN `lecturers` as `l` on `l`.`id`=`sc`.`id_lector`
WHERE `l`.`rank` = 'Кандидат Наук' AND (`d`.`semestr`=7 OR `d`.`semestr`=8)
----------------------
7) SELECT `title`
FROM `disciplines`
GROUP BY `title`
HAVING count(DISTINCT `semestr`)>1
----------------------
8) SELECT `l`.`fio`
FROM `schedule` as `sc`
JOIN `disciplines` as `d` on `sc`.`id_discipline`=`d`.`id`
JOIN `lecturers` as `l` on `l`.`id`=`sc`.`id_lector`
WHERE `l`.`rank`='Доцент'
GROUP BY `l`.`id`
HAVING count(DISTINCT `d`.`title`)=3
----------------------
9) SELECT `l`.`fio`
FROM `schedule` as `sc`
JOIN `disciplines` as `d` on `sc`.`id_discipline`=`d`.`id`
JOIN `lecturers` as `l` on `l`.`id`=`sc`.`id_lector`
WHERE `l`.`rank`='Доктор Наук' AND `d`.`form`='exam' AND (`d`.`semestr`=9 OR `d`.`semestr`=10)