Разработка запросов на языке SQL

Размещено на http://www.allbest.ru/

Разработка запросов на языке SQL

Введение

Для выполнения задания была создана база данных из 2-х таблиц:

Таблица Сотрудники содержит информацию о сотрудниках, а именно поле Фамилия, Имя, Отчество, Стаж, Адрес, Должность, Телефон и поле [Номер сотрудника], являющееся первичным ключом.

Таблица Проекты содержит информацию о проектах, а именно: поля [Наименование проекта], Стоимость,[Начало разработки],[Длительность работы(дней)],Оплата и поле [Номер проекта], являющееся первичным ключом.



1. Изучение предложения SELECT

1.1 Вывести полную информацию обо всех сотрудниках предприятия

SELECT*

FROM Сотрудники

1.2 Вывести информацию о фамилии и должностях сотрудников

SELECT Фамилия,Должность

FROM Сотрудники

1.3 Вывести информацию о двух первых хранимых записях о проектах

SELECT TOP 2 *

FROM Проекты

1.4 Вывести информацию обо всех сотрудниках с указанием фамилии, специализации и стажа работы, выраженного в месяцах

SELECT Фамилия,Должность, Стаж*12

FROM Сотрудники

1.5 Выполнить задание из п. 1.1.4 с указанием имен для вычисляемых полей

SELECT Фамилия,Должность, Стаж*12 AS [Стаж в месяцах]

FROM Сотрудники

1.6 Вычислить средний стаж работы сотрудников предприятия

SELECT AVG(Стаж)AS [Средний стаж сотрудников]

FROM Сотрудники

1.7 Найти минимальную стоимость проекта

SELECT MIN(Стоимость)AS [Минимальная стоимость]

FROM Проекты

1.8 Найти максимальную стоимость проекта

SELECT MAX(Стоимость)AS [Максимальная стоимость]

FROM Проекты

1.9 Найти количество разрабатываемых проектов

SELECT COUNT(*)AS [Количество проектов]

FROM Проекты

2. Изучение предложения WHERE

2.1 Вывести информацию обо всех сотрудниках, стаж которых меньше 40 лет

SELECT*

FROM Сотрудники

WHERE Стаж < 40

2.2 Вывести информацию обо всех сотрудниках, стаж которых меньше 40, но больше 20 лет, используя логические операции

SELECT*

FROMСотрудники

WHERE Стаж>20

AND Стаж<40



2.3 Выполнить предыдущее задание, используя операцию BETWEEN

SELECT*

FROM Сотрудники

WHERE Стаж BETWEEN 20 AND 40

2.4 Вывести полную информацию о следующих сотрудниках: Ручкин, Карандашов, Линейкин

SELECT*

FROM Сотрудники

WHERE [Фамилия] ='Ручкин '

OR [Фамилия] ='Карандашов'

OR [Фамилия] ='Линейкин '

2.5 Вывести информацию о проектах, названия которых начинается с буквы П или Р

SELECT*

FROM Проекты

WHERE [Наименование проекта] LIKE'П%'

2.6 Вывести информацию о сотрудниках, работающих на должности «Программист»

SELECT*

FROM Сотрудники

WHERE Должность='Программист '

2.7 Вывести информацию о сотрудниках, для которых не указан стаж работы

SELECT*

FROM Сотрудники

WHERE Стаж IS NULL

3. Изучение предложения GROUP BY

3.1 Вывести количество сотрудников, работающих на каждой из должностей

SELECT Должность,SUM([Номер сотрудника])AS [Количество сотрудников]

FROM Сотрудники

GROUP BY Должность

3.2 Найти минимальную стоимость проекта, среди проектов, название которых начинается с буквы П

SELECT MIN(Стоимость)AS [Минимальная стоимость]

FROM Проекты

WHERE [Наименование проекта] LIKE'П%'

4. Изучение предложения HAVING

4.1 Вывести название должностей, на которых работает только один сотрудник

SELECT Должность,

COUNT([Номер сотрудника])AS [Код сотрудника]

FROM Сотрудники

GROUPBY Должность

HAVING COUNT([Номер сотрудника])=1

4.2 Выполнить предыдущий запрос для тех должностей, названия которых начинается с букв Д или П

SELECT Должность,

COUNT([Номер сотрудника])AS [Количество сотрудников]

FROM Сотрудники

WHERE Должность LIKE'П%'

OR Должность LIKE'Д%'

GROUP BY Должность

HAVING COUNT([Номер сотрудника])=1

5. Изучение предложения ORDER BY

5.1 Вывести список фамилий сотрудников в алфавитном порядке

SELECT Фамилия

FROM Сотрудники

ORDER BY Фамилия

5.2 Вывести список проектов, отсортировав его по убыванию даты начала разработки

