Question

SQL Queries – in this assignment you will be asked to create several SQL queries relating to the Sakila database that we installed in class. You may freely use DBeaver to create these queries, but I will expect your solution to show me the SQL code to answer or complete the tasks below.

1. Write a query that produces the last name, first name, address, district, and phone number for every customer in the customer table. (You don’t need to include the city or postal code for this question).
2. Same as #5, but only list the customers who are inactive, and include the city, country, postal code for each customer as well.
3. Add 2 rows to the Film Database before working on this query. These new films are the only results you should get.

Use this script to insert the new record.

INSERT INTO sakila.film (film_id,title,description,release_year,language_id,rental_duration,rental_rate,length,replacement_cost,rating,special_features)

VALUES (1001,'1st Grade FBI Agent','An undercover FBI agent must pretend to be a 1st grade teacher to catch the bad guy',2014,2,5,4.99,123,20.99,'PG-13','trailers'),

(1002,'2nd Grade DEA Agent','An undercover DEA agent must pretend to be a 2nd grade teacher to catch the bad guy',2015,3,4,5.99,132,16.99,'PG-13','trailers');

Write a query that produces the title and description of all films that are not in English.

4. Write a query that produces the title, rental rate, and language for all Sci-Fi films.
5. Write a query that produces a count of the number of   Action films(distinct titles, not copies of films)
6. Same as #9, but look at the inventory – so if there are 2 copies of a film it needs to be counted twice.
7. Write a query that produces the title and rental duration of all films that have a replacement cost between $15 and $23 (inclusive).
8. Write a query that produces the title of every action film with a rating of PG-13, R, or NC-17.
9. Write a query that produces the average length of all Children movies.
10. Write a query that produces the last name and first name and film title of all actors who are in Family, Foreign, or Horror films ordered by last name
11. Write a query that produces the last name and first name and film title and film category of every actor who is in either a Family movie OR a Horror movie ordered by last name. rename the category from “name” to “category” in your result table. (hint: “AS” keyword)
12. Write a query that produce the last name and email address of every customer who has rented a film starring NICK WAHLBERG or MATTHEW JOHANSSON or RITA REYNOLDS (we don’t want to have duplicate rows returned for our query – there is a keyword you might need to use).

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using WAMP server.

Question :

SQL Query :

select last_name,first_name,address,district,phone from
customer,address where
customer.address_id=address.address_id;

Screen in PHPMyAdmin :

Server phpMyAdmin demo - MySQL Database: sakila»Table: address Search Insert Imp SQL Browse Structure Export are not available. Showing rows 0 24 (599 total, Query took 0.0014 seconds.) - select last_name,first_name, address,district, phone from customer,address where ... . customer.address id-address.address id Profiling [Edit inline] [ Edit ] [ Explain SQL ][ Create PHP code ] [ Refresh] |Number of rows: 1 25 >> Options last name first name address district phone 1913 Hanoi Way SMITH MARY Nagasaki 28303384290 1121 Loja Avenue California 838635286649 JOHNSON PATRICIA Attika WILLIAMS LINDA 692 Joliet Street 448477190408 Mandalay JONES BARBARA 1566 Inegl Manor 705814003527 ELIZABETH 53 Idfu Parkway BROWN Nantou 10655648674 1795 Santiago de Compostela Way |900 Santiago de Compostela Parkway 478 Joliet Way DAVIS JENNIFER Texas 860452626434 MILLER MARIA Central Serbia 716571220373 WILSON SUSAN Hamilton 657282285970 MARGARET 613 Korolev Drive Masqat MOORE 380657522649 1531 Sal Drive Esfahan TAYLOR DOROTHY 648856936185 1542 Tarlac Parkway 635297277345 ANDERSON LISA Kanagawa 808 Bhopal Manor Haryana THOMAS 465887807014 NANCY 270 Amroha Parkway Osmaniye JACKSON KAREN 695479687538 Console 4

