OVERVIEW OF DATABASE LANGUAGES:

SQL COMMANDS
SQL commands are instructions, they are used to communicate with the database, they are
also used to perform specific tasks, functions and queries of data.
SQL can perform various tasks like create a table, add data to tables, drop the table, to
modify the table, set permissions for users etc.
Types of SQL Commands
There are five main types of SQL commands: Data Definition Language (DDL), Data Query
Language (DQL), Data Manipulation Language (DML), Data Control Language (DCL), and
Transaction Control Language (TCL).

1. Data Definition Language (DDL):

• DDL changes the structure of the table. These commands define and modify
the structure of a database.
• All the commands of the DDL are auto committed, that means it permanently
saves all the changes in the database.
Some commands under the DDL:
I. CREATE: It is used to create a new database or table in the database.
II. DROP: It is used to delete both the structure and record stored in a database
table. It is basically used to delete a table or the entire database.
III. ALTER: It is used to alter the structure of the database. This change could be
either to modify the characteristics of an existing attribute or to add a new
attribute.
IV. TRUNCATE: It is used to delete all the rows from a table but keeps the
database structure.
V. COMMENTS: Comments are used to document the code and make it easier to
understand. They are ignored during execution.
VI. RENAME: It is used to manage and modify the database schema without
altering the data.

2. Data Manipulation Language (DML):

• These commands manipulate the data in tables.
• DML commands are used to modify the database. It is responsible for all
forms of change in the database.
• DML commands are not auto committed, that means it cannot permanently
save all the changes in the database. They can be Rollback.
Some commands under the DML:
I. INSERT: The insert statement is an SQL query. It is used to add new rows i.e. to
insert data into the rows of a table.
II. UPDATE: This command is used to update or modify the value of an existing
row in a database table.
III. DELETE: This is used to remove specific rows from a table.

3. Data Query Language (DQL):

• This command is used to retrieve or fetch data from the database.
• It uses only one command which is the SELECT command.
Some commands under the DQL:
I. SELECT: It is used to query data from one or more tables in a database.

4. Data Control Language (DCL):

• These commands manage permissions.
• DCL commands are used to grant and take back authority from any database
user.
Some commands under the DQL:
I. GRANT: It is used to give users access privileges to a database.
II. REVOKE: It is used to take back permissions from the users.

5. Transaction Control Language (TCL):

• These commands manage transactions.
• TCL commands can only work with DML commands like (Insert, Update and
Delete).
• These operators are auto committed in the database that is why they cannot
be used while creating tables or dropping them.
Some commands under the TCL:
I. COMMIT: Commit commands are used to save all the transactions to the
database.
II. ROLLBACK: Rollback command is used to undo all the transactions that have
not already been saved to the database.
III. SAVEPOINT: It is used to roll the transaction back to a certain point without
rolling back the entire transaction.

DATATYPES
In the context of database, a datatype defines the kind of data that can be stored in a
particular column of a database table. It specifies the type of value (e.g. number, text, data)
and determines how much memory the data will occupy as well as the operations that can
be performed on it.
Common Database Datatypes:
1. Numeric Datatypes:
These store numeric values.
• INT: Integer values (whole numbers)
Examples: 122, -45 etc.

• DECIMAL (p, s): Fixed point numbers with Precision (p) and Scale (s).
Example: Decimal (5, 2). This stores up to five (5) digits, with two (2) after the
decimal point, (e.g. 123.56)

• FLOAT: Approximate decimal values for large or small numbers.

Example: 3.142596….

• BIGINT: Larger integers than INT.

Example: 92383844768282738445….

• SMALLINT: Smaller range of integer values.

2. Character Datatypes:
They are used to store text or strings.
• CHAR (n): Fixed length string with exactly n characters.
Example: CHAR (5) stores “Hello” or “He ” (padded with spaces).

• VARCHAR (n): Variable length strings up to n character.

Example: VARCHAR (50) stores “John” or “This is a long sentence”.
• TEXT: Variable-length text for large strings.
Example: Stores entire paragraphs or documents.

3. Date and Time Datatypes:

They are used for storing date and time information.
• DATE: Stores a date in YYYY—MM—DD format.
Example: 2024-12-25.

• TIME: Stores time in HH: MM: SS format.

Example: 14:30:00

• DATETIME or TIMESTAMP: Stores date and time together.

Example: 2024-12-25 14:30:00

• YEAR: Stores only the year.

Example: 2025

4. Binary Datatypes:
They are used for binary data like files or images.
• BLOB: Binary Large Object for storing binary files (images, videos etc.)

5. Boolean Datatype:
They store True or False values.
• BOOL: Stores TRUE or FALSE.
Some databases use SMALLINT where 1 = TRUE and 0 = FALSE
Choosing the right datatype

• Use INT for ID’s or whole numbers.

