Skip to main content

🔍 Database Queries

Database queries are powerful tools for extracting specific information from databases. They allow users to filter, sort, and analyze data according to various criteria, transforming raw data into meaningful information.

🧩 Understanding Database Queries​

📊 What is a Query?​

  • A request for data or information from a database
  • A way to extract specific records that meet certain criteria
  • A tool for performing calculations on database data
  • A method for viewing data from multiple related tables simultaneously

🎯 Purpose of Queries​

  • Retrieve only the data you need from large datasets
  • Answer specific questions about your data
  • Perform calculations and analysis
  • Prepare data for reports and forms
  • Update, add, or delete records in bulk

🛠️ Types of Database Queries​

🔎 Select Queries​

  • Most common type of query
  • Retrieves data from one or more tables
  • Allows filtering, sorting, and calculations
  • Returns a recordset that can be viewed, printed, or exported
  • Does not change the underlying data

Example select query:

SELECT FirstName, LastName, Grade
FROM Students
WHERE Grade > 80
ORDER BY LastName ASC;

🔄 Action Queries​

📝 Update Queries​

  • Modifies existing records in bulk
  • Changes field values based on specified criteria
  • Affects multiple records simultaneously

Example update query:

UPDATE Students
SET Grade = Grade * 1.05
WHERE CourseID = 101;

➕ Append Queries​

  • Adds records from one table to another
  • Useful for combining data from multiple sources
  • Can filter which records are appended

Example append query:

INSERT INTO ArchivedStudents
SELECT * FROM CurrentStudents
WHERE GraduationYear = 2024;

❌ Delete Queries​

  • Removes records that meet specified criteria
  • Permanently deletes data (use with caution)
  • Often used for data cleanup or archiving

Example delete query:

DELETE FROM OrderDetails
WHERE OrderDate < '2023-01-01';

🏗️ Make-Table Queries​

  • Creates a new table from query results
  • Useful for creating snapshots of data
  • Helps with data analysis and reporting

Example make-table query:

SELECT StudentID, FirstName, LastName, AVG(Grade) AS AverageGrade
INTO StudentAverages
FROM Grades
GROUP BY StudentID, FirstName, LastName;

🔄 Cross-Tab Queries​

  • Summarizes data in a spreadsheet-like format
  • Displays values at the intersection of rows and columns
  • Useful for data analysis and comparison

Example cross-tab query:

TRANSFORM AVG(Grade)
SELECT StudentName
FROM Grades
GROUP BY StudentName
PIVOT Subject;

📋 Query Creation Methods​

🖌️ Using Query Wizards​

  1. Open the database application (e.g., Microsoft Access)
  2. Select "Create Query" or "Query Wizard"
  3. Choose the query type (simple select, crosstab, etc.)
  4. Select tables and fields to include
  5. Specify sort order and filter criteria
  6. Name and save the query

💻 Using Query Design View​

  1. Create a new query in design view
  2. Add tables to the query
  3. Select fields to include
  4. Set criteria for filtering
  5. Specify sort order
  6. Run and save the query

📝 Using SQL View​

  1. Create a new query
  2. Switch to SQL view
  3. Write SQL statements directly
  4. Run and save the query

🔍 Query Components and Criteria​

📊 Fields and Tables​

  • Select specific fields to include in results
  • Join multiple tables using relationships
  • Create calculated fields using expressions

🔢 Criteria and Operators​

  • Comparison operators: =, <, >, <=, >=, <>
  • Logical operators: AND, OR, NOT
  • Wildcards: * (multiple characters), ? (single character)
  • Range operator: BETWEEN
  • List operator: IN
  • Null value: IS NULL, IS NOT NULL

Example criteria:

-- Students with names starting with 'L'
WHERE LastName LIKE 'L*'

-- Products with price between $10 and $20
WHERE Price BETWEEN 10 AND 20

-- Orders from specific cities
WHERE City IN ('Hong Kong', 'Kowloon', 'New Territories')

-- Records with missing phone numbers
WHERE PhoneNumber IS NULL

