Lab Lect 2

Introduction to SQL Using Oracle
Lecture:2
Instructor: Nitesh Jha
The SQL SELECT Statement
The SELECT statement is used to select data from a database.
SELECT Syntax
SELECT column1, column2, ...

FROM table_name;
It will select display all the records in two columns from the table.
SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a
specified condition.
WHERE Syntax
SELECT column1, column2, ...

FROM table_name
WHERE condition;
Note: The WHERE clause is not only used in SELECT statement, it

is also used in UPDATE, DELETE statement, etc.!
Simple SQL Query-Where condition
Product PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi
SELECT *
FROM Product
WHERE category='Gadgets'
PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
“selection” Powergizmo 29.99 Gadgets GizmoWorks

Simple SQL Query
Product PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
SELECT PName, Price, Manufacturer

FROM Product
WHERE Price > 100
PName Price Manufacturer
“selection” and SingleTouch $149.99 Canon
“projection” MultiTouch $203.99 Hitachi
SQL QUERY
Four forms of selection:

1. All rows and All columns :
SELECT * FROM TABLE_NAME;
2. All rows and selected column:
SELECT COL1,COL2 FROM TABLE_NAME;
3. Selected rows and all columns:
SELECT * FROM TABLE_NAME WHERE <CONDITION>;
4. Selected rows and Selected columns:
SELECT COL1,COL2 FROM TABLE_NAME WHERE
<CONDITION>;
Creating a Table from a table
CREATE TABLE NEW_TABLE_NAME AS SELECT
COLUMN 1,COLUMN 2,…..,COLUMN N FROM
EXISTING_TABLE_NAME;
• INSERT FROM ANOTHER TABLE
Copy all columns from one table to another table:
INSERT INTO NEW_TABLE
SELECT * FROM EXISTING_TABLE
WHERE CONDITION;
• Copy only some columns from one table into another table:
INSERT INTO NEW_TABLE_NAME(COLUMN 1,
COLUMN 2,…
SELECT COLUMN 1,COLUMN 2,…..
FROM EXISTING_TABLE;
Updating Table Rows
• UPDATE
– Change or Modify data in a table
– Syntax:
• UPDATE TABLE_NAME
SET COLUMN_NAME = VALUE WHERE
CONDITION_LIST;
• If more than one attribute is to be updated in row, separate corrections with
commas
###########One more way to update table###################
• UPDATE TABLE_NAME
SET COLUMN_NAME = ‘&col1’ WHERE
CONDITION1=‘&col2’;
###Just run the command enter updated value and give your condition#####
8
Deleting Table Rows
• DELETE
– The Delete query is used to delete records from a table.
– Syntax:
• DELETE FROM TABLE_NAME
WHERE CONDITION_LIST ;
• WHERE condition is optional
• If WHERE condition is not specified, all rows from
specified table will be deleted
• It will only delete records from the table, Table
structure will already be there.
9
Modifying the Structure of table (DDL
Commands)
Alter table allows changing the structure of an existing table (like add, delete or modify
columns data_types ). SEE BELOW SYNTAX
ADD NEW COLUMN-
ALTER TABLE TABLENAME
ADD NEWCOLUMN1 DATA_TYPE SIZE,
NEWCOLUMN2 DATA_TYPE SIZE,
….
NEW_COLUMN N DATA_TYPE SIZE;
DROP COLUMN- ALTER TABLE TABLENAME DROP COLUMN1;
MODIFY COLUMN- ALTER TABLE TABLENAME MODIFY

COLUMN_NAME DATA_TYPE(SIZE);
RENAME COLUMN- ALTER TABLE TABLENAME RENAME COLUMN

OLD_COLUMN TO NEW_COLUMN;
Renaming and truncating tables
RENAME TABLE_NAME TO NEWTABLE_NAME;

Renames the table.
TRUNCATE TABLE TABLE_NAME;
It is same as Delete but the number of deleted rows cannot be
returned. It DROPs the table completely and recreates the
table structure, which is much faster than deleting it one by
one.
DROP TABLE TABLE_NAME;
It destroys the table from the database and cannot be
recovered again.
SQL ORDER BY KEYWORD
The ORDER BY keyword is used to sort the result-set in ascending or
descending order.
The ORDER BY keyword sorts the records in ascending order by

default. To sort the records in descending order, use the DESC
keyword.
ORDER BY Syntax
SELECT COLUMN1 FROM TABLENAME

ORDER BY COLUMN1
ASC|DESC;
SORTING DATA IN A TABLE
• SELECT * FROM TABLENAME ORDER BY COL1

DESC; //Descending order
• SELECT * FROM TABLENAME ORDER BY COL1
; //Ascending Order
EXAMPLE:
SELECT * FROM STUDENTS ORDER BY REG_NO
DESC;
If you don‟t mention DESC after column name Oracle will
automatically sort it in Ascending order.
SQL condition statement
EXAMPLE FOR COMPARISON
OPERATOR
Query:
Display the name of of instructor who doesn’t belongs to
Physics department?
Ans: SELECT NAME FROM INSTRUCTOR WHERE

DEPT_NAME!='PHYSICS';
EXAMPLE FOR LOGICAL OPERATOR
Query:
Display the name of of instructor who belongs to Physics
department and getting salary less than 9000.
Ans: SELECT NAME FROM INSTRUCTOR WHERE

DEPT_NAME='PHYSICS' AND SALARY<9000;
SQL CONDITION STATEMENT
The SQL SELECT DISTINCT statement
The SELECT DISTINCT statement is used to return only distinct

(different) values.
Inside a table, a column often contains many duplicate values; and

sometimes you only want to list the different (distinct) values.
The SELECT DISTINCT statement is used to return only distinct
(different) values.
SELECT DISTINCT column1, column2, ...

FROM table_name;
ELIMINATE DUPLICATE ROWS
WHEN USING SELECT
STATEMENT
• SELECT DISTINCT * FROM TABLE_NAME;
• SELECT DISTINCT COL1_NAME,COL2_NAME
FROM TABLE_NAME;
• SELECT DISTINCT COL1_NAME,COL2_NAME
FROM TABLE_NAME WHERE CONDITION1;
Example
f. SELECT DISTINCT DEPT_NAME FROM
DEPARTMENT;
CHECK AN ATTRIBUTE VALUE IS
WITHIN RANGE
Example
SELECT * FROM INSTRUCTOR
WHERE SALARY BETWEEN 60000 AND
80000;
MATCHING STRING PATTERN
Two types of matching
• Full string matching: It uses '%'
Ex: 'P%' matches 'Peter', 'Pam', 'Pratima'
'%i%' matches 'Srinivasan', 'Einstein', 'El Said'
• One letter matching: It uses „_‟
Ex: '_ohit' matches 'Mohit', 'Rohit', 'Sohit'
QUERY: Display details about Instructors with names starting
from K.
SELECT * FROM INSTRUCTOR WHERE NAME LIKE
'K%';
IN The SQL IN condition allows you to

easily test if an expression matches
any value in a list of values.
EXAMPLE OF IN SYNTAX
The syntax for the IN condition in SQL is:
expression IN (value1, value2, .... Value N);

EXAMPLE
i) Display the course_ids, titles and credits of course that are offered in any of the
departments namely: Physics, Music, Finance and Biology.
EXAMPLE OF IN SYNTAX
SELECT course_id, title, credits
FROM course table
WHERE dept_name IN ('Physics', ‘Music', ‘Finance');
Course_id title credits
PHY-101 Physical Principles 4
MU-199 Music Video Production 3
FIN-201 Investment Banking 3

