Министерство образования Республики Беларусь

Учреждение образования

БЕЛОРУССКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

ИНФОРМАТИКИ И РАДИОЭЛЕКТРОНИКИ

Кафедра экономической информатики

ЛАБОРАТОРНАЯ РАБОТА №3

ЯЗЫК SQL. СОЗДАНИЕ ТАБЛИЦ И ОГРАНИЧЕНИЙ НА SQL. СОЗДАНИЕ ЗАПРОСОВ.

Вариант 13

Выполнил: студент гр. 972304

Рушева М. В.

Проверил:

Минск, 2022

Добавление записей в таблицы

use departaments

-- Departament

INSERT INTO Departament

VALUES('Отдел финансов', 420, '+375257691966', 'Рушева М М')

INSERT INTO Departament

VALUES('Отдел маркетинга', 1, '+375257391966', 'Пырко Вадим В')

INSERT INTO Departament

VALUES('Отдел разработки', 3, '+375212691966', 'Никто Н Н')

INSERT INTO Departament

VALUES('Отдел логистики', 2, '+37523331966', 'Вика М В')

INSERT INTO Departament

VALUES('Отдел технического контроля', 4, '+375257691966', 'Опять Никто Мн')

-- Employee

INSERT INTO Employee

VALUES('Рушева М М', 'Прочий обслуживающий персонал', 1, 'Jakuba Kolasa 53/3/83 Minsk', '2002-07-06', 2)

INSERT INTO Employee

VALUES('Г Г Г', 'Лаборант', 1, 'Jakuba Kolasa 23/3/83 Minsk', '2002-07-06', 1)

INSERT INTO Employee

VALUES('Никто Н Н', 'Конструктор', 1, 'Jakuba Kolasa 53/3/83 Minsk', '2002-07-06', 3)

INSERT INTO Employee(name, position, isMale, address, birthday)

VALUES('С У К', 'Конструктор', 1, 'Paris, Lenin Square', '2002-07-06')

INSERT INTO Employee

VALUES('А А А', 'Техник', 1, 'Jakuba Kolasa 53/3/83 Minsk', '2002-07-06', 5)

-- Organization

INSERT INTO Organization VALUES

('БелГазПром Банк', 'Банк', 'Минск', 'Беларусь', 'Сурганова 32', 'Вадим Викторович Ведрищев')

INSERT INTO Organization VALUES

('Шаурма Мираж', 'Ресторан', 'Минск', 'Беларусь', 'где то около комаровки', 'Я Не Могуэто')

INSERT INTO Organization(organizationName, typeOfActivity, city, country, address, directorName)

VALUES ('Трамп', 'Застройщик', 'Минск', 'Беларусь', 'Незалежнасьці 12', 'Господи Я Зае')

INSERT INTO Organization VALUES

('ОАО Украина', 'Церковь', 'Киев', 'Украина', 'Майдан', 'Никто Но Но')

INSERT INTO Organization VALUES

('ОАО Нильфгаард', 'Империя', 'Минск', 'Беларусь', 'Сурганова 32', 'Я Устала Капец')

-- Agreement

INSERT INTO Agreement VALUES

('BGP12221', '2022-02-24', 4422.23, 1)

INSERT INTO Agreement(agreementNumber, date, price)

VALUES('NFG-12', '1227-02-24', 800000)

INSERT INTO Agreement VALUES

('UK12', '2022-02-24', 3131212.23, 5)

INSERT INTO Agreement VALUES

('123321', '2022-02-24', 4422.23, 4)

INSERT INTO Agreement VALUES

('GG11112', '2022-02-24', 4422.23, 2)

-- DesignWork

INSERT INTO DesignWork VALUES

('2014-02-12', '2022-02-24', 1, 2)

INSERT INTO DesignWork VALUES

('2012-02-12', '2022-02-24', 2, 3)

INSERT INTO DesignWork VALUES

('2014-02-01', '2022-02-24', 4, 1)

INSERT INTO DesignWork VALUES

('2014-02-12', '2022-02-24', 1, 5)

INSERT INTO DesignWork VALUES

('2014-02-12', '2022-02-24', 2, 5)

select * from Departament

select * from Employee

select * from Organization

select * from Agreement

Запросы с отбором строк по условию

SELECT * FROM Employee

WHERE birthday < GETDATE()

SELECT * FROM Departament

WHERE floorNumber <= 4

SELECT * FROM DesignWork

WHERE startDate >= '2013-01-01'

-- AND, OR, NOT

SELECT * FROM Organization

WHERE city = 'Минск' OR city = 'Киев'

SELECT * FROM Agreement

WHERE price > 5000 AND date < '2022-01-01'

SELECT * FROM Employee

WHERE NOT position = 'Техник' AND NOT position = 'Конструктор'

-- compining logical operators

SELECT * FROM Organization

WHERE address LIKE '%32%' AND NOT typeOfActivity = 'Банк'

SELECT * FROM Employee

WHERE birthday BETWEEN '2001-01-01' AND '2004-01-01' AND NOT position IN('Конструктор')

-- Columns expressions

SELECT * FROM Agreement

