Back to IP Curriculum

Database Concepts and MySQL

CBSE Class 11 - Information Practices

Welcome to the world of SQL (Structured Query Language) - the language that powers databases! Follow along as we explore how to store, manage, and retrieve data efficiently using SQL.

Chapter 1: The Foundation - Basic Terms

What is a File?

A file is a container used to store data on a computer. Files can have different formats:

CSV

Comma-Separated Values - plain text format for tabular data

Binary

Stores data in binary form for images, executables

Text

Plain text data readable by text editors

Key Database Terms

1. SQL (Structured Query Language)

Language used for managing and manipulating relational databases. Allows users to create, update, retrieve, and delete data.

2. RDBMS (Relational Database Management System)

Database management system based on the relational model. Organizes data into tables (relations) with rows and columns, supporting relationships between tables.

3. Database

Organized collection of structured information stored in tables (relations). Each table consists of rows and columns for efficient data storage and retrieval.

4. Relation

A two-dimensional structure represented by rows and columns. Each relation is essentially a table.

5. DBMS (Database Management System)

Software that manages and interacts with databases. Examples: MySQL, Oracle, Microsoft Access, PostgreSQL, Sybase.

6. ER Diagram (Entity-Relationship Diagram)

Graphical representation of entities and their relationships in a database, used for database design.

Chapter 2: Why Databases? - Limitations of File System

File systems have several limitations that databases solve. Let's understand why databases are better:

1. Difficulty in Access

File System:

Accessing data requires creating application programs. Complex and time-consuming.

Database:

Efficient access using SQL query language. No need for additional application programs.

2. Data Redundancy

File System:

Same data duplicated in multiple files (e.g., student names in both "Student" and "Attendance" files).

Database:

Minimizes redundancy through normalization. Data stored once and accessed as needed.

3. Data Inconsistency

File System:

Same data in multiple locations may not be updated uniformly, leading to inconsistencies.

Database:

Maintains consistency through data integrity rules. Changes reflected throughout the database.

4. Data Isolation

File System:

Related data spread across multiple files, difficult to combine and analyze.

Database:

Structured format allows easy cross-referencing and relational queries.

5. Data Dependence

File System:

Changing file structure requires updating all application programs.

Database:

Data independence - structure can be modified without impacting applications.

6. Controlled Data Sharing

File System:

Complex to implement access control. Difficult to restrict users from modifying data.

Database:

Built-in security mechanisms. Can grant specific permissions to different user roles.

Chapter 3: Database Rules - Constraints

1. Primary Key

Unique identifier for each row in a table.

  • Must be unique
  • Cannot be NULL
  • Only one primary key per table

2. Foreign Key

Attribute in one table that links to another table's primary key. Enforces referential integrity.

3. Unique Key

Enforces uniqueness for a column's values but allows NULL values (unlike primary key).

4. NOT NULL

Ensures a column cannot have NULL values.

5. DEFAULT

Assigns a default value to a column if no value is specified during insertion.

6. CHECK

Ensures values in a column satisfy a specific condition (e.g., Age >= 18).

Difference: Primary Key vs Unique Key

Primary Key

  • ✓ Enforces uniqueness
  • ✓ Does NOT allow NULL
  • ✓ Only one per table

Unique Key

  • ✓ Enforces uniqueness
  • ✓ Allows NULL values
  • ✓ Multiple allowed per table

Degree and Cardinality

Degree: The number of columns (attributes) in a table.

Cardinality: The number of rows (tuples) in a table.

Example: Students table with columns ID, Name, Age and 5 rows:

• Degree = 3 (columns: ID, Name, Age)

• Cardinality = 5 (rows)

Chapter 4: Data Types in SQL

SQL Data Types Overview

1. Numeric Data Types

bit, tinyint, smallint
int, bigint
decimal, numeric, float, real

2. Date/Time Data Types

Date
Time
Datetime, Timestamp, Year

3. Character/String Data Types

Char
Varchar
Varchar(max), Text

CHAR vs VARCHAR

CHAR(n)

  • • Fixed-length string (1-255 characters)
  • • Example: CHAR(20) stores "Kunal" as 20 characters with blank spaces
  • • Wastage of space if actual string is shorter

VARCHAR(n)

  • • Variable-length string up to maximum
  • • Example: VARCHAR(20) stores "Kunal" as only 5 characters
  • • No wastage of space

Max Size of Literals

  • Text Literal: TINYTEXT → 255 | TEXT → 65,535 | MEDIUMTEXT → 16,777,215
  • Numeric Literal: DECIMAL (65,30)

