SQL Part 2

In a SQL table, each column has a data type as an attribute that specifies the type of data of any object in it. Each column, variable, and expression has a related data type. Different types of SQL could have different types of data types but the common ones that exist are -

• INT — Whole number value
• decimal/float — numerical decimal value
• string/char- letters
• varchar- similar to char but a limit of characters can be specified
• boolean — True and False
• datetime — timestamps

Basic Statements with SELECT and FROM

The two commands that you will use the most in a SQL query are the SELECT and FROM statements. SELECT points to the columns that you want to be returned and the FROM command points to which table these columns are in.

Let’s start by looking at a simple query:

SELECT brand,
Country,
stars
FROM ramenratings;

Output Table

So what’s happening in the above query? The query is telling the database to return the brand and Country columns from the table ramenratings. When this query is run, you’ll get back a view of a table with values in each of these columns.

The columns names are separated in the query but a comma between the last column and the FROM statement is not needed.

If you want every column in the table to be returned you can use * instead of column names:

SELECT *
FROM ramenratings;

You might have noticed that the statements are followed by a semicolon(;). The reason for this is to separate the individual statements.

Formatting convention

Noticed that the SELECT and FROM commands are capitalized. This is a convention that makes SQL statements easier to read. SQL will still compute the commands are in lowercase, additionally, SQL treats multiple spaces, tabs, and line break the same. For example, the following two statements are the same.

SELECT * FROM ramenratings

and

SELECT *
    FROM ramen ratings

There are also conventions for linebreaks that make statements easier to read.

Column Names

Another convention is for column names. It is good practice to use lower cases for the names and underscores instead of spaces. The table name itself should also avoid using spaces. The reason for this is that it can become a hassle to deal with spaces in SQL. You would have to put the column names in double quotes every time you reference them.

When writing a query statement, you can rename the column's name. If you want the table to look more presentable by using proper capitalization and spaces, you could do so by typing:

SELECT brand AS “Brand Name”,
stars AS “No. of Stars”
FROM ramenratings

If you simply wrote the query without the double quotes, it would read Brand and Name as separate objects and an error would be raised. Notice how the columns are capitalized, had you simply written SELECT brand as Brand_Name, all the letters in the column name would still be in lower case.

LIMIT

Another useful command is LIMIT. This specifies the number of rows that you want to return in the table. This can be used in tandem with the ORDER BY command, which will be talked about later, to get only the top results. You can also use it to get a preview of the table. When you are working with a large database with a large number of rows, it is a good idea to use LIMIT to get a preview of the table. Else it might take forever for the query to finish executing.

SELECT brand,
       variety,
       stars
       FROM ramenratings
       WHERE stars = 5
       ORDER BY 3 DESC
       LIMIT 5

The above statement returns the 5 five stars rated ramens’ brand, variety, and star ratings.