MySQL - CVIKO 8

USE sakila;

SELECT title AS nazov, length AS dlzka FROM film WHERE length > 150 ORDER BY dlzka; -- First task --

SELECT F.title, C.name FROM film AS F, film_category AS FC, category AS C WHERE F.film_id = FC.film_id AND FC.category_id = C.category_id; -- Second task --

SELECT F.title, A.first_name, A.last_name FROM film as F, film_actor as FA, actor AS A WHERE F.film_id = FA.film_id AND FA.actor_id = A.actor_id ORDER BY title; -- Third task --

/*
ORDER BY title - addon
*/

SELECT CONCAT(first_name, ' ', last_name) AS 'celemeno' FROM actor; -- Fourth task --

SELECT CONCAT(first_name, ' ',  last_name) AS 'celeMeno', address, city, country FROM customer, address, city, country WHERE customer.address_id = address.address_id AND address.city_id = city.city_id AND city.country_id = country.country_id;

USE cv;

SELECT * FROM cv.Student;

SELECT * FROM cv.Zapis;

SELECT meno, priemer, (4-priemer)*100 as bodyInternat FROM Student;

USE sakila;

SELECT * FROM actor AS A, actor AS B WHERE A.last_name = B.first_name;

SELECT * FROM actor AS A, actor AS B WHERE A.last_name = B.last_name and A.actor_id < B.actor_id;

USE cv;

SELECT id, meno FROM Student WHERE odbor = 'API' UNION ALL SELECT id, meno FROM Student WHERE odbor = 'RK' ORDER BY meno;

USE cv;

SELECT meno FROM Student WHERE odbor = 'API' OR odbor = 'RK';

SELECT A.meno FROM Student AS A, Student as B WHERE A.meno = B.meno AND A.odbor = 'API' AND B.odbor = 'RK';

SELECT id, meno, rok FROM Student, Zapis WHERE id = studentID AND predmet = 'DBS';

SELECT id, meno FROM Student WHERE id IN (SELECT studentID FROM Zapis WHERE predmet = 'DBS';

SELECT id, meno FROM Student WHERE id IN (SELECT studentID FROM Zapis WHERE predmet = 'MAT1') AND id NOT IN (SELECT studentID FROM Zapis WHERE predmet = 'MAT2');

SELECT skratka FROM predmet AS P1 WHERE NOT EXISTS (SELECT * FROM Predmet AS P2 WHERE P1.skratka <> P2.skratka AND P1.ustav = P2.ustav);

SELECT meno, priemer FROM Student AS S1 WHERE NOT EXISTS (SELECT priemer FROM Student AS S2 WHERE S2.priemer > S1.priemer);

SELECT meno, priemer FROM Student WHERE priemer >= ALL(SELECT priemer FROM Student);

SELECT meno, priemer FROM Student WHERE NOT priemer < ANY(SELECT priemer FROM Student);

SELECT meno, priemer FROM Student WHERE priemer > ANY(SELECT priemer FROM Student);