SELECT*

FROM Проекты

ORDER BY [Начало разработки] DESC

5.3 Вывести информацию о трех самых опытных сотрудниках

SELECT TOP 3 *

FROM Сотрудники

ORDER BY Стаж DESC

6. Изучение предложения UNION

6.1 Вывести информацию о проектах, названия которых начинаются с буквы П или Р

SELECT*

FROM Проекты

WHERE [Наименование проекта] LIKE'Р%'

UNION

SELECT*

FROM Проекты

WHERE [Наименование проекта] LIKE'П%'

6.2 Вывести полную информацию обо всех сотрудниках. Если стаж сотрудника не указан, то в соответствующем столбце вывести строку: «опыт работы отсутствует»

SELECT [Фамилия],

[Имя],

[Отчество]

FROM Сотрудники

WHERE [Стаж] IS NOT NULL

UNION

SELECT [Фамилия],

[Имя],

'Опыт работы отсутствует'AS [Стаж]

FROM Сотрудники

WHERE [Стаж] ISNULL;

6.3 Вывести информацию о проектах, увеличив стоимость проекта в соответствии со следующей таблицей

Стоимость, тыс.руб. Наценка

< 100 10%

100 .. 500 20%

> 500 30%

SELECT [Наименование проекта],

СтоимостьAS [Старая стоимость],

'10%'AS Наценка,

Стоимость*1.1 AS [Новая стоимость]

FROM Проекты

WHEREСтоимость<100

UNION

SELECT [Наименование проекта],

СтоимостьAS [Старая стоимость],

'20%'AS Наценка,

Стоимость*1.2 AS [Новая стоимость]

FROM Проекты

WHERE Стоимость BETWEEN 100 AND 500

UNION

SELECT [Наименование проекта],

СтоимостьAS [Старая стоимость],

'30%'AS Наценка,

Стоимость*1.3 AS [Новая стоимость]

FROM Проекты

WHEREСтоимость>500

7. Изучение операции внутреннего соединения INNER JOIN

база данные информация

7.1 Вывести информацию о сотрудниках с указанием кодов проектов, в которых они принимаю участие

SELECT Сотрудники.Фамилия,

Проекты.[Номер проекта]

FROM Сотрудники INNER JOIN Проекты

ON Сотрудники.[Номер сотрудника]=

Проекты.[Номер сотрудника]

7.2 Вывести следующую информацию о проектах: название проекта, дата начала работы, фамилия сотрудника, а также стоимость проекта

SELECT Проекты.[Наименование проекта],Проекты.[Начало разработки],Проекты.Стоимость Сотрудники.[Фамилия]

FROM Проекты INNER JOIN Сотрудники

ON Проекты.[Номер сотрудника]=

Поставки.[Номер сотрудника]

Или:

SELECT Сотрудники.Фамилия,

Проекты.[Наименование проекта], Проекты. [Начало разработки], Проекты. Стоимость

FROM Сотрудники, Проекты

WHERE Сотрудники.[Номер сотрудника]=

Проекты.[Номер сотрудника]

7.3 Изучение операций внешнего левого LEFT JOIN и правого RIGHT JOIN соединений

Вывести информацию обо всех сотрудниках. Если сотрудник участвовал в проекте, то указать код проекта и оплату за выполнение работ.

SELECT Сотрудники.Фамилия,

Проекты. Оплата,Проекты.[Номер проекта]

FROM Проекты RIGHT JOIN Сотрудники

ON Проекты.[Номер сотрудника]=Сотрудники.[Номер сотрудника]



7.4 Вывести информацию обо всех проектах. Если за проектом закреплены сотрудники, то вывести табельный номер сотрудника и длительность работы сотрудника над проектом

SELECT Проекты.[Наименование проекта] ,Проекты.[Длительность работы(дней)], Проекты.Оплата, Сотрудники.Фамилия, Сотрудники.[Номер сотрудника]

FROM Проекты LEFT JOIN Сотрудники

ON Проекты.[Номер сотрудника]= Сотрудники.[Номер сотрудника]

7.5 Изучение операции декартова произведения для соединения таблиц

Вывести следующую информацию о проектах: фамилия сотрудника, название проекта, длительность работы сотрудника над проектом, а также оплату работы сотрудника.

SELECT Сотрудники.[Фамилия],

Проекты.[Наименование проекта], Проекты. [Длительность работы(дней)], Проекты. [Оплата]

FROM Сотрудники, Проекты

7.6 Вывести информацию обо всех сотрудниках. Если сотрудник участвовал в проекте, то указать код проекта и оплату за выполнение работ

?SELECT Сотрудники.[Фамилия],

Проекты.[Номер проекта], Проекты.[Оплата]

FROM Сотрудники, Проекты

