Structured Query Language (Section B1)

Structured Query Language

STRUCTURED QUERY LANGUAGE (SQL): COMPREHENSIVE CUET STUDY NOTES

1. Introduction to Structured Query Language (SQL)

In a file system, users must write complex application programs to access data. However, for Database Management Systems (DBMS), we use a special kind of language called a query language. 

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.

Advantages of SQL:

• Easy to Learn: Statements comprise descriptive English words.

• Case Insensitivity: SQL is generally not case-sensitive; for example, ‘salary’ and ‘SALARY’ are treated the same.

• Declarative Nature: Users do not need to specify how to get the data; they simply specify what is to be retrieved, and SQL handles the rest.

• Versatility: Beyond simple queries, SQL provides statements for defining data structures, manipulating data, and declaring constraints.

2. Classification of SQL Commands

SQL commands are categorised based on their functional purpose within the database:

1. Data Definition Language (DDL): Used for defining, modifying, and deleting relation schemas (the structure of the database). Examples include CREATE, ALTER, and DROP.

2. Data Manipulation Language (DML): Used for managing the actual data within the tables, including insertion, removal, and modification of records. Examples include INSERT, UPDATE, and DELETE.

3. Data Query Language (DQL): Focused on the retrieval of data from tables. The primary command is SELECT.

3. Introduction to MySQL and Database Creation

MySQL is an open-source RDBMS software. After installation, users interact with it via the MySQL Shell, indicated by the mysql> prompt.

Key Usage Rules:

• Always end SQL statements with a semicolon (;).

• For multiline statements, press Enter without a semicolon to continue on the next line (prompt changes to ->).

Creating a Database: To create a database, use the CREATE DATABASE statement.

• Syntax: CREATE DATABASE databasename;

• Example: mysql> CREATE DATABASE StudentAttendance;

To start working with a specific database among many, use the USE command:

• Example: mysql> USE StudentAttendance;

4. Data Types and Constraints in MySQL

Common Data Types:

1. INT: Stores whole numbers; occupies 4 bytes.

2. CHAR(n): Fixed-length character string of length n (0–255). If the input is shorter, it is padded with spaces.

3. VARCHAR(n): Variable-length character string (0–65535). It only occupies as much space as the actual string entered.

4. FLOAT: Holds numbers with decimal points; occupies 4 bytes.

5. DATE: Used for dates in 'YYYY-MM-DD' format.

SQL Constraints:

Constraints are restrictions used to ensure the correctness and integrity of data:

• NOT NULL: Ensures a column cannot have missing/unknown values.

• UNIQUE: Ensures all values in a column are distinct.

• DEFAULT: Provides a default value if none is specified.

• PRIMARY KEY: Uniquely identifies each row; it is essentially a combination of NOT NULL and UNIQUE.

• FOREIGN KEY: Refers to a Primary Key in another table to establish a link.

5. Data Definition Language (DDL) Commands

A. CREATE TABLE

Used to define a table's structure.

• Syntax: CREATE TABLE tablename(attributename1 datatype constraint, ...);

• Example:

B. ALTER TABLE

Used to modify the schema of an existing table.

• Add Primary Key: ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);

• Add Foreign Key: ALTER TABLE STUDENT ADD FOREIGN KEY(GUID) REFERENCES GUARDIAN(GUID);

• Add Column: ALTER TABLE GUARDIAN ADD income INT;

• Modify Datatype/Constraint: ALTER TABLE GUARDIAN MODIFY GAddress VARCHAR(40);

• Remove Column: ALTER TABLE GUARDIAN DROP income;

C. DROP TABLE / DATABASE

Permanently removes a table or database. Caution: This action cannot be undone.

• Syntax: DROP TABLE table_name; or DROP DATABASE database_name;

6. Data Manipulation Language (DML) Commands

A. INSERT INTO

Used to populate a table with records.

• Syntax: INSERT INTO tablename VALUES (val1, val2, ...);

• Note: Text and date values must be enclosed in single quotes (' ').

B. UPDATE

Used to modify existing values in a table.

• Syntax: UPDATE table_name SET attribute = value WHERE condition;

