Database Fundamentals

Relational Database Systems

• Oracle: Enterprise systems, banking, telecommunications
• MySQL: Widely adopted in web applications
• Microsoft SQL Server: Common in Windows-based environments
• SQLite: Lightweight, ideal for mobile platforms

Core Database Components

• Database: Collection of related tables
• Table: Two-dimensional structure with rows and columns
• Row: Individual record
• Column: Field definition

MySQL Overview MySQL is an open-source relational database management system originally developed by MySQL AB, later acquired by Oracle Corporation. It supports multiple platforms and programming languages, making it an excellent choice for learning relational database concepts.

Key Features:

• Written in C/C++ with cross-platform compatibility
• Supports Linux, Windows, and UNIX operating systems
• Provides APIs for various programming languages
• Optimized query processing for performance
• Capable of handling large-scale datasets
• Standards-compliant SQL implementation

SQL Language Fundamentals Structured Query Language (SQL) is the standard language for interacting with relational databases. It enables operations across different database systems including Oracle, MySQL, and SQL Server.

SQL Language Categories:

1. DQL: Data Query Language (SELECT)
2. DDL: Data Definition Language (CREATE, DROP)
3. DML: Data Manipulation Language (INSERT, UDPATE, DELETE)
4. TCL: Transaction Control Language (COMMIT, ROLLBACK)

For testing professionals, proficiency in DQL is essential, while familiarity with other categories is beneficial.

SQL Syntax and Comments

Single-line Comments

-- This is a single-line comment

Multi-line Comments

/* This is a
   multi-line comment */

Common MySQL Data Types

Database Elements

• Database: Collection of tables
• Table: Structured data container
• Field: Column definition
• Record: Row of data

Table Creation Syntax

Basic Table Creation

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype
);

Example: Simple Table

-- Create table with name field
CREATE TABLE employee_data (employee_name VARCHAR(10));

Example: Multi-column Table

-- Create table with multiple fields
CREATE TABLE user_profile (
    user_id INT,
    full_name VARCHAR(20),
    user_age TINYINT UNSIGNED
);