WHERE price + agreementId < 800000

SELECT * FROM Agreement

WHERE price/100000 < agreementId

-- Sets

SELECT * FROM Agreement

WHERE price/100000 IN (2,4,8)

SELECT * FROM Employee

WHERE position IN ('Конструктор', 'Техник')

-- Ranges

SELECT * FROM Agreement

WHERE price/100000 BETWEEN 2 AND 8

SELECT * FROM Employee

WHERE position BETWEEN 'Лаборант' AND 'Техник'

-- Pattern

SELECT * FROM Employee

WHERE address LIKE '%53%'

SELECT * FROM Employee

WHERE name LIKE 'С%'

-- not null

SELECT * FROM Employee

WHERE departamentId IS NULL

SELECT * FROM Employee

WHERE departamentId IS NOT NULL

Многотабличные запросы

SELECT * FROM Employee, Departament

SELECT * FROM Organization CROSS JOIN Employee

------

SELECT * FROM Employee, Departament

WHERE Employee.departamentId = Departament.departamentId

SELECT * FROM Agreement

INNER JOIN Organization ON Organization.organizationId = Agreement.organizationId

-------

SELECT * FROM Agreement

INNER JOIN Organization ON Organization.organizationId = Agreement.organizationId

WHERE typeOfActivity = 'Банк'

SELECT * FROM DesignWork

INNER JOIN Departament ON Departament.departamentId = DesignWork.departamentId

WHERE departamentName LIKE '% % %'

SELECT * FROM DesignWork

INNER JOIN Departament ON Departament.departamentId = DesignWork.departamentId

INNER JOIN Agreement ON Agreement.agreementId = DesignWork.agreementId

SELECT * FROM Employee

FULL JOIN Departament ON Departament.departamentId = Employee.departamentId

SELECT * FROM Agreement

FULL JOIN Organization ON Organization.organizationId = Agreement.organizationId

SELECT * FROM Employee

LEFT JOIN Departament ON Departament.departamentId = Employee.departamentId

SELECT * FROM Agreement

LEFT JOIN Organization ON Organization.organizationId = Agreement.organizationId

SELECT * FROM Departament

RIGHT JOIN Employee ON Departament.departamentId = Employee.departamentId

SELECT * FROM Agreement

RIGHT JOIN Organization ON Organization.organizationId = Agreement.organizationId

SELECT em1.*

FROM Employee em1, Employee em2

WHERE em2.name LIKE '%Селявко%' AND em2.address = em1.address AND NOT em1.name LIKE '%Селявко%'

SELECT agr1.*

FROM Agreement agr1, Agreement agr2

WHERE agr2.agreementNumber = 'BGP12221' AND agr2.date = agr1.date AND NOT agr1.agreementNumber = 'BGP12221'

Запросы на группировку и сортировку, изменение, удаление и использование внутренних функций

-- COUNT

SELECT COUNT(*) as total_count_of_agreements FROM Agreement

SELECT COUNT(departamentId) as total_count_of_departaments FROM Employee

-- SUM

select sum(price) as total_price from Agreement

select sum(employeeId) as sum_of_id from Employee

-- UPPER/LOWER

select upper(name) as upper_name from Employee

select lower(name) as lower_name from Employee

-- DateTime Functions

select datediff(day, startDate, endDate) as count_of_days from DesignWork

select day(birthday) as day, month(birthday) as month, year(birthday) as year from Employee

-- Grouping by one column

select address, Count(*) from Employee

group by address

select price, sum(price) from Agreement

group by price

-- Grouping by several columns

select date, price, count(*) from Agreement

group by date, price

select startDate, endDate, count(*) from DesignWork

group by startDate, endDate

-- GroupBy + Having

select date, price, count(*) from Agreement

group by date, price

having count(*) > 1

select price, sum(price) from Agreement

group by price

having sum(price) <> 800000

-- Having without GroupBy

select sum(price) from Agreement

having sum(price) <> 800000

select count(*) from Agreement

having count(*) > 4

-- OrderBy

select * from Employee

order by name

select * from Employee

order by name desc

-- insert date

select * from Employee

select * from Departament

INSERT INTO Employee

VALUES('Селявко Иван Никитич', 'Прочий обслуживающий персонал', 1, 'Jakuba Kolasa 53/3/83 Minsk', '2002-07-06', 2)

INSERT INTO Departament

VALUES('Отдел разработки', 3, '+37512345678', 'Дольский Евгений')

select * from Employee

select * from Departament

-- update data

select * from DesignWork

update DesignWork

set startDate = DATEADD(DAY, 1, startDate)

select * from DesignWork

select * from Employee

update Employee

set name = lower(name)

select * from Employee

-- update data with where

select * from Agreement

update Agreement

set price = price + 1

where price <> 800000

select * from Agreement

select * from Employee

update Employee

set name = 'Селявко Иван Александрович'

where name LIKE '%елявко%'

select * from Employee

-- delete data

select * from Employee

delete Employee

where name like '%орович%'

select * from Employee

select * from Departament

delete Departament

where phone = '+37512345678'

select * from Departament