Introduction to Database Design and SQL

Author(s): LOUIS S. SAPIA

Edition: 2

Copyright: 2020

Pages: 284

Choose Your Format

Choose Your Platform | Help Me Choose

Ebook

$90.30

ISBN 9781792410857

Details Electronic Delivery EBOOK 180 days

Access card


This product is currently not available.

Introduction to SQL provides step-by-step instructions with plenty of graphics and examples to help students grasp the material and gain the skills needed for database-related employment. Students have access to a full human resources database that can be downloaded from the author's website. This database is referenced throughout the examples and end of chapter exercises to give readers hands on experience as they work through the content.

The foundation of the text is a result of the author's own work experience and the desire to make the material easy to comprehend. With the help of student Peter Ereshena, who provided valuable feedback and assisted with editing, the author has created material that students will find easy to read and understand.

Chapter 1 - Basic Relational Database Design
Chapter Objectives
Purpose of Databases
Entities
Primary Keys
Foreign Keys
Database Normalization
First Normal Form
Second Normal Form
Third Normal Form
Summary
Exercises

Chapter 2 - Creating a Physical Database
Creating a Physical Database
Chapter Objectives
Starting SQL Server
Connecting to SQL Server
Creating a Database
Database Objects
Character Data Types 
Binary Data Types
Numeric Data Types
Tables
Database Diagram
Creating a Database Diagram
Summary
Exercise

Chapter 3 - Inserting, Updating, and Deleting Data
Inserting, Updating, and Deleting Data
Chapter Objectives
Insert Statement
Saving Your Work
Comments
UPDATE Statement
DELETE Statement
Summary
Exercises

Chapter 4 - Single table Query Basics
Single Table Query Basics
Chapter Objectives
Environment Setup
SELECT Statement
FROM Clause
Renaming Columns
DISTINCT Clause
WHERE Clause
Comparison Operators
Comparing Character Data
Order By Clause
TOP Keyword
Summary
Exercises

Chapter 5 - Using Logical Operators in SQL Statements
Chapter Objectives
AND Operator
OR Operator
NOT Operator
The IN and NOT IN Operator
The BETWEEN Operator
LIKE and NOT LIKE OPERATOR
NULL and NOT NULL Operators
Expressions
Order of Precedence
Summary
Exercises

Chapter 6 - Grouping Data Using Aggregate Functions
Chapter Objectives
Using Aggregate Functions
COUNT() Function
GROUP BY Clause
Errors
HAVING Clause
Basic Grouping Rules
AVG() Function
Using the DISTINCT Keyword
SUM() Function
MIN() Function
MAX() Function
Summary
Exercises

Chapter 7 - Joins
Chapter Objectives
Referential Integrity
INNER JOIN
Outdated Join Syntax
Cartesian Product
Know your Data
Using the WHERE Clause with Joins
OUTER JOIN
FULL OUTER JOIN
SELF JOINS
Joining on Multiple Tables
Summary
Exercises

Chapter 8 - Going Beyond Joins with Set Operations: UNION, INTERSECT, EXCEPT
Chapter Objectives
UNION Operator
ORDER BY
UNION ALL
INTERSECT Operator
EXCEPT Operator
Use of UNION operator
Summary
Exercises

Chapter 9 - Subqueries
Chapter Objectives
Noncorrelated Subqueries and Comparison Operators
Data Type Compatibility
Comparing Multiple Return Values
Subqueries and the IN Operator
Using the JOIN Operator Instead
Subqueries and the NOT IN Operator
Using the ALL and ANY Keywords
"= ANY" ‐ Equals Any Operator
"!= ANY" ‐ Not Equal Any Operator
Correlated Subqueries
EXISTS Operator
NOT EXISTS Operator
Summary
Exercises

Chapter 10 - More Functions
Chapter Objectives
String Functions
LEFT and RIGHT Functions
LEN() Function
Concatenation
LTRIM and RTRIM Functions
UPPER and LOWER Functions
CHARINDEX() Function
REPLACE() Function
SUBSTRING() Function
ISNULL() Function
MATH Functions
ABS() Function
POWER() Function
SQRT() Function
SQUARE() Function
ROUND() Function
CONVERT() Function
CAST() Function
Date Functions
GETDATE() Function
DATEPART() Function
DATEADD() Function
DATEDIFF() Function
CASE Statement
Summary

Chapter 11 - Views and Stored Procedures
Chapter Objectives
Views
Create Views
Joining Views and Tables
Dropping Views
Stored Procedures
ALTER STORED PROCEDURES
Parameters
Dropping Stored Procedures
Summary
Exercises

Index
Appendix A
Chapter Exercise Answers
Appendix B
Instructions to Load
Human Resources Database
Appendix C
Human Resources Database
Insert Statements

LOUIS S. SAPIA

Louis Sapia has worked as an Information Technology professional for over 25 years in various roles including application developer, project manager and data architect. As a data architect, Louis designs, creates and deploys data storage and retrieval solutions to serve business function and reporting.

