Chapter 5_Database Query Languages

Query language

is a specialized computer language used to make queries and retrieve information from databases.

Query language

It serves as an interface between users and databases, enabling users to manage data from a database management system (DBMS).

Structured Query Language (SQL),

• One of the most widely used modern query languages is ___

• a domain-specific programming language used to interact with relational databases and perform operations on structured data within.

"FIND," "INSERT," "DELETE," "ORDER BY," "SUM," and "SELECT,"

schema, searching, populating, updating

Structured query languages make use of commands like "___," "___," "___," "___," "___," and "___," which can be formed into syntactical statements for operations including defining and changing database ___, ___, ___, and ___ the contents of a database, and defining everything from triggers and integrity constraints to stored procedures and authorization rules.

query

• In the broadest sense, a ___ refers to a request for data or information from a database or a data repository system.

• This request is typically made in the form of a specific question or command, written in a query language that the database understands.

query

A ___can be as simple as fetching a specific subset of data from a particular table or as complex as performing intricate calculations and data transformations.

find,

summarize,

filter,

combine,

adjust,

delete,

insert,

update

(FSFCADIU)

Queries can be used to ___, ___, ___, ___, ___, ___, ___, and ___ data. They can also answer data-related questions, analyze data from multiple tables at once, and automate data management tasks.

select queries,

parameter queries,

make-table queries,

append queries,

crosstab queries

Some basic types of queries include ___, ___, ___, ___, and ___, but with the structured commands enabled by query languages, queries can also grow exponentially elaborate.

procedural

declarative

Primary types of query languages:

Query languages are integral in database management, serving as mediums to retrieve and manipulate data.

Broadly categorized, they fall into two buckets: 

Procedural languages

__ instruct a computer on how to execute tasks, and the focus is on defining step-by-step procedures.

src

Procedural languages require a specific exact ___ to the data, accounting for every step taken to fetch and construct the desired output.

Declarative query languages

___ abstract the 'how-to,' focusing on the 'what' — defining only the desired outcome without dictating the retrieval method. They are more aligned with traditional database queries and allow for easier manipulation of data structures, increasing productivity.

SQL, 

PL/SQL

Declarative query examples include the widely used ___, while procedural paradigms are exemplified by languages like ___.

relational databases,

declarative language

SQL is by far the most popular and commonly used query language for ___.

It is known as a ___, meaning it describes what needs to be accomplished rather than how to accomplish it, but it also includes traditionally procedural elements.

• Data Query Language (DQL)

• Data Definition Language (DDL)

• Data Control Language (DCL)

• Data Manipulation Language (DML)

• Transaction Control Language (TCL)

SQL sub-languages are commonly divided into five main categories: (5)

Data Query Language (DQL)

is used to query or retrieve data from the database. It typically involves using the SELECT statement.

Example

SELECT * FROM employees WHERE department = 'IT';

Data Definition Language (DDL)

is used to define and modify the structure of database objects such as tables, indexes, and schemas.

Data Definition Language (DDL)

Examples: Creating Table

CREATE TABLE employees (

id INT PRIMARY KEY,

name VARCHAR(100),

department VARCHAR(50)

);

Data Definition Language (DDL)

Examples: After Table

ALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);

Examples: Drop Table

DROP TABLE employees;

Data Control Language (DCL)

is used to control access to the data in the database, dealing with permissions and user rights.

Examples: Grant Permission

GRANT SELECT ON employees TO user_name;

Examples: Revoke Permission

REVOKE INSERT ON employees FROM user_name;

Data Manipulation Language (DML)

is used to manipulate data within the tables, allowing for insertion, updates, deletion, and retrieval.

Data Manipulation Language (DML)

Example: Insert

