Data Management I

Database Query Language

Chinonso G. Alaebo [Link] Data Science

 Data Management I

Learning Objectives
At the end of this lesson, you should be able to:
● Explain the purpose of database query languages
(SQL and QBE) and their role in managing data.
● Identify and describe the categories of SQL
commands.
● State the features of Query By Example (QBE).
● Compare SQL and NoSQL query languages.
 Data Management I

Introduction to Database query language

This lesson explores two fundamental aspects of
database systems which are database query
languages and their applications. Understanding
these concepts is crucial for eectively managing
and manipulating data in a database. We’ll dive into
the structure and purpose of query languages like
SQL, and Query By Example (QBE).
 Data Management I

Database query language

Database query languages are specialised
languages used to queries in databases and
retrieve or manipulate data. They allow users to
interact with the database system to perform
various operations such as retrieval, insertion,
update, and deletion of data.
Data Management I

Structured Query
Language (SQL)
 Data Management I

Structured Query Language (SQL)

SQL is the most widely used database query language
for relational databases. It allows users to perform
various operations such as querying data, updating
records, and managing database objects.
 Data Management I

Concepts of Database Query Language

Example SQL commands:

-- Creating a table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Major VARCHAR(50)
);
 Data Management I

Concepts of Database Query Language

Example SQL commands:
-- Inserting data into a table
INSERT INTO Students (StudentID, Name, Major)
VALUES (1, 'John Doe', 'Computer Science');
 Data Management I

Components of SQL
Data Definition Language (DDL) is a subset of SQL
(Structured Query Language) used to define and
manage the structure of a database. DDL commands
allow database administrators and developers to
create, modify, and delete database objects such as
tables, indexes, schemas, and constraints.
 Data Management I

Components of SQL
Data Manipulation Language (DML) is a subset of SQL
(Structured Query Language) used to interact with and
manipulate data within a database. DML commands allow
users to retrieve, insert, update, and delete data stored
in database tables while preserving the structure
defined by the Data Definition Language (DDL).
 Data Management I

Components of SQL
Data Control Language (DCL): is a subset of SQL
(Structured Query Language) used to manage
access rights and permissions within a database.
DCL commands ensure that only authorised users
can access, modify, or manage the database and its
components. It plays a crucial role in database
security and compliance by controlling who can
perform specific actions.
 Data Management I

Concepts of Database Query Language

Transaction Control Language (TCL): manages database
transactions. The most frequently used commands in
this category are COMMIT, ROLLBACK, and SAVEPOINT.
They ensures the integrity of data during and after
transactions.
Data Management I

Query By Example (QBE)

 Data Management I

Query By Example (QBE)

Query By Example (QBE) is a database query language
for relational databases. It provides a graphical or visual
approach to querying databases where users create
queries by filling out templates (examples) to specify the
criteria for the desired data. QBE is particularly
user-friendly, making it accessible to users who may not
be familiar with traditional query languages like SQL.
 Data Management I

Application of QBE
QBE is typically used in database management systems
that support graphical user interfaces (GUIs)like Microsoft
Access. It allows users to interact with the database
through forms or templates rather than writing code,
simplifying the process of data retrieval.
 Data Management I

Components and Process

1. Forms and templates:
Users interact with the database using forms that
represent tables and fields in a graphical format.

Each form corresponds to a database table, and fields in

the form represent columns in the table.
 Data Management I

Components and Process

2. Filling out templates:
Users fill out the form with example values to specify search
criteria. For instance, to find all customers from New York, a
user would enter "New York" in the city field of the customer
form.
 Data Management I

Query By Example (QBE) Use case

Consider a database with a table named Customers with the following
columns: CustomerID, Name, City, and Phone.

● A QBE form for this table would include fields for each column.
● To find all customers from "New York," the user would fill out the
form as follows. The QBE system would then generate and
execute an SQL query similar to:

SELECT * FROM Customers WHERE City = 'New York';

Data Management I

Advantages and
Disadvantages of QBE
 Data Management I

Advantages of QBE
Visual representation:
Users can see a graphical representation of
their query, which helps in understanding and
refining the search criteria.
 Data Management I

