SELECT, WHERE & ORDER BY

Understand the concept

A relational table holds rows and named columns. SELECT chooses the columns to return; FROM names the table. WHERE filters individual rows using conditions such as score >= 70. ORDER BY sorts the resulting rows; DESC means descending.

Use single quotes for text literals. AND requires both conditions to be true; OR requires at least one. SQL NULL represents missing or unknown information. Use IS NULL or IS NOT NULL to test for it, rather than = NULL.

The example uses SQLite and Python’s built-in sqlite3 module so you can run it without a separate database server. The database lives in memory for this run. Other SQL dialects may differ. For real user input, use query parameters instead of building SQL by joining strings.

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE learners (name TEXT, score INTEGER)")
db.executemany("INSERT INTO learners VALUES (?, ?)",
               [("Asha", 82), ("Ravi", 65), ("Meera", 91)])
rows = db.execute("""
    SELECT name, score FROM learners
    WHERE score >= 70 ORDER BY score DESC
""").fetchall()
print(rows)
db.close()

Output / what to expect

[('Meera', 91), ('Asha', 82)]

Try it yourself

Using the same table, return only names of learners with scores from 65 to 85 inclusive, sorted by name.

SELECT name
FROM learners
WHERE score BETWEEN 65 AND 85
ORDER BY name;
-- Asha, Ravi

Concept samjho

Relational table mein rows aur named columns hote hain. SELECT batata hai kaunse columns chahiye; FROM batata hai table ka naam. WHERE individual rows ko condition se filter karta hai, jaise score >= 70. ORDER BY result ko sort karta hai; DESC ka matlab descending hai.

Text literals ke liye single quotes use karo. AND mein dono conditions true honi chahiye; OR mein kam se kam ek. NULL missing ya unknown information ko represent karta hai. Isko check karne ke liye IS NULL ya IS NOT NULL use karo, = NULL nahi.

Example SQLite aur Python ke built-in sqlite3 module ka hai, isliye alag server nahi chahiye. Database is run ke liye memory mein rahega. Dusre SQL dialects alag ho sakte hain. Real user input ke liye query parameters use karo, strings jodkar SQL mat banao.

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE learners (name TEXT, score INTEGER)")
db.executemany("INSERT INTO learners VALUES (?, ?)",
               [("Asha", 82), ("Ravi", 65), ("Meera", 91)])
rows = db.execute("""
    SELECT name, score FROM learners
    WHERE score >= 70 ORDER BY score DESC
""").fetchall()
print(rows)
db.close()

Output / kya expect karein

[('Meera', 91), ('Asha', 82)]

Ab khud try karo

Isi table se un learners ke sirf names nikalo jinka score 65 se 85 tak hai, dono limits included. Name se sort karo.

SELECT name
FROM learners
WHERE score BETWEEN 65 AND 85
ORDER BY name;
-- Asha, Ravi