Cacher is the code snippet organizer for pro developers

// =============================================== //

   QUERYING - retrieve information

// =============================================== //




## Select name, imdb_rating from movies table ##

SELECT name, imdb_rating FROM movies;



---


## DISTINCT unique value in the specific column ##

SELECT DISTINCT genre FROM movies;


---

## The way to filter queries in SQL is to use the WHERE clause ##

SELECT * FROM movies WHERE imdb_rating > 8;


= equals
!= not equals
> greater than
< less than
>= greater than or equal to
<= less than or equal to


---

## LIKE can be a useful operator when you want to compare similar values ##
## The _ means you can substitute any individual character ##

SELECT * FROM movies
WHERE name LIKE 'Se_en';


---

## matches all movies with names that begin with a ##

SELECT * FROM movies
WHERE name LIKE 'a%';


## matches all movies with names that end with a ##

SELECT * FROM movies
WHERE name LIKE '%a';


## matches all movies with names with the word man ##

SELECT * FROM movies
WHERE name LIKE '%man%';


---

## The BETWEEN operator is used to filter the result set within a certain range. The values can be numbers, text or dates. ##

SELECT * FROM movies
WHERE name BETWEEN 'A' AND 'J';


SELECT * FROM movies
WHERE year BETWEEN 1990 AND 2000;


---


## AND - condition must be true - add genre comedy ##

SELECT * FROM movies
WHERE year BETWEEN 1990 AND 2000
AND genre = 'comedy';



---


## OR if any of the conditions are true then the row is added to the result set. ##


SELECT * FROM movies
WHERE genre = 'comedy'
OR year < 1980;


---


## ORDER BY - sort alphabetically or numerically ##
## DESC - descending order (high to low or Z-A)  ##
## ASC - ascending order (low to high or A-Z)  ##

SELECT * FROM movies
ORDER BY imdb_rating DESC;


---


## filtered results can return thousands of rows ##
## LIMIT the return of the search ##

SELECT * FROM movies
ORDER BY imdb_rating ASC
LIMIT 3;