A Comprehensive Study of SQL - Practice and Implementation is designed as a textbook and provides a comprehensive approach to SQL (Structured Query Language), the standard programming language for defining, organizing, and exploring data in relational databases. It demonstrates how to leverage the two most vital tools for data query and analysis – SQL and Excel – to perform comprehensive data analysis without the need for a sophisticated and expensive data mining tool or application.
Features
•The book provides a complete collection of modeling techniques, beginning with fundamentals and gradually progressing through increasingly complex real-world case studies.
•It explains how to build, populate, and administer high-performance databases and develop robust SQL-based applications.
•It also gives a solid foundation in best practices and relational theory.
•The book offers self-contained lessons on key SQL concepts or techniques at the end of each chapter using numerous illustrations and annotated examples.
This book is aimed primarily at advanced undergraduates and graduates with a background in computer science and information technology. Researchers and professionals will also find this book useful.
Author(s): Jagdish Chandra Patni
Publisher: CRC Press/Chapman & Hall
Year: 2022
Language: English
Pages: 233
City: Boca Raton
Cover
Half Title
Title Page
Copyright Page
Dedication
Table of Contents
Preface
Biography
Acknowledgement
Chapter 1: Database Systems
1.1 Introduction to Databases
1.1.1 Difference between a Database and a Spreadsheet?
1.1.2 Database Challenges
1.1.3 Advantages of DBMS
1.1.4 Disadvantages of DBMS
1.1.5 Types of Databases
1.1.5.1 Relational Databases
1.1.5.2 Object-Oriented Databases
1.1.5.3 Distributed Databases
1.1.5.4 Data Warehouses
1.1.5.5 NoSQL Databases
1.1.5.6 Graph Databases
1.1.5.7 On-Line Transaction Processing Databases
1.1.5.8 Open-Source Databases
1.1.5.9 Cloud Databases
1.1.5.10 Multimodel Database
1.1.5.11 Document/JSON Database
1.1.5.12 Self-Driving Databases
1.2 Database Models
1.2.1 Hierarchical Databases
1.2.2 Network Databases
1.2.3 Object-Oriented Model
1.2.4 Graph Databases Model
1.2.5 Entity–Relationship Model
1.2.6 Document Databases Model
1.2.7 NoSQL Databases Model
1.2.8 The Relational Model
1.2.9 Terminology
1.3 Database Languages
1.3.1 Data Definition Language (DDL)
1.3.2 Data Manipulation Language (DML)
1.3.3 Data Control Language (DCL)
1.3.4 Data Retrieval Language (DRL)
1.4 SQL: A Nonprocedural Language
1.4.1 MySQL
1.4.2 SQL Examples
1.5 Data Types in SQL
1.6 Creating Database
1.7 Use of MySQL
1.8 Summary
References
Chapter 2: Creating and Manipulating Database
2.1 Data Definition Language (DDL)
2.1.1 Create Command
2.1.2 ALTER Command
2.1.3 DROP Command
2.1.4 TRUNCATE Command
2.1.5 Rename Command
2.2 Data Manipulation Language (DML)
2.2.1 INSERT INTO Command
2.2.2 DELETE Command
2.2.3 Update Command
2.2.4 SELECT Command
2.3 Data Control Language (DCL)
2.4 Transaction Control Language (TCL)
2.5 Database Structure
2.6 Examples
2.7 Summary
References
Chapter 3: Data and Integrity Constraints
3.1 Introduction
3.1.1 Domain Integrity
3.1.2 Entity Integrity
3.1.3 Referential Integrity
3.1.4 User-Defined Integrity
3.2 Types of Keys in DBMS
3.2.1 Super Key
3.2.2 Candidate Key
3.2.3 Primary Key
3.2.4 Alternate Key
3.2.5 Foreign Key
3.3 Check Constraints
3.4 Unique Constraints
3.5 Domain Constraints
3.6 Summary
References
Chapter 4: Query Execution and Aggregate Functions
4.1 Select Statement
4.1.1 Single Attribute Selection
4.1.2 Multiple Attribute Selection
4.1.3 Complete Table Selection
4.1.4 Distinct Selection
4.1.5 Where Clause
4.2 Aggregate Functions
4.2.1 Average
4.2.2 Count()
4.2.3 Sum()
4.2.4 Min()
4.2.5 Max()
4.3 Order By Clause
4.4 Group By Clause
4.5 Having Clause
4.6 Examples
4.7 Summary
References
Chapter 5: SQL Server vs. Oracle
5.1 Design Schema
5.1.1 Similarities between SQL Server and Oracle
5.1.2 Schema Object Names
5.1.3 Design Issues
5.1.3.1 Data Types
5.1.3.2 Entity Integrity Constraints
5.1.3.3 Referential Integrity Constraints
5.1.3.4 Unique Key Constraints
5.1.3.5 Check Constraints
5.2 Data Types
5.3 Data Storage
5.4 DML Statement from SQL Server vs. Oracle
5.4.1 Connecting to the Database
5.4.2 SELECT Statement
5.5 Microsoft SQL Server vs. Oracle: The Same but Different
5.6 Summary
References
Chapter 6: Conditional Statements and Operators in SQL
6.1 Introduction
6.2 Conditional Evaluation
6.3 Types of Condition
6.3.1 IF Condition
6.3.2 The CASE Statement
6.3.3 While Statement
6.3.4 For Statement
6.4 Operators
6.4.1 Arithmetic Operators
6.4.2 Comparison Operator
6.4.3 Logical Operators
6.4.3.1 ANY Operator
6.4.3.2 ALL Operator
6.4.3.3 AND Operator
6.4.3.4 OR Operator
6.4.3.5 Between Operator
6.4.3.6 EXISTS Operator
6.4.3.7 IN Operator
6.4.3.8 LIKE Operator
6.4.3.9 NOT Operator
6.4.3.10 IS NULL Operator
6.4.3.11 UNIQUE Operator
6.5 Summary
References
Chapter 7: Nested Query and Join
7.1 Understanding Subquery
7.2 Understanding Nested Query
7.3 Join Operator
7.3.1 INNER JOIN
7.3.2 SELF JOIN
7.3.3 OUTER JOIN
7.3.3.1 Left OUTER JOIN
7.3.3.2 Right OUTER JOIN
7.3.3.3 Full OUTER JOIN
7.4 Summary
References
Chapter 8: Views, Indexes, and Sequence
8.1 Introduction to VIEW
8.2 Types of Views
8.2.1 Simple View
8.2.2 Composite View
8.3 Indexes
8.4 Types of Index
8.4.1 Simple Index
8.4.2 Composite Index
8.4.3 Unique Index
8.4.4 Reverse Index
8.4.5 Function-Based Index
8.5 Sequences
8.6 Types of Sequences
8.6.1 Auto-Increment Sequence
8.6.2 Cycle Sequence
8.6.3 No Cycle Sequence
8.7 Examples
8.8 Summary
References
Chapter 9: PL/SQL
9.1 Introduction to PL/SQL
9.2 Features of PL/SQL
9.3 Advantages of PL/SQL
9.4 Data Types in PL/SQL
9.5 Variables and Constants
9.6 PL/SQL Literals
9.7 Examples
9.7.1 IF Statement
9.7.1.1 IF-THEN Statement
9.7.1.2 IF-THEN-ELSE Statement
9.7.2 Case Statement
9.7.3 Loop Statement
9.7.4 Continue Statement
9.7.5 GOTO Statement
9.8 Summary
References
Chapter 10: Procedures and Functions
10.1 Understanding Functions
10.2 Features and Advantages
10.3 Types of Functions
10.4 Return Types
10.5 Procedures
10.6 Difference between Function and Procedure
10.7 Examples
10.8 Summary
References
Chapter 11: Cursors and Triggers
11.1 Understanding Cursor
11.2 Types of Cursors
11.2.1 Implicit Cursors
11.2.2 Explicit Cursors
11.3 Examples of Cursors
11.4 Uses of Cursors
11.5 Understanding Trigger
11.6 Types of Triggers
11.6.1 Row-Level Trigger
11.6.2 Table-Level Triggers
11.6.3 After Trigger
11.6.4 Before Trigger
11.7 Trigger Example
11.8 Summary
References
Chapter 12: Database Change Management
12.1 Overview
12.2 Database Definitions
12.2.1 Scope Specification
12.2.2 Versions
12.2.3 Exporting/Importing Dictionaries
12.3 Comparisons
12.3.1 Defining Dictionary Comparisons
12.3.2 Dictionary Comparison Sources
12.4 Synchronizations
12.4.1 Defining Dictionary Synchronizations
12.4.1.1 Source and Destination
12.4.1.2 Synchronization Mode
12.4.2 Creating Synchronization Versions
12.5 Summary
References
Chapter 13: Sample Questions and Answers
13.1 Company Database Example
13.2 Fill in the Blanks
13.3 True and False
13.4 Multiple Choice Questions
Index