Advantages of QBE
Eiciency:
QBE reduces the need to write complex SQL
queries, speeding up the query-building process.
QBE speeds up query formulation as users do not
need to manually write or debug query
statements.
 Data Management I

Advantages of QBE
User-friendly:
Provides a simple and intuitive way to query
databases, making it accessible to
non-technical users. QBE is designed with a
graphical interface, making it easier for
non-technical users to create database queries
without needing to know complex query
languages like SQL.
 Data Management I

Disadvantages of QBE
● Limited flexibility
QBE may not support all the advanced features and
capabilities of SQL, limiting the complexity of queries that
can be performed.

● Dependent on Graphical Users Interface (GUI)

QBE requires a graphical user interface, which may not be
available in all database management systems.
Data Management I

Mid-Lesson Question
 Data Management I

Mid-Lesson Questions
What is the primary advantage of Query By Example (QBE)?

It requires users to write complex SQL

A queries manually.

It provides a graphical interface for data

B retrieval using forms and templates.

It eliminates the need for database

C schema design.

It standardises query languages

D across all databases.
 Data Management I

Mid-Lesson Questions
What is the primary advantage of Query By Example (QBE)?

It requires users to write complex SQL Explanation: QBE simplifies data retrieval
A queries manually. by allowing users to interact with the
database visually without writing code.

It provides a graphical interface for data

B retrieval using forms and templates.

It eliminates the need for database

C schema design.

It standardises query languages

D across all databases.
 Data Management I

Mid-Lesson Questions
Which of the following is NOT a component of SQL?

A Data Definition Language (DDL).

B Data Manipulation Language (DML).

C Data Query Language (DQL).

D Transaction Control Language (TCL).

 Data Management I

Mid-Lesson Questions
Which of the following is NOT a component of SQL?

Explanation: SQL is composed of DDL,

A Data Definition Language (DDL). DML, DCL, and TCL. DQL is not a standard
component of SQL.

B Data Manipulation Language (DML).

C Data Query Language (DQL).

D Transaction Control Language (TCL).

Data Management I

NoSQL Query Languages

NoSQL Query Languages
 Data Management I

NoSQL Query Languages

NoSQL (Not Only SQL) query languages are designed to
query and manage data in NoSQL databases. Unlike
traditional SQL databases, NoSQL databases can handle a
wide variety of data models, including document,
key-value, wide-column, and graph formats. As a result,
NoSQL query languages are diverse and tailored to the
specific data model and use case of each NoSQL database.
 Data Management I

Types of NoSQL Databases and their Query Languages

Document databases:
● MongoDB
● CouchDB

Query language:
● MongoDB uses a JSON-like query language that
allows for complex querying and aggregation
operations.
 Data Management I

Types of NoSQL Databases and their Query Languages

Example:
● [Link].find({ "city": "New York" })
This query retrieves all documents from the customers collection where the city field is
"New York."
 Data Management I

Types of NoSQL Databases and their Query Languages

Wide-column stores:
a) Cassandra
b) HBase

Query language:
Cassandra uses the Cassandra Query Language
(CQL), which is similar to SQL but tailored for its
column-family data model.
 Data Management I

Types of NoSQL Databases and their Query Languages

Document databases
Data model: Store data as JSON, BSON, or XML documents.

Key features:
● Flexible schema for unstructured or semi-structured data.
● Each document contains fields and values (arrays, nested documents, etc.).

Use cases:
● Content management systems.
● Real-time analytics.

Examples:
MongoDB, CouchDB.
 Data Management I

Types of NoSQL Databases and their Query Languages

Graph databases
Data model:
NoSQL represent data as nodes, edges, and properties, focusing
on relationships.

Key features:
● Ideal for interconnected data.

● Queries based on relationships(e.g., shortest path,

connected nodes).
 Data Management I

Types of NoSQL Databases and their Query Languages

● Use cases:
○ Social networks.

○ Recommendation systems.

○ Fraud detection.

● Examples:
○ Neo4j, Amazon Neptune.
 Data Management I

Types of NoSQL Databases and their Query Languages

