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.
Review the options below to login to check your access.
Log in with your Cambridge Higher Education account to check access.
If you believe you should have access to this content, please contact your institutional librarian or consult our FAQ page for further information about accessing our content.