Chapter 5: SQL Commands

1. DDL (Data Definition Language)

Commands that define and manage the structure of database objects.

CREATE

CREATE DATABASE dbname;
CREATE TABLE tablename (
column1 datatype,
column2 datatype
);

Example: Creating Instagram Users

CREATE TABLE InstaUsers (
UserID int PRIMARY KEY,
Handle varchar(50),
Followers int
);

ALTER

ALTER TABLE tablename ADD columnname datatype;
ALTER TABLE tablename MODIFY columnname datatype;
ALTER TABLE tablename DROP columnname;
ALTER TABLE child_table ADD FOREIGN KEY (col) REFERENCES parent_table(col);

Example: Adding Bio

ALTER TABLE InstaUsers ADD Bio varchar(150);

DROP

DROP TABLE tablename;

2. DML (Data Manipulation Language)

Commands used to manipulate data within database tables.

INSERT

INSERT INTO tablename VALUES(value1, value2, ...);

Example: User Signup (Aarav)

INSERT INTO InstaUsers VALUES(1, '@aarav_codes', 500);

UPDATE

UPDATE tablename
SET column = value
WHERE condition;

Example: Updating Followers (Diya)

UPDATE InstaUsers
SET Followers = 1000
WHERE Handle = '@diya_trends';

DELETE

DELETE FROM tablename
WHERE condition;

Example: Removing Spam (Vihaan)

DELETE FROM InstaUsers
WHERE Handle = '@vihaan_spam';

4. DQL (Data Query Language)

Commands used to query data within database tables.

SELECT

SELECT column1, column2, ...
FROM tablename
WHERE condition;

Example: Finding Influencers

SELECT Handle, Bio
FROM InstaUsers
WHERE Followers > 10000;

Chapter 6: SELECT Commands - Querying Data

Aggregate Functions

COUNT()

SELECT COUNT(age) FROM employee;

Counts non-null values

AVG()

SELECT AVG(age) FROM employee;

Calculates average

SUM()

SELECT SUM(salary) FROM employee;

Returns sum of values

MIN() / MAX()

SELECT MIN(age), MAX(age) FROM employee;

Finds minimum/maximum

COUNT(column) vs COUNT(*)

COUNT(column_name)

Counts only non-NULL values in the specified column.

SELECT COUNT(email) FROM employees;

Returns 3 if 4 rows exist but 1 has NULL email

COUNT(*)

Counts all rows in the table, including NULL values.

SELECT COUNT(*) FROM employees;

Returns 4 (total rows regardless of NULLs)

WHERE Clause Examples

LIKE with Wildcards

WHERE name LIKE "am%" -- starts with "am"
WHERE name LIKE "%a" -- ends with "a"
WHERE name LIKE "%a%" -- contains "a"
WHERE name LIKE "_a%" -- "a" in 2nd position

BETWEEN

SELECT * FROM products WHERE price BETWEEN 1000 AND 5000;

Includes both 1000 and 5000 (inclusive)

IN / NOT IN

SELECT * FROM customers WHERE state IN ("Delhi", "Mumbai");

ORDER BY

ORDER BY price ASC -- ascending
ORDER BY price DESC -- descending

DISTINCT

SELECT DISTINCT customer_name FROM orders;

Chapter 8: GROUP BY and HAVING

GROUP BY

Groups rows that have the same values in specified columns. Used with aggregate functions.

SELECT dept_id, SUM(salary) AS total_salaries
FROM employees
GROUP BY dept_id;

Groups employees by department and calculates total salary for each department.

HAVING Clause

Filters groups after GROUP BY. Similar to WHERE but works on grouped data.

SELECT dept_id, SUM(salary)
FROM employees
GROUP BY dept_id
HAVING SUM(salary) > 5000;

Shows only departments with total salary greater than 5000.

Query Order

SELECT columnname
FROM tablename
WHERE condition
GROUP BY columnname
HAVING condition
ORDER BY columnname;

Key Takeaways

  • SQL Commands: DDL (structure), DML (data), DQL (query)
  • Constraints: Primary Key (unique, not null), Foreign Key (relationships), Unique, NOT NULL, DEFAULT, CHECK
  • Data Types: CHAR (fixed) vs VARCHAR (variable), Numeric, Date/Time types
  • SELECT: WHERE, LIKE, BETWEEN, IN, ORDER BY, GROUP BY, HAVING
Get in Touch