Database Design

1. Database Design Sections 12 & 15- Introduction to Application Express (APEX) SQL editor, Introduction to SQL statements

2. Using APEX SQL editor • You may either type the command into the SQL editor or use the cut and paste option • If you are going to cut/paste the command copy the command from the word or PowerPoint document into NotePad. This will drop out hidden characters. • You may need to do some editing to remove extra characters, or replace incorrect characters. • Next copy the command from the NotePad into the editor Marge Hohly

3. Enter SQL command Marge Hohly

4. Display the Table structure • Enter the following command: • DESCRIBE music; • The structure of the table should be shown. • Results on next slide Marge Hohly

5. Music Table Structure Marge Hohly

6. Select command structure • SELECT field1, field2, field3FROM table_nameWHERE condition; • Try the following commandSELECT employee_id, first_name, last_name, department_idFROM employees; • SELECT employee_id, first_name, last_name, department_idFROM employeesWHERE department_id = 90; Marge Hohly

7. Resulting Screen Marge Hohly

8. SQL DESCRIBE • DESCRIBE <table name>; • DESCRIBE employees;Try the last statement. Marge Hohly

9. Inserting Data • INSERT INTO tablename (column1, column2,....)VALUES(value1,value2,...); • Remember character data needs to be enclosed in single quotes. Marge Hohly

10. Insert Data • Note the data types for each column • Inserting data into the table. • Since the table is empty all fields need to be populated, so column names can be omitted • INSERT INTO musicVALUES (10,'Marge Hohly','Folk'); • This will insert one record into the table • Next display the contents of the table to view the data • SELECT *FROM music; Marge Hohly

11. Insert Data into table • INSERT INTO <table name>VALUES (value 1, value 2, value 3, etc); • INSERT INTO musicVALUES (10,'Marge Hohly','Folk'); Marge Hohly

12. Typical error messages • The following statement has a spelling error: • SELCT *FROM employees;The error message is:       ORA-00900: invalid SQL statement • The following statement incorrectly names the table employee instead of employees: • SELECT *FROM employee;The error message is:       ORA-00942: table or view does not exist • Run the correct statement. Marge Hohly

13. Subset of data WHERE clause • SELECT <column name 1, column name 2, etc.> FROM <table name>WHERE <condition>; • SELECT first_name, last_name, salaryFROM employeesWHERE salary > 5000; Marge Hohly

14. ALTER table structure • ALTER TABLE <table name>ADD (<new_column_name> <data type>); • Try to modify the structure of the MUSIC table • Add a column (which you will misspell) COUNTRI instead of COUNTRY). • ALTER TABLE my_musicADD (countri VARCHAR2(20)); Marge Hohly

15. Deleting a column from a table • ALTER TABLE <table name>DROP COLUMN <column_name>; • Now delete the column (COUNTRI) you just added. Marge Hohly

16. Deleting a row • DELETE from <table name>WHERE <column_name> = 'some value' ; • DELETE from musicWHERE musicid = 10; • DELETE from musicWHERE type = ‘Folk’; Marge Hohly

17. Database Facts • Currently 20% of the world's data resides in relational RDBMSs. • In the next two years, databases are expected to grow larger than 100 terabytes. A database this big would be able to store 100,000 copies of the Encyclopedia Britannica or 200,000 hours of music or about 10 billion web pages. • The top 10 world's largest databases using the Oracle RDBMS are: • France Telecom, 29.2TB -- a communications company (a TB is a terabyte equivalent to 1,000 gigabytes) • Amazon.com with, 13 TB -- selling books and merchandise • The Claria Corporation,12TB -- Internet behavioral marketing company tracking Internet user behavior Marge Hohly

18. Oracle Facts • One of Oracles first customers was the CIA, for which they created a relational database management system. • Much of Oracles early success was owed to picking up IBM as a major customer. • 2nd half of the ‘90s was spend retooling Oracle products in line with that online strategy (the internet) • Ellison said in 1998: “If the internet turns out not to be the future of computing, we’re toast. But if it is, we’re golden.” • Oracle was the first enterprise software to fully embrace the Java Programming language back in 1998. • Oracle has purchased 57 different companies in the last 5 years. • The buying frenzy was launched with the $10.3 billion acquisition of PeopleSoft in 2005, which put Oracle squarely in the ERP software fray. Marge Hohly

19. Oracle Facts • The latest mega-purchase: Sun Microsystems, a $7.4 billion acquisition due to close this year if regulators don't object. • Oracle today now has 345,000 customers worldwide. • Oracle employs more than 73,201 people around the globe. • Some of the top 10 world’s largest databases using Oracle RDBMS are: • France Telecom, 29.2 TB – a communications company (a TB is a terebyte equivalent to 1,000 gigabytes) • Amazon.com with, 13TB – selling books and merchandise • The Clarian Corporation, 13TB – Internet behavioral marketing company tracking Internet user behavior Marge Hohly

