Queries

SQL에서 Query문 작성하는 방법에 대해 정리하였습니다.

---

< Introduction >

One of the core purposes of the SQL language is to retrieve information stored in a database.
This is commonly referred to as querying. Queries allow us to communicate with the database by asking questions and returning a result set with data relevant to the question.

 

 

---

< SELECT >

SELECT is used every time you want to query data from a database and * means all columns.

 

< Syntax >

SELECT column1, column2 
FROM table_name;

SELECT *
FROM table_name;

 

 

---

< AS >

AS is a keyword in SQL that allows you to rename a column or table using an alias.

The new name can be anything you want as long as you put it inside of single quotes ' '.

 

< Syntax >

SELECT column1 AS '______'
FROM table_name;

Note :

• Although it’s not always necessary, it’s best practice to surround your aliases with single quotes.
• When using AS, the columns are not being renamed in the table. The aliases only appear in the result.

 

 

---

< DISTINCT >

DISTINCT is used to return unique values in the output. It filters out all duplicate values in the specified column(s).

 

< Syntax >

SELECT DISTINCT column1 
FROM table_name;

 

Example)

SELECT tools 
FROM inventory;

produce :

tools

Hammer

nails

nails

nails

SELECT DISTINCT tools 
FROM inventory;

tools

Hammer

nails

 

 

---

< Where >

To obtain only the information we want.

 

< Syntax >

SELECT *
FROM table_name
WHERE condition;

: WHERE clause filters the result to only include rows where the following condition is true.

 

Example)

SELECT *
FROM movies
WHERE rating > 8;

: The > is an operator. Operators create a condition that can be evaluated as either true or false.

 

< Comparison operators used with the WHERE clause are : >

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

 

 

---

< Like >

LIKE is a special operator used with the WHERE clause to search for a specific pattern in a column.

 

< Syntax >

SELECT *
FROM table_name
WHERE condition LIKE ____;

 

Example1 : select all movies that start with ‘Se’ and end with ‘en’ and have exactly one character in the middle

SELECT * 
FROM movies
WHERE name LIKE 'Se_en';

: The _ means any individual character.

 

Example2 : select all movies that start with ‘A’

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

: The % means zero or more missing letters in the pattern.

 

 

---

< Is Null >

Unknown values are indicated by NULL

 

• It is not possible to test for NULL values with comparison operators, such as = and !=
• Instead, use these operators :
  1. IS NULL
  2. IS NOT NULL

 

Example)

SELECT name
FROM movies 
WHERE rating IS NOT NULL;

 

 

---

< Between >

BETWEEN is a operator used with the WHERE clause to filter the result within a certain range.

 

< Syntax >

SELECT *
FROM table_name
WHERE condition BETWEEN ____ AND ____;

: It accepts two values that are either numbers, text or dates.

Note : when the values are text, BETWEEN filters the result set for within the alphabetical range.

 

Example1 : filters the result to only include movies with years from 1990 up to, and including 1999.

SELECT *
FROM movies
WHERE year BETWEEN 1990 AND 1999;

 

Example2 : filters the result to only include movies with names that begin with the letter ‘A’ up to, but not including ones that begin with ‘J’.

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

: However, if a movie has a name of simply ‘J’, it would actually match. This is because BETWEEN goes up to the second value — up to ‘J’. So the movie named ‘J’ would be included in the result set but not ‘Jaws’.

 

 

---

< And >

AND is a operator to combine multiple conditions in WHERE.
 

 

< Syntax >

SELECT *
FROM table_name
WHERE condition1 AND condition2;

AND operator displays a row if all the conditions are true.

 

Example : return 90’s romance movies.

SELECT * 
FROM movies
WHERE year BETWEEN 1990 AND 1999
   AND genre = 'romance';

 

 

---

< Or >

OR is a operator to combine multiple conditions in WHERE.

 

< Syntax >

SELECT *
FROM table_name
WHERE condition1 OR condition2;

OR operator displays a row if any condition is true.

 

Example : check out a new movie or something action-packed.

SELECT *
FROM movies
WHERE year > 2014
   OR genre = 'action';

 

 

---

< Order By >

To list the data in our result in a particular order.

We can sort the results using ORDER BY, either alphabetically or numerically.

 

< Syntax >

SELECT *
FROM table_name
ORDER BY ____;

SELECT *
FROM table_name
ORDER BY ____ ASC;

SELECT *
FROM table_name
ORDER BY ____ DESC;

• DESC is a keyword used in ORDER BY to sort the results in descending order (high to low or Z-A).
• ASC(default) is a keyword used in ORDER BY to sort the results in ascending order (low to high or A-Z).

Note : ORDER BY always goes after WHERE (if WHERE is present).

 

Example : select all of the movies, sorted from highest to lowest by their year

SELECT *
FROM movies
WHERE rating > 8
ORDER BY year DESC;

 

 

---

< Limit >

LIMIT is a clause to specify(limit) the maximum number of rows the result will have.

 

< Syntax >

SELECT *
FROM table_name
LIMIT ____;

 

Example : specify the result can’t have more than 10 rows.

SELECT *
FROM movies
LIMIT 10;

 

 

---

< Case >

A CASE is a statement allows us to create different outputs (usually in the SELECT statement).
It is SQL’s way of handling if-then logic.

 

Example : Suppose we want to condense the ratings in moviesto three levels

• If the rating is above 8, then it is Fantastic.
• If the rating is above 6, then it is Poorly Received.
• Else, Avoid at All Costs.

SELECT name,
 CASE
  WHEN imdb_rating > 8 THEN 'Fantastic'
  WHEN imdb_rating > 6 THEN 'Poorly Received'
  ELSE 'Avoid at All Costs'
 END AS 'Review'
FROM movies;

• Each WHEN tests a condition and the following THEN gives us the string if the condition is true.
• The ELSE gives us the string if all the above conditions are false.
• The CASE statement must end with END.

+. In the result, you have to scroll right because the column name is very long. To shorten it, we can rename the column to ‘Review’ using AS

 

< result >

name	Review
Man of Steel	Poorly Received
Iron Man 2	Poorly Received
Up	Fantastic
Scary Movie 2	Avoid at All Costs
Back to the Future	Fantastic
The Purge	Avoid at All Costs

 

 

Reference : SQL queries don't start with SELECT

SQL queries don't start with SELECT