Using MySQL and sakila table, definitions, and download found here: https://dev.

Question

Using MySQL and sakila table, definitions, and download found here:

https://dev.mysql.com/doc/sakila/en

1.Using the Sakila schema, produce two queries that identify the films each actor has been in. List the film’s title, film’s release year, and actor’s last name

a.Use implicit inner join syntax across three tables in the first query.

b.Use two explicit inner join syntax in the second query.

Result Set Size: 5462

2.Using the Company schema, provide a query that lists all employees along with any dependents. Include employees without dependents in the result set.

List the employee first name, last name, ssn, and the dependent’s name and relationship if a dependent exists.

Use the explicit joins.

Result Set Size: 47

3.Using the Company schema, select the unique project numbers and project names where either the project department’s manager is ‘Freed’ or an employee’s last name is ‘Freed’.  

Use explicit join notation where necessary.

Result Set Size: 2

Nested selects may be needed in this case.

4.Using the Company schema, list every project name and average employee salary for employees in the project.

Use explicit joins.

Note that the avg salary for project 61 (OperatingSystems) is 42,166.

Result Set Size: 11

Explanation / Answer

1.ANS:

SELECT a.title, a.release_year, c.last_name FROM `film` a inner JOIN `film_actor` b, `actor` c WHERE b.actor_id=c.actor_id and b.film_id=a.film_id;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.