Как высчитать одновременно две зарплаты sql

Указание нескольких условий поиска для одного столбца (визуальные инструменты для баз данных)

Иногда может понадобиться сочетание нескольких условий поиска для одного столбца. Например, можно сделать следующее:

• Найти несколько разных имен сотрудников в таблице employee , находящихся в разных группах по уровню зарплаты. Такой тип поиска требует использования условия OR (или).
• Найти книгу, название которой начинается со слова «The» и содержит слово «Cook». Такой тип поиска требует использования условия AND (и).

Сведения, приведенные в этом подразделе, применимы к условиям поиска как в предложении WHERE, так и в предложении HAVING запроса. В примерах в основном используется предложение WHERE, но рассматриваемые принципы применимы к обоим типам задания условий поиска.

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

Указание условия OR

Использование условия OR позволяет указать несколько альтернативных значений для поиска в столбце. Этот параметр расширяет пространство поиска и может вернуть больше записей, чем при поиске одного значения.

Часто можно использовать оператор IN вместо того, чтобы искать несколько значений в одном столбце.

Указание условия OR

1. На панели критериевдобавьте столбец для поиска.
2. Укажите первое условие в столбце Фильтр только что добавленного столбца данных.
3. Укажите второе условие в столбце Или. этого столбца данных.

Конструктор запросов и представлений создает предложение WHERE, содержащее условие OR, подобное следующему:

SELECT fname, lname FROM employees WHERE (salary < 30000) OR (salary >100000) 

Указание условия AND

Использование оператора AND позволяет задать такие условия поиска, при которых результирующий набор удовлетворяет сразу двум (или более) критериям поиска. При использовании этого параметра пространство поиска сужается и обычно возвращается меньше записей, чем при поиске по одному значению.

Для поиска в диапазоне значений можно использовать оператор BETWEEN вместо двух условий, объединенных оператором AND.

Указание условия AND

1. На панели критериев добавьте столбец для поиска.
2. Укажите первое условие в столбце Фильтр только что добавленного столбца данных.
3. Добавьте тот же столбец на панель критериев еще раз, поместив его в пустую строку сетки.
4. В столбце Фильтр второй строки укажите второе условие.

Конструктор запросов создает предложение WHERE, которое содержит условие AND, подобное следующему:

SELECT title_id, title FROM titles WHERE (title LIKE '%Cook%') AND (title LIKE '%Recipe%') 

5 вопросов по SQL, которые часто задают дата-сайентистам на собеседованиях

Хотя составление SQL-запросов — это не самое интересное в работе дата-сайентистов, хорошее понимание SQL чрезвычайно важно для того, кто хочет преуспеть в любом занятии, связанном с обработкой данных. Дело тут в том, что SQL — это не только SELECT , FROM и WHERE . Чем больше SQL-конструкций знает специалист — тем легче ему будет создавать запросы на получение из баз данных всего, что ему может понадобиться.

Автор статьи, перевод которой мы сегодня публикуем, говорит, что она направлена на решение двух задач:

1. Изучение механизмов, которые выходят за пределы базового знания SQL.
2. Рассмотрение нескольких практических задач по работе с SQL.

Вопрос №1: второе место по зарплате

Напишите SQL-запрос для получения из таблицы со сведениями о заработной плате сотрудников ( Employee ) записи, содержащей вторую по размеру заработную плату.

Например, такой запрос, выполненный для таблицы, представленной ниже, должен вернуть 200 . Если в таблице нет значения, меньшего, чем самая высокая зарплата — запрос должен вернуть null .

+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+

▍Решение А: использование IFNULL и OFFSET

Вот основные механизмы, которые будут использованы в данном варианте решения задачи:

• IFNULL(expression, alt) : эта функция возвращает свой аргумент expression в том случае, если он не равен null . В противном случае возвращается аргумент alt . Мы воспользуемся этой функцией для того чтобы возвратить null в том случае, если в таблице не окажется искомого значения.
• OFFSET : этот оператор используется с выражением ORDER BY для того чтобы отбросить первые n строк. Это нам пригодится по той причине, что нас интересует вторая строка результата (то есть — вторая по величине зарплата, данные о которой есть в таблице).

SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 ), null) as SecondHighestSalary FROM Employee LIMIT 1

▍Решение B: использование MAX

В запросе, представленном ниже, используется функция MAX . Здесь выбирается самое большое значение заработной платы, не равное максимальной заработной плате, полученной по всей таблице. В результате мы и получаем то, что нам нужно — вторую по величине заработную плату.

SELECT MAX(salary) AS SecondHighestSalary FROM Employee WHERE salary != (SELECT MAX(salary) FROM Employee)

Вопрос №2: дублирующиеся адреса электронной почты

