SQL: Inner Join

Note: This is an experimental tutorial that I am building.

Welcome, my name is Alejandro and today we are reviewing SQL Inner Join.

When making a select query, using inner joins, we obtain a combination of rows from our tables. In other words, based on a join condition that matches obtain certain records coming from those tables. In our case, it is going to be between the table A and the table B.

For the table A, we have a list of clients and from the table B; we have a list of services. Both tables have fields known as columns and each column has records known as rows.

You should take particular attention to the field ID in both tables.This field is used to give a unique identifier to each of their records. Also, take attention to the ClientId field in the Services table. Yes. This field is a Foreign Key field, which holds IDs referring to the IDs in the Client table. We are going to match those IDs from both tables when doing the inner join. Below, I included an example of the result table that we wish to obtain.

Let us being by writing a basic select query. First, we are using the wildcard asterisk; this wildcard indicates that we wish to include all the fields, from all tables, used in our query. Second, we have a condition after the keyword ON. This condition will be used by the INNER JOIN to only pull records in which the ID of the client matches the ID in the ClientId fields from the Services table. The rest of the records should be ignored.

As you can see, we have the fields from the table clients, first; then, we have the fields from the services table.

At the Services table, I am showing an example of the record that will not be shown in the result table since there is not a match.

Now, this table does not resemble yet to the result table we wish to obtain. To display our table, as we want it, we need to indicate which fields from which tables we want to display. Plus, we must use the alias keyword AS, which allow us to show a different name for the fields in our result table.

Let’s see this working.

There are may online websites that will allow you to test your queries without requiring installing any software in your computer. One of my favorites is w3schools.com. This website has tutorials, references, quizzes and more.

Go to this link: W3Schools.com

On your right, you can see a list of tables that are at our disposition. We are going to duplicate what we did previously but using the tables Customers and Orders. From the table Customers, we only care about the fields CustomerId and CustomerName. From the table Orders, we only care for the fields OrderId and OrderDate. The following query will give us the results we wish to see:

Notices once again that we use the alias AS keyword to change the name for the column in our results.

Thank you for your time. I hope short instructional was useful for you. Since this is the first tutorial with video of a bunch I am planning to do. Your feedback is appreciated. Please have in consideration that this is a low budget production which I hope to improve in the long run as income and time allows it.

Share

Social Security: Background Checking: Flaws in Website and Database Design

Recently, I was hired by the company IQNavigator. The hiring process involve a background checking done by the company ADP. While ADP was doing my background checking an issue shows up with my Social Security. I would like to use my current personal issue to talk about website and database design.

Continuing with my story, ADP requested me a letter verifying the validity of your social security number. So, I went to the Social Security Administration office at Greeley, CO. The SSA representative told me that they didn’t issue such letter. The only letters they provided was when a change was done to the social security or when requesting a social security number. After some seconds on the computer, the SSA representative notice that while my full name was correctly printed in my social security card, my last name was shorter by one letter in the SSA database system. She fix this issue in the database and gave me a receipt of the transaction.

The SSA transaction receipt was not accepted by ADP; therefore, ADP requested me other documentation. I am going to skip this part of the story with ADP since will move us out of the point I wish to do with this posting. Perhaps, I will write another posting about it.

Later, I decided to create an account at the SSA website without success. The last name field in the form do not allow me to type completely my last name. The field cuts my last name by one letter; therefore, I cannot create an account.

When creating fields on the form and/or in a database, the length should have between 10 to 20 percent extra space of length. This approach is quite common in engineering. For example, the tires of your car display a maximum amount of P.S.I. you can inflate that tire; however, it you inflate the tire to the maximum displayed, it shouldn’t explode in your face. Why? Because a good engineer design the tired to provide a margin above the maximum P.S.I. in case a user exceed the maximum by mistake.

Another lesson is in the SQL query in the software being used by ADP for the social security number verification. I am assuming that they are using my first name, last name, my birthday, and my social security number to make a match. The fact that my last name is missing a letter it could create problems. If their software doesn’t use the same field length limit as used by the SSA, then their query would try to search for “CARLSTEIN RAMOS MEJIA” instead of “CARLSTEIN RAMOS MEJI”.

If the company who build the software which is used by ADP would have the proper communication with the SSA, they could have known the size of each field and prevented this issue to happens.

I hope this doesn’t get in the middle of my employment and any future transactions which requires my Social Security number.

