info 2102 l4 basic select statement lab1

International Islamic University MalaysiaDepartment of Information Systems

Kulliyyah of Information & Communication Technology

LECTURE 4: BASIC SELECT STATEMENTS

Dr. Mira Kartiwi

Capabilities of SQL SELECT Statements

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Selection (Restriction) – Allows for the retrieval of rows that satisfy certain specified condition (predicate).

Projection – Allows for the retrieval of specified columns (attributes).

Joining – Allows for the linking of data in different tables.

More About SELECT

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT is technically a Data Manipulation Language (DML). However, Oracle does not classify it as such.

You can write SELECT statements on multiple lines. However, you are not allowed to split or abbreviate keywords.

SELECT statements are not case sensitive.

More About SELECT

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Clauses are usually placed on separate lines.

Indents are used to enhance readability.

Basic SELECT Statement

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

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

SELECT identifies what column FROM identifies which table

Selecting All Columns

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT *FROM doctor;

Selecting Specific Columns

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT doc_name, areaFROM doctor;

Using Arithmetic Operations

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT doc_name, (annual_bonus+ 500)FROM doctor; Four arithmetic operations according to

precedence: (*, /), (+, -)

Operators of the same priority are evaluated from left to right.

Parentheses are used to force prioritized evaluation and to clarify statements.

Operator Precedence and Parantheses

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT doc_name, annual_bonus, 10*annual_bonus+500 FROM doctor;

SELECT doc_name, annual_bonus, 10*(annual_bonus+500) FROM doctor;

--What is the difference????

NULL Values

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

How many doctor has Null value in the result???Why??

Arithmetic expressions containing a null value evaluate to null

Column Alias

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Renames a column heading. Useful with calculations. Immediately follows the column name – there can

also be the optional AS keyword between column name and alias.

Requires double quotation marks if it contains spaces or special characters or is case sensitive.

Column Alias

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT doc_name AS name, annual_bonusFROM doctor

SELECT doc_name AS “Name”, annual_bonus AS “Bonus”FROM doctor

SELECT doc_name, annual_bonus AS “Bonus Upgrade”FROM doctor

Concatenation Operator

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT doc_name||area AS “Doctor”FROM doctor; Concatenates columns or character

strings to other columns. Is represented by two vertical bars (||). Creates a resultant column that is a

character expression.

Literal Character String

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT doc_name || ' is from ' || area AS “DOCTOR”FROM doctor; A literal is a character, a number, or a date

included in the SELECT statement. Date and character literal values must be

enclosed within single quotation marks. Each character string is output once for each row

returned.

Eliminating Duplicate Rows

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT DISTINCT areaFROM doctor;

-- how many area??????

Limiting the Rows Selected

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT *| { [DISTINCT] column | expression[alias],...}

FROM tableWHERE condition(s);

The Where Clause

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

The WHERE clause can be added to the SELECT statement to restrict the results to rows that satisfy a specified condition.

Rows that do not meet the condition will not be included in the results.

Comparisons

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

The two expressions must be of the same type

Character string literals should be enclosed in single quotes

Date literals should be of the form 'DD-MON-YY'

Numeric literals should consist of digits and optionally, a decimal and/or sign (no commas or dollar signs)

Comparison Conditions

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Equal (=) Greater than (>) Greater than or equal to (>=) Less than (<) Less than or equal to (<=) Not equal to (<>, !=, ^=)

WHERE Clause

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT doc_nameFROM doctorWHERE chgperappt >= 40;

--What does it means????? -- how many doctor name James????? -- What area is Stevenson?????

Other Comparison Conditions

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Between two values (inclusive) –BETWEEN... AND...

IN (set) LIKE IS NULL IS NOT NULL

BETWEEN Condition

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Several special operators serve as shortcuts for longer expressions.One is the BETWEEN operator.

It is used to determine whether or not a value lies within a specific range (including the end points)

General syntax:Expression1 BETWEEN Expression2 ANDExpression 3

BETWEEN Condition

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT doc_name, annual_bonusFROM doctorWHERE annual_bonus BETWEEN 2000 AND 4000;

-- how many people?

The IN Operator

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Used to see if a value occurs in a set of possible values

The set of possible values is specified within parentheses with commas between values

IN Condition

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT doc_id, doc_name, areaFROM doctorWHERE doc_id IN (100,356, 558);

-- what area are they in???????

The IS NULL Operator

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Any comparison to a null value that uses the standard comparison operators will not yield a match.

If a check for null values is needed, the IS NULL operator must be used.

The IS NULL Operator

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Display the IDs of doctors that do not receive annual bonuses.

SELECT doc_idFROM doctorWHERE annual_bonus IS NULL;

--What is the name?---

The LIKE Operator

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Used in conjunction with wildcard characters to match character string patterns

% is used to match zero or more characters

_ is used to match a single character Wildcards cannot be used without the

LIKE operator The LIKE operator should not be used

without wildcards

LIKE Condition

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT *FROM doctorWHERE doc_name LIKE 'J%';-- change to lowercase j

Note:– Represents any sequence of zero or more

characters (%)– Represents a single character (_)

The LIKE Operator

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Display the full name and phone number for customers whose phone number begins with 549-67SELECT pt_lname || ', ' || pt_fname "FULL NAME"FROM patientWHERE ptdob LIKE ’13-MAY-__' ;

Logical Operators

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

3 standard logical operators (AND, OR, andNOT) are used to combine expressions

AND will return a value of true only if both expressions are true

OR will return a value of true if either or both of the expressions are true

NOT will return the opposite value of the expression.

Order of precedence: NOT, AND, OR

Other NOT Operators

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

The NOT operator can also be used in conjunction with the special operators as follows: NOT BETWEEN NOT IN IS NOT NULL NOT LIKE

NULL and NOT NULL Conditions

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

SELECT docid, docname, annual_bonus FROM doctor WHERE annual_bonus IS NULL;

TO NEGATE ..

SELECT docid, docname, annual_bonus FROM doctor WHERE annual_bonus IS NOT NULL;

Lab Assignment 1

International Islamic University Malaysia Kuliyyah of ICT – Department of Information Systems

Run the hospital script and create a question in English as well as its SQL query that generates: NULL values Values not within a specific range List of names that started from or ended

with specific letter