Introduction to SQL

This chapter covers the key aspects of SQL (Structured Query Language) used in managing and querying databases, specifically with MySQL as the RDBMS (Relational Database Management System).

Overview of SQL and RDBMS

• SQL is crucial for interacting with RDBMS systems which include MySQL, Oracle, MS SQL Server, etc.
• The advantage of using SQL is that it abstracts the physical storage of data, allowing users to specify what data they need without detailing how to retrieve it.
• SQL is not just a querying language but extends to data definition, constraints, and manipulation.

Setting Up MySQL

• Download and install MySQL from its official website. Ensure to start the MySQL service to begin using its command line interface where user can enter SQL commands.
• Basic rules when writing SQL in MySQL include:
  • SQL is case insensitive.
  • End each statement with a semicolon.
  • Use multi-line entry capabilities by utilizing '->' for continuation.

Data Types in MySQL

• MySQL supports various data types which can be classified as:
  • Numeric Types: INT, FLOAT, etc.
  • Date and Time Types: DATE, TIME, etc.
  • String Types: CHAR, VARCHAR are used for textual data. For instance:
    • CHAR(n) for fixed-length strings where 'n' is the number of characters.
    • VARCHAR(n) allows variable-length strings, occupying space based on actual data.

Table of Common Data Types

| Data Type | Description | |-----------|-------------| | CHAR(n) | Fixed-length string of length n. | | VARCHAR(n)| Variable-length string of max length n. | | INT | Integer type occupying 4 bytes. | | FLOAT | Floating point number with decimal. | | DATE | Represents date in 'YYYY-MM-DD' format. |

Constraints in SQL

• Constraints are rules enforced on data in the database. Common constraints include:
  • NOT NULL: Ensures a column cannot contain NULL values.
  • UNIQUE: Disallows duplicate values in a column.
  • DEFAULT: Sets a default value for a column if no value is provided.
  • PRIMARY KEY: Identifies each row uniquely in a table.
  • FOREIGN KEY: Ensures referential integrity by linking to primary keys of other tables.

SQL for Data Definition (DDL)

• DDL (Data Definition Language) commands include CREATE, ALTER, and DROP that manage database structures:
  • CREATE DATABASE <dbname> to create a new database.
  • CREATE TABLE <tablename> defines a new table including attributes and their constraints.
  • ALTER TABLE <tablename> modifies the structure by adding or dropping columns or constraints.
  • DROP TABLE <tablename> deletes a table permanently along with its data.

Example Commands

• **Creating a Database:

CREATE DATABASE StudentAttendance;
```**  
- **Creating a Table:  
```sql
CREATE TABLE STUDENT (
    RollNumber INT PRIMARY KEY,
    SName VARCHAR(20) NOT NULL,
    SDateofBirth DATE NOT NULL,
    GUID CHAR(12)
);
```**
- **Altering Table:  
```sql
ALTER TABLE STUDENT ADD GRADE CHAR(2); // Add column  
ALTER TABLE STUDENT DROP GUID; // Remove column
```**

## SQL for Data Manipulation (DML)
- DML operations allow for inserting, updating, and deleting records within a table:
  - **INSERT:** Adds new data into a table.
  - **UPDATE:** Modifies existing data in a table.
  - **DELETE:** Removes data from a table.

### Example Usage
- **Inserting Data:  
```sql
INSERT INTO STUDENT (RollNumber, SName, SDateofBirth) VALUES (1, 'John Doe', '2003-08-15');
```**  
- **Updating Data:  
```sql
UPDATE STUDENT SET SDateofBirth = '2003-07-15' WHERE RollNumber = 1;
```**  
- **Deleting Data:  
```sql
DELETE FROM STUDENT WHERE RollNumber = 1;
```**

## SQL for Data Query (SELECT)
- The SELECT statement is primarily used to retrieve data from tables:
  - Basic structure:  
```sql
SELECT column1, column2  
FROM tablename  
WHERE condition;

• Can use * to represent all columns, and clauses like DISTINCT, ORDER BY, etc., to refine results.
• Substrings and pattern matching can be achieved using LIKE operator along with wildcards (% for multiple characters, _ for a single character).

Example Queries

• **Select All Data:

SELECT * FROM STUDENT;
```**  
- **Select with Condition:  
```sql
SELECT SName FROM STUDENT WHERE RollNumber = 1;
```**

## Summary
- SQL is a powerful and essential tool for managing RDBMS. The key components discussed are useful for defining and manipulating data effectively.
- Always ensure proper use of SQL syntax to avoid errors and maintain database integrity.

1. SQL is the language used for managing contents of a relational database.
2. Use MySQL as an open-source RDBMS to implement SQL.
3. SQL operations can be categorized into DDL, DML, and DQL.
4. Essential SQL commands include CREATE, INSERT, UPDATE, DELETE, and SELECT.
5. Understand data types and constraints to define proper structure in databases.
6. Use PRIMARY KEY for unique identification of records in a table.
7. It's crucial to utilize WHERE clause correctly to avoid unexpected changes/deletions in data.
8. The SELECT statement retrieves data, and it can be refined with WHERE, ORDER BY, and LIKE clauses.
9. Remember that NULL values indicate unknown data; use IS NULL for checks.
10. SQL commands are case-insensitive but maintain consistent casing for readability.