113
Chapter 4
Database Development and
Management with SQL
Objectives
Get started with SQL
Create database objects with SQL
Manage database objects with SQL
Control database object privileges with SQL
4.1 Introduction
In this chapter, SQL, a commonly used database development tool, will be introduced. You will
learn some often-used SQL commands and use them to create and manage database objects. You
will use SQL to create database objects such as tables, constraints, users, and roles. You will learn
how to modify the database structure and data content with SQL commands.
4.2 Structured Query Language
As shown in the previous chapter, you can use Windows Azure SQL Database to create database
objects with a graphical tool. Like Microsoft, some other DBMS products also provide graphical
tools for database development and management. It is relatively easy to learn how to use graphical
tools. Especially for beginners, graphical database development tools can help avoid many mis-
takes due to lack of experience.
On the other hand, experienced database developers prefer a more popular database develop-
ment tool called SQL. ere are some advantages to using SQL instead of graphical database
development tools. For example, a DBMS product does not share its graphical tools with another
DBMS product. Also, a graphical database development tool requires interactivity with users.
114 ◾  Cloud Database Development and Management
erefore, it is not good for bulk operations. On the other hand, SQL code is portable and can be
executed by bulk operations.
With SQL, database developers can create database objects such as tables and views. ey
can also use SQL to perform operations such as inserting, updating, and deleting records in a
table. SQL provides functions and procedures to help you implement database security measures
and performance tuning. SQL commands can be used to manage databases such as defining
constraints to maintain database integrity. Since the early 1970s, SQL has been a primary tool in
database development.
As the most important database development tool, SQL is supported by various DBMS prod-
ucts. It has been modified in many ways to serve the special needs of each individual DBMS prod-
uct. To set up a common ground, the American National Standards Institute (ANSI) published the
standard for SQL. In 1986, the ANSI standardized the database querying language and named it
ANSI SQL-86. Since then, the SQL standard has been updated several times. SQL has been for-
mally adopted as an International Standard by the International Organization for Standardization
(ISO), the International Electrotechnical Commission (IEC), and it has also been adopted as a
Federal Information Processing Standard (FIPS) for the U.S. federal government. ere are impor-
tant new features added to this latest version such as the support for XML and additional collection
data types.
Even with the added features, ANSI-based SQL is still a very basic query language. DBMS
vendors have added their own extensions on top of the basic ANSI-based SQL. e extended SQL
language used by Microsoft SQL Server is called Transact-SQL, which supports ANSI SQL. In
addition, Transact-SQL has added some commands for procedural programming. In this chapter,
let us first study the basic commands for creating and managing database objects.
ere are three major types of commands for creating and managing database objects:
Data Definition Language (DDL): Statements in DDL can be used to create, modify, and
delete database objects such as databases, tables, columns, and constraints. e commonly
used DDL statements are
CREATE: Used to create database objects
ALTER: Used to modify database objects
DROP: Used to delete database objects
Data Control Language (DCL): Statements in DCL can be used to grant or revoke per-
missions on database objects. e commonly used DCL statements are
GRANT: Used to grant permissions for a user to use a database object or execute some SQL
commands
REVOKE: Used to remove previously granted permissions
Data Manipulation Language (DML): Statements in DML can be used to select, insert,
update, and delete data in a database object. e commonly used DML statements are
SELECT: Used to query information from a database
INSERT: Used to insert new rows of data into database tables
UPDATE: Used to modify data values in database tables
DELETE: Used to delete existing rows in database tables
SQL statements by various DBMS vendors may be different. Often, the difference is minor.
SQL code conversion from one DBMS to another DBMS does not take much effort. is makes
SQL almost portable across DBMS products from different database vendors. Some companies

Get Cloud Database Development and Management now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.