NoteVerse

Basic SQL queries

Comprehensive study notes, diagrams, and exam preparation for Basic SQL queries.

Basic SQL queries

Definition

A basic SQL query is a command written in SQL to retrieve, insert, update, delete, or manipulate data in a relational database table using a structured syntax.

In simple terms, a SQL query is a request made to the database. For example, if a table stores student information, a SQL query can be used to display all students, find students from a specific class, or count the number of students in each department.

Example:

SELECT name, age
FROM students
WHERE age > 18;

This query asks the database to show the name and age of students whose age is greater than 18.

Main Content

1. SELECT Query

  • The SELECT statement is used to retrieve data from one or more tables. It is the most commonly used SQL query and forms the basis of nearly all database reporting and analysis tasks.
  • A SELECT query can return all columns, specific columns, distinct values, or calculated results. It is often combined with clauses like WHERE, ORDER BY, GROUP BY, and HAVING to refine the output.

Basic syntax:

SELECT column1, column2
FROM table_name;

Example:

SELECT student_id, student_name
FROM students;

This returns only the student_id and student_name columns from the students table.

Selecting all columns:

SELECT *
FROM students;

The * symbol means “all columns.” This is useful for quick inspection, but in practice, selecting only required columns is better for performance and readability.

Using DISTINCT:

SELECT DISTINCT department
FROM employees;

This returns each department only once, removing duplicate values.

Using WHERE with SELECT:

SELECT name, marks
FROM students
WHERE marks >= 80;

This retrieves only students with marks of 80 or more.

Using ORDER BY with SELECT:

SELECT name, marks
FROM students
ORDER BY marks DESC;

This sorts the result by marks in descending order.

2. Filtering and Sorting Data

  • The WHERE clause is used to filter records based on a condition. It allows the database to return only rows that meet a specific requirement, making the result more meaningful and manageable.
  • The ORDER BY clause is used to sort query results in ascending or descending order. Sorting helps users find the highest, lowest, earliest, or alphabetically ordered values quickly.

Filtering with WHERE:

SELECT *
FROM employees
WHERE department = 'Sales';

This query returns only employees in the Sales department.

Using multiple conditions:

SELECT *
FROM students
WHERE age > 18 AND city = 'Delhi';

This returns students older than 18 who live in Delhi.

Common logical operators in filtering:

  • AND — both conditions must be true
  • OR — at least one condition must be true
  • NOT — reverses the condition

Example with OR:

SELECT *
FROM products
WHERE category = 'Books' OR category = 'Stationery';

This returns products that belong to either category.

Sorting with ORDER BY:

SELECT name, salary
FROM employees
ORDER BY salary ASC;

ASC means ascending order, which is the default. For descending order, use DESC.

Sorting by more than one column:

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

This first sorts employees by department and then sorts salaries within each department from highest to lowest.

Pattern matching with LIKE:

SELECT *
FROM students
WHERE name LIKE 'A%';

This finds names starting with A.

Range checking with BETWEEN:

SELECT *
FROM products
WHERE price BETWEEN 100 AND 500;

This returns products priced between 100 and 500, inclusive.

Checking list membership with IN:

SELECT *
FROM students
WHERE class IN ('10A', '10B', '10C');

This returns students from any of the listed classes.

3. Aggregate and Group Queries

  • Aggregate functions are used to perform calculations on sets of values. They summarize data instead of showing every individual record. Common aggregate functions include COUNT(), SUM(), AVG(), MIN(), and MAX().
  • The GROUP BY clause groups rows that share the same value in one or more columns, making it possible to compute totals or averages for each group.

Common aggregate functions:

SELECT COUNT(*)
FROM students;

Counts all rows in the students table.

SELECT AVG(marks)
FROM students;

Calculates the average marks of all students.

SELECT MAX(salary)
FROM employees;

Finds the highest salary.

SELECT MIN(price)
FROM products;

Finds the lowest price.

SELECT SUM(amount)
FROM orders;

Calculates the total amount of all orders.

Using GROUP BY:

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

This counts how many employees are in each department.

Using GROUP BY with AVG:

SELECT class, AVG(marks)
FROM students
GROUP BY class;

This calculates the average marks for each class.

Filtering grouped results with HAVING:

SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

HAVING is used after grouping to filter groups based on aggregate values. This query shows only departments with more than 5 employees.

Difference between WHERE and HAVING:

  • WHERE filters individual rows before grouping
  • HAVING filters grouped results after aggregation

Example showing both:

SELECT department, AVG(salary)
FROM employees
WHERE salary > 30000
GROUP BY department
HAVING AVG(salary) > 50000;

Here, only employees with salary above 30000 are considered, and then departments with average salary above 50000 are shown.

Working / Process

1. Identify the required data

  • First, determine what information is needed from the database.
  • Decide whether you want all records, filtered records, sorted output, grouped data, or a calculated summary.
  • Example: If you want students who scored above 90, you need a SELECT query with a WHERE condition.

2. Write the SQL query using proper syntax

  • Choose the correct SQL clause or combination of clauses.
  • Maintain the standard structure: SELECT, FROM, optional WHERE, optional GROUP BY, optional HAVING, optional ORDER BY.
  • Example: sql SELECT name, marks FROM students WHERE marks > 90 ORDER BY marks DESC;

3. Execute the query and interpret the result

  • Run the query in the database system.
  • Check whether the output matches the expected result.
  • If the result is incorrect, revise the condition, column names, sorting order, or grouping logic.
  • Example interpretation: If the query returns five rows, then five students matched the condition.

General processing flow:

User requirement
      ↓
Choose SQL clause
      ↓
Write query
      ↓
Database processes query
      ↓
Result table returned

Example end-to-end query:

SELECT department, AVG(salary)
FROM employees
WHERE salary > 40000
GROUP BY department
ORDER BY AVG(salary) DESC;

This query:

  • selects departments,
  • considers only employees earning above 40000,
  • groups them by department,
  • calculates average salary,
  • and sorts the departments from highest to lowest average salary.

Advantages / Applications

Easy data retrieval

  • SQL makes it simple to retrieve specific information from large databases without manually searching through records.
  • Example: fetching all customer names from a customer table.

Efficient filtering and analysis

  • Queries can filter, sort, and summarize data quickly, which is useful in reporting and decision-making.
  • Example: finding total sales per month or top-performing students.

Widely used in real-world systems

  • SQL is used in websites, mobile apps, banking systems, educational systems, hospitals, and business software.
  • Example: an online store uses SQL to display products, manage orders, and track inventory.

Supports data-driven decisions

  • Organizations use SQL queries to analyze trends, monitor performance, and make informed decisions.
  • Example: a school can use SQL to identify students who need academic support.

Works with many database systems

  • SQL is supported by popular systems such as MySQL, PostgreSQL, Oracle, SQL Server, and SQLite.
  • This makes SQL skills transferable across platforms.

Summary

  • Basic SQL queries are used to retrieve and manage data in tables.
  • SELECT, WHERE, ORDER BY, GROUP BY, and aggregate functions are core parts of simple SQL querying.
  • SQL helps users filter, sort, and summarize information efficiently.
  • Important terms to remember: SELECT, FROM, WHERE, ORDER BY, GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAX
← Prev: Query Language SQL
Next: SQL functions and constraints →