Oracle Database 11g SQL Fundamentals Lab 1

2y ago
24 Views
3 Downloads
467.56 KB
45 Pages
Last View : 22d ago
Last Download : 2m ago
Upload by : Averie Goad
Transcription

Oracle Database 11gSQL Fundamentals – Lab 1

Lab Rules You MUST attend in your section. Please commit to the lab start time. Oracle 11g INSTALLATION:– Refer to “ Installation – Database.ppt” fordatabase installation2

Structured Query Language(SQL)

SQL, PL/SQL, and SQL*PLUS SQL: Structured Query Language, What to do NOT - How to do. PL/SQL: Procedural Language SQL, a completelanguage that contains loops, if s etc. SQL Developer: An execution environment to writeSQL and PL/SQL (the program itself).4

Data retrieval command (DRC)

Basic SELECT StatementSELECTFROM* {[DISTINCT] column expression [alias],.}table;In its simplest form, a SELECT statement mustinclude the following: SELECT identifies what columns FROM identifies which table6

Selecting All ColumnsSELECT *FROMdepartments;7

Selecting Specific ColumnsSELECT department id, location idFROMdepartments;In the SELECT clause, specify the columns that you want,in the order in which you want them to appear in the output.8

Using Arithmetic OperatorsSELECT last name, salary, salary 300FROMemployees; Note that the resultant calculated column SALARY 300 is not a newcolumn in the EMPLOYEES table; it is for display only. By default, thename of a new column comes from the calculation “salary 300”9

Operator Precedence* / - Multiplication and division take priority over additionand subtraction. Operators from the same priority are evaluated fromleft to right. Parentheses are used to enforceevaluation and to clarify statements.10prioritized

Operator PrecedenceSELECT last name, salary, 12*salary 100FROMemployees; SELECT last name, salary, salary 100*12FROMemployees;11

Using ParenthesesSELECT last name, salary, 12*(salary 100)FROMemployees; You can override the rules of precedence by using parenthesesto specify the order in which operators are executed.12

Defining a Null Value A null is a value that is unavailable, unassigned,unknown, or inapplicable. A null is not the same as zero or a blank space.SELECT last name, job id, salary, commission pctFROMemployees; 13

Null Valuesin Arithmetic ExpressionsArithmetic expressions containing a null value evaluate tonull.SELECT last name, 12*salary*commission pctFROMemployees; 14

Defining a Column AliasA column alias: Renames a column heading Is useful with calculations Immediately follows the column name The optional AS keyword may be usedbetween thecolumn name and alias Requires double quotation marks if it contains spacesor special characters or is case sensitive15

Using Column AliasesSELECT last name AS name, commission pct commFROMemployees; SELECT last name "Name", salary*12 "Annual Salary"FROMemployees; 16

Concatenation OperatorA concatenation operator: Concatenates columns or character strings toother columns Is represented by two vertical bars ( )Creates a resultant column that is a characterexpression17

Using the Concatenation OperatorSELECT last name job id AS "Employees"FROM employees; 18

Literal Character Strings A literal is a character, a number, or a dateincluded in the SELECT list. Date and character literal values must be enclosedwithin single quotation marks. Each character string is output once for eachrow returned.19

Using Literal Character StringsSELECT last name ' is a ' job idAS "Employee Details"FROMemployees; 20

Duplicate RowsThe default display of queries is all rows, includingduplicate rows.SELECT department idFROMemployees; 21

Eliminating Duplicate RowsEliminate duplicate rows by using the DISTINCT keywordin the SELECT clause.SELECT DISTINCT department idFROMemployees;22

Eliminating Duplicate Rows Youcan specify multiple columns after theDISTINCT qualifier. The DISTINCT qualifier affectsall the selected columns, and the result is everydistinct combination of the columns. You can not specifyDISTINCT qualifier.23columnsbeforethe

Restricting Data

Limiting the Rows Selected Restrict the rows returned by using the WHERE clause.SELECTFROM[WHERE * {[DISTINCT] column expression [alias],.}tablecondition(s)];The WHERE clause follows the FROM clause. It consistsof three elements:– Column name– Comparison operator– Column name, constant, or list of values25

Using the WHERE ClauseA WHERE clause contains a condition that must be met.If the condition is true, the row meeting the condition is returned.SELECT employee id, last name, job id, department idFROMemployeesWHERE department id 90 ; Equal , Less than , Greater than , Not equal26

Character Strings and Dates Character strings and date values are enclosedin single quotation marks. Character values are case sensitiveDate values are format sensitive.SELECT last name, job id, department idFROMemployeesWHERE last name 'Whalen'; All character searches are case sensitive.27

Using Comparison ConditionsSELECT last name, salaryFROMemployeesWHERE salary 3000;28

Other Comparison ConditionsOperatorMeaningBETWEEN.AND.Between two values (inclusive),IN(set)Match any of a list of valuesLIKEMatch a character patternIS NULLIs a null value29

The BETWEEN ConditionUse the BETWEEN condition to display rows based ona range of values.SELECT last name, salaryFROMemployeesWHERE salary BETWEEN 2500 AND 3500;Lower limitUpper limitValues specified with the BETWEEN condition are inclusive.You must specify the lower limit first.30

The IN Condition Use the IN membership condition to test for values ina list.The IN operator can be used with any datatype.SELECT employee id, last name, salary, manager idFROMemployeesWHERE manager id IN (100, 101, 201);31

