Use your common sense on these. Show the SQL query AND the output. DO NOT provid

Question

Use your common sense on these. Show the SQL query AND the output. DO NOT provide IDs in the

final answer; all answers should be meaningful. Be careful; a movie can have more than one star.

1. Which movies’ show times are longer than “I Know What You Did.” but shorter than “Wonton Soup”?

2. What are the full addresses of all movie theatres in California?

Hints: you need to use NVL() function. Please read Oracle document for its usage.

3. What are movie titles that start with letter ‘T’ and are released between 2000 and 2005?

4. How many theatres are in New York or Connecticut?

5. How many movie people are not actors?

6. Find all actors whose last name contains an ‘a’ in the second position or first name ends with an

‘a’.

7. Find all theatres whose names have the pattern of ‘name Theatre’ (e.g. ‘Worst Theatre’ where

‘Worst’ is the name), and extract the name (e.g. Worst).

Explanation / Answer

[1]
SELECT MOVIE_NAME
FROM    TABLE_NAME
WHERE   TIME > (SELECT TIME
               FROM TABLE_NAME
      WHERE MOVIE_NAME = 'I Know What You Did')
   AND TIME < (SELECT TIME
               FROM TABLE_NAME
      WHERE MOVIE_NAME = 'Wonton Soup');

[2]

SELECT NVL(address,NULL)
FROM   TABLE_NAME
WHERE CITY = 'California';

[3]

SELECT MOVIE_TITLE
FROM   TABLE_NAME
WHERE MOVIE_TITLE LIKE 'T%'
   AND EXTRACT(YEAR FROM RELEASE_DATE) BETWEEN 2000 AND 2005;

[4]

SELECT count(*)
FROM   TABLE_NAME
WHERE CITY IN ('New York','Connecticut');

[5]

SELECT count(people)
FROM   TABLE_NAME
WHERE TYPE NOT IN ('ACTORS');

[6]
SELECT NAME
FROM   TABLE_NAME
WHERE ( first_name LIKE '%a'
     OR last_name LIKE '_a%')
AND TYPE IN ('ACTORS');


  
     

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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