Database Testing

Part 1:
Data base is a storage area, where actual application data is stored or
maintained.

If we want to communicate with database/ work with data base then

we need to know query language called SQL.

SQL: Structure Query Language.

Front-End: UI Selenium, Tosca

Backend Testing: API, Postman, Karate Rest API, Soap
Database Testing: Mainly we test data base operations.

Which is white box as well as black box testing.

We mainly focus on database objects, schema and data.

Database contains tables, view indexes., contains transactional data.
1. Database Testing
What: Testing the backend database of an application.
Focus: Data integrity, correctness, and accuracy in tables.
Example: After filling a form in a web app, checking if data is
correctly stored in the database.
You test:
 Data stored correctly?
 Data constraints (like NOT NULL, UNIQUE)?
 Joins and queries returning correct data?
2. ETL Testing (Extract, Transform, Load)
What: Testing the ETL process that moves data from one system
to another.
Focus: Ensures that data is extracted, transformed, and loaded
correctly.
Example: Moving data from a sales application to a data
warehouse.
You test:
 Data extracted properly from source?
 Transformation rules applied correctly?
 Data loaded properly in the target system?
3. BI Testing (Business Intelligence)
What: Testing dashboards and reports that help businesses
make decisions.
Focus: Ensures reports show correct and meaningful data from
the warehouse.
Example: A sales report showing total revenue by region.
You test:
  Report values match the data warehouse?
 Charts show correct trends?
 Filters and drill-downs work properly?
Relation Between Them:
Application Data
↓
Database Testing
↓
ETL Process (Extract-Transform-Load)
↓
ETL Testing
↓
Data Warehouse / BI Tool
↓
BI Testing
 Database Testing checks the original data.
 ETL Testing checks the flow of data between systems.
 BI Testing checks how that data is shown in reports.

Types of Database Testing categorized into:

  White Box Testing (Structural)
 Black Box Testing (Architectural)
o Functional Testing
o Non-Functional Testing

✅ 1. White Box Testing (Structural)

Tester knows internal structure (DB schema, logic)
Schema Testing includes:
 Table
 Table Columns
 Views
 Stored Procedures
 Functions
 Triggers

✅ 2. Black Box Testing (Architectural)

Tester doesn’t need internal DB knowledge—only tests input/output
and behavior
a) Functional Testing
Tests whether the database behaves as expected based on business
rules.
Includes:
 DML Operations w.r.t GUI (Insert, Update, Delete through UI)
 Data Mapping (data from UI → DB)
 Data Integrity & Keys (FK, PK, etc.)
b) Non-Functional Testing
Tests performance and security aspects.
Includes:
 Performance Testing
o Load Testing
o Stress Testing
 Security Testing

Ahhh got it! You were laying out a data journey from raw to
structured management and operations. Let me polish your flow into
a clean, logical format that shows the relationship from data to SQL
in an easy-to-understand path.

🔄 Complete Flow: From Data to SQL Operations

1. Raw Data
⬇️
2. Structured Format (rows/columns, tables)
⬇️
3. Stored in Database (organized collection of data)
⬇️
4. Managed by DBMS (Database Management System)
⬇️
5. Types of DBMS:
o Hierarchical
o Network
 o Relational (RDBMS)
⬇️
6. RDBMS Example: Oracle
⬇️
7. Tool to interact: Oracle SQL Developer
⬇️
8. Perform operations on data using SQL:
o SELECT – Retrieve
o INSERT – Add
o UPDATE – Modify
o DELETE – Remove
⬇️
9. To do this, you need knowledge of: SQL (Structured Query
Language)

🧠 In short:
Data → Structured → Database → Managed by DBMS → RDBMS
(like Oracle) → Use SQL Developer → Perform operations using SQL

SQL Beginner Cheat Sheet

1. Select Data
Task Query
All columns SELECT * FROM Employees;
Specific columns SELECT EmpName, Salary FROM Employees;
2. WHERE Clause (Filter Rows)
Task Query
Salary greater than SELECT * FROM Employees WHERE Salary >
50000 50000;
SELECT * FROM Employees WHERE
Department is IT
Department = 'IT';
SELECT * FROM Employees WHERE EmpName
Name starts with A
LIKE 'A%';
SELECT * FROM Employees WHERE EmpName
Name ends with n
LIKE '%n';
Name has ‘ra’ SELECT * FROM Employees WHERE EmpName
anywhere LIKE '%ra%';
Tip: Strings → 'single quotes'
Numbers → no quotes

3. Aggregate Functions (Summary Values)

Task Query
Count total SELECT COUNT(*) AS TotalEmployees FROM
employees Employees;
SELECT MAX(Salary) AS HighestSalary FROM
Maximum salary
Employees;
SELECT MIN(Salary) AS LowestSalary FROM
Minimum salary
Employees;
SELECT AVG(Salary) AS AvgSalary FROM
Average salary
Employees;
Total salary SELECT SUM(Salary) AS TotalSalary FROM
Task Query
Employees;

4. ORDER BY (Sort Data)

Task Query
Salary descending SELECT * FROM Employees ORDER BY Salary DESC;
Salary ascending SELECT * FROM Employees ORDER BY Salary ASC;

5. LIMIT / TOP (Get Few Rows)

Task Query
First 5
SELECT * FROM Employees LIMIT 5; (MySQL)
employees
SELECT TOP 5 * FROM Employees ORDER BY Salary
Top 5 salaries
DESC; (SQL Server)

