Today I am going to write about a very important part of programming, SQL. SQL stands for Structured Query Language. It is a language used by programmers to manage data in relational databases. There are other types of relational based databases but many used today use SQL. There are some that don't, but for today we are going to focus on SQL and how it works with our databases.
When we set up a database, one of the first things we want to set up is our schema. The schema will tell us how pieces of data in our back end are related and organized. There are many tools to help visualize how this works and generally they will look something like this.
This is a general schema picture that I found on google. As we can see it sets up seven tables; employees, jobs, departments, job_history, locations, countries and regions. You might have noticed that some of these tables blocks have arrows pointing at others. This means that they have a relation somewhere being connected by some category or piece of data.
To break this down even more, we can look at the employee table. If we look at this table, it explains to us all that an "employee" is made up of in this database. An employee would have a first_name, an employee_id, job_id, salary, etc. These are all attributes of an employee that would be filled in when creating or modifying one.
These databases can get rather large however. Imagine how many employees would be in the Walmart database for example, or how many users would be in the Twitter database. We need ways to find pieces of data and sometimes we don't know exactly what we are looking for. Maybe we just want employees with a salary under 40000$ a year, or maybe we only want employees with a certain job_id. SQL is a language set up to help us make these queries to find things in our database.
There are many SQL commands that everyone should know if they are going to attempt to play with a database. For the most part these commands are very accessible on the internet, and I'll go over some right now. The most basic of commands would be to find all employees. In SQL the * means all. The command for finding all the employees would be SELECT * FROM employees.
This is a SQL workbench application that I have that I was using for a SQL course. It happens to have a database with employees so it sort of works with the picture (keep in mind all the columns will not match). Which reminds me I did not mention columns yet. If we look at the table in the picture right above where we found all the employees we can see that there are columns with things like firstName, lastName, title, etc. These are the categories that are under each table in the picture at the top. They are referred to as columns.
SQL commands are generally put in a sort of syntax that I think is a little weird. The commands such as SELECT will be all uppercase and the table_names and column_names will be lowercase. Some of the more common commands that one might see would be SELECT column_name FROM table_name. This is fairly straightforward but it becomes more complicated obviously when you need more specified or modified information. Once we are more comfortable with the patterns involved, things like this are actually quite simple.
What this query is trying to do is just get a listing of all the category names of the products and a count of how many products are associated with them. SQL commands are a little funky, but once you get the pattern down it really is rather intuitive.
Top comments (0)