CVIKO 12 SQL

use cv;
-- A1 a) 69
SELECT COUNT(*) FROM Zapis;
-- A1 b) 63
SELECT COUNT(*) FROM Zapis WHERE body >= 56;
-- A1 c) 5
SELECT COUNT(*) FROM Zapis WHERE body < 56;
-- A1 d) 1
SELECT COUNT(*) FROM Zapis WHERE body IS NULL;
-- A1 e) 68
SELECT COUNT(*) FROM Zapis WHERE body >= 56 OR body < 56;
-- A1 f)
SELECT COUNT(*) FROM Zapis WHERE body IS NOT NULL;
-- B1
SELECT CONCAT(SQRT(POW(3, 2) +4 * 4), 'cm');
-- B3
SELECT NOW(); -- vypise aktualny cas a datum;
-- B4
SELECT DATE(NOW()); -- Samotny datum;
-- B6
SELECT LENGTH('Hello');
-- C1
use sakila;

SELECT title, length FROM film WHERE length BETWEEN 100 AND 150 ORDER BY length;
-- D1
SELECT title FROM film WHERE title LIKE '%lion%'; -- gde kolvek nech sa nachadza lion on ho vypise;
-- D2
SELECT title FROM film WHERE title LIKE 'a%'; -- Prve pismeno z filma je A;
-- D3
SELECT title FROM film WHERE title LIKE '%a'; -- posledne pismeno  je a tak ho vypise;
-- D4
SELECT title FROM film WHERE title LIKE '_a%';
-- E1
use cv;
SELECT *,
CASE nastup
    WHEN 2017 THEN 'prvak'
    WHEN 2016 THEN 'druhak'
    WHEN 2015 THEN 'tretiak'
    ELSE '?'
END AS rocnik FROM Student;
-- E2
SELECT *,
CASE
    WHEN body >= 92 THEN 'Presiel predmet!'
    WHEN body > 56 THEN 'Presiel predmet!'
    WHEN body = 56 THEN 'Presiel, nevzdelaval'
    ELSE 'Treba ziskat dalsie body'
END AS Komentar FROM Zapis; -- Treba ukladat podla podmienok, on prvu podmienku vypisie.
-- F1
SELECT Student.*, predmet FROM Student, Zapis WHERE id = studentID;
-- Vytvaranie view:
-- G1
use xrener1;
CREATE VIEW StudentBody AS SELECT *, round((4 - priemer) * 100) AS body FROM Student;

SELECT * FROM StudentBody;

DROP VIEW StudentBody;
-- G2
CREATE VIEW UspesnostPredmetu AS
SELECT * FROM Predmet.*, COUNT(studentID) AS Zapisov, COUNT(DISTINCT studentID) AS Studentov, (SELECT COUNT(body) FROM Zapis WHERE Zapis.predmet = Predmet.skratka AND body >= 56) AS Absolvovani LEFT JOIN Zapis ON skratka = predmet GROUP BY skratka; -- DISTINCT bez opakuvania prikaz.
SELECT * FROM UspesnostPredmetu;
DROP VIEW UspesnostPredmetu;
-- H1
CREATE TABLE ProgJazyk(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    nazov VARCHAR(20),
    PRIMARY KEY (id)
);
-- H2
CREATE TABLE StudentJazyk(
    studentID INT UNSIGNED NOT NULL,
    jazykID INT UNSIGNED NOT NULL,
    PRIMARY KEY (studentID, jazykID),
    FOREIGN KEY (studentID) REFERENCES Student(id),
    FOREIGN KEY (jazykID) REFERENCES ProgJazyk(id)
);
-- I1
INSERT INTO ProgJazyk (nazov) VALUE ('C');
-- I2
INSERT INTO ProgJazyk (nazov) VALUES ('C++'), ('Java'), ('Python'), ('SQL');
-- I3
INSERT INTO StudentJazyk (studentID, jazykID) VALUES (20, 1), (20, 2);
-- I4
INSERT INTO StudentJazyk (studentID, jazykID) SELECT StudentID, (SELECT id FROM ProgJazyk WHERE nazov = 'SQL') FROM Zapis WHERE predmet = 'DBS' AND body >= 56;
-- J1
UPDATE Predmet SET kapacita = 100;
-- J2
UPDATE Predmet SET kapacita = 200 WHERE skratka = 'DBS';
-- J3
UPDATE Zapis SET znamka = CASE
    WHEN body >= 92 THEN 'A'
    WHEN body >= 83 THEN 'B'
    WHEN body >= 74 THEN 'C'
    WHEN body >= 65 THEN 'D'
    WHEN body >= 56 THEN 'E'
    ELSE 'FX'
END
WHERE body IS NOT NULL;
-- J4 49
DELETE FROM Zapis WHERE predmet = 'BEZ';
-- L1
ALTER TABLE Zapis ADD COLUMN aktualizacia DATE;
-- L2
ALTER TABLE Zapis MODIFY COLUMN aktualizacia TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
-- L3
ALTER TABLE Zapis DROP COLUMN aktualizacia;