Data Analysis Week 3 Notes

Structured Query Language (SQL) is the standard language used to interact with relational databases.

• Mainly used to manage data. Whether you want to create, delete, update or read data, SQL provides the structure and commands to perform these operations.
• Widely supported across various database systems like MySQL, Oracle, PostgreSQL, SQL Server and many others.
• Mainly works with Relational Databases (data is stored in the form of tables)

After your MySQL environment is set up, you can write your SQL program. Below is the example to display " Hello World" using SQL.

1. Create a database named test_db

CREATE DATABASE test_db;

1. Use the test_db database

USE test_db;

1. Create a table named greetings

CREATE TABLE greetings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message VARCHAR(255)
);

1. Insert the message 'Hello, World!' into the greetings table

INSERT INTO greetings (message)
VALUES ('Hello, World!');

1. Retrieve the message from the greetings table

SELECT message FROM greetings;

Why Learn SQL?
SQL's integration with various technologies makes it essential for managing and querying data in databases. Whether it is in traditional relational databases (RDBMS) or modern technologies such as machine learning, AI and blockchain, SQL plays a key role. It works effortlessly with DBMS to help users interact with data, whether stored in structured RDBMS or other types of databases.

Data Science & Analytics: Used for querying large datasets, data cleaning and analysis. Analysts use SQL to generate reports and insights that inform business decisions.
Machine Learning & AI: Helps in preparing and managing the data required for training machine learning models and AI algorithms. It is used for data cleaning, transformation, and extraction.
Web Development: Used to manage user data, e-commerce transactions, and content management in websites and applications built with frameworks like Django, Node.js, and Ruby on Rails.
Cloud and Big Data: SQL is integrated into cloud-based databases (e.g., Amazon RDS, Microsoft Azure SQL) and Big Data platforms (e.g., Apache Hive) to enable seamless data querying and management.
Blockchain and Decentralized Systems: In blockchain systems, SQL can be used to manage off-chain data, providing efficient data storage and retrieval alongside decentralized ledger technology

SQL
Structured Query Language (SQL) is the standard language used to interact with relational databases.

Allows users to store, retrieve, update, and manage data efficiently through simple commands.
Known for its user-friendly syntax and powerful capabilities, SQL is widely used across industries.

How Does SQL Work?
We interact with databases using SQL queries.
SQL Engine breaks down, optimizes and executes these queries efficiently
DBMS tools like MySQL and SQL Server have their own SQL engine and an interface where users can write and execute SQL queries..

Key Components of a SQL System
Databases : A database is a structured collection of data. It organizes data into tables, which are like spreadsheets with rows (records) and columns (fields) .
Tables: Each table enforces rules and relationships among its columns for data integrity.
Indexes: Indexes speed up queries by allowing the database to quickly locate data without scanning the entire table.
Views: A view is a virtual table—basically a saved SELECT statement you can query like a table.
Stored Procedures: These are pre-written SQL scripts stored inside the database. They can receive inputs, run complex logic, and return results boosting performance, reusability, and security.
Transactions: A transaction groups multiple SQL operations into a single unit. It ensures all changes are applied successfully or none are, preserving data integrity (ACID properties)
Security & Permissions: SQL includes tools to restrict access, letting DBAs assign who can do what whether it's accessing tables, executing procedures, or changing structures.
Joins: Joins combine data from multiple tables based on relationships essential for querying across related datasets.

Rules for Writing SQL Queries
There are certain rules for SQL which would ensure consistency and functionality across databases. By following these rules, queries will be well formed and well executed in any database.