INSERT INTO employees (id, name, department) VALUES (1, 'John Doe', 'IT’);

Example: Update

UPDATE employees SET department = 'HR' WHERE id = 1;

Example: Delete

DELETE FROM employees WHERE id = 1;

Transaction Control Language (TCL)

is used to manage transactions within a database, ensuring the integrity and consistency of data.

Transaction Control Language (TCL)

Example: Commit Transaction

COMMIT;

Example: Rollback Transaction

ROLLBACK;

Example: Save Transaction Point

SAVEPOINT savepoint_name;

NoSQL,

Not Only SQL

there are languages for non-relational databases called ___, or "___" databases.

key values,

graphs,

wide columns,

documents

Unlike relational databases that use tables and structured data, NoSQL databases use methods like ___, ___, ___, and ___ to store and retrieve data.

dynamic schemas

While SQL is suited for relational databases housing structured data and using predefined schemas, NoSQL excels with ___ for unstructured data and big data.

serve different purposes

Query languages and programming languages ______.

While both are critical in the realm of computing, each has a distinct role in interaction with data and systems:

build

Programming languages like Python or Java are designed to ___ software applications, handling everything from algorithmic processing to data manipulation and user interface design.

interacting

Query languages, on the other hand, are specialized for ___ with databases.

Query languages

__ exist within a subset of computer languages — solely focused on data retrieval and management. This specialization allows users to extract, update, and manipulate data within a database without affecting the underlying software logic.

Query commands

__ are concise and highly focused, simplifying complex data operations into readable statements. This is a major difference from programming languages, which often require multiple lines of code to perform similar database operations.

GraphQL

allows clients to request specific data, reducing over-fetching and under-fetching.

Example:

{

user(id: "123") {

name

email

posts {

title

content

}

}

}

SPARQL

is used to query RDF data on the Semantic Web

Example:

SELECT ?person ?email

WHERE {

?person rdf:type ex:Employee .

?person ex:email ?email .

}

SQL

is used to manage relational databases, with operations like SELECT, INSERT, UPDATE, and DELETE.

Example:

SELECT name, email

FROM employees

WHERE department = 'IT’;

Oracle PL/SQL

is Oracle’s procedural extension to SQL, allowing for more complex operations.

Example:

BEGIN

FOR emp IN (SELECT id, salary FROM employees WHERE department = 'HR') LOOP

emp.salary := emp.salary * 1.10;

UPDATE employees SET salary = emp.salary WHERE id = emp.id;

END LOOP;

COMMIT;

END;

Prisma

is a database toolkit that offers type-safe query building for modern databases.

Example (JavaScript Prisma Query):

const employees = await prisma.employee.findMany({

where: { department: 'IT' },

select: { name: true, email: true },

});

MongoDB

NoSQL databases use flexible schemas; ___ is a popular example that uses a JSON-like query language.

Example:

db.employees.find({ department: 'IT' }, { name: 1, email: 1 });

• Extracting relevant data

• Filtering unnecessary data

Query languages are vital in managing databases, and with their diverse features, they empower users to fulfill multiple data manipulation use cases. Some common scenarios include: (2)

Extracting relevant data

Use query languages to retrieve specific data from a large database quickly, avoiding manual extraction.

Filtering unnecessary data

With declarative query languages like SQL's SELECT statement, filter out unnecessary data and provide a more efficient way to interact with databases.

sorting

Query languages like SPARQL enable the ___ of retrieved data by specific fields, making it easier to visualize and analyze results.

Updating existing data

Use UPDATE in SQL or equivalent commands in other query languages to alter existing data within databases without having to manually modify each record.

Aggregating data

Query languages often incorporate aggregation functions like SUM, COUNT, AVERAGE, and MAX to summarize and analyze data within databases.

Managing data integrity

With ACID properties in SQL or similar capabilities in other query languages, ensure the integrity of database transactions.

Web development

Query languages like SQL and MongoDB's MQL are integral in web development, enabling seamless data access for applications.

Business Intelligence (BI)

BI tools allow users to query databases directly using familiar declarative query language commands to analyze trends or insights from large datasets.

Data warehousing

Efficiently manage and transform huge amounts of data with the help of query languages, enabling data warehousing and business intelligence solutions.

Database administration

Query languages empower database administrators with the tools necessary to manage, maintain, and optimize databases for different use cases within an organization

GraphQL

SPARQL

SQL

Oracle PL/SQL

Prisma

NoSQL

Common Query Languages (GSSOPN)