Databases and SQL basics

Basics

• Data: Unprocessed set of inputs, a collection of unorganized information from various sources and the starting point for retrieving meaningful information.
• Information: Data retrieved by analyzing and interpreting raw data.
• Analogy: data resembles raw kitchen ingredients, while the final dish represents information derived from those ingredients.
• Database: Organized set of data that's easy to access, manage, and retrieve.
• DBMS (Database Management System): System software enabling users to interact with a database for storing, retrieving, and manipulating data.
• MySQL: Database management system software using SQL language and known for being free, open-source, fast, and reliable.
• Semicolon (;): Used to separate SQL statements.
• SQL: Standard programming language for managing databases, invented in the 1970s.

SQL Commands

• DDL (Data Definition Language): Used to define and modify the database structure, includes commands such as Alter, Create, Drop, Truncate, and Rename.
• DML (Data Manipulation Language): Used to manipulate data in a database, includes commands such as Insert, Delete, Update, and Select.
• TCL (Transaction Control Language): Used to control transaction execution, including commands like Savepoint, Rollback, and Commit.
• DCL (Data Control Language): Used to manage database security and permissions, including commands Grant and Revoke.

Basic Terminology

• Table: Basic building block of a database, containing rows (tuples or records) and columns (fields or attributes).
• Queries: SQL commands that perform operations.
• Constraint: Rules applied to a table to ensure data integrity by filtering data that meets specific conditions.

Characteristics of SQL

• SQL has efficient database management.
• SQL is easy to retrieve data from.
• SQL is known for being easy to use..
• SQL is readily accessible.

Benefits of SQL

• SQL can handle complex queries.
• SQL can easily retrieve data.
• SQL is easy to use and simple to understand.
• SQL is flexible.
• SQL is scalable for both small and large-scale operations.

Limitations of SQL

• SQL has complexity in advanced operations.
• SQL presents platform-specific variations.

SQL Use-Cases

• SQL can be used within healthcare.
• SQL can be used within e-commerce.
• SQL can be used within banking.

SQL Data Types

• SQL data types ensure data is stored efficiently and accurately in relational databases.
• SQL data types prevent invalid data entry.
• SQL data types improve query optimization and search speed.
• SQL data types reduce storage costs by allocating necessary space for data.

Types of SQL Data Types

• Numeric.
• Character & String.
• Time & Date.
• Boolean.
• Binary.
• Special.

Numeric Data Type

• Int: Range of plus/minus 2^31, takes 4 bytes of storage.
• BigInt: Range of plus/minus 2^63, takes 8 bytes of storage.
• SmallInt: Range of plus/minus 2^15, takes 2 bytes of storage.
• TinyInt: Range of 0 to 255, takes 1 byte of storage.
• Decimal: Range of plus/minus 2^38.
• Numeric: Range of plus/minus 2^38.
• Boolean: Range of 0 to 1, takes 1 byte of storage.
• Money: Range up to 922 Trillion.
• SmallMoney: Range up to 2 billion.
• Float: Range of plus/minus 1.79E+308.
• Real: Range of plus/minus 3.40E+38.

Character & String Data Type

• Char: Maximum length of 8000 characters, non-unicoded data.
• Varchar: Maximum length of 8000 characters, non-unicoded data.
• Text: 2 billion characters.
• Nchar: Maximum length of 4000 characters, unicoded data.
• Nvarchar: Maximum length of 4000 characters, unicoded data.

Time & Data Data Type

• Time: Stores time values.
• Date: Stores date values.
• DateTime: Stores both date and time values.
• Timestamp: Stores current time.

Boolean Data Type

• Stores logical values of true or false.

Binary Data Type

• Binary: 8000 bytes length.
• varBinary: 8000 bytes length.
• Image: 2 billion bytes of data.

Special Data Type

• XML: Stores data in XML format.

SQL Operators

• SQL is important in database management systems, allowing manipulation and efficient data retrieval.
• Types of SQL Operators: (ABCCLS).
• Arithmetic Operator (+,-,/,%,*).
• Bitwise Operator (&,|,^,~,>>,<<).
• Compound Operator (+=,-=,/=,*=,%=,&=,|=,^=).
• Comparison Operator (>,<,<=,>=,==).
• Logical Operator (AND, OR, NOT).
• Special Operator (IN, BETWEEN, ALL, ANY, EXISTS).

Queries Related to Databases in SQL

• Create Database: Syntax: Create database db_name; e.g., create database MyCompany;
• Show List of Databases: Syntax: show databases;
• Selecting a Database: Syntax: Select database db_name; e.g., select database MyCompany;
• Drop Database: Syntax: Drop Database db_name; e.g., drop database MyCompany; Use IF EXISTS to avoid errors while dropping.
• Change Name of Database: Syntax: Alter database old_db_name modify name='new_db_name'; e.g., alter database MyCompany modify name='Spice Heaven';