Database 4

The DELETE Command The DELETE command removes tuples from a relation. It includes a WHERE clause, similar to that used in an SQL query, to select the tuples to be deleted. Tuples are explicitly deleted from only one table at a time. However, the deletion may propagate to tuples in other relations if referential triggered actions are specified in the referential integrity constraints of the DDL (see Section 4.2.2).12 Depending on the number of tuples selected by the condition in the WHERE clause, zero, one, or several tuples can be deleted by a single DELETE command.

A missing WHERE clause specifies that all tuples in the relation are to be deleted; however, the table remains in the database as an empty table. We must use the DROP TABLE command to remove the table definition (see Chapter 5). The DELETE commands in U4A to U4D, if applied independently to the database in Figure 3.6, will delete zero, one, four, and all tuples, respectively, from the EMPLOYEE relation: U4A: DELETE FROM EMPLOYEE WHERE Lname='Brown'; U4B: DELETE FROM EMPLOYEE WHERE Ssn='123456789'; U4C: DELETE FROM EMPLOYEE WHERE Dno=5; U4D: DELETE FROM EMPLOYEE

which cannot be NULL. U3: INSERT INTO EMPLOYEE (Fname, Lname, Ssn, Dno) VALUES ('Robert', 'Hatcher', '980760540', 2); (U2 is rejected if referential integrity checking is provided by DBMS.) U2A: INSERT INTO EMPLOYEE (Fname, Lname, Dno) VALUES ('Robert', 'Hatcher', 5); (U2A is rejected if NOT NULL checking is provided by DBMS.)

A DBMS that fully implements SQL should support and enforce all the integrity constraints that can be specified in the DDL. For example, if we issue the command in U2 on the database shown in Figure 3.6, the DBMS should reject the operation because no DEPARTMENT tuple exists in the database with Dnumber = 2. Similarly, U2A would be rejected because no Ssn value is provided and it is the primary key, which cannot be NULL

tables as a set

An SQL table with a key is restricted to being a set, since the key value must be distinct in each tuple.10 If we do want to eliminate duplicate tuples from the result of an SQL query, we use the keyword DISTINCT in the SELECT clause, meaning that only distinct tuples should remain in the result. In general, a query with SELECT DISTINCT eliminates duplicates, whereas a query with SELECT ALL does not. Specifying SELECT with neither ALL nor DISTINCT—as in our previous examples— is equivalent to SELECT ALL. For example, Q11 retrieves the salary of every employee; if several employees have the same salary, that salary value will appear as many times in the result of the query, as shown in Figure 4.4(a). If we are interested only in distinct salary values, we want each value to appear only once, regardless of how many employees earn that salary. By using the keyword DISTINCT as in Q11A, we accomplish this, as shown in Figure 4.4

Interval data

Another data type related to DATE, TIME,and TIMESTAMP is the INTERVAL data type.This specifies an interval—a relative value that can be used to increment or decrement an absolute value of a date, time, or timestamp. Intervals are qualified to be either YEAR/MONTH intervals or DAY/TIME intervals

arithematic in queries

Another feature allows the use of arithmetic in queries. The standard arithmetic operators for addition (+), subtraction (-), multiplication (*), and division (/) can be applied to numeric values or attributes with numeric domains. For example, suppose that we want to see the effect of giving all employees who work on the 'ProductX' project a 10 percent raise; we can issue Query 13 to see what their salaries would become. This example also shows how we can rename an attribute in the query result using AS in the SELECT clause

Boolean data

Boolean data type has the traditional values of TRUE or FALSE. In SQL, because of the presence of NULL values, a three-valued logic is used, so a third possible value for a Boolean data type is UNKNOWN.We discuss the need for UNKNOWN and the three-valued logic in Chapter 5.

Tables as Sets in SQL

As we mentioned earlier, SQL usually treats a table not as a set but rather as a multiset; duplicate tuples can appear more than once in a table, and in the result of a query. SQL does not automatically eliminate duplicate tuples in the results of queries, for the following reasons: ■ Duplicate elimination is an expensive operation. One way to implement it is to sort the tuples first and then eliminate duplicates. ■ The user may want to see duplicate tuples in the result of a query. ■ When an aggregate function (see Section 5.1.7) is applied to tuples, in most cases we do not want to eliminate duplicates.

Specifying Attribute Constraints and Attribute Defaults

