RDBMS and DBMS Concepts

● DBMS (Database Management System): Software that provides an interface for

users to interact with databases, allowing for data creation, retrieval, update, and
management. It organizes data and provides mechanisms for ensuring its
consistency and integrity.
● RDBMS (Relational Database Management System): A type of DBMS that uses a
relational model, where data is stored in tables (relations). Each table consists of
rows and columns, and relationships can be established between tables using foreign
keys. RDBMSs support SQL for querying and maintaining the database.

Data Normalization

● Normalization: A process used to organize a database into tables and columns such
that redundancy is minimized and data integrity is maximized.
○ 1NF (First Normal Form): Ensures that the table has a primary key and
that each column contains atomic (indivisible) values.
○ 2NF (Second Normal Form): Achieved when the table is in 1NF and all
non-key attributes are fully functionally dependent on the primary key.
○ 3NF (Third Normal Form): Achieved when the table is in 2NF, and all
attributes are dependent only on the primary key (no transitive dependency).
○ BCNF (Boyce-Codd Normal Form): A stronger version of 3NF where every
determinant is a candidate key.

Constraints

● Primary Key: A unique identifier for each record in a table. It ensures that no two
rows have the same primary key value.
● Foreign Key: A field in one table that uniquely identifies a row in another table. It
establishes a relationship between the two tables and ensures referential integrity.
● Unique Constraint: Ensures that all values in a column are different.
● Not Null Constraint: Prevents null (empty) values from being entered into a column,
ensuring that each row must contain a value.
● Check Constraint: Ensures that all values in a column satisfy a specific condition
(e.g., age > 18).
● Default Constraint: Assigns a default value to a column if no value is specified
during the insertion of a record.

Syntax

● SQL syntax refers to the set of rules for writing queries and commands in SQL. It
dictates how queries should be structured, including the correct order of keywords
and clauses.

Data Types

● Data types specify the kind of data that can be stored in a column. Common data
types include:
○ INTEGER: Whole numbers.
○ FLOAT/DOUBLE: Floating-point numbers (numbers with decimals).
○ VARCHAR(size): Variable-length string.
○ CHAR(size): Fixed-length string.
○ DATE: Date values (e.g., 'YYYY-MM-DD').
○ BOOLEAN: True/False values.

Operators

● Operators are used to perform operations on data:

○ Arithmetic Operators: Used for mathematical calculations. Examples: +, -, *,
/
○ Comparison Operators: Used to compare values. Examples: =, !=, >, <, >=, <=
○ Logical Operators: Used to combine multiple conditions. Examples: AND, OR,
NOT

Expressions

● Expressions are combinations of values, variables, operators, and functions that

SQL evaluates to produce a result. Examples include 5 + 10, salary * 0.1, and age >
18.

Commands
● SELECT: Used to retrieve data from one or more tables. Example: SELECT * FROM
employees;
● CREATE: Used to create new databases, tables, or other database objects.
Example: CREATE TABLE employees (id INT, name VARCHAR(100));
● DROP: Used to delete databases, tables, or other objects. Example: DROP TABLE
employees;
● DELETE: Used to remove records from a table. Example: DELETE FROM employees
WHERE id = 1;
● INSERT: Used to add new records to a table. Example: INSERT INTO employees
(id, name) VALUES (1, 'John Doe');
● WHERE: Filters records in a SELECT, UPDATE, or DELETE statement based on
specified conditions. Example: SELECT * FROM employees WHERE age > 30;
● AND/OR: Logical operators used with WHERE to combine multiple conditions.
Example: SELECT * FROM employees WHERE age > 30 AND department = 'Sales';
● UPDATE: Modifies existing data in a table. Example: UPDATE employees SET salary
= salary * 1.1 WHERE department = 'Sales';
● LIKE: Used in a WHERE clause to search for a specified pattern in a column.
Example: SELECT * FROM employees WHERE name LIKE 'J%';
● ORDER BY: Sorts the result set by one or more columns. Example: SELECT * FROM
employees ORDER BY salary DESC;
● GROUP BY: Groups rows that have the same values in specified columns and allows
aggregate functions (e.g., COUNT, SUM) to be applied to each group. Example:
SELECT department, COUNT(*) FROM employees GROUP BY department;
● ALTER: Modifies an existing database object, such as adding a column to a table.
Example: ALTER TABLE employees ADD COLUMN birthdate DATE;
● TRUNCATE: Deletes all records from a table without deleting the table itself.
Example: TRUNCATE TABLE employees;

Joins and Unions

● Joins: Combine rows from two or more tables based on a related column between
them.
○ INNER JOIN: Returns only the rows that have matching values in both
tables. Example: SELECT employees.name, departments.name FROM
employees INNER JOIN departments ON employees.department_id =
departments.id;
○ LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and
the matching rows from the right table. If there is no match, NULL values
are returned for columns from the right table. Example: SELECT
employees.name, departments.name FROM employees LEFT JOIN
departments ON employees.department_id = departments.id;
○ RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right
table and the matching rows from the left table. If there is no match, NULL
values are returned for columns from the left table. Example: SELECT
employees.name, departments.name FROM employees RIGHT JOIN
departments ON employees.department_id = departments.id;
○ FULL JOIN (FULL OUTER JOIN): Returns rows when there is a match in
either table. If there is no match, NULL values are returned for the missing
column values. Example: SELECT employees.name, departments.name FROM
employees FULL JOIN departments ON employees.department_id =
departments.id;
● Union: Combines the result sets of two or more SELECT statements into a single
result set, removing duplicates. Example: SELECT name FROM employees UNION
SELECT name FROM customers;
● Union All: Combines the result sets of two or more SELECT statements, including
all duplicates. Example: SELECT name FROM employees UNION ALL SELECT name
FROM customers;

Null Values

● Null: A special marker used in SQL to indicate that a data value does not exist in
the database. Null is different from an empty string or zero and means "unknown"
or "missing value".

Indexes

● Indexes are special lookup tables that the database search engine can use to speed
up data retrieval. Indexes can be created on one or more columns of a table to
improve query performance. However, they can also slow down data insertion,
deletion, and updating because the index must be updated whenever the data is
modified.

Views
● Views: Virtual tables that are the result of a SQL query. A view does not store data
physically; instead, it retrieves data from one or more tables when accessed. Views
can simplify complex queries and provide security by allowing users to access data
without giving them direct access to the base tables.

Having Clause

● Having: Used to filter results produced by GROUP BY based on a condition. It is

similar to WHERE but is used with aggregate functions. Example: SELECT
department, COUNT(*) FROM employees GROUP BY department HAVING
COUNT(*) > 5;

Transactions

● A transaction is a sequence of one or more SQL operations treated as a single unit.

Transactions follow the ACID properties to ensure data integrity:
○ Atomicity: All operations within the transaction are completed; if not, the
transaction is aborted.
○ Consistency: The database must be in a valid state before and after the
transaction.
○ Isolation: Transactions are isolated from each other. Intermediate results
of a transaction are invisible to other transactions.
○ Durability: Once a transaction is committed, its changes are permanent, even
in the case of a system failure.

Wildcard Operators

● Wildcards are used with the LIKE operator to search for patterns within string
data.
○ %: Represents zero or more characters. Example: SELECT * FROM
employees WHERE name LIKE 'J%'; (Finds names starting with 'J')
○ _: Represents a single character. Example: SELECT * FROM employees
WHERE name LIKE '_a%'; (Finds names with 'a' as the second character)

Sub-Queries

● A sub-query, or nested query, is a query within another query. Sub-queries can be

used in various places, such as in SELECT, FROM, WHERE, and HAVING clauses