Topic: SQL

MySQL gives us four functions to round numbers: CEILING, FLOOR, ROUND, and TRUNCATE. These four functions all have different purposes, but together, they can round a number any way you would like. You might already know the definition of these words from your math classes, but you most likely have no idea what it means to truncate. I will discuss them all in detail so that you can take these concepts with you into other languages, as each language has similar functions. We will not need any tables or additional work as MySQL allows us to input integers into our functions.

The creators long ago thought of rounding numbers in a metaphorical way. The ceiling would be the highest of the room and the floor would be the lowest of the room. Why they did not use up and down is beyond my knowledge, but just know that the ceiling is up and the floor is down like you would expect.

Rounding numbers with MySQL

SQL CEILING Function

SELECT CEILING( 1 ); /* returns 1 */
SELECT CEILING( 1.6 ); /* returns 2 */
SELECT CEILING( 1.4 ); /* returns 2 */

The CEILING function rounds the decimal up to the next integer. The decimal is irrelevant in this function as it is always eliminated when the number rounds up to an integer. Of course, this means that you will lose your decimal places.

SQL FLOOR Function

SELECT FLOOR( 1 ); /* returns 1 */
SELECT FLOOR( 2.1 ); /* returns 2 */
SELECT FLOOR( 2.9 ); /* returns 2 */

If CEILING is up, then the FLOOR function is the same but only the number is rounded down. Again, you will lose the decimal place and an integer will be displayed. The decimals are essentially truncated from the number. Truncated simply means to be cut off.

SQL ROUND Function

SELECT ROUND( 1 ); /* returns 1 */
SELECT ROUND( 1.5 ); /* returns 2 */
SELECT ROUND( 1.4635, 1 ); /* returns 1.5 */

The ROUND function you probably already understand from your math classes. You might have known CEILING and FLOOR, but ROUND is by far the most common. Rounding just means to round up from 5 or down from anything less. ROUND is unique because you can tell SQL which position you would like rounded. In our third statement, we have SQL round to the first decimal by placing 1 as our second argument to the ROUND function.

SQL TRUNCATE Function

SELECT TRUNCATE( 1, 0 ); /* returns 1 */
SELECT TRUNCATE( 1.699, 1 ); /* returns 1.6 */
SELECT TRUNCATE( 185, -1 ); /* returns 180 */

The TRUNCATE function is not very common to use. Basically, it strips characters from your number. Why would you want to lose information about your number? There are a few reasons, but you will find those later in your programming career, but we should talk about why it works. You provide the value in the first argument and the position you want to truncate to in the second argument. You can see in the example that our third SQL statement has a negative one. It returns 180 because we truncated the 5, but we put the placeholder 0 in there so the numbers in the integer keep their position.

One of the best ways to test your knowledge of SQL is by taking the SQL Quiz. The SQL test will allow you to see how well you understand certain areas of SQL as well as telling you the areas that you need to improve. The test requires moderate knowledge of the database language, but the advanced parts have been omitted because these tutorials are introductory.

To begin the quiz, click SQL quiz. If you see any parts that confuse you or you disagree with the answer on one of the quiz questions, use the contact form to tell me about it. The SQL quiz is intended to be only for practice and is by no means a testing tool for job opportunities.

The SQL language used is MySQL, which might effect some of the answers on the quiz.

SQL is a language that interacts with the database on the server-side. This dynamite duo has changed the structure of the internet forever. Sure, programming languages like PHP, ASP, and ColdFusion made the internet dynamic, but the efficiency of SQL has revolutionized how user’s interact with websites. Get on Facebook, post a status saying “I love AfterHoursProgramming.com!!!” and you just found yourself indirectly using SQL. Any good website that permits users to provide content to that site should use SQL. We have client-side languages and server-side languages, but where is SQL?

Is SQL a Database?

No! I know it is somewhat complicated, but SQL just talks to a database and tells it what to do and exactly what it needs. Think of how PHP, ASP, or ColdFusion interact with files. SQL is just like that, but it was created for that very purpose unlike the other languages mentioned. SQL is much smaller and faster than them also. It has the power to index and to make itself faster. It can handle large amounts of data and you can organize the data into specific tables, columns, and rows.

This will be an introduction to SQL and will not cover everything about SQL, but we will get some solid knowledge on how to work with it and learn how to become a query master.. However, you don’t need to be crazy advanced to utilize the majority of SQL’s power. Let’s get to it.

SQL’s syntax is a lot different than other web programming languages. It is extremely readable and very specific. In the most basic form, we will only be using SQL to create queries to select or change data (data manipulation). A query is just SQL code designed to do just that. So, we will not deal with creating tables, databases, or indexes and we won’t be deleting them either. I may add a part about them later, but the majority of web hosting companies offer fantastic tools that do those tasks for you. Let’s get to your first SQL query::

One of the most simple queries you will ever write. Look at how easily that can be read. SELECT some asterisk thing FROM some table_name. First, I will be using things like table_name, where that will be a real table name like users, reviews, items, etc. The * (asterisk) simply means everything in SQL. So, in this case, the asterisk means select every column in that table.

What about the capitalized words? I don’t know of exactly what to call these things because they are so arbitrary, they are keywords really. SQL is made up from these keywords and functions, and it is a relatively tiny language compared to server side languages. SELECT tells SQL that this is a statement, but FROM tells SQL where to perform the statement.

Notice how SELECT and FROM and in uppercase. While I should tell you that is how it must be written, it isn’t. SQL isn’t case sensitive in terms of keywords and functions, but please, please, please always write the actual SQL syntax in uppercase. It helps distinguish the SQL from the column names and values. If you do this one simple trick, people will think you know what you are doing. Go ahead, pretend like you do. They won’t notice.

With an intent of data manipulation, you really only have 4 types of SQL statements you can make: SELECT, INSERT, UPDATE, or DELETE. We will first discuss the only way to get information from a database using the SQL command SELECT. SELECT is probably the most common query that you will use. You probably are more concerned with displaying data to the user as opposed to get data from them. Luckily in SQL, the SELECT statement has the easiest syntax to master. First up, we need some information to retrieve. Think of this following table as a table in your database that you just created. Now we are ready to perform master SQL ninja skills.

SQL SELECT STATEMENT

So this is our default table. Nothing really to special about it. It is just a tiny little users with a bad way to store passwords (you should look into hashing passwords if you plan to create a real users table). Now let’s get some data from our table.

SELECT username 
FROM table_name

We already know that SELECT simply tells SQL we want to get information from the database. Then, username is just the name of the column that we want returned. So, if we would have put the column name as “id” we would have returned the 1, 2, and 3. You can add extra column names by putting a comma after the column before the new column name. Next, we have to tell SQL where SELECT data with the FROM keyword, which just gives SQL a table name. That is one simply query, but let’s get more specific

WHERE Keyword

SQL has a built in keyword WHERE that you can give a condition. Pseudo code here: WHERE column_name = “value”. The WHERE keyword is another term you will use very often in your queries, because you probably don’t want to work with every record in the table all the time. WHERE allows you to work with a selected set of records. Let’s steal “theFlanders” password:

SELECT password
FROM table_name
WHERE username = ‘theFlanders’

WHERE is fairly straight forward when you are searching for single exact matches. What if I had 2 or more conditions? We’ll get into the more advanced stuff like that after we introduce all of the SQL Statements. All you would really need to do is add an AND or an OR after the WHERE statement. You can add as many as you want. Go ahead and go crazy.