Key-value databases
● Data model: Data is stored as key-value pairs.

● Key features:
○ Simple and fast lookups by key.

○ Values can be strings, JSON, or any binary data.

 Data Management I

Types of NoSQL Databases and their Query Languages

Key-value databases
● Use cases:
○ Caching.

○ Session management.

○ User preferences storage.

● Examples:
○ Redis, Amazon DynamoDB.
Data Management I

Advantages and
NoSQL Query Languages
Disadvantages of NoSQL
Database Languages
 Data Management I

Advantages of NOSQL
1. Scalability
● Horizontal scaling: NoSQL databases are
designed for easy horizontal scaling, allowing
the addition of more servers to handle
increased load.

● Ideal for applications with growing datasets and

high traic.
 Data Management I

Advantages of NOSQL
2. Flexibility
● Schema-less design: NoSQL databases allow dynamic
schema changes, making them suitable for unstructured or
semi-structured data.

● Easily adapt to changes in application requirements without

significant database restructuring.
 Data Management I

Advantages of NOSQL
3. High performance
● Optimised for specific data models and use cases,
resulting in faster data access and processing.

● Eicient handling of read/write operations,

especially for large datasets.
 Data Management I

Disadvantages of NOSQL
1. Lack of standardisation:
● No unified query language like SQL across all NoSQL
databases.

● Each NoSQL system has its own query mechanism and

syntax, leading to steep learning curves when switching
between databases.
 Data Management I

Disadvantages of NOSQL
2. Limited maturity:
● Compared to relational databases (RDBMS), many
NoSQL databases are relatively newer and may lack
the robustness and maturity of well-established
RDBMS solutions.
 Data Management I

Disadvantages of NOSQL
3. Complexity in joins:
● NoSQL databases are not designed for complex
relationships and joins between datasets.

● Workarounds, such as manual data aggregation, can

increase application complexity and development eort.
 Data Management I

Comparison of SQL and NoSQL

Aspect SQL NoSQL

Data model Relational (table-based with Non-relational (document, key-value,
rows and columns). wide-column, or graph-based).
Schema Fixed schema; requires Flexible schema; supports dynamic or
predefined structure. unstructured data.
Query language Structured Query Language Database-specific query languages,
(SQL), standardised across such as MongoDB’s JSON-like syntax
platforms. or Cypher for Neo4j.
Examples MySQL, PostgreSQL, Oracle MongoDB, Cassandra, Neo4j,
Database, Microsoft SQL DynamoDB, Couchbase.
Server.
Data Management I

NoSQL Query Languages

Summary
 Data Management I

Summary
● Database query languages like SQL and QBE enable users to
interact with databases for data retrieval, insertion, updates,
and deletion.

● SQL components include DDL (defining structures), DML

(manipulating data), DCL (controlling access), and TCL
(managing transactions).

● Query By Example (QBE) provides a visual interface for querying

databases using forms and templates, simplifying data retrieval
for non-technical users.
 Data Management I

Summary
● NoSQL query languages cater to diverse database types like
document, wide-column, and graph databases, oering
flexibility and scalability for specific use cases.

● SQL excels in relational data management, while NoSQL is

ideal for handling unstructured or semi-structured data and
horizontal scaling.

● Advantages of NoSQL include flexibility, scalability, and

performance; disadvantages include complexity, lack of
standardisation, and a less mature ecosystem.
 Data Management I

Further Reading Resources

● Laudon, K. C., & Laudon, J. P. (2020). Management Information Systems:
Managing the Digital Firm (16th ed.). Pearson.
● Stair, R., & Reynolds, G. (2021). Principles of Information Systems (14th ed.).
Cengage Learning.
● Turban, E., Pollard, C., & Wood, G. (2018). Information Technology for
Management: On-Demand Strategies for Performance, Growth and
Sustainability (11th ed.). Wiley.
● Chaey, D., & White, G. (2011). Business Information Management (2nd ed.).
Pearson Education.
● Becerra-Fernandez, I., Leidner, D. E., & Leidner, M. J. (2014). Knowledge
Management: An Evolutionary View (3rd ed.). Routledge.
Data Management I

Thank
You