SQL: Chpt2 More "Beginning" SQL Commands and Statements

All of the comparison operators:

> (greater than), <> not equal, = equal, > = greater than or equal to, and so on are available for WHERE conditions. There are also other common operators that include IN, EXISTS, and BETWEEN (each of which will be discussed later in the text). Multiple conditions can be included in a WHERE clause by using logical operators, AND and OR. In addition there is also a BETWEEN operator. The following sections discuss the use of the AND, OR, and BETWEEN operators in the WHERE clause.

Data Types

A data type is used so that you can keep your data consistent. For example, you want to make sure that all the values in a zipcode field are numbers. You can then make the zipcode field with a numeric data type - specifically, you could make it a INTEGER field. For entering textual data, you would make a field CHAR or VARCHAR2 data type. What is the difference between the two? Basically, a CHAR data type is fixed, so if you define a field to be 20 characters, and you did not use up the 20 characters, that field would still occupy the 20 characters. A VARCHAR2 field, on the other hand is not fixed length, so if you defined a field as VARCHAR2(20) and only use 5 character, the other 15 character spaces are free from memory. Read about the rest of the data types.

Data-types

A data-type of an attribute defines the allowable values as well as the operations we can perform on the attri- bute. We commonly use the NUMBER data-type for numbers and the CHAR and VARCHAR2 data-types for character strings. In this section, we will explore these and other commonly and uncommonly used data-types.

Now, to alter the table, type:

ALTER TABLE Course_copy MODIFY offering_dept VARCHAR2(6); This will give: Table altered. Now, DESC Course_copy

The ALTER TABLE Statement Example, the following will add the address attribute (of data-type VARCHAR2) to the Customer table created earlier in this chapter:

ALTER TABLE Customer ADD address VARCHAR2(20);

The ALTER TABLE Statement For example, the following will modify the balance attribute of the Customer table, making it a size of eight with two decimal places:

ALTER TABLE Customer MODIFY balance NUMBER (8,2); We can only make attributes larger and, we cannot violate any existing data with this statement.

The ALTER TABLE Statement The ALTER TABLE statement is used to alter the structure of a table. With the ALTER TABLE statement you can add/delete columns from tables and/or alter the size or data-types of columns. The simplified syntax to add a column to an existing table would be:

ALTER TABLE Tablename ADD column-name data-type Using the ALTER TABLE statement, we can define or change a default column value, enable or disable integrity constraints, manage internal space, and do some other useful things we will cover later. ***If you modify a column, you can only make it bigger, not smaller, unless there is no data; and, all the data in the existing database must conform to your modified type. If you add a column, it will contain null values until you put data into it with an UPDATE or INSERT command to change the values in the new column.

The ALTER TABLE Statement To change a column's type, the simplified syntax would be:

ALTER TABLE tablename MODIFY column-name new_data-type

Using OR

Another way to combine conditions in a WHERE clause is by using the OR operator. The OR operator can be used when either of the conditions can be met for a row to be included in the result set. For example, consider the following query: SELECT sname, class, major FROM Student WHERE class = 4 -- either condition being true gives us a row OR major = 'MATH'; This result set is a tabulation of all students who are either MATH majors OR seniors (class = 4). The OR means that either of the criteria, WHERE class = 4 or major = 'MATH', has to be met for the row to be included in the result set.

Using AND

By using AND in the WHERE clause of a SELECT we may combine conditions. The result set with WHERE .. AND .. can never contain more rows than the SELECT with either of the conditions by themselves. For example, consider the following query: SELECT sname, class, major FROM Student WHERE class = 4 -- both conditions must be true to retrieve a row AND major = 'MATH'; The AND clause means that both the conditions, WHERE class = 4 and major = 'MATH' have to be met for the row to be included in the result set.

