The challange I solved at the time to apply what I learned, maybe it will help you too

1

Sort the data in the title and description columns in the movie table.

SELECT title , description FROM film;

Sort the data in all columns in the movie table with the condition that the movie length is greater than 60 AND less than 75.

SELECT * FROM film WHERE length >= 60 and length <= 75 ;

Sort the data in all columns in the movie table with the conditions rental_rate 0.99 AND replacement_cost 12.99 OR 28.99.

SELECT * FROM film WHERE rental_rate = 0.99 and replacement_cost = 12.99 or rental_rate = 0.99 and replacement_cost = 28.99 ; 

What is the value in the last_name column of the customer whose value in the first_name column in the Customer table is 'Mary'?

SELECT last_name FROM customer WHERE first_name = 'Mary';

Sort the data in the movie table that does NOT have a length greater than 50 and does NOT have a rental_rate of 2.99 or 4.99.

SELECT * FROM film WHERE length < 50 and NOT (rental_rate = 2.99 or rental_rate = 4.99)

2

Sort the data in all columns in the movie table, provided that the replacement cost value is greater than 12.99 and less than 16.99 (use the BETWEEN - AND structure).

SELECT * FROM film WHERE replacement_cost BETWEEN 12.99 and 16.99;

Sort the data in the first_name and last_name columns in the Actor table, provided that first_name is 'Penelope' or 'Nick' or 'Ed' (use the IN operator).

SELECT first_name , last_name FROM actor WHERE first_name  IN ('Penelope', 'Nick', 'Ed');

Sort the data in all columns in the movie table with the conditions rental_rate 0.99, 2.99, 4.99 AND replacement_cost 12.99, 15.99, 28.99 (use IN operator).

SELECT * FROM film WHERE rental_rate  IN (0.99, 2.99, 4.99) and replacement_cost IN (12.99, 15.99, 28.99);

3

List the country names in the country column of the country table starting with the character 'A' and ending with the character 'a'.

SELECT country FROM country WHERE country ~~ 'A%a' ;

List the country names in the country column of the country table that have at least 6 characters and end with the character 'n'.

SELECT country From Country WHERE LENGTH(country) >= 6 AND country ~~'%n';

At least 4 movie titles in the title column of the movie table containing at least 4 uppercase or lowercase 'T' characters

SELECT title FROM film WHERE title ~~* '%t%t%t%t%';

Sort the data in all columns in the movie table starting with title 'C', length greater than 90 and rental_rate 2.99.

SELECT * FROM film WHERE title ~~ 'C%' and length > 90 and rental_rate = 2.99 ;

4

Film tablosunda bulunan replacement_cost sütununda bulunan birbirinden farklı değerleri sıralayınız.

SELECT DISTINCT replacement_cost FROM film ;

How many different data are in the replacement_cost column in the movie table?

SELECT COUNT(DISTINCT replacement_cost) FROM film ;

How many of the movie titles in the movie table start with the character T and also have a rating equal to 'G'?

SELECT COUNT(*) FROM film WHERE title ~~ 'T%' and rating = 'G' ;

How many of the country names in the Country table have 5 characters?

SELECT COUNT(*) FROM country WHERE country ~~* '_____' ;

How many of the city names in the City table end with the character 'R' or r?

SELECT COUNT(*) FROM city WHERE city ~~* '%r' ;

5

List the 5 longest movies in the movie table whose title ends with the character 'n'.

SELECT * FROM film WHERE title ~~* '%n'  ORDER BY length DESC LIMIT 5;

List the second 5 shortest (length) movies in the movie table whose title ends with the character 'n'.

SELECT * FROM film WHERE title ~~* '%n'  ORDER BY length OFFSET 5 LIMIT 5;

Sort the first 4 data in descending order according to the last_name column in the Customer table, provided that store_id is 1.

SELECT * FROM customer WHERE store_id ='1' ORDER BY last_name DESC LIMIT 4;

6

What is the average of the values in the rental_rate column in the movie table?

SELECT AVG(rental_rate) FROM film ;

How many of the movies in the movie table start with the character 'C'?

SELECT COUNT(*) FROM film WHERE title ~~'C%' ;

How many minutes is the longest movie in the movie table with a rental_rate equal to 0.99?

SELECT MAX(length) FROM film WHERE rental_rate = '0.99' ;

How many different replacement_cost values are in the movie table for movies with a length greater than 150 minutes?

SELECT COUNT(DISTINCT replacement_cost) FROM film WHERE length > '150' ;

7

Group the movies in the movie table according to their rating values.

SELECT rating FROM film GROUP BY rating ;

When we group the movies in the movie table according to the replacement_cost column, sort the replacement_cost value with more than 50 movies and the corresponding number of movies.

