Mada za sehemu hiiImplementing physical database structure with structured query languageMada 5
Implementing Data Query Language (DQL)
Data Query Language (DQL) is the part of SQL used to retrieve or fetch data from a database. The main DQL command is SELECT, which allows you to ask questions about your data and get answers back in the form of a result table.
The SELECT statement is the most important DQL command. It tells the database what information you want to see.
Basic syntax
SELECT column1, column2, ... FROM table_name;
- SELECT — keyword that starts the query
- column1, column2 — names of the columns you want to retrieve
- FROM — keyword that specifies which table to get data from
- table_name — the name of the table
Retrieving all columns
To get every column from a table, use the asterisk (*):
SELECT * FROM table_name;
The WHERE clause filters records that meet a specific condition. It returns only rows that satisfy your condition.
Syntax
SELECT column1, column2 FROM table_name WHERE condition;
Common comparison operators
| Operator | Meaning |
|---|---|
| = | Equal to |
| <> or != | Not equal to |
| > | Greater than |
| < | Less than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| AND | Both conditions must be true |
| OR | Either condition can be true |
Worked example
Consider a products table in a shop database:
| id | name | price | category |
|---|---|---|---|
| 1 | Sugar 1kg | 1500 | Food |
| 2 | Soap | 2000 | Household |
| 3 | Rice 5kg | 12000 | Food |
| 4 | Candle | 500 | Household |
Query: Find all food items
SELECT * FROM products WHERE category = 'Food';
Result:
| id | name | price | category |
|---|---|---|---|
| 1 | Sugar 1kg | 1500 | Food |
| 3 | Rice 5kg | 12000 | Food |
The ORDER BY clause sorts the result set in ascending or descending order.
Syntax
SELECT column1, column2 FROM table_name ORDER BY column_name [ASC|DESC];
- ASC — ascending order (default, smallest to largest)
- DESC — descending order (largest to smallest)
Worked example
Query: List all products sorted by price from highest to lowest
SELECT name, price FROM products ORDER BY price DESC;
Result:
| name | price |
|---|---|
| Rice 5kg | 12000 |
| Soap | 2000 |
| Sugar 1kg | 1500 |
| Candle | 500 |
Aggregate functions perform calculations on sets of rows and return a single result.
Common aggregate functions
| Function | Description |
|---|---|
| COUNT() | Counts the number of rows |
| SUM() | Adds all values in a column |
| AVG() | Calculates the average value |
| MAX() | Finds the maximum value |
| MIN() | Finds the minimum value |
Worked example
Query: What is the total amount spent on all products?
SELECT SUM(price) AS total_price FROM products;
Result:
| total_price |
|---|
| 15000 |
The AS keyword gives a temporary name (alias) to the result column.
The GROUP BY clause groups rows that have the same values into summary rows. It is often used with aggregate functions.
Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;
Worked example
Query: Find the total price for each category
SELECT category, SUM(price) AS total FROM products GROUP BY category;
Result:
| category | total |
|---|---|
| Food | 13500 |
| Household | 2500 |
JOIN combines rows from two or more tables based on a related column.
INNER JOIN
Returns only the rows that have matching values in both tables.
Worked example
Consider two tables:
customers table:
| id | name | city |
|---|---|---|
| 1 | Amina | Dar es Salaam |
| 2 | Juma | Mwanza |
orders table:
| id | customer_id | amount |
|---|---|---|
| 101 | 1 | 25000 |
| 102 | 1 | 15000 |
| 103 | 2 | 8000 |
Query: List each order with the customer's name
SELECT orders.id, customers.name, orders.amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;
Result:
| id | name | amount |
|---|---|---|
| 101 | Amina | 25000 |
| 102 | Amina | 15000 |
| 103 | Juma | 8000 |
A subquery is a query nested inside another query. It is used to perform operations that depend on the results of another query.
Worked example
Query: Find products whose price is above the average price
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
Result:
| name | price |
|---|---|
| Rice 5kg | 12000 |
| Soap | 2000 |
The inner query (SELECT AVG(price) FROM products) first calculates the average price (7500), then the outer query finds products above that value.
| Command | Purpose |
|---|---|
| SELECT | Retrieves data from one or more columns |
| FROM | Specifies the table to query |
| WHERE | Filters data based on conditions |
| ORDER BY | Sorts results (ASC or DESC) |
| GROUP BY | Groups rows for aggregate calculations |
| JOIN | Combines data from multiple tables |
| AS | Gives a temporary name to a column |
| Aggregate functions | COUNT, SUM, AVG, MAX, MIN |
In Tanzania, shop owners use DQL queries daily to make business decisions. For example, a mama lishe at Mwanza market can use SELECT product_name, SUM(quantity_sold) FROM sales GROUP BY product_name to find out which items sell the most each week, helping her order the right amount of stock and reduce wastage of perishable goods like mboga and fruits.
Jinsi utakavyotathminiwaTathmini ya mchakato · Tathmini ya matokeo · Tathmini ya maarifa
Tathmini ya mchakato
- Write SELECT queries to extract required data from a database table.
- Use filtering, grouping, and sorting.
Tathmini ya matokeo
Queries performing correct retrieval of data based on user needs are implemented.
Tathmini ya maarifa
Detailed knowledge of:
Method used: The student should explain how to:
- retrieve data from database tables
- filter or sort data retrieved from the database
Principles: The student should explain the principles of:
- Efficient data retrieval.
Theories: The student should explain:
- Different DQL syntaxes (such as SELECT)
- JOINs, subqueries, and aggregate functions
Circumstantial knowledge:
Detailed knowledge about:
- Query optimization and indexing.
Swali
Which SQL keyword is used to retrieve data from a database table?
Ingia ili kuwasilisha jibu lako na lihesabiwe katika umahiri wako.
Ingia ili kufanya mazoeziMwalimu
Umekwama? Niulize chochote kuhusu mada hii.
Ingia ili kumuuliza Mwalimu wa AI wa Sonza kuhusu swali hili.
Ingia ili kuulizaMajadiliano
Hakuna maswali bado