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 characters 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
- Python Data Persistence – Constraints
- Python Data Persistence – RDBMS Concepts
- Python Data Persistence – RDBMS Products
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