• Use CHAR for fixed-length codes e.g. (course code, country codes etc.)
• Use VARCHAR for variable-length text name or emails.
• Use DATE, TIME or DATETIME for date/time values.
• Use TEXT for long descriptive fields.
SYNTAX
In programming, syntax refers to the set of rules that define the correct structure and format
of statements in a programming language.
In the context of databases, syntax refers to the rules for structuring queries and commands
in a database management system (DBMS). These commands are typically written in SQL
(Structured Query Language) to interact with the database. Below are key aspects of SQL
syntax:
1. Basic SQL Query Syntax
•Retrieve data from one or more tables.
•Syntax:
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Example:
SELECT name, age
FROM students
WHERE age > 18;

2. Creating a Table
•Used to define a new table in the database.
•Syntax:
CREATE TABLE table_name
(
column1 datatype constraints,
column2 datatype constraints,
...
);
Example:
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
enrollment_date DATE
);

3. Inserting Data into a Table

•Adds new records (rows) into a table.
•Syntax:
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
Example:
INSERT INTO students (id, name, age, enrollment_date)
VALUES (1, 'John Doe', 20, '2024-01-01');

4. Updating Records
•Modifies existing records in a table.
•Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Example:
UPDATE students
SET age = 21
WHERE id = 1;
5. Deleting Records
•Removes records from a table.
•Syntax:
DELETE FROM table_name
WHERE condition;
Example:
DELETE FROM students
WHERE id = 1;

6. Altering Tables
•Modifies an existing table structure.
•Add a Column:
ALTER TABLE table_name
ADD column_name datatype;

•Drop a Column:
ALTER TABLE table_name
DROP COLUMN column_name;

Example:
ALTER TABLE students
ADD email VARCHAR(100);

7. Dropping Tables
• Deletes a table from the database.
• Syntax:
DROP TABLE table_name;
Example:
DROP TABLE students;

8. Creating Indexes
•Improves the speed of data retrieval.
•Syntax:
CREATE INDEX index_name
ON table_name (column_name);
Example:
CREATE INDEX idx_name
ON students (name);

Etc…

Key Notes on Database Syntax:

• SQL commands are not case-sensitive, but it’s a good practice to write keywords in
uppercase (e.g., SELECT, WHERE).
• Always terminate SQL statements with a semicolon ;
• Follow the correct order of clauses in queries: SELECT → FROM → WHERE → GROUP
BY → HAVING → ORDER BY.

CONSTRAINTS
In a database, constraints are rules applied to table columns to ensure the integrity,
accuracy, and reliability of the data. They help enforce certain conditions on data before it is
inserted, updated, or deleted.
Here are the common types of constraints in a database:
1. NOT NULL
•Ensures that a column cannot have NULL values.
•Syntax:
CREATE TABLE table_name (
column_name datatype NOT NULL
);
Example:
CREATE TABLE students (
id INT NOT NULL,
name VARCHAR(50) NOT NULL
);

2. UNIQUE
•Ensures that all values in a column are distinct.
•Syntax:
CREATE TABLE table_name (
column_name datatype UNIQUE
);
Example:
CREATE TABLE students
(
id INT UNIQUE,
email VARCHAR(100) UNIQUE
);

3. PRIMARY KEY
•Uniquely identifies each row in a table. Combines NOT NULL and UNIQUE.
•A table can have only one primary key.
•Syntax:
CREATE TABLE table_name (
column_name datatype PRIMARY KEY
);
Example:
CREATE TABLE students
(
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
•For composite keys:
CREATE TABLE enrollments (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id)
);

4. FOREIGN KEY
•Ensures referential integrity by linking a column to the primary key of another table.
•Syntax:
CREATE TABLE table_name (
column_name datatype,
FOREIGN KEY (column_name) REFERENCES
parent_table (primary_key_column)
);
Example:
CREATE TABLE enrollments (
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students (id)
);

5. CHECK
•Ensures that column values meet specific conditions.
•Syntax:
CREATE TABLE table_name
(
column_name datatype CHECK (condition)
);
Example:
CREATE TABLE students (
id INT PRIMARY KEY,
age INT CHECK (age >= 18)
);

6. DEFAULT
•Assigns a default value to a column when no value is provided.
•Syntax:
CREATE TABLE table_name (
column_name datatype DEFAULT default_value
);
Example:
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
enrollment_date DATE DEFAULT CURRENT_DATE
);

7. AUTO_INCREMENT (MySQL-specific)
•Automatically generates a unique number for a column, usually for primary keys.
•Syntax:
CREATE TABLE table_name (
column_name INT AUTO_INCREMENT PRIMARY KEY
);
Example:
CREATE TABLE students
(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);

8. INDEX (Not strictly a constraint)

•Improves the speed of data retrieval by creating an index on one or more columns.
•Syntax:
CREATE INDEX index_name
ON table_name (column_name);
Example:
CREATE INDEX idx_name
ON students (name);
Summary Table of Constraints

Constraint Description
NOT NULL Ensures column cannot contain NULL values.

UNIQUE Ensures all column values are unique.

PRIMARY KEY Uniquely identifies each row (combines NOT
NULL + UNIQUE).
FOREIGN KEY Enforces referential integrity between tables.
CHECK Ensures data meets specified conditions.
DEFAULT Sets a default value for a column.
AUTO_INCREMENT Automatically generates sequential numbers
(MySQL).
INDEX Improves query performance (not strictly a
constraint).

