Guide to High School Computer Science
  • 💻Introduction
    • windows & Python Development
    • macOS & Python Development
    • Visual Studio Code Settings
    • Set up Github
    • Author Page
  • 🧠Prerequisite Skills
    • Keyboard Typing
    • Files & Directories
    • Use of Command Line
    • Git & GitHub
    • Markdown
    • Starting Your Python Project
  • 🐍Python Programming
    • 🍎Python Basics
      • What is Python?
      • Procedural Programming & Programming Paradigms
      • String Formatting
      • Data Types
      • Input & Output to Console
      • Working with Numbers
      • Useful Built-in Functions
      • Math & Random Module
      • Boolean Data Object
      • Comparison, Logical, and Membership Operators
      • If Statements
      • Binary Decisions
      • Multiple Decisions
      • Nested Conditions
      • [EXTRA] Bitwise Operators
      • [EXTRA] Python Style Guide
    • ⏮️Iterations
      • Introduction to While Loops
      • Infinite Loop
      • Controlling Your While Loops
      • Introduction to For Loops
      • For Loops w/ Numeric Sequences
      • For Loops w/ Strings & Lists
      • Iterable Functions w/ For Loops
    • 📦Collections
      • Strings
        • String Basics
        • String Indexing
        • String Slicing
        • String Operators & Functions
        • Basic String Methods
        • String Methods Extended
        • String Methods Document
      • Tuples & Lists
        • Tuples
        • List Basics
        • List are Mutable
        • Adding Items to a List
        • Removing Items from a List
        • Search & Reverse a List
        • List Comprehension
        • List Methods Document
      • Sets
      • Dictionary
      • How to Store Multiple Data Items
    • 💡Defining Functions
      • Functions
      • print() vs return
      • Pre-determined Arguments
      • Nested Functions
      • Map & Filter
      • [Extra] Dynamic Arguments
    • 💾File I/O
      • How to Save Text to an External File
      • Reading CSV in Python
      • Reading JSON in Python
    • 🔨Basic Python Projects
      • Basic Calculator
        • Improving the calculator
        • Exercise Set 1
        • Exercise Set 2
        • 💎Streamlit Application #1
      • Basic Password Generator
        • Exercise Set 3
        • Exercises Related to Math
        • 💎Streamlit Application #2
      • A To-Do Task List
    • ⏳Introduction to Algorithmic Thinking
      • Big-O Notation
      • Basic Algorithms
        • Linear Search
        • Binary Search
        • Basic Sorting Algorithms
      • Recursion
      • Brute Force Algorithms
      • Greedy Algorithm
        • Time on Task (CCC 2013 J4)
        • Dijkstra’s Algorithm
      • Divide and Conquer
        • Merge Sort
      • Dynamic Programming
    • 🤯Object Oriented Programming
      • Class & Objects (Definitions)
      • OOP in Python
      • Encapsulation
      • Polymorphism
      • Inheritance & Overriding
      • Override Magic Methods
      • Case Study: 2D Vectors
      • Case Study: Deck of Cards
      • Exercise
      • Abstract Data Types
      • Case Study: Static 1D Array From Java
    • Competitive Programming
      • Is This Sum Possible?
        • Is the dataset sorted?
        • Searching for a value
        • Determine if the difference between an integer from the array and the target value exists
        • Sorting Algorithms
        • Using Two Pointers
      • Two Sum - LeetCode
        • Generate all possible pairs of values
        • Subtract each value from the target, see if the difference exists in the list
      • Longest Common Prefix - LeetCode
        • Compare all possible prefixes
        • Create the longest common prefix with the direct neighbour
      • Length of Last Word - LeetCode
        • Compare all possible prefixes
      • Where can I go from one point to another?
      • Sample Outline
    • IB Recipe Book
  • 💾Python & Databases
    • Intro to Databases & Data Modeling
      • Common Data Types in SQL
      • Introduction to ERDs
      • Primary Keys and Foreign Keys
      • Database Normalization
    • What is SQL?
      • Getting Started
      • SELECT Queries
        • Selection with Conditions
        • Selection with Fuzziness
        • Selection and Sorting in Order
        • Selection without Duplicates
        • Selection with Limited Number of Outputs
      • AGGREGATE Queries
        • Counting Rows
        • Sum, Average, Min/Max Queries
        • Working with Aggregate Queries
        • Power of using Groups
        • Exercise
      • Interacting with Multiple Table
      • Inserting Data
      • External Resource
  • ☕Java Essentials
    • Basics
      • Starting Java
      • Data & Variables
      • Handling User Inputs & Type Conversion
      • Arithmetic
      • IPO Model
      • Basic Built-in Methods
      • Exercise Questions
    • Conditionals
      • Boolean Operators
      • Compare Strings
      • If Statements
      • If Else Statements
      • Making Multiple Decisions
      • Using Switch
      • Flowchart Symbols
      • Exercise Questions
    • Iterations
      • While Loops
      • For Loop
      • Exercises
    • Java Type Casting
    • Strings
      • Common String Practices
      • String Formatting
      • Java Special Characters
    • Collection
      • Arrays
      • For Each Loop
      • ArrayList
      • Exercise Questions
    • Static Methods
      • (Aside) Clearing your Console
    • Randomness in Java
    • Delayed Output in Java
    • Java Output Formatting
    • Java Style Guide
  • 🛠️JavaScript Programming
    • Our Programming Editor & Workflow
      • Hello, world!
      • Commenting & Variables
      • Data in JavaScript
      • Operators
      • String Formatting
      • Getting User Input
    • JavaScript Exercise Set 1
    • Making Decisions
      • Comparing Values
      • Combining Boolean Comparisons
      • Creating Branches
    • JavaScript Exercise Set 2
    • While Loops
      • Infinite While Loop
      • While Loops and Numbers
      • While Loops and Flags
      • While loops w/ Strings
    • JavaScript Exercise Set 3
    • Subprograms & Functions
      • Creating a Function in JavaScript
      • Function with Input and Assignable Output
    • JavaScript Exercise Set 4
  • 💾Topics in CS
    • Computer Environments & Systems
      • Computer Components
        • In-depth Explanations
      • File Maintenance
      • Computer & Safety
      • Software Development
      • Bits & Binary
    • Careers related to Computer Science
    • Postsecondary Opportunities
