Course Module 4. Working with databases - Lecture: Aliases

Changing column names

We also need to deal with the column names. Otherwise, we repeat the names name and id, but they contain different data. On the other hand, there is the first id column and the employee_id column, which contain the same data.

Let's write a query, where there will be only the necessary columns, and also rename the columns with the same names:

SELECT  
    task.id AS task_id,  
    task.name AS task_desc, 
    task.deadline AS deadline, 
    emploee.id AS emploee_id,  
    emploee.name AS emp_name,  
emploee.occupation AS	
    emp_occupation 
FROM employee, task
WHERE emploee.id = task.emploee_id

And the result of this query:

task_id	task_desc	deadline	emploee_id	emp_name	emp_occupation
1	Fix a bug on the frontend	2022-06-01	1	Ivanov Ivan	Programmer
2	Fix a bug on the backend	2022-06-15	2	Petrov Petr	Programmer
7	Enjoy life	(NULL)	4	Rabinovich Moisha	Director
3	Buy coffee	2022-07-01	5	Kirienko Anastasia	Office Manager
4	Buy coffee	2022-08-01	5	Kirienko Anastasia	Office Manager
5	Buy coffee	2022-09-01	5	Kirienko Anastasia	Office Manager
8	Enjoy life	(NULL)	6	Vaska	cat

Great, the problem with incomprehensible column names has been successfully solved. The query has become a bit long, but everything is clear in the resulting table. And no extra columns.

Table aliases

Sometimes table names are too long and take up a lot of space in the query. Therefore, the creators of SQL, to improve readability, as in the case of columns, offered the ability to specify table aliases.

The general form of aliases (table aliases) is as follows:

FROM table1 alias1, table2 alias2

Let's rewrite our previous query with short aliases:

SELECT  
    t.id AS task_id,  
    t.name AS task_desc, 
    t.deadline AS deadline, 
    e.id AS emploee_id,  
    e.name AS emp_name,  
    e.occupation AS emp_occupation 
    FROM employee e, task t 
WHERE e.id = t.emploee_id

Readability has decreased slightly, but this is because the names of the tables were initially simple and clear. It might as well be like this:

SELECT  
  	task.id AS task_id,  
  	task.name AS task_desc, 
  	task.deadline AS deadline, 
  	emploee.id AS emploee_id,  
  	emploee.name AS emp_name,  
emploee.occupation AS	
  	emp_occupation 
FROM  
  	Microsoft_it_department_employee employee, 
  	Year2022_priority_task task 
WHERE emploee.id = task.emploee_id

And in this case, aliases are already useful, right? ;)

primary key

And one more important information about tables. Remember that we had an employee_id column in the task table? With it, we referenced the employee ID from the employee table.

If we want to refer from one table to the rows of another table, then the referenced table must have a column with an ID, which is also called the primary key - PRIMARY KEY .

Most often, this is a specially added column whose value type is int . When adding records to a table, SQL automatically sets the value of this column.

Then a lot of things are tied to these keys:

linking different tables to each other;
quick search and filtering by id;
data integrity in the database (no references to non-existent id);
deleting data that no one refers to;
and many many others.

By the way, there are situations when a table has a so-called natural key . This is when there is a column whose contents imply uniqueness. For example, we decided to add to the employee table:

The order of their arrival in the company;
Tax number;
Number and series of the passport.

Sometimes database designers use a natural key as the primary key, but most often they are used separately. After all, records can be deleted, changed, and the like.

I suppose you read stories on the Internet when bailiffs hang debts of his full namesake on a person? This is just related to the concept of a unique key. It is very convenient for banks and bailiffs to search for a person by full name and year of birth. And in 99% of cases this is enough to identify a person.

But the remaining <1% are full namesakes, with the same year of birth. In the life of each of us, there are most likely no such people, but on a national scale, there are. In general, if you are writing software or designing a database, then it is useful to know that this can also be the case.