SQL Data Query Language Statements

In this chapter you will learn how to use the available Data Query Language statements in retrieving data from database tables. Through SQLiteStudio, you will be able to use SELECT, WHERE, ORDER BY and GROUP BY statements in requesting and displaying significant database information.
Once you have created and populated your tables with data values, there will come a time that you will need to perform database queries to retrieve relevant information. A query is a valid inquiry into the database to extract and display data in a readable or understandable format, depending on the user’s request. The main challenge in SQL is to correctly instruct the computer what to search for by manipulating the database through row selection. Once you have selected the values you need then you can further perform various operations such as data addition, deletion, modification and more.

SELECT Statement

Retrieving data values is the most performed manipulation task by database users. In doing such operation you need to use the DML command statement called SELECT. You have the option to retrieve just one row, a number of rows or all the rows of the database table.
Using the SELECT statement in retrieving all the records of a particular table is the basic form of this DML command statement. Even if the SELECT command is considered to be the most powerful statement, it requires other clauses to function correctly in performing a query. The syntax in its simplest form is:

SELECT * FROM TABLE_NAME;
In the programming line above, the asterisk sign (*) signifies everything. This means that the wildcard character is a shortcut for the listing of all the column names of a particular table.
To select all the data rows from the Customer_TBL table:

1. Click the SQL Editor option and then type the following lines of code:
   SELECT * FROM Customer_TBL;


2. Click the EXECUTE QUERY button displayed inside the GRID VIEW tab.

The result of this SELECT command is The result basically shows the entire data of the Customer_TBL table since the code instructs the database to select all the rows and columns of the said table.

WHERE Statement

When you want to be more specific in selecting rows of data from your database tables then you need to add a bit of complexity to your programming lines. At this point, you need the function of the WHERE clause, which means that the SELECT operation will be performed once the stated condition inside such clause is true. The syntax of the SELECT statement with the WHERE clause is as follows:
SELECT COLUMN_LIST
                   FROMTABLE_NAME                              WHERECONDITION;
To select only the rows of data where the job position of the customer is Vice-President:

1. Click the option and then type the following lines of code:

SELECT *
FROM Customer_TBL
WHERE JobPosition = ‘Vice-President’;

2. Click the EXECUTE QUERY button displayed inside the GRID VIEW tab.

The result shows the records of the two customers named Kevin Lord and Mike Armhs who are both Vice-Presidents of their respective companies.
What if you only want to select certain columns of the table, maybe just the full name and company of the customer? You will now modify your lines of code into the following:

1. In the QUERY tab, change the wildcard character * (asterisk sign) into CustomerName and CompanyName by typing the following:

SELECT CustomerName, CompanyName
FROM Customer_TBL
WHERE JobPosition = ‘Vice-President’;

2. Click the EXECUTE QUERY button displayed inside the GRID VIEW tab.

The result of this SELECT command is By specifying the columns you want to select, you are trying to customize what data you want to retrieve and how you want them to be displayed. In the previous example, you only wanted to know the customer’s name and his company where the job position is vice-president.