Question

Consider the following table GAMES. Write a python program to display the records for question (i) to (iv) and give outputs for SQL queries (v) to (viii)? Table: GAMES

Gcode	Name	GamesName	Number	PrizeMoney	ScheduleDate
101	Padmaja	Carom Board	2	5000	01-23-2014
102	Vidhya	Badminton	2	12000	12-12-2013
103	Guru	Table Tennis	4	8000	02-14-2014
105	Keerthana	Carom Board	2	9000	1-01-2014
108	Krishna	Table Tennis	4	25000	12-12-2014

1. To display the name of all Games with their Geodes in descending order of their schedule date. 

2. To display details of those games which are having Prize Money more than 7000.

3. To display the name and gamename of the Players in the ascending order of Gamename.

4. To display sum of PrizeMoney for each of the Numberof participation groupings (as shown in column Number 4)

5. Display all the records based on GameName

Answer 1

1. To display the name of all Games with their Geodes in descending order of their schedule date.

import sqlite3

conn = sqlite3.connect(“Games.db”)

cursor = conn.cursor( )

cursor.execute(“Select GameName, Geode from Games

order by ScheduleDate Desc “)

result = cursor,fetchall( )

print(*result, sep = “\n”)

conn.close( )

Output:

(’Table Tennis’ , 108)

(’Table Tennis’ , 103)

(’Carom Board’ , 101)

(’Carom Board’ , 105)

(’Badminton1, 102)

2. To display details of those games which are having

Prize Money more than 7000.

import sqlite3

conn = sqlit3.connect(“Games.db”) 

cursor = conn.cursor( )

cursor.execute(” Select * from Games where prize money > 7000″)

result = cursor.fetchall( )

print(*result, sep = “\n”)

conn.close( )

Output:

(102, ‘Vidhya’ , ‘Badminton’, 2, 12000, ’12-12-2013′)

(103, ‘Guru’ , ’Table Tennis’ , 4, 8000, ’02-14-2014’) 

(105, ‘Keerthana’ , ‘Carom Board’ , 2, 9000, ’01-01-2014′)

(108, ’Krishna’ , ’Table Tennis’ , 4, 25000, ’03-19-2014′)

3. To display the name and gamename of the Players in the ascending order of Gamename.

import sqlite3

conn = sqlite3.connect(“Games.db”)

cursor = conn.cursor( )

cursor.execute(” Select Name, GameName from games

order by GameName “)

result = cursor.fetchall( )

print(*result, sep = “\n”)

conn.close( )

Output:

(‘Vidhya’ , ‘Badminton’)

(‘Padmaja’ , ‘Carom Board’)

(‘Keerthana’ , ‘Carom Board’)

(‘Guru’ , ‘Table Tennis’)

(‘Krishna’ , ‘Table Tennis’)

4. To display sum of PrizeMoney for each of the Numberof participation groupings (as shown in column Number 4)

import sqlite3

conn = sqlite3.connect(“Games.db”)

cursor = conn.cursor( )

cursor.execute(“Select Sum(Number * Prizemoney) from games”)

result = cursor, fetchall( )

print(result)

conn.close( )

Output:

[(184000)]

5. Display all the records based on GameName import sqlite3

conn = sqlite3.connect(“Games.db”)

cursor = conn.cursor( )

cursor.execute(“Select * from games group by gamename”)

result = cursor. fetchall( )

print(*result, sep = “\n”)

conn.close( )

Output:

(‘Carom Board’ , 101, ‘Padmaja’ , 2, 5000, ’01-23-2014′)

(’Carom Board’ , 105, ‘Keerthana’ , 2, 9000, ’01-01-2014′)

(‘Badminton’, 102, ‘Vidhya’ , 2, 12000, ’12-12-2013′)

(‘Table Tennis’ , 103, ‘Guru’ , 4, 8000, ’02-14-2014′) 

(’Table Tennis’ , 108, ‘Krishna’ , 4, 25000, ’03-19-2014′)