----1.	Find the names of all stars which starred 
---in at least 3 movies (according to our database)
SELECT star_name, count (*)
FROM starsin
GROUP BY star_name
HAVING count (*) >=3;

---2. Find the stars who have worked for only one studio.
SELECT star_name, count(distinct studio)
FROM movie m, starsin s
WHERE m.title = s.movie_title AND m.year = s.movie_year
GROUP BY star_name
HAVING count(distinct studio)=1;

---3. Find the stars who have worked for Disney but no other studio.
SELECT star_name FROM starsin s, movie m
WHERE s.movie_title = m.title AND s.movie_year = m.year AND studio = 'Disney'
EXCEPT
SELECT star_name FROM starsin s, movie m
WHERE s.movie_title = m.title AND s.movie_year = m.year AND studio IS NOT NULL AND studio <> 'Disney';

---4. Find the shortest movie(s) of Disney
SELECT * from movie 
where studio='Disney';

SELECT title, year, length from movie 
where studio='Disney'
AND length = (SELECT MIN(length) FROM movie where studio='Disney');

---5. For each star that has at least two movies with Paramount, 
---find how many movies he/she has with Fox.
SELECT s.star_name, count (s.movie_title) 
FROM starsin s, movie m	
WHERE s.movie_title = m.title AND s.movie_year = m.year
AND m.studio = 'Paramount'
GROUP BY s.star_name
HAVING count (s.movie_title)>=2;

SELECT s1.star_name
FROM starsin s1, movie m1	
WHERE s1.movie_title = m1.title AND s1.movie_year = m.year
AND m1.studio = 'Paramount'
GROUP BY s1.star_name
HAVING count (s1.movie_title)>=2;

SELECT s.star_name as star, count (s.movie_title) as foxcount
FROM starsin s, movie m
WHERE s.movie_title = m.title AND s.movie_year = m.year
AND m.studio = 'Fox' 
AND s.star_name IN
(SELECT s1.star_name
FROM starsin s1, movie m1	
WHERE s1.movie_title = m1.title AND s1.movie_year = m.year
AND m1.studio = 'Paramount'
GROUP BY s1.star_name
HAVING count (s1.movie_title)>=2)
GROUP BY s.star_name;


---6. Find the stars who have worked for every studio. 
SELECT title, year, studio, star_name
	FROM Movie, StarsIn 
	WHERE Movie.title = StarsIn.movie_title and Movie.Year = Starsin.Movie_year;
	
SELECT DISTINCT star_name
	FROM StarsIn;

SELECT DISTINCT star_name
FROM StarsIn X
WHERE NOT EXISTS (
	 SELECT name
	 FROM Studio
		EXCEPT
	 SELECT studio
	 FROM (SELECT title, year, studio, star_name
			FROM Movie, StarsIn 
			WHERE Movie.title = StarsIn.movie_title 
			and Movie.Year = Starsin.Movie_year AND
			star_name = X.star_name)
			);

---7. Suppose StarsIn relation has an additional attribute “salary”
---StarsIn(movieTitle, movieYear, starName, salary)
---Now, find the stars who were paid for some movie 
---more than the average salary for that movie. 
SELECT star_name, movie_title, movie_year
FROM StarsIn X
WHERE salary >
(SELECT AVG(salary)
FROM StarsIn
);

SELECT X.star_name, X.movie_title, X.movie_year
	FROM StarsIn X, (SELECT movie_title, movie_year, AVG(salary) AS avgSalary
			      FROM StarsIn
			      GROUP BY movie_title, movie_year) Y 
	WHERE X.salary>Y.avg_salary AND 
	X.movie_title=Y.movie_title 
      AND X.movie_year=Y.movie_year;




