SQL for Beginners - Database Concepts (Database, DBMS, Data Models, RDBMS and
SQL)
In-order to understand SQL, we have to first understand different Database Concepts like:
Database
DBMS
Data Models
RDBMS
SQL etc.
Database Concepts:
o Databases
The place we can store the related data and later retrieve the data is known as
Database
Storing the related Data like Employee ID, Employee Name, Employee Salary,
Department etc. can be stored
Retrieving the data based on some conditions
o Database Management Systems
Shortly called as DBMS
Software that stores data in databases in an organized way to make it easier to create,
retrieve, update etc.
Examples: DBase, FoxPro, MySQL, Oracle, MongoDB, MariaDB, SQLite, Cassandra
and many more
o Data Models
Defines how related data is connected to each other and stored inside the database.
Types of Data Models:
Hierarchical Model
Network Model
Entity-Relationship Model
Relational Model
Data is stored in the form of tables
Tables organize the data in the form of rows and columns
It is a popular and widely used by most of the DBMS Software
o RDBMS
DBMS using Relational Data Models are known as RDBMS
Examples for RDBMS Software:
Oracle
MS-SQL Server
DB2
MySQL
MS-ACCESS
etc.
o SQL
Query language for Relational Databases
Stands for Structured Query Language
The following things can be performed on Database using SQL:
Inserting Data
Retrieving Data
Updating Data
Deleting Data
And many more
DOWNLOAD SSMS
VIDEO
[Link]
SSMS LINK
[Link]
STEP 1: CLICK ON ABOVE LINK AND DOWNLOAD DEVELOPER VERSION
STEP 2: CLICK ON EXECUTABLE
STEP 3: CHOOSE THE BASIC OPTION
STEP 4: CLICK ON ACCEPT
STEP 5: CLICK ON INSTALL
STEP 6: NOW CLICK ON INSTALL SSMS
STEP 7: CHOOSE BELOW OPTION
DOWNLOADS ANOTHER EXECUTABLE
STEP 8: CLICK ON THE EXECUTABLE ONCE IT IS DOWNLOADED
CLICK ON INSTALL
INSTALLS SUCCESSFULLY MESSAGE
IN THE START OPTION
SEARCH FOR SQL SERVER MANAGEMENT STUDIO
CONSTRAINTS IN SQL
Constraints in SQL are rules applied to database tables to enforce data integrity, accuracy, and
reliability. Constraints define the conditions that must be met by data in the database to maintain
consistency.
In SQL, data types define the type of data that a column in a table can hold. Here’s a
categorized list of common SQL data types:
1. Numeric Data Types
Used to store numerical values.
INT (or INTEGER): Stores whole numbers (e.g., 1, -1).
BIGINT: Stores large integers.
SMALLINT: Stores smaller range of integers.
TINYINT: Stores very small integers (e.g., 0 to 255).
DECIMAL(p, s) or NUMERIC(p, s): Stores fixed-point numbers (e.g., 123.45,
where p is precision and s is scale).
FLOAT: Stores approximate values for floating-point numbers.
2. Character/String Data Types
Used to store text or character data.
CHAR(n): Fixed-length string of size n.
VARCHAR(n): Variable-length string with a maximum size of n.
3. Date and Time Data Types
Used to store date and time values.
DATE: Stores only the date (YYYY-MM-DD).
TIME: Stores only the time (HH:MM:SS).
DATETIME: Stores date and time (YYYY-MM-DD HH:MM:SS).
TIMESTAMP: Similar to DATETIME but often used for tracking changes (with
timezone in some systems).
4. Binary Data Types
Used to store binary data (like images, files).
BINARY(n): Fixed-length binary data.
VARBINARY(n): Variable-length binary data.
5. Boolean Data Types
Used to store true/false values.
BOOLEAN: Stores TRUE or FALSE (or 1/0 in some systems).
Examples of Usage:
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(50),
HireDate DATE,
Salary DECIMAL(10, 2),
IsActive BOOLEAN
);
DDL commands
DDL (Data Definition Language) commands are used in SQL to define and manage the
structure of database objects like tables, indexes, and schemas. These commands do not
manipulate the data but focus on the schema. Below are the main DDL commands:
1. CREATE
Used to create a new database object such as a table, view, index, or database.
Example: Create a Table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
HireDate DATE,
Salary DECIMAL(10, 2)
);
Example: Create a Database
CREATE DATABASE CompanyDB;
2. ALTER
Used to modify an existing database object (e.g., add, modify, or drop columns in a table).
Example: Add a Column
ALTER TABLE Employees
ADD Email VARCHAR(100);
Example: Modify a Column
ALTER TABLE Employees
MODIFY COLUMN Salary DECIMAL(12, 2);
Example: Drop a Column
ALTER TABLE Employees
DROP COLUMN Email;
3. DROP
Used to delete a database object permanently (e.g., a table or database).
Example: Drop a Table
DROP TABLE Employees;
Example: Drop a Database
DROP DATABASE CompanyDB;
4. TRUNCATE
Used to remove all rows from a table without logging individual row deletions. The table
structure remains intact.
Example: Truncate a Table
TRUNCATE TABLE Employees;
5. RENAME (Supported in some databases)
Used to rename a database object.
Example: Rename a Table
RENAME TABLE Employees TO Staff;
Key Differences:
DROP: Deletes the object and its data permanently.
TRUNCATE: Deletes all data but keeps the table structure.
ALTER: Changes the structure or properties of an object.
Constraints in SQL are rules applied to database tables to enforce data integrity, accuracy,
and reliability. Constraints define the conditions that must be met by data in the database to
maintain consistency.
Definition of Constraints:
Constraints are used to limit the type of data that can be inserted into a table. They ensure the
correctness of data and enforce business rules in the database.
Types of Constraints:
1. NOT NULL Constraint
Ensures that a column cannot have a NULL value.
Example:
CREATE TABLE Employees (
EmployeeID INT NOT NULL,
Name VARCHAR(50) NOT NULL
);
2. UNIQUE Constraint
Ensures all values in a column or group of columns are unique (no duplicates).
Example:
CREATE TABLE Employees (
EmployeeID INT UNIQUE,
Email VARCHAR(100) UNIQUE
);
3. PRIMARY KEY Constraint
Combines NOT NULL and UNIQUE. It uniquely identifies each row in a table. A
table can have only one primary key.
Example:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50)
);
4. FOREIGN KEY Constraint
Ensures referential integrity by linking a column in one table to the PRIMARY KEY
in another table.
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
EmployeeID INT,
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);
5. DEFAULT Constraint
Provides a default value for a column when no value is specified during insertion.
Example:
CREATE TABLE Employees (
EmployeeID INT,
HireDate DATE DEFAULT GETDATE()
);
DATA TYPES IN SQL
Category Data Type Description
Numeric INT Stores whole numbers.
Numeric BIGINT Stores large integers.
Numeric SMALLINT Stores smaller range of integers.
Numeric TINYINT Stores very small integers (e.g., 0 to 255).
Numeric DECIMAL(p, s) Stores fixed-point numbers with precision and scale.
Numeric FLOAT Stores approximate values for floating-point numbers.
Character/
String CHAR(n) Fixed-length string of size n.
Character/
String VARCHAR(n) Variable-length string with a maximum size of n.
Date and Time DATE Stores only the date (YYYY-MM-DD).
Date and Time TIME Stores only the time (HH:MM:SS).
Date and Time DATETIME Stores date and time (YYYY-MM-DD HH:MM:SS).
Similar to DATETIME but often used for tracking changes (with timezone in som
Date and Time TIMESTAMP systems).
Binary BINARY(n) Fixed-length binary data.
Boolean BOOLEAN Stores TRUE or FALSE values.