Notes on Structured Query Language (SQL)
Introduction
- Relational Database Management Systems (RDBMS) store, retrieve, and manipulate data
using queries.
- Examples: MySQL, Microsoft SQL Server, PostgreSQL, Oracle.
- SQL (Structured Query Language) is the primary language for interacting with RDBMS.
SQL Features
1. Case-insensitive.
2. Statements end with a semicolon `;`.
3. Supports data definition, manipulation, and querying.
Data Types and Constraints
Data Types in MySQL
1. CHAR(n): Fixed-length string (0 to 255 characters).
2. VARCHAR(n): Variable-length string (up to 65535 characters).
3. INT: Integer values.
4. FLOAT: Decimal numbers.
5. DATE: Stores date in YYYY-MM-DD format.
Constraints
1. NOT NULL: Ensures a column cannot have NULL values.
2. UNIQUE: Ensures all values in a column are unique.
3. DEFAULT: Provides a default value if none is specified.
4. PRIMARY KEY: Uniquely identifies a record.
5. FOREIGN KEY: Refers to a primary key in another table.
SQL Commands Overview
Data Definition Language (DDL)
1. CREATE DATABASE:
- Syntax: CREATE DATABASE database_name;
- Example: CREATE DATABASE StudentAttendance;
2. CREATE TABLE: Defines table structure.
- Syntax:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);
- Example:
CREATE TABLE STUDENT (
RollNumber INT PRIMARY KEY,
SName VARCHAR(20) NOT NULL,
SDateofBirth DATE,
GUID CHAR(12)
);
Functions in SQL
Single-Row Functions
1. Numeric Functions:
- POWER(x, y): Returns x^y.
- ROUND(n, d): Rounds n to d decimal places.
- MOD(a, b): Remainder of a / b.
2. String Functions:
- UCASE(s): Converts s to uppercase.
- LCASE(s): Converts s to lowercase.
- LENGTH(s): Returns length of s.
- MID(s, start, length): Substring from s.
3. Date Functions:
- NOW(): Current date and time.
- YEAR(date): Extracts year.
- DAYNAME(date): Day of the week.
Best Practices
1. Always use descriptive table and column names.
2. Use constraints to enforce data integrity.
3. Test queries before applying them to production databases.
4. Backup databases regularly.