Server phpMyAdmin demo - MySQL Database: sakila»Table: address Search Insert Imp SQL Browse Structure Export are not available. Showing rows 0 24 (599 total, Query took 0.0014 seconds.) - select last_name,first_name, address,district, phone from customer,address where ... . customer.address id-address.address id Profiling [Edit inline] [ Edit ] [ Explain SQL ][ Create PHP code ] [ Refresh] |Number of rows: 1 25 >> Options last name first name address district phone 1913 Hanoi Way SMITH MARY Nagasaki 28303384290 1121 Loja Avenue California 838635286649 JOHNSON PATRICIA Attika WILLIAMS LINDA 692 Joliet Street 448477190408 Mandalay JONES BARBARA 1566 Inegl Manor 705814003527 ELIZABETH 53 Idfu Parkway BROWN Nantou 10655648674 1795 Santiago de Compostela Way |900 Santiago de Compostela Parkway 478 Joliet Way DAVIS JENNIFER Texas 860452626434 MILLER MARIA Central Serbia 716571220373 WILSON SUSAN Hamilton 657282285970 MARGARET 613 Korolev Drive Masqat MOORE 380657522649 1531 Sal Drive Esfahan TAYLOR DOROTHY 648856936185 1542 Tarlac Parkway 635297277345 ANDERSON LISA Kanagawa 808 Bhopal Manor Haryana THOMAS 465887807014 NANCY 270 Amroha Parkway Osmaniye JACKSON KAREN 695479687538 Console 4

*****************************************

Question :

SQL Query :

select last_name,first_name,address,District,phone,city,country,postal_code FROM customer,address,city,country where customer.customer_id=address.address_id and address.city_id=city.city_id and city.country_id=country.country_id and active=0

Screen in PHPMyAdmin :

Server: phpMyAdmin demo - MySQL>Database: sakila Structure SQL Search Query Export mport Operations Privileges Routines More select last name, first_name, address, District, phone, city, country, postal_code FROM Customer, address, city, country where customer.customer id-address.ad city.country_idecountry.country id and active-e SELECL Show all Number of rows: Filter rows 25 Search this table +Options last_name first name address District phone city postal_code country | 808 Bhopal Manor Haryana Rajasthan Risaralda Xinxiang 647 A Corua (La Corua) Street Chollanam 465887807014 Yamuna Nagar India 250767749542 Jaipur 765345144779 Dos Quebradas Colombia 935508855935 Hami 792557457753 Naju 621625204422 Salzburg SANDRA MARTIN 10672 81 Hodeida Way COX JUDITH India 55561 SHEILA 241 Mosul Lane WELLS 76157 154 Tallahassee Loop MATTHEWS ERICA China 62250 LARSON HEIDI South Korea 36971 NEAL PENNY 1587 Loja Manor Salzburg Austria 5410 Minas Gerais 103602195112 lbirit 568367775448 Nyeri 794553031307 Koriyama 406784385440 Batna 553452430707 Stockport Central Luzon 82671830126 Talavera 105882218332 Benguela 955349440539 Haining GOODEN 59686 KENNETH 543 Bergamo Avenue Brazil 1658 Jastrzebie-Zdrj Loop ARCE HARRY Central Kenya 96584 Fukushima Japan Algeria United Kingdom 41060 Philippines Angola RUNYON NATHAN 454 Patiala Lane 13496 THEODORE 1924 Shimonoseki Drive CULP Batna 52625 England 808 Naala-Porto Parkway CRAWLEY MAURICE EASTER BEN 48 Maracabo Place 1570 Benguela |Zhejiang Nantou 486 Ondo Parkway JUNG CHRISTIAN 35202 52234 EGGLESTON JIMMIE 904 Clarksville Drive China 182178609211 Tsaotun TERRANCE 1923 Stara Zagora Lane ROUSH Taiwan 95179

*****************************************

Question :

SQL Query :

select title,description from film where language_id not in (select language_id from language WHERE name='English')

Screen in PHPMyAdmin :

Database: sakila » Server phpMyAdmin demo - MySQL Table: film Browse Structure ZInsert Privileges SQL Search Export Import Show query box Showing rows 0 1 (2 total, Query took 0.0018 seconds.) select title, description from film where language id not in (select language id from language WHERE name-'English') SELECL .... Number of rows: Filter rows: Search this table Show all Sort by key: 25 None Options T title description Edit Copy Delete 1st Grade FBI Agent An undercover FBI agent must pretend to be a 1st g. ... Edit Copy Delete 2nd Grade DEA Agent An undercover DEA agent must pretend to be a 2nd g.. Copy Check all With selected: Edit Delete Export

