Unit 8.3 Database Management Systems & SQL (DDL &DML)

Database Management Systems (DBMS)

Software that provides users with an interface for managing databases, allowing interaction through tools for creating, retrieving, updating, and deleting data.

Structured Query Language (SQL)

An industry-standard programming language used to manage and interact with databases.

Data Definition Language (DDL)

A subset of SQL used to create and modify database objects.

Data Manipulation Language (DML)

A subset of SQL used to add, retrieve, and update data in a database.

Integrity (DBMS Pro)

The ability of a database structure to change while keeping the applications using the data unchanged.

Efficiency (DBMS Pro)

The reduction of data duplication and inconsistency, leading to less storage space usage as data is shared.

Consistency (DBMS Pro)

The assurance that data remains the same regardless of who views it or when it is viewed.

Automatic Backups (DBMS Pro)

The capability of a DBMS to automatically back up data to local and remote storage while maintaining data integrity.

Security (DBMS Pro)

The feature of a DBMS that keeps data in one central, secure location.

Access Rights for Users/Groups (DBMS Pro)

The ability to assign varying access levels to users, determining what data they can access, read, write, execute, delete, or append.

Customization (DBMS Pro)

The ability to tailor applications within a DBMS to meet user needs.

Data Modelling (DBMS Pro)

The process of customizing access rights and separating applications from data, allowing different users to view only the data they need.

CHARACTER(n)

A data type in SQL that stores a specific number of characters, which must be defined when setting up a table.

VARCHAR(n)

A data type in SQL that stores a variable number of characters, which must be defined when setting up a table.

BOOLEAN

A data type in SQL that stores Boolean values, either True or False.

INTEGER

A data type in SQL that stores whole numbers without decimal places.

REAL

A data type in SQL that stores numbers with decimal places.

DATE

A data type in SQL that stores date values without a time component.

TIME

A data type in SQL that stores time values without a date component.

CREATE DATABASE

A command in DDL used to create a new database.

CREATE TABLE

A command in DDL used to create a new database object.

DEFINING FOREIGN KEY

A command in DDL that creates a database object with a Foreign Key

ALTER

A command in DDL used to modify the structure of a database object.

SELECT

A DML command that retrieves data from a specified table and fields, a shorthand for all fields is *.

FROM

A command in DML that specifies the table name from which to retrieve data.

WHERE

A command in DML that filters data based on specified conditions.

LIKE

A command in DML used to find information that begins with a certain character, must have quotation marks.

AND

A command in DML used to set multiple conditions for data retrieval.

OR

A command in DML used to retrieve information based on one set of conditions or a different one.

% wildcard

A symbol used to represent characters in a string search.

ORDER BY

A command in DML that sorts results from one or more fields in ascending or descending order.

GROUP BY

A command in DML that groups rows with the same values together.

INNER JOIN

A command in DML that combines rows from two or more tables based on related columns, such as a foreign key.

AVG

A command in DML that calculates the average of a set of values.

SUM

A command in DML that calculates the total of values in a column.

COUNT

A command in DML that counts the number of rows in a table.

INSERT INTO

A command in DML used to add new information into a table, brackets, quotation marks and commas needed.

UPDATE

A command in DML used to modify existing data in a table, quotation marks needed.

DELETE FROM

A command in DML used to remove data from tables.