1 / 69

Chapter 9:

Chapter 9:. Structured Query Language (SQL). Benefits of a Standardized Relational Language. Reduced training costs Productivity Application portability Application longevity Reduced dependence on a single vendor Cross-system communication.

lore
Download Presentation

Chapter 9:

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. Chapter 9: Structured Query Language (SQL)

  2. Benefits of a Standardized Relational Language • Reduced training costs • Productivity • Application portability • Application longevity • Reduced dependence on a single vendor • Cross-system communication

  3. Disadvantages of a standardized relational language • Stifle creativity and innovation • never enough to meet all needs • offspring of compromises • difficult to change • Using special features may result in loss of portability

  4. Terminology • Data Definition Language (DDL): • Commands that define a database, including creating, altering, and dropping tables and establishing constraints. • Data Manipulation Language (DML) • Commands that maintain and query a database. • Data Control Language (DCL) • Commands that control a database, including administering privileges and committing data.

  5. Common SQL Commands • Data Definition Language(DDL):  Create  Drop  Alter  Rename • Data Manipulation Language(DML):  Select  Update  Insert  Delete • Data Control Language(DCL):  Grant  Revoke

  6. SQL statementis entered SQL> SELECT loc 2 FROM dept; Database Data is displayed LOC ------------- NEW YORK DALLAS CHICAGO BOSTON Querying a Database with SQL Statement is sent to database

  7. Writing SQL Statements • SQL statements are not case sensitive(but criteria within quotation marks are) • SQL statements can be on one or more lines • Clauses are usually placed on separate lines • Keywords cannot be split across lines • Tabs and spaces are allowed to enhance readability • Each SQL statement (notline) ends with a semicolon (;)

  8. Data Definition Language (DDL) Creating tables: • Identify appropriate datatypes • Identify columns that should accept null values • Identify columns that need to be unique • Identify all PK/FK mates • Determine any default values to be inserted • Identify columns which need a domain specification • Create the table

  9. DDL example in SQL CREATE TABLE ORDER (Order_Id char not null, Order_Date date default sysdate, Customer_Id char not null, Constraint Order_pk primary key (order_Id) , Constraint Order_fk foreign key (Customer_Id) references Customer(Customer_Id)); Cust_ID Order_ID Customer Order Order_Date

  10. Data Manipulation Language (DML) • This is the major focus of our coverage of SQL • Most useful for querying database based on specific criteria • Includes: insert, delete, update, and select

  11. DML - Inserting • Useful for populating a database • Syntax is: INSERT INTO Product(Product_ID, Product _Name, Unit_Price, On_Hand) VALUES (1, ‘End Table’, 175, 8); • Or INSERT INTO Ca_Customer SELECT * FROM Customer WHERE State=‘CA’;

  12. DML - Deleting DELETE FROM Customer WHERE State=‘HI’; Or DELETE FROM Customer;

  13. DML - Updating UPDATE Product SET Unit_Price = 775 WHERE Product_ID = 7;

  14. DML - SELECT Statement Syntax: SELECT [DISTINCT] column_list FROMtable_list [WHEREconditional expression] [GROUP BYcolumn_list] [HAVINGconditional expression] [ORDER BYcolumn_list] ;

  15. SELECT statement processing order

  16. The Basic SELECT Statement SELECT column1, column2, column3,... FROM table; • SELECT identifies what columns • FROM identifies which table

  17. SQL> SELECT deptno, loc 2 FROM dept; DEPTNO LOC --------- ------------- 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON Example SELECT Statement

  18. SQL> SELECT * 2 FROM dept; DEPTNO DNAME LOC --------- -------------- ––––––––––––- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Selecting All Columns

  19. SQL> SELECT loc AS location 2 FROM dept; LOCATION ------------- NEW YORK DALLAS CHICAGO BOSTON Note Upper-Case Heading Specifying Output Headings

  20. Double quotes SQL> SELECT loc AS “Location” 2 FROM dept; Location ------------- NEW YORK DALLAS CHICAGO BOSTON Note Mixed-Case Heading Specifying Output Headings

  21. SQL> SELECT job 2 FROM emp; JOB --------- PRESIDENT MANAGER MANAGER MANAGER SALESMAN SALESMAN ... A total of 14 records display Duplicate Output

  22. SQL> SELECT DISTINCT job 2 FROM emp; JOB --------- ANALYST CLERK MANAGER PRESIDENT SALESMAN Suppressing Duplicate Output Each unique job is listed only once

  23. SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE job = ‘CLERK’; Case sensitive; single quotes ENAME JOB SAL ---------- --------- --------- JAMES CLERK 950 SMITH CLERK 800 ADAMS CLERK 1100 MILLER CLERK 1300 Limiting Rows with WHERE

  24. WHERE Clause Criteria • Text and dates (sometimes) are enclosed in single quotes • Numbers are not enclosed in quotes • Text values are case sensitive • Date values are format sensitive • Oracle’s default date format is DD-MON-YY, Access’ default is #MM/DD/YY# SQL> SELECT ename, hiredate 2 FROM emp 3 WHERE hiredate >= ’01-Jan-82';

  25. SQL Comparison Operators

  26. SQL Comparison Operators

  27. SQL> SELECT ename, sal 2 FROM emp 3 WHERE sal BETWEEN 1000 AND 1500; ENAME SAL ---------- --------- MARTIN 1250 TURNER 1500 WARD 1250 ADAMS 1100 MILLER 1300 BETWEEN Operator Example

  28. SQL> SELECT ename, job 2 FROM emp 3 WHERE job IN(‘PRESIDENT’,’MANAGER’); ENAME JOB ---------- --------- KING PRESIDENT BLAKE MANAGER CLARK MANAGER JONES MANAGER IN Operator Example

  29. SQL> SELECT ename 2 FROM emp 3 WHERE ename LIKE ‘J%’; ENAME ---------- JONES JAMES The % is a wildcard character that stands for zero to many characters. The underscore character (_) can be used to stand for exactly one character. [ LIKE is not the same as = ] LIKE Operator Example

  30. SQL> SELECT ename, mgr 2 FROM emp 3 WHERE mgr IS NULL; ENAME MGR ---------- --------- KING IS NULL Operator Example

  31. Boolean (Logical) Operators

  32. SQL> SELECT ename, job, sal 2 FROM emp 3 WHERE job = ‘CLERK’ 4 AND sal > 1000; Both conditions must be true ENAME JOB SAL ---------- --------- --------- ADAMS CLERK 1100 MILLER CLERK 1300 AND Operator Example

  33. SQL> SELECT ename 2 FROM emp 3 WHERE ename LIKE ‘J%’ 4 OR ename LIKE ‘M%’; At least one condition must be true ENAME ---------- JONES MARTIN JAMES MILLER OR Operator Example

  34. SQL> SELECT DISTINCT job 2 FROM emp 3 WHERE job NOT LIKE ‘C%’; JOB --------- ANALYST MANAGER PRESIDENT SALESMAN NOT Operator Example

  35. Arithmetic Operators

  36. SQL> SELECT ename, sal, sal*12 AS annual 2 FROM emp 3 WHERE job = ‘CLERK’; ENAME SAL ANNUAL ---------- --------- --------- JAMES 950 11400 SMITH 800 9600 ADAMS 1100 13200 MILLER 1300 15600 Arithmetic Expression Example

  37. SQL> SELECT ename, job, sal 2 FROM emp 3 ORDER BY ename; ENAME JOB SAL ---------- --------- --------- ADAMS CLERK 1100 ALLEN SALESMAN 1600 BLAKE MANAGER 2850 CLARK MANAGER 2450 FORD ANALYST 3000 ... ... ... Sorting Rows with ORDER BY

  38. SQL> SELECT ename, job, sal 2 FROM emp 3 ORDER BY job, ename; The order of the list determines the precedence of the sort order ENAME JOB SAL ---------- --------- --------- FORD ANALYST 3000 SCOTT ANALYST 3000 ADAMS CLERK 1100 JAMES CLERK 950 MILLER CLERK 1300 ... ... ... Sorting by Multiple Fields

  39. SQL> SELECT ename, job, sal 2 FROM emp 3 ORDER BY sal DESC; ENAME JOB SAL ---------- --------- --------- KING PRESIDENT 5000 FORD ANALYST 3000 SCOTT ANALYST 3000 JONES MANAGER 2975 BLAKE MANAGER 2850 ... ... ... Sorting in Descending Order

  40. Aggregate Functions

  41. SQL> SELECT AVG(sal) AS “SALARY AVG” 2 FROM emp 3 WHERE job = ‘SALESMAN’; SALARY AVG ---------- 1400 Aggregate Function Example

  42. SQL> SELECT COUNT(*) 2 FROM emp; COUNT(*) --------- 14 Aggregate Function Example Note: COUNT(*) returns the number of rows in a table while COUNT(field) returns the number of rows that are nonnull for the field counted

  43. Subqueries • Subqueries are useful when a query is based on unknown values (e.g., “Who has a salary greater than Blake?” when Blake’s salary is unknown) • Subqueries involve placing an inner query (SELECT, FROM, WHERE) within a WHERE or HAVING clause of another (outer) query • The inner query is a complete query that could stand on its own and serves to provide values for the search condition of the outer query

  44. The inner query is enclosed in parentheses; indenting is optional SQL> SELECT ename 2 FROM emp 3 WHERE sal > 4 (SELECT sal 5 FROM emp 6 WHERE empno = 7698); ENAME ---------- KING JONES FORD SCOTT The inner query determines the salary of Blake (empno = 7698) and returns it to the outer query Subquery Example

  45. SQL> SELECTename, job, sal 2 FROM emp 3 WHERE sal = 4 (SELECT MIN(sal) 5 FROM emp); ENAME JOB SAL ---------- --------- --------- SMITH CLERK 800 The inner query determines the minimum salary of all employees and returns it to the outer query Aggregate Function Subquery

  46. Processing Multiple Tables • When relationships exist between tables, the tables can be linked together in queries • Relationships between tables are established by setting up primary key to foreign key relationships between columns that are common to both tables • We link related tables together in SQL queries by using either joins or subqueries

  47. Joins • A join is defined as: “A relational operation that causes two tables with a common domain to be combined into a single table” • A join is specified in SQL by using a WHERE clause to match values for the common field between the two tables (if you were joining three tables, you would need two joining WHERE clauses) • Each row in the resultant table (i.e., the join product) contains data from rows in the input tables where values for the common field match

  48. EMP DEPT EMPNO ENAME ... DEPTNO------ ----- ... ------ 7839 KING ... 10 7698 BLAKE ... 30 7782 CLARK ... 10 7566 JONES ... 20 ... ... ... ... DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Data from Multiple Tables EMPNO DEPTNO LOC ----- ------- -------- 7839 10 NEW YORK 7698 30 CHICAGO 7782 10 NEW YORK 7566 20 DALLAS 7654 30 CHICAGO 7499 30 CHICAGO ... ... ... DEPTNO is the common field joining the EMP and DEPT tables

  49. Creating a Join in SQL • Write the join condition in the WHERE clause • Prefix the column name with the table name when the same column name appears in more than one table (to avoid ambiguity) SELECT table1.field, table2.field, ... FROM table1, table2 WHERE table1.fieldX=table2.fieldX; This is the query that is shown on the previous slide SELECT empno, emp.deptno, loc FROM emp, dept WHERE emp.deptno = dept.deptno;

  50. SQL> SELECTord.orderdate, item.qty, product.descrip 2 FROM ord, item, product 3 WHERE ord.ordid = item.ordid 4 AND item.prodid = product.prodid; ORDERDATE QTY DESCRIP --------- --------- ------------------------------ 07-JAN-87 1 ACE TENNIS NET 11-JAN-87 1 ACE TENNIS RACKET II 15-JAN-87 100 ACE TENNIS RACKET I 01-MAY-86 1 SB ENERGY BAR-6 PACK 05-JUN-86 20 ACE TENNIS BALLS-3 PACK 15-JUN-86 3 ACE TENNIS NET ... ... ... 3 Table Join Example Note: Table name prefixes are used here for all fields for clarity

More Related