Department of Computer and Information Science, School of Science, IUPUI

1. Department of Computer and Information Science,School of Science, IUPUI Basic SQL and SQLPlus - Querying using SELECT Dale Roberts, Lecturer Computer Science, IUPUI E-mail: droberts@cs.iupui.edu

2. Relational Data Model • RDBMS’s are based on the Relational Data Model. • A data model is a notation for describing data. The notation generally covers conceptually how data is structured (records, structures, relations, etc.), operations on the data (queries and modifiers), and constraints (domain, cross references). • The two preeminent data models are • Relational – all commercial databases • XML – added feature to most databases (This Relational Data Model discussion comes from the Ullman text)

3. Relational Terminology • The Relational Data Model is based on relations. The relation Movies (our class standards would name this Movie.) • Within the Relational Data Model, the columns are called attributes, and the rows are called tuples. • The name of the relation and the set of attributes is called the schema of the relation: • Movies(title, year, length, genre)

4. Attributes and Tuples • Attributes are a set, not a list. However, there is a “standard” order of attributes that are used for display when another order is not specified. • Other than the header row, the data is a tuple where each tuple has one component per attribute. (Gone With the Wind, 1939, 231, drama) • Each attribute must be atomic, meaning that each attribute must be an elementary data type like integer or string. They cannot be structured data types.

5. Relation Instances • The tuples that comprise a relation are not static over time. Tuples are inserted as new movies come out, and updated as data changes. • Each set of tuples represents an instance of the relation. • A conventional database system only maintains one version of the tuples that are currently in the relation. This is the current instance.

6. Keys of a Relation • A set of attributes forms a key of a relation if no two tuples can have the same values. • A key is an example of a constraint. The DBMS supports many constraints, but the key constraint is so important it is discussed as part of the relation definition. • Underlined attributes identify the key. The attributes title and year together can be used to identify a single Moviestuple. Movies(title, year, length, genre)

7. Defining a Relational Schema in SQL • Terminology shift: • Relation → Table or View • Attribute → Column • Tuple → Row • A stored relation it a table. • A dynamically constructed relation is a view. • Temporary tables are created and dropped by the database engine as needed during queries and data modifications.

