Database: An organized collection of interrelated data.
DBMS: Software used to create, manage, and retrieve data from databases
(e.g., MS Access).
RDBMS: A database system that stores data in the form of related tables
consisting of rows and columns.
Relation: A logical table structure within the database.
Tuple: A single horizontal row in a relation representing a single data record.
Attribute: A vertical column in a relation representing a specific property or
field.
Domain: The set of pool/permissible values allowed for a specific attribute.
Degree: The total number of columns (attributes) in a table.
Cardinality: The total number of rows (tuples) in a table.
Keys (candidate key, primary key, alternate key, foreign key)
Keys in Relational Databases
Keys are fundamental to relational databases as they uniquely identify rows within a
table and establish relationships between tables. Here’s an overview of different
types of keys:
1. Candidate Key
Definition: A candidate key is an attribute or a set of attributes that can uniquely
identify each tuple (row) in a table. A table can have multiple candidate keys, but each
one is a potential primary key.
Characteristics:
Must be unique for each row.
Cannot contain null values.
Example: In a Student table, StudentID and Email might both be candidate keys if
each is unique for every student.
2. Primary Key
Definition: The primary key is a specific candidate key chosen to uniquely identify
each tuple in a table. It ensures that each row is unique and not null.
Characteristics:
Uniquely identifies each record in the table.
Cannot have null values.
Each table can have only one primary key.
Example: In the Students table, StudentID might be selected as the primary key.
3. Alternate Key
Definition: An alternate key is any candidate key that is not chosen as the primary
key. It is still a unique identifier for tuples but is not used as the main key for the
table.
Characteristics:
Each alternate key could potentially serve as a primary key.
Used for ensuring uniqueness in case the primary key is not suitable.
Example: If StudentID is chosen as the primary key, then Email (if unique) becomes an
alternate key.
4. Foreign Key
Definition: A foreign key is an attribute or a set of attributes in one table that refers
to the primary key of another table. It establishes and enforces a link between the
data in the two tables.
Characteristics:
Ensures referential integrity by enforcing that the value in the foreign key
column matches a value in the primary key column of the related table.
Can contain duplicate values and nulls if allowed.
Example: In an Enrollments table, StudentID might be a foreign key that refers to the
StudentID primary key in the Students table.
Fundamental MySQL Data Types
When creating a table, you must assign a explicit data type to every column to specify what type of
information it can store:
Data Type Description Usage Example
INT Stores whole numbers without decimals. RollNo INT
DECIMAL(p,s) / Stores exact fractional or decimal values (p = total digits, s = Price
FLOAT decimal scale). DECIMAL(6,2)
Fixed-length text string up to size n. Right-pads unused
CHAR(n) Gender CHAR(1)
spaces.
Variable-length text string up to size n. Stores only actual Name
VARCHAR(n)
characters without padding. VARCHAR(30)
DATE Stores calendar dates in a standard YYYY-MM-DD format. DOB DATE
Classification of SQL Commands
SQL commands are broadly categorized into sub-languages based on their functional
behavior:
1. Data Definition Language (DDL)
Data Definition Language (DDL) is a subset of SQL used for defining and managing
database structures. It includes commands that define, alter, and drop database
objects such as tables, indexes, and schemas.
Common DDL Commands:
CREATE: Used to create new database objects, such as tables, indexes, and views
o Example: CREATE TABLE Students (StudentID INT PRIMARY KEY, Name
VARCHAR(50), DateOfBirth DATE)
ALTER: Used to modify existing database objects, such as adding or deleting columns
in a table.
o Example: ALTER TABLE Students ADD Email VARCHAR(100)
DROP: Used to delete database objects, such as tables or indexes.
o Example: DROP TABLE Students
TRUNCATE: Used to remove all records from a table without deleting the table
structure.
o Example: TRUNCATE TABLE Students;
2. Data Manipulation Language (DML)
Data Manipulation Language (DML) is a subset of SQL used for manipulating and
querying the data stored in the database. It includes commands to insert, update,
delete, and retrieve data.
Common DML Commands:
SELECT: Retrieves data from one or more tables. It can include conditions, sorting,
and grouping
o Example: SELECT Name, DateOfBirth FROM Students WHERE StudentID = 1
INSERT: Adds new records to a table.
o Example: INSERT INTO Students (StudentID, Name, DateOfBirth) VALUES (1,
'Alice Smith', '2005-05-15')
UPDATE: Modifies existing records in a table.
o Example: UPDATE Students SET Email = 'alice@[Link]' WHERE
StudentID = 1
DELETE: Removes records from a table based on specified conditions.
o Example: DELETE FROM Students WHERE StudentID = 1
CREATE DATABASE school; -- Creates a new database context
SHOW DATABASES; -- Lists all existing databases
USE school; -- Activates the database for subsequent operations
DROP DATABASE school; -- Deletes the database entirely
-- Creating a table with constraints
CREATE TABLE student (
rollno INT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
marks DECIMAL(5,2),
gender CHAR(1)
);
-- Modifying table architecture
ALTER TABLE student ADD email VARCHAR(40); -- Adds a column
ALTER TABLE student DROP COLUMN gender; -- Removes a column
-- Adding data records
INSERT INTO student VALUES (1, 'Amit', 92.50, 'M');
-- Modifying values conditionally
UPDATE student SET name = 'Rahul' WHERE rollno = 1;
-- Deleting filtered entries
DELETE FROM student WHERE rollno = 1;
SELECT * FROM student; -- Retrieves all rows and columns
SELECT name, marks FROM student WHERE marks > 90; -- Filtered retrieval
SELECT DISTINCT gender FROM student; -- Removes duplicate rows from output
SELECT * FROM student ORDER BY marks DESC; -- Sorts results in descending order
Constraints
Constraints are the rules that we can apply on the type of data in a table. That is, we
can specify the limit on the type of data that can be stored in a particular column in a
table using constraints.
We can specify constraints at the time of creating the table using CREATE TABLE
statement. We can also specify the constraints after creating a table using ALTER
TABLE statement.
Syntax:
Below is the syntax to create constraints using CREATE TABLE statement at the time of
creating the table.
CREATE TABLE sample_table
(
column1 data_type(size) constraint_name,
column2 data_type(size) constraint_name,
column3 data_type(size) constraint_name,
....
);
sample_table: Name of the table to be created.
data_type: Type of data that can be stored in the field.
constraint_name: Name of the constraint. for example- NOT NULL, UNIQUE, PRIMARY KEY etc.
NOT NULL
Definition: Ensures that a column cannot have null (empty) values. Every
record must have a value for this column.
Usage: Used when a field is required for every record, such as a user’s email
address.
Example: Email VARCHAR(100) NOT NULL means every student must have
an email address.
CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);
UNIQUE
Definition: Ensures that all values in a column are distinct. No two records
can have the same value for this column.
Usage: Used to enforce uniqueness, like in a column for social security
numbers or usernames.
Example: Username VARCHAR(50) UNIQUE ensures no two users can have
the same username.
CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);
PRIMARY KEY
Definition: A combination of NOT NULL and UNIQUE. It uniquely identifies
each row in a table and cannot be null.
Usage: Used to uniquely identify records, such as in an ID column or a
student number.
Example: StudentID INT PRIMARY KEY means each student will have a
unique StudentID that cannot be duplicated or null.
CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20),
PRIMARY KEY(ID)
);
Summary
Data Types:
CHAR(n): Fixed-length text.
VARCHAR(n): Variable-length text.
INT: Whole numbers.
FLOAT: Decimal numbers.
DATE: Calendar dates.
Constraints:
NOT NULL: Column must have a value.
UNIQUE: Values in the column must be distinct.
PRIMARY KEY: Unique identifier for each row, combining NOT NULL and
UNIQUE.
Create Database, Use Database, Show Databases, Drop Database
SQL Commands for Database Management
Managing databases involves creating, selecting, and deleting databases. Here are the
essential SQL commands for these tasks:
1. CREATE DATABASE
Definition: Creates a new database. This command sets up a new database
environment where tables, views, and other objects can be created.
Syntax:
sql
CREATE DATABASE database_name;
Example: To create a database named SchoolDB, you would use:
sql
CREATE DATABASE GeeksForGeeks;
Output:
2. USE DATABASE
Definition: Select the database to use for subsequent SQL commands. Once
a database is selected, any operations (such as creating tables or querying
data) will be performed on that database.
Syntax:
sql
USE database_name;
Example: To switch to the SchoolDB database, you would use:
sql
USE SchoolDB;
3. SHOW DATABASES
Definition: Lists all databases available in the database management system
(DBMS). This command helps you view all existing databases and check their
names.
Syntax:
sql
SHOW DATABASES;
Example: Running this command will display a list of all databases, including SchoolDB, if it
exists.
4. DROP DATABASE
Definition: Deletes an existing database and all of its contents, including
tables, data, and other objects. This command is irreversible, so be cautious
when using it.
Syntax:
sql
DROP DATABASE database_name;
Example: To delete the SchoolDB database, you would use:
sql
DROP DATABASE SchoolDB;
Show Tables, Create Table, Describe Table, Alter Table (add and
remove an attribute, add and remove primary key), Drop Table, Insert,
Delete
SQL Commands for Table Management
Managing tables involves creating, modifying, and deleting tables, as well as inserting
and deleting data. Here’s a guide to essential SQL commands for these tasks:
1. SHOW TABLES
Definition: Lists all the tables in the currently selected database. Useful for
viewing existing tables and verifying their names.
Syntax:
sql
SHOW TABLES;
Example: Running this command will display a list of tables in the selected
database.
2. CREATE TABLE
Definition: Creates a new table in the database with specified columns and
their data types.
Syntax:
sql
CREATE TABLE table_name ( column1 datatype constraints, column2 datatype constraints, ...);
Example: To create a table named Students with columns StudentID, Name, and
DateOfBirth:
sql
CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT
NULL, DateOfBirth DATE);
3. DESCRIBE TABLE
Definition: Displays the structure of a table, including column names, data
types, and constraints.
Syntax:
sql
DESCRIBE table_name;
Example: To view the structure of the Students table:
sql
DESCRIBE Students;
4. ALTER TABLE
Definition: Modifies the structure of an existing table, such as adding or
removing columns and constraints.
Add an Attribute:
Syntax:
sql
ALTER TABLE table_name
ADD column_name datatype constraints;
Example: To add an Email column to the Students table:
sq
ALTER TABLE Students
ADD Email VARCHAR(100);
Remove an Attribute:
Syntax:
sql
ALTER TABLE table_name
DROP COLUMN column_name;
Example: To remove the Email column from the Students table:
sql
ALTER TABLE Students
DROP COLUMN Email;
Add a Primary Key:
Syntax:
sql
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
Example: To add a primary key constraint to the StudentID column:
sql
ALTER TABLE Students
ADD PRIMARY KEY (StudentID);
Remove a Primary Key:
Syntax:
sql
ALTER TABLE table_name
DROP PRIMARY KEY;
Example: To remove the primary key constraint:
sq
ALTER TABLE Students
DROP PRIMARY KEY;
5. DROP TABLE
Definition: Deletes an existing table and all of its data. This action is
irreversible.
Syntax:
sql
DROP TABLE table_name;
Example: To delete the Students table:
sql
DROP TABLE Students;
6. INSERT
Definition: Adds new rows of data into a table.
Syntax:
sql
INSERT INTO table_name (column1, column2, ...)VALUES (value1, value2, ...);
Example: To insert a new student into the Students table:
sql
INSERT INTO Students (StudentID, Name, DateOfBirth)VALUES (1, 'Alice Smith', '2005-05-15');
7. DELETE
Definition: Removes existing rows from a table based on a specified
condition.
Syntax:
sql
DELETE FROM table_name
WHERE condition;Example: To delete a student with StudentID 1 from the Students table:
sql
DELETE FROM Students
WHERE StudentID = 1;