🔄 Sorting and Grouping​

  • Sort order: Ascending or descending
  • Multiple sort levels: Primary, secondary, etc.
  • Group by: Aggregate records by common values
  • Having: Filter groups based on aggregate values

Example sorting and grouping:

-- Sort by last name then first name
ORDER BY LastName ASC, FirstName ASC

-- Group sales by product and calculate totals
SELECT ProductName, SUM(Quantity) AS TotalSold
FROM Sales
GROUP BY ProductName
HAVING SUM(Quantity) > 100

🧮 Advanced Query Techniques​

🔄 Joins​

  • Inner join: Returns records with matching values in both tables
  • Left join: Returns all records from left table and matching records from right table
  • Right join: Returns all records from right table and matching records from left table
  • Full join: Returns all records when there is a match in either table

Example join query:

SELECT Students.Name, Classes.ClassName
FROM Students
INNER JOIN Classes ON Students.ClassID = Classes.ClassID;

📊 Aggregate Functions​

  • COUNT: Number of records
  • SUM: Total of values
  • AVG: Average of values
  • MIN: Minimum value
  • MAX: Maximum value

Example aggregate query:

SELECT Subject, AVG(Grade) AS AverageGrade, 
       MIN(Grade) AS LowestGrade, 
       MAX(Grade) AS HighestGrade
FROM Grades
GROUP BY Subject;

🔄 Subqueries​

  • Queries nested within other queries
  • Used to perform operations that require multiple steps
  • Can appear in SELECT, FROM, or WHERE clauses

Example subquery:

SELECT StudentName
FROM Students
WHERE StudentID IN 
    (SELECT StudentID 
     FROM Grades 
     WHERE Grade > 90);

🔄 Union Queries​

  • Combines results from multiple SELECT statements
  • Removes duplicate records by default
  • Requires compatible columns in all SELECT statements

Example union query:

SELECT Name, 'Student' AS Type FROM Students
UNION
SELECT Name, 'Teacher' AS Type FROM Teachers
ORDER BY Name;

💼 Practical Query Examples​

📚 Academic Database​

-- Find students with high grades in Mathematics
SELECT s.FirstName, s.LastName, g.Grade
FROM Students s
JOIN Grades g ON s.StudentID = g.StudentID
JOIN Subjects sub ON g.SubjectID = sub.SubjectID
WHERE sub.SubjectName = 'Mathematics' AND g.Grade >= 90
ORDER BY g.Grade DESC;

-- Calculate average grades by class
SELECT c.ClassName, AVG(g.Grade) AS AverageGrade
FROM Classes c
JOIN Students s ON c.ClassID = s.ClassID
JOIN Grades g ON s.StudentID = g.StudentID
GROUP BY c.ClassName
ORDER BY AverageGrade DESC;

🛒 Retail Database​

-- Find top-selling products
SELECT p.ProductName, SUM(od.Quantity) AS TotalSold
FROM Products p
JOIN OrderDetails od ON p.ProductID = od.ProductID
GROUP BY p.ProductName
ORDER BY TotalSold DESC
LIMIT 10;

-- Calculate monthly sales
SELECT 
    YEAR(OrderDate) AS Year, 
    MONTH(OrderDate) AS Month, 
    SUM(TotalAmount) AS MonthlySales
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY Year, Month;

🚀 Query Optimization Tips​

⚡ Performance Considerations​

  • Select only necessary fields
  • Use indexes on fields used for filtering and sorting
  • Limit the use of wildcards, especially at the beginning of search terms
  • Use specific criteria to reduce the result set
  • Avoid unnecessary joins

🔍 Troubleshooting Common Issues​

  • Check for syntax errors in SQL statements
  • Verify table and field names
  • Ensure proper join conditions
  • Test complex queries in parts
  • Use query analyzer tools for performance issues

Database queries are powerful tools for transforming data into actionable information. Mastering query techniques allows you to extract precisely the information you need from your databases, supporting better decision-making and data analysis.