Because SQL allows NULLs as attribute values,a constraint NOT NULL may be speci- fied if NULLis not permitted for a particular attribute.This is always implicitly spec- ified for the attributes that are part ofthe primary key ofeach relation,but it can be specified for any other attributes whose values are required not to be NULL, as shown in Figure 4.1. It is also possible to define a default value for an attribute by appending the clause DEFAULT <value> to an attribute definition.The default value is included in any new tuple ifan explicit value is not provided for that attribute.Figure 4.2illustrates an example ofspecifying a default manager for a new department and a default department for a new employee.Ifno default clause is specified,the default default value is NULL for attributes that do not have the NOT NULL constraint. Another type ofconstraint can restrict attribute or domain values using the CHECK clause following an attribute or domain definition.6 For example, suppose that department numbers are restricted to integer numbers between 1 and 20;then,we can change the attribute declaration of Dnumber in the DEPARTMENT table (see Figure 4.1) to the following: Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);

Sql commands

Because SQL is very important (and quite large),we devote two chapters to its fea- tures. In this chapter, Section 4.1 describes the SQL DDL commands for creating schemas and tables,and gives an overview ofthe basic data types in SQL.Section 4.2 presents how basic constraints such as key and referential integrity are specified. Section 4.3 describes the basic SQL constructs for specifying retrieval queries, and Section 4.4 describes the SQL commands for insertion, deletion, and data updates

Specifying Key and Referential Integrity Constraints

Because keys and referential integrity constraints are very important, there are spe- cial clauses within the CREATE TABLE statement to specify them. Some examples to illustrate the specification ofkeys and referential integrity are shown in Figure 4.1.7 The PRIMARY KEY clause specifies one or more attributes that make up the primary key ofa relation.Ifa primary key has a single attribute, the clause can follow the attribute directly. For example, the primary key of DEPARTMENT can be specified as follows (instead ofthe way it is specified in Figure 4.1): Dnumber INT PRIMARY KEYUNIQUE clause specifies alternate (secondary) keys, as illustrated in the DEPARTMENT and PROJECT table declarations in Figure 4.1.The UNIQUE clause can also be specified directly for a secondary key ifthe secondary key is a single attribute, as in the following example: Dname VARCHAR(15) UNIQUE;

point about sql

Before proceeding, we must point out an important distinction between SQL and the formal relational model discussed in Chapter 3: SQL allows a table (relation) to have two or more tuples that are identical in all their attribute values.Hence, in general, an SQL table is not a set of tuples, because a set does not allow two identical members; rather, it is a multiset (sometimes called a bag) of tuples. Some SQL relations are constrained to be sets because a key constraint has been declared or because the DISTINCT option has been used with the SELECT statement (described later in this section).We should be aware of this distinction as we discuss the examples

Bit string

Bit-string data types are either offixed length n—BIT(n)—or varying length—BIT VARYING(n), where n is the maximum number of bits. The default for n,the length ofa character string or bit string,is 1.Literal bit strings are placed between single quotes but preceded by a B to them from character strings; for example, B'10101'.5 Another variable-length bitstring data type called BINARY LARGE OBJECT or BLOB is also available to specify columns that have large binary values,such as images.As for CLOB,the maximum length ofa BLOB can be specified in kilobits (K), megabits (M), or gigabits (G). For example, BLOB(30G) specifies a maxi- mum length of 30 gigabits.

Sntax of create schema

CREATE SCHEMA COMPANY AUTHORIZATION 'Jsmith';

Typically,the SQL schema in which the relations are declared is implicitly specified in the environment in which the CREATE TABLE statements are executed. Alternatively, we can explicitly attach the schema name to the relation name, sepa- rated by a period. For example, by writing

