Database Management Systems SQL And DDL language

1-2 Copyright © Oracle Corporation, 2001. All rights reserved. Objectives After completing this lesson, you should be able to do the following: • List the capabilities of SQL SELECT statements • Execute a basic SELECT statement • Differentiate between SQL statements and iSQL*Plus commands

1-4 Copyright © Oracle Corporation, 2001. All rights reserved. Basic SELECT Statement SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; • SELECT identifies what columns • FROM identifies which table

1-7 Copyright © Oracle Corporation, 2001. All rights reserved. Writing SQL Statements • SQL statements are not case sensitive. • SQL statements can be on one or more lines. • Keywords cannot be abbreviated or split across lines. • Clauses are usually placed on separate lines. • Indents are used to enhance readability.

1-8 Copyright © Oracle Corporation, 2001. All rights reserved. Column Heading Defaults • iSQL*Plus: – Default heading justification: Center – Default heading display: Uppercase • SQL*Plus: – Character and Date column headings are left- justified – Number column headings are right-justified – Default heading display: Uppercase

1-9 Copyright © Oracle Corporation, 2001. All rights reserved. Arithmetic Expressions Create expressions with number and date data by using arithmetic operators. Operator + - * / Description Add Subtract Multiply Divide

1-11 Copyright © Oracle Corporation, 2001. All rights reserved. Operator Precedence • Multiplication and division take priority over addition and subtraction. • Operators of the same priority are evaluated from left to right. • Parentheses are used to force prioritized evaluation and to clarify statements. * / + _

1-14 Copyright © Oracle Corporation, 2001. All rights reserved. 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_pct FROM employees; … …

1-15 Copyright © Oracle Corporation, 2001. All rights reserved. SELECT last_name, 12*salary*commission_pct FROM employees; Null Values in Arithmetic Expressions Arithmetic expressions containing a null value evaluate to null. … …

1-16 Copyright © Oracle Corporation, 2001. All rights reserved. Defining a Column Alias A column alias: • Renames a column heading • Is useful with calculations • Immediately follows the column name - there can also be the optional AS keyword between the column name and alias • Requires double quotation marks if it contains spaces or special characters or is case sensitive

1-17 Copyright © Oracle Corporation, 2001. All rights reserved. Using Column Aliases SELECT last_name "Name", salary*12 "Annual Salary" FROM employees; SELECT last_name AS name, commission_pct comm FROM employees; … …

1-18 Copyright © Oracle Corporation, 2001. All rights reserved. Concatenation Operator A concatenation operator: • Concatenates columns or character strings to other columns • Is represented by two vertical bars (||) • Creates a resultant column that is a character expression

1-20 Copyright © Oracle Corporation, 2001. All rights reserved. Literal Character Strings • A literal is a character, a number, or a date included in the SELECT list. • Date and character literal values must be enclosed within single quotation marks. • Each character string is output once for each row returned.

1-23 Copyright © Oracle Corporation, 2001. All rights reserved. Eliminating Duplicate Rows Eliminate duplicate rows by using the DISTINCT keyword in the SELECT clause. SELECT DISTINCT department_id FROM employees;

1-24 Copyright © Oracle Corporation, 2001. All rights reserved. SQL and iSQL*Plus Interaction SQL statements Oracle server Query results iSQL*Plus commands Client Formatted report Internet Browser iSQL*Plus

1-25 Copyright © Oracle Corporation, 2001. All rights reserved. SQL Statements Versus iSQL*Plus Commands SQL statements SQL • A language • ANSI standard • Keyword cannot be abbreviated • Statements manipulate data and table definitions in the database iSQL*Plus • An environment • Oracle proprietary • Keywords can be abbreviated • Commands do not allow manipulation of values in the database • Runs on a browser • Centrally loaded, does not have to be implemented on each machine iSQL*Plus commands

1-26 Copyright © Oracle Corporation, 2001. All rights reserved. Overview of iSQL*Plus After you log into iSQL*Plus, you can: • Describe the table structure • Edit your SQL statement • Execute SQL from iSQL*Plus • Save SQL statements to files and append SQL statements to files • Execute statements stored in saved files • Load commands from a text file into the iSQL*Plus Edit window

1-34 Copyright © Oracle Corporation, 2001. All rights reserved. Summary SELECT *|{[DISTINCT] column|expression [alias],...} FROM table; In this lesson, you should have learned how to: • Write a SELECT statement that: – Returns all rows and columns from a table – Returns specified columns from a table – Uses column aliases to give descriptive column headings • Use the iSQL*Plus environment to write, save, and execute SQL statements and iSQL*Plus commands.

1-35 Copyright © Oracle Corporation, 2001. All rights reserved. Practice 1 Overview This practice covers the following topics: • Selecting all data from different tables • Describing the structure of tables • Performing arithmetic calculations and specifying column names • Using iSQL*Plus

Database Management Systems SQL And DDL language

More Related Content

Similar to Database Management Systems SQL And DDL language

Recently uploaded

Database Management Systems SQL And DDL language