URL(//d2vlcm61l7u1fs.cloudfront.net/media%2F1b4%2F1b4f6691-1c78-48e6-9eab-2759c1

Question

URL(//d2vlcm61l7u1fs.cloudfront.net/media%2F1b4%2F1b4f6691-1c78-48e6-9eab-2759c1e9b980%2Fphp0y3qy6.png)

Tables | | actor Columns actorid first_name last_name last-update Indexes Foreign Keys Triggers - address category country customer Columns filmid title description release-year language-id original-language id rental-duration rentalrate length replacement-cost rating special-features last-update Indexes Foreign Keys Triggers - - film actor film-category filmtext - inventory language payment rental staff store > Views Stored Procedures Functions

Explanation / Answer

I am considering the following schema/table structure:

Table : Film_text

film_id,Title,Description,Actor,ReleaseDate.

Query

SELECT FILM_ID,TITLE,DESCRIPTION FROM FILM_TEXT WHERE ACTOR= 'Zero Cage' ORDER BY DESCRIPTION;

Above query is simple and doesn't have subqueries, because there is no need of it. The query fetches the data where the actor is Zero cage and we are ordering the result based on the ascending order(default) of the despriction.

Suppose, the film_text table had only the ID of the actor and if we had another table ,

say Actor_text(actor_id, actor_name), we can use the sub-query, but join opeariotn will also be simple too.

Query with Subquery:

SELECT FILM_ID, TITLE, DESCRIPTION
FROM FILM_TEXT
WHERE ACTOR_ID =
(SELECT ACTOR_ID FROM ACTOR_TEXT WHERE ACTOR = 'Zero Cage');

here the subquery will return one row , the Id of the actor where the actor_name is Zero Cage.

This can be achieved with the following query with join too:

SELECT FILM_ID, TITLE, DESCRIPTION
FROM FILM_TEXT FT, ACTOR_TEXT AT
WHERE FT.ACTOR_ID = AT.ACTOR_ID
AND AT.ACTOR = 'Zero Cage';

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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