Mastering SQL: A Beginner's Guide

CHAPTER 1 ◾ Basics about SQL

In This Chapter

Relational Database Management System (RDBMS)

Keys in SQL

What Is Structured Query Language (SQL)?

Origins of Structured Query Language (SQL)

Purpose of Structured Query Language (SQL)

Installations of SQL

Operations in Structured Query Language (SQL)

What Are the Different Types of Functions?

Aggregated Functions

Scalar Functions

Characteristics of Structured Query Language (SQL)

Foundational Relationships

High Performance

Scalability

Authentication and Security

Independent Vendors

Adaptability to a Variety of Computer Systems

Endorsement and Commitment from IBM (DB2)

Structure Similar to That of English

Database Access through Programming

Transaction Control Language

Various Viewpoints on Data

Dynamic

Architecture of Client/Server

SQL with Java Integration

Some SQL Statements

Advantages of Structured Query Language (SQL)

Disadvantages of Structured Query Language (SQL)

Data Types in Structured Query Language (SQL)

Exact Numeric Data Types

Types of Numeric Data

Types of Date and Time Data

Data Types for Character Strings

Character String Data Types in Unicode

Types of Binary String Data

Other Data Types

Commands of Structured Query Language (SQL)

Data Definition Language (DDL)

Make a Database

Make a Table

Add to Table

Drop a Table

Data Manipulation Language (DML)

Data Control Language (DCL)

Transition Control Language (TCL)

Transactions in SQL

Characteristics of Transactional

Commands for Controlling Transactions

Commands for Transactional Control

Set Transaction

Views in SQL

Views Are Being Removed

Views Are Being Updated

Delete a Row from a View

With Check Option

Views and Their Applications

SQL Comments

Constraints in SQL

SQL Creating Role

Making a Role and Assigning It

SQL Indexes

Unique Indexes

Clustered Index

Non-Clustered Indexes

When Should You Construct Indexes?

When Indexes Should Be Avoided

Drop Index

Altering Index

Confirming Indexes

Renaming an Index

Sequences in SQL

Query Processing in SQL

Common Table ExpressionS (CTE) in SQL

Defining CTEs

Creating a Common Table Expression (Recursive)

Types of Common Table Expressions

Triggers in SQL

Before and After Triggers

Book Management Database in SQL Trigger

Introduction to NoSQL (Non-Relational SQL)

A Brief History of NoSQL Databases

Types of NoSQL Databases

Key-Value

Column-Based

Document-Oriented

Graph-Based

Features of NoSQL

Non-Relational Database Management System (NoSQL) Features

Schema-Free

API That Is Easy to Use

Distributed

NoSQL Query Mechanism Tools

What Is the CAP Theorem, and How Does It Work?

Consistency

Availability

Tolerance for Partitions

Consistency in the Long Run

When Should You Utilise NoSQL?

Advantages

Disadvantages

Summary

Note

CHAPTER 2 ◾ Clauses/Operators

In This Chapter

With Clause in SQL

With Ties Clause in SQL

Arithmetic Operators in SQL

Addition Operator (+)

Subtraction Operator (–)

Operator for Multiplication (*)

Division Operator (/)

Modulus Operator(%)

Wildcard in SQL: An Overview

SQL Wildcards Syntax

Except and Intersect Operators

Except Clause

Using Clause

Knowing How to Use the SQL Merge Statement

SQL Merge Command and its Applications

Improving the SQL Merge Statement’s Performance

Merge Statement in SQL Explained

DDL, DML, DCL and TCL Commands

Create Domain in SQL

Create a New Domain

Describe Statement

Case Statement in SQL

Syntax of SQL Case Statements

Unique Constraints in SQL

Contrasting the Primary Key and Unique Constraints

Unique Constraints for a Group of Columns

Add Unique Constraints to Existing Columns

The Alter Table Drop Constraint Statement

Create Table Extension

Rename in SQL

Add, Drop, Modify

Table Change – Add

Change Table – Drop

Modify the Table

Limit Clause

A Limit Clause: What Is It?

What are the Definition, Syntax, and Parameter Values of a Select Limit Statement?

Parameters or Arguments

