DATABASE QUERY USING SQL: COMPREHENSIVE STUDY NOTES FOR CUET
1. Introduction to SQL and Functions
Structured Query Language (SQL) is the most popular query language used by major Relational Database Management Systems (RDBMS) such as MySQL, ORACLE, and SQL Server.
SQL is designed to be declarative, meaning users specify what to retrieve rather than how to retrieve it. It is generally case-insensitive; for example, the column names ‘salary’ and ‘SALARY’ are treated as identical.
In SQL, a function is used to perform a specific task and returns a result. Functions are categorised based on how they process rows:
Single Row Functions (Scalar Functions): Applied to a single value and return a single value for every row in the result set.
Aggregate Functions (Multiple Row Functions): Work on a set of records as a whole and return one single summary value for the entire group.
2. Math (Numeric) Functions
Math functions accept numeric input and return numeric results.
POWER(X, Y) or POW(X, Y): Calculates X raised to the power Y.
Example: SELECT POWER(2, 3); → Output: 8.
ROUND(N, D): Rounds a number N to D decimal places.
If D is 0, it rounds to the nearest integer.
Example: SELECT ROUND(2912.564, 1); → Output: 2912.6.
Example: SELECT ROUND(283.2); → Output: 283.
MOD(A, B): Returns the remainder after dividing number A by number B.
Example: SELECT MOD(21, 2); → Output: 1.
Application: This is used to find the "remaining amount" after dividing a total into instalments.
3. Text (String) Functions
String functions perform operations on alphanumeric data to change case, extract parts of a string, or calculate length.
UCASE(str) / UPPER(str): Converts a string into all uppercase letters.
Example: SELECT UCASE("Informatics Practices"); → Output: INFORMATICS PRACTICES.
LCASE(str) / LOWER(str): Converts a string into all lowercase letters.
MID(str, pos, n) / SUBSTRING(str, pos, n) / SUBSTR(str, pos, n): Returns a substring of size n starting from the specified position pos.
If n is omitted, it returns the string from pos to the end.
Example: SELECT MID("Informatics", 3, 4); → Output: "form".
LENGTH(str): Returns the total number of characters in the string.
Example: SELECT LENGTH("Informatics"); → Output: 11.
LEFT(str, N): Returns the first N characters from the left side of the string.
RIGHT(str, N): Returns the last N characters from the right side of the string.
INSTR(str, sub): Returns the numeric position of the first occurrence of substring sub in str. Returns 0 if not found.
Example: SELECT INSTR("Informatics", "ma"); → Output: 6.
LTRIM(str): Returns the string after removing leading white space (left side).
RTRIM(str): Returns the string after removing trailing white space (right side).
TRIM(str): Removes both leading and trailing white space.
Special Use: Can also be used to remove specific character extensions, such as TRIM(".com" FROM Email).
4. Date and Time Functions
These functions allow extraction and manipulation of date parts. In MySQL, dates are typically in 'YYYY-MM-DD' format.
NOW(): Returns the current system date and time.
DATE(): Extracts only the date part from a date/time expression.
MONTH(date): Returns the month in numeric form (1 to 12).
MONTHNAME(date): Returns the full name of the month (e.g., "November").
YEAR(date): Returns the four-digit year.
DAY(date): Returns the day of the month (1 to 31).
DAYNAME(date): Returns the name of the day of the week (e.g., "Thursday").
5. Aggregate (Multiple Row) Functions
Aggregate functions work on a set of records and return a single summary value.
MAX(column): Returns the largest value in the specified column.
MIN(column): Returns the smallest value in the specified column.
AVG(column): Returns the average (mean) of values in the column.
SUM(column): Returns the total sum of all values in the column.
COUNT(*): Returns the total number of records in a table, including those with NULL values.
COUNT(column): Returns the number of non-NULL values in the specified column.
Note: Any arithmetic operation with a NULL value results in NULL.
6. Querying and Manipulating Data: Grouping and Sorting
6.1 The ORDER BY Clause
Used to display data in an ordered form based on a specified column.
Default: Ascending order.
DESC: Keyword used to sort in descending order.
Example: SELECT * FROM EMPLOYEE ORDER BY Salary DESC;.
6.2 The GROUP BY Clause
Groups rows together that contain the same values in a specified column. It is almost always used with aggregate functions.
Example: To count cars bought by each customer: SELECT CustID, COUNT(*) FROM SALE GROUP BY CustID;.
6.3 The HAVING Clause
Used to specify conditions on the groups created by the GROUP BY clause.
Difference from WHERE: WHERE filters rows before grouping; HAVING filters groups after they are formed.
Example: SELECT CustId, COUNT(*) FROM SALE GROUP BY CustID HAVING COUNT(*) > 1;.
7. Operations on Relations (Set Theory)
These binary operations merge tuples from two tables. Both relations must have the same number of attributes and corresponding attributes must have the same domain.
UNION (∪): Combines selected rows of two tables. Identical rows are shown only once in the result.
INTERSECT (∩): Returns only the tuples that are common to both tables.
MINUS / Set Difference (-): Returns rows that are in the first table but not in the second.
Cartesian Product (X): Combines every tuple from the first relation with every tuple from the second.
Degree: Sum of the degrees of both relations.
Cardinality: Product of the cardinalities of both relations.
Example: If Table A has 4 rows and Table B has 5, the Cartesian product has 4×5=20 rows.
8. JOIN Operations
A JOIN is used to combine rows from two or more tables based on a related attribute (usually a Primary Key in one and a Foreign Key in another).
Join Condition: Specified using the ON keyword or in the WHERE clause.
Ambiguity: If both tables have a column with the same name, you must use table aliases (e.g., U.UCode) to remove ambiguity.
NATURAL JOIN: A join operation that automatically removes redundant columns. If two tables are joined on a common attribute, that attribute appears only once in the result.
The N-1 Rule: In general, N−1 joins are required to combine N tables on equality conditions.
9. Summary Table: Single-Row vs. Aggregate Functions
Feature | Single Row Functions | Aggregate (Multiple Row) Functions |
Logic | Operates on a single row at a time. | Operates on groups of rows. |
Output | Returns one result per row. | Returns one result for a group. |
Location | SELECT, WHERE, ORDER BY. | Primarily SELECT and HAVING. |
Examples | POW, ROUND, LENGTH, YEAR. | SUM, AVG, MAX, COUNT. |
10. Essential CUET Tips
LIKE Operator: Use % to represent multiple characters and _ for exactly one character.
NULL Testing: You cannot use = for NULL. Always use IS NULL or IS NOT NULL.
DISTINCT: Use this clause inside functions or queries to eliminate duplicates (e.g., SELECT COUNT(DISTINCT Model)).
ALTER vs UPDATE: ALTER changes the structure/schema of a table (DDL); UPDATE changes the data inside the table (DML).
DROP vs DELETE: DROP removes the entire table/database (DDL); DELETE removes specific rows but keeps the structure (DML).