Share

MSSQL Basic Cheat Sheet

Description

Example

Version SELECT @@version
List of Users SELECT name FROM master..syslogins
Current User SELECT user; — Returns user such as “dbo”SELECT user_name(); — Returns user such as “dbo”SELECT system_user; — Returns [DOMAIN]\[USERNAME]SELECT loginame FROM master.sysprocesses WHERE spid = @@SPID;
Privileges SELECT is_srvrolemember(‘sysadmin’);
SELECT is_srvrolemember(‘securityadmin’);
SELECT is_srvrolemember(‘serveradmin’);
SELECT is_srvrolemember(‘setupadmin’);
SELECT is_srvrolemember(‘diskadmin’);
SELECT is_srvrolemember(‘bulkadmin’);
SELECT is_srvrolemember(‘dbcreator’);———————————————————-SELECT name FROM master..syslogins WHERE sysadmin = 1;
SELECT name FROM master..syslogins WHERE securityadmin = 1;
SELECT name FROM master..syslogins WHERE serveradmin = 1;
SELECT name FROM master..syslogins WHERE serveradmin = 1; — May return empty
SELECT name FROM master..syslogins WHERE setupadmin = 1; — May return empty
SELECT name FROM master..syslogins WHERE diskadmin = 1; — May return empty
SELECT name FROM master..syslogins WHERE bulkadmin = 1;
SELECT name FROM master..syslogins WHERE dbcreator = 1;
SELECT name FROM master..syslogins WHERE hasaccess = 1;
SELECT name FROM master..syslogins WHERE denylogin = 0;
SELECT name FROM master..syslogins WHERE isntname = 0;
SELECT name FROM master..syslogins WHERE isntgroup = 0;
List All Databases  SELECT name FROM master..sysdatabases;
Database by Id  SELECT DB_NAME(ID); — Where ID is 0, 1, 2, …, N
Current Database  SELECT DB_NAME();
List Columns SELECT name FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name=’TABLE_NAME’); — Where TABLE_NAME is the table name on the current database
List Tables SELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘U’; — Where DATABASE_NAME is the database which you wish to list the tables
List Views SELECT name FROM DATABASE_NAME..sysobjects WHERE xtype = ‘V’; — Where DATABASE_NAME is the database which you wish to list the views
Hostname SELECT HOST_NAME();
Information and Location of DB file EXEC sp_helpdb DATABASE_NAME; — Where DATABASE_NAME is the name of the database
Share

MySQL Tutorial – Part 3

< (MySQL Tutorial – Part 2) Next | (MySQL Tutorial – Part 4) Next >

MySQL Tutorial : Intermediate

Hurray! Intermediate level! Now, we are talking!
Before continue, I would recommend you to review part 1 and part 2 of this tutorial.

MySQL have many functions for different things such as:

  • String functions
  • Control flow functions
  • Numeric functions
  • Precision mathematics functions
  • Date and time functions
  • Search functions
  • XML functions
  • Bit functions
  • Encryption and compression functions
  • Information functions
  • Miscellaneous functions
  • and more…

In this part of the tutorial, we are going to talk about two groups most commonly used: aggregate functions and scalar functions.

The different between them is that aggregate functions return a single value calculated from values in a column while scalar functions return a single value based on the input value.

Commonly used aggregate functions:

  • MAX(): Returns the largest value in a column
  • MIN(): Returns the smallest value in a column
  • SUM(): Returns the sum of all values in a column
  • AVG(): Returns the average value of all values in a column
  • COUNT(): Returns the number of rows
  • FIRST(): Returns the first value in a column. (NO EXISTENT in MySQL)
  • LAST(): Returns the last value in a column (NO EXISTENT in MySQL)

Commonly used scalar functions:

  • NOW(): Returns the current system date and time based on the input value.
  • ROUND(): Rounds a numeric field to the number of decimals specified based on the input value.
  • LENGTH(): Returns the length of a text field based on the input value.
  • UCASE(): Converts a field to upper case based on the input value.
  • LCASE(): Converts a field to lower case based on the input value.
  • MID(): Extract characters from a text field based on the input value.
  • LEFT(): Extract character from the left side of a text field up to a point indicated by an input value
  • RIGHT(): Extract character from the right side of a text field up to a point indicated by an input value
  • FORMAT(): Formats how a field is to be displayed based on the input value.