Напишите SQL-запрос, который обнаружит в таблице Person все дублирующиеся адреса электронной почты.

+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+

▍Решение А: COUNT в подзапросе

Сначала мы создаём подзапрос, в котором выясняется частота появления каждого адреса в таблице. Затем результат, возвращаемый подзапросом, фильтруется с использованием инструкции WHERE count > 1 . Запрос вернёт сведения об адресах, встречающихся в исходной таблице больше одного раза.

SELECT Email FROM ( SELECT Email, count(Email) AS count FROM Person GROUP BY Email ) as email_count WHERE count > 1

▍Решение B: выражение HAVING

• HAVING : это выражение, которое позволяет использовать инструкцию WHERE вместе с выражением GROUP BY .

SELECT Email FROM Person GROUP BY Email HAVING count(Email) > 1

Вопрос №3: растущая температура

Напишите SQL-запрос, который находит в таблице Weather все даты (идентификаторы дат), когда температура была бы выше температуры на предшествующие им даты. То есть, нас интересуют даты, в которые «сегодняшняя» температура выше «вчерашней».

+---------+------------------+------------------+ | Id(INT) | RecordDate(DATE) | Temperature(INT) | +---------+------------------+------------------+ | 1 | 2015-01-01 | 10 | | 2 | 2015-01-02 | 25 | | 3 | 2015-01-03 | 20 | | 4 | 2015-01-04 | 30 | +---------+------------------+------------------+

▍Решение: DATEDIFF

• DATEDIFF : эта функция вычисляет разницу между двумя датами. Она используется для того, чтобы обеспечить сравнение именно «сегодняшних» и «вчерашних» температур.

SELECT DISTINCT a.Id FROM Weather a, Weather b WHERE a.Temperature > b.Temperature AND DATEDIFF(a.Recorddate, b.Recorddate) = 1

Вопрос №4: самая высокая зарплата в подразделении

В таблице Employee хранятся сведения о сотрудниках компании. В каждой записи этой таблицы содержатся сведения об идентификаторе ( Id ) сотрудника, о его имени ( Name ), о зарплате ( Salary ) и о подразделении компании, где он работает ( Department ).

+----+-------+--------+--------------+ | Id | Name | Salary | DepartmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | +----+-------+--------+--------------+

В таблице Department содержатся сведения о подразделениях компании.

+----+----------+ | Id | Name | +----+----------+ | 1 | IT | | 2 | Sales | +----+----------+

Напишите SQL-запрос, который находит в каждом из подразделений сотрудников с максимальной заработной платой. Например, для вышеприведённых таблиц подобный запрос должен возвращать результаты, представленные следующей таблицей (при этом порядок строк в таблице значения не имеет):

+------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Jim | 90000 | | Sales | Henry | 80000 | +------------+----------+--------+

▍Решение: команда IN

Команда IN позволяет задавать в инструкции WHERE условия, соответствующие использованию нескольких команд OR . Например, две следующие конструкции идентичны:

WHERE country = ‘Canada’ OR country = ‘USA’ WHERE country IN (‘Canada’, ’USA’).

Здесь мы хотим получить таблицу, содержащую название подразделения ( Department ), имя сотрудника ( Employee ) и его заработную плату ( Salary ). Для этого мы формируем таблицу, в которой содержатся сведения об идентификаторе подразделения ( DepartmentID ) и о максимальной зарплате по этому подразделению. Далее мы объединяем две таблицы по условию, в соответствии с которым записи в результирующую таблицу попадают только в том случае, если DepartmentID и Salary есть в ранее сформированной таблице.

SELECT Department.name AS 'Department', Employee.name AS 'Employee', Salary FROM Employee INNER JOIN Department ON Employee.DepartmentId = Department.Id WHERE (DepartmentId , Salary) IN ( SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId )

Вопрос №5: пересаживание учеников

Мэри — учительница в средней школе. У неё есть таблица seat , хранящая имена учеников и сведениях об их местах в классе. Значение id в этой таблице постоянно возрастает. Мэри хочет поменять местами соседних учеников.

Вот таблица исходного размещения учеников:

+---------+---------+ | id | student | +---------+---------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | +---------+---------+

Вот что должно получиться после пересаживания соседних учеников:

+---------+---------+ | id | student | +---------+---------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | +---------+---------+

Напишите запрос, который позволит учительнице решить вышеописанную задачу.

Обратите внимание на то, что если количество учеников является нечётным — последнего ученика никуда пересаживать не надо.

▍Решение: использование оператора WHEN

SQL-конструкцию CASE WHEN THEN можно рассматривать как оператор if в программировании.

