Retrieving data is one of the most important aspects of database management. SELECT statement is the clause we use for this purpose. This is called making a query to the database. A query is a request for information from the database. Lets just dive in and learn how we retrieve data from the database. Ensure you’re connected to the hr database before we start. In case you don’t know how to connect, you can learn from here.
To make queries (retrieve data), we use the SELECT keyword followed by the columns you need from a particular table (remember data is kept in tables). After this, we proceed the column names with the FROM keyword. This is used to identify the table from which the specified columns are got from. Then the table name proceeds the FROM keyword. Remember the rule, all statements must be terminated.
Retrieving data from specific columns.
Sometimes we just need to retrieve specific columns from the table. We write each column name after the SELECT keyword. Separate each column with a comma until all columns are over. Don’t add a comma after the last column.
Syntax
SELECT column_name, column_name (...) FROM table_name;Example: Retrieve all job titles and their ids from the jobs table (job_id and job_title are the columns in the jobs table to help us with this)
SELECT job_id, job_title FROM jobs;
Retrieving data from all columns
There are some cases we need to retrieve data from all the columns in a table. To do this, we use an asterisk (*) in the place of column names. The asterisk means return data from all columns. No comma is needed this time.
Syntax
SELECT * FROM table_nameExample: Retrieve all data in the departments table.
SELECT * FROM departments;
Retrieve available tables
With the examples above, we’ve learnt how to retrieve data from specific columns and all columns in a table. However, we need to know all the tables available tables to us in this hr sample database. To do this, we are going to use a (virtual) table called tab (We shall learn more about virtual tables later). This table keeps track of all tables available to a connected user. We are going to use our SELECT statement we’ve already learnt and no change is to be made from what you already know. Since we need to know every detail in this table, we shall use the asterisk. Here is the code.
SELECT * FROM tab;
We can see we have a number of tables that we can choose from. You can play around with any table you wish. Ensure you use the right name of the table. This command is so important because it provides you with the spelling of the table you wish to use. Therefore, if you need to confirm the table name, just issue this statement.
DESCRIBE keyword
Have you spotted something yet?? Good. You can’t tell which columns are available in a particular table unless you use the asterisk to retrieve all columns. What if this is not what you want. What if you first want to see the columns in a table before querying data from it ? . Don’t worry. Oracle has got you covered.
We use the DESCRIBE keyword followed by the table name for the table whose columns we need to know. Here is the syntax
DESCRIBE table_name;Let us check for the columns in employees table.
DESCRIBE employees;
With this command, we can easily tell the table columns and their right spellings. It doesn’t only give you a view of the column names but also provides you with all details about a particular table for example, the different data types constraints used for each column. (You don’t need to worry about these for now.)
Column Alias
You may have realised that headings in the output are the exact column names ?. Sometimes, the column names are not meaningful enough to communicate what we need or they don’t look so formal to meet the boss’s standards ?. This is where column alias come into play. Column alias helps us to change the column heading for a specific column in the output.
Note: the column name will remain the same in the database table. It won’t be replaced with a column alias.
To use column alias, we put the desired column heading (referred to as column alias) after the column name whose column heading we need to change.
Syntax
SELECT column_name column_alias FROM table_name;For example, if we need to change column last_name to surname and commission_pct to allowance in the employees table, here is what we do.
SELECT last_name surname, commission_pct allowance FROM employees;
If we wish, we can put the AS keyword next to column name before the column alias. The AS keyword is optional. It helps to distinguish a column name from the column alias.
SELECT column_name AS column_alias FROM table_name;Let us use the previous example using the new format
SELECT last_name AS surname, commission_pct AS allowance FROM employees;
Have you noticed that column headings are returned in uppercase only ? . Oh! You may also have noted that you can’t use more than one word as a column alias for example, product name. What if that’s not what we want. What if we need the column headings are returned in a different case! Don’t worry. We have a fix.
Column alias are wrapped in double quotes if we need to use more than one word for an alias or we want to maintain the letter casing for a column heading. For example, we need to rename commission_pct to employee allowances and should be in lower case. Here is what we do.
SELECT last_name AS Surname, commission_pct AS "employee allowances" FROM employees;
Column alias are unique aspect of data management and help a lot in providing meaningful reports.
Summary
1. SELECT keyword is used to retrieve data from a database table.
2. It is followed by column names. We specify the columns we need and separate them by commas. No comma should come after the last column name. for example SELECT id, first_name FROM employees;
3. We use an asterisk if we need data from all columns in a table for example, SELECT * FROM locations;
4. Table names MUST be correct. We can use the SELECT * FROM tab; command to confirm the right name for the table.
5. Column names MUST be correct. DESCRIBE keyword helps us to identify all column names and the right spellings.
6. Column alias are used to change column headings in the output. The AS keyword is optional in a column alias.
7. Column alias MUST be wrapped in double quotes if it it has more than one word or we need to maintain the letter casing for the alias.
8. Column alias don’t change the column names in the table. It simply changes the column heading in the output.
