Lesson 11 - Understanding Structured Query Language in Databases

Structured Query Language (SQL)

A programming language used for managing and manipulating databases.

Data Definition Language (DDL)

SQL commands used to create database objects such as tables, indexes, and views.

Data Manipulation Language (DML)

SQL commands used to insert, update, delete, and retrieve data within database tables.

Data Control Language

The SQL dialect used to secure the database.

Transaction Control Language

Dialect used to manage and control transactions to maintain data integrity.

Schema

A group of database objects—such as tables and indexes—that are related to each other.

Data Type

Defines the characteristics of the data that can be stored in a database column.

INT

Integer data type that stores whole numbers.

VARCHAR

Variable character field

A set of character data of indeterminate length. Variable-length character data type used for storing text.

NVARCHAR

National varying character (NVARCHAR)

Variable-length Unicode character data type used for storing text, may hold up to 4,000 Unicode characters of data

DATETIME

Data type used for storing date and time values.

DECIMAL

Data type used for storing exact numeric values with a fixed number of decimal places.

FLOAT

Data type used for storing approximate numeric values with floating decimal points.

BIT

Data type used for storing binary values, typically 0 or 1.

Data Integrity

The assurance of accuracy and consistency of data within a database.

SQL Server Data Types

Defined types in SQL that determine the kind of data that can be stored in a column.

Sample Table

An example of how data is structured in a database, showing different values for a column.

Consistent Format

A uniform way of storing data that allows for easier reading, sorting, and calculations.

Storage Space

The amount of memory required to store data in a database.

Performance

The efficiency of data retrieval and manipulation operations in a database.

Whole Numbers

Numbers that do not include decimal places, such as -23, 0, 5, and 10045.

Attributes

Characteristics or properties of an entity, such as name, birthdate, weight, and number of children.

Database Management System (DBMS)

Software that interacts with end users, applications, and the database itself to capture and analyze data.

SQL Commands

Instructions used in SQL to perform tasks such as creating, modifying, and querying data.

Data Types Importance

Data types are crucial for ensuring data is stored consistently, affects calculations, and impacts performance.

Big Data

Large and complex data sets that traditional data processing software cannot manage efficiently.

Inconsistent Format

Data that is not stored uniformly, making it difficult to read and analyze.

Database Objects

Elements that make up a database, including tables, views, indexes, and stored procedures.

INT Data Type

Stores integers from -2,147,483,648 to 2,147,483,647.

BIGINT Data Type

Stores larger integers, up to trillions.

CHAR Data Type

Fixed length character data type.

DATETIME Range

Valid from January 1, 1753 to December 31, 9999.

YEAR Function

Extracts year from DATETIME values.

DECIMAL Data Type

Precise values defined by precision and scale.

DECIMAL Precision

Total digits stored in DECIMAL.

DECIMAL Scale

Digits to the right of the decimal point.

FLOAT Data Type

Stores approximate numeric values from -1.79E+308 to 1.79E+308.

BIT Data Type

Stores Boolean values: 1 (TRUE), 0 (FALSE).

IDENTITY

Auto-incrementing unique value for new rows.

PRIMARY KEY

Unique identifier for table rows.

NULL

Column can accept no value.

NOT NULL

Column must have a value.

SQL Server

Microsoft's software for managing databases.

RDBMS - Relational Database Management System

Relational Database Management System.

A program used to create, update, and manage relational databases. Some of the most well-known RDBMSs include MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, and Oracle Database.

SQL Server Analysis Services (SSAS)

SQL Server Analysis Services

for data analysis. Is a tool primarily used by organizations to analyze and make sense of information otherwise spread out, whether over multiple databases or in different tables or files.

SQL Server Reporting Services (SSRS)

SQL Server Reporting Services

for report generation. Is a SQL Server subsystem that enables the creation of graphical, mobile and printed reports using SQL Server and other data sources.

SQL

Structured Query Language

for database management.

Database Creation

Use SQL Server to create databases.

Database Maintenance

Use SQL Server to maintain databases.

SQL Server Integration Services (SSIS)

can be used for extraction, loading, and transformation (ETL) of data by extracting data from multiple sources, such as SQL Server database, Oracle database, and Excel files.

SQL Server Versions

Includes versions 13 (2016), 14 (2017), 15 (2019).

Client-Server Architecture

Structure supporting workstation and server components.

Workstation Components

Installed on user devices for server interaction.

Server Components

Centralized services installed on the server.

SQL Server Instance

An installation of SQL Server software.

Default Instance

Single instance using the server name.

Named Instance

Multiple instances with unique names on one server.

Instance Limitations

16 instances in 2000, 50 in 2005 and later.

Enterprise Edition

Full feature set for large-scale applications.

Standard Edition

Fewer features than Enterprise for basic needs.

Web Edition

Optimized for web application hosting.

Developer Edition

Enterprise-like features for development and testing.

Express Edition

Free version limited to 1 CPU, 1 GB RAM.

Compact Edition

Free embedded database for mobile applications.

Datacenter Edition

No memory limits, supports over 25 instances.

Business Intelligence Edition

Includes advanced BI features, lacks some availability.

Enterprise Evaluation Edition

Fully functional for 6 months, free for learning.

Standalone Installation

Single server setup without clustering.

Cluster-Based Installation

Multiple servers working together for redundancy.

System Requirements

Requires 64-bit Windows 8 or later for Express.

Installation Process

Straightforward, involves pressing [Next] through screens.

License Terms

Agreement required before installation begins.

Global Rules Screen

Displays errors before proceeding with installation.

Feature Selection Screen

Choose components to install during setup.

Database Engine Configuration

Set server authentication and password.

T-SQL

Transact-SQL, Microsoft's extension for SQL.

SQL Server Management Studio

Graphical tool for managing SQL Server.

Create Login

Command to establish a new user account.

System Database

Databases created automatically by SQL Server.

Master Database

System database that tracks all SQL Server components.

Model Database

Template for creating new databases.

MSDB Database

System database for SQL Server Agent jobs, a system database that is used by several SQL Server components such as the SQL Server Agent service.

Tempdb

Temporary storage for SQL Server operations.

Backup

Copy of database data for recovery purposes.

Full Backup

Complete copy of the database at a point in time.

Differential Backup

Backup of changes since the last full backup.