SELECT FirstName, LastName FROM Students;

SELECT * FROM Students;

SELECT FirstName, LastName FROM Students;

SELECT LastName, FirstName, BirthDate FROM Students;

SELECT
    FirstName AS "Ім'я",
    LastName AS Прізвище,
    BirthDate AS ДатаНародження
FROM Students;

SELECT s.FirstName, s.LastName
FROM Students AS s;
--             ^^^^
--             Псевдонім таблиці

SELECT
    FirstName + ' ' + LastName AS FullName
FROM Students;

SELECT
    'Student: ' + LastName + ', ' + FirstName + ' (ID: ' + CAST(Id AS NVARCHAR) + ')' AS StudentInfo
FROM Students;

Student: Петренко, Іван (ID: 1)
Student: Коваленко, Марія (ID: 2)

SELECT
    Grants,
    Grants * 1.1 AS GrantsWithIncrease,
    Grants * 12 AS YearlyGrants
FROM Students
WHERE Grants IS NOT NULL;

SELECT
    Id,
    CAST(Id AS NVARCHAR(10)) AS IdAsString,
    CAST(Grants AS INT) AS GrantsRounded
FROM Students;

SELECT
    BirthDate,
    CONVERT(NVARCHAR, BirthDate, 104) AS DateGerman,  -- DD.MM.YYYY
    CONVERT(NVARCHAR, BirthDate, 103) AS DateBritish, -- DD/MM/YYYY
    CONVERT(NVARCHAR, BirthDate, 101) AS DateUSA      -- MM/DD/YYYY
FROM Students;

SELECT column1, column2
FROM table_name
WHERE condition;

-- Студенти народжені в 1998 році
SELECT * FROM Students
WHERE YEAR(BirthDate) = 1998;

-- Студенти зі стипендією більше 1400
SELECT FirstName, LastName, Grants
FROM Students
WHERE Grants > 1400;

-- Конкретний студент за прізвищем
SELECT * FROM Students
WHERE LastName = 'Петренко';

-- Студенти народжені в 1998 році ЗІ стипендією
SELECT * FROM Students
WHERE YEAR(BirthDate) = 1998 AND Grants IS NOT NULL;

-- Студенти народжені в 1997 АБО 1999 році
SELECT * FROM Students
WHERE YEAR(BirthDate) = 1997 OR YEAR(BirthDate) = 1999;

-- Студенти НЕ народжені в 1998 році
SELECT * FROM Students
WHERE NOT YEAR(BirthDate) = 1998;

-- Еквівалентно:
WHERE YEAR(BirthDate) <> 1998;

-- Хто народився в 1998 ЗІ стипендією > 1300 АБО будь-хто з 1997
SELECT * FROM Students
WHERE YEAR(BirthDate) = 1998 AND Grants > 1300 OR YEAR(BirthDate) = 1997;

-- Логіка: (1998 AND Grants>1300) OR (1997)
--         ^^^^^^^^^^^^^^^^^^^^^^     ^^^^^
--               Група 1             Група 2

SELECT * FROM Students
WHERE (YEAR(BirthDate) = 1998 AND Grants > 1300)
   OR YEAR(BirthDate) = 1997;

-- Студенти які:
-- - Народилися в 1997-1998 І мають стипендію > 1400
-- АБО
-- - Народилися в 1999 І НЕ мають Email
SELECT * FROM Students
WHERE (
        (YEAR(BirthDate) IN (1997, 1998) AND Grants > 1400)
        OR
        (YEAR(BirthDate) = 1999 AND Email IS NULL)
      );

-- Студенти БЕЗ Email
SELECT * FROM Students
WHERE Email IS NULL;

-- Студенти З Email
SELECT * FROM Students
WHERE Email IS NOT NULL;

SELECT
    Grants,
    Grants + 100 AS GrantsPlusBonus
FROM Students;

-- Якщо Grants = NULL, то GrantsPlusBonus = NULL (не 100!)

SELECT
    Grants,
    ISNULL(Grants, 0) + 100 AS GrantsPlusBonus
FROM Students;
-- Якщо Grants = NULL, то ISNULL поверне 0, результат: 0 + 100 = 100

SELECT columns
FROM table
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC];

SELECT FirstName, Email
FROM Students
ORDER BY Email ASC;

-- Порядок результату:
-- 1. Студенти з Email = NULL (спочатку)
-- 2. Студенти з Email (відсортовані А→Я)

SELECT FirstName, LastName, BirthDate
FROM Students
ORDER BY 2, 1;  -- 2 = LastName, 1 = FirstName

SELECT TOP (n) columns
FROM table
[ORDER BY column];

-- 5 найстаріших студентів
SELECT TOP (5) FirstName, LastName, BirthDate
FROM Students
ORDER BY BirthDate ASC;

-- 3 студенти з найбільшою стипендією
SELECT TOP (3) FirstName, Grants
FROM Students
ORDER BY Grants DESC;

-- 10% студентів з найвищою стипендією
SELECT TOP (10) PERCENT FirstName, Grants
FROM Students
ORDER BY Grants DESC;

-- Топ 3, але включити всіх з однаковою стипендією
SELECT TOP (3) WITH TIES FirstName, Grants
FROM Students
ORDER BY Grants DESC;

SELECT DISTINCT column1, column2
FROM table;

-- Унікальні роки народження
SELECT DISTINCT YEAR(BirthDate) AS BirthYear
FROM Students
ORDER BY BirthYear;

-- Результат: 1997, 1998, 1999 (без повторень)

SELECT YEAR(BirthDate) AS BirthYear
FROM Students;

-- Результат: 1998, 1997, 1999, 1998, 1997, 1998, 1999... (з повтореннями)

SELECT DISTINCT LastName, FirstName
FROM Students;

-- Унікальні комбінації (LastName, FirstName)

-- Скільки різних років народження?
SELECT COUNT(DISTINCT YEAR(BirthDate)) AS UniqueYears
FROM Students;

-- Результат: 3 (1997, 1998, 1999)

SELECT
    LastName + ' ' + FirstName AS FullName,
    Grants AS GrantAmount,
    Grants * 12 AS YearlyAmount
FROM Students
WHERE Grants IS NOT NULL
ORDER BY Grants DESC;

SELECT
    Id,
    FirstName,
    LastName,
    'No email provided' AS EmailStatus
FROM Students
WHERE Email IS NULL;

SELECT TOP (5)
    FirstName,
    LastName,
    BirthDate,
    YEAR(GETDATE()) - YEAR(BirthDate) AS ApproximateAge
FROM Students
ORDER BY BirthDate DESC;

SELECT DISTINCT
    MONTH(BirthDate) AS BirthMonth,
    DATENAME(MONTH, BirthDate) AS MonthName
FROM Students
ORDER BY BirthMonth;