6. Combining Conditions
Task Query
Salary > 50000 AND SELECT * FROM Employees WHERE Salary >
Dept = IT 50000 AND Department = 'IT';
Salary > 50000 OR SELECT * FROM Employees WHERE Salary >
Dept = IT 50000 OR Department = 'IT';
SELECT * FROM Employees WHERE
NOT in IT dept
Department != 'IT';

7. Key Rules
1. SELECT → choose columns
 2. FROM → table name
3. WHERE → filter rows
4. Strings → 'single quotes', numbers → no quotes
5. Aggregates → COUNT(), MAX(), MIN(), AVG(), SUM()
6. Patterns → LIKE 'A%' etc.
7. * → all columns

💡 Quick Tip: Think SELECT → FROM → WHERE → GROUP/ORDER →

LIMIT as the order of building your query.
Example 1:

--creating_table

CREATE TABLE RStudent

(

"First Name" char(10),

"Last Name" varchar(255),

ID int,

Experience int
);

SELECT * FROM RStudent;

--1] INSERT INTO without using coloumn name

INSERT INTO RStudent

VALUES ('Vivek','Thakur', 1,3); -- jevdhe column thevde sequence nusar values

select * from RSTUDENT;

--Insert record – pass name above defined char datatype range

INSERT INTO RSTUDENT

VALUES('Vivekkkkkkkkkkkk','Thakur',2,6); --value too large for column

INSERT INTO RStudent

VALUES ('Vivek','Thakur'); -- jevdhe column thevde sequence nusar values, "not enough
values"

--2] To over come from above problem we have second option: by giving particular coloumn
name and Values

INSERT INTO RSTUDENT ("First Name", "Last Name")

VALUES('Virat', 'Shah');

SELECT * FROM RSTUDENT;

INSERT INTO RSTUDENT

VALUES('Rohit','Sharma',4,6);

select * from RSTUDENT;

INSERT INTO RSTUDENT

VALUES('Rohini','Surve',3,9);
select * from RSTUDENT;

INSERT INTO RSTUDENT

VALUES('Vaibhav','Shetty',5,12);

SELECT * FROM RSTUDENT;

Example 2:
CREATE TABLE pstudents (
FN VARCHAR2(255),
LN VARCHAR2(255),
Email VARCHAR2(255),
Mobile NUMBER(10),
City VARCHAR2(255)
);

-- View the table

SELECT * FROM pstudents;

-- Insert full data

INSERT INTO pstudents
VALUES ('DINESH', 'PANDIT', 'dinesh@gmail.com', 8747454785, 'Pune');

-- View the table again

SELECT * FROM pstudents;

-- Another full insert

INSERT INTO pstudents
VALUES ('SANJU', 'SAMSON', 'SAMSON@YAHOO.COM', 7845123654, 'DHULE');
-- View the table again
SELECT * FROM pstudents;

-- Insert only FN and LN (rest will be NULL)

INSERT INTO pstudents (FN, LN)
VALUES ('JAY', 'TUPE');

-- View again
SELECT * FROM pstudents;

-- Insert only Email, Mobile, City

INSERT INTO pstudents (Email, Mobile, City)
VALUES ('null000@gmail.com', 1010101010, 'Mars');

-- Final view
SELECT * FROM pstudents;

SQL Commands:
Awwwwmazing! Here's your SQL cheat sheet — clean and clear with emojis, but without
those number labels 😄

SQL Data types:

complete and clean list of commonly used SQL data types in proper
syntax format

🧱 Numeric Types
INT -- Whole numbers (e.g., ID, age)
SMALLINT -- Smaller whole numbers
BIGINT -- Large whole numbers
DECIMAL(p,s) -- Fixed-point with precision (e.g., DECIMAL(10,2) for
currency)
NUMERIC(p,s) -- Same as DECIMAL (exact values)
FLOAT -- Approximate floating-point numbers (less precision)
REAL -- Lower precision floating-point
DOUBLE PRECISION -- Higher precision floating-point

🔤 String/Text Types
CHAR(n) -- Fixed-length string (e.g., CHAR(10))
VARCHAR(n) -- Variable-length string (e.g., VARCHAR(100))
TEXT -- Long text data (e.g., descriptions, articles)

📆 Date & Time Types

DATE -- 'YYYY-MM-DD' (e.g., '2025-04-16')
TIME -- 'HH:MM:SS'
DATETIME -- 'YYYY-MM-DD HH:MM:SS' (MySQL)
TIMESTAMP -- Same as DATETIME but also stores time zone info
(PostgreSQL)
YEAR -- 'YYYY' (only in some SQL dialects like MySQL)

✅ Boolean Type
BOOLEAN -- TRUE or FALSE
BOOL -- Alias for BOOLEAN

📦 Binary Types
BINARY(n) -- Fixed-length binary data
VARBINARY(n) -- Variable-length binary data
BLOB -- Binary Large Object (e.g., images, files)

🧠 Special / Other Types

ENUM('val1', 'val2', ...) -- Set of predefined values (e.g.,
ENUM('small', 'medium', 'large'))
SET('a','b','c') -- Multiple values from a predefined list (only in
MySQL)
JSON -- Stores JSON-formatted data (used in MySQL,
PostgreSQL)
UUID -- Universally Unique Identifier (PostgreSQL, etc.)
XML -- For XML data (supported in some DBs)

🎁 Pro Tip: Always choose the data type that saves space and matches
the real-world meaning of the data. For example:
 Use DECIMAL for money 💰
 Use VARCHAR for names 🧑
 Use BOOLEAN for flags ✅

SQL Commands:

