forked from theyogicoderRI/PythonMadeEasy
-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathselects.py
More file actions
76 lines (62 loc) · 2.1 KB
/
selects.py
File metadata and controls
76 lines (62 loc) · 2.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
import sqlite3
import csv
conn = sqlite3.connect('first_db.db')
c = conn.cursor()
#select firstname only
c.execute("SELECT fname from people")
for name in c:
print("First Name Only ", name)
print()
#select firstname and lastname only
c.execute("SELECT fname, lnam from people")
for name in c:
print("First namd and Last Name: ", name)
print()
#select firstname and lastname order by lastname in descending order
c.execute("SELECT fname, lnam from people ORDER BY lnam DESC")
for name in c:
print("Order by DESC ", name)
print()
#select firstname and lastname and age order by lastname in descending and the age ascending
c.execute("SELECT fname, lnam, age from people ORDER BY lnam DESC, age ASC")
for name in c:
print("Order by 2 different Criterion: ", name)
print()
#sort by column position
c.execute("SELECT fname, lnam, age from people ORDER BY 3")
for name in c:
print("Sort by Position: ", name)
print()
#using Distinct - remove duplicates
c.execute("SELECT DISTINCT fname, lnam FROM people")
for name in c:
print("Distinct: ", name)
print()
#using Distinct - remove duplicates and then
#remove further dubplicates from the subset and sort in ascending order
c.execute("SELECT DISTINCT fname, lnam FROM people")
c.execute("SELECT DISTINCT lnam FROM people ORDER BY lnam ASC")
for name in c:
print("Compund Distinct: ", name)
print()
# using Where clause
c.execute("SELECT fname, lnam, age FROM people WHERE age > 50")
for name in c:
print("Where Clause: ", name)
print()
# where using And
c.execute("SELECT fname, lnam, age FROM people WHERE lnam = 'Jones' AND fname = 'Joey' ")
for name in c:
print("Where with And: ", name)
print()
# using Limit
c.execute("SELECT fname, lnam, age FROM people Limit 2 ")
for name in c:
print("Using Limit: ", name)
print()
# using Limit and Offset
c.execute("SELECT fname, lnam, age FROM people Limit 2 OFFSET 5 ")
for name in c:
print("Limit and Offset: ", name)
print()
conn.close() #close the connection to the db