Download - Week09 - SQL 1
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 1/12
1
Database I
SQL
SELECT Statement
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 2/12
2
SELECT Statement (1/3)
SELECT is the SQL command for querying
(retrieving) data from a database table, view,
or other object etc. Basic syntax of the SELECT statement in a
textual form (Backus-Naur Form)
SELECT { [alias.]column | expression | [alias.]*[ , « ] }
FROM [schema.]table [alias];
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 3/12
3
Creating Table (1/3) Before we start executing the SQL
statements, we need some data and tables
and there by database
Click the Go to Database Home Page
Enter the password for sys user to log in
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 4/12
4
Creating Table (1/3) Before we start executing the SQL
statements, we need some data and tables
and there by database
Click the Go to Database Home Page
Enter the password for sys user to log in
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 5/12
5
Creating Table (2/3) Create Student table
by selecting create
menu from object
browser
Define the columns as shown in figure and
click next
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 6/12
6
Creating Table (1/3) Before we start executing the SQL
statements, we need some data and tables
and there by database
Click the Go to Database Home Page
Enter the password for sys user to log in
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 7/12
7
Creating Table (2/3) Create Student table
by selecting create
menu from object
browser
Define the columns as shown in figure and
click next
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 8/12
8
Creating Table (3/3) Click next two times and then finish
Click create to confirm
To insert some sample data, select data from
top menu and then insert row
Insert data for individual columns. Click createto insert data
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 9/12
9
Different Flavors of SQL Statement
(1/6)
Simple Queries
SELECT * FROM STD;
SELECT RNo, Name, Address FROM STD;
SELECT RNo * 2 "Double RNo", Name FROM STD;
SELECT RNo * 2 DoubleRNo, Name FROM STD;
SELECT S.RNo, S.Name, S.FName FROM STD S;
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 10/12
10
Different Flavors of SQL Statement
(2/6)SELECT (S.RNo + 2) / 10 , S.Name, S.FName FROM STD S;
WHERE Clause Adds conditions that filter out rows
Use various comparison conditions and logical operators
between each filter
Comparison
Operators:
Equi (=),
anti (!=, <>),
range (<, >,
=<, >=)
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 11/12
11
Different Flavors of SQL Statement
(3/6)SELECT Name, Address FROM STD WHERE RNo = 100;
SELECT * FROM STD WHERE RNo > 102;
SELECT * FROM STD WHERE Name = 'Jamal Khan';
LIKE pattern-matches between strings % and _ characters are pattern-matching wild card
characters
% is used to representing zero or more characters in a
subset of a string
_ is used to represent one and only one character
8/8/2019 Week09 - SQL 1
http://slidepdf.com/reader/full/week09-sql-1 12/12
12
Different Flavors of SQL Statement
(4/6)SELECT * FROM STD WHERE Name LIKE '%G%';
SELECT Name, FName FROM STD WHERE
Name LIKE '_a%';
SELECT Name, FName FROM STD WHERE Address IN(
'Peshawar', 'Lahore');
SELECT RNo, Name, FName FROM STD WHERE RNo BETWEEN
100 AND 102;