🧱 Create a Table
CREATE TABLE table_name (
col_name1 datatype,
col_name2 datatype,
col_name3 datatype,
col_name4 datatype
);
📝 Insert into All Columns
INSERT INTO table_name
VALUES ('value1', 'value2', 123, 456);
🎯 Insert into Specific Columns
INSERT INTO table_name (col_name1, col_name3)
VALUES ('value1', 123);
🔍 Fetch All Data
SELECT * FROM table_name;
Insert multiple rows into a table
 To insert multiple rows using a single INSERT statement, you use the
following construct:
Syntax / Query
INSERT INTO table_name
VALUES (value1, value2,...),
(value1, value2,...),
(value1, value2,...);

//INSERT INTO - Multiple Records

Example: -
INSERT INTO September
VALUES (2,'Ram','Reddy',7),
(3,'Raj','Reddy',8),
(4,'Ravi','Reddy',9);

INSERT INTO September (Id,FN,LN,Experience)

VALUES(2,'Ram','Reddy',7),
(3,'Raj','Reddy',8),
(4,'Ravi','Reddy',9);

INSERT INTO September (FN,LN,Experience)

VALUES('Ram','Reddy',7),
('Raj','Reddy',8),
('Ravi','Reddy',9);

Data types in SQL:

1) Data Type-date
Create Table on Database
Example-
CREATE TABLE M
(
DOB date
)

Demo Example:
-- date datatype

CREATE TABLE X101

DOB DATE -- DATE is data type for :

);

SELECT * FROM X101;

INSERT INTO X101

VALUES('27-06-2024'); -- dd-mm-yyyy , format should be same

SELECT * FROM X101;

-- WILL NOT ACCEPT WRONG FORMAT LIKE:

INSERT INTO X101

VALUES(2022-31-06);

2) Data Type-timestamp
CREATE TABLE N
(
Classtime timestamp
);
Demo Example:
--Timestamp

CREATE TABLE X102(

Class_Time TIMESTAMP --format is: 'HH:MM:SS'

);

SELECT * FROM X102;

INSERT INTO X102

VALUES ('10:5:55');

SELECT * FROM X102;

3) Numeric Data Type: int

//Data Type-int
CREATE TABLE O
(
ID int
);
Demo Example:
-- INT data type

Create Table X103(

ID int
);

Insert into X103

Values(123);
select * from X103;

4] Data Type-decimal
CREATE TABLE P
(
ID decimal(5,2)
);

DECIMAL(p, s) super simple for you 💡

This data type is used to store numbers with digits before and after the
decimal point, and it’s very useful for things like prices, scores, etc.

🧠 What it means:
DECIMAL(p, s)
 p = total number of digits (before and after the decimal)
 s = number of digits after the decimal point
👉 So, digits before decimal = (p - s)

✅ 5 Examples with Explanation:

1.
DECIMAL(4, 2)
 Total digits: 4
 2 digits after decimal
 ✅ Valid: 12.34, 99.99
 ❌ Invalid: 123.45 (has 5 digits)

2.
DECIMAL(5, 2)
 3 digits before, 2 after
 ✅ Valid: 123.45, 9.99
 ❌ Invalid: 1234.56 (too many digits)
3.
DECIMAL(3, 1)
 2 digits before, 1 after
 ✅ Valid: 99.9, 10.1
 ❌ Invalid: 100.0 (3 digits before decimal)

4.
DECIMAL(6, 3)
 3 digits before, 3 after
 ✅ Valid: 123.456, 1.999
 ❌ Invalid: 1234.567 (4 digits before)

5.
DECIMAL(2, 1)
 1 digit before, 1 after
 ✅ Valid: 9.9, 0.1
 ❌ Invalid: 10.0 (2 digits before)

🎯 Summary:
 The first number controls total digits (before + after)
 The second number tells how many are after the decimal
 Anything longer = ❌ error

Demo Example:
--decimal data type

Create table X104(

Bill decimal (3,2) -- here 3 is: No Total digit, 2 is: after decimal need 2 digit
);

select * from X104;

Insert into X104
Values(1.23);
select * from X104;

5] Data Type - char

CREATE TABLE Q
(
FN CHAR(3),
LN VARCHAR(255),
MobileNo int
);
Demo Example:
-- char data type

Create table X105

(
fName char (20), -- here define any desire lenght
cityName varchar (255), --Standard form
mNo int -- here by default upto 38 numbers will store
);

select * from X105;

Insert into X105

Values('Ganesh','Pune',4514256321);

select * from X105;

06] Data Type - varchar

CREATE TABLE R
(
FN CHAR(3),
LN VARCHAR(2),
MobileNo int
);
Demo Example:
select * from X105;
--------------------------------

-- varchar data type

Create table X106(

FN char (20),
LN varchar(255),
MNo int
);

select * from X106;

Insert into X106

Values('Shri','Patil',9852136478);

select * from X106;

Constraints 💪
Let’s keep it super simple, beginner-friendly, and effective — no confusion, just
clarity!

📘 What are Constraints?

Constraints are rules you apply on columns to control what kind of data is
allowed.
They help keep your data: ✅ Accurate
✅ Consistent
✅ Reliable
📜 Types of Constraints (with simple notes)
🔑 PRIMARY KEY
 Makes a column unique and not null
 Used to identify each row
id NUMBER PRIMARY KEY
📝 Only one primary key per table (can be one or more columns)

🧍‍♂️UNIQUE
 Ensures all values are different in that column
email VARCHAR2(100) UNIQUE
📝 Can be applied to multiple columns too