End with Semicolon (;): Each SQL statement must end with a semicolon to execute properly.
Case Insensitivity: SQL keywords (e.g., SELECT, INSERT) are not case-sensitive. However, table/column names may be case-sensitive depending on the DBMS.
Whitespace Allowed: Queries can span multiple lines, but use spaces between keywords and names.
Reserved Words: Avoid using SQL keywords as names. If needed, wrap them in quotes (" ") or backticks (`).

What are Different SQL Commands or Queries?
Structured Query Language (SQL) commands are standardized instructions used by developers to interact with data stored in relational databases. These commands allow for the creation, manipulation, retrieval and control of data, as well as database structures. SQL commands are categorized based on their specific functionalities:

SQL Data Types

In SQL, each column must be assigned a data type that defines the kind of data it can store, such as integers, dates, text, or binary values. Choosing the correct data type is crucial for data integrity, query performance and efficient indexing.

Benefits of using the right data type:

Memory-efficient storage
Accurate operations (e.g., calculations, sorting)
Consistency in stored values
Validation of input data
SQL data types are broadly categorized into several groups

1. Numeric Data Types
Numeric data types are fundamental to database design and are used to store numbers, whether they are integers, decimals or floating-point numbers. These data types allow for mathematical operations like addition, subtraction, multiplication and division, which makes them essential for managing financial, scientific and analytical data.

Data Type Description Range
BIGINT Large integer numbers -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
INT Standard integer values -2,147,483,648 to 2,147,483,647
SMALLINT Small integers -32,768 to 32,767
TINYINT Very small integers 0 to 255
DECIMAL Exact fixed-point numbers (e.g., for financial values) -10^38 + 1 to 10^38 - 1
NUMERIC Similar to DECIMAL, used for precision data -10^38 + 1 to 10^38 - 1
MONEY For storing monetary values -922,337,203,685,477.5808 to 922,337,203,685,477.5807
SMALLMONEY Smaller monetary values -214,748.3648 to 214,748.3647

Approximate Numeric Datatype
These types are used to store approximate values, such as scientific measurements or large ranges of data that don't need exact precision.

Data Type Description Range
FLOAT Approximate numeric values -1.79E+308 to 1.79E+308
REAL Similar to FLOAT, but with less precision -3.40E+38 to 3.40E+38

2. Character and String Data Types
Character data types are used to store text or character-based data. The choice between fixed-length and variable-length data types depends on the nature of your data.

Data Type Description
Char

The maximum length of 8000 characters. (Fixed-Length non-Unicode Characters)

Varchar

The maximum length of 8000 characters. (Variable-Length non-Unicode Characters)

*Varchar(max)
*
The maximum length of 2^31 - 1 characters(SQL Server 2005 only). (Variable Length non-Unicode data)

Text

The maximum length of 2,127,483,647 characters(Variable Length non-Unicode data)

Unicode Character String Data Types
Unicode data types are used to store characters from any language, supporting a wider variety of characters. These are given in below table.

Data Type

Description

Nchar

The maximum length of 4000 characters(Fixed-Length Unicode Characters)

Nvarchar

The maximum length of 4000 characters.(Variable-Length Unicode Characters)

Nvarchar(max)

The maximum length of 2^31 - 1 characters(SQL Server 2005 only). (Variable Length Unicode data)

3. Date and Time Data Type
SQL provides several data types for storing date and time information. They are essential for managing timestamps, events and time-based queries. These are given in the below table.

Data Type Description
Storage Size

DATE

stores the data of date (year, month, day)

3 Bytes

TIME

stores the data of time (hour, minute,second)

3 Bytes

DATETIME

store both the data and time (year, month, day, hour, minute, second)

8 Bytes

4. Binary Data Types in SQL
Binary data types are used to store binary data such as images, videos or other file types. These include:

Data Type Description Max Length
Binary Fixed-length binary data. 8000 bytes
VarBinary Variable-length binary data. 8000 bytes
Image Stores binary data as images. 2,147,483,647 bytes
5. Boolean Data Type in SQL
The BOOLEAN data types are used to store logical values, typically TRUE or FALSE. It is commonly used for flag fields or binary conditions.

1. Special Data Types SQL also supports some specialized data types for advanced use cases:

XML Data Type: Used to store XML data and manipulate XML structures in the database
Spatial Data Type (Geometry): stores planar spatial data, such as points, lines, and polygons, in a database table.

SQL Operators

Types of SQL Operators
SQL operators can be categorized based on the type of operation they perform. Here are the primary types of SQL operators:

Arithmetic Operator
Comparison Operator
Logical Operator
Bitwise Operators
Compound Operators
Special Operators

SQL Arithmetic Operators
Arithmetic operators in SQL are used to perform mathematical operations on numeric data types in SQL queries. Some common arithmetic operators:

Example: Arithmetic Operations
In this example, we calculates a 5% increment on employee salaries and returns both the original and updated salary values.

Query:

SELECT emp_salary, emp_salary * 1.05 AS "Revised Salary" FROM employee;

SQL Comparison Operators
Comparison Operators in SQL are used to compare one expression's value to other expressions. SQL supports different types of comparison operator,

SELECT * FROM MATHS WHERE MARKS=50;

SQL Logical Operators
Logical Operators in SQL are used to combine or manipulate conditions in SQL queries to retrieve or manipulate data based on specified criteria..

Operator Description
AND
Logical AND compares two Booleans as expressions and returns true when both expressions are true.

OR

Logical OR compares two Booleans as expressions and returns true when one of the expressions is true.

NOT
Not takes a single Boolean as an argument and change its value from false to true or from true to false.

SELECT * FROM employee
WHERE emp_city = 'Allahabad' AND emp_country = 'India';

SQL Bitwise Operators
Bitwise operators in SQL are used to perform bitwise operations on binary values in SQL queries, manipulating individual bits to perform logical operations at the bit level. Some SQL Bitwise Operators are:

Operator

Description

&

Bitwise AND operator

Bitwise OR operator

^

Bitwise XOR (exclusive OR) operator

~

Bitwise NOT (complement) operator

<<

Left shift operator

Right shift operator

SQL Compound Operators
Compound operators combine an operation with assignment. These operators modify the value of a column and store the result in the same column in a single step. Some Compound operators are:

Operator

Description

+=

Add and assign

-=

Subtract and assign

*=

Multiply and assign

/=

Divide and assign

%=

Modulo and assign

&=

Bitwise AND and assign

^=

Bitwise XOR and assign

|=

Bitwise OR and assign

SQL Special Operators
SQL also provides several special operators that serve specific functions such as filtering data based on a range, checking for existence, and comparing sets of values.

Operators Description
ALL

ALL is used to select all records of a SELECT STATEMENT. It compares a value to every value in a list of results from a query. The ALL must be preceded by the comparison operators and evaluated to TRUE if the query returns no rows.

ANY
ANY compares a value to each value in a list of results from a query and evaluates to true if the result of an inner query contains at least one row.

BETWEEN
The SQL BETWEEN operator tests an expression against a range. The range consists of a beginning, followed by an AND keyword and an end expression.

IN

The IN operator checks a value within a set of values separated by commas and retrieves the rows from the table that match.

EXISTS

The EXISTS checks the existence of a result of a subquery. The EXISTS subquery tests whether a subquery fetches at least one row. When no data is returned then this operator returns 'FALSE'.

SOME SOME operator evaluates the condition between the outer and inner tables and evaluates to true if the final result returns any one row. If not, then it evaluates to false.
UNIQUE The UNIQUE operator searches every unique row of a specified table.