SQL
1. Relational Database (RDBMS)
• Stores data in tables (rows & columns)
• Uses SQL
Examples: MySQL, Oracle, PostgreSQL
2. NoSQL Database
• Stores data in flexible formats (not tables)
• High speed & scalability
Types of NoSQL:
• Document DB (JSON) → MongoDB
• Key-Value DB → Redis
• Column DB → Cassandra
• Graph DB → Neo4j
3. Object-Oriented Database
• Stores data as objects (like OOP)
Example: ObjectDB, db4o
4. Hierarchical Database
• Data arranged in tree structure
Example: IBM IMS
5. Network Database
• Data arranged like a graph (multiple parents)
Example: IDMS
DATA TYPES
1. Numeric Types
SQL
• INT → whole numbers
FLOAT / DOUBLE → decimal numbers
• DECIMAL(p, s) → exact decimal (money)
2. String / Character Types
• CHAR(n) → fixed-length text
• VARCHAR(n) → variable-length text
• TEXT → long text
3. Date & Time Types
• DATE → YYYY-MM-DD
• TIME → HH:MM:SS
• DATETIME → date + time
• YEAR→ year
4. Binary Type
• blob → binary large object it is use for image, vedio, pdf
COMMANDS
DQL – Data Querry Language
SELECT Statement in MySQL (Simple Explanation)
The SELECT statement is used to retrieve (view) data from a table in MySQL.
Basic Syntax
SELECT
Example 1: Select All Columns
SELECT * FROM student;
SQL
Displays all rows and all columns from student table.
Example 2: Select Specific Columns
SELECT name, gpa FROM student;
Displays only name and gpa.
Select with Condition (WHERE)
SELECT * FROM student
WHERE gpa > 8.0;
Select with Multiple Conditions
SELECT * FROM student
WHERE gpa > 7 AND income < 50000;
SELECT DISTINCT
SELECT DISTINCT gpa FROM student;
Removes duplicate values.
Sorting Data (ORDER BY)
SELECT * FROM student
ORDER BY gpa DESC;
Limit Rows (LIMIT)
SELECT * FROM student
LIMIT 5;
Aggregate Functions
SQL
SELECT COUNT(*) FROM student;
SELECT AVG(gpa) FROM student;
SELECT MAX(gpa) FROM student;
Column Alias (AS)
SELECT name AS Student_Name, gpa AS GPA_Score
FROM student;
Using LIKE
SELECT * FROM student
WHERE name LIKE 'S%';
DDL – Data Definition Language [Link]
(Used to create or change structure)
CREATE
CREATE TABLE student (id INT, name VARCHAR(50));
Rename
Syntax
RENAME TABLE old_table_name TO new_table_name;
Example
RENAME TABLE student TO students;
Table name changed from student → students
ALTER
ADD
Use: Add a new column
SQL
ALTER TABLE student ADD age INT;
DROP
Use: Delete a column
ALTER TABLE student DROP COLUMN income;
MODIFY
Use: Change datatype/size of a column
ALTER TABLE student MODIFY gpa DECIMAL(3,2);
RENAME
Use: Rename column or table
Rename column
ALTER TABLE student RENAME COLUMN roolno TO rollno;
Rename table
ALTER TABLE student RENAME TO students;
DROP
DROP TABLE student;
TRUNCATE
TRUNCATE TABLE student;
DML – Data Manipulation Language uid
(Used to work with data)
INSERT
INSERT INTO student VALUES (1, 'Santhi', 22);
Insert into table name(colume name)values()
UPDATE
UPDATE student SET age = 23 WHERE id = 1;
SQL
DELETE
DELETE FROM student WHERE id = 1;
DCL – Data Control Language gr
(Used to control permissions)
GRANT
GRANT SELECT ON student TO user1;
REVOKE
REVOKE SELECT ON student FROM user1;
TCL – Transaction Control Language scr
(Used to manage transactions)
COMMIT
COMMIT;
ROLLBACK
ROLLBACK;
SAVEPOINT
SAVEPOINT A;
1. CREATE DATABASE
Syntax:
CREATE DATABASE database_name;
Example:
CREATE DATABASE school;
2. CREATE TABLE
Syntax:
SQL
CREATE TABLE table_name (
column1 datatype,
column2 datatype
);
Example:
CREATE TABLE student (
id INT,
name VARCHAR(50),
age INT
);
3. CREATE VIEW
CREATE VIEW student_names AS
SELECT name FROM student;
4. CREATE INDEX
CREATE INDEX idx_name ON student(name);
show datatabes
SHOW DATABASES;
Create table
Create table table_name(column_name1 datatype(size),)
To see the description of table
desc table; …
INSERT Command (DML)
Used to add new data (rows) into a table.
SQL
1. Basic Syntax
INSERT INTO table_name VALUES (value1, value2, value3);
To see the values (rows) stored in a table in MySQL, use:
SELECT * FROM table_name;
INSERT values into SPECIFIC COLUMNS in MySQL, use this syntax
(SQL keywords in CAPITAL letters as you asked):
INSERT INTO STUDENT (ROLLNO, NAME, INCOME)
VALUES (10, 'SWAROOP', 1000.11);
DATE_FORMAT
• MySQL DATE_FORMAT Specifiers (Short)
• Year
• %Y → 2024
• %y → 24
• Month
• %M → January
• %b → Jan
• %m → 01
• %c → 1
• Day
• %d → 15
• %e → 5
• %D → 15th
• %j → 074
• Weekday
• %W → Monday
• %a → Mon
• %w → 0–6 (Sun=0)
SQL
• Time
• %H → 24-hour
• %h / %I → 12-hour
• %i → Minutes
• %s / %S → Seconds
• %p → AM / PM
• Combined
• %Y-%m-%d → 2024-03-15
• %H:%i:%s → 18:45:30
• %r → 06:45:30 PM
• %% → %
Example:
SELECT DATE_FORMAT(CURDATE(), '%d-%M-%Y');
Output: 15-January-2024
);