1 / 42

Database Design Lecture 3_2 Data Manipulation in SQL Simple SQL queries

Database Design Lecture 3_2 Data Manipulation in SQL Simple SQL queries References: Text Chapter 8 Oracle SQL Manual. Tables in the Examples. Customer( custNo , custName, custSt, custCity, age) Product( prodNo , prodName, prodDes, price)

sheffielda
Download Presentation

Database Design Lecture 3_2 Data Manipulation in SQL Simple SQL queries

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 Lecture 3_2 Data Manipulation in SQL Simple SQL queries References: Text Chapter 8 Oracle SQL Manual Database Design lecture 3_2 Slide 1

  2. Tables in the Examples Customer(custNo, custName, custSt, custCity, age) Product(prodNo, prodName, prodDes, price) Orders(ordNo, ordDate, custNo, prodNo, quantity) Where custName, custSt, custCity, prodName, prodDes are strings ordDate is date Others are numbers Database Design lecture 3_2 Slide 2

  3. Sample Data in Customer Table Database Design lecture 3_2 Slide 3

  4. Sample Data in Product Table Database Design lecture 3_2 Slide 4

  5. Sample Data in Orders Table Database Design lecture 3_2 Slide 5

  6. Data Manipulation (DML) • DML is used to retrieve and modify data in the tables • Four basic statements • Select • Insert • Update • delete Database Design lecture 3_2 Slide 6

  7. Simple Queries • The SELECT command is used for submitting queries to the DBMS. • Syntax for simple queries SELECT expression_list FROM table_list [WHERE condition] [ORDER BY expression_list [DESC|ASC]; • Expression is one which involves column names, constants, functions and operators. • Eg. prodNo, Price+10, 1, custSt||custCity • arithmetic operators for numbers: +, -, *, / • || is used to concatenate strings Database Design lecture 3_2 Slide 7

  8. Condition in the WHERE Clause • Condition is an expression that can be evaluated to TRUE or FALSE. • Only rows satisfying the condition will be chosen. • Condition can be simple comparison or compound expression. • Basic comparison operators =, >, <, <=, >=, <> (!=) • can be applied to numbers, strings and dates • Eg, age >20, prodName='Car', • Basic comparisons can be compounded by AND, OR, NOT • Eg, prodNo=100 and ordDate='01-jan-2003' Database Design lecture 3_2 Slide 8

  9. Examples Example 1: List all products (by prodNo and price) which are priced more than 100. Select prodNo, price From Product Where price >100; Example 2. What is the name of the customer whose custNo is 1? Select custName From customer Where custNo=1; Database Design lecture 3_2 Slide 9

  10. Listing All Data in a Table • If WHERE clause is omitted, all rows will be listed. Example: List all data in the customer table SELECT custNo, custName, custSt, custCity FROM customer; or (use * for all columns) SELECT * FROM CUSTOMER; Database Design lecture 3_2 Slide 10

  11. More Examples Example 1: Find all orders of product 100 before 02/01/03. SELECT * FROM orders WHERE prodNo = 100 AND ordDate <'02-jan-2003'; Example 2: Find all products priced less than 200 or greater than 300 SELECT * FROM product WHERE price < 100 OR price >300; Database Design lecture 3_2 Slide 11

  12. Use of BETWEEN Example: List products priced between 200 and 300. SELECT * FROM product WHERE price >=200 and price <=300; or equivalently SELECT * FROM product WHERE price between 200 and 300; Database Design lecture 3_2 Slide 12

  13. Use of IN Example: List all customers living in Gold Coast, or Brisbane, or Sydney. SELECT * FROM Customer WHERE custCity='Gold Coast' OR custCity='Brisbane' OR custCity='Sydney'; or equivalently SELECT * FROM Customer WHERE custCity IN ('Gold Coast', 'Brisbane', 'Sydney'); Database Design lecture 3_2 Slide 13

  14. Use of LIKE Example: List all products whose description contain the string 'Food'. SELECT * FROM product WHERE prodDes LIKE '%Food%'; • Two wildcards can be used % and _ • % represents any sequence of zero or more characters • _ represents any single character Database Design lecture 3_2 Slide 14

  15. More Examples of LIKE LIKE 'H_' : any string beginning with H and exactly 2 characters long. NOT LIKE 'H%': any string not beginning with H LIKE '%y': any string ending with 'y' • If the search string include a pattern matching character _ or % , we can use an ESCAPE character to represent the pattern-matching character. Eg, to search for '15%', we can use LIKE '15#%' ESCAPE '#' Database Design lecture 3_2 Slide 15

  16. IS NULL and IS NOT NULL Example: List all products with a product description. SELECT * FROM product WHERE prodDes IS NOT NULL; Similarly, to list products without description, use SELECT * FROM product WHERE prodDes IS NULL; Database Design lecture 3_2 Slide 16

  17. Expressions Involving NULL • Any numeric, string or date expression involving NULL will evaluate to NULL • Comparison with NULL value will evaluate to FALSE. • Eg, neither SELECT * FROM customer WHERE age> 25 nor SELECT * FROM customer WHERE age <= 25 will list customers whose age is NULL (not known). Database Design lecture 3_2 Slide 17

  18. Use of DISTINCT • To remove duplicate values Example: List all customer cities. SELECT custCity FROM customer; A city will be repeated if there are more than one customer in that city. To eliminate the duplicates, use SELECT DISTINCT custCity FROM customer; Database Design lecture 3_2 Slide 18

  19. Ordering of Rows • Rows can be put in ascending or descending order of some columns. To do this, use ORDER BY Example: list all products in descending order of price SELECT * FROM product ORDER BY price desc; • Default order (ie,if desc is not used) is ascending • Can also order by several attributes, eg ORDER BY price desc, prodName; Database Design lecture 3_2 Slide 19

  20. Aggregate Functions • COUNT - returns the number of selected values • SUM - returns the sum of selected (numeric) values • AVG - returns the average of selected (numeric) values • MIN - returns the minimum of selected values • MAX - returns the maximum of selected values Database Design lecture 3_2 Slide 20

  21. Use of COUNT (*) Example 1: How many products are there in the product table? SELECT count(*) FROM product; Example 2: How many products are priced at 300? SELECT count(*) FROM product WHERE price =300; Note: count(*) also count rows that have NULL values Database Design lecture 3_2 Slide 21

  22. Use of COUNT(column_name) Example 1: List the number of products in the product table SELECT count(prodNo) FROM product; Example 2: List the number of product descriptions in the product table SELECT count(prodDes) FROM product; Note: count(prodDes) does not count rows that have NULL value for prodDes. Database Design lecture 3_2 Slide 22

  23. Use of COUNT(DISTINCT column_name) Example1: How many cities are the customers located in ? SELECT count(distinct custCity) from customer; Example 2: How many customers ordered products since 01/01/2003? SELECT count(distinct custNo) FROM orders WHERE ordDate >= '01-jan-2003'; Database Design lecture 3_2 Slide 23

  24. Use of SUM Example 1: How many products were ordered by customer 1? SELECT SUM(quantity) FROM orders WHERE custNo =1; Example 2: How many orders were made by customer 1 and how many products did he order? SELECT count(ordNo), SUM(quantity) FROM orders WHERE custNo =1; Database Design lecture 3_2 Slide 24

  25. Use of AVG, MIN and MAX Example: list the minimum, maximum and average price of all products. SELECT MIN(price), MAX(price), AVG(price) FROM product; Note: if some product's price are NULLs, then SUM and AVG do not take those products into consideration. Database Design lecture 3_2 Slide 25

  26. Other Oracle Built-in Functions • Other built-in function can be used in SQL query • Built-in functions are system specific • Common functions include • data conversion functions • date to string, string to number • String operation functions • substring, trim, case conversion • Mathematical functions • square root, exponent, floor etc. See Oracle SQL Manual for oracle built-in functions Database Design lecture 3_2 Slide 26

  27. Table names and Column names • Table name can be prefixed with the owner name. eg, if table product is owned by user John, you can use SELECT * FROM John.product; • Column names can be prefixed with table name, eg SELECT product.prodNo FROM product; Database Design lecture 3_2 Slide 27

  28. Oracle SQLplus Only facilities* • Setting system parameters and formatting output • Examples SET PAGESIZE 80 SET LINESIZE 100 SET PAUSE ON|OFF SET SQLPROMPT "enter command>" SET TIMING ON|OFF • To show all system parameters, type SHOW ALL Database Design lecture 3_2 Slide 28

  29. Examples-linesize (1)* SQL> select * from customer; CUSTNO CUSTNAME CUSTST CUSTCITY ---------- -------------------- -------------------- -------------------- AGE ---------- 1 C1 Smith St Gold Coast 20 2 C2 Mains St Brisbane 30 3 C3 Mains Rd Brisbane 25 4 C4 Mains Rd Sydney 5 C5 Mains Rd Brisbane Database Design lecture 3_2 Slide 29

  30. Examples -linesize (2)* SQL> set linesize 100 SQL> select * from customer; CUSTNO CUSTNAME CUSTST CUSTCITY AGE ---------- -------------------- -------------------- -------------------- ---------- 1 C1 Smith St Gold Coast 20 2 C2 Mains St Brisbane 30 3 C3 Mains Rd Brisbane 25 4 C4 Mains Rd Sydney 5 C5 Mains Rd Brisbane Database Design lecture 3_2 Slide 30

  31. Examples – pagesize (1)* SQL> set pagesize 5 SQL> select * from Orders; ORDNO ORDDATE CUSTNO PRODNO QUANTITY ---------- --------- ---------- ---------- ---------- 1 01-JAN-03 1 100 2 2 02-JAN-03 1 101 1 ORDNO ORDDATE CUSTNO PRODNO QUANTITY ---------- --------- ---------- ---------- ---------- 3 01-JAN-03 2 102 1 4 01-JAN-03 3 100 2 ORDNO ORDDATE CUSTNO PRODNO QUANTITY ---------- --------- ---------- ---------- ---------- 5 03-JAN-03 1 101 1 6 06-MAR-03 2 100 10 6 rows selected. Database Design lecture 3_2 Slide 31

  32. Examples – pagesize (2)* SQL> set pagesize 10 SQL> select * from orders; ORDNO ORDDATE CUSTNO PRODNO QUANTITY ---------- --------- ---------- ---------- ---------- 1 01-JAN-03 1 100 2 2 02-JAN-03 1 101 1 3 01-JAN-03 2 102 1 4 01-JAN-03 3 100 2 5 03-JAN-03 1 101 1 6 06-MAR-03 2 100 10 6 rows selected. Database Design lecture 3_2 Slide 32

  33. Examples-column format (1)* SQL> select * from room; ROO HOT TY PRICE --- --- -- ---------- 001 H01 T1 120 002 H01 T1 120 003 H01 T2 150 004 H01 T3 100 005 H01 T4 80 006 H01 T4 80 001 H02 T1 100 002 H02 T1 78 003 H02 T1 78 004 H02 T2 75 10 rows selected. Database Design lecture 3_2 Slide 33

  34. Examples -column format (2)* SQL> column roomNo format A8; SQL> column hotelno format A8; SQL> column type format a5; SQL> coulmn price format $999.99 SQL> select * from room; SQL> select * from room; ROOMNO HOTELNO TYPE PRICE -------- -------- ----- -------- 001 H01 T1 $120.00 002 H01 T1 $120.00 003 H01 T2 $150.00 004 H01 T3 $100.00 005 H01 T4 $80.00 006 H01 T4 $80.00 001 H02 T1 $100.00 002 H02 T1 $78.00 003 H02 T1 $78.00 004 H02 T2 $75.00 10 rows selected. Database Design lecture 3_2 Slide 34

  35. Saving Output and Using HOST* • To save the result of a query or a command, you can use SPOOL to create a file, eg SPOOL c:\mydir\example1 SELECT * FROM products; SELECT * FROM ORDERS; The file example1.lst will be created in directory c:\mydir and it will contain the commands and query results • Spooling can be turned on or off SPOOL OFF|ON • To see your file, type HOST, an OS window will open so that you can run OS commands. To return, type EXIT or close the OS window using your mouse Database Design lecture 3_2 Slide 35

  36. Basic Insert Statement • Syntax INSERT INTO tablename [(attribute list)] VALUES (value_list); • value list must correspond to attribute list • If attribute list is omitted, then a value for every attribute is required • The data types must be correct • Example: for table Customer, Insert into Customer(custNo, custName) values ('6', 'John'); Insert into Customer values ('7', 'David ', 'St1','City1', 20); Database Design lecture 3_2 Slide 36

  37. Inserting Data Using Queries • You can insert the result of a query into a table For example, if you have a table Briscustomer which has the same structure as Customer, then you can use insert into Briscustomer select * from customer where custcity ='Brisbane'; Database Design lecture 3_2 Slide 37

  38. Create Table Using Queries • You can create a new table using the result of a query Example create table Briscustomer AS select custno, custName, custSt, age from customer where custcity ='Brisbane'; will create a table Briscustomer which contains the custno, custName, custSt and age of customers from Brisbane. Database Design lecture 3_2 Slide 38

  39. SQL data loader* • For a table containing a large data set, INSERT command is not efficient to populate the table • Oracle provides a data loader utility SQLLOADER which can be used to load data • runs in OS , not in SQLplus • fussy about data format • table must be created first Database Design lecture 3_2 Slide 39

  40. Update and Delete • UPDATE is used to update the data in table • DELETE is used to delete data from table • TRUNCATE deletes all data in a table and frees storage space for the table rows ( deletes data faster but you cannot rollback) Update customer SET custCity = 'Melbourne', custSt='12 Mains Rd' Where custNo =1; DELETE FROM product WHERE price <5; TRUNCATE TABLE product; Database Design lecture 3_2 Slide 40

  41. Commit and Rollback • If you want to undo your update of the tables, type ROLLBACK; • If you want to make your update permanent, use COMMIT; Database Design lecture 3_2 Slide 41

  42. Data Definition Commands in Standard SQL* • In SQL-92 standard, you can create a schema and domains (not available in Oracle 8i). To create a schema, use CREATE SCHEMA schema_name AUTHORIZATION user_name • Then when creating table you can use a prefix schema_name. so that the tables belong to this schema. • The commands can only be used by authorized users. • To create a domain, you can use, for example, CREATE DOMAIN addresses AS CHAR(30); • Later you can use addresses instead of CHAR(30) in table coulmns. Database Design lecture 3_2 Slide 42

More Related