The following questions will help you to practice writing SQL statements. The qu

Question

The following questions will help you to practice writing SQL statements. The questions are based on the "sakila" database schema on MySQL Workbench.

1. Write the sql statement that will provide you a list of active customers by full address. Sorted by country, city and last name. (Without using the wildcard (*) for the fields.) How many active households do we have?

2. Write the sql statement that will provide you a list of rental dates looking from today, back into history, and the count of rented films per day. (Without using the wildcard (*) for the fields.) How many days did we have movies rented?

3. Write the sql statement that will provide you a list all films that were rented. (Without using the wildcard (*) for the fields.) How many titles did we rent since the beginning?

4. Write the sql statement that will provide you the title of the movie that was most rented in June, 2005. How many times was it rented?

5. Write the sql statement that will provide you with the highest revenue day we had in the past. How much did we make? What was the highest revenue date?

Explanation / Answer

Q1) Write the sql statement that will provide you a list of active customers by full address. Sorted by country, city and last name

SELECT FIRST_NAME, LAST_NAME, ADDRESS, ADDRESS2, DISTRICT, POSTAL_CODE, PHONE, CITY.CITY, COUNTRY.COUNTRY FROM CUSTOMER JOIN ADDRESS ON CUSTOMER.ADDRESS_ID = ADDRESS.ADDRESS_ID JOIN CITY ON CITY.CITY_ID = ADDRESS.CITY_ID JOIN COUNTRY ON COUNTRY.COUNTRY_ID = CITY.COUNTRY_ID WHERE CUSTOMER.ACTIVE = True ORDER BY COUNTRY.COUNTRY ASC, CITY.CITY ASC, CUSTOMER.LAST_NAME ASC;

Q2) Write the sql statement that will provide you a list of rental dates looking from today, back into history, and the count of rented films per day

SELECT RENTAL_DATE, COUNT(RENTAL_DATE) AS 'NO_OF_RENTED_PER_DAY' FROM RENTALS WHERE RENTAL_DATE <= SYSDATE() GROUP BY (RENTAL_DATE);

Q3) Write the sql statement that will provide you a list all films that were rented. (Without using the wildcard (*) for the fields.) How many titles did we rent since the beginning?

SELECT FILM.FILM_ID, COUNT(FILM.TITLE), FILM.DESCRIPTION FROM FILM JOIN INVENTORY ON FILM.FILM_ID = INVENTORY.FILM_ID JOIN RENTAL ON RENTAL.INVENTORY_ID = INVENTORY.INVENTORY_ID WHERE RENTAL.RENTAL_DATE <= SYSDATE() GROUP BY (FILM.FILM_ID, FILM.DESCRIPTION);

Q4) Write the sql statement that will provide you the title of the movie that was most rented in June, 2005. How many times was it rented?

SELECT MAX(FILM.TITLE) AS 'MOST_RENTED' FROM FILM JOIN INVENTORY ON FILM.FILM_ID = INVENTORY.FILM_ID JOIN RENTAL ON RENTAL.INVENTORY_ID = INVENTORY.INVENTORY_ID WHERE TO_DATE(RENTAL.RENTAL_DATE,'DD-MON-YYYY') >= '01-JUN-2005' AND TO_DATE(RENTAL.RENTAL_DATE,'DD-MON-YYYY') <= '30-JUN-2015';

Q5) Write the sql statement that will provide you with the highest revenue day we had in the past. How much did we make? What was the highest revenue date?

SELECT SUM(PAYMENT.AMOUNT) AS 'MAXIMUM_REVENUE', PAYMENT.PAYMENT_DATE FROM PAYMENT JOIN RENTAL ON PAYMENT.RENTAL_ID = RENTAL.RENTAL_ID WHERE RENTAL.RENTAL_DATE <= SYSDATE() GROUP BY (PAYMENT.PAYMENT_DATE) ORDER BY MAXIMUM_REVENUE DESC LIMIT 1

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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