В нашем случае первый оператор WHEN используется для проверки того, назначен ли последней строке в таблице нечётный идентификатор. Если это так — строка не подвергается изменениям. Второй оператор WHEN отвечает за добавление 1 к каждому нечётному идентификатору (например — 1, 3, 5 превращается в 2, 4, 6) и за вычитание 1 из каждого чётного идентификатора (2, 4, 6 превращаются в 1, 3, 5).

SELECT CASE WHEN((SELECT MAX(id) FROM seat)%2 = 1) AND MAX(id) FROM seat) THEN id WHEN id%2 = 1 THEN id + 1 ELSE id - 1 END AS id, student FROM seat ORDER BY id

Итоги

Мы разобрали несколько задач по SQL, попутно обсудив некоторые продвинутые средства, которые можно использовать при составлении SQL-запросов. Надеемся, то, что вы сегодня узнали, пригодится вам при прохождении собеседований по SQL и окажется полезным в повседневной работе.

P.S. В нашем маркетплейсе есть Docker-образ с SQL Server Express, который устанавливается в один клик. Вы можете проверить работу контейнеров на VPS. Всем новым клиентам бесплатно предоставляются 3 дня для тестирования.

Уважаемые читатели! Что вы можете посоветовать тем, кто хочет освоить искусство создания SQL-запросов?

• Блог компании RUVDS.com
• Занимательные задачки
• SQL
• Карьера в IT-индустрии

Выражение CASE (Transact-SQL)

Оценка списка условий и возвращение одного из нескольких возможных выражений результатов.

Выражение CASE имеет два формата:

• Простое CASE выражение сравнивает выражение с набором простых выражений для определения результата.
• Поисковое CASE выражение вычисляет набор логических выражений для определения результата.

Оба формата поддерживают дополнительный аргумент ELSE.

CASE можно использовать в любом операторе или предложении, позволяющем допустимое выражение. Например, можно использовать CASE в таких инструкциях, как SELECT, UPDATE, DELETE и SET, а также в таких предложениях, как , IN, WHERE, ORDER BY и HAVING.

Синтаксис

Синтаксис для SQL Server, База данных SQL Azure и Azure Synapse Analytics.

-- Simple CASE expression: CASE input_expression WHEN when_expression THEN result_expression [ . n ] [ ELSE else_result_expression ] END -- Searched CASE expression: CASE WHEN Boolean_expression THEN result_expression [ . n ] [ ELSE else_result_expression ] END 

Синтаксис для параллельного хранилища данных.

CASE WHEN when_expression THEN result_expression [ . n ] [ ELSE else_result_expression ] END 

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

input_expression

Выражение, вычисляемое при использовании простого CASE формата. input_expression — это любое допустимое выражение.

WHEN when_expression

Простое выражение, с которым input_expression сравнивается при использовании простого CASE формата. when_expression — это любое допустимое выражение. Типы данных аргумента input_expression и каждого из выражений when_expression должны быть одинаковыми или неявно приводимыми друг к другу.

THEN result_expression

Выражение, возвращаемое, когда input_expression равно when_expression вычисляется значение TRUE или Boolean_expression оценивается как TRUE. result expression — это любое допустимое выражение.

ELSE else_result_expression

Выражение возвращается, если операция сравнения не оценивается как TRUE. Если этот аргумент опущен и операция сравнения не оценивается как TRUE, CASE возвращает значение NULL. else_result_expression — это любое допустимое выражение. Типы данных аргумента else_result_expression и каждого из выражений result_expression должны быть одинаковыми или неявно приводимыми друг к другу.

WHEN Boolean_expression

Логическое выражение, вычисляемое при использовании искомого CASE формата. Boolean_expression — это любое допустимое логическое выражение.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Типы возвращаемых данных

Возвращает тип с наивысшим приоритетом из набора типов в выражении result_expressions и необязательном выражении else_result_expression. Дополнительные сведения см. в разделе Приоритет типов данных (Transact-SQL).

Возвращаемые значения

Простое выражение CASE

Простое CASE выражение работает путем сравнения первого выражения с выражением в каждом предложении WHEN для эквивалентности. Если эти выражения эквивалентны, то возвращается выражение в предложении THEN.

• Допускается только проверка равенства.
• В указанном порядке сравнивает значения выражений input_expression и when_expression для каждого предложения WHEN.
• Возвращает выражение result_expression, соответствующее первой операции input_expression = when_expression, равной TRUE.
• Если input_expression when_expression = не оценивается как TRUE, sql Server ядро СУБД возвращает else_result_expression, если указано предложение ELSE, или значение NULL, если предложение ELSE не указано.

Поисковое выражение CASE