7.8 Использование псевдонимов для самосоединения таблиц

Вывести табельные номера сотрудников, которые работают как над проектом 1, так и над проектом 3.

SELECT Проекты.[Номер сотрудника]

FROM Проекты INNER JOIN Проекты AS Проекты1

ON Проекты.[Номер сотрудника]=

Проекты1.[Номер сотрудника]

WHERE Проекты.[Номер проекта]=1

AND Проекты1.[Номер проекта]=3

7.9 Использование соединений таблиц при построении вычислений

Вывести информацию об участии сотрудников впроектах с указанием фамилии сотрудника, названия проекта, а также об оплате труда в рублях, долларах и евро.

SELECT Проекты. Оплата AS [Оплата(руб.)]Сотрудники Фамилия

FROM Проекты INNER JOIN Сотрудники

ON Проекты.[Номер сотрудника]=

Сотрудники.[Номер сотрудника]

UNION

SELECT Оплата/31,2 AS[Оплата в долларах]

FROM Проекты

UNION

SELECT Оплата/42,4 AS[Оплата в евро]

FROM Проекты

Вывести информацию о каждом сотруднике, указав количество проектов, в которых он участвует.

SELECT COUNT Проекты. [Номер проекта] AS [Количество проектов], Сотрудники Фамилия,

FROM Проекты INNER JOIN Сотрудники

ON Проекты.[Номер сотрудника]=

Сотрудники.[Номер сотрудника]

Вычислить среднюю стоимость проектов, в которых участвовал сотрудник Карандашов.

SELECT AVG Проекты.(Стоимость)

Сотрудники Фамилия

FROM Проекты INNER JOIN Сотрудники

ON Проекты.[Номер сотрудника]=

Сотрудники.[Номер сотрудника]

WHERE Сотрудники Фамилия = 'Карандашов'

8. Запросы с подзапросами

8.1 Изучение простых скалярных подзапросов

Вывести информацию обо всех сотрудниках, стаж работы которых больше стажа работы сотрудника Ручкина.

SELECT Фамилия

FROM Сотрудники

WHERE Стаж >(SELECT Стаж

FROM Сотрудники

WHERE [Фамилия]='Ручкин')

Вывести информацию о проектах, стоимость которых не ниже среднего значения стоимости.

SELECT [Номер проекта]

FROM Проекты

WHERE Стоимость <(SELECT AVG(Стоимость)

FROM Проекты)



Вывести фамилии сотрудников, которые были задействованы над выполнением самого дорогого проекта.

SELECT [Номер сотрудника]

FROM Проекты

WHERE Оплата=(SELECT MAX(Оплата)

FROM Проекты)

Вывести список сотрудников, которые никогда не работали ни над одним проектом.

SELECT Фамилия

FROM Сотрудники

WHERE 0=(SELECT COUNT(*)

FROM Проекты

WHERE Сотрудники.[Номер сотрудника]=

Проекты.[ Номер сотрудника])

Вывести фамилии сотрудников, стаж работы которых не меньше стажа работы всех сотрудников.

SELECT Фамилия

FROM Сотрудники

WHERE Стаж>=ALL(SELECT COUNT (Стаж )

FROM Сотрудники)

Вывести информацию о проектах, дата начала работы над которыми превышает соответствующую дату хотя бы одного другого проекта.

SELECT [Наименование проекта]

FROM Проекты

WHERE [Начало разработки]>=ANY(SELECT [Начало разработки]

FROM Проекты)

8.2 Изучение сложных подзапросов с использованием операции EXISTS

Вывести информацию о тех сотрудниках, которые были задействованы хотя бы над одним проектом.

SELECT Сотрудники.Фамилия

FROM Сотрудники

WHERE EXISTS(SELECT *

FROM Проекты

WHERE Сотрудники.[Номер сотрудника]= Проекты.[Номер сотрудника])

Вывести информацию о тех сотрудниках, которые никогда не работали ни над одним проектом.

SELECT Фамилия

FROM Сотрудники

WHERE NOT EXISTS(SELECT *

FROM Проекты

WHERE Сотрудники.[Номер сотрудника]=

Проекты.[Номер сотрудника])

8.3 Изучение подзапросов повышенной сложности

Вывести список фамилий сотрудников, которые задействованы ТОЛЬКО в проекте «Проектирование и разработка детали №1256

1. Изучение оператора INSERT INTO ... VALUES для добавления новой записи в таблицу.

1.1. Добавить запись с информацией о новом проекте с указанием значений всех атрибутов таблицы.

INSERT INTO Проекты ([Наименование проекта],Стоимость,[Начало разработки],[Длительность работы(дней)],Оплата)

VALUES ('Проектирование детали № 23',120000,2008-06-05,30,300000)

Строка добавилась в таблицу

