Oracle Database 11g: SQL Fundamentals I PArt CO PY RI GH TE D MA TE RI AL I
Chapter 1 Introducing SQL Oracle Database 11g: SQL Fundamentals I exam objectives covered in this chapter: ÛÛ Retrieving Data Using the SQL SELECT Statement NN List the capabilities of SQL SELECT statements NN Execute a basic SELECT statement ÛÛ Restricting and Sorting Data NN Limit the rows that are retrieved by a query NN Sort the rows that are retrieved by a query NN Use ampersand substitution to restrict and sort output at runtime
Oracle 11g is a very powerful and feature-rich relational database management system (RDBMS). SQL has been adopted by most RDBMSs for the retrieval and management of data, schema creation, and access control. The American National Standards Institute (ANSI) has been refining standards for the SQL language for more than 20 years. Oracle, like many other companies, has taken the ANSI standard of SQL and extended it to include much additional functionality.
SQL Fundamentals 5 When you install Oracle software, you can choose the Basic Installation option and select the Create Starter Database check box. This database will have the sample schemas used in this book. The password you specify will be applicable to the SYS and SYSTEM accounts. The account SYS is the Oracle dictionary owner, and SYSTEM is a database administrator (DBA) account. Initially, the sample schemas are locked.
Chapter 1 n Introducing SQL Ta b l e 1 .1 Table 1.1 SQL Statement Categories (continued) SQL Category Description Data Definition Language (DDL) Used to define, alter, or drop database objects and their privileges.
SQL Fundamentals 7 Since the test is on SQL and the tool used throughout the book for executing SQL is SQL*Plus, I will discuss some fundamentals of SQL*Plus in this section. SQL*Plus, widely used by DBAs and developers to interact with the database, is a powerful tool from Oracle. Using SQL*Plus, you can execute all SQL statements and PL/SQL programs, format results from queries, and administer the database.
Chapter 1 n Introducing SQL execute at this prompt. With SQL*Plus, you can enter, edit, and execute SQL statements; perform database administration; and execute statements interactively by accepting user input. You can also format query results and perform calculations. sqlplus -help displays a help screen to show the various options available with starting SQL*Plus. To exit from SQL*Plus, use the EXIT command. On platforms where a return code is used, you can provide a return code while exiting.
SQL Fundamentals 9 SELECT 800 400 FROM dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected SQL> You need to put the hyphen in the next line for the query to succeed: SQL> SELECT 800 2 - 400 FROM dual; 800-400 ---------400 SQL> Getting Information with the DESCRIBE Command You can use the DESCRIBE command to get information about the database objects. Using DESCRIBE on a table or view shows the columns, its datatypes, and whether each column can be NULL.
Chapter 1 n Introducing SQL Editing the SQL Buffer The most recent SQL statement executed or entered is stored in the SQL buffer of SQL*Plus. You can run the command in this buffer again by simply typing a slash or using the RUN command. SQL*Plus provides a set of commands to edit the buffer. Suppose you want to add another column or add an ORDER BY condition to the statement in the buffer. You do not need to type the entire SQL statement again.
SQL Fundamentals 11 CHANGE The CHANGE /old/new command changes an old entry to a new entry. The abbreviated command is C. If you omit new, old will be deleted. SQL> 2* SQL> 2* SQL> C /<>/= FROM emp WHERE empno = 7926 C /7926 FROM emp WHERE empno = INPUT The INPUT text command adds a line of text. Its abbreviation is I. If text is omitted, you can add as many lines you want.
Chapter 1 n Introducing SQL SQL> L 1 SELECT empno, ename 2* FROM emp WHERE empno = SQL> CLEAR BUFFER The CLEAR BUFFER command (abbreviated CL BUFF) clears the buffer. This deletes all lines from the buffer. SQL> L 1 SELECT empno, ename 2* FROM emp WHERE empno = SQL> CL BUFF buffer cleared SQL> L No lines in SQL buffer. SQL> Using Script Files SQL*Plus provides commands to save the SQL buffer to a file, as well as to run SQL statements from a file.
SQL Fundamentals E x e r c is e 1 . 1 Practicing SQL*Plus File Commands In this exercise, you will learn how to edit the SQL*Plus buffer using various buffer edit commands. 1. Enter the following SQL; the third line is a blank line so that the SQL is saved in the buffer: SQL> SELECT employee_id, first_name, last_name 2 FROM employees 3 SQL> 2. List the SQL buffer: SQL> L 1 SELECT employee_id, first_name, last_name 2* FROM employees SQL> 3.
Chapter 1 14 n Introducing SQL E x e r c is e 1 . 1 ( c o n t i n u e d ) 2 FROM employees 3* WHERE employee_id = 106 SQL> 8. List the buffer to verify its contents: SQL> 1 2 3* SQL> 9. LI SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = 106 Change the employee number from 106 to 110: SQL> C/106/110 3* WHERE employee_id = 110 SQL> 10. Save the buffer again to the same file: SQL> SAVE myfile SP2-0540: File “MYFILE.sql” already exists. Use “SAVE filename[.
SQL Fundamentals 15 E x e r c is e 1 . 1 ( c o n t i n u e d ) SQL> SAVE myfile APPEND Appended file to MYFILE.sql SQL> @MYFILE EMPLOYEE_ID FIRST_NAME LAST_NAME ----------- -------------------- --------110 John Chen EMPLOYEE_ID FIRST_NAME LAST_NAME ----------- -------------------- --------106 Valli Pataballa SQL> Saving Query Results to a File You can use the SPOOL filename command to save the query results to a file. By default, the SPOOL command creates an .lst file extension.
Chapter 1 n Introducing SQL the column. Oracle has several built-in datatypes to store different kinds of data. In this section, I will go over the built-in datatypes available in Oracle 11g. Detailed discussion on datatypes as well as creating and maintaining tables are discussed in Chapter 6, “Creating Tables and Constraints.” When you create a table to store data in the database, you need to specify a datatype for all the columns you define in the table.
SQL Fundamentals 17 VARCHAR2() The VARCHAR2 datatype is a variable-length alphanumeric string, which has a maximum length in bytes (to specify the length in characters, use the CHAR keyword inside parentheses along with a size; see Chapter 6). VARCHAR2 columns require only the amount of space needed to store the data and can store up to 4,000 bytes. There is no default size for the VARCHAR2 datatype. An empty VARCHAR2(2000) column takes up as much room in the database as an empty VARCHAR2(1) column.
Chapter 1 n Introducing SQL Ta b l e 1 . 3 Precision and Scale Examples Value Datatype Stored Value Explanation 123.2564 NUMBER 123.2564 The range and precision are set to the maximum, so the datatype can store any value. 1234.9876 NUMBER(6,2) 1234.99 Since the scale is only 2, the decimal part of the value is rounded to two digits. 12345.12345 NUMBER(6,2) Error The range of the integer part is only from –9999 to 9999.
SQL Fundamentals 19 Ta b l e 1 . 3 Precision and Scale Examples (continued) Value Datatype Stored Value Explanation 0.09999 NUMBER(4,5) 0.09999 Stored as it is; only four digits after the decimal point and zero. 0.099996 NUMBER(4,5) Error Rounding this value to four digits after the decimal and zero results in 0.1, which is outside the range. DATE The DATE datatype is used to store date and time information.
Chapter 1 20 n Introducing SQL Operators and Literals An operator is a manipulator that is applied to a data item in order to return a result. Special characters represent different operations in Oracle (+ represents addition, for example). Operators are commonly used in all programming environments, and you should already be familiar with the following operators, which may be classified into two types: Unary operator A unary operator has only one operand. Examples are +2 and –5.
SQL Fundamentals 21 or a NULL with another string results in a string, not a NULL (NULL in Oracle 11g represents unknown or missing data). Two vertical bars (||) are used as the concatenation operator. Here are two examples: ‘Oracle11g’ || ‘Database’ results in ‘Oracle11gDatabase’. ‘Oracle11g ‘ || ‘Database’ results in ‘Oracle11g Database’. Operator Precedence If multiple operators are used in the same expression, Oracle evaluates them in the order of precedence set in the database engine.
Chapter 1 22 n Introducing SQL literal is 4,000 bytes. Single quotation marks can be included in the literal text value by preceding it with another single quotation mark. Here are some examples of text literals: ‘The Quick Brown Fox’ ‘That man’’s suit is black’ ‘And I quote: “This will never do.” ‘ ‘12-SEP-2001’ Alternatively, you can use Q or q quoting, which provides a range of delimiters.
Writing Simple Queries NN 1.5f NN –34.567D NN –4d NN –4.0E+0 23 Datetime Literals You can specify a date value as a string literal using the datetime literals. The most common methods to represent the datetime values are to use the conversion function TO_DATE or TO_TIMESTAMP with the appropriate format mask. For completeness of literals, I will discuss the datetime literals briefly.
Chapter 1 n Introducing SQL from a single table or from multiple tables. Queries using multiple tables are discussed in later chapters. Using the SELECT Statement The SELECT statement is the most commonly used statement in SQL. It allows you to retrieve information already stored in the database. The statement begins with the keyword SELECT, followed by the column names whose data you want to query.
Writing Simple Queries MK_MAN MK_REP HR_REP PR_REP Marketing Manager Marketing Representative Human Resources Representative Public Relations Representative 9000 4000 4000 4500 25 15000 9000 9000 10500 19 rows selected. The keywords, column names, and table names are case insensitive. Only literals enclosed in single quotation marks are case sensitive in Oracle.
Chapter 1 n Introducing SQL Column Alias Names The column alias name is defined next to the column name with a space or by using the keyword AS. If you want a space in the column alias name, you must enclose it in double quotation marks. The case is preserved only when the alias name is enclosed in double quotation marks; otherwise, the display will be uppercase.
Writing Simple Queries 27 DEPARTMENT_ID ------------100 30 20 70 90 110 50 40 80 10 60 12 rows selected. To demonstrate that uniqueness is enforced across the row, let’s do one more query using the SELECT DISTINCT clause.
Chapter 1 n Introducing SQL SELECT * FROM TAB; shows all the tables and views in your schema. Don’t be alarmed if you see a table name similar to BIN$PJV23QpwQfu0zPN9uaXw+w==$0. These are tables that belong to the Recycle Bin (or dropped tables). The tasks of creating tables and managing tables are discussed in Chapter 6. The DUAL Table The DUAL table is a dummy table available to all users in the database. It has one column and one row.
Writing Simple Queries 29 are returned or operated upon where the data satisfies the logical condition(s) of the WHERE clause. You can use column names or expressions in the WHERE clause, but not column alias names. The WHERE clause follows the FROM clause in the SELECT statement.
Chapter 1 n Introducing SQL !=, <>, or ^= (Inequality) You can use any one of these three operators to test for inequality. The test evaluates to TRUE if the values on both sides of the operator do not match. SELECT first_name || ‘ ‘ || last_name “Name”, commission_pct FROM employees WHERE commission_pct != .35; Name COMMISSION_PCT ------------------------------------------ -------------John Russell .4 Karen Partners .3 Alberto Errazuriz .3 Gerald Cambrault .3 … … … … … … Jack Livingston .
Writing Simple Queries 31 > (Greater Than) The > operator evaluates to TRUE if the left side (expression or value) of the operator is greater than the right side of the operator. SELECT first_name || ‘ ‘ || last_name “Name”, commission_pct FROM employees WHERE commission_pct > .35; Name COMMISSION_PCT ------------------------------------------ -------------John Russell .
Chapter 1 32 n Introducing SQL Name COMMISSION_PCT ------------------------------------------ -------------John Russell .4 Janette King .35 Patrick Sully .35 Allan McEwen .35 ANY or SOME You can use the ANY or SOME operator to compare a value to each value in a list or subquery. The ANY and SOME operators always must be preceded by one of the following comparison operators: =, !=, <, >, <=, or >=.
Writing Simple Queries 33 Logical Operators Logical operators are used to combine the results of two comparison conditions (compound conditions) to produce a single result or to reverse the result of a single comparison. NOT, AND, and OR are the logical operators. When a logical operator is applied to NULL, the result is UNKNOWN. UNKNOWN acts similarly to FALSE; the only difference is that NOT FALSE is TRUE, whereas NOT UNKNOWN is also UNKNOWN. NOT You can use the NOT operator to reverse the result.
Chapter 1 34 n Introducing SQL OR The OR operator evaluates to TRUE if either operand is TRUE. It evaluates to FALSE if both operands are FALSE. Otherwise, it returns NULL. SELECT FROM WHERE OR first_name, last_name employees first_name = ‘Kelly’ last_name = ‘Smith’; FIRST_NAME -------------------Lindsey William Kelly LAST_NAME ------------------------Smith Smith Chung Logical Operator Truth Tables The following tables are the truth tables for the three logical operators. Table 1.
Writing Simple Queries 35 Table 1.9 is the truth table for the NOT operator. Ta b l e 1 . 9 NOT Truth Table NOT TRUE FALSE FALSE TRUE UNKNOWN UNKNOWN Other Operators In the following sections, I will discuss all the operators that can be used in the WHERE clause of the SQL statement that were not discussed earlier. IN and NOT IN You can use the IN and NOT IN operators to test a membership condition.
Chapter 1 n Introducing SQL FIRST_NAME -------------------Michael Pat Hermann SQL> LAST_NAME DEPARTMENT_ID ---------------------- ------------Hartstein 20 Fay 20 Baer 70 When using the NOT IN operator, if any value in the list or the result returned from the subquery is NULL, the NOT IN condition is evaluated to FALSE. For example, last_name not in (‘Smith’, ‘Thomas’, NULL) evaluates to last_name != ‘Smith’ AND last_name != ‘Thomas’ AND last_name != NULL.
Writing Simple Queries WHERE AND 37 d.department_id = e.department_id d.department_name = ‘Administration’); LAST_NAME FIRST_NAME DEPARTMENT_ID ---------------------- -------------------- ------------Whalen Jennifer 10 SQL> IS NULL and IS NOT NULL To find the NULL values or NOT NULL values, you need to use the IS NULL operator. The = or != operator will not work with NULL values. IS NULL evaluates to TRUE if the value is NULL. IS NOT NULL evaluates to TRUE if the value is not NULL.
Chapter 1 38 n Introducing SQL FIRST_NAME -------------------Sundar Sundita Susan LAST_NAME ------------------------Ande Kumar Mavris The following example looks for all JOB_ID values that begin with AC_. Since _ is a pattern-matching character, you must qualify it with an escape character. Oracle does not have a default escape character.
Writing Simple Queries default. The ORDER BY clause follows the FROM clause and the WHERE clause in the SELECT statement. To retrieve all employee names of department 90 from the EMPLOYEES table ordered by last name, use this query: SELECT first_name || ‘ ‘ || last_name “Employee Name” FROM employees WHERE department_id = 90 ORDER BY last_name; Employee Name ---------------------------------------------Lex De Haan Steven King Neena Kochhar SQL> You can specify more than one column in the ORDER BY clause.
Chapter 1 n Introducing SQL You can use column alias names in the ORDER BY clause. If the DISTINCT keyword is used in the SELECT clause, you can use only those columns listed in the SELECT clause in the ORDER BY clause. If you have used any operators on columns in the SELECT clause, the ORDER BY clause also should use them.
Writing Simple Queries Nancy Daniel John Ismael Jose Manuel Luis William 17-AUG-94 16-AUG-94 28-SEP-97 30-SEP-97 07-MAR-98 07-DEC-99 07-JUN-94 12000 9000 8200 7700 7800 6900 8300 41 101 108 108 108 108 108 205 8 rows selected. The ORDER BY clause cannot have more than 255 columns or expressions. Sorting NULLs By default, in an ascending-order sort, the NULL values appear at the bottom of the result set; that is, NULLs are sorted higher.
Chapter 1 n Introducing SQL LAST_NAME COMMISSION_PCT ------------------------- -------------Rogers Raphaely Rajs Russell .4 SQL> Why Do You Limit and Sort Rows? The power of an RDBMS and SQL lies in getting exactly what you want from the database. The sample tables you considered under the HR schema are small, so even if you get all the information from the table, you can still find the specific data you’re seeking.
Writing Simple Queries CUSTOMER# BACK_ORDER ORD_STATUS TOTAL_AMT SALES_TAX 43 NOT NULL VARCHAR2 (12) CHAR (1) CHAR (1) NOT NULL NUMBER (18,4) NUMBER (12,2) The objective of the query is to find the completed orders that do not have any sales tax. You want to see the order number and total amount of the order. The corresponding columns that appear in the SELECT clause are ORDER# and TOTAL_AMT.
Chapter 1 n Introducing SQL parentheses is evaluated first. Here is an example of a compound expression: ((2*4)/ (3+1))*10. The result of 2*4 is divided by the result of 3+1. Then the result from the division operation is multiplied by 10. The CASE Expression You can use the CASE expression to derive the IF…THEN…ELSE logic in SQL.
Writing Simple Queries 45 The following example categorizes the salary as Low, Medium, and High using a searched CASE expression: SELECT first_name, department_id, salary, CASE WHEN salary < 6000 THEN ‘Low’ WHEN salary < 10000 THEN ‘Medium’ WHEN salary >= 10000 THEN ‘High’ END Category FROM employees WHERE department_id <= 30 ORDER BY first_name; FIRST_NAME DEPARTMENT_ID SALARY CATEGO -------------------- ------------- ---------- -----Alexander 30 3100 Low Den 30 11000 High Guy 30 2600 Low Jennifer 10 440
Chapter 1 n Introducing SQL The following query may return several rows depending on the activity and number of users connected to the database: SELECT username, sid, serial#, program FROM v$session; If you’re using SQL*Plus, you may have to adjust the column width to fit the output in one line: COLUMN program FORMAT a20 COLUMN username FORMAT a20 SELECT username, sid, serial#, program FROM v$session; USERNAME SID SERIAL# PROGRAM -------------------- ---------- ---------- ----------------118 6246 O
Accepting Values at Runtime 47 WHERE username is NOT NULL ORDER BY logon_time; USERNAME SID SERIAL# PROGRAM -------------------- ---------- ---------- --------------DBSNMP 148 608 emagent.exe DBSNMP 124 23310 emagent.exe BTHOMAS 121 963 sqlplus.exe SCOTT 132 23 TOAD.EXE SJACOB 231 32 discoverer.exe Accepting Values at Runtime To create an interactive SQL statement, you can define variables in the SQL statement.
Chapter 1 n Introducing SQL to provide a value. The variable will always have the CHAR datatype associated with it. Here is an example of defining a substitution variable: SQL> DEFINE DEPT = 20 SQL> DEFINE DEPT DEFINE DEPT = “20” (CHAR) SQL> LIST 1 SELECT department_name 2 FROM departments 3* WHERE department_id = &DEPT SQL> / old 3: WHERE DEPARTMENT_ID = &DEPT new 3: WHERE DEPARTMENT_ID = 20 DEPARTMENT_NAME --------------Marketing 1 row selected.
Accepting Values at Runtime 49 The old line with the variable and the new line with the substitution are displayed. You can turn off this display by using the command SET VERIFY OFF. Saving a Variable for a Session Consider the following SQL, saved to a file named ex01.sql. When you execute this script file, you will be prompted for the COL1 and COL2 values multiple times: SQL> SELECT &COL1, &COL2 2 FROM &TABLE 3 WHERE &COL1 = ‘&VAL’ 4 ORDER BY &COL2 5 SQL> SAVE ex01 Created file ex01.
Chapter 1 n Introducing SQL To clear a defined variable, you can use the UNDEFINE command. Let’s edit the ex01.
Summary 51 DEPARTMENT_NAME DEPARTMENT_ID ------------------------------ ------------Administration 10 1 row selected. SQL> If you save the SQL as a script file, you can submit the substitution-variable values while invoking the script (as command-line arguments). Each time you run this command file, START replaces each &1 in the file with the first value (called an argument) after START filename, then replaces each &2 with the second value, and so forth.
Chapter 1 n Introducing SQL SQL*Plus supports all SQL statements and has its own formatting and enhancement commands. Using this tool, you can produce interactive SQL statements and formatted reports. SQL*Plus is the command-line interface to the database widely used by DBAs. SQL*Plus has its own buffer where SQL statements are buffered. You can edit the buffer using SQL*Plus editing commands. The DESCRIBE command is used to get information on a table, view, function, or procedure.
Review Questions Review Questions 1. You issue the following query: SELECT salary “Employee Salary” FROM employees; How will the column heading appear in the result? A. EMPLOYEE SALARY 2. B. EMPLOYEE_SALARY C. Employee Salary D. employee_salary The EMP table is defined as follows: Column Datatype Length EMPNO NUMBER 4 ENAME VARCHAR2 30 SALARY NUMBER 14,2 COMM NUMBER 10,2 DEPTNO NUMBER 2 You perform the following two queries: 1. SELECT empno enumber, ename FROM emp ORDER BY 1; 2.
Chapter 1 54 3. n Introducing SQL You issue the following SELECT statement on the EMP table shown in question 2. SELECT (200+((salary*0.1)/2)) FROM emp; What will happen to the result if all the parentheses are removed? A. No difference, because the answer will always be NULL. 4. B. No difference, because the result will be the same. C. The result will be higher. D. The result will be lower. In the following SELECT statement, which component is a literal? (Choose all that apply.
Review Questions 9.
Chapter 1 56 n Introducing SQL 11. Refer to the EMP table in question 2. When you issue the following query, which line has an error? 1. 2. 3. 4. 5. SELECT empno “Enumber”, ename “EmpName” FROM emp WHERE deptno = 10 AND “Enumber” = 7782 ORDER BY “Enumber”; A. 1 B. 5 C. 4 D. No error; the statement will finish successfully. 12. You issue the following query: SELECT empno, ename FROM emp WHERE empno = 7782 OR empno = 7876; Which other operator can replace the OR condition in the WHERE clause? A.
Review Questions D. SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE ‘DI\_’ ESCAPE ‘\’; E. SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE ‘DI_%’ ESCAPE ‘\’; 57 15. COUNTRY_NAME and REGION_ID are valid column names in the COUNTRIES table. Which one of the following statements will execute without an error? A. SELECT country_name, region_id, CASE region_id = 1 THEN ‘Europe’, region_id = 2 THEN ‘America’, region_id = 3 THEN ‘Asia’, ELSE ‘Other’ END Continent FROM countries; B.
Chapter 1 58 n Introducing SQL What will be the value in the first row of the result set when the following query is executed? SELECT hire_date FROM employee ORDER BY salary, emp_name; A. 02-APR-91 B. 17-DEC-90 C. 28-SEP-91 D. The query is invalid, because you cannot have a column in the ORDER BY clause that is not part of the SELECT clause. 18.
Review Questions 59 20. Column alias names cannot be used in which clause? A. SELECT clause B. WHERE clause C. ORDER BY clause D. None of the above 21. What is wrong with the following statements submitted in SQL*Plus? DEFINE V_DEPTNO = 20 SELECT LAST_NAME, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = V_DeptNo; A. Nothing is wrong. The query lists the employee name and salary of the employees who belong to department 20. B. The DEFINE statement declaration is wrong. C.
Chapter 1 60 n Introducing SQL A. SELECT * FROM products WHERE shelf like ‘%BL’ ORDER BY available_qty SORT DESC; B. SELECT * FROM products WHERE shelf like ‘BL%’; C. SELECT * FROM products WHERE shelf = ‘BL%’ ORDER BY available_qty DESC; D. SELECT * FROM products WHERE shelf like ‘BL%’ ORDER BY available_qty DESC; E. SELECT * FROM products WHERE shelf like ‘BL%’ ORDER BY available_qty SORT; 24.
Answers to Review Questions 61 Answers to Review Questions 1. C. Column alias names enclosed in quotation marks will appear as typed. Spaces and mixed case appear in the column alias name only when the alias is enclosed in double quotation marks. 2. A. Statements 1 and 2 will produce the same result. You can use the column name, column alias, or column position in the ORDER BY clause. The default sort order is ascending.
Chapter 1 n Introducing SQL 14. C. Since _ is a special pattern-matching character, you need to include the ESCAPE clause in LIKE. The % character matches any number of characters including 0, and _ matches a single character. 15. C. A CASE expression begins with the keyword CASE and ends with the keyword END. 16. D. An asterisk (*) is used to denote all columns in a table. 17. B. The default sorting order for a numeric column is ascending.