8. Defining a Relational Schema in SQL • The CREATE keyword is used in SQL to create tables and views. CREATE TABLE Movies( title CHAR(100), year INT, length INT, genre CHAR(10), studioName CHAR(30), producerC# INT, PRIMARY KEY (title, year) ); CREATE TABLE Movie( title_TXT VARCHAR2(100), year_NBR NUMBER(4), length_NBR NUMBER(3), genre_CD VARCHAR2(10), studio_Name VARCHAR2(30), producer_CERT_NBR NUMBER(9), PRIMARY KEY (title_TXT , year_NBR ) ); Standard SQL Typical Enterprise Oracle SQL

9. Using SELECT with Relations • The SQL SELECT statement is used to query relations. • SELECT statements return a result set, which itself is a relation. • Because the SELECT operation is closed over the set relations, the SELECT statements can be nested to perform complex queries. N R1 R3 SELECT M R2 M x N

10. Using SELECT with Relations • SELECT statements can be nested, and the SELECT statement itself allows many tables to participate. • SELECT always produces a single result set. • The relations can be tables or views or temporary tables. R1 R3 SELECT R2 R1 R5 SELECT R4

11. Relation Compatibility • SELECT statements return a result set with shape M x N. • If M=1, the result set is compatible with a list (IN keyword). • If N=1, M=1, the result set is compatible with a single value. 1 M 1 1

12. The Basic Parts of Speech in SQL • SQL is a language. Oracle7 SQL is a superset of the American National Standards Institute (ANSI) and the International Standards Organization (ISO) SQL92 standard at entry level conformance. • PL/SQL is Oracle’s procedural language extension to SQL. It allows you to link several SQL commands through procedural language. • SQL*Plus (SQLPLUS from command line) is a tool that allows users to interact with Oracle. SQL*Plus enables you to manipulate SQL commands and PL/SQL blocks, and to perform many additional tasks as well. Through SQL*Plus, you can: • enter, edit, store, retrieve, and run SQL commands and PL/SQL blocks • format, perform calculations on, store, and print query results in the form of reports • list column definitions for any table • access and copy data between SQL databases • send messages to and accept responses from an end user

13. The Basic Parts of Speech in SQL • SQL*Plus,… very quick overview • first, create/run a script to start an Oracle database instance • from the command line:sqlplus or sqlpluswSQL> • Common commands in SQL*Plus: • get <filename> • @<filename> • save <filename> • list or l • change or c • edit • save • ! (shell to OS) • Capitalization generally does not matter

14. The Basic Parts of Speech in SQL • Simple Select Examples • Select statement, single table query, all rows: • SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • ORDER BY CUSTOMER_NUMBER; • ... specific rows (where clause): • SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE CUSTOMER_NUMBER > 500; • ... with a calculation: • SELECT CUSTOMER_NUMBER, LAST, (CREDIT_LIM - BALANCE) • FROM CUSTOMER • WHERE CUSTOMER_NUMBER > 500; • ... multiple where conditions (and): • SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE BALANCE >= 500 • AND BALANCE <= 1000;

15. The Basic Parts of Speech in SQL • The SELECT clause: • can be a list of columns, • an asterisk * for all columns, • a calculation (or other expressions), • group functions (chapter 9), • distinct keyword -- removes duplicates. • SELECT DISTINCT SUPPLIER • FROM PRODUCTS; • column names can be renamed in the output with an alias.SELECT CUSTOMER_NUMBER, LAST, FIRST, (CREDIT_LIM - BALANCE) AS REMAINING_CREDIT • FROM CUSTOMER; • The FROM clause: • in single tables queries, just provide your table’s name …. more on this in multi-table queries.

16. The Basic Parts of Speech in SQL • The WHERE Clause: • this is a logical, boolean expression which must evalute to true for each row in the query’s output. • where clause comparison operators: • = equal to • <, > less than, greater than • <=, >= less than or equal, greater than or equal • <>, != , ^= not equal • compound conditions are built with the boolean operators AND, OR. • AND is evaluated first by default. Use parenthesis ( ) to force the order of OR/AND. • a compliment of a condition can be evaluated with NOT. Examples below are equivalent: • SELECT PART_DESCRIPTION FROM PART WHERE WAREHOUSE_NUMBER != ‘3’; • SELECT PART_DESCRIPTION FROM PART WHERE NOT (WAREHOUSE_NUMBER=‘3’);

17. The Basic Parts of Speech in SQL • The WHERE Clause (continued): • BETWEEN keyword is the same as >= and =<. For example, these 2 statements are the same: SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE 500 <= BALANCE AND BALANCE <= 1000; • SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE BALANCE BETWEEN 500 AND 1000; • LIKE keyword is used for pattern matching. ‘%’ is for any number of characters.‘_’ (underscore) is for one character. • SELECT CUSTOMER_NUMBER, LAST, FIRST • FROM CUSTOMER WHERE LAST LIKE ‘JONE%’; • SELECT CUSTOMER_NUMBER, LAST, FIRST • FROM CUSTOMER WHERE LAST LIKE ‘JONE_’;

18. The Basic Parts of Speech in SQL • The WHERE Clause (continued): • IN keyword provides a list of numbers or strings to compare to. This is similar to using OR with =. For example, these 2 statements are the same: SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE CUSTOMER_NUMBER IN (10, 11, 12); • SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE (CUSTOMER_NUMBER = 10) • OR (CUSTOMER_NUMBER = 11) • OR (CUSTOMER_NUMBER = 12); • NULL and NOT NULL keywords:NULL does not mean 0, null means no value!Example- this statement only gets customers whom we have a complete name for: SELECT CUSTOMER_NUMBER, LAST, FIRST FROM CUSTOMER WHERE (FIRST IS NOT NULL) AND (LAST IS NOT NULL);

19. The Basic Parts of Speech in SQL • The WHERE Clause (continued): • Values can also be compared to another query in the where clause. This is called a subquery. For example: SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE CUSTOMER_NUMBER IN (SELECT CUSTOMER_NUMBER FROM CUSTOMER WHERE BALANCE > 500); • This however may not work sometimes with a subquery: SELECT CUSTOMER_NUMBER, LAST, FIRST, BALANCE • FROM CUSTOMER • WHERE CUSTOMER_NUMBER = (SELECT CUSTOMER_NUMBER FROM CUSTOMER WHERE BALANCE > 500);because subqueries return sets of values, not a single value.

20. The Basic Parts of Speech in SQL • The WHERE Clause (continued): • Tables can be joined by common attributes. This is done in the where clause. SELECT WEATHER.CITY, CONDITION, TEMPERATURE, LATITUDE,. . . FROM WEATHER, LOCATION WHERE WEATHER.CITY = LOCATION.CITY; • A shortcut (not in book). Tablenames can be aliased to save typing, reduce wordiness. SELECT W.CITY, CONDITION, TEMPERATURE, LATITUDE,. . . FROM WEATHER W, LOCATION L WHERE W.CITY = L.CITY; • Joining data from different tables is one of the more powerful parts of SQL. It is not without its dangers. • Joining tables the wrong way (not on common attributes), the wrong data comes back. • Forgetting to specify common keys in the where clause returns a “cartesian product” (each row of both tables joined to each other, m times n rows)

21. The Basic Parts of Speech in SQL • The ORDER BY Clause: • Specifies the ordering of the returned data. • Multiple columns can be selected to sort on. • ASC is ascending order order, default. • DESC is descending sort order. SELECT FEATURE, SECTION, PAGE • FROM NEWSPAPER • WHERE SECTION = ‘F’ ORDER BY PAGE DESC, FEATURE ASC; • Views: • Makes a query act similar to a table. • Views are “describe”-able, “select”-able • The view’s select statement is run everytime rows are selected from the view--ie data is not saved somewhere (like a table) • CREATE VIEW INVASION AS • SELECT W.CITY, CONDITION, TEMPERATURE, LATITUDE FROM WEATHER W, LOCATION L WHERE W.CITY = L.CITY;

22. Acknowledgements • Loney, Kevin. Oracle Database 10g The Complete Reference. • McFadden and Hoffer. Database Management, pp. 214-216. • Ullman, Jeffrey and Widom, Jennifer. A First Course in Database Systems.