Try .NET /elinq/
Powered by Try .NET

Sakila Database Queries

We omitted the most trivial questions and DDL.

1b. Display the first and last name of each actor in a single column in upper case letters. Name the column Actor Name

var query = DbContext.ActorNames.Query((Actor actor, ActorName alias) =>
{
    var result = SELECT<ActorName>((actor.FirstName + " " + actor.LastName).ToUpper().@as(alias.FullName));
    FROM(actor);

    return result;
});

foreach (var actorName in query.Take(3))
    Console.WriteLine(actorName.FullName);

... using C# interpolation syntax:

var query = DbContext.ActorNames.Query((Actor actor, ActorName alias) =>
{
    var result = SELECT<ActorName>($"{actor.FirstName} {actor.LastName}".ToUpper().@as(alias.FullName));
    FROM(actor);

    return result;
});

foreach (var actorName in query.Take(3))
    Console.WriteLine(actorName.FullName);

2a. You need to find the ID number, first name, and last name of an actor, of whom you know only the first name, "Joe."

var name = "Joe";

var query = DbContext.Actor.Query((Actor actor) =>
{
    var result = SELECT(actor);
    FROM(actor);
    WHERE(actor.FirstName.ToLower() == name.ToLower());

    return result;
});

foreach (var actor in query.Take(3))
    Console.WriteLine((actor.ActorId, actor.FirstName, actor.LastName, actor.LastUpdate));

2b. Find all actors whose last name contain the letters GEN

var letters = "GEN";

var query = DbContext.Actor.Query((Actor actor) =>
{
    var result = SELECT(actor);
    FROM(actor);
    WHERE(actor.LastName.ToUpper().Contains(letters));

    return result;
});

foreach (var actor in query.Take(3))
    Console.WriteLine((actor.ActorId, actor.FirstName, actor.LastName, actor.LastUpdate));

2c. Find all actors whose last names contain the letters LI. This time, order the rows by last name and first name, in that order

var letters = "LI";

var query = DbContext.Actor.Query((Actor actor) =>
{
    var result = SELECT(actor);
    FROM(actor);
    WHERE(actor.LastName.ToUpper().Contains(letters));
    ORDER(BY(actor.LastName), BY(actor.FirstName));

    return result;
});

foreach (var actor in query.Take(3))
    Console.WriteLine((actor.ActorId, actor.FirstName, actor.LastName, actor.LastUpdate));

2d. Using IN, display the country_id and country columns of the following countries: Afghanistan, Bangladesh, and China

string[] countries = { "China", "Afghanistan", "Bangladesh" };

var query = DbContext.Country.Query((Country country) =>
{
    var result = SELECT(country);
    FROM(country);
    WHERE(countries.Contains(country.Name));

    return result;
});

foreach (var country in query.Take(3))
    Console.WriteLine((country.CountryId, country.Name, country.City, country.LastUpdate));

4a. List the last names of actors, as well as how many actors have that last name

var query = DbContext.ActorNameCounts.Query((Actor actor, ActorNameCount alias) =>
{
    var result = SELECT<ActorNameCount>(actor.LastName.@as(alias.LastName), COUNT().@as(alias.Count));
    FROM(actor);
    GROUP(BY(actor.LastName));
    ORDER(BY(actor.LastName).DESC);

    return result;
});

foreach (var actor in query.Take(3))
    Console.WriteLine((actor.LastName, actor.Count));

4b. List last names of actors and the number of actors who have that last name, but only for names that are shared by at least two actors

var query = DbContext.ActorNameCounts.Query((Actor actor, ActorNameCount alias) =>
{
    var actorCount = COUNT();

    var result = SELECT<ActorNameCount>(actor.LastName.@as(alias.LastName), actorCount.@as(alias.Count));
    FROM(actor);
    GROUP(BY(actor.LastName));
    HAVING(actorCount > 1);
    ORDER(BY(actorCount).DESC, BY(actor.LastName));

    return result;
});

foreach (var actor in query.Take(3))
    Console.WriteLine((actor.LastName, actor.Count));

4c. The actor HARPO WILLIAMS was accidentally entered in the actor table as GROUCHO WILLIAMS. Write a query to fix the record

var newFirstName = "HARPO";
var oldFirstName = "GROUCHO";
var lastName = "WILLIAMS";

var query = DbContext.Actor.Query((Actor actor) =>
{
    UPDATE(actor).SET(() => actor.FirstName = newFirstName);
    WHERE(actor.FirstName == oldFirstName && actor.LastName == lastName);

    Semicolon();

    var result = SELECT(actor);
    FROM(actor);
    WHERE(actor.LastName == lastName);

    return result;
})
    .AsEnumerable();