SELECT replacement_cost,COUNT(*) FROM film GROUP BY replacement_cost HAVING COUNT(*) >  50;

What are the number of customers corresponding to the store_id values in the Customer table?

SELECT store_id ,COUNT(*) FROM customer GROUP BY store_id 

After grouping the city data in the City table according to the country_id column, share the country_id information and the number of cities with the highest number of cities.

SELECT country_id ,COUNT(*) FROM city GROUP BY country_id ORDER BY COUNT(*) DESC 

8

In your test database, let's create a table named employee with column information id(INTEGER), name VARCHAR(50), birthday DATE, email VARCHAR(100).

CREATE TABLE employee (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100),
  birthday DATE
);

Let's add 50 data to the employee table we created using the 'Mockaroo' service.

insert into employee (name, email, birthday) values ('Mack', '[email protected]', '2008-07-22');
insert into employee (name, email, birthday) values ('Fredric', '[email protected]', null);
insert into employee (name, email, birthday) values ('Lanny', '[email protected]', '2014-01-31');
insert into employee (name, email, birthday) values ('Gwenneth', '[email protected]', '2016-05-28');
insert into employee (name, email, birthday) values ('Lee', '[email protected]', '2002-02-22');
insert into employee (name, email, birthday) values ('Wilton', '[email protected]', '2003-05-27');
insert into employee (name, email, birthday) values ('Lusa', '[email protected]', '2019-09-27');
insert into employee (name, email, birthday) values ('Nariko', '[email protected]', '2015-03-14');
insert into employee (name, email, birthday) values ('Tristan', '[email protected]', '2015-07-27');
insert into employee (name, email, birthday) values ('Stephana', '[email protected]', '2015-01-08');
insert into employee (name, email, birthday) values ('Trever', '[email protected]', '2006-05-18');
insert into employee (name, email, birthday) values ('Analise', null, '2016-10-23');
insert into employee (name, email, birthday) values ('Salim', '[email protected]', '2001-11-28');
insert into employee (name, email, birthday) values ('Daryn', '[email protected]', '2020-05-12');
insert into employee (name, email, birthday) values ('Irene', '[email protected]', null);
insert into employee (name, email, birthday) values ('Ethelda', '[email protected]', '2019-09-05');
insert into employee (name, email, birthday) values ('Inglebert', '[email protected]', '2008-02-11');
insert into employee (name, email, birthday) values ('Bradly', '[email protected]', '2013-06-12');
insert into employee (name, email, birthday) values ('Thurston', '[email protected]', '2018-11-01');
insert into employee (name, email, birthday) values ('Gayelord', '[email protected]', null);
insert into employee (name, email, birthday) values ('Delaney', '[email protected]', null);
insert into employee (name, email, birthday) values ('Domeniga', '[email protected]', '2014-01-02');
insert into employee (name, email, birthday) values ('Garey', null, '2007-06-07');
insert into employee (name, email, birthday) values ('Jaye', '[email protected]', '2018-07-31');
insert into employee (name, email, birthday) values ('Mort', '[email protected]', null);
insert into employee (name, email, birthday) values ('Kellia', null, null);
insert into employee (name, email, birthday) values ('Edgard', '[email protected]', '2009-09-25');
insert into employee (name, email, birthday) values ('Juli', '[email protected]', '2002-06-21');
insert into employee (name, email, birthday) values ('Ginger', '[email protected]', '2008-03-19');
insert into employee (name, email, birthday) values ('Morey', null, '2019-08-22');
insert into employee (name, email, birthday) values ('Holli', '[email protected]', null);
insert into employee (name, email, birthday) values ('Lew', null, '2007-08-21');
insert into employee (name, email, birthday) values ('Corbett', null, '2016-09-19');
insert into employee (name, email, birthday) values ('Egon', '[email protected]', '2016-10-04');
insert into employee (name, email, birthday) values ('Tadio', '[email protected]', '2012-06-03');
insert into employee (name, email, birthday) values ('Shir', '[email protected]', '2012-06-03');
insert into employee (name, email, birthday) values ('Chryste', '[email protected]', '2007-10-31');
insert into employee (name, email, birthday) values ('Marrilee', '[email protected]', null);
insert into employee (name, email, birthday) values ('Greta', '[email protected]', null);
insert into employee (name, email, birthday) values ('Karrie', '[email protected]', '2014-10-12');
insert into employee (name, email, birthday) values ('Tanhya', '[email protected]', '2012-04-24');
insert into employee (name, email, birthday) values ('Erny', '[email protected]', '2004-09-08');
insert into employee (name, email, birthday) values ('Cullin', '[email protected]', '2011-07-01');
insert into employee (name, email, birthday) values ('Reynold', '[email protected]', '2009-09-09');
insert into employee (name, email, birthday) values ('Mikol', '[email protected]', '2005-02-11');
insert into employee (name, email, birthday) values ('Adolphus', null, '2013-06-02');
insert into employee (name, email, birthday) values ('Friederike', null, '2005-07-02');
insert into employee (name, email, birthday) values ('Lindy', '[email protected]', '2002-07-02');
insert into employee (name, email, birthday) values ('Normy', '[email protected]', '2010-08-28');
insert into employee (name, email, birthday) values ('Elissa', '[email protected]', null);

