From trying to learn enough of SQL to attempt today's challenge, I ended up being fascinated by the popular SQL injection vulnerability and race condition problems. We would talk about these topics at a later date. Had to learn SQL on the go today. Coming this far already, I couldn't let something as light as this stop me.
Problem Statement: Here's a link to the problem statement
My Approach for today's challenge:
I learned a few SQL statements,the:
CREATE statement lets you create a DB or a table INSERT statement lets you insert new records in a table SELECT statement lets you select data from a database. The data returned is stored in a result table, called the result-set. DELETE statement lets delete existing records in a table. UPDATE statement lets modify the existing records in a table.
For today's challenge, only the SELECT statement seemed important, so I moved to take a deeper dive on the topic.
I learned a few clauses that go together with this statement like the:
JOIN clause which is used to combine rows from two or more tables, based on a related column between them, FROM clause which is used to specify which table to select or delete data from, LEFT JOIN clause which returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match, ON clause which is used to specify a join condition, ORDER BY clause which is used to sort the result-set in ascending or descending order.
These clauses enabled me to make these more specific selections based on the conditions in the problem statement.
In the end, I was able to put together this compound Query that selects columns from two different tables and joined them based on the relationship they both shared, that is the personID key.
Below is the code snippet of the query.
SELECT Person.firstName, Person.lastName, Address.city, Address.state
FROM Person
LEFT JOIN Address
ON Person.personId=Address.personID
ORDER BY Person.personId;
In plain English it says,
1) select the following columns, the firstName and lastName columns from the Person table. 2)select the following columns, the city, and state columns from the Address table. 3)Returns all records(in a new table) from the left table (Person table), and the matching records from the right table (Address). The result is null records from the right side, if there is no match 4) ON the condition that the personId in the Persons table is equal to the personId on the Address table 5) Lastly, order by the personId