Question

1) Write SQL to return the first and last name of all actors with a first name of "ADAM" (use the ACTOR table) who have: (a) an ID of 11, 16, or greater than or equal to 70 AND (b) a last name that starts with the letter H or higher (I, J, K, etc.)

2) Write SQL with the LIKE operator to return addresses that are in the "Dhaka" district and have street addresses that do not contain the character sequence "Loop". You can return attributes (columns) values from the ADDRESS table for rows that matches this criteria.

3) Write SQL with the BETWEEN and ORDER BY clause to return the payments in descending order (i.e., highest payments first) from the PAYMENT table for customer IDs between 300 and 400. You can return all columns in the table.

4) Write SQL with the IN operator to return a concatenation of film title and description from the FILM table that have a replacement cost of either 10.99, 23.99, or 21.99 or have a description that contains the character sequence "Drama". Do NOT use the OR operator more than once in your solution.Your result should contain a single column and start like the following (HINT: you will need to use the CONCAT function)

ALABAMA DEVIL: A Thoughtful panorama of a database adm.....

ALI FOREVER: A Action-Packed Drama of a Dentist and a cro....

5) Write SQL with logical operators to return all information about payments in the PAYMENT table that were either (a) more than $3 and less or equal to than $8 OR (b) round to $1 (HINT: use the ROUND function). You should only return the first 10 of these rows, as ordered by the payment amount.

Answer 1

1.

a. select firstname, lastname from Actor where firstname = 'ADAM' and (id in (11,16) or id >= 70)

b. select firstname, lastname from Actor where firstname = 'ADAM' and lastname like '[H-Z]%'

3. select * from Payment where customer_ids between (300,400) order by payments desc

4. select concat(f.title, ': ' ,f.description) as FilmDescription from Film as f where replacement_cost in (10.99,23.99,21.99) or description like '%Drama%'

5. select * from Payment where (payments > 3 and payments <= 8) or round(payments) = 1