DataBase_labs(2-6)

--lr2
create table Subject_1
(Subj_id integer primary key,
Subj_name varchar(20),
Hour smallint,
)
create table Lecturer_1
(Lecturer_id integer primary key,
Surname varchar(20),
Name varchar(20),
Middle_name varchar(20),
City varchar(20),
)
create table Semester_plan_1
(Semester_id integer primary key,
Lecturer_id integer FOREIGN KEY (Lecturer_id) REFERENCES Lecturer_1 (Lecturer_id),
Subj_id integer foreign key references Subject_1(Subj_id),
Semester tinyint,
)
create table University_1
(Univ_id integer primary key,
Univ_name varchar(20),
Rating tinyint,
City varchar(20),
)
create table Student_1
(Student_id integer primary key,
Name varchar(20),
Surname varchar(20),
Middle_name varchar(20),
City varchar(20),
Birthday date,
Univ_id integer foreign key references University_1(Univ_id) ON UPDATE CASCADE ON DELETE set null,
)
create table Exam_marks_1
(Exam_id integer primary key,
Student_id integer foreign key(Student_id) references Student_1(Student_id)ON UPDATE CASCADE ON DELETE CASCADE,
Subj_id integer foreign key(Subj_id) references Subject_1(Subj_id)ON UPDATE CASCADE ON DELETE CASCADE,
Mark decimal(3,2),
Exam_date date,
)

create table University_2
(Univ_id integer not null,
Univ_name varchar(20),
Rating tinyint,
City varchar(20),
)
create table Student_2
(Student_id integer not null,
Name varchar(20),
Surname varchar(20),
Middle_name varchar(20),
City varchar(20),
Birthday date,
Univ_id integer,
)
alter table University_2
add primary key(Univ_id);

alter table Student_2
add primary key(Student_id);
alter table Student_2
add constraint Univ_id foreign key(Univ_id) references University_1(Univ_id) ON UPDATE CASCADE ON DELETE CASCADE;


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

select Subj_name, Hour
from Subject_1

select Subj_id
from Exam_marks_1
where Subj_id = 5

select Addres, Surname, Name
from Student_1

select distinct Mark
from Exam_marks_1

select Kurs, Surname
from Student_1
where Kurs >= 3

Select Kurs, Surname, Name, Stipendia
from Student_1
where Stipendia > 2500

select Hour, Subj_name
from Subject_1
where Hour > 26

select Univ_name, Rating
from University_1
where Rating > 60

Select Kurs, Surname, Name, Stipendia, City
from Student_1
where Stipendia >= 2500 and City = 'Уфа'

select*
from Student_1
where (Stipendia < 100 or not(Birthday >= '10/03/1995' and Student_id > 5))

select*
from Student_1
where not ((Birthday >= '10/03/1995' or Stipendia > 100) and Student_id >= 3)





--lr 3
SELECT Subj_Id, Exam_Date
FROM Exam_mark
WHERE Exam_Date BETWEEN '2019-01-10' AND '2019-01-21'

SELECT Subj_Id,Student_Id
FROM Exam_mark
WHERE Student_Id IN (10, 12)

SELECT Subj_name
FROM Subject
WHERE Subj_name LIKE 'И%'

SELECT *
FROM Student
WHERE Surname LIKE '%[ИС]%'

SELECT *
FROM Student
WHERE Name LIKE '_В%' or Name LIKE '__Р%'

SELECT *
FROM Exam_mark
WHERE Mark IS NULL

SELECT *
FROM Exam_mark
WHERE Mark IS NOT NULL

SELECT UPPER(Surname)+'; '+UPPER(Name)+'; '+UPPER(City)+'; '
+ concat(convert(varchar(20),Birthday,103), '; ',Univ_Id, '.')
FROM Student

SELECT SUBSTRING(Name, 1, 1)+'. '+ Surname +'; место жительства - '+City+'; родился - ' + concat(convert(varchar(20),Birthday,104), '.')
FROM Student

SELECT LOWER(SUBSTRING(Name, 1, 1))+'. '+LOWER(Surname)+'; место жительства - '+LOWER(City)+
'; родился: '+concat(replace(convert(varchar(20),Birthday,106), ' ', '-'), '.')
FROM Student

SELECT Surname+' '+Name+' родился в '+concat(year(Birthday),' году.')
FROM Student

SELECT Surname, Name, (Stipendia*100) AS 'Стипендия'
FROM Student