20. Application Express SQL editor • The SQL course will use the three following sets of database tables for examples and practice exercises. • Oracle tables: COUNTRIES, REGIONS, DEPARTMENTS, EMPLOYEES, JOBS, JOB_HISTORY AND JOB_GRADES • DJs on Demand database tables: D_CDS, D_PACKAGES, D_TYPES, D_THEMES, D_CLIENTS, D_VENUES, D_SONGS, D_TRACK_LISTINGS, D_PARTNERS, D_EVENTS, D_PLAY_LIST_ITEMS, D_JOB_ASSIGNMENTS • Global Fast Foods database tables: F_CUSTOMERS, F_REGULAR_MENUS, F_PROMOTIONAL_MENUS, F_SHIFTS, F_STAFFS, F_FOOD_ITEMS, F_ORDERS, F_ORDER_LINES, F_SHIFT_ASSIGNMENTS • Print out these tables for your reference when using the Application Express editor • These tables are available on the Student Resource web page for this class Marge Hohly

21. Review the tables • There are six properties of tables in a relational database: • Property 1: Entries in columns are single-valued. • Property 2: Entries in columns are of the same kind. • Property 3: Each row is unique. • Property 4: Sequence of columns is insignificant. • Property 5: Sequence of rows is insignificant. • Property 6: Each column has a unique name. Marge Hohly

22. Categories of SQL Statements • Data manipulation language (DML) statements • Begin with INSERT, UPDATE, DELETE, or MERGE • Used to modify the table by entering new rows, changing existing rows, or removing existing rows. • Data definition language (DDL) statements set up, change, and remove data structures from the database. • The keywords CREATE, ALTER, DROP, RENAME, and TRUNCATE begin DDL statements. • Transaction control (TCL) statements are used to manage the changes made by DML statements. • Changes to the data are executed using COMMIT, ROLLBACK, and SAVEPOINT. TCL changes can be grouped together into logical transactions. • Data control language (DCL) • keywords GRANT and REVOKE are used to give or remove access rights to the database and the structures within it. Marge Hohly

23. Questions • The Fast Foods database consists of how many tables? ____ tables • How is the F_SHIFTS table related to the F_STAFFS table? • What are the names of the columns in the F_CUSTOMERS table? • How many rows of data have been entered in the F_PROMOTIONAL_MENUS table? • In the F_FOOD_ITEMS table, column _________ is a foreign-key column. What table and column is this key referencing? • List the primary key to foreign key relationships required to go from the F_SHIFTS table to the F_REGULAR_MENUS table. • Which table(s) contain null values? Marge Hohly

24. KEYWORD, CLAUSE, STATEMENT • Throughout this course, the words keyword, clause, and statement are used as follows: • A keyword refers to an individual SQL element. For example, SELECT and FROM are keywords. • A clause is a part of a SQL statement. SELECT employee_id, last_name, .... is a clause. • A statement is a combination of two or more clauses.SELECT *FROM employees; is a SQL statement. Marge Hohly

25. Selection vs. Projection • SELECT salaryFROM employeesWHERE last_name like ‘Smith’; • Selection (row) Projection (column) Marge Hohly

26. Join Marge Hohly

27. SELECT statement • SELECT statements can provide the same information depending on how they are written • Example: • SELECT *FROM d_songs; • SELECT id, title, duration, artist, type_codeFROM d_songs; Marge Hohly

28. SELECTION • SELECT *FROM employeesWHERE department_id = 60; • SELECT * FROM employeesWHERE salary > 10000; Marge Hohly

29. Projections • A subset of columns • SELECT first_name, last_name, salaryFROM employees; • SELECT id, title, artistFROM d_songs; Marge Hohly

30. Arithmetic Expressions • Create expressions with number and date data by using arithmetic operators. Marge Hohly

31. Operator Precedence • Operator Precedence • Multiplication and division take priority over addition and subtraction. • Operators of the same priority are evaluated from left to right. • Parentheses are used to force prioritized evaluation and to clarify statements. • Remember: Please excuse my dear aunt Sally Marge Hohly

32. Rewrite in order of precedence • You want to calculate the annual salary if the employee received $100 raise each month • Revise the following statement in the correct order of precedence to get the desired results • Run each example in the editor • SELECT last_name, salary, 12*salary+100FROM employees; Marge Hohly

33. What is null? • If a row lacks the data value for a particular column, that value is said to be null, or to contain a null. A null is a value that is unavailable, unassigned, unknown, or inapplicable. • A null is not the same as zero. Zero is a number. • A null is not a space. Space is a character. Marge Hohly

34. Null Values in Arithmetic Expressions • SELECT last_name, salary, commission_pct, salary*commission_pctFROM employees; Marge Hohly

35. Practice • SELECT *FROM d_songs; • SELECT id, title, duration, artist, type_codeFROM d_songs; • SELECT id, title, artist, FROM d_songs; • SELECT last_name, salary, salary – 300FROM employees; • SELECT last_name, salary, salary * 1.05FROM employees; Marge Hohly

36. Column Alias • Renames a column heading • Is useful in naming columns of derived values • Immediately follow the column name • Uses optional AS keyword between the column name and alias • Required double quotation marks if it contains spaces or special characters or is case sensitive Marge Hohly

37. Using Aliases • SELECT last_name name, salary AS Salary, salary*12 “Annual Salary”FROM employees; Marge Hohly