CREATE TABLE COMPANY.EMPLOYEE ... rather than CREATE TABLE EMPLOYEE ... as in Figure 4.1,we can explicitly (rather than implicitly) make the EMPLOYEE table part ofthe COMPANY schema. The relations declared through CREATE TABLE statements are called base tables (or base relations);this means that the relation and its tuples are actually created and stored as a file by the DBMS.Base relations are distinguished from virtual relations, created through the CREATE VIEW statement (see Chapter 5),which may or may not correspond to an actual physical file.In SQL,the attributes in a base table are considered to be ordered in the sequence in which they are specified in the CREATE TABLE statement. However, rows (tuples) are not considered to be ordered within a relation. It is important to note that in Figure 4.1,there are some foreign keys that may cause errors because they are specified either via circular references or because they refer to a table that has not yet been created.For example,the foreign key Super_ssn in the EMPLOYEE table is a circular reference because it refers to the table itself.The foreign key Dno in the EMPLOYEE table refers to the DEPARTMENT table, which not been created yet.To deal with this type ofproblem,these constraints can be left out ofthe initial CREATE TABLE statement,and then added later using the ALTER TABLE statement (see Chapter 5).We displayed all the foreign keys in Figure 4.1to show the complete COMPANY schema in one place.

Example illustrating how default attribute values and referential integrity triggered actions are specified in SQL.

CREATE TABLE EMPLOYEE ( . . . , Dno INT NOT NULL DEFAULT 1, CONSTRAINT EMPPK PRIMARY KEY (Ssn), CONSTRAINT EMPSUPERFK FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT EMPDEPTFK FOREIGN KEY(Dno) REFERENCES DEPARTMENT(Dnumber) ON DELETE SET DEFAULT ON UPDATE CASCADE); CREATE TABLE DEPARTMENT ( . . . , Mgr_ssn CHAR(9) NOT NULL DEFAULT '888665555', . . . , CONSTRAINT DEPTPK PRIMARY KEY(Dnumber), CONSTRAINT DEPTSK UNIQUE (Dname), CONSTRAINT DEPTMGRFK FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn) ON DELETE SET DEFAULT ON UPDATE CASCADE); CREATE TABLE DEPT_LOCATIONS ( . . . , PRIMARY KEY (Dnumber, Dlocation), FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber) ON DELETE CASCADE ON UPDATE CASCADE);

Character string

Character-string data types are either fixed length—CHAR(n) or CHARACTER(n), where n is the number of characters—or varying length— VARCHAR(n) or CHAR VARYING(n) or CHARACTER VARYING(n), where nis the maximum number of characters.When specifying a literal string value,it is placed between single quotation marks (apostrophes),and it is case sensi- tive (a distinction is made between uppercase and lowercase).3 For fixed- length strings,a shorter string is padded with blank characters to the right. For example,ifthe value 'Smith'is for an attribute oftype CHAR(10), it is padded with five blank characters to become 'Smith 'ifneeded.Padded blanks are generally ignored when strings are compared. For comparison purposes, strings are considered ordered in alphabetic (or lexicographic) order; if a string str1 appears before another string str2 in alphabetic order, then str1 is considered to be less than str2.4 There is also a concatenation operator denoted by || (double vertical bar) that can concatenate two strings in SQL.For example,'abc' || 'XYZ'results in a single string'abcXYZ'.Another variable-length string data type called CHARACTER LARGE OBJECT or CLOB is also available to specify columns that have large text values,such as documents. The CLOB maximum length can be specified in kilobytes (K), megabytes (M), or gigabytes (G). For example, CLOB(20M) specifies a max- imum length of 20 megabytes.

1Schema and Catalog Concepts in SQL

Early versions ofSQL did not include the concept ofa relational database schema;all tables (relations) were considered part ofthe same schema.The concept ofan SQL schema was incorporated starting with SQL2 in order to group together tables and other constructs that belong to the same database application.An SQL schema is identified by a schema name,and includes an authorization identifier to indicate the user or account who owns the schema,as well as descriptors for each element in the schema. Schema elements include tables, constraints, views, domains, and other constructs (such as authorization grants) that describe the schema.A schema is cre- ated via the CREATE SCHEMA statement,which can include all the schema elements' definitions. Alternatively, the schema can be assigned a name and authorization identifier, and the elements can be defined later. For example, the following state- ment creates a schema called COMPANY,owned by the user with authorization iden- tifier 'Jsmith'.Note that each statement in SQL ends with a semicolon.

For example, consider the following query.Query 12. Retrieve all employees whose address is in Houston, Texas. Q12: SELECT Fname, Lname FROM EMPLOYEE WHERE Address LIKE '%Houston,TX%';

