1. Introduction to Databases
A database is an organized collection of structured data that can be stored, accessed, and managed efficiently. A Database Management System (DBMS) is software that manages databases.
Examples of DBMS: MySQL, Oracle, PostgreSQL, SQLite, Microsoft SQL Server.
2. DBMS vs File System
| Feature | File System | DBMS |
|---|---|---|
| Data Redundancy | High (duplicate data) | Low (controlled redundancy) |
| Data Consistency | Difficult to maintain | Enforced through constraints |
| Data Security | Limited | Comprehensive access control |
| Data Integrity | Not enforced | Enforced through constraints |
| Concurrent Access | Not supported | Supported with ACID properties |
3. Relational Database Model
The Relational Database Model organizes data into tables (relations) consisting of rows (tuples) and columns (attributes).
- Relation: A table with rows and columns.
- Tuple: A single row representing a record.
- Attribute: A column representing a property.
- Degree: Number of attributes (columns).
- Cardinality: Number of tuples (rows).
- Domain: Set of allowed values for an attribute.
4. Keys in RDBMS
| Key Type | Description | Example |
|---|---|---|
| Primary Key | Uniquely identifies each row; no NULL values | Student ID |
| Candidate Key | A potential primary key | Email, Aadhar Number |
| Foreign Key | References primary key of another table | Dept ID in Employee table |
| Alternate Key | Candidate keys not selected as primary key | Email if ID is primary key |
| Composite Key | Combination of columns serving as primary key | CourseID + StudentID |
5. Introduction to SQL
| Category | Full Form | Commands |
|---|---|---|
| DDL | Data Definition Language | CREATE, ALTER, DROP, TRUNCATE |
| DML | Data Manipulation Language | INSERT, UPDATE, DELETE, SELECT |
| DCL | Data Control Language | GRANT, REVOKE |
| TCL | Transaction Control Language | COMMIT, ROLLBACK, SAVEPOINT |
6. DDL Commands
DDL commands define the structure of the database.
7. CREATE TABLE
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Class VARCHAR(10),
Marks FLOAT,
City VARCHAR(30)
);Data types: INT, FLOAT, CHAR(n), VARCHAR(n), DATE, TEXT, BOOLEAN.
8. ALTER TABLE
-- Add a new column
ALTER TABLE Student ADD Email VARCHAR(50);
-- Modify an existing column
ALTER TABLE Student MODIFY Marks INT;
-- Drop a column
ALTER TABLE Student DROP COLUMN Email;9. DROP and TRUNCATE
DROP TABLE Student;
TRUNCATE TABLE Student;10. DML Commands
DML commands manipulate data in the database.
11. INSERT INTO
INSERT INTO Student VALUES (101, "Riya", "XII", 95.5, "Delhi");
INSERT INTO Student (RollNo, Name, Class) VALUES (104, "Rahul", "XII");12. SELECT Statement
SELECT * FROM Student;
SELECT Name, Marks FROM Student;
SELECT Name AS StudentName FROM Student;13. WHERE Clause
SELECT * FROM Student WHERE Marks > 90;
SELECT * FROM Student WHERE Class = "XII" AND City = "Delhi";
SELECT * FROM Student WHERE Name LIKE "R%";
SELECT * FROM Student WHERE Marks BETWEEN 80 AND 100;
SELECT * FROM Student WHERE City IN ("Delhi", "Mumbai");14. Aggregate Functions
| Function | Description | Example |
|---|---|---|
| COUNT() | Counts rows | SELECT COUNT(*) FROM Student; |
| SUM() | Sum of values | SELECT SUM(Marks) FROM Student; |
| AVG() | Average of values | SELECT AVG(Marks) FROM Student; |
| MAX() | Maximum value | SELECT MAX(Marks) FROM Student; |
| MIN() | Minimum value | SELECT MIN(Marks) FROM Student; |
15. GROUP BY and HAVING
SELECT City, COUNT(*), AVG(Marks) FROM Student GROUP BY City;
SELECT City, AVG(Marks) FROM Student GROUP BY City HAVING AVG(Marks) > 85;16. ORDER BY
SELECT * FROM Student ORDER BY Marks;
SELECT * FROM Student ORDER BY Marks DESC;
SELECT * FROM Student ORDER BY Class ASC, Marks DESC;17. UPDATE and DELETE
UPDATE Student SET Marks = 96 WHERE RollNo = 101;
DELETE FROM Student WHERE RollNo = 104;
DELETE FROM Student;18. Joins
CREATE TABLE Course (CourseID INT PRIMARY KEY, CourseName VARCHAR(50), RollNo INT, FOREIGN KEY (RollNo) REFERENCES Student(RollNo));
SELECT Student.Name, Course.CourseName FROM Student INNER JOIN Course ON Student.RollNo = Course.RollNo;
SELECT Student.Name, Course.CourseName FROM Student LEFT JOIN Course ON Student.RollNo = Course.RollNo;19. Subqueries
SELECT Name FROM Student WHERE Marks = (SELECT MAX(Marks) FROM Student);
SELECT Name FROM Student WHERE RollNo IN (SELECT RollNo FROM Course WHERE CourseName = "Computer Science");20. Integrity Constraints
| Constraint | Description |
|---|---|
| NOT NULL | Column cannot have NULL values |
| UNIQUE | All values must be different |
| PRIMARY KEY | NOT NULL + UNIQUE |
| FOREIGN KEY | References primary key in another table |
| CHECK | Validates values based on condition |
| DEFAULT | Default value for column |
21. Revision Questions and Answers
Very Short Answer Questions
1. What is DBMS?
A software system that manages databases.
2. Expand SQL.
Structured Query Language.
3. Which command creates a table?
CREATE TABLE.
4. Which constraint ensures unique values?
UNIQUE.
5. Which function counts rows?
COUNT().
Short Answer Questions
1. Differentiate between DDL and DML.
DDL defines database structure (CREATE, ALTER, DROP). DML manipulates data (INSERT, UPDATE, DELETE, SELECT).
2. Explain primary key and foreign key.
Primary key uniquely identifies rows. Foreign key references primary key of another table to create relationships.
Long Answer Questions
1. Write SQL queries: (a) Create table Employee (EmpID, Name, Salary, DeptID). (b) Insert 5 records. (c) Display employees with salary > 50000. (d) Department-wise avg salary.
CREATE TABLE Employee (EmpID INT PRIMARY KEY, Name VARCHAR(50), Salary FLOAT, DeptID INT);
INSERT INTO Employee VALUES (1, "Riya", 60000, 101), (2, "Amit", 45000, 102);
SELECT * FROM Employee WHERE Salary > 50000;
SELECT DeptID, AVG(Salary) FROM Employee GROUP BY DeptID;
2. Explain GROUP BY and HAVING.
GROUP BY groups rows with same values for aggregate functions. HAVING filters groups (like WHERE for rows). Example: SELECT City, COUNT(*) FROM Student GROUP BY City HAVING COUNT(*) > 1;