SQL Statements.sql

--SQL Statements--
--a) 
SELECT `filmID` 
FROM `film` 
WHERE `origTitle` LIKE '%love%'
--b) 
SELECT `filmID` 
FROM `film` 
WHERE `origTitle` LIKE '%love%'
AND `germanTitle` LIKE '%Liebe%'
--c) 
SELECT * 
FROM `film` 
WHERE `origTitle` LIKE '%The_Ring%'
OR `origTitle` LIKE '%Ring, The%'
OR `germanTitle` LIKE '%The_Ring%'
--d) 
SELECT * 
FROM (
 SELECT `year` , MAX( `visited` ) AS `visited` 
 FROM `film` 
 WHERE `year` <=2000
 AND `year` >=1990
 GROUP BY `year`
)t1
INNER JOIN `film` t2 ON t2.`year` = t1.`year` 
AND t1.`visited` = t2.`visited` 
ORDER BY t2.`year` DESC
--e) 
SELECT SUM( `visited` ) 
FROM `film` 
--f) 
SELECT * 
FROM `film` 
WHERE `germanTitle` IS NULL
--g) 
SELECT * 
FROM `film` 
WHERE `germanPremiere` < `premiere`
--h) 
SELECT * 
FROM `person` 
WHERE `firstname` LIKE 'J%'
--i) 
SELECT COUNT( * ) AS `n` , `nationality` 
FROM `akas` 
GROUP BY `nationality` 
ORDER BY `n` DESC
--j) 
SELECT * 
FROM `film` 
WHERE `nationality` LIKE '%jp%'
--k) 
SELECT COUNT( * ) AS `n` , `nationality` 
FROM `film` 
WHERE `nationality` = 'de'
OR `nationality` = 'fr'
GROUP BY `nationality`
--l) 
SELECT COUNT( * ) , `year` 
FROM `film` 
WHERE `year` <=2010
AND `year` >=1990
GROUP BY `year`
--m) 
SELECT `akas`.`titel` 
FROM `akas` , `film` 
WHERE `akas`.`filmFID` = `film`.`filmID` 
AND `film`.`germanTitle` = 'Herr der Ringe, Der: Die Gefährten'
AND `akas`.`nationality` = 'fr'
--n) 
SELECT COUNT( * ) AS `n` , SUBSTR( `germanTitle` , 1, 1 ) AS `fchar` 
FROM `film` 
GROUP BY `fchar` 
ORDER BY `n` DESC
--o) 
SELECT COUNT( * ) AS `n` , SUBSTR( `germanTitle` , 1, 1 ) AS `fchar` 
FROM `film` 
GROUP BY `fchar` 
ORDER BY `n` DESC 
LIMIT 1