❌ NOT NULL
 Column cannot be empty
name VARCHAR2(50) NOT NULL
📝 Must insert a value — no skipping!

📎 CHECK
 Puts a condition/rule on values
age NUMBER CHECK (age >= 18)
📝 Rejects data that breaks the rule

🔗 FOREIGN KEY
 Links a column to another table’s PRIMARY KEY
dept_id NUMBER REFERENCES Departments(dept_id)
📝 Used to connect tables together

🧪 Example Table with Constraints

CREATE TABLE Students (
roll_no NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
age NUMBER CHECK (age >= 5),
 email VARCHAR2(100) UNIQUE,
dept_id NUMBER REFERENCES Departments(dept_id)
);

🧠 Super Easy Summary:

Constraint Meaning

PRIMARY Unique + Not Null (Main

KEY ID)

UNIQUE No duplicate values

NOT NULL Value must be given

CHECK Apply a condition or rule

FOREIGN
Link to another table
KEY

📋 InstagramUsers Table (with Constraints)

CREATE TABLE InstagramUsers (
user_id NUMBER PRIMARY KEY, -- Every user has a unique ID
username VARCHAR2(30) UNIQUE NOT NULL, -- No duplicate usernames,
must have one
email VARCHAR2(100) UNIQUE NOT NULL, -- Every user must have a
unique email
password VARCHAR2(50) NOT NULL, -- Password is required (duh!)
age NUMBER CHECK (age >= 13), -- Instagram rule: 13+ only
country VARCHAR2(50),
referral_id NUMBER REFERENCES InstagramUsers(user_id) -- Referral system
like "invited by"
);

💡 Let's Break it Down with Insta Vibes:

Constraint Insta Meaning 😎

PRIMARY
user_id – Each Insta user has a unique ID to identify them
KEY

UNIQUE username, email – No two people can have the same handle
Constraint Insta Meaning 😎

or email

NOT NULL You must enter a username, email, and password to sign up

CHECK age >= 13 – Instagram doesn’t allow kids under 13

FOREIGN referral_id – Let’s say User A invited User B → Linked through

KEY ID

🧱 Basic Structure:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);
You can add:
 one constraint
 multiple constraints
 or none (but not recommended in real-world apps)

✅ Example with Combo:

CREATE TABLE InstagramUsers (
user_id NUMBER PRIMARY KEY, -- 1 constraint
username VARCHAR2(30) UNIQUE NOT NULL, -- 2 constraints
email VARCHAR2(100) UNIQUE NOT NULL, -- 2 constraints
age NUMBER CHECK (age >= 13), -- 1 constraint
password VARCHAR2(50) NOT NULL -- 1 constraint
);

💥There are exactly 6 main types of constraints in SQL (including Oracle)

— think of them as different kinds of rules you can apply to your table columns.
Here’s your super clean and effective list 🔥

✅ Types of Constraints in SQL (Oracle)

Constraint What it does Example

Makes sure the column cannot be name VARCHAR2(50) NOT

NOT NULL
empty NULL

Makes sure all values are different email VARCHAR2(100)

UNIQUE
in the column UNIQUE

PRIMARY Combines UNIQUE + NOT NULL to user_id NUMBER PRIMARY

KEY identify each row KEY

FOREIGN dept_id REFERENCES

Links to a column in another table
KEY Departments(id)

Adds a custom rule/condition on age NUMBER CHECK (age

CHECK
values >= 18)

Sets a default value if no value is status VARCHAR2(10)

DEFAULT
provided DEFAULT 'New'

🎯 Quick Summary with Emojis:

 ❌ NOT NULL – Can’t leave blank
 👯‍♀️UNIQUE – No duplicates
 🔑 PRIMARY KEY – Unique + Not Null (main identifier)
 🔗 FOREIGN KEY – Connects to another table
 🧪 CHECK – Must pass a condition
 🆕 DEFAULT – Auto-filled if no value given

Data types vs Constraints – both are different

Data types and Constraints are totally different things — but they work together when creating a
table.
Let’s make this super simple and crystal clear for you 👇

🎯 Difference Between: Data Types vs Constraints

Feature Data Types ⚙️ Constraints 🔒

Meaning Tells what kind of data the column stores Rules applied to control the data

Examples VARCHAR2, NUMBER, DATE, CHAR NOT NULL, PRIMARY KEY, UNIQUE

Purpose To define the type & size of data To enforce validity & rules on data

Usage Used for storage Used for safety/accuracy

Syntax name VARCHAR2(50) email VARCHAR2(100) UNIQUE NOT NULL

🧠 Think of it like:

 🔣 Data Type = "What kind of info?"

o Is it text, number, date, etc.?

o Example: age NUMBER

 🧱 Constraint = "What rules apply?"

o Can it be blank? Must it be unique?

o Example: age NUMBER CHECK (age >= 18)

Constraint Type:

1. Yesss let's gooo! 🚀

Starting with the NOT NULL constraint — it's the simplest, but also one of the most important in
real-world database design! 💡

1] What is NOT NULL?

It is a constraint that means:

❗ “This column cannot be left empty.”

If you try to insert a row without giving a value to that column → ❌ Oracle will throw an error.

🔧 Syntax:

column_name datatype NOT NULL

📘 Simple Table Example:

CREATE TABLE Users (

user_id NUMBER PRIMARY KEY,

name VARCHAR2(50) NOT NULL,

email VARCHAR2(100) NOT NULL

);

Here:

 You must give a name and email when inserting data.

 If you don’t → you’ll get an error.

