Lesson 12 - Fundamentals of Database Systems: Data Manipulation Commands

Data Manipulation Language (DML)

Commands for manipulating data in databases.

Structured Query Language (SQL)

Standard language for managing relational databases.

Database Management System (DBMS)

Software for creating and managing databases.

Relational Database

Database structured into related tables.

CREATE DATABASE

SQL command to create a new database.

USE statement

Selects a database for subsequent commands.

CREATE TABLE

SQL command to create a new table.

Constraints

Rules defining data types in a column.

CHAR

Fixed-length string data type.

VARCHAR

Variable-length string data type.

INT

Integer data type for whole numbers.

DECIMAL

Data type for precise decimal numbers.

DATE

Data type for date values in YYYY-MM-DD.

DATETIME

Data type for date and time values.

TIMESTAMP

Data type for precise timestamp data.

TEXT

Data type for large text strings.

Rules of Precedence

Guidelines for evaluating operators in SQL.

Alias

Temporary name for a database table or column.

Task Optimizer

Analyzes execution plans for SQL queries.

Parser

Translates SQL statements into a parse tree.

Execution Plan

Algorithm for executing an SQL command.

MySQL

Popular open-source DBMS for relational databases.

SQL Server Express

Free version of Microsoft's SQL Server.

Oracle

Commercial DBMS known for enterprise applications.

Database Security

Measures to protect databases from unauthorized access.

Data Consistency

Ensures accuracy and reliability of data across users.

UNIQUE constraint

Ensures unique values in a column.

PRIMARY KEY constraint

Marks a column as a unique identifier.

DEFAULT constraint

Sets a default value for a column.

AUTO_INCREMENT

Automatically assigns incrementing values to a column.

CHECK constraint

Restricts values that can be entered in a column.

NOT NULL constraint

Excludes null values from a column.

identity(1,1)

MS SQL Server equivalent of AUTO_INCREMENT.

CREATE TABLE statement

Defines a new table structure in SQL.

IF NOT EXISTS

Prevents error if table already exists.

INSERT INTO statement

Adds new rows to a specified table.

VALUES clause

Specifies data to insert into columns.

NULL value

Represents missing or undefined data.

CHAR data type

Fixed-length character string type.

VARCHAR data type

Variable-length character string type.

TRY...CATCH construct

Handles errors during SQL execution.

Unique Key Violation

Inserting duplicate key value causes error.

Primary Key Violation

Inserting duplicate primary key causes error.

Foreign Key Violation

Inserting child row without parent causes error.

Data Type Conversion error

Value cannot be converted to column type.

SET ARITHABORT

Controls behavior of arithmetic errors.

NEWID() function

Generates a globally unique identifier.

Identity Column Property

Automatically increments value for new rows.

INSERT SELECT statement

Inserts data from one table to another.

INSERT multiple rows

Adds several rows in one statement.

Data Type Considerations

Padding or truncation affects CHAR and VARCHAR.

NULL handling

Specifying NULL allows absence of data.

Column order in INSERT

Columns can be specified in any order.

Error handling in INSERT

Errors prevent any rows from being inserted.

INSERT statement behavior

All or nothing; no partial inserts allowed.

INSERT statement

Used to add new rows to a table.

IDENTITY column

Column with auto-incrementing numeric values.

SET IDENTITY_INSERT

Allows explicit values for identity columns.

Default Values

Values automatically assigned by the DBMS.

NULL-able column

Column that can accept NULL values.

INSERT INTO SELECT

Inserts rows from one table to another.

Common Table Expression (CTE)

Temporary result set for complex queries.

SELECT clause

Fetches data from specified columns in tables.

WHERE clause

Filters records based on specified conditions.

Comparison Operators

Operators for comparing values in queries.

AND operator

Combines conditions, requiring both to match.

OR operator

Combines conditions, requiring at least one to match.

IN operator

Selects values that match a specified list.

BETWEEN operator

Selects values within a specified range.

ORDER BY clause

Organizes query results in a specified order.

LIMIT clause

Restricts the number of results returned.

TOP clause

Limits results in SQL Server queries.

UPDATE statement

Modifies existing records in a table.

INSERT SELECT syntax

Combines INSERT and SELECT for data insertion.

DBMS

Database Management System handling data operations.

INNER JOIN

Combines rows from two tables based on a condition.

Development copy

Test version of a database for development purposes.

Syntax

Rules governing the structure of SQL statements.

Fetch

Retrieve data from a database.

Column list

Specifies which columns to include in queries.

Condition

Criteria used to filter query results.

Value

Data assigned to a column in a database.

UPDATE Statement

Used to modify existing records in a table.

SET Clause

Identifies columns to be updated in SQL.

WHERE Clause

Filters records affected by SQL statements.

INSERT Statement

Adds new records to a specified table.

TRY...CATCH Construct

Handles errors during SQL statement execution.

Data Type Conversion Error

Happens when values can't convert to target type.

Exclusive Lock

Prevents other transactions from modifying data.

DELETE Statement

Removes one or more rows from a table.

Basic Structure of DELETE

Includes table name and criteria for removal.

Transaction

A sequence of operations treated as a single unit.

ROLLBACK

Reverts changes made during a transaction.

Primary Key

Uniquely identifies each record in a table.

High Performing Sales People

Salespeople with sales greater than $2,000,000.

Sample Update Example

Updates city for all salespeople to Ann Arbor.

Updating Multiple Columns

Allows changing several columns in one statement.

Updating Multiple Rows

Affected by conditions specified in the WHERE clause.

Deleting Every Row

Removes all records when no WHERE clause is used.

Deleting a Single Row

Targets a specific row using its primary key.