Using the Limit Keyword

When Should the Limit Clause Be Used?

The Limit Clause’s Benefits

Insert Ignore Statement

How Does MySQL’s Insert Ignore Function Work?

Drawback

Like Operator

Some SQL Operator

Offset Fetch in SQL Server

Application of Offset and Fetch Offset

Fetch and Offset

Offset Only

Fetch Only

SQL Statement Except

Prerequisites for the SQL Except Statement

Using Joins and the Over Clause in SQL to Combine Aggregate and Non-Aggregate Values

Utilising Joins

Over Clause

Operators for SQL Any and ALL

ANY Operator in SQL

SQL Operator ALL

Exists in SQL

Group by Statement in SQL

Union Clause

Union Versus Union All

In SQL, An Example of the Union Operator

Where Clause Is Combined with the Union Operator

Union All Operator in SQL

Example of Union All

SQL in Aliases

Order by Clause in SQL

Select Top Clause in SQL

Top Clause Syntax in SQL Server

SQL Select the Highest Percentage of Records to Return

Multiple Select Top Statements

SQL Update Command

Delete Statement in SQL

Insert into SQL Statement

AND and OR SQL Operators

AND Operator

Combining AND and OR

Clause Where

Unique Clause in SQL

Select in SQL Statement

Where Clause in Select Statement

Group by Clause in SQL Select Statement

Select Statement with Group by Clause Example

Having Clause in SQL Select Statement

Order by Clause in Select Statement

Drop and Truncate Table in SQL

Truncate Table in SQL

Table Drop in SQL

Create in SQL

Make a Database

Table Creation

Joins in SQL

Alternate Quote Operator

Operator for Concatenation

Operator Minus

Division Operator

The NOT Operator in SQL

Between and In Operator

Between Operator

The SQL Syntax

In Operator

Join (Inner, Left, Right and Full Joins)

Different Types of Joins

How to Determine Which SQL Join to Use

Inner Joining

Full Joining

Join on the Left

Right Joining

SQL Constraint Check

Summary

CHAPTER 3 ◾ SQL Injections

In This Chapter

What is SQL Injection (SQLi)?

Goals of SQLi

Mechanism of SQL Injection Attack

SQL Injection Types

Detection and Prevention of SQL Injection Attacks

Simple SQLi Example

Sqlmap: Test a Website SQL Injection Vulnerability

Sqlmap

Features of Sqlmap

How to Download

Where Sqlmap May Be Used

Damn Vulnerable Web Application (DVWA)

Determine the Database Management System (DBMS) in the Site

Listing of Tables in a Database

Getting Rid of a Table

Mitigating the SQL Injection Attack with Prepared Declarations

Prepared Statements

Mechanism of Action of Prepared Statements

Terminology

What is the Benefit of Utilising a Prepared Statement in Java?

What about Sanitization of the Input?

Summary

Notes

CHAPTER 4 ◾ SQL Functions

In This Chapter

SQL Functions

Aggregate Functions

Analytic Functions

Scalar SQL Functions

SQL Server Mathematical Functions

Conversion Function

Explicit vs. Implicit

Conversion of Implicit Data Types

SQL Conversion of Explicit Data Types Conversion

Using the TO CHAR Function with Dates

Using the TO CHAR Function with Numbers

The to Number and to Date Functions

General Functions in SQL

NVL()

NVL2 Function

DECODE()

COALESCE()

NULLIF()

LVL()

Conditional Statements in SQL

Case Statement in SQL

IF Proclamation in SQL

Character Functions

Case-Manipulative Functions

Character-Manipulative Functions

Listing Function

The On Overflow Statement

Distinct

Combining Listing with Filter and Over

Compatibility

Arrays

Document Types

Using with Recursive

Proprietary Extensions

Proprietary Alternatives

Aggregate Function in SQL

Date Functions

Null Values in SQL

Why Are Null Functions Necessary?

How Can Null Values Be Tested?

Is Null Syntax

Is Not Null Syntax

Numeric Functions

String Functions

Deterministic and Nondeterministic

Built-in Function Determinism

Summary

Note

BIBLIOGRAPHY

INDEX