1.2. Добавить запись с информацией о новом сотруднике с указанием значений только тех атрибутов таблицы, которые являются обязательными.

INSERT INTO Сотрудники (Фамилия, Имя, Отчество, Стаж, Адрес, Должность, Телефон, [Номер проекта])

VALUES ('Макаров',NULL,NULL,NULL,NULL,'Мастер',NULL,NULL)

Строка добавилась в таблицу

2. Изучение оператора INSERT INTO ... SELECT для добавления выборки в существующую таблицу.

2.1. Создать таблицу «Разрабатываемые Карандашовым проекты», поместив в нее информацию о соответствующих проектах, над которыми работает данный сотрудник.

INSERT INTO [Разрабатываемые Карандашовым проекты] ( [Номер проекта], [Наименование проекта], [Дата начала],Стоимость)

VALUES (1, 'Проектирование и разработка детали № 1256',2009-09-09,30000)

Запись в таблицу добавлена

2.2. Создать таблицу «Разработка проекта Лазер», поместив в нее информацию о всех сотрудниках, задействованных над этим проектом.

INSERT INTO [Разработка проекта Лазер]

SELECT *

FROM Сотрудники

WHERE [Номер проекта]=8

Запись скопирована из таблицы «Сотрудники» в новую таблицу



2.3. Создать таблицу «Оплата проектов», поместив в нее следующую информацию: код проекта, его название, стоимость проекта в условных единицах.

INSERT INTO [Оплата проектов]([Код проекта],Название,[Стоимость в у.е.])

VALUES (1,'Деталь № 9',15000)

3. Изучение оператора DELETE для удаления записей из таблицы.

3.1. Удалить все записи из таблицы «Разработка проекта Лазер».

DELETE

FROM [Разработка проекта Лазер]

Строки были удалены

3.2. Удалить записи с информацией о проекте «Проектирование и разработка детали №1256» из таблицы «Разрабатываемые Карандашовым проекты»

DELETE

FROM [Разрабатываемые Карандашовым проекты]

WHERE [Наименование проекта]='Проектирование и разработка детали № 1256'

3.3. Удалить записи из таблицы «Оплата проектов» о разрабатываемых проектах, стоимость которых меньше 50 у.е. и больше 300 у.е.

DELETE

FROM [Разработка проекта Лазер]

WHERE BETWEEN 50 AND 300

4. Изучение оператора UPDATE для обновления записей таблицы.

4.1. В связи с окончанием очередного календарного года увеличить стаж работы всех сотрудников на 1 год.

UPDATE Сотрудники SET Стаж = Стаж+1

4.2. Изменить название должности «специалист по маркетингу» на «маркетолог».

UPDATE Сотрудники

SET Должность = 'Специалист по маркетингу'

WHERE Должность='Маркетолог'

4.3. Изменить фамилию сотрудницы Ластиковой на Стеркинав связи с заключением брака.

UPDATE Сотрудники

SET Фамилия = 'Ластикова'

WHERE Фамилия ='Стеркина'

4.4. Увеличить на 20% стоимость проектов, выполняемых сотрудником с самым большим стажем.

Подготовить сценарий для создания файлов БД и таблиц БД. При создании таблиц задать следующие виды ограничений:

- первичные ключи (PRIMARYKEY);

- внешние ключи (FOREIGNKEY);

- ограничения на использование NULL-значений (NOTNULL);

- ограничения уникальности значений (UNIQUE);

- ограничения на значение (CHECK);

- значения по умолчанию (DEFAULT).

Подготовить сценарий, позволяющий заполнить таблицы

исходными данными.

1.Создать таблицу «Сотрудники»

CREATE TABLE Сотрудники

([Номер сотрудника] INTEGER,

ФамилияVARCHAR(50) NOTNULL,

Имя VARCHAR(50),

Отчество VARCHAR(50),

Стаж INTEGER,

Адрес VARCHAR(50),

Должность VARCHAR(50)NOT NULL,

ТелефонINTEGER,

[Номер проекта]INTEGER,

CONSTRAINT PK_Сотрудники

PRIMARY KEY ([Номер сотрудника])

CONSTRAINT FK_Сотрудники_Проекты

FOREIGN KEY([Номер сотрудника])

REFERENCES Проекты)

2. .Создать таблицу «Проекты»

CREATETABLE Проекты

([Номер проекта] INTEGER NOT NULL,

[Наименование проекта ] VARCHAR(50)NOT NULL,

Стоимость VARCHAR(50)NOT NULL,

[Начало разработки]DATETIME,

[Длительность работы(дней)] INTEGER,

Оплата VARCHAR(50)

CONSTRAINT PK_Проекты

PRIMARY KEY ([Номер проекта])

Размещено на Allbest.ru