SELECT UPPER(Surname)+' '+UPPER(Name)+' родился - '+concat(year(Birthday),' году')
FROM Student WHERE Kurs=1 or Kurs=2 or Kurs=4

SELECT UPPER(Univ_Name)+'-г.'+UPPER(City)+'; РЕЙТИНГ='+UPPER(Rating)
FROM University

SELECT Univ_name+'-г.'+City+concat('; Рейтинг=',round(Rating,-2),'.')
FROM University




--lr 4
--1)
SELECT COUNT(Student_Id) as 'students'
FROM Exam_mark
WHERE Subj_Id = 2

--2)
SELECT COUNT(DISTINCT Subj_Id) as 'Количество дисциплин'
FROM Exam_mark;

--3)
SELECT Student_Id, MIN(Mark) as 'Мин оценка'
FROM Exam_mark
GROUP BY Student_Id;

--4)
SELECT Student_Id, MAX(Mark) as 'Макс оценка'
FROM Exam_mark
GROUP BY Student_Id;

--5)
SELECT Subj_Id, MIN(Mark) as 'Мин оценка'
FROM Exam_mark
GROUP BY Subj_Id;

--6)
SELECT Exam_Date, COUNT(Student_Id) as 'Кол-во студентов'
FROM Exam_mark
GROUP BY Exam_Date;

--7)
SELECT Subj_Id, AVG(Mark) as 'Средний балл'
FROM Exam_mark
GROUP BY Subj_Id

--8)
SELECT Student_Id, AVG(Mark) as 'Средний балл'
FROM Exam_mark
GROUP BY Student_Id

--9)
SELECT COUNT(DISTINCT Subj_Id) as 'Кол-во дисциплин'
FROM Exam_mark
WHERE Mark>4

------------------------------------------------------------------
--1)*
SELECT Student_id, Surname, Stipendia,(Stipendia*120)/100 AS 'Стипендия +20%'
FROM Student
ORDER BY Stipendia, Surname

--2)
SELECT TOP (2) Surname
FROM Student WHERE Surname like 'П%'
ORDER BY Surname

--3)
SELECT Student_id, max(Mark) as 'Макс. оценка' , min(Mark) as 'Мин. оценка'
FROM Exam_mark
GROUP BY Student_Id

--4)*
SELECT Subj_Name, Hour
FROM Subject
ORDER BY Subj_Name DESC, hour asc

--5)
SELECT Exam_Date, Sum(Mark) as 'Сумма баллов'
FROM Exam_mark
GROUP BY Exam_Date
ORDER BY Sum(Mark) DESC

--6)*
SELECT AVG(Mark) as 'Средняя оценка',MIN(Mark) as 'Минимальная оценка',MAX(Mark) as 'Максимальная оценка', Exam_Date
FROM Exam_mark
GROUP BY Exam_Date
ORDER BY 1 DESC, 2 desc, 3 desc




--lr 5
--1
INSERT INTO Department (Full_name, Hours)
VALUES ('Производственный', 150);

--2
insert into Company(Full_name, Short_name, City)
values ('Нефтяная компания Башнефть','Башнефть','Воронеж')

INSERT INTO Department (Full_name, Company_id)
VALUES ('Научный', 4);

INSERT INTO Cooperator (Surname, Name, City, Dept_id)
VALUES ('Орлов','Николай', 'Воронеж', 3);

--3
delete  from Evaluation
where Cooperator_id = 6

--4
update Company
set Coefficient = Coefficient + 1
where City = 'Санкт-Петербург'

--5
update Cooperator
set City = 'Москва'
where Surname = 'Иванов'

--6
INSERT INTO Good_Cooperator (Surname, Name, City, Phone_number, Passport_num, Start_date, Salary, Category, Dept_id)
SELECT Surname, Name, City, Phone_number, Passport_num, Start_date, Salary, Category, Dept_id
FROM Cooperator
WHERE Cooperator_id IN
        (SELECT Cooperator_id
        FROM Evaluation
        WHERE 3 <=
            (SELECT Count(Status)
            FROM Evaluation
            WHERE Evaluation.Cooperator_id = Cooperator.Cooperator_id AND Evaluation.Status > 2));

--7
DELETE FROM Cooperator
WHERE Cooperator_id NOT IN
    (SELECT Cooperator_id
    FROM Evaluation
    WHERE Cooperator.Cooperator_id = Evaluation.Cooperator_id);


