Introduction to SQL 341 There are a number of database query languages in use, either commercially or experimentally. In this chapter, as well as in Chapters 4 and 5, we study the most widely used query language, SOL. Although we refer to the SQL language as a “query language,” it can do much more than just query a database. It can define the structure of the data, modify data in the database, and specify security constraints. It is not our intention to provide a complete users’ guide for SQL. Rather, we present SQLs fundamental constructs and concepts. Individual implementations of SQL may differ in details, or may support only a subset of the full language. Overview of the SQL Query Language 1bM developed the original version of SQL, originally called Sequel, as part of the System R project in the early 1970s. The Sequel language has evolved since then, and its name has changed to SQL (Structured Query Language). Many products now support the SOL language. SOL has clearly established itself as the standard relational database language. In 1986, the American National Standards Institute (ANSI) and the Interna- tional Organization for Standardization (ISO) published an SQL standard, called SQI-86. ANSI published an extended standard for SQL, SQL-89, in 1989. The next ver- sion of the standard was SQL-92 standard, followed by SQL:1999, SQL:2003, SQL:2006, and most recently 8QL:2008. The bibliographic notes provide references to these standards. The SQL language has several parts: © Data-definition language (DDL). The SQL DDL provides commands for defin- ing relation schemas, deleting relations, and modifying relation schemas. © Data-manipulation language (DML). The SQL DML provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database. 873.2. SOL Data Definition 59 e The security and authorization information for each relation. © The physical storage structure of each relation on disk. We discuss here basic schema definition and basic types; we defer discussion of the other SQL DDL features to Chapters 4 and 5. 3.2.1 Basic Types The SQL standard supports a variety of built-in types, including: char(n): A fixed-length character string with user-specified length 1. The full form, character, can be used instead. varchar(n): A variable-length character string with user-specified maximum length 1. The full form, character varying, is equivalent. int: An integer (a finite subset of the integers that is machine dependent). The full form, integer, is equivalent. smallint: A small integer (a machine-dependent subset of the integer type). numeric(p.d): A fixed-point number with user-specified precision. The num- ber consists of p digits (plus a sign), and d of the p digits are to the right of the decimal point. Thus, numeric(3,1) allows 44.5 to be stored exactly, but neither 444.5 or 0.32 can be stored exactly in a field of this type. real, double preci : Floating-point and double-precision floating-point numbers with machine-dependent precision. float(n): A floating-point number, with precision of at least 1 digits. Additional types are covered in Section 4.5. Each type may include a special value called the null value. A null value indicates an absent value that may exist but be unknown or that may not exist at all. In certain cases, we may wish to prohibit null values from being entered, as. we shall see shortly. The char data type stores fixed length strings. Consider, for example, an attribute A of type char(10). If we store a string “Avi" in this attribute, 7 spaces are appended to the string to make it 10 characters long. In contrast, if attribute B were of type varchar(10), and we store “Avi” in attribute B, no spaces would be added. When comparing two values of type char, if they are of different lengths extra spaces are automatically added to the shorter oné to make them the same size, before comparison. When comparinga char type witha varchar type, one may expect extra spaces to be added to the varchar type to make the lengths equal, before comparison; however, this may or may not be done, depending on the database system. As a result, even if the same value “Avi” is stored in the attributes A and B above, a comparison A=B may return false. We recommend you always use the varchar type instead of the char type to avoid these problems.