Scribd Logo
Upload

Welcome to Scribd!

  • 16 views

    Lab 07 Sol

    Uploaded by

    AFAQ AHMAD

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Lab 07 Sol

    Uploaded by

    AFAQ AHMAD
    16 views8 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    16 views8 pages

    Lab 07 Sol

    Uploaded by

    AFAQ AHMAD

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 8

    Department of Computer Science

    CS220: Database Systems

    Class: BSCS-11AB
    Lab 07: SQL Joins

    Date: 28 October, 2022


    Time: 09:00-12:00 & 02:30-05:00
    Instructor: Dr. Shams Uddin Qazi

    Lab Engineer: Ayesha Asif

    CLO4: Demonstrate SQL queries to retrieve information from a relational


    database

    CS220: Database Systems Page 1


    Lab 07: SQL Joins

    Introduction
    A SQL join clause combines records from two or more tables in a database. It creates a set that can be saved as a
    table or used as it is. A JOIN is a means for combining fields from two tables by using values common to each.

    Objectives
    After performing this lab students should be able to: 1. Design SQL queries to retrieve data from multiple tables by
    using JOIN operation. 2. Select among INNER, OUTER, NATURAL, CROSS join as and when required.

    Tools/Software Requirement
    • MySQL Community Server 5.6
    • MySQL Workbench 6.1
    • Sakila Database

    Description
    1. This lab assumes that MySQL Community Server is running, Sakila database has been
    loaded using MySQL Workbench, and the query window is open.
    2. Execute the JOIN queries on Sakila including cross join, inner join, natural join, outer
    join (left outer join, right outer join, and full outer join) and observe how each join
    operation differs from the other.

    3. For your practice, create the following two tables and execute the following queries to better
    understand how each join operation works.

    Users

    id name course_id

    1 Alice 1

    2 Bob 1

    3 Caroline 2

    4 David 5

    5 Emma (NULL)

    CS220: Database Systems Page 2


    Course

    course_id name

    1 HTML5

    2 NULL

    3 JavaScript

    4 PHP

    5 MySQL

    a. Specify a primary-foreign key relationship between both tables.


    b. Produce a set of all users who are enrolled in a course?
    c. List of all students and their courses even if they’re not enrolled in any course yet?
    d. All records in both tables regardless of any match?

    4. Try running following SQL JOIN queries. You can try equivalent SQL queries for the
    same result as well.

    a. List rental and return date for the movie BREAKING HOME (notice one of the
    join uses on clause where as the other uses using). 13 entries of renting this movie
    should be found from the database.

    use sakila;

    select r.rental_date, return_date


    from rental r join inventory i
    using (inventory_id) join film f
    on (f.film_id=i.film_id)
    where f.title like 'BREAKING HOME' ;

    b. List of movie titles that were never rented. There are 43 of them. Notice that there
    are two outer joins as the movie might be missing altogether from the inventory
    but may still be included in the result set. On the other hand movie might be
    available in the inventory but never rented, the SQL query covers both cases.

    CS220: Database Systems Page 3


    select f.title
    from film f left join inventory i
    using (film_id) left join rental r
    using (inventory_id)
    where (i.inventory_id is null or r.rental_id is null);

    Lab Task
    Write SQL queries for the following information needs. You should execute your attempts
    on SQL workbench and make necessary corrections if needed. .

    a. What category does the movie CHOCOLATE DUCK belong to?


    Query:
    select category.name,category.category_id,category.last_update from category
    join( film_category join film on film.film_id=film_category.film_id) on
    category.category_id=film_category.category_id where film.title
    like('CHOCOLATE DUCK')
    Result:

    b. Track the location (city & country) of staff member JON.


    Query:
    select city.city,country.country from (staff join address on
    staff.address_id=address.address_id) join (city join country on
    city.country_id=country.country_id) on address.city_id=city.city_id where
    staff.first_name like('JON')
    Result:

    c. Retrieve first and last name of actors who played in ALONE TRIP.
    Query:
    select actor.first_name,actor.last_name from film join (film_actor join actor on
    film_actor.actor_id=actor.actor_id) on film.film_id=film_actor.film_id where
    film.title like('ALONE TRIP')
    Result:

    CS220: Database Systems Page 4


    d. List of movies in Games category having rental rate of more than $4 and sorted
    on movie titles.
    Query:
    select film.title from category join( film_category join film on
    film.film_id=film_category.film_id) on
    category.category_id=film_category.category_id where (category.name
    like('GAMES') AND film.rental_rate>4) order by title
    Result:

    e. Email addresses of customers who although rented a movie (include movie title in
    the output) but didn’t pay anything.
    Query:
    select film.title,customer.email from payment,customer,rental,inventory,film
    where payment.customer_id=customer.customer_id and
    payment.rental_id=rental.rental_id and

    CS220: Database Systems Page 5


    rental.inventory_id=inventory.inventory_id and inventory.film_id=film.film_id
    and payment.amount='0.00';
    Result:

    f. List of unpaid rentals (film title, rental id, rental date and for how many days
    rented). List should be sorted on film title and rental date.
    Query:
    select film.title,rental.rental_id,rental_date,film.rental_duration from
    payment,customer,rental,inventory,film where
    payment.customer_id=customer.customer_id and
    payment.rental_id=rental.rental_id and
    rental.inventory_id=inventory.inventory_id and inventory.film_id=film.film_id
    and payment.amount='0.00';
    Result:

    CS220: Database Systems Page 6


    g. How many films involve a word “Crocodile” and a “Shark”?
    Query:
    select count(*) as 'Number of Movies' from film where description like
    '%Crocodile%' and description like '%Shark%'
    Result:

    h. Retrieve language ids of languages ENGLISH and FRENCH.


    Query:
    SELECT language_id, name FROM language WHERE name IN ('ENGLISH',
    'FRENCH')
    Result:

    CS220: Database Systems Page 7


    Deliverables:
    Submit a PDF document including the SQL queries to answer above-mentioned information
    needs as well as snapshot of their outcome when executed over MySQL using the Workbench.

    CS220: Database Systems Page 8

    You might also like