Company(cname varchar(30) primary key, country varchar(30));
Product(pname varchar(30) primary key, price float, category varchar(30), manufacturer varchar(30)
references Company);
Sales(pname varchar(30) references Product, sold int, month varchar(10), primary key (pname, month));
Employees(empID integer primary key, name varchar(50), phone varchar(12), managerID integer);
Projects(empID integer, project varchar(40));
Q5) List all employee names and the projects that each is involved in. An employee may
appear multiple times in the result if they are involved in multiple projects. Order the
result alphabetically by employee name, and secondarily by project name.
SELECT e.name, p.project
FROM Employees e, Projects p
WHERE e.empID = p.empID
ORDER BY e.name, p.project; Q6) Return a list of all employee names who work on both the ‘Web archive’, and ‘Phone
app’ projects. Order the results alphabetically by employee name.
SELECT e.name
FROM Employees e, Projects p1, Projects p2
WHERE e.empid = p1.empid AND e.empid = p2.empid AND p1.project = 'Web archive' AND
p2.project = 'Phone app'
ORDER BY e.name; Q7) Retrieve the average price of all products. Your query should return a single value
SELECT avg(price)
FROM Product; Q8) Retrieve the average price for each category of products. Your query should return
a list of categories and the average price of all products within each category
SELECT category, avg(price)
FROM Product
GROUP BY category; Q9) Compute the average price of all products cheaper than $150, for each category of products.
SELECT category, avg(price)
FROM Product
WHERE price < 150 GROUP BY category; Q10) Retrieve the list of countries with companies that have sold products during the
month of ‘June’. Make sure that the same country does not appear multiple times on your list.
SELECT DISTINCT c.country
FROM Company c, Product p, Sales s
WHERE p.manufacturer = c.cname AND p.pname = s.pname AND s.month = 'June'; Q11) Return a list of names of all employees who work on two (or more) distinct projects. != not equal to
SELECT DISTINCT e.name
FROM Employees e, Projects p1, Projects p2
WHERE e.empid = p1.empid AND e.empid = p2.empid AND p1.project != p2.project; Q12) During which months of the year have companies from Korea sold products? Your
query should return a list of the appropriate months.
SELECT DISTINCT s.month
FROM Sales s, Product p, Company c
WHERE s.pname = p.pname AND p.manufacturer = c.cname AND c.country = 'Korea'; Q13) Return a list of all employee names and the names of their managers. The first
column in your answer should give the name of the employee, and the second column
the name of the corresponding manager. Do not omit any employees even if they have no manager.
SELECT e1.name, e2.name AS manager
FROM Employees e1 LEFT OUTER JOIN Employees e2 ON e1.managerid = e2.empid; Q14) Compute the total number of sales for each product, only for those products that
have sold more than 2 items in total. In your result, the first column should have the
product name, and the second column should list the number of sold items for that product.
SELECT pname, sum(sold)
FROM sales
GROUP BY pname
HAVING sum(sold)>2; Q15) For each country, compute how many ‘Gadgets’ products it produces. In your
result, the first column should have the name of the country, and the second column the
number of Gadgets products for that country.
SELECT country, count(*)
FROM product, company
WHERE cname = manufacturer AND category = 'Gadgets'
GROUP BY country; Q16) For each category, compute how many countries produce products in that category.
In your result, the first column should have the category, and the second column should
have the appropriate number.
SELECT p.category, count(distinct c.country)
FROM Product p, Company c
WHERE p.manufacturer = c.cname
GROUP BY p.category; Q17) For each project, list how many employees are assigned to it. In your result, the
first column should list all projects, and the second column should have the appropriate numbers.
SELECT p.project, count(e.name)
FROM employees e RIGHT OUTER JOIN projects p ON e.empid = p.empid
GROUP BY p.project; Q18) For each product, find the month during which the product had the most sales out
of the year. For example, if out of all the months of the year, Gizmo had the most sales
in February, then the tuple (Gizmo, February) should be in the result.
SELECT s1.pname, s1.month
FROM sales s1
WHERE s1.sold = (SELECT max(s2.sold)
FROM sales s2
WHERE s2.pname = s1.pname); actor (id, fname, lname, gender)
movie (id, name, year)
directors (id, fname, lname)
casts (pid, mid, role)
movie_directors (did, mid)
genre (mid, genre)
• actor.id, movie.id, and director.id are primary keys for the corresponding tables
• casts.pid is a foreign key to actor.id
• casts.mid is a foreign key to movie.id
• movie directors.did is a foreign key to directors.id
• movie directors.mid is a foreign key to movie.id
• genre.mid is a foreign key to movie.id
Q1) Return the names of movies directed by Frank Darabont. Eliminate any duplicates
in the result.
select distinct m.name
from directors d, movie_directors md, movie m
where d.id=md.did and md.mid=m.id and d.fname = 'Frank' and d.lname = 'Darabont'; Q2) Compute the number of roles that were cast in ‘The Shawshank Redemption’. Do
not eliminate duplicates in case of multiple roles with the same name, or multiple movies
with the same title.
select count(c.role)
from Casts c, Movie m
where c.mid = m.id AND m.name='The Shawshank Redemption'; Q3) Compute how many actors appeared in a movie released in 1995.
select count(distinct x.id)
from actor x, casts xy, movie y
where x.id = xy.pid AND xy.mid = y.id AND y.year = 1995; Q4) Compute how many actors appeared in a movie released in 1995 and also in a movie
released in 2010.
select count(distinct x.id)
from actor x, casts xy, casts xz, movie y, movie z
where x.id = xy.pid AND x.id = xz.pid AND xy.mid = y.id
AND xz.mid = z.id AND y.year = 1995 AND z.year = 2010; Q5) Return the number of actors who have played two (or more) roles in the same
movie. (If an actor has played multiple roles in several movies, they should be counted
only once.)
select count(distinct a.id)
from actor a, casts c1, casts c2
where a.id=c1.pid and a.id=c2.pid and c1.mid=c2.mid and c1.role != c2.role; | Q6) Compute how many movies were filmed in 1890. The output of your query should
be a number.
select count(*)
from movie
where year=1890; Q7) For each year between 1890 and 1899 (including 1890 and 1899), compute how many
movies were filmed that year. Your query should return the year in the first column and
the appropriate number in the second column. Order the results in increasing order of the year.
select year, count(*)
from movie
where year>=1890 and year <= 1899 group by year
order by year; Q8) List all directors who directed 40 or more ‘Thriller’ movies, in descending order of
the number of thrillers they directed. Return the directors’ first and last names and the
number of thrillers each of them directed.
select x.fname, x.lname, count(*) as c
from directors x, movie_directors y, genre g
where x.id = y.did AND g.genre = 'Thriller' AND g.mid = y.mid
group by x.id, x.fname, x.lname
having count(*) >= 40
order by c desc; Q9) List all actors who appeared in 40 or more ‘Thriller’ movies, in descending order of
the number of thrillers they appeared in. Return the actors’ first and last names and
the number of thrillers each of them appeared in. Output format is the same as Q3.
select x.fname, x.lname, count(distinct y.mid) as c
from actor x, casts y, genre g
where x.id = y.pid AND g.genre = 'Thriller' AND g.mid = y.mid
group by x.id, x.fname, x.lname
having count(distinct y.mid) >= 40
order by c desc; Q10) Return the maximum number of thrillers that any director has directed. The output
of your query should be a number
select max(c) from
(select count(*) as c
from movie_directors y, genre g
where g.genre = 'Thriller' AND g.mid = y.mid
group by y.did) m; Q11) We want to find actors who played exactly five distinct roles in the same movie
during the year 1990. Write a query that returns the actors’ first name, last name, and
movie name.
select x.fname, x.lname, y.name
from actor x, casts xy, movie y
where x.id = xy.pid AND xy.mid = y.id
AND y.year = 1990
group by x.id, x.fname, x.lname, y.id, y.name
having count(distinct xy.role) = 5; 
Q1 (IMDB): Return the year during which the most movies were released.
select year
from movie where year is not NULL
group by year
order by count(*) desc limit 1; Q2 (DBLP): Return the number of authors have published an article in January but
don’t have a homepage.
select count(distinct author.id)
from author, article, authored
where authored.id = author.id and authored.pubid = article.pubid
and month = 'January' and homepage IS NULL; Q3 (WorldDB): Return the name of the city that has the largest population in a country
which has exactly 11 languages.
SELECT ct.name
FROM (SELECT name, code FROM country, countrylanguage
WHERE country.code = countrylanguage.countrycode
GROUP BY name, code HAVING count(language) = 11) c, city ct
WHERE ct.countrycode = c.code
ORDER BY ct.population desc LIMIT 1; Q4 (IMDB): Return the first and last name of the actor who appeared in the most movies
in the year 2000.
SELECT actor.fname, actor.lname
FROM actor
JOIN casts ON actor.id = casts.pid
JOIN movie ON casts.mid = movie.id
WHERE movie.year=2000
GROUP BY actor.id, actor.fname, actor.lname
ORDER BY COUNT(distinct casts.mid) DESC
LIMIT 1; |