• Вычисляет в указанном порядке выражения Boolean_expression для каждого предложения WHEN.
• Возвращает выражение result_expression, соответствующее первому выражению Boolean_expression, которое имеет значение TRUE.
• Если значение Boolean_expression равно TRUE, ядро СУБД возвращает else_result_expression, если указано предложение ELSE, или значение NULL, если не указано предложение ELSE.

Замечания

SQL Server позволяет использовать только 10 уровней вложения в CASE выражениях.

Выражение CASE нельзя использовать для управления потоком выполнения инструкций Transact-SQL, блоков инструкций, определяемых пользователем функций и хранимых процедур. Список методов управления потоком см. в статье Язык управления потоком (Transact-SQL).

Выражение CASE оценивает условия последовательно и останавливается с первым условием, условие которого удовлетворено. В некоторых ситуациях выражение вычисляется до того, как CASE выражение получает результаты выражения в качестве входных данных. При оценке этих выражений возможны ошибки. Агрегированные выражения, которые отображаются в аргументах WHEN для CASE выражения, вычисляются сначала, а затем предоставляются выражению CASE . Например в следующем запросе создается ошибка деления на ноль при вычислении значения агрегата MAX. Это происходит до оценки CASE выражения.

WITH Data (value) AS ( SELECT 0 UNION ALL SELECT 1 ) SELECT CASE WHEN MIN(value) = 100 THEN 1 END FROM Data; GO 

Вы должны зависеть только от порядка вычисления условий WHEN для скалярных выражений (включая не коррелированные вложенные запросы, возвращающие скаляры), а не для агрегатных выражений.

Кроме того, необходимо убедиться, что хотя бы одно из выражений в предложениях THEN или ELSE не является константой NULL. Хотя значение NULL может быть возвращено из нескольких выражений результатов, не все из них могут явно быть константой NULL. Если все выражения результатов используют константу NULL, возвращается ошибка 8133.

Примеры

А. Использование инструкции SELECT с простым выражением CASE

При использовании в инструкции SELECT простое выражение CASE позволяет выполнить только проверку на равенство. Другие проверки не выполняются. В следующем примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными.

USE AdventureWorks2022; GO SELECT ProductNumber, Category = CASE ProductLine WHEN 'R' THEN 'Road' WHEN 'M' THEN 'Mountain' WHEN 'T' THEN 'Touring' WHEN 'S' THEN 'Other sale items' ELSE 'Not for sale' END, Name FROM Production.Product ORDER BY ProductNumber; GO 

B. Использование инструкции SELECT с поисковым выражением CASE

При использовании в инструкции SELECT поисковое выражение CASE позволяет заменять значения в результирующем наборе в зависимости от результатов сравнения. В следующем примере отображается список цен в виде текстового комментария, основанного на диапазоне цен для продукта.

USE AdventureWorks2022; GO SELECT ProductNumber, Name, "Price Range" = CASE WHEN ListPrice = 0 THEN 'Mfg item - not for resale' WHEN ListPrice < 50 THEN 'Under $50' WHEN ListPrice >= 50 AND ListPrice < 250 THEN 'Under $250' WHEN ListPrice >= 250 AND ListPrice < 1000 THEN 'Under $1000' ELSE 'Over $1000' END FROM Production.Product ORDER BY ProductNumber; GO 

C. Использование CASE в предложении ORDER BY

В следующих примерах выражение используется CASE в предложении ORDER BY для определения порядка сортировки строк на основе заданного значения столбца. В первом примере вычисляется значение столбца SalariedFlag таблицы HumanResources.Employee . Сотрудники, для которых столбец SalariedFlag имеет значение 1, возвращаются в порядке BusinessEntityID (по убыванию). Сотрудники, для которых столбец SalariedFlag имеет значение 0, возвращаются в порядке BusinessEntityID (по возрастанию). Во втором примере результирующий набор упорядочивается по столбцу TerritoryName , если столбец CountryRegionName содержит значение «США», и по столбцу CountryRegionName в остальных строках.

SELECT BusinessEntityID, SalariedFlag FROM HumanResources.Employee ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC, CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END; GO 

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName FROM Sales.vSalesPerson WHERE TerritoryName IS NOT NULL ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName ELSE CountryRegionName END; GO 

D. Использование CASE в инструкции UPDATE

В следующем примере выражение в инструкции UPDATE используется CASE для определения значения столбца для VacationHours сотрудников с SalariedFlag заданным значением 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов. С помощью предложения OUTPUT отображаются исходная и обновленная продолжительности отпуска.

USE AdventureWorks2022; GO UPDATE HumanResources.Employee SET VacationHours = ( CASE WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40 ELSE (VacationHours + 20.00) END ) OUTPUT Deleted.BusinessEntityID, Deleted.VacationHours AS BeforeValue, Inserted.VacationHours AS AfterValue WHERE SalariedFlag = 0; GO 

