SQL (2) SELECT – Let's look up data

Let’s look at some of the most basic questionnaires with examples. We will use SQLite Studio and the DB provided by Chadwick Baseball Bureau (see the last article).

■ Basic SELECT Statement

The basic syntax is as follows:

SELECT column-name FROM database-name 

The column name of the data is a header. For example, “People” is a DB is composed of the following format, the top header of the DB is the column name.

You can see how the DB is configured in the SQLite Studio on the left DB list, and if you want to see the detailed data, you can check on the link.

If you want to look up the playerID and birthYear in the People DB, write a query as follows and press the green play button to complete.

The column name can be added with ‘,’ and at the end type ‘;’ to complete the sentence. If you want to load all columns without specifying a specific column, you can use ‘*’ as a column name.

--look up playerID and birthYear from People DB
SELECT playerID, birthYear FROM People; 

--look up all columns from People DB
SELECT * FROM People;  

Note that the case has no effect on the execution of the command. So either select or SELECT is fine. However, SQL queries are typically capitalized because of readability. That is, to distinguish whether the select is a value of the data or a query statement.

■ SELECT Statement – LIMIT: Specify the number of outputs

If the amount of data is large, it is possible to limit the number of data to be viewed with LIMIT. In this case, the output amount is determined according to the number after LIMIT. The resulting values are then shown in the order in which they are stored in the DB.

-- look up 5 data
SELECT playerID, birthYear from People LIMIT 5;

■ SELECT Statement – ORDER BY: sort in ascending or descending order.

Use ASC for ascending order, and DESC for descending order.

If you want to find the heavy player from the DB, you can use ORDER BY to sort in descending order.

-- Sort
SELECT column-name FROM DB-name ORDER BY criteria-column ASC (or DESC);

-- Sort in ascending by 'weight'
SELECT * FROM People ORDER BY weight ASC;

-- Sort in descending order by 'weight'
SELECT * FROM People 
ORDER BY weight DESC 
LIMIT 10;

If you look at the last query, you can see that it is linebreaking. There is nothing wrong with executing commands. Because it is recognized as a sentence until ‘;’ is shown. The example can be briefly explained in one sentence, but in the case of complex queries, the row can be changed appropriately for readability.

When you run the query, you can see that the heaviest player in the DB is Walter Young, who weighs 320 pounds (about 145 kilograms), and the data is well organized by weight.

■ SELECT Statement – WHERE: Filtering conditions

WHERE is useful when looking up certain data.

Basic comparison operators (=, !=, >, >, >==, <=, etc.) are available in WHERE statement. Each condition can be added as ‘AND’, and if the condition value is a letter, put inside ”. If it is a number, you don’t need to put inside ”.

For example, if you want to look up a player whose brithYear is 1990 and birthCountry is USA, you can use it as follows:

-- Filtering
SELECT column-name FROM DB-name
WHERE reference column = 'conditional value'; 

-- Filtering players born in the United States in 1990
SELECT * FROM People 
WHERE birthYear = '1990'
AND birthCountry = 'USA'
ORDER BY weight DESC 
LIMIT 10;

■ SELECT Statement – Aggregate functions (SUM, AVG, MIN, MAX, COUNT)

Basic aggregation functions are also available, along with comparison operators. Think of the Excel function.

-- Aggregate functions
SELECT aggregation-function(column-name) FROM DB-name; 

-- The total weight of players who was born in USA
SELECT SUM (weight) FROM People 
WHERE birthCountry = 'USA';

-- Average weight of players born in the United States
SELECT AVG (weight) FROM People 
WHERE birthCountry = 'USA';

-- The minimum weight of players who was born in USA
SELECT MIN (weight) FROM People 
WHERE birthCountry = 'USA';

-- The maximum weight of players who was born in USA
SELECT MAX (weight) FROM People 
WHERE birthCountry = 'USA';

-- Number of weight data of a player born in the United States
SELECT COUNT (weight) FROM People 
WHERE birthCountry = 'USA';

■ SELECT Statements – |: Look up two or more columns in combination

If you look at People DB, you can see that the first and last names are stored separately. Use ‘||’ when you want to inquire the values of these divided columns together.

-- Look up two or more columns together( linking to a blank space between the column and the column(''))
SELECT Column-name-1 | ' ' || Column-name-2 FROM DB name; 

-- Look up the full name of an athlete weighing more than 300 pounds
SELECT nameFirst | ' ' || nameLast
AS full_name -- Save colum name as full_name. If AS is not used, column name is saved by default with nameFirst | ' |||nameLast . 
FROM People 
WHERE weight >= '300'
LIMIT 5;

If you look at the resulting value above, it looks only full_name. If you want to look up more columns, you can add columns using ‘,’ after SELECT.

■ SELECT Statement – DISTINCT: delete duplicate values

Finally, if you want to eliminate duplication of data, you can use DISTINCT.

-- delete duplication
SELECT DISTINCT(column-name) FROM DB-name;

-- Delete duplicate data from the full_name
SELECT DISTINCT(full_name) from People;