Download
1 / 47
470 likes | 599 Views
ITBIS373 Database Development. Lecture3b - Chapter 3: Using SQL Queries to Insert, Update, Delete, and View Data. Guide to Oracle 10 g. Lesson B Objectives. After completing this lesson, you should be able to: Write SQL queries to retrieve data from a single database table
E N D
ITBIS373 Database Development Lecture3b - Chapter 3: Using SQL Queries to Insert, Update, Delete, and View Data Guide to Oracle 10g
Lesson B Objectives After completing this lesson, you should be able to: • Write SQL queries to retrieve data from a single database table • Create SQL queries that perform calculations on retrieved data • Use SQL group functions to summarize retrieved data Guide to Oracle 10g
Retrieving Data From a Single Database Table • Syntax SELECT columnname1, columnname2, … FROM ownername.tablename [WHERE search_condition]; • Retrieve all of columns • Use asterisk ( * ) as wildcard character in SELECT clause • SELECT * from … Guide to Oracle 10g
Suppressing Duplicate Rows • SQL DISTINCT qualifier • Examines query output before it appears on screen • Suppresses duplicate values • Syntax • SELECT DISTINCT columnname; Guide to Oracle 10g
Using Search Conditions in SELECT Queries • Use search conditions to retrieve rows matching specific criteria • Exact search conditions • Use equality operator • Inexact search conditions • Use inequality operators • Search for NULL or NOT NULL values • WHERE columnname IS NULL • WHERE columnname IS NOT NULL Guide to Oracle 10g
Using Search Conditions in SELECT Queries (continued) • IN comparison operator • Match data values that are members of a set of search values Guide to Oracle 10g
LIKE operator • Use to match part of character string • Syntax • WHERE columnname LIKE 'string' • Character string should contain wildcard character %, or_, or both. • The percent sign (%) wildcard character represents multiple characters. If you place (%) on the left edge of the character string to be matched, the DBMS searches for an exact match on the far-right characters and allows an exact match for the characters represented by(%). Guide to Oracle 10g
For example, the search condition WHERE term_descLIKE ‘%2006’ retrieves all term rows in which the last four characters in TERM_DESC column 2006. The DBMS ignores the characters on the left side of the character string up to the substring 2006. • The search condition WHERE term_desc LIKE ‘Fall%’ retrieves all term rows in which the first four characters are Fall, regardless of the value of the rest of the string. • The search condition WHERE course_name LIKE’%Systems%’ retrieves every row in the COURSE table in which the COURSENAME column contains the character string Systems any where in the string. Guide to Oracle 10g
The underscore ( _ ) wildcard character represents a single character. For example, the search condition WHERE s_class LIKE ‘_R’ retrieves all values for S_CLASS in which the first character can be any value, but the second character must be the letter R. • You can use the underscor ( _ ) and percent sign (%) wildcard characters together in a single search condition. For example, the search condition WHERE c_sec-day LIKE ‘_T%’ retrieves all course sections that meet on Tuesday, provided exactly one character proceeds T in the C_SEC_DAY column. The search condition ignores all of the characters that follow T in the column table value, so the query retrieves values such as MT, MTW, and MTWRF. Guide to Oracle 10g
Sorting Query Output • ORDER BY clause • Sort query output • Syntax for select with ordered results SELECT columnname1, columnname2, … FROM ownername.tablename WHERE search_condition ORDER BY sort_key_column; • Sort can be ascending or descending • Can specify multiple sort keys Guide to Oracle 10g
Using Calculations in SQL Queries • Perform many calculations directly within SQL queries • Very efficient way to perform calculations • Create SQL queries • Perform basic arithmetic calculations • Use variety of built-in functions Guide to Oracle 10g
Arithmetic operations on retrieved data • Addition (+) • Subtraction (-) • Multiplication (*) • Division (/) • Example: SELECT inv_id, qoh*price FROM inventory; Guide to Oracle 10g
Performing Arithmetic Calculations • Perform arithmetic calculations on columns that have data types • NUMBER • DATE • INTERVAL • SYSDATE pseudocolumn • Retrieves current system date • Use + and – to calculate differences between dates Guide to Oracle 10g
Interval Calculation • The oracle 10g DBMS can perform calculations using interval values that store elapsed time value. Guide to Oracle 10g
Ex: suppose you need to update the TIME_ENROLLED column every month by adding one month to the interval value. • SELECT s_id, time_enrolled+TO_YMINTERVAL(‘0-1’) FROM student; • Similarity, you use the following query to add an interval of 10 minutes to the c_SEC_DURATION column in the COURSE_SECTION table. • SELECT c-sec_id, c_sec_duration +TO_DSINTERVAL (‘0 00:10:00’) • FROM course_section; Guide to Oracle 10g
Oracle 10g SQL Functions • Built-in functions perform calculations and manipulate retrieved data values • Called single-row functions • Return single result for each row of data retrieved • To use: • List function name in SELECT clause followed by required parameter in parentheses Guide to Oracle 10g
Single-row Character Functions Guide to Oracle 10g
Single-row Data Functions Guide to Oracle 10g
Oracle 10g SQL Group Functions • Group function • Performs operation on group of queried rows • Returns single result such as column sum • To use: • List function name followed by column name in parentheses Guide to Oracle 10g
Using the COUNT Group Function • The COUNT group function returns an integer that represents the number of rows that a query returns. The COUNT(*) version of this function calculates the total number of rows in a table that satisfy a given search condition. The COUNT(*) function is the only group function in Table3-10 that include NULL values. The other functions ignore NULL values. Guide to Oracle 10g
Using the GROUP BY Clause to Group Data • GROUP BY clause • Group output by column with duplicate values • Apply group functions to grouped data • Syntax • GROUP BY group_columnname; • Follows FROM clause • All columns listed in SELECT clause must be included in GROUP BY clause Guide to Oracle 10g
Using the HAVING Clause to Filter Grouped Data • HAVING clause • Place search condition on results of queries that display group function calculations • Syntax • HAVING group_functioncomparison_operator value • Example • HAVING sum(capacity) >= 100 Guide to Oracle 10g
Creating Alternate Column Headings • Column headings for retrieved columns are names of database table columns • Specify alternate output heading text SELECT columnname1 "heading1_text ", columnname2 "heading2_text", … Guide to Oracle 10g
Creating Alternate Column Headings (continued) • Alias • Alternate name for query column • Syntax • SELECT columnname1 AS alias_name1… Guide to Oracle 10g
Formatting Data Using Format Models • TO_CHAR function • Convert column to character string • Apply desired format model to value • Syntax • TO_CHAR(column_name, 'format_model') • Use for data types • DATE • INTERVAL • NUMBER Guide to Oracle 10g