SOME USEFUL COMMANDS
To display all created tables in SQL account
SYNTAX- SELECT * FROM TAB;
To improve and beautify table display in command line

SYNTAX- set linesize 160; ###160 is default but may change
depending on screen size and pixels.
END OF LECTURE 2

Lab Lect 2

Uploaded by

Copyright:

Available Formats

Lab Lect 2

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Lab Lect 2

Uploaded by

Copyright:

Available Formats

Introduction to SQL Using Oracle

The SELECT statement is used to select data from a database.

SELECT column1, column2, ...

SELECT column1, column2, ...

Note: The WHERE clause is not only used in SELECT statement, it

“selection” Powergizmo 29.99 Gadgets GizmoWorks

SELECT PName, Price, Manufacturer

Four forms of selection:

DROP COLUMN- ALTER TABLE TABLENAME DROP COLUMN1;

MODIFY COLUMN- ALTER TABLE TABLENAME MODIFY

RENAME COLUMN- ALTER TABLE TABLENAME RENAME COLUMN

RENAME TABLE_NAME TO NEWTABLE_NAME;

The ORDER BY keyword sorts the records in ascending order by

SELECT COLUMN1 FROM TABLENAME

• SELECT * FROM TABLENAME ORDER BY COL1

Ans: SELECT NAME FROM INSTRUCTOR WHERE

Ans: SELECT NAME FROM INSTRUCTOR WHERE

The SELECT DISTINCT statement is used to return only distinct

Inside a table, a column often contains many duplicate values; and

SELECT DISTINCT column1, column2, ...

IN The SQL IN condition allows you to

expression IN (value1, value2, .... Value N);

Course_id title credits

PHY-101 Physical Principles 4

MU-199 Music Video Production 3

FIN-201 Investment Banking 3

To improve and beautify table display in command line

You might also like