Relational Database Systems - A Step Ahead

Walkman gets thirty five years old. Yes, the Walkman referred here is the portable music player that all of us used to use. Though with digital advancement it is obsolete, it still is the pioneer in making music move !! (with us).Kudos to it.

Anwers to the questions of last month, now.

We need to know a very important statement called ‘SELECT’ which is used for querying. Querying helps us to list only those rows that we need to see specifically. To get a result set of rows that we need specifically we need to mention the criteria to be met or that should not be met along with the SELECT statement. A SELECT statement comes with a syntax as below

SELECT column_list( field names) FROM table name

[WHERE clause]

[GROUP BY clause]

[HAVING clause]

[ORDER BY clause];

The database will convert this query into a best possible execution plan which is very efficient with the given conditions and constraints .This optimisation varies with software ,versions and within executions itself.

If we need to know the transaction details of Smitha only, we will use the Select statement like

‘SELECT * FROM transaction where Customer_no = ’C0100’;

The output would be all the columns and all the rows of the transactions of Smitha from the transaction table.

This will list all the records that pertain to customer number ‘C0100’ from the Transaction table.This would be a very long listing of all the field details of the table. We can restrict the field details by specifying them instead of *.Like

When executed would give an output shown below.

Thus the column name listing would restrict the output to contain only those fields that are listed in the select statement.

This will list only the customer number, amount transacted, description and available balance of Smitha whose customer number is c0100.

If we need the transaction for 01/MAY/2014 then we write

‘SELECT customer_no ,amount ,desc ,balance from transaction WHERE customer_no = ’c0100’ and time_stamp = ’01-MAY-2014’;

To get only those rows of Smitha and Travolta we write

‘SELECT customer_no ,amount ,desc ,balance from transaction where customer_no = ’c0100’ or customer_no = ’c5600’;

To get the total expenditure of Smitha with reference to this set of rows we use

SELECT sum(amount) as Amountspent FROM transaction;

The output would be

Amount Spent 34,800

Each one of the above SQL statements have in a sequence, taken us through the process of how queries help to get the report printed on transaction(s) executed by a specific customer including setting criteria for those transaction(s).Eg report for transactions on a particular date by a particular customer. So, now we know how we get a report of all our transactions of our swipes at the end of the month.

By using the SELECT statement the appropriate fields of specific rows belonging to a particular customer are listed out and the report gets generated. And why the mailer or despatcher just needs to know which account number belongs to which customer was explained in the last article.

What if someone enters a negative number for amount?

What if someone enters a customer’s name a 007?

In the next article we will see ‘business rules’.