In addition to working in a professional capacity, Louis has a successful academic career where he teaches information technology courses at various colleges and universities. Due to increased demand in business analytics, Louis has focused his career towards database technologies to meet the demand and shares that knowledge and experience in the classroom.

Introduction to SQL provides step-by-step instructions with plenty of graphics and examples to help students grasp the material and gain the skills needed for database-related employment. Students have access to a full human resources database that can be downloaded from the author's website. This database is referenced throughout the examples and end of chapter exercises to give readers hands on experience as they work through the content.

The foundation of the text is a result of the author's own work experience and the desire to make the material easy to comprehend. With the help of student Peter Ereshena, who provided valuable feedback and assisted with editing, the author has created material that students will find easy to read and understand.

Chapter 1 - Basic Relational Database Design
Chapter Objectives
Purpose of Databases
Entities
Primary Keys
Foreign Keys
Database Normalization
First Normal Form
Second Normal Form
Third Normal Form
Summary
Exercises

Chapter 2 - Creating a Physical Database
Creating a Physical Database
Chapter Objectives
Starting SQL Server
Connecting to SQL Server
Creating a Database
Database Objects
Character Data Types 
Binary Data Types
Numeric Data Types
Tables
Database Diagram
Creating a Database Diagram
Summary
Exercise

Chapter 3 - Inserting, Updating, and Deleting Data
Inserting, Updating, and Deleting Data
Chapter Objectives
Insert Statement
Saving Your Work
Comments
UPDATE Statement
DELETE Statement
Summary
Exercises

Chapter 4 - Single table Query Basics
Single Table Query Basics
Chapter Objectives
Environment Setup
SELECT Statement
FROM Clause
Renaming Columns
DISTINCT Clause
WHERE Clause
Comparison Operators
Comparing Character Data
Order By Clause
TOP Keyword
Summary
Exercises

Chapter 5 - Using Logical Operators in SQL Statements
Chapter Objectives
AND Operator
OR Operator
NOT Operator
The IN and NOT IN Operator
The BETWEEN Operator
LIKE and NOT LIKE OPERATOR
NULL and NOT NULL Operators
Expressions
Order of Precedence
Summary
Exercises

Chapter 6 - Grouping Data Using Aggregate Functions
Chapter Objectives
Using Aggregate Functions
COUNT() Function
GROUP BY Clause
Errors
HAVING Clause
Basic Grouping Rules
AVG() Function
Using the DISTINCT Keyword
SUM() Function
MIN() Function
MAX() Function
Summary
Exercises

Chapter 7 - Joins
Chapter Objectives
Referential Integrity
INNER JOIN
Outdated Join Syntax
Cartesian Product
Know your Data
Using the WHERE Clause with Joins
OUTER JOIN
FULL OUTER JOIN
SELF JOINS
Joining on Multiple Tables
Summary
Exercises

Chapter 8 - Going Beyond Joins with Set Operations: UNION, INTERSECT, EXCEPT
Chapter Objectives
UNION Operator
ORDER BY
UNION ALL
INTERSECT Operator
EXCEPT Operator
Use of UNION operator
Summary
Exercises

Chapter 9 - Subqueries
Chapter Objectives
Noncorrelated Subqueries and Comparison Operators
Data Type Compatibility
Comparing Multiple Return Values
Subqueries and the IN Operator
Using the JOIN Operator Instead
Subqueries and the NOT IN Operator
Using the ALL and ANY Keywords
"= ANY" ‐ Equals Any Operator
"!= ANY" ‐ Not Equal Any Operator
Correlated Subqueries
EXISTS Operator
NOT EXISTS Operator
Summary
Exercises

Chapter 10 - More Functions
Chapter Objectives
String Functions
LEFT and RIGHT Functions
LEN() Function
Concatenation
LTRIM and RTRIM Functions
UPPER and LOWER Functions
CHARINDEX() Function
REPLACE() Function
SUBSTRING() Function
ISNULL() Function
MATH Functions
ABS() Function
POWER() Function
SQRT() Function
SQUARE() Function
ROUND() Function
CONVERT() Function
CAST() Function
Date Functions
GETDATE() Function
DATEPART() Function
DATEADD() Function
DATEDIFF() Function
CASE Statement
Summary

Chapter 11 - Views and Stored Procedures
Chapter Objectives
Views
Create Views
Joining Views and Tables
Dropping Views
Stored Procedures
ALTER STORED PROCEDURES
Parameters
Dropping Stored Procedures
Summary
Exercises

Index
Appendix A
Chapter Exercise Answers
Appendix B
Instructions to Load
Human Resources Database
Appendix C
Human Resources Database
Insert Statements

LOUIS S. SAPIA

Louis Sapia has worked as an Information Technology professional for over 25 years in various roles including application developer, project manager and data architect. As a data architect, Louis designs, creates and deploys data storage and retrieval solutions to serve business function and reporting.

In addition to working in a professional capacity, Louis has a successful academic career where he teaches information technology courses at various colleges and universities. Due to increased demand in business analytics, Louis has focused his career towards database technologies to meet the demand and shares that knowledge and experience in the classroom.