Transcript Lecture 3 - More SQL

Lecture 3
19/1/15
More on SQL…
SQL is a 4th generation language.
• Non-procedural
• SQL processes several rows together rather than
one row at a time.
When we issue an SQL command, the command is
sent to the server where all rows are dealt with at
the one time.
2
SQL is used to
• Query data
• Insert, update, and delete rows
• Create, alter, and delete database objects
• Control access to database objects and to the
database itself
• Guaranty database consistency
• Monitor database performance
3
Who uses SQL?
• User
• Developer
• Designer
• DBA
4
Commands can be divided into four
categories
• Query.
• Data Manipulation Language (DML)
commands
• Data Definition Language (DDL) commands
• Security
5
Entering SQL commands
SQL has a buffer which stores the current
command
• SQL commands can span lines
• Although not obligatory, clauses should be
put on separate lines to improve readablity.
• Tabs should be used to make a command
more readable
• SQL commands are not case sensitive
6
Comparison Operators
select *
from employee
where dept_no <=4
order by emp_name;
7
Comparison Operators
select *
from employee
where dept_no <>4;
8
Displaying Table Structure
DESC
• Displays the structure of the table including
column names and data types
9
Datatypes
• The VARCHAR2 datatype stores variable-length character strings
• The VARCHAR datatype is synonymous with the VARCHAR2 datatype.
• To avoid possible changes in behavior, always use the VARCHAR2 datatype
to store variable-length character strings.
• The NUMBER datatype stores fixed and floating-point numbers. Numbers
of virtually any magnitude can be stored and are guaranteed portable
among different systems operating Oracle Database, up to 38 digits of
precision
• The following numbers can be stored in a NUMBER column:
– Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38
significant digits
– Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant
digits
– Zero
– Positive and negative infinity
10
Datatypes –
Number continued…
• For numeric columns, you can specify the
column as:
• column_name NUMBER Optionally, you can
also specify a precision (total number of
digits) and scale (number of digits to the right
of the decimal point):
• column_name NUMBER (precision, scale)
11
Datatypes
• The DATE datatype stores point-in-time values (dates
and times) in a table.
• The DATE datatype stores the year (including the
century), the month, the day, the hours, the minutes,
and the seconds (after midnight)
• Oracle Database uses its own internal format to store
dates. Date data is stored in fixed-length fields of seven
bytes each, corresponding to century, year, month, day,
hour, minute, and second.
– For input and output of dates, the standard Oracle date
format is DD-MON-YY, as follows:
– '13-NOV-92'
12
Like %
Select name, address
from customer
where address like 'C%';
name
address
CIARA
CORK
MARY
CORK
Select name, address
from customer
where address like 'c%';
Result???
Another example:
select *
from employee
where job like '%NALYST';
3
HEARNE
ANALYST 4
800
13
Error?
Select *
from customer
Select name, address
from customer
where name =mary;
Select name address
from customer;
Select name CUS NAME
from customer;
16
SQL Questions
cust_code cust_name cust_city
C00013
C00001
C00020
C00025
C00024
C00015
C00002
C00018
C00021
C00019
working_a
rea
London
New York
New York
Bangalore
London
London
New York
Brisban
Brisban
Holmes
London
Micheal
New York
Albert
New York
Ravindran Bangalore
Cook
London
Stuart
London
Bolt
New York
Fleming
Brisban
Jacks
Brisban
Yearannaid
Chennai Chennai
u
cust_count
grade
ry
UK
USA
USA
India
UK
UK
USA
Australia
Australia
opening_a
mt
2 6000.00
2 3000.00
3 5000.00
2 5000.00
2 4000.00
1 6000.00
3 5000.00
2 7000.00
1 7000.00
receive_a
mt
5000.00
5000.00
7000.00
7000.00
9000.00
8000.00
7000.00
7000.00
7000.00
payment_
amt
7000.00
2000.00
6000.00
4000.00
7000.00
3000.00
9000.00
9000.00
7000.00
outstandin
phone_no
g_amt
4000.00 BBBBBBB
6000.00 CCCCCCC
6000.00 BBBBSBB
8000.00 AVAVAVA
6000.00 FSDDSDF
11000.00 GFSGERS
3000.00 DDNRDRH
5000.00 NHBGVFC
7000.00 WERTGDF
agent_cod
e
A003
A008
A008
A011
A006
A003
A008
A005
A005
India
1 8000.00
7000.00
7000.00
8000.00
A010
ZZZZBFV
Create a report to list:
1. All customer names in alphabetical order
2. Customer names and phone numbers of those living in London
3. Customer countries – avoid duplicates
4. Customer names and codes where their outstanding amount is less
than 5000.00
5. Customer countries with ‘U’ in the name
6. All customer details where the sum of
'opening_amt' and 'receive_amt' is greater than 15000
17