If an underscore or % is needed as a literal character in the string, the character should be preceded by an escape character, which is specified after the string using the keyword ESCAPE. For example, 'AB\_CD\%EF' ESCAPE '\' represents the literal string 'AB_CD%EF' because \ is specified as the escape character. Any character not used in the string can be chosen as the escape character. Also, we need a rule to specify apostrophes or single quotation marks (' ') if they are to be included in a string because they are used to begin and end strings. If an apostrophe (') is needed, it is represented as two consecutive apostrophes (") so that it will not be interpreted as ending the string. Notice that substring comparison implies that attribute values

sql comparison operators

In SQL, the basic logical comparison operators for comparing attribute values with one another and with literal constants are =, <, <=, >, >=, and <>. These correspond to the relational algebra operators =, <, ≤, >, ≥, and ≠, respectively, and to the C/C++ programming language operators =, <, <=, >, >=, and !=. The main syntactic difference is the not equal operator. SQL has additional comparison operators that we will present gradually. We illustrate the basic SELECT statement in SQL with some sample queries. The queries are labeled here with the same query numbers used in Chapter 6 for easy cross-reference.

Ambiguous Attribute Names, Aliasing, Renaming, and Tuple Variables

In SQL, the same name can be used for two (or more) attributes as long as the attributes are in different relations. If this is the case, and a multitable query refers to two or more attributes with the same name, we must qualify the attribute name with the relation name to prevent ambiguity. This is done by prefixing the relation name to the attribute name and separating the two by a period

Specifying Constraints on Tuples Using CHECK

In addition to key and referential integrity constraints, which are specified by special keywords, other table constraints can be specified through additional CHECK clauses at the end of a CREATE TABLE statement. These can be called tuple-based constraints because they apply to each tuple individually and are checked whenever a tuple is inserted or modified. For example, suppose that the DEPARTMENT table in Figure 4.1 had an additional attribute Dept_create_date, which stores the date when the department was created. Then we could add the following CHECK clause at the end of the CREATE TABLE statement for the DEPARTMENT table to make sure that a manager's start date is later than the department creation date. CHECK (Dept_create_date <= Mgr_start_date);

Sql shema and catalog

In addition to the concept ofa schema,SQL uses the concept ofa catalog—a named collection ofschemas in an SQL environment.An SQL environment is basically an installation of an SQL-compliant RDBMS on a computer system.2 A catalog always contains a special schema called INFORMATION_SCHEMA, which provides informa- tion on all the schemas in the catalog and all the element descriptors in these schemas. Integrity constraints such as referential integrity can be defined between relations only ifthey exist in schemas within the same catalog.Schemas within the same catalog can also share certain elements, such as domain definitions.

The INSERT Command

In its simplest form, INSERT is used to add a single tuple to a relation.We must specify the relation name and a list of values for the tuple. The values should be listed in the same order in which the corresponding attributes were specified in the CREATE TABLE command

Substring Pattern Matching and Arithmetic Operators

In this section we discuss several more features of SQL. The first feature allows comparison conditions on only parts of a character string, using the LIKE comparison operator. This can be used for string pattern matching. Partial strings are specified using two reserved characters: % replaces an arbitrary number of zero or more characters, and the underscore (_) replaces a single character. For example, consider the following query.

example query in ambiguous

Q1A: SELECT Fname, EMPLOYEE.Name, Address FROM EMPLOYEE, DEPARTMENT WHERE DEPARTMENT.Name='Research' AND DEPARTMENT.Dnumber=EMPLOYEE.Dnumber; Fully qualified attribute names can be used for clarity even if there is no ambiguity in attribute names. Q1 is shown in this manner as is Q1 below.We can also create an alias for each table name to avoid repeated typing of long table names (see Q8 below)

The SELECT-FROM-WHERE Structure of Basic SQL Queries

Queries in SQL can be very complex. We will start with simple queries, and then progress to more complex ones in a step-by-step manner. The basic form of the SELECT statement, sometimes called a mapping or a select-from-where block, isformed of the three clauses SELECT, FROM, and WHERE and has the following form:9 SELECT <attribute list> FROM <table list> WHERE <condition>; where ■ <attribute list> is a list of attribute names whose values are to be retrieved by the query. ■ <table list> is a list of the relation names required to process the query. ■ <condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved by the query.

query example

Query 0. Retrieve the birth date and address of the employee(s) whose name is 'John B. Smith'. Q0: SELECT Bdate, Address FROM EMPLOYEE WHERE Fname='John' AND Minit='B' AND Lname='Smith'; This query involves only the EMPLOYEE relation listed in the FROM clause. The query selects the individual EMPLOYEE tuples that satisfy the condition of the WHERE clause, then projects the result on the Bdate and Address attributes listed in the SELECT clause. The SELECT clause of SQL specifies the attributes whose values are to be retrieved, which are called the projection attributes, and the WHERE clause specifies the Boolean condition that must be true for any retrieved tuple, which is known as the selection condition. Figure 4.3(a) shows the result of query Q0 on the database of Figure 3.6. We can think of an implicit tuple variable or iterator in the SQL query ranging or looping over each individual tuple in the EMPLOYEE table and evaluating the condition in the WHERE clause. Only those tuples that satisfy the condition—that is,

query example

Query 1. Retrieve the name and address of all employees who work for the 'Research' department. Q1: SELECT Fname, Lname, Address FROM EMPLOYEE, DEPARTMENT WHERE Dname='Research' AND Dnumber=Dno; In the WHERE clause of Q1, the condition Dname = 'Research' is a selection condition that chooses the particular tuple of interest in the DEPARTMENT table, because Dname is an attribute of DEPARTMENT. The condition Dnumber = Dno is called a join condition, because it combines two tuples: one from DEPARTMENT and one from EMPLOYEE, whenever the value of Dnumber in DEPARTMENT is equal to the value of Dno in EMPLOYEE. The result of query Q1 is shown in Figure 4.3(b). In general, any number of selection and join conditions may be specified in a single SQL query. A query that involves only selection and join conditions plus projection attributes is known as a select-project-join query. The next example is a select-project-join query with two join conditions.

table as a set example

Query 11. Retrieve the salary of every employee (Q11) and all distinct salary values (Q11A). Q11: SELECT ALL Salary FROM EMPLOYEE; Q11A: SELECT DISTINCT Salary FROM EMPLOYEE; SQL has directly incorporated some of the set operations from mathematical set theory, which are also part of relational algebra (see Chapter 6). There are set union (UNION), set difference (EXCEPT),11 and set intersection (INTERSECT) operations. The relations resulting from these set operations are sets of tuples; that is, duplicate tuples are eliminated from the result. These set operations apply only to union-compatible relations, so we must make sure that the two relations on which we apply the operation have the same attributes and that the attributes appear in the same order in both relations. The next example illustrates the use of UNION

query example ss

Query 13. Show the resulting salaries if every employee working on the 'ProductX' project is given a 10 percent raise. Q13: SELECT E.Fname, E.Lname, 1.1 * E.Salary AS Increased_sal FROM EMPLOYEE AS E, WORKS_ON AS W, PROJECT AS P WHERE E.Ssn=W.Essn AND W.Pno=P.Pnumber AND P.Pname='ProductX'; For string data types, the concatenate operator || can be used in a query to append two string values. For date, time, timestamp, and interval data types, operators include incrementing (+) or decrementing (-) a date, time, or timestamp by an interval. In addition, an interval value is the result of the difference between two date, time, or timestamp values. Another comparison operator,

Ordering of Query Results SQL allows the user to order the tuples in the result of a query by the values of one or more of the attributes that appear in the query result, by using the ORDER BY clause

Query 15. Retrieve a list of employees and the projects they are working on, ordered by department and, within each department, ordered alphabetically by last name, then first name. Q15: SELECT D.Dname, E.Lname, E.Fname, P.Pname FROM DEPARTMENT D, EMPLOYEE E, WORKS_ON W, PROJECT P WHERE D.Dnumber= E.Dno AND E.Ssn= W.Essn AND W.Pno= P.Pnumber ORDER BY D.Dname, E.Lname, E.Fname; The default order is in ascending order of values.We can specify the keyword DESC if we want to see the result in a descending order of values. The keyword ASC can be used to specify ascending order explicitly. For example, if we want descending alphabetical order on Dname and ascending order on Lname, Fname, the ORDER BY clause of Q15 can be written as ORDER BY D.Dname DESC, E.Lname ASC, E.Fname ASC

query example 2

Query 2. For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birth date. Q2: SELECT Pnumber, Dnum, Lname, Address, Bdate FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Plocation='Stafford'; The join condition Dnum = Dnumber relates a project tuple to its controlling department tuple, whereas the join condition Mgr_ssn = Ssn relates the controlling department tuple to the employee tuple who manages that department. Each tuple in the result will be a combination of one project, one department, and one employee that satisfies the join conditions. The projection attributes are used to choose the attributes to be displayed from each combined tuple. The result of query Q2 is shown in Figure 4.3(c).

table as a set

Query 4. Make a list of all project numbers for projects that involve an employee whose last name is 'Smith', either as a worker or as a manager of the department that controls the project. Q4A: (SELECT DISTINCT Pnumber FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname='Smith' ) UNION ( SELECT DISTINCT Pnumber FROM PROJECT, WORKS_ON, EMPLOYEE WHERE Pnumber=Pno AND Essn=Ssn AND Lname='Smith' ); The first SELECT query retrieves the projects that involve a 'Smith' as manager of the department that controls the project, and the second retrieves the projects that involve a 'Smith' as a worker on the project.Notice that if several employees have the last name 'Smith', the project names involving any of them will be retrieved. Applying the UNION operation to the two SELECT queries gives the desired result. SQL also has corresponding multiset operations, which are followed by the keyword ALL (UNION ALL, EXCEPT ALL, INTERSECT ALL). Their results are multisets (duplicates are not eliminated). The behavior of these operations is illustrated by the examples in Figure 4.5. Basically, each tuple—whether it is a duplicate or not—is considered as a different tuple when applying these operations

basic sql queries

SQL has one basic statement for retrieving information from a database: the SELECT statement. The SELECT statement is not the same as the SELECT operation of relational algebra, which we discuss in Chapter 6. There are many options and flavors to the SELECT statement in SQL, so we will introduce its features gradually. We will use sample queries specified on the schema of Figure 3.5 and will refer to the sample database state shown in Figure 3.6 to show the results of some of the sample queries. In this section, we present the features of SQL for simple retrieval queries.

Sql definition

SQL is a comprehensive database language: It has statements for data definitions, queries,and updates.Hence,it is both a DDL and a DML. In addition, it has facili- ties for defining views on the database, for specifying security and authorization, for defining integrity constraints, and for specifying transaction controls. It also has rules for embedding SQL statements into a general-purpose programming language such as Java, COBOL, or C/C++.

Sql and DBMS

SQL language may be considered one ofthe major reasons for the commercial success of rela- tional databases. Because it became a standard for relational databases, users were less concerned about migrating their database applications from other types of database systems—for example, network or hierarchical systems—to relational sys- tems.This is because even ifthe users became dissatisfied with the particular rela- tional DBMS product they were using, converting to another relational DBMS product was not expected to be too expensive and time-consuming because both systems followed the same language standards.

SQL table row column

SQL uses the terms table, row,and column for the formal relational model terms relation,tuple,and attribute, respectively.We will use the corresponding terms inter- changeably.The main SQL command for data definition is the CREATE statement, which can be used to create schemas,tables (relations),and domains (as well as other constructs such as views, assertions, and triggers). Before we describe the rel- evant CREATE statements, we discuss schema and catalog concepts in Section 4.1.1 to place our discussion in perspective.Section 4.1.2 describes how tables are created, and Section 4.1.3 describes the most important data types available for attribute specification. Because the SQL specification is very large, we give a description of the most important features.Further details can be found in the various SQL stan- dards documents (see end-of-chapter bibliographic notes).

2The CREATE TABLE Command in SQL

The CREATE TABLE command is used to specify a new relation by giving it a name and specifying its attributes and initial constraints. The attributes are specified first, and each attribute is given a name,a data type to specify its domain ofvalues,and any attribute constraints, such as NOT NULL. The key, entity integrity, and referen- tial integrity constraints can be specified within the CREATE TABLE statement after the attributes are declared,or they can be added later using the ALTER TABLE com- mand (see Chapter 5).Figure 4.1shows sample data definition statements in SQL for the COMPANY relational database schema

Date data type

The DATE data type has ten positions,and its components are YEAR, MONTH,and DAY in the form YYYY-MM-DD.The TIME data type has at least eight positions, with the components HOUR, MINUTE,and SECOND in the form HH:MM:SS.Only valid dates and times should be allowed by the SQL implementation.This implies that months should be between 1 and 12 and dates must be between 1 and 31;furthermore,a date should be a valid date for the corresponding month.The < (less than) comparison can be used with dates or times—an earlier date is considered to be smaller than a later date, and similarly with time. Literal values are represented by single-quoted strings preceded by the keyword DATE or TIME; for example, DATE '2008-09- 27'or TIME '09:12:47'. In addition, a data type TIME(i), where i is called time fractional seconds precision, specifies i+ 1 additional positions for TIME—one position for an additional period (.) separator character,and i positions for specifying decimal fractions of a second. A TIME WITH TIME ZONE data type includes an additional six positions for specifying the displacement from the standard universal time zone,which is in the range +13:00 to -12:59 in units of HOURS:MINUTES. If WITH TIME ZONE is not included,the default is the local time zone for the SQL session.

Discussion and Summary of Basic SQL Retrieval Queries A simple retrieval query in SQL can consist of up to four clauses, but only the first two—SELECT and FROM—are mandatory. The clauses are specified in the following order, with the clauses between square brackets [ ... ] being optional: SELECT <attribute list> FROM <table list> [ WHERE <condition> ] [ ORDER BY <attribute list> ];

The SELECT clause lists the attributes to be retrieved, and the FROM clause specifies all relations (tables) needed in the simple query. The WHERE clause identifies the conditions for selecting the tuples from these relations, including join conditions if needed. ORDER BY specifies an order for displaying the results of a query. Two additional clauses GROUP BY and HAVING will be described in Section 5.1.8. In Chapter 5, we will present more complex features of SQL retrieval queries. These include the following: nested queries that allow one query to be included as part of another query; aggregate functions that are used to provide summaries of the information in the tables; two additional clauses (GROUP BY and HAVING) that can be used to provide additional power to aggregate functions; and various types of joins that can combine records from various tables in different ways.

The UPDATE Command

The UPDATE command is used to modify attribute values of one or more selected tuples. As in the DELETE command, a WHERE clause in the UPDATE command selects the tuples to be modified from a single relation. However, updating a

3Attribute Data Types and Domains in SQL

The basic data types available for attributes include numeric, character string, bit string, Boolean, date, and time. ■ Numeric data types include integer numbers ofvarious sizes (INTEGER or INT,and SMALLINT) and floating-point (real) numbers of various precision (FLOAT or REAL,and DOUBLE PRECISION). Formatted numbers can be declared by using DECIMAL(i,j)—or DEC(i,j) or NUMERIC(i,j)—where i,the precision,is the total number ofdecimal digits and j,the scale,is the number ofdigits after the decimal point.The default for scale is zero,and the default for precision is implementation-defined

Examples of data types

The format of DATE, TIME,and TIMESTAMP can be considered as a special type of string.Hence,they can generally be used in string comparisons by being cast (or coerced or converted) into the equivalent strings. It is possible to specify the data type ofeach attribute directly,as in Figure 4.1;alter- natively,a domain can be declared,and the domain name used with the attribute specification.This makes it easier to change the data type for a domain that is used by numerous attributes in a schema, and improves schema readability. For example, we can create a domain SSN_TYPE by the following statement: CREATE DOMAIN SSN_TYPE AS CHAR(9); We can use SSN_TYPE in place of CHAR(9) in Figure 4.1for the attributes Ssn and Super_ssn of EMPLOYEE, Mgr_ssn of DEPARTMENT, Essn of WORKS_ON,and Essn of DEPENDENT.A domain can also have an optional default specification via a DEFAULT clause,as we discuss later for attributes.Notice that domains may not be available in some implementations of SQL.

Sql standard

The later SQL standards (starting with SQL:1999) are divided into a core specifica- tion plus specialized extensions.The core is supposed to be implemented by all RDBMS vendors that are SQL compliant.The extensions can be implemented as optional modules to be purchased independently for specific database applications such as data mining,spatial data,temporal data, data warehousing,online analytical processing (OLAP), multimedia data, and so on.

Sql history

The name SQL is presently expanded as Structured Query Language. Originally, SQL was called SEQUEL (Structured English QUEry Language) and was designed and implemented at IBM Research as the interface for an experimental relational database system called SYSTEM R.SQL is now the standard language for commer- cial relational DBMSs. A joint effort by the American National Standards Institute (ANSI) and the International Standards Organization (ISO) has led to a standard version ofSQL (ANSI 1986),called SQL-86 or SQL1.A revised and much expanded standard called SQL-92 (also referred to as SQL2) was subsequently developed. The next standard that is well-recognized is SQL:1999,which started out as SQL3.Two later updates to the standard are SQL:2003 and SQL:2006,which added XML fea- tures (see Chapter 12) among other updates to the language.Another update in 2008 incorporated more object database features in SQL (see Chapter 11).We will try to cover the latest version ofSQL as much as possible.

insert example type

U1: INSERT INTO EMPLOYEE VALUES ( 'Richard', 'K', 'Marini', '653298653', '1962-12-30', '98 Oak Forest, Katy, TX', 'M', 37000, '653298653', 4 );

second form of the INSERT statement allows the user to specify explicit attribute names that correspond to the values provided in the INSERT command. This is useful if a relation has many attributes but only a few of those attributes are assigned values in the new tuple. However, the values must include all attributes with NOT NULL specification and no default value. Attributes with NULL allowed or DEFAULT values are the ones that can be left out. For example, to enter a tuple for a new EMPLOYEE for whom we know only the Fname, Lname, Dno, and Ssn attributes, we can use U1A

U1A: INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn) VALUES ('Richard', 'Marini', 4, '653298653'); Attributes not specified in U1A are set to their DEFAULT or to NULL, and the values are listed in the same order as the attributes are listed in the INSERT command itself. It is also possible to insert into a relation multiple tuples separated by commas in a single INSERT command. The attribute values forming each tuple are enclosed in parentheses.

