Chapter 1: Introduction to Databases & SQL Architecture
1.1 What exactly is a Database?
A database is a systematic collection of data. It supports electronic storage and manipulation of
data. Databases make data management easy.
Manual System: Imagine a huge register in a shop where every sale is written by hand. Finding
one specific sale from last year is very hard.
Database System: The same data stored in a computer. You can find any specific detail in
milliseconds using a query.
1.2 Relational Database Management System (RDBMS)
RDBMS is the basis for SQL. Data in RDBMS is stored in database objects called Tables.
Table: A collection of related data entries and it consists of columns and rows.
Field (Column): A column in a table that is designed to maintain specific information about every
record in the table.
Record (Row): A row is an individual entry that exists in a table.
1.3 SQL: The Language of Databases
SQL (Structured Query Language) is used to perform tasks such as updating data on a
database, or retrieving data from a database.
Is it a Case-Sensitive Language? No. SELECT is the same as select. However, it is a best
practice to write keywords in CAPITAL letters for better readability.
1.4 Data Types in SQL (Very Important)
Before creating a table, you must know what kind of data you are storing.
Numeric: INT (Integers), DECIMAL (For salary/price).
Character/String: CHAR (Fixed length), VARCHAR (Variable length - Best for names/emails).
Date/Time: DATE (YYYY-MM-DD), TIMESTAMP.
1.5 The 5 Pillars of SQL Commands (Detailed)
A. DDL (Data Definition Language)
These commands change the structure of the database (adding columns, deleting tables).
CREATE: To build a new table.
,ALTER: To change the structure (Add/Modify/Drop columns).
DROP: To delete the table structure and data permanently.
TRUNCATE: To empty the table (Delete all rows but keep the columns).
B. DML (Data Manipulation Language)
These commands deal with the actual data inside the rows.
INSERT: To add new records.
UPDATE: To modify existing records.
DELETE: To remove specific records based on a condition.
1.6 Practical Example (Your First Code)
If you want to create a table for your LBH Creations customers:
SQL
-- Creating the table structure (DDL)
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100),
City VARCHAR(50),
OrderAmount DECIMAL(10, 2)
);
-- Adding data into the table (DML)
INSERT INTO Customers VALUES (1, 'Nadiya', 'Mumbai', 500.00);
Chapter 1: Interview Quick-Fire Questions
1.Difference between DBMS and RDBMS? (Hint: RDBMS uses Tables and Keys).
2.What is a Primary Key? (Hint: A unique ID for every row).
3.Difference between DELETE and TRUNCATE? (Hint: Delete is DML, Truncate is DDL).
Chapter 2: Data Retrieval & Filtering (The DQL Power)
In this chapter, we focus on the SELECT statement. As a Software Engineer at a company like
Amazon or NTT Data, you will spend 80% of your time writing SELECT queries to pull
meaningful data from millions of records.
2.1 The SELECT Statement
, The SELECT statement is used to fetch data from a database. The data returned is stored in a
result table, called the result-set.
Syntax to select ALL columns: SELECT * FROM TableName;
(The asterisk * is a wildcard that tells SQL to give you every single column in that table).
Syntax to select SPECIFIC columns: SELECT Column1, Column2 FROM TableName;
(Always select only the columns you need to improve performance).
2.2 Filtering Data with the WHERE Clause
The WHERE clause is used to filter records. It is used to extract only those records that fulfill a
specified condition.
Example: At LBH Creations, if you want to see only those customers who ordered more than
₹500:
SELECT * FROM Customers WHERE OrderAmount > 500;
Operator Description Example
= Equal to WHERE City = 'Mumbai’
> Greater than WHERE Age > 18
< Less than WHERE Salary < 30000
>= Greater than or equal WHERE Marks >= 90
<= Less than or equal WHERE Quantity <= 5
!= Or <> Not equal WHERE Status !=
'Cancelled’
2.3 Logical Operators (AND, OR, NOT)
When you have more than one condition to check, you use Logical Operators.
AND Operator: Displays a record if all the conditions separated by AND are TRUE.
SELECT * FROM Students WHERE Course = 'CSE' AND Age > 20;
OR Operator: Displays a record if any of the conditions separated by OR is TRUE.
SELECT * FROM Customers WHERE City = 'Hyderabad' OR City = 'Bangalore';
NOT Operator: Displays a record if the condition(s) is NOT TRUE.
1.1 What exactly is a Database?
A database is a systematic collection of data. It supports electronic storage and manipulation of
data. Databases make data management easy.
Manual System: Imagine a huge register in a shop where every sale is written by hand. Finding
one specific sale from last year is very hard.
Database System: The same data stored in a computer. You can find any specific detail in
milliseconds using a query.
1.2 Relational Database Management System (RDBMS)
RDBMS is the basis for SQL. Data in RDBMS is stored in database objects called Tables.
Table: A collection of related data entries and it consists of columns and rows.
Field (Column): A column in a table that is designed to maintain specific information about every
record in the table.
Record (Row): A row is an individual entry that exists in a table.
1.3 SQL: The Language of Databases
SQL (Structured Query Language) is used to perform tasks such as updating data on a
database, or retrieving data from a database.
Is it a Case-Sensitive Language? No. SELECT is the same as select. However, it is a best
practice to write keywords in CAPITAL letters for better readability.
1.4 Data Types in SQL (Very Important)
Before creating a table, you must know what kind of data you are storing.
Numeric: INT (Integers), DECIMAL (For salary/price).
Character/String: CHAR (Fixed length), VARCHAR (Variable length - Best for names/emails).
Date/Time: DATE (YYYY-MM-DD), TIMESTAMP.
1.5 The 5 Pillars of SQL Commands (Detailed)
A. DDL (Data Definition Language)
These commands change the structure of the database (adding columns, deleting tables).
CREATE: To build a new table.
,ALTER: To change the structure (Add/Modify/Drop columns).
DROP: To delete the table structure and data permanently.
TRUNCATE: To empty the table (Delete all rows but keep the columns).
B. DML (Data Manipulation Language)
These commands deal with the actual data inside the rows.
INSERT: To add new records.
UPDATE: To modify existing records.
DELETE: To remove specific records based on a condition.
1.6 Practical Example (Your First Code)
If you want to create a table for your LBH Creations customers:
SQL
-- Creating the table structure (DDL)
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100),
City VARCHAR(50),
OrderAmount DECIMAL(10, 2)
);
-- Adding data into the table (DML)
INSERT INTO Customers VALUES (1, 'Nadiya', 'Mumbai', 500.00);
Chapter 1: Interview Quick-Fire Questions
1.Difference between DBMS and RDBMS? (Hint: RDBMS uses Tables and Keys).
2.What is a Primary Key? (Hint: A unique ID for every row).
3.Difference between DELETE and TRUNCATE? (Hint: Delete is DML, Truncate is DDL).
Chapter 2: Data Retrieval & Filtering (The DQL Power)
In this chapter, we focus on the SELECT statement. As a Software Engineer at a company like
Amazon or NTT Data, you will spend 80% of your time writing SELECT queries to pull
meaningful data from millions of records.
2.1 The SELECT Statement
, The SELECT statement is used to fetch data from a database. The data returned is stored in a
result table, called the result-set.
Syntax to select ALL columns: SELECT * FROM TableName;
(The asterisk * is a wildcard that tells SQL to give you every single column in that table).
Syntax to select SPECIFIC columns: SELECT Column1, Column2 FROM TableName;
(Always select only the columns you need to improve performance).
2.2 Filtering Data with the WHERE Clause
The WHERE clause is used to filter records. It is used to extract only those records that fulfill a
specified condition.
Example: At LBH Creations, if you want to see only those customers who ordered more than
₹500:
SELECT * FROM Customers WHERE OrderAmount > 500;
Operator Description Example
= Equal to WHERE City = 'Mumbai’
> Greater than WHERE Age > 18
< Less than WHERE Salary < 30000
>= Greater than or equal WHERE Marks >= 90
<= Less than or equal WHERE Quantity <= 5
!= Or <> Not equal WHERE Status !=
'Cancelled’
2.3 Logical Operators (AND, OR, NOT)
When you have more than one condition to check, you use Logical Operators.
AND Operator: Displays a record if all the conditions separated by AND are TRUE.
SELECT * FROM Students WHERE Course = 'CSE' AND Age > 20;
OR Operator: Displays a record if any of the conditions separated by OR is TRUE.
SELECT * FROM Customers WHERE City = 'Hyderabad' OR City = 'Bangalore';
NOT Operator: Displays a record if the condition(s) is NOT TRUE.
