Structured Query Language (SQL) is a language intended to interact with the database (whether a software or a user directly interacting with the database). SQL is not a programming language. This means that it cannot be used for other purposes aside from extracting, manipulation of information from databases and storing data into databases. SQL is also used to construct and manipulate structures inside databases where all the data will be stored.
Learning how to write SQL statement is not that difficult. Writing SQL statement is about translating your data extraction logic into a structured statement that the DBMS would understand. Let us illustrate this point with the given example below.
What we have is a snapshot of the database design made for a company. The Employee table holds important data and information about employees and the EMPLOYEE_SPOUSE table holds data about the spouse of married employees (including the date of their marriage).
The two tables above are related to each other (through the foreign keys declared in the EMPLOYEE_SPOUSE table). These relationships are important to anyone who writes SQL. For the meantime, let us concentrate on practical tips to writing SQL statements a lot easier even for beginners.
As we have said earlier, SQL statements are written with purpose of extracting information from databases. Let us say that a typical report that the software you will build is this -- LIST OF EMPLOYEES THAT ARE SINGLE.
What do we need to think of first for us to write SQL statement?
Definitely, it will be how we see the output information to be like. Just like in programming, not having the output in mind will surely not set a good and defined direction to which we will code a software. So, let us see how do we see the output information to be like.
If you are thinking of an output like what is shown below, then you are doing fine so far. When thinking of the output information, two things you have to take note of
- Structure of the output (columns that are part of the output)
- Information itself (assuming that a snapshot of the data is available to you)
Given that we have a snapshot of data with us and you were able to think of the output information shown above, this shows that you can actually do SQL. Mentally, you were able to extract the data you needed and produce the information we wanted. This mental process just simply needs to be translated into a logic conducive for SQL writing.
Analyzing how you have probably extracted the data you needed to produce the information, you may have observed that you actually looked for employees recorded in the EMPLOYEE table that does not exist in the list of employees in the EMPLOYEE_SPOUSE table. This is quite logical, if I am an employee recorded in the EMPLOYEE_SPOUSE table, that means I have a spouse and married. So if I am not in this list, then I am single.
This logic will form the cornerstone of your SQL statement. Let's go back to your logic and see how easy it is to translate to SQL. First, we looked for employees in the EMPLOYEE table. In SQL, this is simply
SELECT ENUM, NAME, DOB, DNAME
FROM EMPLOYEE
But we know from our discussion that we don't need all the employees, only those that are not in the EMPLOYEE_SPOUSE table. How is this written?
SELECT ENUM, NAME, DOB, DNAME
FROM EMPLOYEE
WHERE ENUM NOT IN ( )
SQL is like English if you have noticed. If you will read our SQL so far, it reads -- "We are getting the ENUM, NAME, DOB, DNAME from the EMPLOYEE table BUT we are only interested in those whose ENUM is not in a list." What will list be? This is the list of employee numbers in the EMPLOYEE_SPOUSE table. How do we get this list?
SELECT ENUM
FROM EMPLOYEE_SPOUSE
Now, plugging it in our draft SQL statement, we will have:
SELECT ENUM, NAME, DOB, DNAME
FROM EMPLOYEE
WHERE ENUM NOT IN ( SELECT ENUM
FROM EMPLOYEE_SPOUSE )
See, writing SQL is not really that difficult.
If you have a mental picture of what the output will look like, and have an extraction logic with you, writing it's SQL statement will just be a breeze.
0 comments:
Post a Comment