Podstawy SQL
– ćwiczenia

SELECT imie, nazwisko, klasa
FROM   uczniowie
ORDER BY nazwisko ASC;

SELECT imie, nazwisko, email
FROM   uczniowie
WHERE  klasa = '3TI'
ORDER BY nazwisko;

SELECT id_ucznia, id_przedm, ocena, data_wystawienia
FROM   oceny
WHERE  ocena IN (5, 6)
ORDER BY ocena DESC, data_wystawienia;

SELECT imie, nazwisko, klasa
FROM   uczniowie
WHERE  nazwisko LIKE '%ski';

SELECT   klasa, COUNT(*) AS liczba_uczniow
FROM     uczniowie
GROUP BY klasa
ORDER BY klasa;

SELECT ROUND(AVG(ocena), 2) AS srednia
FROM   oceny
WHERE  id_przedm = 1;

SELECT
  u.imie, u.nazwisko,
  p.nazwa  AS przedmiot,
  o.ocena
FROM       oceny o
INNER JOIN uczniowie  u ON o.id_ucznia = u.id_ucznia
INNER JOIN przedmioty p ON o.id_przedm = p.id_przedm
WHERE      o.id_ucznia = 1
ORDER BY   o.ocena DESC;

SELECT
  u.imie, u.nazwisko,
  ROUND(AVG(o.ocena), 2) AS srednia
FROM       oceny      o
INNER JOIN uczniowie  u ON o.id_ucznia = u.id_ucznia
GROUP BY   u.id_ucznia, u.imie, u.nazwisko
HAVING     AVG(o.ocena) > 4
ORDER BY   srednia DESC;

SELECT
  p.nazwa,
  MAX(o.ocena)              AS najlepsza,
  MIN(o.ocena)              AS najgorsza,
  ROUND(AVG(o.ocena), 2)  AS srednia
FROM       oceny      o
INNER JOIN przedmioty p ON o.id_przedm = p.id_przedm
GROUP BY   p.id_przedm, p.nazwa
ORDER BY   srednia DESC;

SELECT id_ucznia, id_przedm, ocena, data_wystawienia
FROM   oceny
WHERE  data_wystawienia BETWEEN '2024-10-01' AND '2024-10-31'
ORDER BY data_wystawienia;

SELECT imie, nazwisko, klasa
FROM   uczniowie
WHERE  id_ucznia NOT IN (
  SELECT DISTINCT id_ucznia
  FROM            oceny
);

SELECT
  CONCAT(imie, ' ', nazwisko) AS pelne_imie,
  klasa,
  email
FROM     uczniowie
ORDER BY nazwisko;

INSERT INTO uczniowie (imie, nazwisko, klasa, email, data_ur)
VALUES ('Marek', 'Jabłoński', '4TI', 'm.jab@szkola.pl', '2006-09-03');

-- Sprawdzenie:
SELECT * FROM uczniowie WHERE nazwisko = 'Jabłoński';

INSERT INTO oceny (id_ucznia, id_przedm, ocena) VALUES
  (9, 2, 5),  -- Informatyka
  (9, 1, 4),  -- Matematyka
  (9, 5, 3);  -- Fizyka

-- Sprawdzenie nowych ocen:
SELECT o.ocena, p.nazwa
FROM       oceny      o
INNER JOIN przedmioty p ON o.id_przedm = p.id_przedm
WHERE      o.id_ucznia = 9;

INSERT INTO przedmioty (nazwa, nauczyciel, sala)
VALUES ('Bazy danych', 'Anna Wiśniewska', 'lab1');

-- Sprawdź jaki ID dostał nowy przedmiot:
SELECT LAST_INSERT_ID() AS nowe_id;

INSERT INTO oceny (id_ucznia, id_przedm, ocena)
SELECT u.id_ucznia, 2, 5
FROM   uczniowie u
WHERE  u.klasa = '3TI'
  AND  u.id_ucznia NOT IN (
    SELECT id_ucznia FROM oceny
    WHERE  id_przedm = 2
  );

-- Zawsze sprawdź co zmienisz!
SELECT id_ucznia, imie, nazwisko, klasa
FROM   uczniowie
WHERE  imie = 'Adam' AND nazwisko = 'Nowak';

-- Aktualizacja:
UPDATE uczniowie
SET    klasa = '4TI'
WHERE  id_ucznia = 1;

UPDATE oceny
SET
  ocena            = 3,
  data_wystawienia = CURDATE()
WHERE
  id_ucznia = 3   -- Piotr Wiśniewski
  AND id_przedm = 5;  -- Fizyka

UPDATE przedmioty
SET    sala = 'lab3'
WHERE  sala = 'lab2';

-- Sprawdź zmiany:
SELECT nazwa, nauczyciel, sala
FROM   przedmioty
WHERE  sala = 'lab3';

UPDATE oceny
SET    ocena = 2
WHERE  ocena = 1
  AND  id_ucznia IN (
    SELECT id_ucznia
    FROM   uczniowie
    WHERE  klasa = '3TI'
  );

-- Najpierw SELECT, żeby sprawdzić co usuniesz:
SELECT * FROM oceny
WHERE  id_ucznia = 5 AND id_przedm = 1;

-- Usunięcie:
DELETE FROM oceny
WHERE       id_ucznia = 5
  AND       id_przedm = 1;

-- Krok 1: Usuń oceny (klucz obcy!)
DELETE FROM oceny
WHERE       id_ucznia = 9;

-- Krok 2: Usuń ucznia
DELETE FROM uczniowie
WHERE       id_ucznia = 9;

-- Sprawdź ile rekordów dotyczy:
SELECT COUNT(*) AS do_usuniecia
FROM   oceny
WHERE  data_wystawienia = '2024-09-15';

-- Usuń:
DELETE FROM oceny
WHERE       data_wystawienia = '2024-09-15';

DELETE FROM oceny
WHERE       id_ucznia IN (
  SELECT id_ucznia
  FROM   uczniowie
  WHERE  klasa = '3TI'
);