Д. Использование CASE в инструкции SET

В следующем примере используется CASE выражение в инструкции SET в функции dbo.GetContactInfo с табличным значением. В базе данных AdventureWorks2022 все данные, связанные с людьми, хранятся в таблице Person.Person . Например, человек может быть сотрудником, представителем поставщика или заказчиком. Функция возвращает имя и фамилию заданного BusinessEntityID и типа контакта для этого человека. Выражение CASE в инструкции SET определяет значение, отображаемое для столбца на основе существования столбца ContactType BusinessEntityID в Employee Vendor таблицах или Customer таблицах.

USE AdventureWorks2022; GO CREATE FUNCTION dbo.GetContactInformation (@BusinessEntityID INT) RETURNS @retContactInformation TABLE ( BusinessEntityID INT NOT NULL, FirstName NVARCHAR(50) NULL, LastName NVARCHAR(50) NULL, ContactType NVARCHAR(50) NULL, PRIMARY KEY CLUSTERED (BusinessEntityID ASC) ) AS -- Returns the first name, last name and contact type for the specified contact. BEGIN DECLARE @FirstName NVARCHAR(50), @LastName NVARCHAR(50), @ContactType NVARCHAR(50); -- Get common contact information SELECT @BusinessEntityID = BusinessEntityID, @FirstName = FirstName, @LastName = LastName FROM Person.Person WHERE BusinessEntityID = @BusinessEntityID; SET @ContactType = CASE -- Check for employee WHEN EXISTS ( SELECT * FROM HumanResources.Employee AS e WHERE e.BusinessEntityID = @BusinessEntityID ) THEN 'Employee' -- Check for vendor WHEN EXISTS ( SELECT * FROM Person.BusinessEntityContact AS bec WHERE bec.BusinessEntityID = @BusinessEntityID ) THEN 'Vendor' -- Check for store WHEN EXISTS ( SELECT * FROM Purchasing.Vendor AS v WHERE v.BusinessEntityID = @BusinessEntityID ) THEN 'Store Contact' -- Check for individual consumer WHEN EXISTS ( SELECT * FROM Sales.Customer AS c WHERE c.PersonID = @BusinessEntityID ) THEN 'Consumer' END; -- Return the information to the caller IF @BusinessEntityID IS NOT NULL BEGIN INSERT @retContactInformation SELECT @BusinessEntityID, @FirstName, @LastName, @ContactType; END; RETURN; END; GO SELECT BusinessEntityID, FirstName, LastName, ContactType FROM dbo.GetContactInformation(2200); GO SELECT BusinessEntityID, FirstName, LastName, ContactType FROM dbo.GetContactInformation(5); GO 

F. Использование CASE в предложении HAVING

В следующем примере выражение в предложении HAVING используется CASE для ограничения строк, возвращаемых инструкцией SELECT. Инструкция возвращает почасовую ставку для каждого названия задания в HumanResources.Employee таблице. Предложение HAVING ограничивает названия тех, которые удерживаются зарплатными сотрудниками с максимальной ставкой заработной платы больше 40 долларов, или неоплачиваемых сотрудников с максимальной ставкой заработной платы больше 15 долларов.

USE AdventureWorks2022; GO SELECT JobTitle, MAX(ph1.Rate) AS MaximumRate FROM HumanResources.Employee AS e INNER JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID GROUP BY JobTitle HAVING ( MAX(CASE WHEN SalariedFlag = 1 THEN ph1.Rate ELSE NULL END) > 40.00 OR MAX(CASE WHEN SalariedFlag = 0 THEN ph1.Rate ELSE NULL END) > 15.00 ) ORDER BY MaximumRate DESC; GO 

Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)

G. Использование инструкции SELECT с выражением CASE

В инструкции CASE SELECT выражение позволяет заменить значения в результирующем наборе на основе значений сравнения. В следующем примере выражение CASE используется для изменения способа отображения категорий линейки продуктов с целью сделать их более понятными. Если значение не существует, отображается текст "Не для продажи".

-- Uses AdventureWorks SELECT ProductAlternateKey, Category = CASE ProductLine WHEN 'R' THEN 'Road' WHEN 'M' THEN 'Mountain' WHEN 'T' THEN 'Touring' WHEN 'S' THEN 'Other sale items' ELSE 'Not for sale' END, EnglishProductName FROM dbo.DimProduct ORDER BY ProductKey; GO 

H. Использование CASE в инструкции UPDATE