• Caution: Omitting the WHERE clause will update all records in the table.

C. DELETE

Used to remove records from a table.

• Syntax: DELETE FROM table_name WHERE condition;

• Caution: Omitting the WHERE clause will delete all records.

7. Data Query Language (DQL): The SELECT Statement

The SELECT statement is used to retrieve data, displaying the result in tabular form.

• Basic Syntax: SELECT attribute1, attribute2 FROM table_name WHERE condition;

• Select All: SELECT * FROM table_name;

• Aliasing (Renaming Columns): Use AS to rename a column in the output.

  • Example: SELECT EName AS Name FROM EMPLOYEE;

• DISTINCT Clause: Returns records without repetition.

• WHERE Clause Operators:

  • Relational: =, <, <=, >, >=, !=.

  • Logical: AND, OR, NOT.

  • Range: BETWEEN val1 AND val2.

  • Membership: IN (val1, val2, ...) compares a value with a set.

  • NULL Check: IS NULL or IS NOT NULL.

• ORDER BY Clause: Sorts results in ascending (ASC, default) or descending (DESC) order.

• Pattern Matching (LIKE): Uses wildcards:

  • %: Represents zero, one, or multiple characters.

  • _: Represents exactly one single character.

8. SQL Functions

A. Math (Numeric) Functions

• POWER(X, Y): Calculates X to the power Y.

• ROUND(N, D): Rounds number N to D decimal places. If D=0, it rounds to the nearest integer.

• MOD(A, B): Returns the remainder of A divided by B.

B. String (Text) Functions

• UCASE() / UPPER(): Converts string to uppercase.

• LCASE() / LOWER(): Converts string to lowercase.

• MID() / SUBSTRING() / SUBSTR(str, pos, n): Returns a substring of size n starting from position pos.

• LENGTH(): Returns the number of characters in a string.

• LEFT(str, N) / RIGHT(str, N): Returns N characters from the left or right side.

• INSTR(str, sub): Returns the position of the first occurrence of a substring.

• LTRIM() / RTRIM() / TRIM(): Removes leading, trailing, or both types of white spaces.

C. Date Functions

• NOW(): Current system date and time.

• DATE(): Extracts the date part from an expression.

• MONTH(): Returns the month in numeric form (1–12).

• MONTHNAME(): Returns the full name of the month.

• YEAR(): Returns the four-digit year.

• DAY(): Returns the day of the month (1–31).

• DAYNAME(): Returns the name of the day of the week (e.g., Thursday).

D. Aggregate (Multiple Row) Functions

These operate on groups of rows and return a single value:

• MAX(column): Largest value.

• MIN(column): Smallest value.

• AVG(column): Average of values.

• SUM(column): Total sum of values.

• COUNT(column): Number of non-NULL values.

• COUNT(*): Total number of records in the table, including NULLs.

9. Advanced Querying: GROUP BY and HAVING

• GROUP BY: Groups rows that have the same values in specified columns. It is typically used with aggregate functions.

• HAVING: Used to specify conditions on the grouped rows created by the GROUP BY clause.

  • Example: SELECT CustID, COUNT(*) FROM SALE GROUP BY CustID HAVING COUNT(*) > 1;

10. Operations on Relations (Set Theory)

These binary operations require both tables to have the same number of attributes and matching domains.

• UNION (∪): Combines rows from two tables, eliminating duplicates.

• INTERSECT (∩): Returns only the common tuples between two tables.

• MINUS (-): Returns rows present in the first table but not in the second (set difference).

• Cartesian Product (X): Combines every row of the first table with every row of the second.

  • Resulting Degree: Sum of degrees of both tables.

  • Resulting Cardinality: Product of cardinalities of both tables.

11. JOIN Operations

A JOIN combines tuples from two tables based on a related attribute (usually a Primary Key in one and a Foreign Key in another).

• JOIN with ON Clause: Explicitly specifies the join condition.

• NATURAL JOIN: An extension of JOIN that automatically removes redundant columns (the joining attribute appears only once).

• N-1 Rule: In general, N−1 joins are required to combine N tables.