Let's do 5 UPDATE operations that will update other columns according to each of the columns.

UPDATE employee SET name = 'Ciguli', birthday = '24-06-2004' WHERE name = 'Elissa' RETURNING * ;

UPDATE employee SET  email = '[email protected]' WHERE name = 'Ciguli' RETURNING * ;

UPDATE employee SET  email = '[email protected]' WHERE name = 'Mack' RETURNING * ;

UPDATE employee SET  birthday = '24-06-2004' WHERE name = 'Mack' RETURNING * ;

UPDATE employee SET name = 'Ecrin', email = '[email protected]', birthday = '24-06-2004' WHERE name = 'Mack' RETURNING * ;

Let's do 5 DELETE operations that will delete the corresponding row according to each of the columns.

DELETE FROM employee WHERE name = 'Fredric';

DELETE FROM employee WHERE id = 8;

DELETE FROM employee WHERE birthday = '2014-01-31';

DELETE FROM employee WHERE email = '[email protected]';

DELETE FROM employee WHERE name = 'F%';

9

Write the INNER JOIN query that we can see the city and country names in the city table and country table together.

SELECT city, country FROM city INNER JOIN country ON city.country_id = country.country_id;

Write the INNER JOIN query in which we can see the payment_id in the customer table and payment table and the first_name and last_name names in the customer table together.

SELECT payment_id , first_name, last_name FROM customer INNER JOIN payment ON customer.customer_id = payment.customer_id;

Write the INNER JOIN query in which we can see the first_name and last_name names in the customer table together with the rental_id in the customer table and rental table.

SELECT rental_id , first_name, last_name FROM customer INNER JOIN rental ON customer.customer_id = rental.customer_id;

10

Write the LEFT JOIN query that we can see the city and country names in the city table and country table together.

SELECT city, country FROM city LEFT JOIN country ON city.country_id = country.country_id;

Write the RIGHT JOIN query where we can see the payment_id in the customer table and payment table and the first_name and last_name names in the customer table together.

SELECT rental_id, first_name, last_name FROM customer FULL JOIN rental ON customer.customer_id = rental.customer_id;

Write the FULL JOIN query in which we can see the first_name and last_name names in the customer table together with the rental_id in the customer table and rental table.

SELECT rental_id, first_name, last_name FROM customer FULL JOIN rental ON customer.customer_id = rental.customer_id;

11

Let's sort all data for first_name columns in actor and customer tables.

(SELECT first_name FROM actor)
UNION
(SELECT first_name FROM customer);

Let's sort the intersecting data for first_name columns in actor and customer tables.

(SELECT first_name FROM actor)
INTERSECT
(SELECT first_name FROM customer);

For the first_name columns in the actor and customer tables, let's sort the data in the first table but not in the second table.

(SELECT first_name FROM actor)
EXCEPT 
(SELECT first_name FROM customer);

Let's do the first 3 queries for repeated data.

(SELECT first_name FROM actor)
UNION ALL
(SELECT first_name FROM customer);

(SELECT first_name FROM actor)
INTERSECT ALL
(SELECT first_name FROM customer);

(SELECT first_name FROM actor)
EXCEPT ALL
(SELECT first_name FROM customer);

12

In the movie table, the movie length is shown in the length column. How many movies are longer than the average movie length?

SELECT COUNT(*) as NUMBEROFMOVİES FROM film WHERE length > ANY (SELECT ROUND(AVG(length)) FROM film)

How many movies have the highest rental_rate in the movie table?

SELECT COUNT(*) FROM film WHERE rental_rate = ANY (SELECT MAX(rental_rate) FROM film)

In the movie table, list the movies with the lowest rental_rate and the lowest replacement_cost.

SELECT DISTINCT title FROM film WHERE rental_rate = any (SELECT MIN(rental_rate) FROM film) AND replacement_cost = any (SELECT MIN(replacement_cost) FROM film)

List the customers with the highest number of purchases in the Payment table.

Condition = 1

SELECT first_name,last_name
FROM customer c
JOIN payment p
ON ( p.customer_id = c.customer_id )
WHERE amount = ( SELECT MAX(amount) from payment );

Condition = 2

SELECT first_name, last_name
FROM customer
WHERE customer_id = any
(
SELECT customer_id
FROM payment
WHERE amount = ( SELECT MAX(amount) from payment )
)