Here’s a complete workflow using SQL commands to manage a database for a student
management system.

1. Create the Database and Table (DDL)

Start by creating a database and defining a table structure.

-- Create a database:
CREATE DATABASE StudentManagement;

-- Use the database:

USE StudentManagement;

-- Create a Students table:

CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Age INT,
Major VARCHAR(50),
Email VARCHAR(100)
);

2. Insert Data into the Table (DML)

-- Insert student records:

INSERT INTO Students (ID, Name, Age, Major, Email)
VALUES
(1, 'Alice', 21, 'Computer Science', '[email protected]'),
(2, 'Bob', 22, 'Mathematics', '[email protected]'),
(3, 'Charlie', 20, 'Physics', '[email protected]');

3. Query Data (DQL)

-- Retrieve all students:

SELECT * FROM Students;

-- Retrieve specific columns:

SELECT Name, Major
FROM Students;

-- Retrieve students with a specific condition:

SELECT *
FROM Students
WHERE Major = 'Mathematics' AND Age > 20;

The (*) sign represents ALL. So (SELECT * FROM Students;) means select all from students.

4. Update Data (DML)

-- Update a student's major:

UPDATE Students
SET Major = 'Data Science'
WHERE ID = 1;

-- Update multiple fields:

UPDATE Students
SET Email = '[email protected]', Age = 23
WHERE ID = 2;

5. Delete Data (DML)

-- Delete a specific student:

DELETE FROM Students
WHERE ID = 3;

-- Delete all students in a specific major:

DELETE FROM Students
WHERE Major = 'Mathematics';
6. Manage Transactions (TCL)

-- Start a transaction:
START TRANSACTION;

-- Make some changes:

INSERT INTO Students (ID, Name, Age, Major, Email)
VALUES (4, 'Diana', 19, 'Biology', '[email protected]');

UPDATE Students
SET Major = 'Chemistry'
WHERE ID = 4;

-- Rollback the transaction if needed:

ROLLBACK;

-- Commit the changes:

COMMIT;

7. Manage Permissions (DCL)

-- Grant a user permission to view and insert data:

GRANT SELECT, INSERT ON Students TO 'user1'@'localhost';

-- Revoke the insert permission:

REVOKE INSERT ON Students FROM 'user1'@'localhost';
8. Alter Table Structure (DDL)

-- Add a new column for GPA:

ALTER TABLE Students
ADD GPA DECIMAL(3, 2);

-- Drop the GPA column:

ALTER TABLE Students
DROP COLUMN GPA;

9. Delete Table and Database (DDL)

-- Remove the Students table:

DROP TABLE Students;

-- Remove the database:

DROP DATABASE StudentManagement;

This workflow gives you an end-to-end example of using SQL commands to manage a
database. Let me know if you’d like help with setting up an environment to test these
commands, like MySQL, PostgreSQL, or SQLite!.

OPERATORS AND OTHER FUNCTIONS IN QUERY STATEMENT

In query statements, different operators can be used to manipulate data to get needed
information. Such operators include:
• “ =” use to specify the exact required criteria
• ‘BETWEEN’ used for conditional queries to specify range of values. A BETWEEN
statement is true for values that fall within specified minimum and maximum
values.
Example:
SELECT lastname,
firstname FROM student
WHERE age BETWEEN ‘16’ AND ‘25’;

• ‘AND’ operator allows you to add additional criteria WHERE statement. It is known as
CONJUNCTION operation, the AND statement is true only when both criteria
connected by AND is true.
Example:

SELECT lastname, firstname

FROM student, dept

WHERE age BETWEEN ‘16’ AND ‘25’

AND department_name = “Computer Science”

Here the students’ lastname and firstname will be listed only if their age is between 16 and
25 years and their department is Computer Science.

• ‘OR’ DISJUNCTION operator can also be used with WHERE statement, unlike AND both
conditions must not be true to appear in result but any of the conditions must be satisfied.

• ‘NOT’ NEGATION can also be used with WHERE statement to display values in which the
specified condition is not true.
For example, to pull other students except Computer Science students from the database,
it can be queried thus:
Example:
SELECT lastname, firstname
FROM student, dept
WHERE NOT department_name = “Computer Science”

• LIMIT function
This can be used to limit the number of results that can be gotten especially when sampling
is needed from a voluminous database.
• VIEWS
Views are virtual tables created by selecting fields from one or more tables present in the
database. They also have rows and columns. They can have specific rows based on certain
conditions or all the rows of a table.

Syntax for view creation in SQL:

CREATE VIEW view_name AS
SELECT column1, column2 (field)…
FROM table_name
WHERE condition;

Creating a view from a single table, for example let’s create a view named Student_Detail
from a table named student.
CREATE VIEW Student_Detail AS
SELECT Matno, Name, gender
WHERE age<=20;

Creating view from multiple tables:

CREATE VIEW Student_score AS

SELECT Student.matno, student.name, student.department, Score.score
FROM Student,score
WHERE Student.matno = score.matno;

To display data from the view:

Student_score SELECT * FROM Student_score;

To delete a view:

DROP view name;

Example:

DROP Student_score;