随笔分类 - 11G SQL FDM
摘要:Rolling Back Changers to a MarkerCreate a marker in the current transaction by using the SAVEPOINT statement.Roll back to that marker by using the ROLLBACK TO SAVEPOINT statement.UPDATE ...SAVEPOINT update_done;INSERT ...ROLLBACK TO update_done;Implict Transaction ProcessingAn automatic commit occur
阅读全文
摘要:Changing Data in a TableUPDATE Statement SyntaxModify existing values in a table with the UPDATE statementUPDAE tableSET column = value [,column = value,...][WHERE condition];Update more than one row at a time(if required)Updating Rows in a TableValuesfor a specific row or rows are modified if you s
阅读全文
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Describe each data manipulation language(DML) statement.Insert rows into a table.Update rows in a table.Delete rows from a table.Control transactions.Lesson AgendaAdding new rows in a table-INSERT statementChanging data in
阅读全文
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Describe set operators.Use a set operator to combine multiple queries into a single query.Control the order of rows returned.Lesson AgendaSet Operators:Types and guidelinesTables used in this lessonUNION and UNION ALL oper
阅读全文
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Define subqueries.Describe the types of problems that the subqueries can solve.List the types of subqueries.Write single-row and multiple-row subqueries.single-row:查询结果返回一条记录multiple-row:查询结果返回多条记录Lesson AgendaSubquery:Typ
阅读全文
摘要:Applying Additional Condtions to a JoinUse the AND cluause or the WHERE clause to aplly additional condtions:AND CLAUSESQL> SELECT e.employee_id,e.last_name,d.department_id,d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149;EMPLOYEE
阅读全文
摘要:Creating Natural JoinsThe NATURAL JOIN clause is based on all columns in the two tables that have the same name.It selects rows from the two tables that have equal values in all matched columns.If the columns having the same names have different data types,an error is returned.Retrieving Records wit
阅读全文
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Write SELECT statements to access data from more than one table using equijoins and nonequijoins.Join a table to itself by using a self-joinView data that generally does not meet a join condtions by using outer joinsGenera
阅读全文
摘要:Using the GROUP BY ClauseThe GROUP BY column does not have to be in the SELECT list.(不过这样一般意义不大)View Code SQL> SELECT AVG(salary) AvgSalary FROM employees GROUP BY department_id ORDER BY AVGSALARY; AVGSALARY----------3475.55556 4150 4400 5760 6500 70008601.333338955.88235 ...
阅读全文
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Identify the available group functionsDescribe the use of group functionsGroup data by using the GROUP BY clauseInclude or exclude grouped rows by using the HAVING clauseLesson AgendaGroups functions:-Types and syntax-Use
阅读全文
摘要:ObjectivesAfter completing this lesson,you should be able to do the following:Limit the rows that are retrieved by a query.Sort the rows that are retrieved by a query.Use ampersand substitution to restrict and sort output at run time.Lesson AgendaLimiting rows with:-The WHERE clause-The comparison c
阅读全文
摘要:ObjectiveAfter completing this lesson,you should be able to do the following:List the capabilities of SQL statements.Execute a basic SELECT statementLesson AgendaBasic SELECT statementArithmetic expressions and NULL values in the SELECT statementColumn aliasesUse of concatenation operator,literal ch
阅读全文
摘要:Realational Database Terminology1->row;2->主键列;3->普通列;4->外键列;5->普通字段值;6->普通字段null值;Using SQL to Query Your DatabaseStructured query language(SQL) is:The ANSI standard languagte for operating relational databaseEfficient,easy to learn,and useFunctionally complete(With SQL,you can def
阅读全文
摘要:ObjectivesAfter comleting this lesson,you should be able to do the following:Understant the goals of course.Discuss the theoretical and physical aspect of arelational database.Identify the development environments that can be used fo this coureDescribe the database and schema used in this course.Cou
阅读全文
摘要:Conditional ExpressionsPrvoide the use of the IF-THEN-ELSE logic within a SQL statementUse two methods-CASE expression(SQL标准)-DECODE function(专属Oracle)CASE ExpressionFacilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement:CASE expr WHEN comparison_expr1 THEN return_expr1 ...
阅读全文
摘要:General FunctionsThe following functions work with any data ype and pertain to using nulls:NVL(expr1,expr2)NVL2(expr1,expr2,expr3)NULLIF(expr1,expr2)COALESCE(expr1,expr2,...,exprn)NVL FunctionConverts an null value to an actual value:Data types that can be used are date,character,and number.Data typ
阅读全文
摘要:Nesting FunctionsSingle-row functions can be nested to any level.Nested functions are evaluated from the deepese level to the least deep level.View Code SQL> SELECT last_name,UPPER(CONCAT(SUBSTR(LAST_NAME,1,8),'_US')) FROM employees WHERE department_id = 60;LAST_NAME UPPER(CONCAT(SUB...
阅读全文
摘要:Conversions FunctionsImplicit Data Type ConversionIn expressions,the Oracle server can automatically conver the following:FromToVARCHAR2 or CHARNUMBERVARCHAR2 or CHARDATEDemo-01:View Code SQL> SELECT employee_id,first_name,TO_CHAR(hire_date,'YYYY-MM-DD HH24:MI:SS') FROM employees WHERE hi
阅读全文
摘要:Using Conversion Functions and Conditional ExpressionsAfter completing this lesson,you should be able to do the following:Describe various types of conversion functions that are available in SQLUse the TO_CHAR,TO_NUMBER,and TO_DATE conversion functions.Apply conditional expressions in a SELECT state
阅读全文
摘要:Working with DatesThe Oracle database stores dates in an internal numeric format:century,year,month,day,hours,minutes,and seconds.The default date display format is DD-MON-RR.-Enables you to store 21st-century dates in the 20th century by specifying only the last two digits of the year.-Enables you
阅读全文