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


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


Clauses are usually placed on separate lines.

Indents are used to enhance readability.

Basic SELECT Statement


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

SELECT identifies what column FROM identifies which table

Selecting All Columns


SELECT *FROM doctor;

Selecting Specific Columns


SELECT doc_name, areaFROM doctor;

Using Arithmetic Operations


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


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


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

Arithmetic expressions containing a null value evaluate to null

Column Alias


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


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


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


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


SELECT DISTINCT areaFROM doctor;

-- how many area??????

Limiting the Rows Selected


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

FROM tableWHERE condition(s);

The Where Clause


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


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


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

WHERE Clause


SELECT doc_nameFROM doctorWHERE chgperappt >= 40;

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

Other Comparison Conditions


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

IN (set) LIKE IS NULL IS NOT NULL

BETWEEN Condition


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


SELECT doc_name, annual_bonusFROM doctorWHERE annual_bonus BETWEEN 2000 AND 4000;

-- how many people?

The IN Operator


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


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

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

The IS NULL Operator


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


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


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


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


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


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


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


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


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

info 2102 l4 basic select statement lab1

Health & Medicine