Class Notes: Database Concepts and Structured Query Language (SQL)

1. Database Concepts

• What is a Database?
  • A database is a collection of information or data organized in a way that it can be easily accessed, managed, and updated. For example, a school database can store student details like names, roll numbers, marks, etc.
• Why Do We Need Databases?
  • To store data systematically.
  • To retrieve or update data quickly.
  • To avoid data duplication or mistakes.
• What is a Database Management System (DBMS)?
  • A DBMS is software used to manage databases. It helps users create, store, and retrieve data easily.
  • Examples: MySQL, Oracle, and Microsoft SQL Server.

---

2. Relational Data Model

• A relational database organizes data into tables, where each table is called a relation.
• Key Terms:
  • Domain: A set of allowed values for a column. For example, the “Grade” column in a table may allow values like A, B, C, etc.
  • Tuple: A row in a table. Each row stores information about one entry.
  • Relation: A table in the database.
  • Candidate Key: A column (or a set of columns) that can uniquely identify a row in a table.
  • Primary Key: A column selected as the unique identifier for the table. For example, the “Roll Number” column can be a primary key.
  • Alternate Key: Other columns that can also act as a candidate key but are not selected as the primary key.

---

3. Advantages of SQL (Structured Query Language)

• SQL is used to manage and interact with databases.
• Benefits:
  • Easy to use and learn.
  • Allows fast data retrieval and updates.
  • Helps maintain large datasets efficiently.
  • Works with different database systems like MySQL, PostgreSQL, etc.

---

4. Categories of SQL

1. Data Definition Language (DDL):
   • Used to define the structure of a database.
   • Commands:
     • CREATE: To create a database or table.
     • DROP: To delete a database or table.
     • ALTER: To modify the structure of a table.
2. Data Manipulation Language (DML):
   • Used to retrieve data from a database.
   • Commands:
     • SELECT: To retrieve specific data from a table.
     • Used to change or manipulate data in a database.
     • Commands:
       • INSERT: To add new data.
       • UPDATE: To modify existing data.
       • DELETE: To remove data from a table.

---

5. Introduction to MySQL

• What is MySQL?
  MySQL is an open-source database management system that uses SQL to manage and query data.
• Steps to Use MySQL:
  • Install MySQL software.
  • Use commands to create and manage databases.
• Data Types in MySQL:
  • INT: To store numbers.
  • VARCHAR: To store text data.
  • DATE: To store dates.

---

6. Common SQL Commands

1. Data Definition (DDL):
   • CREATE DATABASE: To create a new database.
     Example: CREATE DATABASE School;
   • CREATE TABLE: To create a table with specific columns.
     Example:sqlCopy codeCREATE TABLE Students ( RollNo INT, Name VARCHAR(50), Class INT );
   • DROP: To delete a database or table.
     Example: DROP TABLE Students;
   • ALTER: To modify a table.
     Example: ALTER TABLE Students ADD Marks INT;
2. Data Manipulation (DML):
   • SELECT: To retrieve data.
     Example: SELECT * FROM Students;
   • WHERE: To filter data based on conditions.
     Example: SELECT * FROM Students WHERE Class = 10;
   • Relational Operators:
     • = (equal to), < (less than), > (greater than), >=, <=
   • Logical Operators:
     • AND, OR, NOT
   • Special Operators:
     • BETWEEN: To filter data within a range.
       Example: SELECT * FROM Students WHERE Marks BETWEEN 40 AND 80;
     • IS NULL: To find empty values.
       Example: SELECT * FROM Students WHERE Marks IS NULL;
   • INSERT
     • INSERT: To add new data.
       Example:sqlCopy codeINSERT INTO Students (RollNo, Name, Class) VALUES (1, 'John', 10);
   • DELETE
     • DELETE: To remove data.
       Example: DELETE FROM Students WHERE RollNo = 1;
   • UPDATE
     • UPDATE: To modify data.
       Example:sqlCopy codeUPDATE Students SET Marks = 90 WHERE RollNo = 1;