SQL is a very organized, functional but tricky language. It is tricky because you have to logically think about which things or variables need to be factored in in order to smartly obtain important data from a table/database. I learnt a lot in one of my projects about handling some of the functions used in SQL and how they are used so that we can query a dataset with relevant data. It does get tricky, lets see some other commands used in this very interesting language: JOINS!There are some basic commands in SQL about having to retrieve only some number of cols of the table, or the entire table, or just some records (or rows) of the table, and these commands are: SELECT, FROM, WHERE, HAVING, ORDER BY. But what if you have more than one table? And you want to find out what the relationship between these two table's are? For example, lets say you have two tables: the customers table and a person table. If you want to filter out the commonalities between these two tables (which records consist of the same individual, for example), you could use Joins. If two tables had a common column or variable, you can merge these two tables together to gather valuable information. So the table to the left is the Customers database, and the table to the right represents the Persons database. Lets say the problem was to find out which individuals have been a customer to a particular store. How would you go about solving this? The common column or key between this two tables is the 'CustomerID'/'PersonID' column. We can use this column to essentially 'match' the tables, and collect valuable info as to the people who have shopped in a particular store. So to query the common keys between the tables is to use the "INNER JOIN" command. The INNER JOIN outputs the common records which share the same ID/key value between two or more tables. So using the INNER JOIN command here would give you: Querying the data
The Inner Join deals with finding the common keys between tables, but there are many many more JOIN types! (Such as Left Join, Right Join, Self Joins, Anti Join, Semi Join..etc. It is absolutely an endless but exciting world of SQL.
0 Comments
A lot of people say "S..Q..L", by separating the letters or some pronounce it as "sequel". It's up to you, I like "sequel", it just sounds cooler. SQL is a superrrrrrrrrrr important skill to learn when dealing with huge amounts of data sets. SQL, which stands for Structured Query Language, is a programming language used for retrieving data and manipulating the data models (aka in table format). To specify that, it is used to extract data from a relational database. A relational database is basically a bunch of tables put together. This skill is very useful for applications in machine learning, when you are dealing with a vast amount of data sets and you need a way to extract the important information so you can predict things with future data. In a table, the rows are the records of the database, and the columns are the fields or parameters of the data. So for example, if we were dealing with a table of movies, then the fields of a movie can be 'genre', 'rating' and 'duration'. Every row, or record, would consist of a single movie. The 'SELECT' and 'FROM' commandsJust like any other programming language, SQL comes with certain syntax. The syntax is used to gather specific information from a huge table, so you can process the most important and valuable information you need. The SELECT and FROM commands are one of the foundational commands of SQL, used to extract the columns of a table. The commands in SQL can be in uppercase or lowercase, but most people prefer uppercase since its easier to distinguish from the column and table names. For instance, if we had a table featuring regular customers in a store, and we want to extract the name column of the customers table, we would write: SQL Tutorial 1
Suppose our customer database kinda looks like the one above. The above commands are basically focusing on the 'name' column in the table, highlighted in orange. What if you also wanted to take in the age aspect of the customers into consideration? If you want to select more than one column of a table, you simply have to add a comma to the SELECT statement and type in the additional column right next to it. Also, at the end of every SQL statement, we have to add a semicolon for the compiler to understand that the SQL commands are over. If you want to select all of the columns of the table (so you are essentially selecting the whole table), you need to type in the asterisk symbol next to SELECT like this: ' WHERE', 'LIKE' and 'AND' commandswhat if you want to make your commands even more specific and extract those details from large datasets? What if you want to get the names of the customers that start with the letter A? Or what if you wanted to get the customers who are 18-25 years old? To filter a dataset, you can use the WHERE command. The 'WHERE' command allows you to filter the data in order to get the information you need. So, if you want to get the names of the customers who are in 18-25 age range, you first select all of the column 'name' from the customers table. Then, you use WHERE to get the names of the customers who are 18 years old or older. But we have a boundary. We need to get the customers who are younger than 25 AND older than 18. This is where the AND command comes in. The AND command allows you to combine conditions of a particular field. So since we need to find customers who are older than 18 but younger than 25, we can use the AND command to restrict the range of the age. To get the customers who are 18-25 years old
That's a wrap for SQL! These are like the simple, intro commands; there are lots more!!!!!!!!!!!!!!!!!
|
Archives
December 2020
Topics
All
|