-
Basic Functionality 6
-
Lecture1.1
-
Lecture1.2
-
Lecture1.3
-
Lecture1.4
-
Lecture1.5
-
Lecture1.6
-
-
Pandas & Indices 4
-
Lecture2.1
-
Lecture2.2
-
Lecture2.3
-
Lecture2.4
-
-
Joins, Unions & Order By 3
-
Lecture3.1
-
Lecture3.2
-
Lecture3.3
-
-
Group By & Views 3
-
Lecture4.1
-
Lecture4.2
-
Lecture4.3
-
Summary Statistics
By using LIMIT, we can pull only the first N rows. For example, if we want to pull the first 10 rows, we just add LIMIT 10.
#SELECT with LIMIT can pull the top N rows
query = 'SELECT * FROM Curves LIMIT 10;'
df = pd.read_sql(query, conn)
print(df)
Using MAX() and MIN() with different columns will return both the minimum and maximum values for the columns. If, for example, we wanted to get the minimum and maximum values for the Linear1 and Linear 2 curves, this will achieve that:
#When given a column, MIN() returns the minimum
query = 'SELECT MIN(Linear1), MIN(Linear2) FROM Curves;'
df = pd.read_sql(query, conn)
print(df)
print()
print()
#And MAX() returns the maximum
query = 'SELECT MAX(Linear1), MAX(Linear2) FROM Curves;'
df = pd.read_sql(query, conn)
print(df)
COUNT returns the number of records of a given column, but very often is used with a WHERE condition.
#Count can be used to find the number of entries, with or without a given condition.
query = 'SELECT COUNT(Linear1) FROM Curves;'
df = pd.read_sql(query, conn)
print(df)
print()
print()
query = 'SELECT COUNT(Linear1) FROM Curves WHERE Linear1 > 50;'
df = pd.read_sql(query, conn)
print(df)
AVG gives us the average of a given column.
#AVG gives an average, with or without a given condition.
query = 'SELECT AVG(Linear1) FROM Curves;'
df = pd.read_sql(query, conn)
print(df)
print()
print()
query = 'SELECT AVG(Linear1) FROM Curves WHERE Linear1 > 50;'
df = pd.read_sql(query, conn)
print(df)
SUM adds up all the values in a given column. In the context of this particular data it does not make actual sense, but we will show it anyways.
#There is also SUM, with or without a given condition.
#It does not actually make sense in this context though
query = 'SELECT SUM(Linear1) FROM Curves;'
df = pd.read_sql(query, conn)
print(df)
print()
print()
query = 'SELECT SUM(Linear1) FROM Curves WHERE Linear1 > 50;'
df = pd.read_sql(query, conn)
print(df)
Prev
Primary Key
Next
Updating the Model