Select Queries from DVD Rental database

• first, Download the tar file https://github.com/syedjaferk/postgres_sample_database/blob/main/dvd_rental/dvdrental.tar
  using the above link

• then open pgAdmin4 Login to your postgres then upload the downloaded database

• create a new database called dvdrental using the cmd
  CREATE DATABASE dvdrental;

-To check if your database has been uploaded use this cmd \dt

SELECT Query

• Basic SELECT Statement SELECT first_name FROM customer; use the cmd to display all name in database

1) Use column aliases to rename title as "Movie Title" and rental_rate as "Rate"

cmd: SELECT title AS "Movie Title", rental_rate AS "Rate"
FROM film;

sample op:

Movie Title | Rate
-----------------------------+------
Chamber Italian | 4.99
Grosse Wonderful | 4.99
Airport Pollock | 4.99
Bright Encounters | 4.99
Academy Dinosaur | 0.99
Ace Goldfinger | 4.99
Adaptation Holes | 2.99

2) List customer names and their email addresses. Alias first_name and last_name as "First Name" and "Last Name"

cmd: SELECT first_name AS "First Name", last_name AS "Last Name", email FROM customer;

sample op:
First Name | Last Name | email
-------------+--------------+------------------------------------------
Jared | Ely | [email protected]
Mary | Smith | [email protected]
Patricia | Johnson | [email protected]
Linda | Williams | [email protected]
Barbara | Jones | [email protected]
Elizabeth | Brown | [email protected]
Jennifer | Davis | [email protected]
Maria | Miller | [email protected]

3) Get a list of films sorted by rental rate in descending order. If two films have the same rental rate, sort them alphabetically by title.

cmd: SELECT title, rental_rate FROM film ORDER BY rental_rate DESC, title ASC;

sample op:
title | rental_rate
-----------------------------+-------------
Ace Goldfinger | 4.99
Airplane Sierra | 4.99
Airport Pollock | 4.99
Aladdin Calendar | 4.99
Ali Forever | 4.99
Amelie Hellfighters | 4.99
American Circus | 4.99
Anthem Luke | 4.99
Apache Divine | 4.99
Apocalypse Flamingos | 4.99
Attacks Hate | 4.99

4) Retrieve actor names sorted by last name, then first name.

cmd: ORDER BY last_name ASC, first_name ASC;

sample op:
first_name | last_name
-------------+--------------
Christian | Akroyd
Debbie | Akroyd
Kirsten | Akroyd
Cuba | Allen
Kim | Allen
Meryl | Allen
Angelina | Astaire
Russell | Bacall
Audrey | Bailey
Jessica | Bailey
Harrison | Bale
Renee | Ball
Julia | Barrymore