🚫 Example That Will FAIL:

INSERT INTO Users (user_id, name)

VALUES (1, NULL);

💥 This will throw:

ORA-01400: cannot insert NULL into ("USERS"."NAME")

✅ Correct Example:

INSERT INTO Users (user_id, name, email)

VALUES (1, 'Rahul', 'rahul@example.com');

🎯 Real-Life Analogy (Instagram style):

 Imagine signing up for Instagram 📱

 Can you leave the username or email blank? ❌ No!

 They are NOT NULL fields in the Users table.

🧠 Summary:

Constraint Meaning Example

NOT NULL Value must be given name VARCHAR2(50) NOT NULL

--Creating table
CREATE TABLE SECTIONA(

ROLLNUM INT,

FIRSTN CHAR (10), -- use char when you want fixed length

LASTN VARCHAR (255), -- will store upto 255 character

ADDRESS VARCHAR(255), -- 255 is standard in SQL

EXP INT

);

SELECT * FROM SECTIONA; -- * 'STAR' means fetch all Row and Coloumn in particular Table

CREATE TABLE X101

DOB DATE -- DATE is data type for :

);

SELECT * FROM X101;

INSERT INTO X101

VALUES('27-06-2024'); -- dd-mm-yyyy , format should be same

SELECT * FROM X101;

-- WILL NOT ACCEPT WRONG FORMAT LIKE:

INSERT INTO X101

VALUES(2022-31-06);

---------------------------------------

--Timestamp
CREATE TABLE X102(

Class_Time TIMESTAMP --format is: 'HH:MM:SS'

);

SELECT * FROM X102;

INSERT INTO X102

VALUES ('10:5:55');

SELECT * FROM X102;

----------------------

-- INT data type

Create Table X103(

ID int

);

Insert into X103

Values(5);

select * from X103;

----------------------------

--decimal data type

Create table X104(

Bill decimal (3,2) -- here 3 is: No Total digit, 2 is:after decimal need 2 digit

);
select * from X104;

Insert into X104

Values(1.23);

select * from X104;

---------------------------------

-- char data type

Create table X105

fName char (20), -- here define any desire lenght

cityName varchar (255), --Standard form

mNo int -- here by default upto 38 numbers will store

);

select * from X105;

Insert into X105

Values('Ganesh','Pune',4514256321);

select * from X105;

--------------------------------

-- varchar data type

Create table X106(

FN char (20),
 LN varchar(255),

MNo int

);

select * from X106;

Insert into X106

Values('Shri','Patil',9852136478);

select * from X106;

-------------------------------------------

create table Users(

id int Not Null,

FN varchar(255),

LN varchar(255),

Exp int

);

select * from USERS;

Insert into USERS

Values(1,'Soham','Tupe', 7454574574);

Insert into USERS

Values(2, 'Hrushi','Kadu', 8585645625);

select * from USERS;

Exactly 6 main types of constraints in SQL (including Oracle)

Here are exactly 6 main types of constraints in SQL (including Oracle):

In SQL, constraints are rules applied on columns to enforce data integrity and business rules. Here
are the 6 main types of constraints, commonly used across all SQL-based systems including Oracle:

1. NOT NULL

Ensures that a column must always have a value – it cannot be left empty.
📌 Example use: To prevent inserting incomplete or missing data in essential fields like email or
username.

2. UNIQUE

Ensures that all values in a column (or combination of columns) are distinct.
📌 Used when duplicates aren’t allowed but NULLs are (only one NULL allowed in Oracle).

3. PRIMARY KEY

A combination of NOT NULL + UNIQUE – used to uniquely identify each record in the table.
📌 Each table can have only one Primary Key, and it cannot contain NULLs.

4. FOREIGN KEY

Enforces referential integrity by linking a column to the Primary Key of another table.
📌 It ensures that the value in one table must already exist in the related table.

5. CHECK

Validates the data against a specific condition or rule (like range, format, or logic).
📌 Example: CHECK (salary > 0) prevents negative salary entries.

6. DEFAULT

Automatically assigns a default value to a column when no value is provided during INSERT.
📌 Reduces chances of NULLs and ensures consistent initial values.

🎯 Bonus Tip for Interviews:

“Think of constraints as quality gates in a table – each one has a role to protect data consistency,
accuracy, and relationships.”
Examples for each of the 6 SQL constraints.

This is super handy for interviews and quick revision. ⚡

✅ 1. NOT NULL

Table:

CREATE TABLE Users (

user_id INT NOT NULL,

username VARCHAR(50) NOT NULL

);

 ✅ Valid:

INSERT INTO Users (user_id, username) VALUES (1, 'John');

 ❌ Invalid:

INSERT INTO Users (user_id, username) VALUES (2, NULL); -- username can't be NULL/field is
mandatory

✅ 2. UNIQUE

Table:

CREATE TABLE Users (

email VARCHAR(100) UNIQUE

);

 ✅ Valid:

INSERT INTO Users (email) VALUES ('user@example.com');

 ❌ Invalid:

INSERT INTO Users (email) VALUES ('user@example.com'); -- duplicate email not allowed

✅ 3. PRIMARY KEY

Table:

CREATE TABLE Users (

user_id INT PRIMARY KEY

);

 ✅ Valid:
INSERT INTO Users (user_id) VALUES (101);

 ❌ Invalid:

INSERT INTO Users (user_id) VALUES (101); -- duplicate primary key

 ❌ Invalid:

INSERT INTO Users (user_id) VALUES (NULL); -- primary key can't be NULL