A variation of the INSERT command inserts multiple tuples into a relation in conjunction with creating the relation and loading it with the result of a query. For example, to create a temporary table that has the employee last name, project name, and hours per week for each employee working on a project, we can write the statements in U3A and U3B:

U3A: CREATE TABLE WORKS_ON_INFO ( Emp_name VARCHAR(15), Proj_name VARCHAR(15), Hours_per_week DECIMAL(3,1) );

Unspecified WHERE Clause and Use of the Asterisk

We discuss two more features of SQL here. A missing WHERE clause indicates no condition on tuple selection; hence, all tuples of the relation specified in the FROM clause qualify and are selected for the query result. If more than one relation is specified in the FROM clause and there is no WHERE clause, then the CROSS PRODUCT—all possible tuple combinations—of these relations is selected. For example, Query 9 selects all EMPLOYEE Ssns (Figure 4.3(e)), and Query 10 selects all combinations of an EMPLOYEE Ssn and a DEPARTMENT Dname, regardless of whether the employee works for the department or not (Figure 4.3(f)). Queries 9 and 10. Select all EMPLOYEE Ssns (Q9) and all combinations of EMPLOYEE Ssn and DEPARTMENT Dname (Q10) in the database. Q9: SELECT Ssn FROM EMPLOYEE; Q10: SELECT Ssn, Dname FROM EMPLOYEE, DEPARTMENT; It is extremely important to specify every selection and join condition in the WHERE clause; if any such condition is overlooked, incorrect and very large relations may result. Notice that Q10 is similar to a CROSS PRODUCT operation followed by a PROJECT operation in relational algebra (see Chapter 6). If we specify all the attributes of EMPLOYEE and DEPARTMENT in Q10, we get the actual CROSS PRODUCT (except for duplicate elimination, if any).

Several tuples can be modified with a single UPDATE command. An example is to give all employees in the 'Research' department a 10 percent raise in salary, as shown in U6. In this request, the modified Salary value depends on the original Salary value in each tuple, so two references to the Salary attribute are needed. In the SET clause,

refers to the old Salary value before modification, and the one on the left refers to the new Salary value after modification: U6: UPDATE EMPLOYEE SET Salary = Salary * 1.1 WHERE Dno = 5; It is also possible to specify NULL or DEFAULT as the new attribute value.Notice that each UPDATE command explicitly refers to a single relation only. To modify multiple relations, we must issue several UPDATE commands

The CHECK clause can also be used to specify more general constraints using

the CREATE ASSERTION statement of SQL. We discuss this in Chapter 5 because it requires the full power of queries,

Timestamp

timestamp data type (TIMESTAMP) includes the DATE and TIME fields, plus a minimum ofsix positions for decimal fractions ofseconds and an optional WITH TIME ZONE qualifier. Literal values are represented by single- quoted strings preceded by the keyword TIMESTAMP,with a blank space between data and time; for example, TIMESTAMP '2008-09-27 09:12:47.648302'.