chhapter 1 basic sql statements.ppt

7/25/2019 ChHAPTER 1 BASIC sql STATEMENTS.ppt

http://slidepdf.com/reader/full/chhapter-1-basic-sql-statementsppt 1/26

Writing BasicSQL Statements



Objectives

• After completing this lesson, youshould be able to do the following:

– List the capabilities of SQL SELECstatements

– E!ecute a basic SELEC statement



Capabilities of SQL SELECTStatementsSelectionSelection ProjectionProjection

Table 1Table 1 Table 2Table 2

Table 1Table 1 Table 1Table 1JoinJoin



Basic SELECT Statement

SELECT [DISTINCT] {*, column [ alias ],...}FROM table;

SELECT [DISTINCT] {*, column [ alias ],...}FROM table;

– SELEC identifies what columns" – #$%& identifies which table"



Writing SQL Statements

– SQL statements are not case sensiti'e" – SQL statements can be on one or

more lines" – (eywords cannot be abbre'iated or split

across lines" – Clauses are usually placed on separate

lines" – abs and indents are used to enhance

readability"



Selecting All Columns

DEPTNO DNAME LOC--------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARC DALLAS !0 SALES C ICAGO "0 OPERATIONS #OSTON

S$L% SELECT * 2 FROM &'()



Selecting Specific Columns

DEPTNO LOC--------- ------------- 10 NEW YORK 20 DALLAS !0 C ICAGO "0 #OSTON

S$L% SELECT &'()+ , 2 FROM &'()



Column Heading efaults

– )efault *ustification• Left: )ate and character data

• $ight: +umeric data – )efault display: ppercase



Arit!metic E"pressions• Create e!pressions on + &-E$ and

)A E data by using arithmeticoperators"

Operator

+

-

*

/

Description

Add

Subtract

Multiply

Di ide



#sing Arit!metic Operators

S$L% SELECT '+/ ', / , / !00 2 FROM ' (

ENAME SAL SAL !00

---------- --------- ---------KING 3000 3!00#LAKE 2430 !130CLARK 2"30 25306ONES 2753 !253

MARTIN 1230 1330

ALLEN 1800 1700...1" 9 : ' ' )'&.



Operator $recedence

– &ultiplication and di'ision ta.e priority

o'er addition and subtraction" – %perators of the same priority are

e'aluated from left to right" – /arentheses are used to force prioriti0ed

e'aluation and to clarify statements"

** / / ++ ! !



Operator $recedenceS$L% SELECT '+/ ', / , 12* / 100 FROM ' (

ENAME SAL 12*SAL 100

---------- --------- ----------KING 3000 80100#LAKE 2430 !"!00CLARK 2"30 273006ONES 2753 !3400

MARTIN 1230 13100

ALLEN 1800 17!00...1" 9 : ' ' )'&.



#sing $arent!esesS$L% SELECT '+/ ', / , 12*; / 100< 2 FROM ' (

ENAME SAL 12*;SAL 100<

---------- --------- -----------KING 3000 81200#LAKE 2430 !3"00CLARK 2"30 !08006ONES 2753 !8700

MARTIN 1230 18200

...1" 9 : ' ' )'&.



efining a %ull &alue – A null is a 'alue that is una'ailable,unassigned, un.nown, or inapplicable" – A null is not the same as 0ero or a blan.

space"

S$L% SELECT '+/ ', = >, 2 FROM ' (

ENAME 6O# COMM ---------- --------- ---------KING PRESIDENT#LAKE MANAGER ...TURNER SALESMAN 0...1" 9 : ' ' )'&.



%ull &alues

in Arit!metic E"pressions• Arithmetic e!pressions containing anull 'alue e'aluate to null"

S$L% ' ' ) '+/ ', 12* /2 ?9 ' (

! W ERE '+/ '@ KING

ENAME 12*SAL COMM---------- -----------KING



efining a Column Alias

– $enames a column heading – 1s useful with calculations

– 1mmediately follows column name2optional AS .eyword between columnname and alias

– $e3uires double 3uotation mar.s if itcontains spaces or special characters oris case sensiti'e



#sing Column Aliases

S$L% SELECT '+/ ' AS +/ ', / / /9B 2 FROM ' (

NAME SALARY

------------- ---------

...

S$L% SELECT '+/ ' N/ ' , 2 / *12 A++ / S/ /9B ! FROM ' (

N/ ' A++ / S/ /9B

------------- -------------

...



Concatenation Operator

– Concatenates columns or characterstrings to other columns

– 1s represented by two 'ertical bars 4556 – Creates a resultant column that is a

character e!pression



#sing t!e Concatenation Operator

S$L% SELECT '+/ ' = > AS E ( B'' 2 FROM ' (

E ( B''-------------------KINGPRESIDENT#LAKEMANAGER CLARKMANAGER 6ONESMANAGER

MARTINSALESMAN ALLENSALESMAN

...1" 9 : ' ' )'&.



Literal C!aracter Strings

– A literal is a character, e!pression, ornumber included in the SELEC list"

– )ate and character literal 'alues must beenclosed within single 3uotation mar.s"

– Each character string is output once foreach row returned"



#sing Literal C!aracter Strings

E ( B'' D')/-------------------------KING / PRESIDENT#LAKE / MANAGER CLARK / MANAGER 6ONES / MANAGER

MARTIN / SALESMAN...1" 9 : ' ' )'&.

E ( B'' D')/-------------------------KING / PRESIDENT#LAKE / MANAGER CLARK / MANAGER 6ONES / MANAGER

MARTIN / SALESMAN...1" 9 : ' ' )'&.

S$L% SELECT '+/ ' / = >2 AS E ( B'' D')/

! FROM ' (



uplicate 'o(s

• he default display of 3ueries is allrows, including duplicate rows"

S$L% SELECT &'()+ 2 FROM ' (

S$L% SELECT &'()+ 2 FROM ' (

DEPTNO--------- 10 !0

10 20...1" 9 : ' ' )'&.



Eliminating uplicate 'o(sEliminate duplicate rows by using the)1S 1+C .eyword in the SELEC clause"

S$L% SELECT DISTINCT &'()+ 2 FROM ' (

DEPTNO--------- 10 20

!0



ispla)ing Table Structure• se the SQL7/lus )ESC$1-E

command to display the structure of atable"

DESC[RI#E] tablenameDESC[RI#E] tablename



ispla)ing Table Structure

S$L% DESCRI#E &'()S$L% DESCRI#E &'()

N/ ' N TB('----------------- -------- ------------DEPTNO NOT NULL NUM#ER;2<DNAME HARC AR2;1"<LOC HARC AR2;1!<

N/ ' N TB('----------------- -------- ------------

DEPTNO NOT NULL NUM#ER;2<DNAME HARC AR2;1"<LOC HARC AR2;1!<



$ractice Overvie(

– Selecting all data from different tables – )escribing the structure of tables

– /erforming arithmetic calculations andspecifying column names – sing SQL7/lus editor

chhapter 1 basic sql statements.ppt

Documents