Suppose you had a table of values and you deleted some of the rows. You can undo the delete action by issuing a ROLLBACK statement. For example, suppose while updating the Customer table you issued the following DELETE statement: DELETE FROM Customer WHERE balance < 500; This deletes all rows in the Customer table where balances are less than 500. Then you note that your boss actually asked you to delete customers where the balance was less than 50, not 500. You can ROLLBACK the previous statement with: ROLLBACK; The ROLLBACK command resets the Customer table to whatever the values were at the beginning of the transaction. If you are sure that you have successfully executed the correct command, you may execute:

COMMIT; and the table will not be "ROLLBACK-able." After the COMMIT, the transaction is history. We mentioned that ROLLBACK would work under certain conditions because some statements contain implied COMMITs. Implied COMMITs are: • when you use Data Definition Language (DDL) commands (DDL commands define or delete data- base objects. Examples of such commands include CREATE VIEW, CREATE TABLE, CREATE INDEX, DROP TABLE, RENAME TABLE, ALTER TABLE.) • when you log off of SQL, implicitly COMMITting your work. For valuable tables, an explicit backup (or two) should be made and permissions for update and delete should be judiciously managed by the table owner. As an intermediate COMMIT/ROLLBACK action, you can also name a transaction milestone called a SAVEPOINT. For example, you can use the following command to mark a point in a transaction with the name, point1: SAVEPOINT point1 You can then ROLLBACK to point1 with the following statement: ROLLBACK TO SAVEPOINT point1 The naming of SAVEPOINT points allows you to have several ROLLBACK places in a transaction— "milestones" if you will. These milestones allow partial ROLLBACKs. COMMIT is much stronger than a SAVEPOINT because it commits all actions and wipes out the SAVEPOINTs if there are any.

A little while ago you created a backup copy of the Course table like this:

CREATE TABLE Course_copy AS SELECT * FROM COURSE; Now, we'll use the backup to provide an example of ALTER TABLE: DESC Course_copy

Be careful with this INSERT INTO .. SELECT statement. Unlike INSERT INTO ..VALUES, which inserts one row at a time, you almost always insert multiple rows with INSERT INTO .. SELECT. If types match, the insert will take place regardless of whether it makes sense or not. Finally, the previous two statements (CREATE TABLE and INSERT INTO) may be combined for creating backup copies of tables like this:

CREATE TABLE Course_copy AS SELECT * FROM COURSE You will get: Table created.

The UPDATE Statement Another common statement used for setting/changing data values in tables is the UPDATE statement. As with the INSERT INTO .. SELECT option, you often update more than one row at a time with UPDATE. To illustrate the UPDATE statement, you may create a table called Customer2, like this:

CREATE TABLE Customer2 (cno CHAR(3), balance NUMBER(5), date_opened DATE);

You may limit the SELECT and load to less than the whole table—fewer rows or columns, as necessary. Some examples of restricted SELECTs for the INSERT statement follow. Suppose you had a table with only one attribute, a name, which was created as follows:

CREATE TABLE Namelist (customer_name VARCHAR2(20)); And assume that a second table existed that had the following structure: Customer1(cname, cnumber, amount) where cname is VARCHAR2(20).

Simple CREATETABLE Statement The CREATE TABLE statement allows us to create a table in which we can store data. A minimal syntax for this statement is as follows (we will expand this CREATE TABLE syntax with more options in a later chapter):

CREATE TABLE Tablename (attribute_name data-type, attribute_name data-type, ...); Tablename and attribute names are your choice, but keywords should be avoided (words like table or select). A data-type defines the kind of data that is allowable for that attribute - like numbers, alphanumeric characters or dates. We have a whole section on data-types later in the chapter. **Work Examples on pg 44

An example of a multi-row delete from our sample Customer2 table might be:

DELETE FROM Customer2 WHERE balance < 10; or DELETE FROM Customer2 WHERE date_opened < '01-JAN-17';

The DELETE Statement The DELETE statement is used to delete rows from tables. A sample syntax for the DELETE statement is:

DELETE FROM Table WHERE (condition) The (condition) determines which rows to delete. ***With UPDATE and DELETE, multiple rows can be affected and hence these can be dangerous commands. Be careful when using them and please wait until you learn to use ROLLBACK before applying these statements.

