Structured Query Language - I

1. Introduction to Structured Query Language (SQL)

What is SQL? In a file system, users must write complex application programs to access or manage data. However, for Database Management Systems (DBMS), we use a special kind of language called Structured Query Language (SQL). SQL is the most popular query language used by major relational database management systems such as MySQL, ORACLE, and SQL Server.

Advantages of Using SQL:

• Easy to Learn: SQL statements are composed of descriptive English words, making them intuitive for beginners.

• Declarative Nature: You do not have to specify how to get the data; you simply specify what needs to be retrieved, and the SQL engine handles the rest.

• Case Insensitivity: SQL is generally not case-sensitive (e.g., SELECT and select are the same), though it is good practice to be consistent with table and database names.

• Versatility: While called a "query" language, it handles much more, including defining data structures, declaring constraints, and manipulating records.

2. Classification of SQL Commands

SQL commands are broadly categorised based on their purpose:

1. Data Definition Language (DDL): These commands are used to define, modify, or delete the structure (schema) of the database. Examples include CREATE, ALTER, and DROP.

2. Data Manipulation Language (DML): These commands are used to work with the actual data inside the tables. This includes inserting new records, updating existing ones, or deleting them. Examples include INSERT, UPDATE, and DELETE.

3. Data Query Language (DQL): A subset of SQL used specifically for retrieving data from tables. The primary command here is SELECT.

3. Introduction to MySQL and Database Management

MySQL is an open-source RDBMS software. After installation, users interact with it through the MySQL Shell, which displays a mysql> prompt when ready.

Key Rules for Using MySQL:

• Every SQL statement must end with a semicolon (;).

• To enter a multiline statement, press Enter without a semicolon. The prompt will change to ->, indicating the statement continues on the next line.

Creating and Selecting a Database:

• To create a database: Use CREATE DATABASE databasename;.

    ◦ Example: CREATE DATABASE StudentAttendance;.

• To see all databases: SHOW DATABASES;.

• To use a database: Before creating tables, you must select the database using USE databasename;.

• To see existing tables: SHOW TABLES;.

4. Data Types and Constraints

Before creating a table, you must define what kind of data each column will hold.

Common Data Types:

1. INT: Used for whole numbers (integers). Occupies 4 bytes of storage.

2. CHAR(n): Fixed-length character string of size n. If the data is shorter than n, MySQL pads the rest with spaces.

3. VARCHAR(n): Variable-length character string. It only uses as much space as needed to store the actual string.

4. FLOAT: Used for numbers with decimal points. Occupies 4 bytes.

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

SQL Constraints:

Constraints are restrictions applied to columns to ensure data correctness:

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

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

• PRIMARY KEY: Uniquely identifies each row (implies NOT NULL + UNIQUE).

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

• FOREIGN KEY: Links a column to a Primary Key in another table, ensuring referential integrity.

5. Data Definition Language (DDL) Commands

A. CREATE TABLE

Used to define a new table's structure.

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

• Example:

B. ALTER TABLE

Used to modify the structure of an existing table.

• Add a column: ALTER TABLE table_name ADD col_name DATATYPE;.

• Modify a column (datatype or constraint): ALTER TABLE table_name MODIFY col_name NEW_DATATYPE;.

• Remove a column: ALTER TABLE table_name DROP col_name;.

• Add a Primary/Foreign Key: ALTER TABLE table_name ADD PRIMARY KEY (col);.

C. DROP TABLE / DATABASE

Used for permanent deletion.

• Syntax: DROP TABLE table_name; or DROP DATABASE db_name;.

• Caution: This action cannot be undone and deletes all associated data.

6. Data Manipulation Language (DML) Commands

A. INSERT INTO

Used to add new records.

• Inserting all columns: INSERT INTO tablename VALUES (val1, val2, ...);.

• Inserting specific columns: INSERT INTO tablename (col1, col3) VALUES (val1, val3);.

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

B. UPDATE

Used to modify existing records.

• Syntax: UPDATE table_name SET col1 = val1 WHERE condition;.

• Caution: If the WHERE clause is omitted, all records in the table will be updated.

C. DELETE

Used to remove records.

• Syntax: DELETE FROM table_name WHERE condition;.

• Caution: If WHERE is omitted, all rows are deleted (the table becomes empty).

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

The SELECT statement is the primary tool for retrieving data.

• Retrieve all columns: SELECT * FROM table_name;.

• Retrieve specific columns: SELECT col1, col2 FROM table_name;.

• The WHERE Clause: Filters records based on conditions.

    ◦ Example: SELECT * FROM EMPLOYEE WHERE Salary > 50000;.

• Logical Operators: Combine multiple conditions using AND, OR, and NOT.

• DISTINCT Clause: Eliminates duplicate values in the result.

• BETWEEN: Filters values within a specific range (inclusive).

• IN: Checks if a value matches any in a specified list.

• LIKE (Pattern Matching): Used for substring searches.

    ◦ %: Matches zero or more characters.

    ◦ _: Matches exactly one character.

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

8. SQL Functions: Single Row Functions

Functions perform specific tasks and return values. Single-row functions operate on one row at a time and return one result per row.

A. Math Functions

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

    ◦ Example: SELECT POWER(2, 3); → Output: 8.

2. ROUND(N, D): Rounds 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.

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

    ◦ Example: SELECT MOD(21, 2); → Output: 1.

B. Text (String) Functions

1. UCASE(str) / UPPER(str): Converts string to uppercase.

2. LCASE(str) / LOWER(str): Converts string to lowercase.

3. LENGTH(str): Returns the number of characters in the string.

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

    ◦ Example: SELECT MID("Informatics", 3, 4); → Output: "form".

5. LEFT(str, n): Returns n characters from the left side.

6. RIGHT(str, n): Returns n characters from the right side.

7. INSTR(str, sub): Returns the position of the first occurrence of the substring sub in string str. Returns 0 if not found.

8. LTRIM(str): Removes leading spaces (left side).

9. RTRIM(str): Removes trailing spaces (right side).

10. TRIM(str): Removes both leading and trailing spaces.

Special use: Can also remove specific leading/trailing strings, e.g., TRIM(".com" FROM Email).

9. Key Differences Summary