SQL and MySQL – Detailed Notes
1. What is SQL?
SQL (Structured Query Language) is a standard programming language used to manage and
manipulate relational databases.
It is used to create databases, store data, retrieve data, update data and delete data.
Key Uses of SQL:
• Create and manage databases
• Insert, update, delete records
• Retrieve data using queries
• Create tables, views, and indexes
• Manage permissions
Example:
SELECT * FROM students;
2. What is MySQL?
MySQL is a relational database management system (RDBMS) that uses SQL as its query
language.
It is open-source and widely used for web applications.
Difference:
SQL → Language
MySQL → Database software that uses SQL
Examples of other RDBMS:
• Oracle
• SQL Server
• PostgreSQL
• SQLite
3. Types of SQL Commands
SQL commands are divided into five main categories.
1. DDL (Data Definition Language)
Used to define database structure.
Commands:
CREATE – create database/table
ALTER – modify table
DROP – delete table
TRUNCATE – remove all records
RENAME – rename object
Example:
CREATE TABLE student(
id INT,
name VARCHAR(50)
);
2. DML (Data Manipulation Language)
Used to manipulate data.
Commands:
INSERT
UPDATE
DELETE
Example:
INSERT INTO student VALUES (1,'Rahul');
3. DQL (Data Query Language)
Used to retrieve data.
Command:
SELECT
Example:
SELECT * FROM student;
4. TCL (Transaction Control Language)
Commands:
COMMIT – save changes
ROLLBACK – undo changes
SAVEPOINT – create rollback point
5. DCL (Data Control Language)
Commands:
GRANT – give permission
REVOKE – remove permission
4. Data Types in SQL
Data types define the type of data stored in a column.
Numeric Data Types
INT – integer values
BIGINT – large integers
FLOAT – decimal numbers
DECIMAL – precise decimal values
Example:
salary DECIMAL(10,2)
Character Data Types
CHAR(n) – fixed length string
VARCHAR(n) – variable length string
TEXT – large text data
Example:
name VARCHAR(50)
Date and Time Data Types
DATE – stores date
TIME – stores time
DATETIME – date and time
TIMESTAMP – auto timestamp
Example:
dob DATE
5. Constraints in SQL
Constraints are rules applied on columns to maintain data integrity.
1. NOT NULL
Column cannot contain NULL values.
Example:
name VARCHAR(50) NOT NULL
2. UNIQUE
All values must be unique.
Example:
email VARCHAR(100) UNIQUE
3. PRIMARY KEY
Uniquely identifies each row.
Cannot contain NULL.
Example:
id INT PRIMARY KEY
4. FOREIGN KEY
Creates relationship between two tables.
Example:
FOREIGN KEY (dept_id) REFERENCES department(id)
5. CHECK
Ensures a condition is satisfied.
Example:
age INT CHECK (age >= 18)
6. DEFAULT
Provides default value.
Example:
country VARCHAR(50) DEFAULT 'India'
6. Attributes, Tuples, Degree and Cardinality
These are basic database terms used in relational model.
Attribute
An attribute is a column in a table.
Example Table: Student
ID | Name | Age
Here ID, Name, Age are attributes.
Tuple
A tuple is a row in a table.
Example:
1 | Rahul | 21
Degree
Degree means the number of columns (attributes) in a table.
Example:
Student table has columns:
ID, Name, Age
Degree = 3
Cardinality
Cardinality means the number of rows (tuples) in a table.
Example:
If Student table has 50 rows
Cardinality = 50
7. Example Table
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Salary DECIMAL(10,2),
Department VARCHAR(50)
);
Insert Example:
INSERT INTO Employee VALUES (1,'Amit',50000,'IT');