1 / 58

Database Design

Database Design. Sections 17 & 18 – Columns, Characters, Rows, Concatenations, DISTINCT, DESCRIBE, Logical Operators, Order of Operations, Sorting. Using DESCRIBE (DESC). Use the DESCRIBE command to display the structure of a table. DESC[RIBE] tablename;. Describe command.

flann
Download Presentation

Database Design

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design Sections 17 & 18 – Columns, Characters, Rows, Concatenations, DISTINCT, DESCRIBE, Logical Operators, Order of Operations, Sorting

  2. Using DESCRIBE (DESC) • Use the DESCRIBE command to display the structure of a table. DESC[RIBE] tablename; Marge Hohly

  3. Describe command • DESCRIBE (DESC) command displays the structure of a table • Try the following:DESC departments; • See the results on the next slide. Marge Hohly

  4. Describe command Marge Hohly

  5. Concatenation • A concatenation operator: • Concatenation means to connect or link together in a series • Concatenates columns or character strings to other columns • Is represented by two vertical bars (||) • Creates a resultant column that is a character expression Marge Hohly

  6. Practice • SELECT last_name ||', '|| first_nameFROM employees; • Using the DJ on Demand EVENT table, create and display :"On date, the event was name“ • Run the command again using an alias Events for the column heading • Create SQL to get the phase like “King earns $24000” from the employees table • SELECT doesn’t change data Marge Hohly

  7. Practice database UCLA • CREATE table UCLA (id number, name varchar2(20)); • DESC ucla; • INSERT INTO uclavalues(1,'Joe'); • values(2,‘Mary'); • SELECT *FROM ucla; • We can use the ucla table for practice during lecture Marge Hohly

  8. Using Literal Character Strings • A literal is a character, a number, or a date included in the SELECT list. • Date and character literal values must be enclosed within single quotation marks. • Each character string is output once for each row returned. Marge Hohly

  9. Using Literals • SELECT id, 'hello‘FROM ucla; • hello is a literal • This inserts values in a column of the UCLA table with the value ‘hello’ • Try it. • Try this:SELECT id, 15FROM ucla Marge Hohly

  10. DISTINCT: Eliminating duplicate rows • The default display of queries is all rows, including duplicate rows. • Use the DISTINCT keyword in the SELECT clause to eliminate duplicate rows. • DISTINCT must appear just after the SELECT keyword. Marge Hohly

  11. DISTINCT practice • SELECT job_id FROM employees; • Note the large number of rows returned • Revise using DISTINCT • SELECT DISTINCT job_id FROM employees; • When DISTINCT is after the SELECT it applies to all other fields in the SELECT • SELECT DISTINCT job_id, salaryFROM employees; Marge Hohly

  12. Limiting rows • SELECT*|{[DISTINCT] column|expression [alias],...}FROM table[WHEREcondition(s)]; • Example:SELECT DISTINCT year AS “Year”FROM d_cdWHERE year < 2000; Marge Hohly

  13. Practice • Each statement below has errors. Correct the errors and execute the query in APEX • SELECT first nameFROM f_staffs; • SELECT first_name |"  " | last_name AS “DJs on Demand Clients”FROM d_clients; • SELECT DISCTINCT f_order_linesFROM quantity; • SELECT order numberFROM f_orders; Marge Hohly

  14. Practice • Which of the following is TRUE about the following query? SELECT first_name, DISTINCT birthdateFROM f_staffs; • Only two rows will be returned. • Four rows will be returned. • Only Fred 05-JAN-88 and Lizzie 10-NOV-87 will be returned. • No rows will be returned. Marge Hohly

  15. More Practice • True/False -- The following SELECT statement executes successfully:SELECT last_name, job_id, salary AS SalFROM employees; • True/False -- The following SELECT statement executes successfully:SELECT *FROM job_grades; • There are four coding errors in this statement. Can you identify them?SELECT employee_id, last_namesal x 12 ANNUAL SALARYFROM employees; • In the arithmetic expression salary*12 - 400, which operation will be evaluated first? Marge Hohly

  16. Practice • Which of the following can be used in the SELECT statement to return all columns of data in the Global Fast Foods f_staffs table? • column names • * • DISTINCT id • both a and b • Using SQL to choose the columns in a table uses which capability? • selection • projection • partitioning • join • SELECT department_id, first_name, last_name AS "Employee". The column heading in the query result will appear as: • EMPLOYEE • employee • Employee • "Employee: Marge Hohly

  17. More Practice • Which statement below will return a list of employees in the following format? Mr./Ms. Steven King is an employee of our company. • SELECT "Mr./Ms."||first_name||' '||last_name 'is an employee of our company.' AS "Employees“FROM employees; • SELECT 'Mr./Ms. 'first_name,last_name ||' '||'is an employee of our company.' FROM employees; • SELECT 'Mr./Ms. '||first_name||' '||last_name ||' '||'is an employee of our company.' AS "Employees“FROM employees • SELECT Mr./Ms. ||first_name||' '||last_name ||' '||"is an employee of our company." AS "Employees“FROM employees • Which expression below will produce the largest value? • SELECT salary*6 + 100 • SELECT salary* (6 + 100) • SELECT 6(salary+ 100) • SELECT salary+6*100 Marge Hohly

  18. More Practice • Which is true about SQL statements? • SQL statements are case-sensitive • SQL clauses should not be written on separate lines. • Keywords cannot be abbreviated or split across lines. • SQL keywords are typically entered in lowercase; all other words in uppercase. • Which query will return three columns each with UPPERCASE column headings? • SELECT "Department_id", "Last_name", "First_name“FROM employees; • SELECT DEPARTMENT_ID, LAST_NAME, FIRST_NAME FROM employees; • SELECT department_id, last_name, first_name AS UPPER CASE FROM employees; • SELECT department_id, last_name, first_nameFROM employees; Marge Hohly

  19. More Practice • Which statement below will likely fail? • SELCT * FROM employees; • Select * FROM employees; • SELECT * FROM EMPLOYEES; • SelecT* FROM employees; Marge Hohly

  20. Limiting Rows with WHERE • The WHERE clause can compare values in columns, literal values, arithmetic expressions, or functions. It consists of three elements: • Column name • Comparison condition • Expressions, constant, or list of values Marge Hohly

  21. WHERE clause • The WHERE clause follows the FROM clause in a SQL statement. • An alias cannot be used in the WHERE clause! • Example: • SELECT last_name, first_name, department_idFROM employeesWHERE department_id = 50; Marge Hohly

  22. Comparison Operators • The symbols != and ^= can also represent the not equal tocondition. Marge Hohly

  23. WHERE clauses • Using comparison operators • SELECT last_name,first_name,department_idFROM employeesWHERE department_id >= 60; • SELECT last_name,first_name,department_idFROM employeesWHERE department_id <> 60; • Write a SELECT statement to produce the following results:The song title is "Another one bites the dust.” Where ‘Another one bites the dust’ is the title of a song.Remember Oracle SQL is case sensitive Marge Hohly

  24. BETWEEN ... AND • BETWEEN...AND operator is used to select and display rows based on a range of values. • BETWEEN ... AND are inclusive • SELECT last_name,first_name,department_idFROM employeesWHERE department_id BETWEEN 60 AND 90; • SELECT last_name,first_name,department_idFROM employeesWHERE department_id NOT BETWEEN 60 AND 90; • SELECT title, yearFROM   d_cdsWHERE  year  BETWEEN '1999' AND '2001'; Marge Hohly

  25. More Complex WHERE clause • Example: • "I would like to know Global Fast Foods employees whose manager ID is 19 and earn a salary less than $12.00 per hour. • SELECT *FROM f_staffsWHERE manager_id = 19 and salary <12.00; Marge Hohly

  26. WHERE clause using LIKE • LIKE condition allows you to select rows that match either literal characters or number patterns. • % and underscore (_ ) • are wildcard characters, used to construct a search string. • % symbol used to represent any sequence of zero or more characters. • Underscore (_ ) symbol used to represent a single character. Marge Hohly

  27. LIKE examples • SELECT last_name,first_name,department_idFROM employeesWHERE last_name LIKE 'A%'; • SELECT last_name,first_name,department_idFROM employeesWHERE last_name LIKE '%s‘; • SELECT last_name,first_name,department_idFROM employeesWHERE last_name LIKE '%s%‘; • SELECT last_name,first_name,department_idFROM employeesWHERE last_name LIKE ‘_ _ _ _ _s%‘;Using 5 underscores Marge Hohly

  28. Like • For an exact match where _ or % are part of the string and not a wildcard use the \ symbol Marge Hohly

  29. Using IS NULL, IS NOT NULL • NULL is unavailable, unassigned, unknown, or inapplicable. No presence of data. • NOT NULL test for presence of data in column/field. • Examples: • SELECT last_name,first_name,department_id, commission_pctFROM employeesWHERE commission_pct is NULL; • SELECT last_name,first_name,department_id, commission_pctFROM employeesWHERE commission_pct is NOT NULL; Marge Hohly

  30. Practice problems • Display the location type and comments for all DJs on Demand venues that are Private Home. • Who are the partners of DJs on Demand who do not get an authorized expense amount? Marge Hohly

  31. IN is like OR’s • IN is like a string of OR clauses. • Example: • SELECT last_name,first_name,department_idFROM employeesWHERE department_id IN(50, 60, 90); Marge Hohly

  32. Logical Comparisons • A logical condition combines the results of two or more conditions to produce a single result. A row is returned ONLY IF the overall result of the condition is true. • AND – Returns TRUE if both conditions are true. • OR – Returns TRUE if either conditions are true. • NOT – Returns TRUE if the condition is false. Marge Hohly

  33. Precedence using AND & OR • Evaluate these two examples. What is the difference? • SELECT last_name, job_id, salaryFROM employeesWHERE job_id = ‘SA_REP’OR job_id=‘AD_PRES’AND salary>15000; • SELECT last_name, job_id, salaryFROM employeesWHERE (job_id = ‘SA_REP’OR job_id=‘AD_PRES’)AND salary>15000; • AND before OR Marge Hohly

  34. Logical Comparisons • Logical conditions (a few examples) • SELECT last_name, job_id, department_idFROM employeesWHERE job_id = ‘SA_REP’OR department_id = 10; • SELECT last_name, job_id, SALARYFROM employeesWHERE job_id = ‘SA_REP’AND salary > 15000; • Can you explain what rows are to be returned in each? Marge Hohly

  35. Logical operator • Write a select statement to find an address in the DJs on Demand database d_venues table that has the word "Primrose" in the description. • SELECT  addressFROM  d_venuesWHERE address LIKE '%Primrose%‘; • Variations: • …WHERE cd_id NOT IN(105, 206, 332); • …WHERE cd_id != 105 and cd_id != 206 and cd_id!= 332; Marge Hohly

  36. Examples: • Below are phrases and conditions in which they would not be true • employee_id = 100 AND last_name LIKE 'S%' • Ms. Smith whose employee_id is 50 • Employee_id 100 whose last name is King • department = 10 AND employee_id  = 100; • Employee_id 100 is in department 20 • Employee_id 50 is in department 10 Marge Hohly

  37. Complex AND and OR clause • Take an arc #id ID = A ID = B ID = C Marge Hohly

  38. Complex AND and OR clause • Example Table: Marge Hohly

  39. Complex AND and OR clause • WHERE clause example for ARC: • WHERE ((A is NOT NULL and B is NULL and C is NULL) OR (A is NULL and B is NOT NULL and C is NULL) OR (A is NULL and B is NULL and C is NOT NULL)) /*--- comment ---*/ • Precedence is AND before OR Marge Hohly

  40. Rules of Precedence • The rules of precedence determine the order in which expressions are evaluated and calculated. The table lists the default order, which can be overridden by using parentheses. Marge Hohly

  41. Order of Operations Examples • What order will these process in? • SELECT last_name, specialty, auth_expense_amtFROM   d_partnersWHERE  specialty ='All Types‘OR     specialty IS NULLAND   auth_expense_amt = 300000; • SELECT last_name, specialty, auth_expense_amtFROM   d_partnersWHERE  (specialty ='All Types‘OR     specialty IS NULL)AND   auth_expense_amt = 300000; Marge Hohly

  42. Examples 18.1.8 & 18.1.9 • “I need to know who the Global Fast Foods employees are that make more than $6.50/hour and their position is not Order Taker.“ • What's my email address?Because I have been working for Oracle since the beginning of 1996, I make more than $9000 per month. Because I make so much money, I don't get a commission. Marge Hohly

  43. Examples: AND and OR conditions • What are the titles of the jobs whose minimum salary is 4000 and whose maximum salary is 9000? • Try this with OR and then AND • Rewritten query including the salary columns to verify that both conditions are met: • Note: you might want to review the jobs table fields/columns etc. Marge Hohly

  44. Sorting rows • ORDER BY • Information sorted by the column referenced. • ORDER BY clause follows the FROM clause and is at the end of the SELECT statement. • ORDER BY clause sorts in ascending order by default. • Only limit to how many columns can be added to the ORDER BY clause is the number of columns in the table. Marge Hohly

  45. ORDER BY • Example of single value sort • SELECT department_id, job_id, last_name, salaryFROM employeesORDER BY department_id desc; Marge Hohly

  46. ORDER BY multiple values • SELECT department_id, job_id, last_name, salaryFROM employeesORDER BY department_id, job_id, salary; • SELECT department_id, job_id, last_name, salaryFROM employeesORDER BY department_id, job_id desc, salary; • SELECT department_id, job_id, last_name, salaryFROM employeesORDER BY 3;The 3 is field from SELECT • SELECT department_id, job_id, last_name, salaryFROM employeesORDER BY 2, last_name; Marge Hohly

  47. Sort order • The default sort order is ascending. • Numeric values are displayed lowest to highest. • Date values are displayed with the earliest value first. • Character values are displayed in alphabetical order. • Null values are displayed last in ascending order and first in descending order. Marge Hohly

  48. ORDER BY multiple values • Place the following hire dates in descending order: 22-MAY-85, null, 10-JAN-04, 17-NOV-55, 21-DEC-98 • Write a query to find the answer, using the employees table and hire_date column Marge Hohly

  49. Order of execution in a SELECT statement • The order of execution of a SELECT statement is as follows: • FROM clause -- locates the table that contains the data • WHERE clause -- restricts the rows to be returned • SELECT clause -- selects from the reduced data set the columns requested • ORDER BY -- orders the results set Marge Hohly

  50. Example • SELECT employee_id, first_name FROM employeesWHERE employee_id < 105ORDER BY last_name; • What order does this execute in? Marge Hohly

More Related