Lets begin by looking at the most commonly used aggregate functions:

MAX() Function

The MAX() function will return always the largest value of the selected column.

In the previous part of this tutorial we used the function MAX() as follow:

mysql> SELECT * FROM tbl_users;
+----+------------+-------------+-----------+
| id | first_name | middle_name | last_name |
+----+------------+-------------+-----------+
|  1 | Martin     | Agustin     | Pachecho  |
|  2 | Matias     | NULL        | Delgado   |
|  3 | Jose       | George      | Pachecho  |
+----+------------+-------------+-----------+
3 rows in set (0.00 sec)

mysql> SELECT first_name, last_name FROM db_examples.tbl_users WHERE first_name='Jose' OR last_name='Pachecho';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Martin     | Pachecho  |
| Jose       | Pachecho  |
+------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT first_name, MAX(last_name) FROM db_examples.tbl_users WHERE first_name='Jose' OR last_name='Pachecho';
+------------+----------------+
| first_name | MAX(last_name) |
+------------+----------------+
| Martin     | Pachecho       |
+------------+----------------+
1 row in set (0.00 sec)

In our last selection, we ask MySQL to provide us a result table displaying the ‘first_name’ column and the ‘last_name’ column. The rows to show should first match the first name ‘Jose’ or the last name ‘Pachecho’.
By using the function MAX(), we obtain only one of those results.

As you can see, this example is very confusing.
Lets create a new table which allow us to work with this function and make it more clear how its work:

mysql> CREATE TABLE `db_examples`.`tbl_orders` (
    -> `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `customer_name` VARCHAR( 25 ) NOT NULL ,
    -> `price` INT NOT NULL ,
    -> `date_sale` DATE NOT NULL ,
    -> PRIMARY KEY ( `id` )
    -> );

Now lets upload information to this new table so we can work:

mysql> LOAD DATA LOCAL INFILE '/home/acarlstein/Documents/example_orders.csv'
    -> INTO TABLE db_examples.tbl_orders
    -> FIELDS TERMINATED BY ','
    -> LINES TERMINATED BY '\n'
    -> (customer_name, price, @temp_variable)
    -> SET date_sale = str_to_date(@temp_variable, '%Y-%d-%m');
Query OK, 7 rows affected (0.00 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM tbl_orders;
+----+---------------+-------+------------+
| id | customer_name | price | date_sale  |
+----+---------------+-------+------------+
| 14 | Diego         |   800 | 2009-02-01 |
| 15 | Alejandro     |  1200 | 2008-11-12 |
| 16 | Maria         |  1600 | 2010-04-03 |
| 17 | Maria         |   300 | 2011-03-04 |
| 18 | Diego         |   500 | 2007-06-06 |
| 19 | Diego         |   100 | 2008-03-08 |
| 20 | Alejandro     |   100 | 2009-09-08 |
+----+---------------+-------+------------+
7 rows in set (0.00 sec)

Before continuing, you may notice that we are using the function str_to_date(). We first indicate to LOAD DATA to fill up the customer_name, price and a temporary variable (instead of using the date_sale column).
LOAD DATA will fill up the row using the first two columns (customer_name and price); however, the value its read from the file for the date will go to a variable which will be use later (within str_to_date function) to obtain a date that can be use for the purpose of filling the column ‘date_sale’.

Now that we have the information inside the table ‘tbl_orders’, lets play with it using MAX().

As we explained before,  the MAX() function will return the largest value of the selected column:

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT MAX(price) FROM tbl_orders;
+------------+
| MAX(price) |
+------------+
|       1600 |
+------------+
1 row in set (0.00 sec)

 

The MIN() Function

The MIN() function will return the smallest value of the selected column:


mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT MIN(price) FROM tbl_orders;
+------------+
| MIN(price) |
+------------+
|        100 |
+------------+
1 row in set (0.00 sec)

 

The SUM() Function

The SUM() function returns the total sum of a column. The column must be numeric.

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT SUM(price) FROM tbl_orders;
+------------+
| SUM(price) |
+------------+
|       4600 |
+------------+
1 row in set (0.00 sec)

 

The AVG() Function

The AVG() function returns the average value of all values in a column.

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT AVG(price) FROM tbl_orders;
+------------+
| AVG(price) |
+------------+
|   657.1429 |
+------------+
1 row in set (0.00 sec)

The COUNT() Function

The COUNT() function returns the number of rows.

mysql> SELECT price FROM tbl_orders;
+-------+
| price |
+-------+
|   800 |
|  1200 |
|  1600 |
|   300 |
|   500 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT COUNT(price) FROM tbl_orders;
+--------------+
| COUNT(price) |
+--------------+
|            7 |
+--------------+
1 row in set (0.00 sec)

The FIRST() Function and LAST() Function

The FIRST() function returns the first value in a column while the LAST() function returns the last value in a column.

This sounds wonderful if wasn’t the fact that these two functions do not exist in MySQL.

The reason because they don’t exist is that the data is stored in b-trees; therefore, there are no definitions of how the data is being stored in a table.

However, we can emulate FIRST() and LAST() functions by using ORDER BY (together with ASC or DESC), and LIMIT.
Emulating the function FIRST():

mysql> SELECT price FROM tbl_orders ORDER BY price ASC;
+-------+
| price |
+-------+
|   100 |
|   100 |
|   300 |
|   500 |
|   800 |
|  1200 |
|  1600 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT price FROM tbl_orders ORDER BY price ASC LIMIT 1;
+-------+
| price |
+-------+
|   100 |
+-------+
1 row in set (0.00 sec)

Emulating the function LAST():

mysql> SELECT price FROM tbl_orders ORDER BY price DESC;
+-------+
| price |
+-------+
|  1600 |
|  1200 |
|   800 |
|   500 |
|   300 |
|   100 |
|   100 |
+-------+
7 rows in set (0.00 sec)

mysql> SELECT price FROM tbl_orders ORDER BY price DESC LIMIT 1;
+-------+
| price |
+-------+
|  1600 |
+-------+
1 row in set (0.00 sec)

 

Lets continue by looking at the most commonly used scalar functions:

 

The NOW() Function

The NOW() functions returns the current system date and time based on the input value.

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2012-02-05 19:11:36 |
+---------------------+
1 row in set (0.01 sec)

For example, we could use the function str_to_date() to format the input, NOW(), and update the date of one record.

mysql> SELECT STR_TO_DATE(NOW(), '%Y-%m-%d');
+--------------------------------+
| STR_TO_DATE(NOW(), '%Y-%m-%d') |
+--------------------------------+
| 2012-02-06                     |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM tbl_orders;
+----+---------------+-------+------------+
| id | customer_name | price | date_sale  |
+----+---------------+-------+------------+
| 14 | Diego         |   800 | 2009-02-01 |
| 15 | Alejandro     |  1200 | 2008-11-12 |
| 16 | Maria         |  1600 | 2010-04-03 |
| 17 | Maria         |   300 | 2011-03-04 |
| 18 | Diego         |   500 | 2007-06-06 |
| 19 | Diego         |   100 | 2008-03-08 |
| 20 | Alejandro     |   100 | 2009-09-08 |
+----+---------------+-------+------------+
7 rows in set (0.02 sec)

mysql> UPDATE tbl_orders SET date_sale=STR_TO_DATE(NOW(), '%Y-%m-%d') WHERE id=14;
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM tbl_orders;
+----+---------------+-------+------------+
| id | customer_name | price | date_sale  |
+----+---------------+-------+------------+
| 14 | Diego         |   800 | 2012-02-06 |
| 15 | Alejandro     |  1200 | 2008-11-12 |
| 16 | Maria         |  1600 | 2010-04-03 |
| 17 | Maria         |   300 | 2011-03-04 |
| 18 | Diego         |   500 | 2007-06-06 |
| 19 | Diego         |   100 | 2008-03-08 |
| 20 | Alejandro     |   100 | 2009-09-08 |
+----+---------------+-------+------------+
7 rows in set (0.00 sec)

The ROUND() Function

The ROUND() function rounds a numeric field to the number of decimals specified based on the input value.

Lets say we wish to round the result of a average value obtained from all the orders:

mysql> SELECT AVG(price) FROM tbl_orders;
+------------+
| AVG(price) |
+------------+
|   657.1429 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT ROUND(AVG(price)) FROM tbl_orders;
+-------------------+
| ROUND(AVG(price)) |
+-------------------+
|               657 |
+-------------------+
1 row in set (0.00 sec)

 

The LENGTH() Function

The LENGTH() function returns the length of a text field based on the input value.

mysql> SELECT
    -> customer_name,
    -> LENGTH(customer_name) AS 'Number of Characters'
    -> FROM tbl_orders;
+---------------+----------------------+
| customer_name | Number of Characters |
+---------------+----------------------+
| Diego         |                    5 |
| Alejandro     |                    9 |
| Maria         |                    5 |
| Maria         |                    5 |
| Diego         |                    5 |
| Diego         |                    5 |
| Alejandro     |                    9 |
+---------------+----------------------+
7 rows in set (0.00 sec)

Notice that I had introduce something new, the AS keyword. The AS keyword is an Alias. In this case we are saying that ‘Number of Characters’ is an alia for LENGTH(customer_name).
In the next part of these tutorial, we will go in more detail.

 

The UCASE() Function

The UCASE() function converts a field to upper case based on the input value.

mysql> SELECT customer_name FROM tbl_orders;
+---------------+
| customer_name |
+---------------+
| Diego         |
| Alejandro     |
| Maria         |
| Maria         |
| Diego         |
| Diego         |
| Alejandro     |
+---------------+
7 rows in set (0.00 sec)

mysql> SELECT UCASE(customer_name) FROM tbl_orders;
+----------------------+
| UCASE(customer_name) |
+----------------------+
| DIEGO                |
| ALEJANDRO            |
| MARIA                |
| MARIA                |
| DIEGO                |
| DIEGO                |
| ALEJANDRO            |
+----------------------+
7 rows in set (0.00 sec)

 

The LCASE() Function

The LCASE() function converts a field to lower case based on the input value.

mysql> SELECT customer_name FROM tbl_orders;
+---------------+
| customer_name |
+---------------+
| Diego         |
| Alejandro     |
| Maria         |
| Maria         |
| Diego         |
| Diego         |
| Alejandro     |
+---------------+
7 rows in set (0.00 sec)

mysql> SELECT LCASE(customer_name) FROM tbl_orders;
+----------------------+
| LCASE(customer_name) |
+----------------------+
| diego                |
| alejandro            |
| maria                |
| maria                |
| diego                |
| diego                |
| alejandro            |
+----------------------+
7 rows in set (0.00 sec)

 

The MID() Function

The MID() function extract characters from a text field based on the input value.

mysql> SELECT MID('12EAS678P', 2, 4);
+------------------------+
| MID('12EAS678P', 2, 4) |
+------------------------+
| 2EAS                   |
+------------------------+
1 row in set (0.00 sec)

 

The LEFT() Function

The LEFT() function extract characters from the left side of a text field to a point indicated by input value.

mysql> SELECT LEFT('12EAS678P', 3);
+----------------------+
| LEFT('12EAS678P', 3) |
+----------------------+
| 12E                  |
+----------------------+
1 row in set (0.00 sec)

 

The RIGHT() Function

The RIGHT() function extract characters from the right side of a text field to a point indicated by input value.

mysql> SELECT RIGHT('12EAS678P', 3);
+-----------------------+
| RIGHT('12EAS678P', 3) |
+-----------------------+
| 78P                   |
+-----------------------+
1 row in set (0.00 sec)

 

The FORMAT() Function

The FORMAT() function formats how a field is to be displayed based on the input value.
The field is formatted to a format like '#,###,###.##'. Numbers are rounded to D decimal places. Then, the result is returned as a string.
If D decimal places is 0, the result has no decimal point or fractional part.

mysql> SELECT customer_name, price FROM tbl_orders;
+---------------+-------+
| customer_name | price |
+---------------+-------+
| Diego         |   800 |
| Alejandro     |  1200 |
| Maria         |  1600 |
| Maria         |   300 |
| Diego         |   500 |
| Diego         |   100 |
| Alejandro     |   100 |
+---------------+-------+
7 rows in set (0.00 sec)

mysql> SELECT customer_name, FORMAT(price, 2) FROM tbl_orders;
+---------------+------------------+
| customer_name | FORMAT(price, 2) |
+---------------+------------------+
| Diego         | 800.00           |
| Alejandro     | 1,200.00         |
| Maria         | 1,600.00         |
| Maria         | 300.00           |
| Diego         | 500.00           |
| Diego         | 100.00           |
| Alejandro     | 100.00           |
+---------------+------------------+
7 rows in set (0.00 sec)

 

< (MySQL Tutorial – Part 2) Next | (MySQL Tutorial – Part 4) Next >

Share