The LIKE Condition Use the LIKE condition to perform wildcardsearches of valid search string values. Search conditions can contain either literalcharacters or numbers:– % denotes zero or many characters.– denotes one character.SELECTFROMWHEREfirst nameemployeesfirst name LIKE 'S%';32

The LIKE Condition– The following example displays the last namesand hire dates of all employees who joinedbetween January 1995 and December 1995:SELECT last name, hire dateFROMemployeesWHERE hire date LIKE '%95';33

Using the LIKE Condition You can combine pattern-matching characters.SELECT last nameFROMemployeesWHERE last name LIKE ' o%';34

The NULL ConditionsTest for nulls with the IS NULL operator.SELECT last name, manager idFROMemployeesWHERE manager id IS NULL;35

Logical ConditionsOperatorMeaningANDReturns TRUE if both componentconditions are trueORReturns TRUE if either componentcondition is trueNOTReturns TRUE if the followingcondition is falseYou can use several conditions in one WHERE clauseusing the AND and OR operators.36

Using the AND OperatorAND requires both conditions to be true.SELECTFROMWHEREANDemployee id, last name, job id, salaryemployeessalary 10000job id LIKE '%MAN%';37

Using the OR OperatorOR requires either condition to be trueSELECTFROMWHEREORemployee id, last name, job id, salaryemployeessalary 10000job id LIKE '%MAN%';38

Using the NOT OperatorSELECT last name, job idFROMemployeesWHERE job idNOT IN ('IT PROG', 'ST CLERK', 'SA REP');39

Sorting Data

The ORDER BY Clause The ORDER BY clause is last in SELECT statement.The default sort order is ascending.You can sort by column name, expressions or aliases.Null values are displayed:–Last for ascending order–First for descending orderSELECTlast name, job id, department id, hire dateFROMemployeesORDER BY hire date;41

Sorting in Descending Order The sort order can be reversed by using DESC.SELECTlast name, job id, department id, hire dateFROMemployeesORDER BY hire date DESC ; 42

Sorting by Multiple Columns The order of ORDER BY clause list is order of sort.SELECTlast name, job id, department id, hire dateFROMemployeesORDER BY department id, last name; You can order by position, e.g. 2nd column in select clause.SELECTlast name, job id, department id, hire dateFROMemployeesORDER BY 2; You can sort by a column that is not in the SELECT list.43

General SyntaxSELECT{ * [DISTINCT] column [alias], }FROMtable[WHEREcondition (s)][ORDER BY{column exp alias} [ASC DESC]];44

Thank You

Oracle Database 11g SQL Fundamentals . – Refer to “ Installation –Database.ppt” for database installation. Structured Query Language (SQL) 4 SQL, PL/SQL, and SQL*PLUS

Related Documents:

Oracle Database 11g Pl a n Ba sel ine Plan History HJ GB 4.Upgrade to 11g 5.Migrate stored outlines into SPM Figure 4 Bulk load the SMB after upgrade using Stored outlines. From the Cursor Cache Starting in Oracle Database 11g it is possible to load plans for statements directly from the cursor cache into the SQL Management Base.File Size: 417KBPage Count: 26

Oracle Database 11g Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions, Second Edition Dear Reader, Expert Oracle Database Architecture, 2nd Edition is a book that explores and defines the Oracle d

Oracle is a registered trademark and Designer/2000, Developer/2000, Oracle7, Oracle8, Oracle Application Object Library, Oracle Applications, Oracle Alert, Oracle Financials, Oracle Workflow, SQL*Forms, SQL*Plus, SQL*Report, Oracle Data Browser, Oracle Forms, Oracle General Ledger, Oracle Human Resources, Oracle Manufacturing, Oracle Reports,

Oracle TIGHT / Oracle Database 11g & MySQL 5.6 Developer Handbook / Michael McLaughlin / 885-8 Oracle TIGHT / Oracle Database 11g & MySQL 5.6 Developer Handbook / Michael McLaughlin / 885-8 Chapter 1: Architecture 5 The client software component provides an interactive and batch interface that lets you and your programs interact with the database.

Oracle 11g New Features for Administrators . Summary Sheets . Version. 2.3 . . Oracle Database 11g New Features for DBAs and Developers, by Sam R. Alapati and Charles Kim, Apress, ISBN: 978-1-59059-910-5 . 2 . Book . Oracle Database 11g New Features by Rober G. Freeman, Oracle Press . 3 .

PeopleSoft Oracle JD Edwards Oracle Siebel Oracle Xtra Large Model Payroll E-Business Suite Oracle Middleware Performance Oracle Database JDE Enterprise One 9.1 Oracle VM 2.2 2,000 Users TPC-C Oracle 11g C240 M3 TPC-C Oracle DB 11g & OEL 1,244,550 OPTS/Sec C250 M2 Oracle E-Business Suite M

Oracle Database 11g reinvents files in the database . from within the database Produce PDF files from Result Implement database-resident Content Management System HTTP Call-Out . New in 11g release PL/SQL, Oracle Call Interface (OCI) and Pro*C Support.

API and DNV codes describe slightly different approaches to assess the axial bearing capacity of a pile. These codes provide guidline for the calculation of pile length in common soil conditions such as clay (cohesive) or sand (cohesionless). The assessment also depends on the type of soil information available i.e. laboratory test results showing soil properties such as undrained shear .