”;
When you have divided the data in two tables you can fetch combined records from these two tables using Joins.
Example
Assume we have created a table with name CRICKETERS using the following query −
sqlite> CREATE TABLE CRICKETERS ( First_Name VARCHAR(255), Last_Name VARCHAR(255), Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255) ); sqlite>
Let us create one more table OdiStats describing the One-day cricket statistics of each player in CRICKETERS table.
sqlite> CREATE TABLE ODIStats ( First_Name VARCHAR(255), Matches INT, Runs INT, AVG FLOAT, Centuries INT, HalfCenturies INT ); sqlite>
Following statement retrieves data combining the values in these two tables −
sqlite> SELECT Cricketers.First_Name, Cricketers.Last_Name, Cricketers.Country, OdiStats.matches, OdiStats.runs, OdiStats.centuries, OdiStats.halfcenturies from Cricketers INNER JOIN OdiStats ON Cricketers.First_Name = OdiStats.First_Name; First_Name Last_Name Country Matches Runs Centuries HalfCenturies ---------- ---------- ------- ------- ---- --------- -------------- Shikhar Dhawan Indi 133 5518 17 27 Jonathan Trott Sout 68 2819 4 22 Kumara Sangakkara Sril 404 14234 25 93 Virat Kohli Indi 239 11520 43 54 Rohit Sharma Indi 218 8686 24 42 sqlite>
Join Clause Using Python
Following SQLite example, demonstrates the JOIN clause using python −
import sqlite3 #Connecting to sqlite conn = sqlite3.connect(''example.db'') #Creating a cursor object using the cursor() method cursor = conn.cursor() #Retrieving data sql = ''''''SELECT * from EMP INNER JOIN CONTACT ON EMP.CONTACT = CONTACT.ID'''''' #Executing the query cursor.execute(sql) #Fetching 1st row from the table result = cursor.fetchall(); print(result) #Commit your changes in the database conn.commit() #Closing the connection conn.close()
Output
[ (''Ramya'', ''Rama priya'', 27, ''F'', 9000.0, 101, 101, ''[email protected]'', ''Hyderabad''), (''Vinay'', ''Battacharya'', 20, ''M'', 6000.0, 102, 102,''[email protected]'', ''Vishakhapatnam''), (''Sharukh'', ''Sheik'', 25, ''M'', 8300.0, 103, 103, ''[email protected]'', ''Pune''), (''Sarmista'', ''Sharma'', 26, ''F'', 10000.0, 104, 104, ''[email protected]'', ''Mumbai'') ]
Advertisements
”;