SQL Tutorial - Full Database Course for Beginners

Course Overview

• Purpose: Introduction to SQL and relational database management systems (RDBMS)
• Structure:
  • Basics of databases
  • Types of databases
  • SQL usage
  • Installing RDBMS (MySQL)
  • Writing SQL queries (CRUD operations)
  • Database schemas and design
  • Advanced querying techniques

Database Fundamentals

What is a Database?

• General Definition: Any collection of related information
• Examples:
  • Phonebook
  • Shopping list
  • To-do list
  • Social media user database
• Storage Methods: Paper, mind, computer, comment section
• Key Point: Computers are excellent for storing databases due to speed, security, backup, and ease of data manipulation.

Types of Databases

• Relational (SQL)
  • Organizes data into tables with rows & columns
  • Unique key identifies each row
  • Examples: MySQL, Oracle, PostgreSQL, MariaDB
  • Uses SQL (Structured Query Language)
• Non-Relational (NoSQL)
  • Organizes data in formats other than tables (key-value, document, graph, etc.)
  • Examples: MongoDB, DynamoDB, Firebase

SQL Basics

Structured Query Language (SQL)

• Purpose: Interact with RDBMS
• Functions:
  • CRUD operations
  • Administrative tasks
  • User management
  • Table and schema definitions
• Standardization: Minor differences across RDBMS (MySQL, PostgreSQL, Oracle, etc.)

Core SQL Operations

• CRUD: Create, Read, Update, Delete
• Queries: Requests made to the RDBMS for specific information, written using SQL
• Syntax: Case-insensitivity for SQL commands, semicolon (;) ends statements

Installing MySQL and PopSQL (Windows & Mac)

MySQL Installation

• Steps:
  • Download MySQL Community Server
  • Custom or Developer Default installation
  • Configure MySQL Server (user, password, system startup option)
  • Verify installation through MySQL command-line client

Setting Up PopSQL

• Purpose: Simplified SQL query editor
• Steps:
  • Download from PopSQL.io
  • Connect to MySQL Database (localhost, root user)

Creating and Managing Tables

Data Types

• INT: Whole numbers
• DECIMAL(M,N): Numbers with decimals (M = total digits, N = digits after decimal)
• VARCHAR(N): Variable-length string (N = max length)
• BLOB: Binary large objects
• DATE: YYYY-MM-DD
• TIMESTAMP: YYYY-MM-DD HH:MM:SS

SQL Commands for Table Management

• CREATE TABLE: Define a new table
• DESCRIBE: Show table structure
• ALTER TABLE: Modify an existing table
• DROP TABLE: Delete a table
• INSERT INTO: Add records to a table

Advanced SQL Techniques

Inserting Data with Constraints

• NOT NULL: Column cannot have NULL values
• UNIQUE: All values in the column must be unique
• DEFAULT: Set a default value for a column
• AUTO_INCREMENT: Automatically incrementing value for primary keys

Updating and Deleting Data

• UPDATE: Modify existing records
  • Example: UPDATE student SET major = 'Bio' WHERE major = 'Biology'
• DELETE: Remove records from a table
  • Example: DELETE FROM student WHERE student_id = 5

Querying Data

Basic Queries

• SELECT: Retrieve data from databases
  • Example: SELECT * FROM student (Retrieves all records)
• WHERE: Filter results based on conditions
  • Example: SELECT name FROM student WHERE major = 'Biology'
• ORDER BY: Sort results
  • Example: SELECT * FROM student ORDER BY name ASC
• LIMIT: Limit the number of results
  • Example: SELECT * FROM student LIMIT 5
• DISTINCT: Eliminate duplicate values
  • Example: SELECT DISTINCT major FROM student

Functions and Aggregation

• COUNT: Count rows
  • Example: SELECT COUNT(*) FROM student
• AVG: Average value
  • Example: SELECT AVG(salary) FROM employee
• SUM: Sum of values
  • Example: SELECT SUM(salary) FROM employee
• GROUP BY: Group results
  • Example: SELECT sex, COUNT(*) FROM employee GROUP BY sex

Wildcards and LIKE Clause

• Wildcards: % (any number of characters), _ (single character)
  • Example: SELECT * FROM client WHERE client_name LIKE '%LLC'

UNION Operations

• Combine results from multiple queries
  • Example: SELECT first_name FROM employee UNION SELECT branch_name FROM branch

JOIN Operations

• INNER JOIN: Default join, matches records between tables
  • Example: SELECT employee.name, branch.name FROM employee INNER JOIN branch ON employee.branch_id = branch.branch_id
• LEFT JOIN: All records from left table, matched records from right table
• RIGHT JOIN: All records from right table, matched records from left table

Nested Queries

• Using results from one query within another
  • Example: SELECT * FROM employee WHERE emp_id IN (SELECT emp_id FROM works_with WHERE total_sales > 30000)

Database Design: ER Diagrams

ER Diagrams Introduction

• Purpose: Visual representation of database schema
• Components:
  • Entities (rectangles)
  • Attributes (ovals)
  • Primary Key (underlined)
  • Relationships (diamonds)
  • Composite & Multivalued Attributes (double circles)
  • Derived Attributes (dashed ovals)
• Types of Participation: Total (double line) vs Partial (single line)
• Relationship Cardinality: Defines numerical relationships (1:1, 1:N, N:M)
• Weak Entities & Identifying Relationships: Weak entity (double rectangle) depends on another entity

Designing an ER Diagram

• Process: Derive from requirements
  • Identify Entities and Attributes
  • Define Relationships and Cardinality
  • Example: Company database with branches, employees, and clients

Converting ER Diagrams to Database Schemas

• Steps:
  1. Map regular entities to relations
  2. Map weak entities
  3. Map binary 1:1 relationships
  4. Map binary 1:N relationships
  5. Map binary M:N relationships
• Example: Create tables for employee, branch, client, branch supplier, etc.
  • Define primary and foreign keys
  • Establish relations based on ER diagram

Conclusion

• Objectives Achieved: Comprehensive understanding of SQL and RDBMS, practical knowledge of designing and querying databases, and skills to create and maintain database schemas.