Calisota

Challenge:

One of our other analysts isn't familiar with SQL and needs help finding out how many users live in which states. Submit the SQL command used to get the total number of users in the users table who live in California and Minnesota.

NOTE: Send a screenshot of your command and result to syyntax over Slack.
Use the file from Address Book.

Solution:

Using the database I had created in the Address Book challenge I viewed the columns from the users table to refresh my memory:

mysql> SHOW COLUMNS IN users;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| user_id  | int         | NO   | PRI | NULL    | auto_increment |
| username | varchar(52) | NO   | UNI | NULL    |                |
| first    | varchar(52) | NO   |     | NULL    |                |
| last     | varchar(52) | NO   |     | NULL    |                |
| middle   | varchar(24) | YES  |     | NULL    |                |
| email    | varchar(52) | NO   | UNI | NULL    |                |
| street   | varchar(52) | NO   |     | NULL    |                |
| city     | varchar(52) | NO   |     | NULL    |                |
| state_id | int         | NO   | MUL | NULL    |                |
| zip      | varchar(10) | NO   |     | NULL    |                |
| gender   | varchar(8)  | NO   |     | NULL    |                |
| dob      | date        | NO   |     | NULL    |                |
+----------+-------------+------+-----+---------+----------------+

Interestingly, there is a state_id field with an integer datatype, so I will have to SQL join another table to match an actual State name. I took another look at the available tables:

mysql> SHOW TABLES;
+---------------------+
| Tables_in_westridge |
+---------------------+
| countries           |
| courses             |
| degree_types        |
| enrollments         |
| passwords           |
| payment_statuses    |
| programs            |
| roles               |
| roles_assigned      |
| states              |
| term_courses        |
| terms               |
| users               |
+---------------------+

A table names states looked promising so I took a look at its fields:

mysql> SHOW COLUMNS IN states;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| state_id     | int          | NO   | PRI | NULL    | auto_increment |
| state_full   | varchar(128) | NO   | UNI | NULL    |                |
| state_abbrev | varchar(32)  | NO   | UNI | NULL    |                |
| country_id   | int          | NO   | MUL | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Now I have a rough idea on how the SQL query should look. In simple text:

"California or Minnesota" -> states.state_full -> states.state_id -> users.state_id

And translated to a SQL query:

mysql> SELECT count(*) FROM users INNER JOIN states ON users.state_id = states.state_id WHERE states.state_full = "California" OR states.state_full = "Minnesota";
+----------+
| count(*) |
+----------+
|       40 |
+----------+

Unfortunately, even though I had a working query it wasn't being accepted as the correct flag. After speaking with an admin the challenge description was updated with an instruction to provide a screenshot of your working query in order to receive the expected flag. I provided my screenshot and was given the flag of flag{select count(*) from users where state_id=6 or state_id=28;}, so it appears the challenge author hadn't expected that players would leverage a SQL JOIN to solve in a single query.