Database Query Using SQL

Main Idea

SQL is used to create, retrieve, modify, and manage data stored in relational databases. Its queries combine commands, conditions, operators, functions, grouping, sorting, and joins to obtain meaningful information, while aggregate functions support the summarisation of data for analysis and reporting.

Key Concepts and Definitions

• Database: An organized collection of related data stored for easy access and management.
• Table: A structure in a relational database that stores data in rows and columns.
• Record or Row: A complete set of related values representing one entity or item in a table.
• Field or Column: A named attribute that stores one type of information for all records.
• Primary Key: A column or combination of columns that uniquely identifies each row in a table.
• Foreign Key: A column that refers to the primary key of another table and establishes a relationship between tables.
• SQL: Structured Query Language used to communicate with and manage relational databases.
• DDL: Data Definition Language commands such as CREATE, ALTER, and DROP used to define database structures.
• DML: Data Manipulation Language commands such as INSERT, UPDATE, and DELETE used to change table data.
• DQL: Data Query Language, mainly represented by SELECT, used to retrieve data.
• SELECT: Retrieves specified columns or expressions from one or more tables.
• FROM: Specifies the table or tables from which data is selected.
• WHERE: Filters individual rows according to a condition.
• DISTINCT: Removes duplicate values from the query result.
• ORDER BY: Sorts query results in ascending (ASC) or descending (DESC) order.
• Aliases: Temporary alternative names given to tables or columns using AS.
• Operators: Symbols or keywords used in conditions, including arithmetic, comparison, logical, IN, BETWEEN, LIKE, and IS NULL operators.
• NULL: A special value indicating missing, unknown, or unavailable data; it is tested using IS NULL or IS NOT NULL.
• Aggregate Function: A function that processes multiple rows and returns one result, such as COUNT, SUM, AVG, MIN, and MAX.
• Scalar Function: A function that works on individual values and returns one result for each row, such as UCASE, LCASE, LENGTH, ROUND, and MOD.
• GROUP BY: Combines rows having the same values in selected columns so that aggregate calculations can be performed for each group.
• HAVING: Filters groups after GROUP BY, usually using aggregate conditions.
• Join: A query operation that combines rows from related tables using matching columns.
• Equi-Join: A join that matches rows using equality between related columns, commonly written with INNER JOIN or a WHERE condition.
• Cartesian Product: The result of combining every row of one table with every row of another table, usually produced by a join without a matching condition.
• Constraint: A rule applied to table data, such as PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, and CHECK.

Supporting Arguments and Evidence

• Basic retrieval uses SELECT with FROM:

   SELECT column1, column2 FROM table_name;
   

To retrieve every column, use:

   SELECT * FROM table_name;
   

A query should select only the required columns when possible because this improves clarity and can reduce unnecessary data retrieval.

• The WHERE clause filters individual rows:

   SELECT columns FROM table_name WHERE condition;
   

Common comparison operators are =, <>, !=, <, >, <=, and >=. Logical operators AND, OR, and NOT combine or reverse conditions.

• SQL provides specialised filtering operators. IN tests membership in a list:

   WHERE city IN ('Delhi', 'Mumbai')
   

BETWEEN checks an inclusive range:

   WHERE marks BETWEEN 60 AND 90
   

LIKE performs pattern matching: % represents any number of characters, while _ represents one character.

• NULL represents missing, unknown, or unavailable information. It must be tested with IS NULL or IS NOT NULL; ordinary comparison operators do not correctly test NULL.

• Results can be sorted using:

   SELECT columns FROM table_name
   ORDER BY column_name ASC;
   

or:

   SELECT columns FROM table_name
   ORDER BY column_name DESC;
   

DISTINCT removes duplicate values, and aliases provide temporary alternative names using AS.

• Aggregate functions summarise multiple rows. These include COUNT(), SUM(), AVG(), MIN(), and MAX(). COUNT(*) counts rows, whereas COUNT(column) ignores NULL values in that column.

• Grouped summaries use:

   SELECT group_column, aggregate_function(column)
   FROM table_name
   GROUP BY group_column;
   

GROUP BY forms groups with the same values, while HAVING filters those groups. In contrast, WHERE filters individual rows before grouping.

• A common logical processing order is:

FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, and ORDER BY. Understanding this order helps explain why WHERE applies to rows and HAVING applies to groups.

• Joins combine related data from multiple tables. A standard inner join is:

   SELECT columns
   FROM table1
   INNER JOIN table2
   ON table1.common_column = table2.common_column;
   

An INNER JOIN returns only rows with matching values in both tables. A LEFT JOIN returns all rows from the left table and matching rows from the right table; where no match exists, right-side values appear as NULL.

• An equi-join matches related columns using equality, either through INNER JOIN or an equivalent WHERE condition. Joins without a matching condition can produce a Cartesian product, combining every row of one table with every row of another table. Therefore, joins should use clearly related tables and appropriate matching conditions.

• SQL also modifies and defines database structures. The relevant statements are:

   INSERT INTO table_name (column1, column2)
   VALUES (value1, value2);
   

   UPDATE table_name
   SET column1 = value1
   WHERE condition;
   

   DELETE FROM table_name
   WHERE condition;
   

A WHERE clause should generally be used with UPDATE to avoid changing every row and with DELETE to avoid deleting every row. Without WHERE, all rows may be affected or deleted.

• CREATE TABLE defines a table, including its columns, data types, and constraints. ALTER TABLE changes an existing structure, such as by adding or modifying a column. DROP TABLE permanently removes a table and its stored data.

• Common MySQL data types include INT for whole numbers, DECIMAL for precise numeric values, CHAR or VARCHAR for text, DATE for dates, and TIME for time values. String and date values are generally written inside single quotation marks. SQL keywords are commonly written in uppercase for readability, although many systems accept lowercase keywords, and a semicolon usually marks the end of an SQL statement.

• SQL functions can transform data during retrieval without changing the original stored values. Scalar function examples include:

- UCASE(text) - LCASE(text) - LENGTH(text) - LEFT(text, number) - RIGHT(text, number) - ROUND(number, decimals) - MOD(number, divisor)

Date-related functions may include YEAR(date), MONTH(date), DAY(date), NOW(), and CURDATE(), depending on the SQL system.

• Primary keys maintain uniqueness, while foreign keys establish relationships between tables. Together with constraints such as PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, and CHECK, they support the integrity and reliable management of relational data.

What to Remember

SQL uses SELECT, FROM, WHERE, and ORDER BY to retrieve, filter, and arrange data; aggregate functions with GROUP BY and HAVING summarise it. Joins combine related tables through matching columns, whereas missing join conditions can create a Cartesian product. Primary and foreign keys maintain database relationships, and suitable WHERE conditions are essential when using UPDATE and DELETE to prevent unintended changes.