✅ 4. FOREIGN KEY

Referenced Table:

CREATE TABLE Departments (

dept_id INT PRIMARY KEY

);

Child Table:

CREATE TABLE Employees (

emp_id INT PRIMARY KEY,

dept_id INT,

FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)

);

 ✅ Valid:

INSERT INTO Departments VALUES (10);

INSERT INTO Employees (emp_id, dept_id) VALUES (1, 10);

 ❌ Invalid:

INSERT INTO Employees (emp_id, dept_id) VALUES (2, 99); -- dept_id 99 doesn't exist

✅ 5. CHECK

Table:

CREATE TABLE Employees (

emp_id INT PRIMARY KEY,

salary DECIMAL(10,2) CHECK (salary > 0)

);

 ✅ Valid:

INSERT INTO Employees VALUES (1, 50000);

  ❌ Invalid:

INSERT INTO Employees VALUES (2, -1000); -- salary must be > 0

✅ 6. DEFAULT

Table:

CREATE TABLE Users (

user_id INT PRIMARY KEY,

country VARCHAR(50) DEFAULT 'India'

);

 ✅ Valid:

INSERT INTO Users (user_id) VALUES (1);

-- country auto-filled as 'India'

 ✅ Also valid:

INSERT INTO Users (user_id, country) VALUES (2, 'USA');

✅ Your Matching Pairs — Polished for Interviews

1. NOT NULL – Field is mandatory

👉 Example: email field while signing up — can’t be empty.

2. UNIQUE – Field must have a unique value (no duplicates)

👉 Example: userID or username — must be different for each user.

3. CHECK – Field must satisfy a given condition

👉 Example: age > 18 — used in apps with minimum age criteria.

4. DEFAULT – Field takes default value if user doesn't give one

👉 Example: country defaults to 'India', but if user types 'USA', that will be accepted.

5. PRIMARY KEY – Unique + Not Null (must identify a row)

👉 Example: email in Instagram registration — can’t be empty and must be unique.

6. FOREIGN KEY – Links child table to parent table (referential integrity)

👉 Example:

o User registers with email → stored in Register table

o Later logs in using email → checked from Login table

✅ The email in Login must exist in Register → that’s FK at work.
Let me know if you want this in a table format or printable PDF style for your notes! 📒✨

🔑 Primary Key vs Unique Key – Key Differences

Feature PRIMARY KEY UNIQUE

Uniquely identifies each row in a

Purpose Ensures all values are unique (except NULLs)
table

✅ Allowed (but only one NULL per column in

NULLs allowed? ❌ NOT allowed
Oracle)

Number per
Only one PRIMARY KEY per table Can have multiple UNIQUE constraints
table

Default Automatically implies NOT NULL + Only implies UNIQUE, NOT NULL must be
behavior UNIQUE added separately

Index created? Yes, a clustered or unique index Yes, a unique index

Here's a simple, confident, and impressive explanation of the difference between Primary Key vs
Foreign Key — perfect for interviews:

🔑 Primary Key vs Foreign Key (Tabular + Unique Explanation)

Feature Primary Key Foreign Key

Definition Uniquely identifies each record in a table Creates a relationship between two tables

Uniqueness Must be unique for every row Can have duplicate values

NULL allowed? ❌ Cannot be NULL ✅ Can be NULL

Table Location Defined within the same table Refers to the Primary Key of another table

Purpose Ensures row-level uniqueness Ensures relational integrity between tables

🎯 Confident Explanation (Interview Style):

A Primary Key is like a person’s Aadhar number – it's unique, compulsory, and used to identify them
specifically in a crowd.
A Foreign Key is more like a reference to that Aadhar number in another system – it connects two
databases (tables), creating a relationship while allowing repetition and even blanks.

Great set of questions! These are super important for both SQL basics and interviews. Let’s break
them down in a simple, clean, and confident way 👇
✅ 1. What are Constraints?

Constraints are rules applied to columns in a table to control the data stored in them.
They ensure data accuracy, validity, and integrity in a database.

🧠 Think of them as filters or restrictions that the database will not allow you to break.

✅ 2. What are different types of Constraints?

Here are the most common SQL constraints:

Constraint Purpose

NOT NULL Ensures a column cannot have NULL (empty) values

UNIQUE Ensures all values in a column are different

PRIMARY KEY Uniquely identifies each record (also NOT NULL + UNIQUE)

FOREIGN KEY Creates a relationship between two tables

CHECK Ensures values satisfy a specific condition

DEFAULT Sets a default value for a column if no value is provided

✅ 3. Explanation of each Constraint (Simple and Clear):

Constraint Description Example

NOT NULL Column must always have a value Name VARCHAR(50) NOT NULL

UNIQUE All values in column must be different Email VARCHAR(100) UNIQUE

PRIMARY Used to identify each row uniquely, must

StudentID INT PRIMARY KEY
KEY be NOT NULL + UNIQUE

FOREIGN Links two tables; refers to Primary Key in StudentID INT FOREIGN KEY REFERENCES
KEY another table Students(StudentID)

CHECK Adds a rule to validate data Age INT CHECK (Age >= 18)

DEFAULT Sets a default value if none is provided Status VARCHAR(10) DEFAULT 'Active'

✅ 4. What is the difference between UNIQUE & PRIMARY KEY?

Feature UNIQUE PRIMARY KEY

Uniqueness ✅ Yes ✅ Yes

NULL Allowed? ✅ Yes (only 1 NULL) ❌ No NULL allowed