В следующем примере выражение в инструкции UPDATE используется CASE для определения значения столбца для VacationHours сотрудников с SalariedFlag заданным значением 0. Если при вычитании 10 часов из VacationHours получается отрицательное значение, VacationHours увеличивается на 40 часов. В противном случае значение VacationHours увеличивается на 20 часов.

-- Uses AdventureWorks UPDATE dbo.DimEmployee SET VacationHours = ( CASE WHEN ((VacationHours - 10.00) < 0) THEN VacationHours + 40 ELSE (VacationHours + 20.00) END ) WHERE SalariedFlag = 0; GO 

См. также

• Выражения (Transact-SQL)
• SELECT (Transact-SQL)
• COALESCE (Transact-SQL)
• IIF (Transact-SQL)
• CHOOSE (Transact-SQL)

10 популярных функций в MYSQL, которые вам нужно знать

Без лишних слов принесли вам некоторые популярные функции MySQL, без которых вы точно не обойдётесь в работе.

AVG

Возвращает среднее значение из ряда числовых значений в виде десятичной дроби.

Например, если столбец таблицы содержит значения 1 , 2 , 3 и 4 , то функция AVG, применённая к этому столбцу, вернёт значение 2.5 . Функция AVG игнорирует значения NULL , поэтому, если какое-либо из значений в наборе является NULL , оно не будет включено в расчёт. Синтаксис функции AVG следующий:

AVG(expression) 

Где expression — это столбец или выражение, содержащее числовые значения, которые необходимо усреднить. Например, вы можете использовать следующий запрос для расчёта средней зарплаты всех сотрудников в таблице:

SELECT AVG(salary) FROM employees; 

COUNT

Возвращает количество строк, которые соответствуют заданном условию. Результат —всегда целое число.

Например, если у вас есть таблица с 10 строками, то функция COUNT , примененная к этой таблице, вернёт значение 10 . Функция COUNT тоже игнорирует значения NULL , поэтому если какая-либо из строк таблицы содержит значения NULL в подсчитываемых столбцах, они не будут включены в расчёт.

Синтаксис функции COUNT следующий:

COUNT(expression) 

Где expression — столбец или выражение для подсчёта.

Вы также можете использовать условие, чтобы указать, какие строки должны быть подсчитаны. Например, так можно посчитать, сколько в компании сотрудников с зарплатой выше 50 000 ₽:

SELECT COUNT(*) FROM employees WHERE salary > 50000; 

MAX и MIN

Возвращают максимальное и минимальное значение из набора числовых или строковых значений.

Из набора 1 , 2 , 3 и 4 функция MAX вернёт значение 4 . Если столбец содержит строковые значения яблоко , банан и вишня , функция MAX вернёт значение яблоко . Функция MAX , как и предыдущие, игнорирует значения NULL , поэтому если какое-либо из значений в наборе является NULL , оно не будет включено в расчёт. Синтаксис функции MAX следующий:

MAX(expression) 

Где expression — столбец или выражение, содержащее сравниваемые значения. Например, вы можете использовать следующий запрос, чтобы найти самую высокую зарплату всех сотрудников в таблице:

SELECT MAX(salary) FROM employees; 

Функция MIN устроена аналогичным образом, только возвращает минимальное значение.

SUM

Возвращает сумму набора числовых значений в виде десятичного числа.

Например, для набора 1 , 2 , 3 и 4 функция SUM вернёт значение 10 . Функция SUM игнорирует значения NULL , поэтому если какое-либо из значений в наборе является NULL , оно не будет включено в расчёт.

Синтаксис функции SUM следующий:

SUM(expression) 

Где expression — столбец или выражение, содержащее числа, которые необходимо суммировать. Например, вы можете использовать следующий запрос для вычисления общей зарплаты всех сотрудников в таблице:

SELECT SUM(salary) FROM employees; 

NOW

Возвращает текущую дату и время.

Функция NOW часто используется в комбинации с другими функциями или в условиях запроса. Например, её можно использовать для вставки текущей даты и времени в таблицу или для сравнения текущей даты и времени с временной меткой, хранящейся в таблице.

Синтаксис функции NOW очень простой — функция не принимает никаких аргументов.

Например, вы можете использовать следующий запрос для вставки текущей даты и времени в столбец timestamp таблицы table :

INSERT INTO table (timestamp) VALUES (NOW()); 

SUBSTRING

Возвращает часть строки, начинающуюся с указанной позиции и имеющую заданную длину.

Она возвращает извлеченную часть строки в виде новой строки. Например, если у вас есть строка Hello, world! и вы используете функцию SUBSTRING для извлечения пяти символов, начиная с седьмого, то функция вернёт строку world .

Синтаксис функции SUBSTRING следующий:

SUBSTRING(string, start, length) 