Deleting a table To remove a table from the database, you would use the DROP TABLE command as follows:

DROP TABLE Table_names; Once you drop a table, you cannot bring the table or its data back. DROPping tables cannot be undone

Now suppose that you wanted to load a table called Emp1 from Employee with the following attributes, where the attributes stand for address, salary, and employee number, respectively:

Emp1 (addr, sal, empno)

As with the INSERT INTO .. VALUES, if you create a table with n attributes, you usually would have n values in the INSERT INTO .. SELECT in the order of definition. You have a table like the following:

Employee (name, address, employee_number, salary)

The following INSERT would fail because the Employee table has four attributes while the Emp1 table has only three:

INSERT INTO Emp1 SELECT * FROM Employee;

The following INSERT would also fail because the attribute order of the SELECT must match the order of definition of attributes in the Emp1 table:

INSERT INTO Emp1 SELECT address, employee_number, salary FROM Employee;

As with INSERT INTO .. VALUES, the INSERT INTO .. SELECT with no named attributes must match column for column and would look like the following:

INSERT INTO Emp1 SELECT address, salary, employee_number FROM Employee;

As you might guess from the last INSERT INTO .. SELECT example, you can load fewer attributes than the whole row of the Emp1 table using named attributes in the INSERT with a statement like:

INSERT INTO Emp1 (address, salary) SELECT address, salary FROM Employee;

However, this would leave the other attribute, employee_number, with a value of NULL or with a default value. Therefore, although loading less than a "full row" is syntactically correct, you must be aware of the result. One final point: INSERT INTO .. SELECT could succeed if the data-types of the SELECT matched the data-types of the attributes in the table to which you are INSERTing. For example, if you had another table called Emp2 with name, address as attributes (both defined as VARCHAR2), and if you executed the following, the statement could succeed, but you would have an address in a name attribute and vice versa:

INSERT INTO Emp2 SELECT address, name FROM Employee; **We say "could" here because there are ways to prevent integrity violations of this type, but we have not introduced them yet.

You do not have to copy all the names from Customer1 because you can restrict the SELECT as illustrated in the following example:

INSERT INTO Namelist SELECT cname FROM Customer1 WHERE amount > 100

You can populate the Namelist table with the cname from the Customer1 table as follows:

INSERT INTO Namelist SELECT cname FROM Customer1; This could copy all the names from Customer1 to Namelist.

INSERT INTO..SELECT Example, the following statement will insert all the values from the table Customer into another table called Newcustomer:

INSERT INTO Newcustomer SELECT * FROM Customer; Before using this above INSERT INTO .. SELECT statement, you would have had to first create the Newcustomer table; and, though the attributes of Newcustomer do not have to be named exactly what they are named in Customer, the data-types and sizes have to match. The size of the attributes that you are inserting into, that is, the size of the attributes of Newcustomer, have to be at least as big as the size of the attributes of Customer

INSERT INTO .. SELECT With the INSERT INTO ..VALUES option, you insert only one row at a time into a table. With the INSERT INTO .. SELECT option, you may (and usually do) insert many rows into a table at one time. The syntax of the INSERT INTO .. SELECT is:

INSERT INTO Table-name "SELECT clause"

ISSUING A COMMIT DURING A SESSION

If you issue a COMMIT during a session, your transaction ends at that point and a new one begins. Data definition commands contain implicit COMMITs - they end the current transaction and start a new one. Data definition commands we have seen are: CREATE TABLE and DROP TABLE. If either of these two commands are issued, an implied COMMIT ensues, the current transaction ends and a new transaction begins. Several transactions may take place within a single session.

An asterisk (*) in place of the "attributes" would mean "list all the attributes (or columns) of the table" (i.e., whole rows). An example of using the asterisk would be:

SELECT * FROM Student; where the "*" means return "all columns" from the table, Student,

Using BETWEEN The BETWEEN operator returns rows when a value occurs within a given range of values. The general syntax of the BETWEEN operator is:

SELECT ... FROM ... WHERE attribute BETWEEN value1 AND value2;

The simplest format of the SELECT with a WHERE clause would be:

SELECT attribute(s) FROM Table WHERE criteria; For example, to list the snames of only those students who are seniors we would type: SELECT sname FROM Student -- we add a row filter to show only seniors WHERE class = 4;

An Extended SELECT Statement The SELECT is usually the first word in a SQL statement. The SELECT statement instructs the database engine to return information from the database as a set of rows, a "result set." The SELECT displays the result on the computer screen, but does not save the results. The simplest form of the SELECT syntax is:

SELECT attributes FROM Table; A database consists of a collection of tables and each table consists of rows of data. The above statement returns a result set of zero or more rows which is drawn from specified columns (attributes) that are available in a table. In the above statement, "Table" is the name of the table in the database from which the data will be taken, and "attributes" are the selected columns (attributes) in the table. The keywords SELECT and FROM are always present in a SELECT statement in Oracle. For example, SELECT name, address FROM Old_Customer; would select the attributes name and address from the table, Old_Customer. The result set would consist of rows of names and addresses in no particular row-order. ***The table, Old_Customer, has not been created for you. You would have to create this table before you could try out this statement.

To show a listing of student names, we use the following query:

SELECT sname FROM Student;

It is not necessary to include all of the attributes used in the WHERE clause in the result set. It is a good idea to include the attributes when checking a query, but the following query is also legal:

SELECT sname -- this is allowable, but why not include class and major too? FROM Student WHERE class = 4 OR major = 'MATH';

If we want to find all the student rows with class values between 1 and 3 (inclusive), we could type:

SELECT sname, class FROM Student WHERE class -- class = 1, 2 and 3 will satisfy the WHERE condition BETWEEN 1 and 3;

In Oracle SQL, value1 has to be less than value2. Also note that the end points are included in the result set (BETWEEN is "inclusive"). The same "between result" could also be obtained using the query:

SELECT sname, class FROM Student WHERE class >=1 AND class <=3;

We may also order within an order. For example, suppose we type:

SELECT sname, major FROM Student ORDER BY major DESC, sname; Here the output is principally ordered by major in descending order and then by sname within major with the names in ascending order. ***The ascending order is the default order of the ORDER BY clause

To order the output in descending order, the keyword DESC can be appended to the appropriate attribute in the ORDER BY clause as follows:

SELECT sname, major FROM Student -- this time we ask for descending order ORDER BY sname DESC;

For example, the query below will show the names (snames) and majors in the Student table, ordered by sname. Here the output will be ordered in ascending order of sname because ascending order is the default of the ORDER BY clause.

SELECT sname, major FROM Student ORDER BY sname;

SELECTing Attributes[Columns] All of the attributes (columns) and/or all of the rows do not have to be retrieved with a SELECT statement. What is shown is called the result set. Attribute names may be SELECTed from a table, provided the exact name of the attribute is known. To find out the exact name of the attributes, we use "DESC tablename" (DESCribe) as we discussed in Chapter 1. For example, in our Student-Course database, we have a table called Student. And, we want to list all the student names from this Student table. First, we use DESC Student and find that the attribute name for "student name" is "sname":

SQL> DESC Student ** All SQL statements require semicolons or slashes to execute them. SQLPLUS commands do not require semicolons or a terminating character, but if you use one, SQLPLUS is usually forgiving and will execute the command correctly anyway. Hence, DESC does not require a semicolon, while the SELECT does.

INSERT INTO .. VALUES

The INSERT INTO with the VALUES option is a way of creating one row of a table. The following example inserts one row into the Names table: INSERT INTO Names VALUES ('Joe Smith'); where • INSERT is the name of the command. • INTO is a necessary keyword. • "Names" is the name of the existing table. • VALUES is another necessary keyword. • 'Joe Smith' is a string of letters in agreement with the data-type Note that 'Joe Smith' is surrounded by single quotes. "Joe Smith" would be invalid. ***Review Examples on pg 45, 46

