Basic SQL queries
AdvancedThe SQL query skeleton: SELECT fields FROM table WHERE condition ORDER BY field DESC. * means all fields; text values take single quotes '3A'; AND/OR combine conditions; DESC descending, ASC ascending.
- SQL is the standard language for databases; the query skeleton is SELECT fields FROM table.
- * means all fields; picking only the needed fields keeps results clean.
- WHERE filters records: text values take quotes '3A', numbers are bare; equality uses one =.
文體小知識：編程教學課每個概念都配一段可以親手試的代碼例子，照住輸入、改一改，最快學會。
§Text Passage
1SQL (Structured Query Language) is the standard language for talking to a database: one SQL sentence states what you want, and the database finds it. The most basic pattern: SELECT fields FROM table. In SELECT * FROM students; the star * means all fields, and the whole sentence means 'take every field of every record from the students table'.
2To see only some fields, list them: SELECT name, score FROM students; returns just the name and score columns. To see only some records, add a WHERE condition: SELECT * FROM students WHERE class = '3A'; returns only rows of class 3A. WHERE takes comparison operators =, >, <, >=, <=, and AND/OR to combine conditions.
3Results can be sorted too: ORDER BY score DESC puts scores high to low (DESC = descending), ASC ascending (the default when nothing is written). To see only the top few, sort and then LIMIT 3 returns the top three. One full query can use all these parts at once.
4SQL has a writing convention: keywords (SELECT, FROM, WHERE) in capitals, table and field names as they are, text values in single quotes '3A', number values bare like 80. The trick to reading any SQL is to parse its structure: what fields does SELECT take, from which table in FROM, and how do WHERE and ORDER BY filter and sort?
1Summary 重點摘要
The SQL query skeleton: SELECT fields FROM table WHERE condition ORDER BY field DESC. * means all fields; text values take single quotes '3A'; AND/OR combine conditions; DESC descending, ASC ascending.
2Vocabulary cards Vocabulary
The standard language for talking to databases — querying, inserting, updating and deleting data.
The SQL keyword choosing which fields to return; SELECT * means all fields.
The SQL keyword naming which table the data comes from.
Sets a condition so only matching records return, e.g. WHERE score >= 80.
The symbols compared in WHERE: =, >, <, >=, <=; SQL tests equality with a single =.
Combines conditions: AND requires all to hold; OR requires any one.
Sorts results by a field: DESC high to low, ASC low to high (the default).
Returns only the first few rows, e.g. LIMIT 3 for the top three after sorting.
The star after SELECT, meaning every field of the table.
3Step by step Steps
- Your first SQL: the whole tableSQL (Structured Query Language) is the standard language for talking to databases: one sentence states what you want, and the database finds it. The first sentence: SELECT * FROM students;, where the star means every field. If the students table holds 5 records (Mary 3A 85, John 3B 90 and so on), this returns all 5 rows and all columns. SELECT names the fields, FROM names the table, and a semicolon closes the sentence; keywords are capitalised by convention, keeping them distinct from table and field names.
- Choosing fields and filtering rowsTake only the fields you need: SELECT name, score FROM students; returns just the name and score columns, with class and student number hidden — a cleaner result. Take only some rows by adding WHERE: SELECT * FROM students WHERE class = '3A'; returns only the 3A rows. Note that text values take single quotes, '3A', while numbers stand bare as score = 85. The comparison operators =, >, <, >=, <= are available; WHERE score >= 80 keeps every record scoring 80 or more.
- Combining conditions with ANDAND demands that both conditions hold: SELECT * FROM students WHERE class = '3A' AND score >= 80; keeps only rows of class 3A scoring 80 or more. Mary-in-3A with 85 qualifies; John in 3B with 90 has the score but the wrong class, so he is filtered out; a 60 in 3A misses on the score. Combined conditions are the heart of precise queries — OR instead keeps a row when either condition holds. Write each condition complete in itself and place the connector between them, keeping the layers clear.
- Sorting with ORDER BYORDER BY sorts the result: SELECT name, score FROM students ORDER BY score DESC; lines every student up from high to low, John 90 first and Mary 85 second. DESC is descending, ASC ascending, with ASC the default when nothing is written. Sorting never changes the data stored in the database — only the display order of this one query. To see the top few, sort and then add LIMIT: ORDER BY score DESC LIMIT 3 returns only the best three, a podium with room for exactly three.
- Assembling one full queryFour parts assemble a complete query: SELECT name, score FROM students WHERE score >= 80 ORDER BY score DESC LIMIT 3;. Read it piece by piece: SELECT name, score takes names and scores; FROM students names the table; WHERE score >= 80 keeps 80 or above; ORDER BY score DESC sorts high to low; LIMIT 3 shows only the top three. The trick to reading any SQL is parsing in the order SELECT, FROM, WHERE, ORDER BY, LIMIT, with each clause answering exactly one question.
- Writing conventions and formatSQL writing conventions: keywords SELECT, FROM, WHERE and ORDER BY in capitals; table and field names exactly as they are; text values in single quotes like '3A', number values bare like 80; and a semicolon at the end. These conventions are not enforced — most databases accept lowercase keywords — but capitalised keywords make the sentence's structure obvious at a glance, which matters greatly in teamwork. Indentation helps too: long queries put WHERE and ORDER BY on fresh lines, greatly improving readability and making conditions harder to miss when editing.
- Databases in daily lifeDatabases are everywhere: school enrolment systems, library loan records, online shop orders, streaming catalogues — behind each stands a set of tables. Clicking 'price low to high' on a shopping site runs exactly ORDER BY price ASC; 'four stars and up' runs WHERE rating >= 4. Once you know SELECT, WHERE and ORDER BY, the logic beneath almost every search interface becomes visible — one of the most job-realistic skills in the ICT curriculum.
4Diagrams Diagrams
5Worked example Example
Example: the students table has fields student_id, name, class, score. Write one SQL sentence listing the names and scores of 3B students on 70 or above, sorted high to low.
- Decide the fields: name and score → SELECT name, score.
- Decide the source: the data lives in the students table → FROM students.
- Decide the condition: class 3B and at least 70 → WHERE class = '3B' AND score >= 70 (text '3B' takes quotes, the number 70 does not).
- Decide the order: high to low → ORDER BY score DESC. Assembled: SELECT name, score FROM students WHERE class = '3B' AND score >= 70 ORDER BY score DESC;
- Answer: SELECT name, score FROM students WHERE class = '3B' AND score >= 70 ORDER BY score DESC;
6Common mistakes Mistakes
7Quick check Quick check
Q1In SELECT * FROM students;, what does the * mean?
Q2To return only records scoring 80 or above, add which clause? A. ORDER BY B. WHERE score >= 80 C. SELECT 80
Q3In ORDER BY score DESC, what does DESC mean?
Q4Which records does WHERE class = '3A' AND score >= 80 return?
Q5Write one SQL sentence listing every student's name from the students table in alphabetical order.
8Recap Recap
- SQL is the standard language for databases; the query skeleton is SELECT fields FROM table.
- * means all fields; picking only the needed fields keeps results clean.
- WHERE filters records: text values take quotes '3A', numbers are bare; equality uses one =.
- AND needs every condition to hold; OR needs any one.
- ORDER BY sorts: DESC high to low, ASC low to high (default); LIMIT keeps the first few rows.