Powered by GitBook
On this page
  • This Tutorial's Database: spicy.db
  • Sorting by Inventory from most to least number of items
  • Exercise
  1. Python & Databases
  2. What is SQL?
  3. SELECT Queries

Selection and Sorting in Order

PreviousSelection with FuzzinessNextSelection without Duplicates

Last updated 10 months ago

This Tutorial's Database: spicy.db

Table Name: hotpeppers; Showing only first 3 peppers

name
maxSHU
inventory

Jalapeno

8000

12

Serrano

5000

7

Habanero

350000

8

There are 23 named hot peppers in the dataset

  • name -> TEXT column of hot pepper names

  • maxSHU -> INTEGER column of each pepper's maximum level of range

  • inventory -> INTEGER column of random counts of how many peppers are available

Sorting by Inventory from most to least number of items

Query

SELECT * 
FROM hotpeppers 
ORDER BY inventory DESC
# Python w/ SQLite
import sqlite3
connection = sqlite3.connect("spicy.db")
cursor = connection.cursor()

query = '''
SELECT * 
FROM hotpeppers 
ORDER BY inventory DESC
'''

result = cursor.execute(query)
print(f"Our wanted pepper data: {result.fetchall()}")

cursor.close()
connection.close()

Output

Our wanted pepper data: [('Fresno', 10000, 19), ('Thai Chili', 100000, 18), ('Chipotle', 8000, 18), ('Piri Piri', 175000, 18), ('Anaheim', 2500, 15), ('Ancho', 2000, 15), ('Carolina Reaper', 2200000, 14), ('Scotch Bonnet', 350000, 14), ('Mulato', 4000, 14), ('Jalapeno', 8000, 12), ('Tepin', 100000, 12), ('Pasilla', 2500, 11), 
('Pepperoncini', 500, 10), ('Hungarian Wax', 15000, 10), ('Habanero', 350000, 8), ("Bird's Eye", 100000, 8), ('Serrano', 5000, 7), ('Ghost Pepper', 1041427, 7), ('Cayenne', 50000, 5), ('Poblano', 2000, 5), ('Chiltepin', 100000, 4), ('Banana Pepper', 500, 2), ('Hatch Chile', 2500, 1)]

We can add an ORDER BY statement to sort based on column. We can also add ASC for least to greatest or DESC for the opposite.

An ORDER BY statement should be written after a WHERE clause

Exercise

  • List the names of peppers in alphabetical order with a query

  • List all the pepper data ordered from least to most spicy

💾
Scoville Heat Unit