foreach (var actor in query.Take(3))
    Console.WriteLine((actor.ActorId, actor.FirstName, actor.LastName, actor.LastUpdate));

6b. Use JOIN to display the total amount rung up by each staff member in August of 2005. Use tables staff and payment

var payMonth = 8;
var payYear = 2005;

var query = DbContext.StaffPayments.Query((Staff staff, Payment payment, StaffPayment alias) =>
{
    var r = SELECT<StaffPayment>(staff.StaffId.@as(alias.Staff.StaffId), SUM(payment.Amount).@as(alias.Amount));
    FROM(staff).LEFT_JOIN(payment).ON(staff == payment.Staff);
    WHERE(MONTH(payment.PaymentDate) == payMonth && YEAR(payment.PaymentDate) == payYear);
    GROUP(BY(staff.StaffId)); //must GROUP BY StaffId since this field is used in SELECT

    return r;
})
    .Include(sp => sp.Staff);

foreach (var staffPayment in query.Take(3))
    Console.WriteLine((staffPayment.Staff.FirstName, staffPayment.Staff.LastName, staffPayment.Amount));

using DateTime properties mappings:

var payMonth = 8;
var payYear = 2005;

var query = DbContext.StaffPayments.Query((Staff staff, Payment payment, StaffPayment alias) =>
{
    var r = SELECT<StaffPayment>(staff.StaffId.@as(alias.Staff.StaffId), SUM(payment.Amount).@as(alias.Amount));
    FROM(staff).LEFT_JOIN(payment).ON(staff == payment.Staff);
    WHERE(payment.PaymentDate.Month == payMonth && payment.PaymentDate.Year == payYear);
    GROUP(BY(staff.StaffId)); //must GROUP BY StaffId since this field is used in SELECT

    return r;
})
    .Include(sp => sp.Staff);

foreach (var staffPayment in query.Take(3))
    Console.WriteLine((staffPayment.Staff.FirstName, staffPayment.Staff.LastName, staffPayment.Amount));

7a. The music of Queen and Kris Kristofferson have seen an unlikely resurgence. As an unintended consequence, films starting with the letters K and Q have also soared in popularity. Use subqueries to display the titles of movies starting with the letters K and Q whose language is English

var language = "English";

var query = DbContext.Film.Query((Film film) =>
{

    var languages = SubQuery((Language lang) =>
    {
        var l = SELECT(lang.LanguageId);
        FROM(lang);
        WHERE(lang.Name == language);

        return l.AsCollection();
    });

    var r = SELECT(film);
    FROM(film);
    WHERE((film.Title.StartsWith("K") || film.Title.StartsWith("Q")) && languages.Contains(film.Language.LanguageId));

    return r;
});

foreach (var film in query.Take(3))
    Console.WriteLine(film.Title);

7b. Use subqueries to display all actors who appear in the film Alone Trip

var filmName = "Alone Trip";

var query = DbContext.Actor.Query((Actor actor) =>
{

    var films = SubQuery((Film film) =>
    {
        var l = SELECT(film.FilmId);
        FROM(film);
        WHERE(film.Title.ToLower() == filmName.ToLower());

        return l.AsCollection();
    });

    var actors = SubQuery((FilmActor filmActor) =>
    {
        var l = SELECT(filmActor.ActorId);
        FROM(filmActor);
        WHERE(films.Contains(filmActor.FilmId));

        return l.AsCollection();
    });

    var r = SELECT(actor);
    FROM(actor);
    WHERE(actors.Contains(actor.ActorId));

    return r;
});

foreach (var actor in query.Take(3))
    Console.WriteLine((actor.FirstName, actor.LastName));

same with ActorsByFilms refactored out:

public void Test7B_1()
{
    var filmName = "Alone Trip";

    var query = DbContext.Actor.Query((Actor actor) =>
    {
        var films = SubQuery((Film film) =>
        {
            var l = SELECT(film.FilmId);
            FROM(film);
            WHERE(film.Title.ToLower() == filmName.ToLower());

            return l.AsCollection();
        });

        var actors = ActorsByFilms(films);

        var r = SELECT(actor);
        FROM(actor);
        WHERE(actors.Contains(actor.ActorId));

        return r;
    });

    foreach (var actor in query.Take(3))
        Console.WriteLine((actor.FirstName, actor.LastName));
}

private static ICollection<ushort> ActorsByFilms(ICollection<ushort> films)
{
    return SubQuery((FilmActor filmActor) =>
    {
        var l = SELECT(filmActor.ActorId);
        FROM(filmActor);
        WHERE(films.Contains(filmActor.FilmId));

        return l.AsCollection();
    });
}

< BACK | HOME