Database concepts
Objective:
----------
This chapter introduces you to the fundamentals of databases and SQL (Structured Query Language). You will learn what databases are, why they are important, and how SQL is used to interact with them.
Table of Contents:
------------------
1. What is a Database?
2. What is SQL?
3. Types of Databases
4. RDBMS and Popular SQL Databases
5. Basic SQL Syntax
6. Key Concepts: Tables, Rows, and Columns
7. SQL Statements Overview
8. Hands-on Practice with Examples
1. What is a Database?
----------------------
A database is an organized collection of data that can be easily accessed, managed, and updated. It helps store large amounts of data efficiently and allows users to retrieve, update, and manipulate data quickly and securely.
Examples:
- - Banking system storing customer transactions
- - Online shopping storing orders and products
- - Hospital managing patient records
2. What is SQL?
---------------
SQL stands for Structured Query Language. It is the standard language used to:
- Create and modify database structures (DDL)
- Insert, update, delete, and retrieve data (DML and DQL)
- Control user access and permissions (DCL)
SQL is case-insensitive, though keywords are often written in uppercase for readability.
3. Types of Databases
---------------------
There are several types of databases:
- Relational databases (e.g., MySQL, Oracle, SQL Server)
- NoSQL databases (e.g., MongoDB, Cassandra)
- In-memory databases (e.g., Redis)
- Cloud databases (e.g., Amazon RDS, Google Cloud SQL)
This course focuses on Relational Databases and SQL.
4. What is RDBMS?
-----------------
RDBMS stands for Relational Database Management System. It stores data in tables (rows and columns) and maintains relationships between them.
Popular RDBMS software:
- - Oracle Database
- - MySQL
- - Microsoft SQL Server
- - PostgreSQL
Each RDBMS may have slightly different SQL syntax, but the core concepts remain the same.
5. Basic SQL Syntax
-------------------
SQL statements follow this general format:
SELECT column1, column2
FROM table_name
WHERE condition;
- - SQL statements end with a semicolon (;)
- - Keywords are not case-sensitive (SELECT = select)
- - Strings should be enclosed in single quotes
6. Key Concepts
---------------
Table – A collection of data organized into rows and columns
Row (Record) – A single entry in a table
Column (Field) – A specific attribute in a table
Example Table: students
| student_id | name | age | grade |
|------------|----------|-----|-------|
| 1 | Alice | 20 | A |
| 2 | Bob | 21 | B |
7. SQL Statements Overview
--------------------------
- DDL (Data Definition Language): Create or alter structure
- CREATE, ALTER, DROP
- DML (Data Manipulation Language): Insert/update/delete data
- INSERT, UPDATE, DELETE
- DQL (Data Query Language): Retrieve data
- SELECT
- DCL (Data Control Language): Grant or revoke permissions
- GRANT, REVOKE
- TCL (Transaction Control Language): Control transactions
- COMMIT, ROLLBACK, SAVEPOINT
8. Hands-on Practice
--------------------
Step 1: Create a Table
----------------------
CREATE TABLE students (
student_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
age NUMBER,
grade CHAR(1)
);
Step 2: Insert Data
-------------------
INSERT INTO students VALUES (1, 'Alice', 20, 'A');
INSERT INTO students VALUES (2, 'Bob', 21, 'B');
INSERT INTO students VALUES (3, 'Charlie', 22, 'C');
Step 3: Retrieve Data
---------------------
SELECT * FROM students;
SELECT name, grade FROM students WHERE age > 20;
Step 4: Update Data
-------------------
UPDATE students SET grade = 'A' WHERE name = 'Bob';
Step 5: Delete Data
-------------------
DELETE FROM students WHERE student_id = 3;
Step 6: Drop Table
------------------
DROP TABLE students;
Conclusion:
-----------
This chapter introduced the foundation of SQL and relational databases. You learned about the structure of databases, what SQL is, and how to write basic SQL commands. In the next chapter, we will explore more advanced SELECT statements including filtering, sorting, and joining tables.
0 Comments