 |
| intro to SQL |
we introduce SQL and discuss why the language is so important to database applications.we introduce the notation used in this book to specify the structure of an SQL statement. we discuss how to retrieve data from relations using SQL, and how to insert, update, and delete data from relations. Looking ahead, in Chapter 6 we examine other features of the language, including data definition, views, transactions, and access control. we examine in some detail the features that have been added to the SQL specification to support object-oriented data management, referred to as SQL:1999 or SQL3. In Appendix E we discuss how SQL can be embedded in high-level programming languages to access constructs that were not available in SQL until very recently. The two formal languages, relational algebra and relational calculus, that we covered in Chapter 4 provide a foundation for a large part of the SQL standard and it may be useful to refer back to this chapter occasionally to see the similarities. However, our presentation of SQL is mainly independent of these languages for those readers who have omitted The examples in this chapter use the DreamHome rental database instance
Introduction to SQL In this section we outline the objectives of SQL, provide a short history of the language, and discuss why the language is so important to database applications.
Objectives of SQL
Ideally, a database language should allow a user to: n create the database and relation structures; n perform basic data management tasks, such as the insertion, modification, and deletion of data from the relations; n perform both simple and complex queries. A database language must perform these tasks with minimal user effort, and its command structure and syntax must be relatively easy to learn. Finally, the language must be portable, that is, it must conform to some recognized standard so that we can use the same command structure and syntax when we move from one DBMS to another. SQL is intended to satisfy these requirements. SQL is an example of a transform-oriented language, or a language designed to use relations to transform inputs into required outputs. As a language, the ISO SQL standard has two major components: n a Data Definition Language (DDL) for defining the database structure and controlling access to the data; n a Data Manipulation Language (DML) for retrieving and updating data.
Until SQL:1999, SQL contained only these definitional and manipulative commands; it did not contain flow of control commands, such as IF...THEN...ELSE, GO TO, or DO . . . WHILE. These had to be implemented using a programming or job-control language, or interactively by the decisions of the user. Owing to this lack of computational completeness, SQL can be used in two ways. The first way is to use SQL interactively by entering the statements at a terminal. The second way is to embed SQL statements in a procedural language, as we discuss in Appendix E. We also discuss SQL:1999 and SQL:2003 in SQL is a relatively easy language to learn: n It is a non-procedural language: you specify what information you require, rather than how to get it. In other words, SQL does not require you to specify the access methods to the data. n Like most modern languages, SQL is essentially free-format, which means that parts of statements do not have to be typed at particular locations on the screen. n The command structure consists of standard English words such as CREATE TABLE, INSERT, SELECT. For example: – CREATE TABLE Staff (staffNo VARCHAR(5), lName VARCHAR(15), salary DECIMAL(7,2)); – INSERT INTO Staff VALUES (‘SG16’, ‘Brown’, 8300); – SELECT staffNo, lName, salary FROM Staff WHERE salary > 10000; n SQL can be used by a range of users including Database Administrators (DBA), management personnel, application developers, and many other types of end-user. An international standard now exists for the SQL language making it both the formal and de facto standard language for defining and manipulating relational databases (ISO, 1992, 1999a).