Principles of Database Management The Practical Guide to Storing, Managing and Analyzing Big and Small Data
Book contents
- Frontmatter
- Contents
- About the Authors
- Preface
- Sober: 1000‰ Driven by Technology
- Part I Databases and Database Design
- Part II Types of Database Systems
- 5 Legacy Databases
- 6 Relational Databases: The Relational Model
- 7 Relational Databases: Structured Query Language (SQL)
- 8 Object-Oriented Databases and Object Persistence
- 9 Extended Relational Databases
- 10 XML Databases
- 11 NoSQL Databases
- Part III Physical Data Storage, Transaction Management, and Database Access
- Part IV Data Warehousing, Data Governance, and (Big) Data Analytics
- Appendix Using the Online Environment
- Glossary
- Index
- Endorsements
7 - Relational Databases: Structured Query Language (SQL)
from Part II - Types of Database Systems
Published online by Cambridge University Press: 17 August 2019
Book contents
- Frontmatter
- Contents
- About the Authors
- Preface
- Sober: 1000‰ Driven by Technology
- Part I Databases and Database Design
- Part II Types of Database Systems
- 5 Legacy Databases
- 6 Relational Databases: The Relational Model
- 7 Relational Databases: Structured Query Language (SQL)
- 8 Object-Oriented Databases and Object Persistence
- 9 Extended Relational Databases
- 10 XML Databases
- 11 NoSQL Databases
- Part III Physical Data Storage, Transaction Management, and Database Access
- Part IV Data Warehousing, Data Governance, and (Big) Data Analytics
- Appendix Using the Online Environment
- Glossary
- Index
- Endorsements
Summary
Chapter Objectives
In this chapter, you will learn to:
• understand the importance of SQL in an RDBMS environment;
• use SQL as a data definition language (DDL);
• use SQL as a data manipulation language (DML) for retrieving, inserting, deleting, and updating data;
• define and use views in SQL;
• define indexes in SQL;
• use SQL for granting and revoking privileges;
• understand how SQL can be used to manage metadata.
Opening Scenario
The relational model adopted by Sober needs to serve various information needs. For example, the insurance firm of the company requests an overview of all accidents together with the number of Sober cars involved and the average damage amount per accident. As part of its customer relationship management (CRM) program, the company wants to retrieve the customer with the maximum wait time and offer him or her a free ride-hailing service. Furthermore, Sober also would like to find out which customers have never booked any of its services and target them with a promotion campaign. To implement its eco-friendly tree-planting program, Sober needs to know which customers booked more than 20 Sober ride-sharing services. Finally, the company remembers that one of the limitations of the EER conceptual data model was that it is perfectly possible that customers book either a ride-hailing or ride-sharing service with their own car. To safeguard its data quality, Sober wants to periodically check that this is not the case in its relational database.
In the previous chapter, we dealt with the modeling aspects of relational databases. In this chapter, we elaborate on SQL, which is the lingua franca of RDBMSs and one of the most popular data definition and manipulation languages in use in the industry nowadays. Its core functionality is implemented, with only some minor variations, throughout all RDBMS products. We discuss the SQL language constructs to express a relational model and to formulate queries that retrieve and modify the data in a relational database.
First, we provide an overview of the SQL data definition language (SQL DDL) to define a relational data model. We also discuss how the SQL data manipulation language (SQL DML) can be used for data manipulation such as retrieving data, updating data, inserting new data and deleting existing data. We then elaborate on SQL views and SQL indexes, which are part of the external and internal data model, respectively.
Information
- Type
- Chapter
- Information
- Principles of Database ManagementThe Practical Guide to Storing, Managing and Analyzing Big and Small Data, pp. 146 - 206Publisher: Cambridge University PressPrint publication year: 2018
Accessibility standard: Unknown
Why this information is here
This section outlines the accessibility features of this content - including support for screen readers, full keyboard navigation and high-contrast display options. This may not be relevant for you.Accessibility Information
Accessibility compliance for the PDF of this book is currently unknown
and may be updated in the future.