1 / 78

Chapter 3 Query Language

Chapter 3 Query Language. Database Application SAK 3408. What is SQL?. SQL or Structured Query Language is an English-like language used to create and manipulate databases.

preston
Download Presentation

Chapter 3 Query Language

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 3Query Language Database ApplicationSAK 3408

  2. What is SQL? • SQL or Structured Query Language is an English-like language used to create and manipulate databases. • SQL is an ANSI (American National Standards Institute) standard for accessing database systems. SQL statements are used to retrieve and update data in a database. • SQL works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, Sybase, etc. • With SQL, we can query a database and have a result set returned.

  3. Types of Statements • Three types of statements: • Data Definition Language (DDL) • Data Manipulation Language (DML) • Data Control Language (DCL) • Data Definition Language creates and manipulates the structure, delete or define indexes of table in database • Data Manipulation Language manipulates data in the table • Data Control Language determines who is allowed to what within the database

  4. SQL Grammar SQL statements are always terminated by a semi-colon (;) SELECT CUSTOMER_ID FROM CUSTOMER;

  5. SQL Grammar SQL statements can be entered on a single line or split across multiple lines (preferred) SELECT CUSTOMER_ID, CUSTOMER_NAME, CREDIT_LINE FROM CUSTOMER WHERE CREDIT_LINE > 1000000 AND CUSTOMER_ID IN [1000,2000]; SELECT CUSTOMER_ID, CUSTOMER_NAME, CREDIT_LINE FROM CUSTOMER WHERE CREDIT_LINE > 1000000 AND CUSTOMER_ID IN [1000,2000];

  6. SQL Grammar SQL statements are not case sensitive; however data is. SeLEct * fROm REQueST wHErE aCcT_sT In [‘GA’,’WA’,’NJ’]; Returns data for all requests where the state is either GA, WA or NJ. No records would have been returned if the where clause had been: wHErE aCcT_sT In [‘ga’,’wA’,’Nj’];

  7. Qualifying a Field Name • When the same field name occurs in two or more tables used in a single SQL statement, you must qualify the field name much like you would include the last name to avoid confusion if two people have the same first name. Tablename.fieldname Customer.last_name

  8. Data Definition Language (DDL) Used to create and modify database objects • Create • Drop • Alter

  9. Data Manipulation Language (DML) Used to create, modify and retrieve data • Insert • Select • Update • Delete

  10. Data Control Language (DCL) Used to control database privileges • Grant • Revoke

  11. DDL - Creating a Table Use the Create keyword and specify: • table name • field (column) name(s) • field type • constraints • primary key - unique identifier for a record • foreign key - establishes relationship • check - value must be in the specified list • not null - must have a value • unique - value must be unique

  12. Table with Primary Key Only CREATE TABLE room( roomID number, bldg char(1) CHECK (bldg IN ('A','B')), roomNo varchar2(10), maxCapacity number, style varchar2(15) CHECK (style IN ('LECTURE','LECTURE/LAB','LAB','OFFICE')), CONSTRAINT room_pk PRIMARY KEY (roomID)); Constraint_name Constraint_attributes Constraint_type

  13. Table with Foreign Key CREATE TABLE faculty( facultyID number, lname varchar2(30) NOT NULL, fname varchar2(20) NOT NULL, dept varchar2(5), officeID number, phone varchar2(15), email varchar2(75) UNIQUE, rank char(4) CHECK (rank IN ('INST', 'ASOC','ASST','FULL','SENR')), CONSTRAINT faculty_pk PRIMARY KEY (facultyID), CONSTRAINT faculty_fk FOREIGN KEY (officeID) REFERENCES room(roomID));

  14. Table with Compound Primary Key create table participation( eventID number, memberID number, constraint participation_pk primary key (eventID, memberID), constraint participation_event_fk foreign key (eventID) references event(eventID), constraint participation_member_fk foreign key (memberID) references member(memberID));

  15. DDL - Altering a Table Use the Alter and Add/Modify keywords and specify: • table name • new or existing field name(s) • field type alter table invoice add(sent_dt date); alter table invoice modify (invoice_nbr varchar2(5));

  16. DDL - Removing a Table Use the Drop keyword and specify: • table name drop table invoice; • If this table is referenced by a foreign key, use the cascade constraints clause drop table invoice cascade constraints;

  17. Indexes • Conceptually similar to book index • Increases data retrieval efficiency • Automatically assigns record numbers • Used by DBMS, not by users • Fields on which index built called Index Key • Have a sorted order • Can guarantee uniqueness • Can include one or more fields

  18. Indexes – Syntax CREATE UNIQUE INDEX cust_num_ind ON customer(Name); CREATE INDEX CustomerName ON Customer (CustomerNum); CREATE INDEX CreditLimitRep_ind ON Customer(CreditLimit, RepNum); DROP INDEX RepBal;

  19. Customer Table with Record NumbersFigure 4.10

  20. Customer Table Index on CustomerNum Figure 4.11

  21. Table Indexes on CreditLimit, RepNumFigure 4.12

  22. PROs Faster/more efficient data retrieval CONs Requires additional space Increased overhead Indexes

  23. Data Manipulation Language (DML) Used to create, modify and retrieve data • Insert • Select • Update • Delete

  24. DML - Adding Data to a Table Use the Insert keyword and specify: • table name • field names - optional • values for each field insert into customer values(‘Teplow’,’MA’,23445.67); OR insert into customer (last_name, state_cd, sales) values (‘Teplow’ ,’MA’,23445.67);

  25. DML - Updating Data in a Table Use the Update and Set keywords and specify: • table name • field name(s) • where clause (optional) update inventory set price = price*1.05; update inventory set price = price*1.05 where product_id = 'P103';

  26. DML-Deleting Records Use the Delete From keywords and specify: • table name • where clause (optional) delete from customer; delete from customer where sales < 10000;

  27. Parts of a DML Select Statement • Select • From • Where (optional) • Order By (optional) • Group By (optional) • Having (optional)

  28. SELECT Which fields do you want retrieved? * is used to select all fields in the table

  29. FROM Which table(s) are these fields in?

  30. Two Sample Tables

  31. Select - Simplest • Show everything in a single table SELECT * FROM customer; Returns LAST_NAME STATE_CD SALES ----------------- -------- ---------------- Teplow MA 23445.67 Abbey CA 6969.96 Porter CA 6989.99 Martin CA 2345.45 Laursen CA 34.34 Bambi CA 1234.55 McGraw NJ 123.45

  32. Select Statement - Simple SELECT last_name, state_cd FROM customer; Returns LAST_NAME STATE_CD ----------------- ------- Teplow MA Abbey CA Porter CA Martin CA Laursen CA Bambi CA McGraw NJ

  33. WHERE - Optional Use to: • specify how to join two tables • restrict the data returned

  34. SQL Operators • Logical Operators and or • Comparison Operators = equality != or <> inequality Like string search In list of values Between range of values

  35. Select Statement - Equals SELECT * FROM customer WHERE state_cd = ‘CA’; Returns LAST_NAME ST SALES ----------------- -- --------- Abbey CA 6969.96 Porter CA 6989.99 Martin CA 2345.45 Laursen CA 34.34 Bambi CA 1234.55

  36. Select Statement – Not Equals SELECT * FROM customer WHERE state_cd <> ‘CA’; Returns LAST_NAME ST SALES -------------- -- ---------- Teplow MA 23445.67 McGraw NJ 123.44

  37. Select Statement - Like SELECT last_name, state_cd, sales FROM customer WHERE upper(state_cd) LIKE ‘%A’; Returns LAST_NAME ST SALES --------------- -- ---------------- Teplow MA 23445.67 Abbey CA 6969.96 Porter CA 6989.99 Martin CA 2345.45 Laursen CA 34.34 Bambi CA 1234.55

  38. Select Statement - In SELECT last_name, state_cd FROM customer WHERE state_cd IN (’MA’,’NJ’); Returns LAST_NAME ST ------------- -- Teplow MA McGraw NJ

  39. Select Statement – Between SELECT * FROM customer WHERE sales BETWEEN 6500 AND 25000; Returns LAST_NAME ST SALES -------------- -- ---------- Teplow MA 23445.67 Abbey CA 6969.96 Porter CA 6989.99

  40. Select Statement – Multiple Conditions Using OR SELECT * FROM customer WHERE state_cd <> ‘CA’ OR sales BETWEEN 6500 AND 25000; Returns LAST_NAME ST SALES -------------- -- ---------- Teplow MA 23445.67 Abbey CA 6969.96 Porter CA 6989.99 McGraw NJ 123.44

  41. Select Statement – Multiple Conditions Using AND SELECT * FROM customer WHERE state_cd <> ‘CA’ AND sales BETWEEN 6500 AND 25000; Returns LAST_NAME ST SALES -------------- -- ---------- Teplow MA 23445.67

  42. Select Statement – Calculated Field SELECT last_name, sales, sales*05 as Tax FROM customer; Returns LAST_NAME SALES TAX ----------- ---------- -------- Teplow 23445.67 1172.28 Abbey 6969.96 348.50 Porter 6989.99 349.50 Martin 2345.45 117.27 Laursen 34.34 1.71 Bambi 1234.55 61.73

  43. ORDER BY - Optional • Used to specify sorting order • Can sort • by multiple fields • in ascending or descending order

  44. Select Statement - Sorting select state_name, last_name, sales from customer, state where customer.state_cd = state.state_cd and state_cd in (‘CA’,’MA’,’NJ’) order by state_name; Returns STATE_NAME LAST_NAME SALES ------------------- ------------------------- -------- California Abbey 6969.96 California Porter 6989.99 Massachusetts Teplow 23445.67 New Jersey McGraw 123.45

  45. Overview • Select Statements continued • Table Joins • Distinct • Group By • Having • Decode function • Sequence numbers • Subqueries • Insert • Update • Delete

  46. Select - Table Join SELECT state_name, last_name, sales FROM customer, state WHERE customer.state_cd = state.state_cd; Returns STATE_NAME LAST_NAME SALES -------------- ----------------- -------- California Abbey 6969.96 California Porter 6989.99 Massachusetts Teplow 23445.67 New Jersey McGraw 123.45

  47. Table Joins • Process of combining data from two or more tables, normally using primary and/or foreign keys. • Basic types of joins: • Equijoin (Equal or Inner Join) • Left join (Outer Join) • Right join

  48. Equi Joins • Most common type of join • Look for records which have matching values of the join fields

  49. Join Processing Table joins are done by matching the first record from the primary table’s first record with each record in the secondary table, then matching the second record in the primary table with each record in the secondary table. Continue until each record from the primary table has been combined with each record from the secondary table. This is called a Cartesian product.

  50. Join Processing - cont • Oracle then goes through the Cartesian product, discarding combined records that have non-matching join fields. • The remaining records are returned.

More Related