This is a direct continuation of my previous article Introduction to SQL.
SQL works on a structure with four components: Table, Field, Row, and Column.
You can think of those components exactly like the ones in spreadsheets like Excel.
A database can have one or more tables in it.
Table
A table is a very common and simple way to organize data.
You have columns describe the kind of information and rows/records consisting of the information itself.
The following two tables Employees and Departments are the ones I will use to demonstrate the capabilities of SQL and how to manage data.
The Employees table contains data about the employees of a certain company.
Employees
| employee_id | name | job_title | salary | hire_date | department_id |
|-------------|-------|---------------|--------|------------|---------------|
| 1 | Bob | Engineer | 80000 | 2015-04-12 | 2 |
| 2 | Mary | Designer | 60000 | 2017-06-21 | 2 |
| 3 | Sarah | Sales Manager | 98000 | 2013-09-05 | 1 |
| 4 | Peter | IT Manager | 95000 | 2010-09-05 | 2 |
| 5 | Brian | HR Manager | 92000 | 2012-09-05 | 3 |The Departments table describes de departments the company has.
Departments
| department_id | department_name | city | country | manager_id |
|---------------|-----------------|-----------|---------|------------|
| 1 | Sales | São Paulo | Brazil | 3 |
| 2 | IT | Barcelona | Spain | 4 |
| 3 | Human Resources | New York | USA | 5 |Field
A field specifies the kind of information a particular column has and its type.
The Employees table has the following fields:
- employee_id: de unique ID that identifies an employee
- name: the name of the employee
- job_title: the formal title the employee has inside the company
- salary: how much the employee makes yearly
- hire_date: when the employee was hired
- department_id: the unique ID of the department where the employee works
The Departments table has the following fields:
- department_id: the unique ID for the Department
- department_name: the name of the Department
- city: the city where the Department is located
- country: the country where the Department is located
- manager_id: the ID of the manager responsible for the department, the manager is an employee
Row
A row is a record of data inside the Table.
The Employee table has 5 rows.
The first row is Bob\’s record.
Notice the row with the identifications of the fields does not count as "first" row, the counting begins with the first record.
| employee_id | name | job_title | salary | hire_date | department_id |
|-------------|-------|---------------|--------|------------|---------------|
| 1 | Bob | Engineer | 80000 | 2015-04-12 | 2 |Column
A column is a field and all of its information.
The column employee_id in the Employees table has all the unique IDs of all the employees.
| employee_id |
|-------------|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |