SELECT
AVG(value) AS avg_value,
CASE WHEN LOWER(TO_CHAR(created_date, 'Day')) = 'monday' THEN 'Понедельник'
WHEN LOWER(TO_CHAR(created_date, 'Day')) = 'tuesday' THEN 'Вторник'
WHEN LOWER(TO_CHAR(created_date, 'Day')) = 'wednesday' THEN 'Среда'
WHEN LOWER(TO_CHAR(created_date, 'Day')) = 'thursday' THEN 'Четверг'
WHEN LOWER(TO_CHAR(created_date, 'Day')) = 'friday' THEN 'Пятница'
WHEN LOWER(TO_CHAR(created_date, 'Day')) = 'saturday' THEN 'Суббота'
WHEN LOWER(TO_CHAR(created_date, 'Day')) = 'sunday' THEN 'Воскресенье'
END week
FROM results
GROUP BY created_date;
where (item, location, action_date) IN
SELECT FieldA,
GROUP_CONCAT(FieldB ORDER BY FieldB SEPARATOR ',') AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;
SELECT FieldA,
STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;
SELECT FieldA,
LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
FROM TableName
GROUP BY FieldA
ORDER BY FieldA;
SELECT * FROM FamilyMembers
WHERE member_name LIKE '% Quincey'
ORDER BY status, member_name;
--С помощью регулярного выражения
select REGEXP_REPLACE('ДГН-04-13601/21-53', '-\d{1,}/', '-********/') from dual;
--Если строка всегда одной длины, то можно с помощью substr
select substr('ДГН-04-13601/21-53', 0, 7) || '********' || substr('ДГН-04-13601/21-53', 13) from dual;
Select ФИО
From Список_специалистов
INNER JOIN Журнал_учёта_работ ON Журнал_учёта_работ.id_Специалиста = Список_специалистов.id
SELECT E.FIRST_NAME,
E.LAST_NAME,
L.STREET_ADDRESS
FROM HR.EMPLOYEES E
LEFT JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
LEFT JOIN LOCATIONS L ON L.LOCATION_ID = D.LOCATION_ID;
SELECT E.FIRST_NAME,
E.LAST_NAME,
L.STREET_ADDRESS
FROM HR.EMPLOYEES E
LEFT JOIN DEPARTMENTS D USING (DEPARTMENT_ID)
LEFT JOIN LOCATIONS L USING (LOCATION_ID);
select sum(rashody)/count(distinct data_prikaza_) over(partition by gosb) from erp_trips
INSERT INTO MyTABLE (Name, CapitalCity, LargestCity, Admission, Population)
select Name, CapitalCity, LargestCity, Admission, Population from OpenJson('
[
{
"name": "Alabama",
"capitalCity": "Montgomery",
"largestCity": "Birmingham",
"admission": "Dec 14, 1819",
"population": "4,903,185"
}, ...
')
with (
Name nvarchar(20) '$.name',
CapitalCity nvarchar(30) '$.capitalCity',
LargestCity nvarchar(20) '$.largestCity',
Admission nvarchar(20) '$.admission',
Population nvarchar(20) '$.population'
)