Count per table 🔄 Can be multiple ⛔ Only one per table

Feature UNIQUE PRIMARY KEY

Purpose Prevents duplicates Identifies records uniquely

✅ 5. What is the difference between PRIMARY KEY & FOREIGN KEY?

Feature PRIMARY KEY FOREIGN KEY

Table In main table In related table

Uniqueness ✅ Unique and NOT NULL ❌ Can be duplicate & NULL

Use Uniquely identifies rows Refers to primary key in another table

Relationship? ❌ No ✅ Yes (creates relationship)

✅ 6. What is the difference between UNIQUE, PRIMARY KEY & FOREIGN KEY?

Feature UNIQUE PRIMARY KEY FOREIGN KEY

Uniqueness ✅ Yes ✅ Yes ❌ No (can repeat)

NULL Allowed? ✅ Yes (1 NULL allowed) ❌ No ✅ Yes

Count/Table 🔄 Many allowed ⛔ Only one 🔄 Many allowed

Purpose Avoid duplicates Uniquely identify records Link tables together

Relationship? ❌ No ❌ No ✅ Yes

1] DDL commands: Data definition Language

DDL Commands (Data Definition Language) in detailed format covering:

What, Syntax, Example, Output/Behavior — clear and practical for both

understanding and interview prep 👇

✅ 1] DDL Commands: Data Definition Language

📘 What is DDL?
DDL (Data Definition Language) is a part of SQL used to create and modify
the structure of database objects such as:
 Tables
  Schemas
 Views
 Indexes
🧠 Key Feature:
DDL commands are auto-committed – meaning, changes are saved
immediately and permanently, and you CANNOT ROLLBACK them.

📌 List of DDL Commands:

Comman
Use Case
d

CREATE Create a new table or object

ALTER Modify existing structure

DROP Delete the object entirely

TRUNCAT Delete all data, keep the

E structure

RENAME Rename an object

✅ DDL Commands in Detail:

🔹 1. CREATE TABLE
📘 What:
Used to create a new table.
🛠 Syntax:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
...
);
✅ Example:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Age INT CHECK (Age >= 18)
);
🖥 Output:
Table created.
✅ Table structure is created and saved permanently.

🔹 2. ALTER TABLE
📘 What:
Used to modify a table — like adding/removing columns or changing data types.

✏️a) Add Column

🛠 Syntax:
ALTER TABLE table_name
ADD column_name datatype;
✅ Example:
ALTER TABLE Students
ADD Email VARCHAR(100);
🖥 Output:
Table altered.

✏️b) Drop Column

🛠 Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;
✅ Example:
ALTER TABLE Students
DROP COLUMN Email;
🖥 Output:
Table altered.

✏️c) Modify Column (datatype)

🛠 Syntax:
ALTER TABLE table_name
MODIFY column_name new_datatype;
✅ Example:
ALTER TABLE Students
MODIFY Age VARCHAR(10);
🖥 Output:
Table altered.

🔹 3. DROP TABLE
📘 What:
Used to delete the table completely (structure + data).
🛠 Syntax:
DROP TABLE table_name;
✅ Example:
DROP TABLE Students;
🖥 Output:
Table dropped.
❗ No recovery! Data and structure both gone.

🔹 4. TRUNCATE TABLE
📘 What:
Used to delete all data from the table, but keep the structure.
🛠 Syntax:
TRUNCATE TABLE table_name;
✅ Example:
TRUNCATE TABLE Students;
🖥 Output:
Table truncated.
✅ Faster than DELETE and cannot be rolled back.

🔹 5. RENAME TABLE
📘 What:
Used to rename a table.
🛠 Syntax:
RENAME TABLE old_name TO new_name;
✅ Example:
RENAME TABLE Students TO Learners;
🖥 Output:
Table renamed.

🔄 DDL Commands Are Auto-Commit

Comman Rollback Affects Affects

d Possible? Structure Data

CREATE ❌ No ✅ Yes ❌ No

ALTER ❌ No ✅ Yes ❌ No

DROP ❌ No ✅ Yes ✅ Yes

TRUNCAT
❌ No ❌ No ✅ Yes
E

RENAME ❌ No ✅ Yes ❌ No

✅ Summary for Interviews:

 DDL is used to define or change structure, not data directly.
 Auto-committed → You cannot ROLLBACK.
 Use CREATE, ALTER, DROP, TRUNCATE, RENAME.

📘 Data Query Language (DQL)

1️⃣📘 What: Retrieve all data from a table

🛠 Syntax:
SELECT * FROM EMPLOYEESUSA;
✅ Example:
Fetches all rows and columns from EMPLOYEESUSA.
🖥 Output:
Displays complete data from the table.

2️⃣📘 What: Retrieve specific columns from a table

🛠 Syntax:
SELECT Emp_ID, Emp_Name, DEPT_ID FROM EMPLOYEESUSA;
✅ Example:
Shows only selected columns.
🖥 Output:
Only employee ID, name, and department ID in the same order.

3️⃣📘 What: Remove duplicate values using DISTINCT

🛠 Syntax:
SELECT DISTINCT JOB_TITLE FROM EMPLOYEESUSA;
✅ Example:
Gets a unique list of job titles.
🖥 Output:
Each job title shown once.

4️⃣📘 What: Use DISTINCT with multiple columns

🛠 Syntax:
SELECT DISTINCT JOB_TITLE, SALARY FROM EMPLOYEESUSA;
✅ Example:
Removes duplicate combinations of JOB_TITLE and SALARY.
🖥 Output:
Unique rows based on both columns together.

