Python Data Persistence – SELECT Statement

Python Data Persistence – SELECT Statement

This is one of the most frequently used SQL statements. The purpose of the SELECT statement is to fetch data from a database table and return it in the form of a result set. In its simplest form SELECT statement is used as follows:

Example

SELECT coll, col2, .., coin FROM table_name;

SQLite console displays data from the named table for all rows in specified columns. SQLite console offers two useful ‘dot’ commands for a neat and formatted output of the SELECT statement. The ‘.header on’ command will display the column names as the header of output. The ‘.mode column’ command will force the left alignment of data in columns.

sqlite> .header on
sqlite> .mode column
sqlite> select name as name, price from products;
name       Price
---------    --------
Laptop   25000
TV          40000
Router   2000
Scanner 5000
Printer   9000
Mobile  15000

You. can use wild card character to indicate all columns in the table.

sqlite> .header on 
sqlite> .mode column 
sqlite> select * from products;
ProductID   Name     Price
 ----------    -------   --------   
       1          Laptop    25000
       2           TV         40000
       3           Router   2000
       4          Scanner   5000
       5          Printer     9000
       6         Mobile     15000

The ORDER BY clause lists selected rows according to ascending order of data in the specified column. The following statement displays records in the Products table in ascending order of price.

sqlite> select * from products order by price;
ProductID          Name              Price
-----------          ---------         ---------     
      3                  Router             2000
      4                  Scanner           5000
      5                  Printer            9000
      6                  Mobile           15000
      1                  Laptop           25000
      2                  TV                  40000

To enforce descending order, attach ‘DESC’ to the ORDER BY clause.

sqlite> select * from products order by name desc;
ProductID             Name              Price
-----------             --------            --------      
       2                     TV                  40000
       4                     Scanner          5000
       3                     Router            2000
       5                     Printer            9000
       6                     Mobile           15000
       1                     Laptop           25000

You can apply the filter on the selection of rows by using the WHERE clause. The WHERE keyword is followed by a logical condition having logical operators (<, >, <=, >=, =, IN, LIKE, etc.). In the following example, only those rows will be selected for which value of the ‘price’ column is less than 10000.

sqlite> select * from products where price<10000;
ProductID            Name           Price
------------         ---------         --------      
      3                    Router          2000
      4                    Scanner        5000
      5                    Printer          9000

A big advantage of the relational model comes through when data from two related tables can be fetched. In our ‘Invoices’ table, we have ProductID as one of the columns that are a primary key of the ‘Products’ table. The following example uses the WHERE clause to join two tables – Invoices and Products – and fetch data from them in a single SELECT statement.

sqlite> select InvID, Products. name, Products.Price,
Quantity 
. . .> from invoices, , Products where invoices.
productID= Products.ProductID; 
InvID            Name           Price             Quantity
-------          --------       ----------         --------
 1                Laptop         25000                 2
  2                 TV               40000                 1
  3                Mobile         15000                 3
  4                Mobile         15000                 1
  5                Printer          9000                   3
  6                   TV              40000                5
  7                Laptop          25000                4
  8                Router          2000                  10
  9                Printer          9000                   2
 10               Scanner        5000                   3

It is also possible to generate a calculated column depending on some operation on other columns. Any column heading can also be given an alias name using AS keyword.
Following SELECT statement displays Total column which is Products. Price*Quantity. The column shows values of this expression is named AS Total.

sqlite > select InvID, Products.: name t Products.
Price, Quantity, Products.Price *Quantity as
Total 
> from invoices, Products where
invoices.productID=Products.ProductID;
InvID            Name            Price            Quantity           Total 
--------        ---------        ----------       -----------      --------
   1            Laptop             25000                2                  50000
   2               TV                 40000                1                  40000
   3            Mobile             15000                3                  45000
   4            Mobile             15000                1                  15000
   5            Printer              9000                 3                   27000
   6             TV                   40000               5                   200000
  7            Laptop              25000               4                   100000
  8            Router               2000                10                   20000
  9            Printer               9000                 2                   18000
 10           Scanner             5000                 3                   15000