*****************************************

Question :

SQL Query :

/*Write a query that produces the title, rental rate, and language for all Sci-Fi films.*/
SELECT title,rental_rate,language.name from film,language,film_category,category
WHERE
film.language_id=language.language_id and
film.film_id=film_category.film_id and
film_category.category_id=category.category_id and category.name='Sci-Fi';

Screen in PHPMyAdmin :

Database: sakila» Table: language Server: phpMyAdmin demo - MySQL EInsert Browse Structure SQL Search Export Import Privileges Oper /Write a query that produces the title, rental rate, and language for all sci-Fi films. SELECT title,rental_rate,language.name from film,language,film_category, category WHERE film.language id=language.language id and film.film_id=film_category.film id and film_category.category id-category.category id and category.name- sci-Fi [Ed Show all Number of rows: Filter rows: Search this table 1 25 > Options + title rental rate name 0.99 English English 4.99 English 2.99 English 2.99 English 2.99 English 2.99 English English 0.99 English 0.99 English 2.99 English 2.99 English 0.99 English English 0.99 English English 4.99 English 4.99 English 4.99 English ANNIE IDENTITY ARMAGEDDON LOST 0.99 ATTACKS HATE BADMAN DAWN BARBARELLA STREETCAR BEVERLY OUTLAW BINGO TALENTED BLINDNESS GUN 4.99 CAMELOT VACATION CHAINSAW UPTOWN CHARADE DUFFEL CHARIOTS CONSPIRACY CHEAPER CLYDE CINCINATTI WHISPERER 4.99 CITIZEN SHREK COLDBLOODED DARLING 4.99 CONNECTICUT TRAMP CROWDS TELEMARK Console AGERIE

*****************************************

Question :

SQL Query :

select title,rental_duration from film WHERE replacement_cost BETWEEN 15 and 23;

Screen in PHPMyAdmin :

Server: phpMyAdmin demo - MySQL Database: sakila »Table: film EE Insert Browse Structure SQL Search Expc select title, rental_duration from film WHERE replacement_cost BETWEEN 15 and 23 ... Show all Number of rows: 1 25 Filter ro Options + T rental duration title Edit Copy Delete ACADEMY DINOSAUR 6 Edit Copy Delete ADAPTATION HOLES 7 Edit Copy Delete AFRICAN EGG 6 Edit Copy Delete AGENT TRUMAN 3 Delete AIRPORT POLLOCK EditCopy 6 Edit Copy Delete ALABAMA DEVIL 3 Edit Copy 6 Delete ALAMO VIDEOTAPE Edit Copy Delete ALASKA PHANTOM 6 Edit Copy Delete ALI FOREVER 4 Edit Copy Delete AMADEUS HOLY 6 Edit Copy Delete AMERICAN CIRCUS 3 Edit Copy Delete ANALYZE HOOSIERS Edit Copy Delete ANGELS LIFE Edit Copy Delete ANNIE IDENTITY 3 EditCopy Delete ANTHEM LUKE 5 Consolet Conv Delete APACHE DIVINE 5 LC et cO LO

*****************************************

Question :

SQL Query :

/*Write a query that produces the title, rental rate, and language for all Sci-Fi films.*/
SELECT count(title) from film,film_category,category
WHERE
film.film_id=film_category.film_id and
film_category.category_id=category.category_id and category.name='Action'
GROUP by category.name;

Screen in PHPMyAdmin :

Database: sakila »Table: language Server phpMyAdmin demo - MySOL» EInsert SQL Privileges Browse Structure Search Export Import Operations Showing rows 0 0 (1 total, Query took 0.0016 seconds.) - /write a query that produces the title, rental rate, and language for all sci-Fi films. SELECT cCount (title) from film,film_category, category WHERE . . film.film id-film_category.film_id and film_category.category id-category.category id and category.name- Action' GROUP by category.name [Edit inline] [ Edit Show all Number of rows: 25 Filter rows: Search this table Options + count(title) 64 Number of rows: Filter rows: Search this table Show all 25

*****************************************

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.