Database and SQL Fundamentals

Flashcards covering fundamental database concepts and SQL.

Database

A set of ordered related data accessed by a computer system.

Database Management System (DBMS)

Provides the services necessary for end users or other software to interact with the data in a database.

Functionality Provided by DBMS

The ability to read, write, update, and delete data; structure data; set permissions; manage physical storage; and prevent database damage.

MySQL

A DBMS for relational databases where data is stored in related rows and columns within tables.

SQL (Structured Query Language)

A special type of programming language used to interact with relational databases.

Database Engine

Core functions of a database system, such as displaying, erasing, or adding data, and changing the database structure.

Non-Relational Databases

Designed to store documents or other types of unordered data.

Declarative Language

A language where a programmer states the logic of what they want the end result to look like, leaving the control flow up to the computer.

SELECT Clause

Tells the database what data to select (e.g., everything or specific columns).

FROM Clause

Indicates the table from which to select data.

WHERE Clause

Filters the results of the select query based on specified criteria.

ORDER BY Clause

Describes how the data should be displayed, such as sorting by a specific column.

UPDATE Statement

Used to change the data in the database.

INSERT Statement

Used to add data to the database.

CREATE TABLE Statement

Adds new tables to the database.

ALTER TABLE Statement

Used to change existing tables.

TRUNCATE Statement

Used to empty tables of their contents while preserving their structure.

DROP Statement

Used to remove tables entirely.

Advantages of Databases over Spreadsheets and Flat Files

Flexibility, scalability, and integrity.

Relational Databases

Organizing data into rows and columns within tables.

Table

A collection of closely related data organized in rows and columns.

Column

Defines the data present in each row.

Row

Contains data that all refer to the same entity in the database.

Value

The intersection of each row and column.

Primary Key

A unique identifier for each row in a table.

Foreign Key

A key used to reference a specific row in another table.

SQL Statement

Any valid command that complies with SQL syntax, ends with a semicolon, and interacts with a database in some way.

CRUD Statements

Create, read, update, and delete.

CRUD Statements in MySQL

Insert, select, update, and delete statements in MySQL.

Query

A type of statement that uses SELECT and returns a set of rows.

Clause

The building blocks of an SQL statement (e.g., SELECT * FROM movies WHERE release_year > 2010).

Predicate

Filters the results within a WHERE clause based on user-defined criteria.

Joining Tables

Joining two tables based on where the data in those two tables overlaps.

Normalization

The practice of designing a database to minimize data redundancy and maximize data integrity.