Где string — входная строка, start — позиция, с которой начинается извлечение, а length — количество символов для извлечения. Если аргумент length опущен, функция извлечет все символы от позиции start до конца строки.

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

SELECT SUBSTRING('Hello, world!', 1, 5); 

В результате будет возвращена строка Hello .

UCASE и LCASE

Функции UCASE и LCASE в MySQL используются для преобразования строки в верхний или нижний регистр, соответственно.

Эти функции часто используются при сравнении строк, которые могли быть введены в разном регистре, или при хранении строк в едином регистре для облегчения поиска и сортировки. Функция UCASE переводит все буквы во входной строке в верхний регистр, а функция LCASE переводит все буквы в нижний регистр. Синтаксис этих функций следующий:

UCASE(string) LCASE(string) 

Где string — это входная строка, которую нужно преобразовать. Например, вы можете использовать следующий запрос для преобразования строки в верхний регистр:

SELECT UCASE('Hello, world!'); 

Это вернёт строку HELLO, WORLD! со всеми буквами в верхнем регистре. Аналогично, следующий запрос вернёт строку hello, world! со всеми буквами в нижнем регистре.

SELECT LCASE('Hello, world!'); 

CONCAT

Соединяет две или более строк в одну.

Она возвращает склеенную строку как новую строку. Например, если у вас есть строки Hello и world с пробелом в начале строки, и вы используете функцию CONCAT для их объединения, функция вернёт строку Hello world .

SELECT CONCAT('Hello', ' world'); 

Синтаксис функции CONCAT следующий:

CONCAT(string1, string2, . ) 

Где string1 , string2 и так далее — строки, которые вы хотите склеить. Функция CONCAT может принимать любое количество аргументов, и все строки будут склеены в том порядке, в котором указаны.

Соединить строки с разделителем

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

SELECT CONCAT_WS(' ', 'Hello', 'world'); 

Это также вернёт строку Hello world , но использование функции CONCAT_WS позволяет указать используемый разделитель.

Это лишь некоторые примеры из множества встроенных функций, доступных в MySQL. Существует множество других, каждая из которых имеет свое специфическое назначение и случаи использования.

Больше статей

• Что такое SQL
• Работа с MySQL в PHP
• Защита от SQL-инъекций

Эту статью нам помогли сделать нейросети. Если вы нашли в ней фактические ошибки — напишите на blog+neural@htmalcademy.ru.

«Доктайп» — журнал о фронтенде. Читайте, слушайте и учитесь с нами.

Читать дальше

Как работает протокол HTTP

HTTP был разработан в 1990-х годах для создания первого интерактивного текстового веб-браузера. За эти годы протокол менялся и совершенствовался, становился более гибким и постепенно превратился в современный интернет. В статье рассмотрим принцип работы протокола и что важно знать о нём разработчику.

• 8 февраля 2023

Подключение файлов в PHP. Метод require()

Способность вызывать сценарий из отдельного файла по его имени называется в PHP подключением файлов. Подключают PHP-сценарии, любые текстовые файлы или HTML-страницы.

• 21 ноября 2022

Массивы в PHP

Массив — это ещё один тип данных, вроде числа или строки. Главное отличие массива от остальных типов данных заключается в его способности хранить в переменной больше одного значения. В предыдущих примерах имя переменной всегда ассоциировалось только с одним значением:

• $name = "Иннокентий"
• $age = 42

А если мы хотим узнать не только пол, имя и возраст пользователя, но и, допустим, любимые сериалы? Очень непросто назвать один самый любимый сериал, а вот вспомнить несколько — намного легче. Сохранение в переменную-массив нескольких значений выглядит так:

 $fav_shows = ["game of thrones", "american horror story", "walking dead"]; 

В этом примере мы сохранили в переменной $fav_shows сразу три значения. Но сохранить эти данные — это только половина дела. Как с ними потом работать? Уже знакомый вам способ вывода переменной на экран не будет работать с массивами:

Так увидеть список любимых сериалов не получится. Дело в том, что массив — это не обычная переменная. Массив хранит не простые типы, вроде текста или чисел (их ещё называют «скалярными типами»), а более сложную структуру данных, поэтому здесь нужен особый подход.

Внутри массива у каждого значения есть адрес, по которому к нему можно обратиться. Такой адрес называется индексом. Индекс — это просто порядковый номер значения внутри массива. Индексация начинается с нуля, так что первый элемент получает индекс — 0 , второй — 1 , и так далее.

Чтобы получить определенный элемент массива, необходимо знать его индекс (ключ). Напечатаем названия всех сериалов из массива через запятую:

Теперь можно дать определение массива: Массив — это совокупность множества элементов вида «ключ: значение».