Common Number Data-types

The most commonly used numeric data-types in Oracle are NUMBER and INTEGER. The NUMBER data- type, with no parentheses, defaults to a number that is up to 38 digits long with 8 decimal places. NUMBER may also be defined as having some maximum number of digits, such as NUMBER(5). Here, the (5) is referred to as the "precision" of the datatype and may be from 1 to 38. If a second number is included in the definition, for example NUMBER (12,2), the second number is called the "scale." The second number defines how many digits will come after a decimal point. Here, with NUMBER (12,2) we may have up to ten digits before the decimal point and two after. A very common datatype used in programming languages is type INTEGER. INTEGER holds whole numbers and is equivalent to NUMBER(38). Usually, you enter a precision and/or a scale for your numbers with entries such as NUMBER(3) or NUMBER(6,2). The NUMBER(3) implies you will have three digits and no decimal places. NUMBER(6,2) means that the numbers you store will be similar to 1234.56 or 12.34, with a decimal before the last two digits in a field that has a maximum of six numbers overall.

SELECTing Rows

The output of rows in the result set may be restricted by adding a WHERE clause to the SELECT. When the WHERE clause is used, the database engine selects the rows from the table that meet the conditions given in the WHERE clause. If no WHERE clause is used, the query will return all rows from the table. In other words, the WHERE clause acts as a "row filter."

Using ORDER BY

The result set (the output) of the above query contains all the sname values (student names) in the Student table. But, the sname rows in the result set are not ordered since a relational table does not guarantee that its rows are in any particular order. Rows in relational database tables are supposed to be mathematical sets (no particular ordering of rows and no duplicate rows); result sets are similar to mathematical sets in that no order is implied, but duplicates may occur). To show the contents of a table in a specific order, we can force the ordering of the result set using the ORDER BY clause in the SELECT.

It is often useful and appropriate to include a WHERE clause on the UPDATE statement so that values are set selectively. For example, the updating of a particular customer in our new table, Customer2, might be done with the following statement:

UPDATE Customer2 SET balance = 0 -- this updates only one customer WHERE cno = 101; This would update only customer 101's row(s). You could also set specific balances to zero with other criteria in the WHERE clause with a statement like the following: UPDATE Customer2 SET balance = 0 WHERE date > '01-JAN-19'; However, in this last example, multiple rows might be updated.

Now suppose some values are inserted into the table using one of the above techniques. And then suppose that you would like to set all balances in the new table to zero. You can do this with an UPDATE statement, as follows:

UPDATE Customer2 SET balance = 0; This statement sets all balances in all rows of the table to zero, regardless of their previous value. ***Beware—this can be a dangerous. Later in the chapter we will discuss a method in which to safeguard against accidental misuse using the ROLLBACK statement. ***End Warning***

SQL> ALTER TABLE Course_copy MODIFY offering_dept CHAR(2) SQL> / Will give:

modify offering_dept char(2) * ERROR at line 2: ORA-01441: cannot decrease column length because some value is too big.

COMMIT and ROLLBACK are explicit transaction-handling statements. _____.

that allow you to divide your work into separate transactions without signing off and on.y.

Inserting Values into a Created Table Values may be inserted into a created table using several methods. We will illustrate two of the three common ways to populate tables:

• INSERT INTO .. VALUES • INSERT INTO .. SELECT •SQLLOADER (for bulk loading of larger tables - we do this later) In this chapter we will look at INSERT INTO .. VALUES and INSERT INTO .. SELECT. We will discuss SQLLOADER later in the text because SQLLOADER is not a command per se, but rather a special Oracle procedure for loading tables.

All transactions have a beginning and an end. A common beginning point for a transaction is when you log on to the database. Provided you have not issued a command with an implied COMMIT, the end point of the transaction is when you sign off. You may also end a transaction by:

• logging off of your database session (an implied COMMIT) • issuing a command that contains an implied COMMIT (like the DROP TABLE command discussed in the previous section) • issuing an explicit COMMIT statement • executing a ROLLBACK statement.