--8-
UPDATE Cooperator
SET Salary = Salary * 0.9
WHERE 3 >=
    (SELECT AVG(Status)
    FROM Evaluation
    WHERE Evaluation.Cooperator_id = Cooperator.Cooperator_id)

--9
UPDATE Cooperator
SET Salary = Salary * 1.2
WHERE 4.7 <
    (SELECT MIN(Status)
    FROM Evaluation
    WHERE Evaluation.Cooperator_id = Cooperator.Cooperator_id)




--test for defense
insert into Cooperator (Surname, Name, Salary, City, Dept_id)
values ('Бычков', 'Алексанdр', 90000 ,'Воронеж', 1)

select Dept_id, (
select count(Cooperator_id)
from Cooperator
where Dept_id = A.Dept_id
)
from Department A;


--lr 6
--1
SELECT *
FROM Evaluation
WHERE Cooperator_id IN (
    SELECT Cooperator_id
    FROM Cooperator
    WHERE Surname = 'Иванов');


--2
SELECT Name
FROM Cooperator
WHERE Cooperator.Dept_id = 5 AND Cooperator_id IN (
    SELECT Cooperator_id
    FROM Evaluation
    WHERE Status > (SELECT AVG(Status)
                   FROM Evaluation))


--3
SELECT Name
FROM Cooperator
WHERE Dept_id = 7 AND Cooperator_id IN (
        SELECT Cooperator_id
        FROM Evaluation
        WHERE Status < (
            SELECT AVG(Status)
            FROM Evaluation));


--4
select Dept_id /*count(*)*/ as amount_of_Deps
from Department D
where (select min(Stat_count)
    from (select count(Status) as Stat_count
        from Evaluation Ev
        where Cooperator_id in (select Cooperator_id
                         from Cooperator
                         where Dept_id = D.Dept_id)
        group by Cooperator_id) B) >3;


--5
SELECT C1.Surname, C1.Name, C1.Dept_id
FROM Cooperator AS C1
WHERE C1.Salary = (
    SELECT MAX(C2.Salary)
    FROM Cooperator AS C2
    WHERE C1.Dept_id = C2.Dept_id);


--6
SELECT Surname, Name
FROM Cooperator
WHERE City NOT IN (
      SELECT DISTINCT City
      FROM Company)


--7
SELECT Coop.Cooperator_id, Coop.Name, Coop.Surname, Coop.City, Comn.City as City_of_comp
FROM Cooperator AS Coop, Department AS Dept, Company AS Comn
WHERE Coop.Dept_id = Dept.Dept_id
    AND Comn.Company_id = Dept.Company_id
        AND Coop.City != Comn.City;

SELECT Cooperator_id, Name, Surname, City
FROM Cooperator AS C
WHERE C.Dept_id IN (
    SELECT Dept_id
    FROM Department
    WHERE Company_id IN (
        SELECT Company_id
        FROM Company
        WHERE City != C.City));



--------------------------------------------------------------------------------------
--8
SELECT DISTINCT Cooperator.Cooperator_id, Surname, Name, City, Salary
FROM Cooperator
INNER JOIN Evaluation ON Cooperator.Cooperator_id = Evaluation.Cooperator_id
WHERE EXISTS (SELECT Cooperator_id
              FROM Evaluation
              WHERE Cooperator.Cooperator_id = Evaluation.Cooperator_id
              GROUP BY Cooperator_id
              HAVING MIN(Status) > 3);


--9
select distinct Cp.Cooperator_id, Cp.Surname, Cp.Name, City, Salary
from Cooperator Cp, Evaluation Ev
where Cp.Cooperator_id = Ev.Cooperator_id and 3 <
                                            (select min(Status)
                                            from Evaluation
                                            where Cooperator_id = Cp.Cooperator_id);



--10
SELECT *
FROM Cooperator c
WHERE EXISTS (
    SELECT *
    FROM Department d
    WHERE d.Dept_id = c.Dept_id
    AND c.City = (
        SELECT City
        FROM Company
        WHERE d.Company_id = Company_id));


--11
SELECT d.Full_name
FROM DEPARTMENT d, Cooperator c
WHERE d.Dept_id = c.Dept_id AND EXISTS (
    SELECT *
    FROM Evaluation e
    WHERE e.Cooperator_id = c.Cooperator_id)
GROUP BY d.Full_name
HAVING COUNT(DISTINCT c.Cooperator_id) > 1;