5️⃣📘 What: Retrieve top rows using ROWNUM (Oracle)

🛠 Syntax:
SELECT * FROM EMPLOYEESUSA WHERE ROWNUM <= 3;
✅ Example:
Returns the first 3 rows.
🖥 Output:
Top 3 rows from the table (not sorted).

6️⃣📘 What: Get top records from a specific column

🛠 Syntax:
SELECT SALARY FROM EMPLOYEESUSA WHERE ROWNUM <= 3;
✅ Example:
Fetches salary values of top 3 rows.
🖥 Output:
3 salary values.

7️⃣📘 What: Sort data using ORDER BY (Ascending)

🛠 Syntax:
SELECT * FROM EMPLOYEESUSA ORDER BY SALARY;
✅ Example:
Sorts all records by salary in ascending order (default).
🖥 Output:
Full table sorted by salary (low to high).

8️⃣📘 What: Sort data using ORDER BY (Descending)

🛠 Syntax:
SELECT * FROM EMPLOYEESUSA ORDER BY SALARY DESC;
✅ Example:
Sorts all records by salary in descending order.
🖥 Output:
Full table sorted by salary (high to low).

9️⃣📘 What: Create a LOCATIONS table

🛠 Syntax:
CREATE TABLE LOCATIONS (
CITY VARCHAR(255),
STATE VARCHAR(255)
);
✅ Example Inserts:
INSERT INTO LOCATIONS VALUES('Pune','MH');
INSERT INTO LOCATIONS VALUES('Mumbai','MH');
INSERT INTO LOCATIONS VALUES('Indore','Mp');
-- etc.
🖥 Output:
City and state data added to the LOCATIONS table.

🔟 📘 What: Use DISTINCT with LOCATIONS

🛠 Unique Cities:
SELECT DISTINCT CITY FROM LOCATIONS;
🛠 Unique City + State Pairs:
SELECT DISTINCT CITY, STATE FROM LOCATIONS;
🖥 Output:
Only distinct rows are shown (either by city or by both city & state).

Absolutely! Here’s a complete and crisp set of notes for SQL DQL (Data
Query Language) — covering every command with:
✅ What it does
✅ Syntax
✅ Example
✅ Expected Output

📘 DQL – Data Query Language

DQL is used to fetch data from the database. It mainly involves the
SELECT statement and its powerful options.

🔹 1. SELECT – Fetch data from one or more tables

✅ What?
Retrieves data from the table.
🛠 Syntax:
SELECT column1, column2 FROM table_name;
💡 Example:
SELECT first_name, salary FROM employees;
📤 Output:
first_na salar
me y

5000
John
0

6000
Alice
0

🔹 **2. SELECT *** – Fetch all columns

✅ What?
Returns all columns from the table.
🛠 Syntax:
SELECT * FROM table_name;
💡 Example:
SELECT * FROM departments;
📤 Output:

dept_i dept_nam
location
d e

New
10 HR
York

Engineeri
20 Mumbai
ng

🔹 3. DISTINCT – Remove duplicate values

✅ What?
Returns only unique values from a column.
🛠 Syntax:
SELECT DISTINCT column_name FROM table_name;
💡 Example:
SELECT DISTINCT job_id FROM employees;
📤 Output:

job_id

HR

Manage
job_id

Analyst

🔹 4. WHERE – Filter rows based on a condition

✅ What?
Fetches rows that match a specific condition.
🛠 Syntax:
SELECT column1 FROM table_name WHERE condition;
💡 Example:
SELECT first_name, salary FROM employees WHERE salary > 50000;
📤 Output:

first_na salar
me y

6000
Alice
0

7000
Bob
0

🔹 5. ORDER BY – Sort the result

✅ What?
Sorts the results in ascending (default) or descending order.
🛠 Syntax:
SELECT column1 FROM table_name ORDER BY column1 [ASC|DESC];
💡 Example:
SELECT first_name FROM employees ORDER BY salary DESC;
📤 Output:

first_na
me

Bob

Alice

John
🔹 6. ROWNUM / FETCH FIRST / LIMIT – Limit number of rows
✅ What?
Returns only a specific number of rows.
🛠 Oracle Syntax:
SELECT * FROM employees WHERE ROWNUM <= 3;
🛠 Standard SQL (non-Oracle):
SELECT * FROM employees FETCH FIRST 3 ROWS ONLY;
-- or
SELECT * FROM employees LIMIT 3;
📤 Output:
Returns first 3 rows from the result set.

🔹 7. ALIAS – Rename column or table temporarily

✅ What?
Assigns a temporary name to columns or tables.
🛠 Syntax:
SELECT column_name AS alias_name FROM table_name;
💡 Example:
SELECT salary AS monthly_income FROM employees;
📤 Output:

monthly_inco
me

50000

60000

🔹 8. IN / BETWEEN / LIKE / IS NULL – Advanced filtering

You’ll often use these with WHERE:
📍IN:
SELECT * FROM employees WHERE job_id IN ('HR', 'Manager');
📍BETWEEN:
SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;
📍LIKE:
SELECT * FROM employees WHERE first_name LIKE 'A%';
📍IS NULL:
SELECT * FROM employees WHERE manager_id IS NULL;

🧠 Pro Interview Line:

“DQL focuses on reading data from the database using SELECT. It
becomes powerful when combined with filtering, sorting, and limiting
clauses like WHERE, ORDER BY, and ROWNUM.”

Would you like this as a printable PDF or chart for your revision board?
Or want to practice with mock queries based on a table?