Массивы позволяют перезаписывать существующие значения и добавлять новые. Добавить новое значение в массив можно так:

$fav_shows[] = "the big bang theory"; 

Новый элемент автоматически получит индекс равный максимальному индексу из существующих + 1. «Теория большого взрыва» сохранится в массиве под индексом 3 .

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

$fav_shows[4] = "fargo"; 

Для полного удаления (без замены на другое) значения по его индексу существует функция unset :

unset($fav_shows[4]); 

• 10 ноября 2022

Синтаксис PHP

Разберёмся, из чего состоит любой язык программирования.

У каждого языка есть правила и конструкции, следуя которым мы выражаем мысли и делаем их понятными для другого человека. В программировании всё точно так же. Но вместо человеческого языка мы используем язык программирования PHP, а в роли нашего собеседника выступает PHP-интерпретатор. Поэтому, чтобы выразить свою мысль, мы должны сделать её понятной для интерпретатора.

• 27 октября 2022

Массивы $_POST и $_GET в PHP. Обработка форм

Формы — это часть языка HTML. Формы нужны для передачи данных от клиента на сервер. Чаще всего формы используются для регистрации пользователей, заполнения анкет, оформления заказа в интернет магазине, и так далее.

Через формы можно отправлять как простую текстовую информацию, так и файлы.

Большую часть времени программирования на PHP вы будете так или иначе работать с формами и данными из них.

HTML описывает то, из каких элементов состоит форма, и как она выглядит. Но без принимающей стороны, то есть сервера, который принимает эти данные и обрабатывает их нужным образом, создавать формы нет никакого смысла.

PHP содержит множество средств для работы с формами. Это позволяет очень просто решать типичные задачи, которые часто возникают в веб-программировании:

• Регистрация и аутентификация пользователя;
• Отправка комментариев на форумах и социальных сетях;
• Оформление заказов.

Практически любой современный сайт содержит как минимум несколько разных HTML-форм.

• 20 октября 2022

Учебник по PHP

1. Знакомство с языком
   • Что такое PHP
   • Синтаксис PHP
   • Массивы
   • Циклы
   • Функции
2. Шаблонизация и подключение файлов
   • Подключение файлов
   • Шаблонизация
3. Протокол HTTP и формы
   • Протокол HTTP
   • Формы
   • Уязвимости
4. Идентификация пользователя на сайте
   • Аутентификация пользователя
5. Базы данных
   • База данных
   • SQL
   • MySQL в PHP
   • Безопасность в MySQL
6. Объекты и использование библиотек
   • Объекты
   • Библиотеки
   • Composer

• 10 сентября 2022

Протокол HTTP и работа с заголовками

Весь современный веб построен на модели взаимодействия клиента и сервера. Как она работает:

• браузер пользователя (клиент) отправляет на сервер запрос с адресом сайта (URL);
• сервер получает запрос и отдаёт клиенту запрошенный контент.

Для реализации процесса используется универсальный протокол HTTP.

• 10 сентября 2022

Защита от SQL-инъекций

Внедрение SQL-кода (SQL инъекция) — один из распространённых способов взлома сайтов, работающих с базами данных. Способ основан на внедрении в запрос произвольного SQL-кода. Внедрение SQL позволяет хакеру выполнить произвольный запрос к базе данных (прочитать содержимое любых таблиц, удалить, изменить или добавить данные).

Атака этого типа возможна, когда недостаточно фильтруются входные данные при использовании в SQL-запросах.

• 10 сентября 2022

Объекты и классы в PHP

Объекты в PHP — это просто ещё один тип данных. Объект позволяет хранить в переменной набор из свойств и их значений, а также встроенные функции. Это делает объекты похожими по своей структуре на ассоциативные массивы. Но отличие от массивов всё-таки есть, и при этом достаточно важное — объекты могут иметь внутреннее состояние.

• 10 сентября 2022

Циклы в PHP. Краткое руководство

Цикл — это конструкция языка, которая выполняет блок кода больше одного раза.

Мы привыкли, что сценарии выполняются линейно: сверху вниз, строчка за строчкой. Но что делать, если надо повторить какую-нибудь инструкцию несколько раз? Например, как вывести на экран натуральные числа от 1 до 9?

Есть очевидный способ:

Но он заставляет писать много кода. И что если требуется вывести последовательность из миллиона чисел? Ещё бывают ситуации, когда заранее неизвестно сколько раз нужно выполнить определённую инструкцию.

Использование циклов значительно упрощает и укорачивает код. Циклы незаменимы в ситуациях, когда заранее неизвестно сколько раз должен выполниться блок кода. Такое число зависит от множества условий и вычисляется в момент выполнения сценария.

Так выглядит цикл в PHP:

• 10 сентября 2022