The SELECT statement is one of the most fundamental SQL commands and is used to retrieve data from a PostgreSQL database.
Syntax:
SELECT column_name FROM table_name;
I was given a few tasks to strengthen my sql basics so this blog will contain the queries that i performed after connecting my dvdrental database to PostgreSQL.
1.Retrieve film titles and their rental rates. Use column aliases to rename title as "Movie Title" and rental_rate as "Rate".
SELECT title AS "Movie_title", rental_rate AS "Rate" FROM film;
2.List customer names and their email addresses. Alias first_name and last_name as "First Name" and "Last Name".
SELECT first_name AS "First Name", last_name AS "Last Name", email FROM customer;
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.
SELECT title, rental_rate FROM film ORDER BY rental_rate DESC, title ASC;
4.Retrieve actor names sorted by last name, then first name.
SELECT first_name, last_name FROM actor ORDER BY last_name, first_name;
5.List all unique replacement costs from the film table.
SELECT DISTINCT replacement_cost FROM film;
We use DISTINCT here to remove duplicates.
6.List all films' title and length in minutes. Alias length as "Duration (min)".
SELECT title, length AS "Duration (min)" FROM film;
7.Retrieve customer first and last names along with their active status. Alias active as "Is Active".
SELECT first_name, last_name, active AS "Is Active" FROM customer;
8.Retrieve the list of film categories sorted alphabetically.
SELECT name FROM category ORDER BY name;
To sort use ORDER BY.
9.List films by length, sorted in descending order. Include only the title and length.
SELECT title, length FROM film ORDER BY length DESC;
To sort in a specific order use ASC/DESC,by default it is ASC.
10.Retrieve all actor names, sorted by their first name in descending order.
SELECT first_name, last_name FROM actor ORDER BY first_name DESC;
11.List all unique ratings available in the film table.
SELECT DISTINCT rating FROM film;
12.Find all unique rental durations from the film table.
SELECT DISTINCT rental_duration FROM film;
13.Retrieve the first unique customer ID based on active status. Include the customer_id and active columns, and order by customer_id.
SELECT DISTINCT customer_id, active FROM customer ORDER BY customer_id;
14.List the earliest rental date for each customer. Include customer_id and rental_date, and order by customer_id.
SELECT customer_id, MIN(rental_date) AS rental_date FROM rental GROUP BY customer_id ORDER BY customer_id;
15.List the 10 shortest films by length. Include the title and length.
- To print till a certain number of records we can use LIMIT
SELECT title, length FROM film ORDER BY length ASC LIMIT 10;
16.Get the top 5 customers with the highest customer_id. Include the first and last name.
SELECT first_name, last_name FROM customer ORDER BY customer_id DESC LIMIT 5;
17.Retrieve all unique values of store_id from the inventory table.
SELECT DISTINCT store_id FROM inventory;
18.Find all unique replacement_cost values in the film table. Sort the results in ascending order.
SELECT DISTINCT replacement_cost FROM film ORDER BY replacement_cost ASC;
19.List the first rental date for each store. Include store_id and rental_date, and sort by store_id.
SELECT store_id, MIN(rental_date) AS rental_date FROM rental GROUP BY store_id ORDER BY store_id;
20.Retrieve a list of film ratings sorted alphabetically and include only unique values.
SELECT DISTINCT rating FROM film ORDER BY rating;
21.List films by rating in ascending order and length in descending order.
SELECT title, rating, length FROM film ORDER BY rating ASC, length DESC;
22.Retrieve actor names sorted by last_name in ascending order and first_name in descending order.
SELECT first_name, last_name FROM actor ORDER BY last_name ASC, first_name DESC;
23.List films ordered by replacement_cost in ascending order and rental_rate in descending order.
SELECT title, replacement_cost, rental_rate FROM film ORDER BY replacement_cost ASC, rental_rate DESC;
24.Retrieve customer names sorted by last_name ascending and first_name descending.
SELECT first_name, last_name FROM customer ORDER BY last_name ASC, first_name DESC;
25.List all rentals sorted by customer_id ascending and rental_date descending.
SELECT customer_id, rental_date FROM rental ORDER BY customer_id ASC, rental_date DESC;
26.Retrieve a list of films ordered by rental_duration ascending and title descending.
SELECT title FROM film ORDER BY rental_duration ASC, title DESC;
From this set of assignment questions,I was able to understand basic sql select questions and more specifically I understood the situations where some keywords play an important role.
Top comments (0)