SQL

Structured Query Language

1
 History
• IBM Sequel language developed as part of System R project at the
IBM San Jose Research Laboratory
• Renamed Structured Query Language (SQL)
• ANSI and ISO standard SQL:
– SQL-86
– SQL-89
– SQL-92
– SQL:1999 (language name became Y2K compliant!)
– SQL:2003
• Commercial systems offer most, if not all, SQL-92 features, plus
varying feature sets from later standards and special proprietary
features.
– Not all examples here may work on your particular system.

2
 Background
• SQL – Structured Query Language
– Developed by IBM in the 1970’s, originally called
Sequel
– The standard relational-database language
– Uses relational-algebra and relational-calculus
constructs
– SQL is used to interact with a database to manage
and ret rieve data.

3
 Background
• SQL is a language that all commercial RDBMS
implementations understand.

• SQL is a non-procedural language

• We would be discussing SQL with respect to oracle syntax

• You can’t write programs like the ones you would have done
using C langauge

• You can only write questions in English like language called

queries which will fetch some data rowsfrom the database.

4
 Data types

• Integer
• Float
• Char
• Varchar2
• Number
• date

5
• Integers
• Decimal numbers--- NUMBER, INTEGER .
• Number is an oracle data type. Integer is an ANSI data type.
Integer is equivalent of NUMBER(38)
• The syntax for NUMBER is NUMBER(P,S) p is the precision
and s is the scale. P can range from 1
• to 38 and s from -84 to 127

• Floating point numbers---- FLOAT

• Fixed length character strings---- CHAR (len)
• Fixed length character data of length len bytes. This should
be used for fixed length data.

6
• Variable length character strings ---
Varchar2(len)
• Variable length character string having
maximum length len bytes. We must specify
the size
• Dates-----DATE

7
• Constants/Literals
• ANSI standard defines format for literals
• Numeric: 21, -32, $0.75,1.2E4
• String: enclosed within ‘ …’
• Date : 12-mar-03*

8
• •Oracle has a built in function called
TO_DATE. This can be used to convert dates
written in other
• formats
• •E.g TO_DATE(‘Mar 12 2003’, ‘mon dd yyyy’)
• •The built in function sysdate can be used to
obtain the current system date.

9
 The DDL, DML and DCL.
• SQL has three flavors of statements.
• DDL is Data Definition Language statements. Some examples:
• CREATE - to create objects in the database
• ALTER - alters the structure of the database
• DROP - delete objects from the database
• TRUNCATE - remove all records from a table, including all spaces
allocated for the records are
• removed
• COMMENT - add comments to the data dictionary
• GRANT - gives user's access privileges to database
• REVOKE - withdraw access privileges given with the GRANT
command

10
 DML is Data Manipulation Language
statements
• Some examples:
• SELECT - retrieve data from the a database
• INSERT - insert data into a table
• UPDATE - updates existing data within a table
• DELETE - deletes all records from a table, the
space for the records remain
• CALL - call a PL/SQL or Java subprogram
• EXPLAIN PLAN - explain access path to data
• LOCK TABLE - control concurrency

11
 DCL is Data Control Language
statements.
• Some examples:
• COMMIT - save work done
• SAVEPOINT - identify a point in a transaction
to which you can later rollback
• ROLLBACK - restore database to original since
the last COMMIT
• SET TRANSACTION - Change transaction
options like what rollback segment to use

12
 SQL - CREATE TABLE
• Syntax:
• CREATE TABLE tablename (column_name data_ type constraints, …)
• Example:
• CREATE TABLE Emp (
• EmpNo short CONSTRAINT PKey PRIMARY KEY,
• EName VarChar(15),
• Job Char(10) CONSTRAINT Unik1 UNIQUE,
• Mgr short CONSTRAINT FKey1 REFERENCES EMP (EmpNo),
• Hiredate Date,
• Sal single,
• Comm single,
• DeptNo short CONSTRAINT FKey2 REFERENCES DEPT(DeptNo));

• Used to create a table by defining its structure, the data type and name of
the various columns, the relationships with columns of other tables etc.

13
 SQL - ALTER TABLE

• Add/Drop Column
• Syntax:
• ALTER TABLE tablename (ADD/DROP
column_name)
• ALTER TABLE EMP (ADD Grade short);
• ALTER TABLE EMP (DROP Grade);
• Used to modify the structure of a table by adding
and removing columns

14
• SQL - ALTER TABLE
• Add/Drop Primary key
• ALTER TABLE EMP ADD CONSTRAINT Pkey1
PRIMARY KEY (EmpNo);
• ALTER TABLE EMP DROP CONSTRAINT Pkey1;

15
• SQL - ALTER TABLE
• Add/Drop Foreign key
• ALTER TABLE EMP ADD CONSTRAINT Fkey1
FOREIGN KEY (Mgr)
• REFERENCES EMP (EName);
• ALTER TABLE EMP DROP CONSTRAINT Fkey1;

16
• SQL - DROP TABLE
• DROP TABLE
• – Deletes table structure
• – Cannot be recovered
• – Use with caution
• DROP TABLE EMP;

17
• NULL
• Missing/unknown/inapplicable data
represented as a null value
• NULL is not a data value. It is just an indicator
that the value is unknown

18
• Data Manipulation Language Statements
The DML statements are used to:
– Insert data into the table
– Delete data from the table
– Retrieve data from the table
– Modify/ update data in the table
 INSERT Statement
• Single-row insert : A single-row INSERT
statement adds a single new row of data to
the table.
• Inserting all columns:
• SQL permits omitting of the column list from
the INSERT statement . When the column list
is omit ted, SQL automatically generates a
column list consisting of all columns of the
table, in left to right sequence.
 DELETE Statement
• The DELETE statement can delete one or more
rows from a table.
• Even if all the data is deleted from the table, the
definition of the table and its column is still stored
in the database. The table still exists. To erase the
definition of the table from the database, the
DROP TABLE statement must be used.
• The DELETE statement cannot delete column(s)
from a table. It deletes only row(s). To delete a
column from a table, the ALTER TABLE statement
must be used.
• Deleting all rows of a t able - Delete all current
customers
• 1. DELETE FROM Customer_Details;
• Deleting some rows of a t able- Delete Customer with
Cust _ID=102 from t he list of customers
• 2. DELETE FROM Customer_Details WHERE Cust_ID =
102;
• Examples of invalid DELETE Statement s
• 3. DELETE * FROM Customer_Details;
• OR
• 4. DELETE Cust_ID FROM Customer_Details;
 Difference between TRUNCATE and DELETE statement

• TRUNCATE is a DDL statement whereas DELETE is a DML statement

• TRUNCATE deletes all records from the table whereas DELETE can
be used to selectively delete records from a table using the WHERE
clause

• TRUNCATE releases the memory occupied by the records of the

table whereas DELETE does not do so

• Data removed using TRUNCATE cannot be recovered whereas data

removed using DELETE can be recovered (using ROLLBACK, a DCL
statement which is covered in chapter 5)
 UPDATE Statement
• The UPDATE statement modifies the values of
one or more columns in selected rows of a
table.
• The target table to be updated is named in the
statement . The ‘WHERE clause’ selects the
rows of the table to be modified. The ‘SET
clause’ specifies which columns are to be
updated and calculates the new values for
them.
1. Changing all rows
Until fresh instructions come in, delete Rate_of_Interest values for all
customers.
UPDATE Customer_Fixed_Deposit
SET Rate_of_Interest_in_Percent = NULL;
2. Changing some rows
For customers with a fixed deposit > 3000, increase Rate_of_Interest
to 7.3%.
UPDATE Customer_Fixed_Deposit
SET Rate_of_Interest_in_Percent = 7.3
WHERE Amount_in_Dollars > 3000;
3. Changing the value for more than one column.
Change the Email_ID and Rate_of_Interest of Customer (Cust _ID =
104)
UPDATE Customer_Fixed_Deposit
SET Cust_Email = ‘Quails_Jack@[Link]’,
Rate_of_Interest_in_Percent = 7.3
WHERE Cust_ID = 104;
 SELECT Statement
• The SELECT statement retrieves data from a
database and returns it in the form of query
results. Refer to Figure 4-14.
• The result of a SQL query is always a table of
data.
 Avoiding duplicates (DISTINCT)
• By default the SELECT statement retrieves all rows
that are filtered by the SELECT statement.

• This may however contain duplicates rows. In

order to eliminate duplicate rows from the result
set returned by the SELECT statement use the
keyword DISTINCT.

• The default keyword is ALL.

1. List all customers name
SELECT ALL Cust_Last_Name FROM Customer_Details;

This is equivalent to:

SELECT Cust_Last_Name
FROM Customer_Details;

2. This is likely to return duplicate rows. To avoid this:

SELECT DISTINCT Cust_Last_Name FROM
Customer_Details;
 Row Selection (WHERE clause)
• The WHERE clause is used to specify a search
condition that limits the number of rows
retrieved. It is a row wise operation.

• For each row, the search condition can produce

one of the three results:
· If the search condition is t rue, the row is included
in the query results
· If the search condition is false, the row is excluded
from the query results
· If the column being searched has a NULL value, the
row is excluded from the query results
1. List all customers wit h an account balance > $10000
SELECT Account_No, Total_Available_Balance_in_Dollars
FROM Customer_Transaction
WHERE Total_Available_Balance_in_Dollars > 10000.00;

2. List t he Cust _ID, Account _No of ‘Graham’ .

SELECT Cust_ID, Account_No
FROM Customer_Details
WHERE Cust_First_Name = ‘Graham’;
Note: The comparison is case sensitive. The column-names are not case-sensitive;
the values of the column(s) are case sensitive.
For Example: ‘GRAHAM’ is not the same as ‘graham’ or
‘Graham’
• The WHERE clause can be used with any of the comparison
operators (=, >, <, >=, <=, <>) or the logical operators (AND, OR,
NOT).
• When SQL compares the values of the two
expressions in the comparison test , three
results can occur:
1. The test may yield a TRUE result
2. The test may yield a FALSE result
3. If either of the two expressions produces a
NULL value, the comparison yields a NULL
result .
1. List all Account _No where
Total_Available_Balance_in_Dollars is at least $10000.00
SELECT Account_No
FROM Customer_Transaction
WHERE Total_Available_Balance_in_Dollars >= 10000.00;

2. List all Cust _ID, Cust _Last _Name where Account _t ype is
‘Savings’ and Bank_Branch is ‘Downt own’ .
SELECT Cust_ID, Cust_Last_Name
FROM Customer_Details
WHERE Account_Type = ‘Savings’
AND Bank_Branch = ‘Downtown’;
• 3. List all Cust _ID, Cust _Last _Name where neither Account _t ype
is ‘Savings’ and nor Bank_Branch is ‘Downt own’ .
SELECT Cust_ID, Cust_Last_Name
FROM Customer_Details
WHERE NOT Account_Type = ‘Savings’
AND NOT Bank_Branch = ‘Downtown’;

• 4. List all Cust _ID, Cust _Last _Name where eit her Account _t ype
is ‘Savings’ or Bank_Branch is ‘Downt own’ .
SELECT Cust_ID, Cust_Last_Name
FROM Customer_Details
WHERE Account_Type = ‘Savings’
OR Bank_Branch = ‘Downtown’;
 To create the table from an existing table and to populate a
table with the contents of an existing table.

Select command to create a table

Syntax: -
create table <new_table_name>
(column_name1,…..,column_name n )
as select column_name1,…..,column_name n
from <existing_table_name>
[where <condition>]);
•
e.g. To create a table similar to emp table but with only a
few columns
from emp table.

create table emp_temp

(id_no, name, deptno)
as select empno, ename, deptno from emp;

To see contents of emp_temp table

select * from emp_temp;

41
 Select command to insert Records
• Inserting data set into a table from another
table.
• Syntax: -
• insert into <table_name>
• select column_name1,…,column_name n
• from <existing_table_name>
• [where <condition>];
•
• e.g. To insert rows into emp_temp from emp table.
•
• insert into emp_temp
• select empno, ename, deptno
• from emp;
• Note : - There are two sets of identical rows in the table EMP_TEMP.
This is because one set of rows was created during the CREATE table
As select command and other set of rows was created by the INSERT
command.
 Select command for Column Aliases.

• The select command can be used to temporarily

change a column name, when the query result is
displayed. Column aliases come in handy, to shorten
column names or if there is a need to hide the actual
column from being displayed.

Syntax:
select column_name <alias_name> from
table_name;

e.g.
select ename “Employees” from emp;
• The COMMIT command used to save all changes
made by the transaction in the database. The
COMMIT command saves all modifications since
the last COMMIT or ROLLBACK command.
• The ROLLBACK command used to undo changes
made by a transaction. The ROLLBACK command
can only undo modifications since the last
COMMIT or ROLLBACK command that was issued.

44
 The select Clause
• The select clause list the attributes desired in the
result of a query
– corresponds to the projection operation of the
relational algebra
• Example: find the names of all instructors:
select name
from instructor
• NOTE: SQL names are case insensitive (i.e., you
may use upper- or lower-case letters.)
– E.g. Name ≡ NAME ≡ name
– Some people use upper case wherever we use bold
font.

45
 The select Clause (Cont.)
• SQL allows duplicates in relations as well as in query results.
• To force the elimination of duplicates, insert the keyword distinct after
select.
• Find the names of all departments with instructor, and remove
duplicates
select distinct dept_name
from instructor
• The keyword all specifies that duplicates not be removed.

select all dept_name

from instructor

46
 The select Clause (Cont.)
• An asterisk in the select clause denotes “all attributes”
select *
from instructor
• The select clause can contain arithmetic expressions involving the
operation, +, –, *, and /, and operating on constants or attributes of
tuples.
• The query:
select ID, name, salary/12
from instructor
would return a relation that is the same as the instructor relation,
except that the value of the attribute salary is divided by 12.

Quiz Q3: Which of these clauses is optional in an SQL query:

(1) select (2) from (3) where (4) none of these

47
 The where Clause
• The where clause specifies conditions that the result must satisfy
– Corresponds to the selection predicate of the relational algebra.
• To find all instructors in Comp. Sci. dept with salary > 80000
select name
from instructor
where dept_name = ‘Comp. Sci.' and salary > 80000
• Comparison results can be combined using the logical connectives and,
or, and not.
• Comparisons can be applied to results of arithmetic expressions.

48
 The from Clause
• The from clause lists the relations involved in the query
– Corresponds to the Cartesian product operation of the relational
algebra.
• Find the Cartesian product instructor X teaches
select *
from instructor, teaches
– generates every possible instructor – teaches pair, with all attributes
from both relations
• Cartesian product not very useful directly, but useful combined with
where-clause condition (selection operation in relational algebra)

49
Customer_Details

50
Customer_Transaction

51
 SQL Operators
Overview
• An operator manipulates individual data items and returns a
result.
• The data items are called operands or arguments.
• Operators are represented by special characters or by keywords.
• For example, the multiplication operator is represented by an
asterisk (*) and the operator that tests for nulls is represented by
the keywords IS NULL.
• There are two general classes of operators:

Unary Operators
• A unary operator uses only one operand. A unary operator
typically appears with its operand in the following format.
operator operand

Binary Operators
• A binary operator uses two operands. A binary operator appears
with its operands in the following format.
operand1 operator operand2 52
 Types of operators

Arithmetic Comparison Logical

Operators Operators Operators

+(addition) =,! =, <, >, <=, AND, NOT

-(subtraction) >=, [between, OR
*(multiplication) like,

/(division) not between,

not like] 53
 • Arithmetic operators
• To perform calculations based on number values, we include
arithmetic expressions in SQL. An arithmetic expression
consists of column names with number datatype and an
arithmetic operator connecting them.
•
Operator Description Example
+ (unary) Makes operand positive SELECT +3 FROM DUAL;
- (unary) Negates operand SELECT -4 FROM DUAL;
/ Division (numbers and dates) SELECT SAL / 10 FROM EMP;
* Multiplication SELECT SAL * 5 FROM EMP;
+ Addition (numbers and dates) SELECT SAL + 200 FROM EMP;

- Subtraction (numbers and dates) SELECT SAL - 100 FROM EMP;

54
 Examples: -
1. select salary + 2000 from emp;
2. select ename , salary + comm “Total Salary” from emp where
deptno = 20;
Note:
1. * and / have equal higher precedence.
2. + and – have equal lower precedence.

The following example illustrates the precedence of operators.

e.g. select 1.5 * (salary + comm) from emp;
In the above example, the result obtained from adding commission to
salary is multiplied by 1.5
Note: If parenthesis is omitted, then multiplication will be
performed first followed by addition.

55
 Comparison or Relational Operators
• Comparison operators are used in conditions to compare one
expression with another. Comparison operators are listed as
below:
Examples: -
Display the list of employees whose job is ‘analyst’.
select * from emp where job = ‘analyst’;

Display the list of employees whose salary is more or equal to 20000.

select * from emp where salary >= 20000 ;

Display the list of employees whose salary is less than 5000.

select ename,job,salary from emp where salary <5000;

Display the list of employees excluding job title as ‘salesman’.

select * from emp where job != ‘salesman’;

56
 Comparison or Relational Operators
Operator Description Example
= Equality test. SELECT ENAME "Employee" FROM EMP WHERE
SAL = 1500;

!=, ^=, <> Inequality test. SELECT ENAME FROM EMP WHERE SAL != 5000;

> Greater than test. SELECT ENAME "Employee", JOB "Title" FROM
EMP WHERE SAL > 3000;

< Less than test. SELECT * FROM PRICE WHERE MINPRICE < 30;

>= Greater than or equal SELECT * FROM PRICE WHERE MINPRICE >= 20;
to test.
<= Less than or equal to SELECT ENAME FROM EMP WHERE SAL <=
test. 1500;

57
Operator Description Example
IN "Equivalent to any member of" test. SELECT * FROM EMP WHERE
Equivalent to "=ANY". ENAME IN ('SMITH', 'WARD');
ANY/ SOME Compares a value to each value in a list or SELECT * FROM DEPT WHERE
returned by a query. Must be preceded by =, LOC = SOME ('NEW
!=, >, <, <= or >=. Evaluates to FASLE if the YORK','DALLAS');
query returns no rows.

NOT IN Equivalent to "!=ANY". Evaluates to FALSE if SELECT * FROM DEPT WHERE

any member of the set is NULL. LOC NOT IN ('NEW YORK',
'DALLAS');
ALL Compares a value with every value in a list or SELECT * FROM emp WHERE sal
returned by a query. Must be preceded by =, >= ALL (1400, 3000);
!=, >, <, <= or >=. Evaluates to TRUE if the
query returns no rows.

IS [NOT] Tests for nulls. This is the only operator that SELECT * FROM EMP WHERE
NULL should be used to test for nulls. COMM IS NOT NULL AND SAL >
1500; 58
Operator Description Example
[NOT] [Not] greater than or equal to x and less SELECT ENAME, JOB
BETWEEN x than or equal to y. FROM EMP WHERE SAL
and y BETWEEN 3000 AND
5000;
EXISTS TRUE if a sub-query returns at least one SELECT * FROM EMP
row. WHERE EXISTS (SELECT
ENAME FROM EMP
WHERE MGR IS NULL);
x [NOT] LIKE TRUE if x does [not] match the pattern y. SELECT * FROM EMP
y [ESCAPE z] Within y, the character "%" matches any WHERE ENAME LIKE
string of zero or more characters except null. '%E%';
The character "_" matches any single
character. Any character following ESCAPE is
interpreted literally, useful when y contains
a percent (%) or underscore (_).

59
 Logical operators
• A logical operator is used to combine the results of two conditions to
produce a single result.
• Logical operators which manipulate the results of conditions .

AND operator
• It is used to combine the result of two conditions and both the
conditions must be true for the entire condition to be true.
Examples:-
• Display the list of employees whose salary is between 5000 and
20000.
select * from emp where salary>= 5000 and salary <= 20000;

• Display the list of employees whose joining date is between

01-apr-1995 and 10-sep- 1996.
select * from emp where joindate >= ‘01-apr-1995’ and joindate <=
’10-sep-1996’;

60
 Logical operators
OR operator
• It is used to combine the result of two conditions and if one of the condition is
true, then entire condition is true.
Examples: -
1) Display the list of employees whose manager is 1003 or department is 30.
select * from emp where mgr = 1003 or deptno = 30;
2) Display the list of employees who belongs to department 10 or 30.
select * from emp where deptno = 10 or deptno=30;

NOT operator
• It is a negative of a single condition.
Examples: -
Display the list of employees whose salary is not less than 5000.
select * from emp where not salary < 5000 ;
2) Display the list of employees except job title as ‘Manager’.
select * from emp where not (job = ‘manager’);

61
 Logical operators
Operator Description Example
NOT Returns TRUE if the following SELECT * FROM EMP WHERE NOT
condition is FALSE. Returns FALSE if (job IS NULL)
it is TRUE. If it is UNKNOWN, it
SELECT * FROM EMP WHERE NOT
remains UNKNOWN. (sal BETWEEN 1000 AND 2000)

AND Returns TRUE if both component SELECT * FROM EMP WHERE

conditions are TRUE. Returns FALSE job='CLERK' AND deptno=10
if either is FALSE; otherwise returns
UNKNOWN.

OR Returns TRUE if either component SELECT * FROM emp WHERE

condition is TRUE. Returns FALSE if job='CLERK' OR deptno=10
both are FALSE. Otherwise, returns
UNKNOWN. 62
 Lab exercise:
1. Display the annual salary of all employees.
2. Display the employee details whose job is not Manager.
3. Display the employees whose salary is greater than Rs.5000
and less than or equal to Rs.10000.
4. Find the list of all employees who stay in a city Nashik or
city Nagpur.
5. List all the employees who are located in Mumbai.

63
 To use comparison operators in ORACLE for range
searching and pattern matching of table data.

• Comparison Operators :
• IN, NOT IN
• BETWEEN, NOT BETWEEN
• IS NULL , IS NOT NULL
• ANY, ALL
• LIKE, NOT LIKE

64
 IN, NOT IN
• The IN operator allows you to specify multiple values in a
WHERE clause.
• The IN operator is a shorthand for multiple OR conditions.

• IN Syntax
• SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

• or:
• SELECT column_name(s)
FROM table_name
WHERE column_nameIN (SELECT STATEMENT);

65
Custom
CustomerName ContactName Address City PostalCode Country
erID
1 AlfredsFutterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
Ana Trujillo Avda. de la
2 Emparedados y Ana Trujillo Constitución México D.F. 05021 Mexico
helados 2222
Antonio Moreno Antonio Mataderos
3 México D.F. 05023 Mexico
Taquería Moreno 2312
120 Hanover
4 Around the Horn Thomas Hardy London WA1 1DP UK
Sq.
Christina
5 Berglundssnabbköp Berguvsvägen 8 Luleå S-958 22 Sweden
Berglund

Customers

66
 IN, NOT IN
• Select all customers that are located in "Germany", "France"
and "UK“.
• SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
•
• Select all customers that are NOT located in "Germany",
"France" or "UK“.
• SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');

• Select all customers that are from the same countries as the
suppliers.
• SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);

67
 BETWEEN, NOT BETWEEN
• The BETWEEN operator selects values within a
given range. The values can be numbers, text,
or dates.
• The BETWEEN operator is inclusive: begin and
end values are included.
• BETWEEN Syntax
• SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND
value2;
68
ProductI Supplier
ProductName CategoryID Unit Price
D ID
10 boxes x 20
1 Chais 1 1 18
bags
24 - 12 oz
2 Chang 1 1 19
bottles
12 - 550 ml
3 Aniseed Syrup 1 2 10
bottles
Chef Anton's
4 1 2 48 - 6 oz jars 22
Cajun Seasoning
Chef Anton's
5 1 2 36 boxes 21.35
Gumbo Mix

69
 BETWEEN, NOT BETWEEN

• Select all products with a price BETWEEN 10

and 20.
• SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

• Display the products outside the range of the

previous example, use NOT BETWEEN:
• SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
70
 BETWEEN, NOT BETWEEN
• Select all products with a price BETWEEN 10 and 20. In
addition; do not show products with a CategoryID of
1,2, or 3:
• SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);

• Select all products with a ProductName BETWEEN

'Carnarvon Tigers' and 'Mozzarella di Giovanni':
• SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers‘
AND ‘Mozzarella di Giovanni'
ORDERBY ProductName;
71
 BETWEEN, NOT BETWEEN
• Select all products with a ProductName NOT
BETWEEN 'Carnarvon Tigers' and 'Mozzarella
di Giovanni':
• Example
• SELECT * FROM Products
WHERE ProductName NOT BETWEEN
'Carnarvon Tigers‘ AND 'Mozzarella di
Giovanni'
ORDERBY ProductName;

72
 OrderID CustomerID EmployeeID OrderDate ShipperID

10248 90 5 7/4/1996 3

10249 81 6 7/5/1996 1

10250 34 4 7/8/1996 2

10251 84 3 7/9/1996 1

10252 76 4 7/10/1996 2

Select all orders with an OrderDate BETWEEN '04-July-1996' and

'09-July-1996':
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN ‘07/04/1996’ AND ‘07/09/1996’;
73
 String Operations
• The strings are enclosed by single quotes, for example,
‘Perryridge’.
• The most commonly used operation on strings is pattern
matching using “like”. Pattern has two special characters:

* Percent(%):matches any substring

* Underscore(_): matches any character

- ‘Perry%’ matches any string beginning with “Perry”.

- ‘_ _ _ %’matches any string of at least 3 characters.

 LIKE
• The SQL LIKE clause is used to compare a value to similar values using
wildcard operators. There are two wildcards used in conjunction with the
LIKE operator:
• · The percent sign (%)
• · The underscore (_)
• The percent sign represents zero, one, or multiple characters.
• The underscore represents a single number or character.
• The symbols can be used in combinations.
• Syntax:
• The basic syntax of % and _ is as follows:
• SELECT FROM table_name
• WHERE column LIKE 'XXXX%'
• or
• SELECT FROM table_name
• WHERE column LIKE '%XXXX%‘
75
 LIKE
• Select all customers with a CustomerName starting with "a“.
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

• Select all customers with a CustomerName ending with "a“.

SELECT * FROM Customers
WHERE CustomerName LIKE '%a';

• Select all customers with a CustomerName that have "or" in any

position.
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';

• Select all customers with a CustomerName that have "r" in the

second position.
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';
76
 LIKE
• Select all customers with a CustomerName that starts with "a" and
are at least 3 characters in length.
• SELECT * FROM Customers
WHERE CustomerName LIKE ‘a_%_%';
• Select all customers with a ContactName that starts with "a" and
ends with "o“.
• SELECT * FROM Customers
WHERE ContactName LIKE 'a%o';
• Select all customers with a CustomerName that NOT starts with "a“.
• SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'a%';
• Using the % Wildcard
• Select all customers with a City starting with "ber“.
• SELECT * FROM Customers
WHERE City LIKE 'ber%';
• Select all customers with a City containing the pattern "es“.
• SELECT * FROM Customers
WHERE City LIKE '%es%';
77
 LIKE
• Using the _ Wildcard
• Select all customers with a City starting with any character,
followed by "erlin“.
• SELECT * FROM Customers
WHERE City LIKE '_erlin';
• Select all customers with a City starting with "L", followed
by any character, followed by "n", followed by any
character, followed by "on".
• SELECT * FROM Customers
WHERE City LIKE 'L_n_on';
• Using the [charlist] Wildcard
• Select all customers with a City starting with "b", "s", or "p".
• SELECT * FROM Customers
WHERE City LIKE'[bsp]%';

78
 String Operations
• “Find the names of all customers whose street
address includes the substring ‘Main’.”

select customer-name
from customer
where customer-street like ‘%Main%’
 Schema:
Customer(id, name, age, address, salary, state)

• Display details of customers where customer name

contains letter 'o' twice.
• Select * from customer
Where name like '%o%o%';

• DISPLAY STATE,[Link] CUSTOMERS IN THE STATE WHERE

THE CUSTOMER NAME CONTAINS THE WORD ‘NIKE’.
SELECT STATE, COUNT(*)
FROM CUSTOMERS
WHERE NAME LIKE '%NIKE%'
GROUP BY STATE;

80
 Order by
• The rows of the query results are not arranged in any
particular order.

• SQL can sort the results of a query by including the ORDER

BY clause in the SELECT statement The ORDER BY is a
row-wise operation.

• By default the ORDER BY clause arranges the rows of the

query resultin ascending order.

• To arrange the rows of the query resultin descending order,

use the keyword DESC.

81
1. List the customers account numbers and their account
balances, in the increasing order of the balance.
SELECT Account_No, Total_Available_Balance_in_Dollars
FROM Customer_Transaction
ORDER BY Total_Available_Balance_in_Dollars;

82
2. List the customers and their account numbers in the decreasing order of the account
numbers.
SELECT Cust_Last_Name, Cust_First_Name, Account_No
FROM Customer_Details
ORDER BY Account_No DESC;
3. List the customers and their account numbers in the decreasing order of the
Customer Last Name and increasing order of account numbers.

SELECT Cust_Last_Name, Cust_First_Name, Account_No

FROM Customer_Details
ORDER BY Cust_Last_Name DESC, Account_No;
83
 Set Operations
• The set operations UNION, INTERSECT , and
except (MINUS)operate on relations and
correspond to the relational algebra
operations ∪, ∩, −.

• union all, INTERSECT all and except all.

84
 UNION
• The UNION operation combines the rows from two sets of
query results.

• By default, the UNION operation eliminates duplicate rows

as part of its processing.

Example:
SELECT Cust_ID
FROM Customer_Fixed_Deposit
UNION
SELECT Cust_ID
FROM Customer_Loan;

85
 UNION
• To retain duplicate rows in a UNION operation,
specify the ALL keyword immediately following
the word UNION.

• Example:
SELECT Cust_ID
FROM Customer_Fixed_Deposit
UNION ALL
SELECT Cust_ID
FROM Customer_Loan;

86
87
88
• There are some restrictions on the table that
can be combined by a UNION operation:

• The SELECT statements combined using UNION

or UNION ALL must contain the same number of
columns.

• The data type of each column in the first table

must be the same as the data type of the
corresponding column in the second table.
• The data width and column name can differ.

89
• Neither of the two tables can be sorted with
the ORDER BY clause.
• However, the combined query results can be
sorted.
• Eliminating duplicate rows from query results
is a time consuming process, especially if the
query results contain a large number of rows.

90
 Union
• “ Find all customers having a loan, an account,
or both at the bank.”

(SELECT customer-name
from depositor)
union
(SELECT customer-name
from borrower)

91
 Names of all customers who have either an accountor an loan

Depositor Borrower Result

92
 INTERSECT
• The INTERSECT operation SELECTs the
common row from two sets of query results.
• Example:
SELECT Cust_ID
FROM Customer_Fixed_Deposit
INTERSECT
SELECT Cust_ID
FROM Customer_Loan;

93
94
 INTERSECT
• “ Find all customers who have both a loan and
an account at the bank.”

(SELECT distinctcustomer-name
from depositor)
INTERSECT
(SELECT distinctcustomer-name
from borrower)

95
“ Find all customers who have both a loan and an account at the
bank.”

Depositor Borrower Result

96
 Except
• “Find all customers who have an account but
no loan at the bank.”

(SELECT distinct customer-name

from depositor)
except
(SELECT customer-name
from borrower)

97
 “Find all customers who have an account but no loan at the
bank.”

Depositor Borrower Result

98
 SetOperations
• Find courses that ran in Fall 2009 or in Spring 2010
(SELECT course_id from section where sem = ‘Fall’ and year = 2009)
union
(SELECT course_id from section where sem = ‘Spring’ and year = 2010)

● Find courses that ran in Fall 2009 and in Spring 2010

(SELECT course_id from section where sem = ‘Fall’ and year = 2009)
INTERSECT
(SELECT course_id from section where sem = ‘Spring’ and year = 2010)
● Find courses that ran in Fall 2009 but notin Spring 2010

(SELECT course_id from section where sem = ‘Fall’ and year = 2009)
except
(SELECT course_id from section where sem = ‘Spring’ and year = 2010)

99
 Aggregation Functions
• Aggregation functions take a collection of values as
input and return a single value.

* Average: avg (number):- average value

* Minimum: min :-minimum value
* Maximum: max :-maximum value
* Total: sum (number) :-sum of values
* Count: count :-number of values

100
 Aggregate Functions (Cont.)

• Find the average salary of instructors in the Computer Science

department.
– SELECT avg (salary)
from instructor
where dept_name= ’Comp. Sci.’;

• Find the total number of instructors who teach a course in the

Spring 2010 semester.
– SELECT count(distinctID)
from teaches
where semester = ’Spring’ and year = 2010

• Find the number of tuples in the course relation.

– SELECT count(*)
from course;
102
1. List the minimum accountbalance.
SELECT MIN (Total_Available_Balance_in_Dollars)
FROM Customer_Transaction;

2. List the maximum accountbalance.

SELECT MAX (Total_Available_Balance_in_Dollars)
FROM Customer_Transaction;

3. List the average accountbalance of customers.

SELECT AVG (Total_Available_Balance_in_Dollars)
FROM Customer_Transaction;

4. List the minimum and sum of all accountbalances.

SELECT MIN (Total_Available_Balance_in_Dollars),SUM (Total_Available_Balance_in_Dollars)
FROM Customer_Transaction; 103
5. List total number of accountholders in the ‘Downtown’ Branch.
SELECT COUNT(*)
FROM Customer_Details WHERE Bank_Branch = ‘Downtown’;

6. List total number of Customers.

SELECT COUNT(*)
FROM Customer_Details;

7. List number of Customers having “ Savings” Account.

SELECT COUNT(*)
FROM Customer_Details WHERE Account_Type = ‘Savings’;

8. List total number of unique Customer LastNames.

SELECT COUNT(DISTINCtCust_Last_Name)
104
FROM Customer_Details
9. List total number of Employees.
SELECT COUNT(*)
FROM Employee_Manager;

10. List total number of Employees who have been assigned a Manager.
SELECT COUNT(Manager_ID)
FROM Employee_Manager;

• Note: COUNT(Column-Name) counts the number of non-NULL

values in a column
• whereas COUNT(*) counts rows of query results and does not
105
depend on the presence or absence of NULL values in a column.
 GROUP BY
• Aggregation function can be applied to a group of
sets of tuples by using group by clause.

• Account (Account_no, branch_id, branch_name,

balance, account_type, date)

“Find the average account balance at each

branch.”

SELECT branch-name, avg(balance)

from account
group by branch-name

106
 Aggregate Functions – Group By
• Find the average salary of instructors in each department
– SELECT dept_name, avg (salary)
from instructor
group by dept_name;
– Note: departments with no instructor will not appear in result

avg_salary

Result
Instructor 107
 GROUP BY
• The GROUP BY clause is used in a SELECT
statement to collect data across multiple
records and group the results by one or more
columns.

• Sometimes it is required to get information

not about each row, but about each group.

108
109
110
111
112
 HAVING
• The HAVING clause is used along with the GROUP BY clause.

• The format of the HAVING clause is similar to the WHERE

clause, consisting of the keyword HAVING followed by a search
condition.

• The HAVING clause thus specifies a search condition for

groups.

• The WHERE clause can be used to SELECT and reject the

individual rows that participate in a query.

• The HAVING clause can be used to SELECT and reject row

groups.
• It is useful to state a condition that applies to groups rather
than to tuples.
113
114
115
 Aggregate Functions – Having Clause
• Find the branches where the average account balance is more than $1200.

SELECT branch-name, avg(balance)

from account
group by branch-name
having avg(balance) > 1200

• Find the names and average salaries of all departments whose average salary is
greater than 42000.

SELECT dept_name, avg (salary)

from instructor
group by dept_name
having avg (salary) > 42000;

Note: predicates in the having clause are applied

after the formation of groups whereas predicates in the
where clause are applied before forming groups.
116
Sub queries
• Display the name of the employee along with
their annual salary (Sal * 12). The name of the
employee earning highest annual salary
should appear first.
• select ename, 12*(sal+nvl(comm,0)) Annual
from emp order by 12*(sal+nvl(comm,0))
desc;
•
Using a Subquery to• Solve a problem,
To solve this Problemyou need
two queries: one to find what
Abel earns, and a second query to
find who earns more than that
amount.

• You can solve this problem by

combining the two queries,
placing one query inside the other
query.

• The inner query or the subquery

returns a value that is used by the
outer query or the main query.

• Using a subquery is equivalent to

performing two sequential
queries and using the result of
the first query as the search value
in the second query.
 Sub-Queries
• A sub-query is a query within a query.

• The results of the sub-query are used by the

DBMS to determine the results of the
higher-level query that contains the
sub-query.

• Usually, the sub-query appears within the

WHERE or HAVING clause of another SQL
statement.
120
121
•
Subqueries
A subquery is a SELECT statement that is embedded in a
clause of another SELECT statement.

• You can build powerful statements out of simple ones by

using subqueries.

• They can be very useful when you need to select rows from a
table with a condition that depends on the data in the table
itself.

• The subquery is often referred to as a nested SELECT,

sub-SELECT, or inner SELECT statement.

• The subquery generally executes first, and its output is used

to complete the query condition for the main or outer query.
• You can place the subquery in a number of SQL clauses, including:

- The WHERE clause

- The HAVING clause
- The FROM clause

• operator includes a comparison condition such as >, =, or IN

• Note: Comparison conditions fall into two classes: single-row operators (>,
=, >=, <, <>, <=) and multiple-row operators (IN, ANY, ALL).

• subqueries can be placed in the CREATE VIEW statement, CREATE TABLE

statement, UPDATE statement, INTO clause of an INSERT statement, and
SET clause of an UPDATE statement.
 Sub-query Syntax

• The subquery (inner query) executes once before the main

query.

• The result of the subquery is used by the main query

(outer query).
 Example :
• In the slide, the inner query determines the salary of
employee Abel.

• The outer query takes the result of the inner query and uses
this result to display all the employees who earn more than
this amount.
• Execute the subquery (inner query) on its
own first to show the value that the
subquery returns.

• Then execute the outer query using the

result returned by the inner query. Finally,
execute the entire query (containing the
subquery), and show that the result is the
same.
 Guidelines for Using Subqueries
• The ORDER BY clause in the subquery is not needed unless you are
performing Top-N analysis.

• Use single-row operators with single-row subqueries and use

multiple-row operators with multiple-row subqueries.

• Prior to release Oracle8i, subqueries could not contain an ORDER BY

clause.

• Only one ORDER BY clause can be used for a SELECT statement, and if
specified it must be the last clause in the main SELECT statement. Starting
with release Oracle8i, an ORDER BY clause can be used and is required in
the subquery to perform Top-N analysis.

• The Oracle server imposes no limit on the number of subqueries; the limit
is related to the buffer size that the query uses.
Top-N- Analysis
Types of Subqueries
• Single-row subqueries: Queries
that return only one row from the
inner SELECT statement

• Multiple-row subqueries:
Queries that return more than one
row from the inner SELECT statement

• Note: There are also multiple-column

subqueries: Queries that return more
than one column from the inner
SELECT statement.
•
Single-Row
Return only one row
Subqueries
• Use single-row comparison operators
 Single-Row Subqueries
Employees (employee_id, first_name, last_name,
salary, emp_comm, department_id, Job_id, location)
 Executing Single-Row Subqueries
• A SELECT statement can be considered as a query block.

• Employees (employee_id, first_name, last_name,

salary, emp_comm, dept_no, Job_id, location)

• Display employees whose job ID is the same as that of

employee 141 and whose salary is greater than that of
employee 143.
Executing Single-Row Subqueries
• The example consists of three query blocks:
the outer query and two inner queries.

• The inner query blocks are executed first,

producing the query results ST_CLERK and
2600, respectively.

• The outer query block is then processed and

uses the values returned by the inner queries
to complete its search conditions.
 Using Group Functions in a Subquery
• Display the employee last name, job ID, and salary of all
employees whose salary is equal to the minimum salary.

• The MIN group function returns a single value (2500) to the

outer query.
 The HAVING Clause with Subqueries
• The Oracle server executes subqueries first.

• The Oracle server returns results into the HAVING clause of the main
query.

• The SQL statement on the slide displays all the departments that have a
minimum salary greater than that of department 50.
The HAVING Clause with Subqueries
 Problems with Subqueries
• A common problem with sub-queries is no rows being
returned by the inner query.
 Problems with Subqueries
• There is no employee named Haas. So the subquery
returns no rows.

• The outer query takes the results of the subquery

(null) and uses these results in its WHERE clause. The
outer query finds no employee with a job ID equal to
null, and so returns no rows.

• If a job existed with a value of null, the row is not

returned because comparison of two null values
yields a null, therefore the WHERE condition is not
true.
 Multiple-Row Subqueries
• Subqueries that return more than one row are called
multiple-row subqueries.

• You use a multiple-row operator, instead of a single-row

operator, with a multiple-row subquery.

• The multiple-row operator expects one or more values.

 ANY Operator in Multiple-Row Subqueries

• The ANY operator compares a value to each value returned by

a subquery.

• The slide example displays employees who are not IT

programmers and whose salary is less than that of any IT
programmer. The maximum salary that a programmer earns is
$9,000.

• <ANY means less than the maximum.

• >ANY means greater than the minimum.
• =ANY is equivalent to IN.
 ALL Operator in Multiple-Row
Subqueries
• <ALL means less than the minimum.
• >ALL means greater than the maximum.

• When using SOME or ANY, you often use the

DISTINCT keyword to prevent rows from being
selected several times.
 Example :

The NOT operator can be used with IN, ANY, and ALL operators.
 Null Values in a Subquery
• One of the values returned by the inner query is a null value,
and hence the entire query returns no rows.

• The reason is that all conditions that compare a null value

result in a null.

• So whenever null values are likely to be part of the results set

of a subquery, do not use the NOT IN operator.

• The NOT IN operator is equivalent to <> ALL.

Subqueries have the following characteristics:

• Can pass one row of data to a main statement that contains a

single-row operator, such as =, <>, >, >=, <, or <=

• Can pass multiple rows of data to a main statement that

contains a multiple-row operator, such as IN

• Are processed first by the Oracle server, and the WHERE or

HAVING clause uses the results

• Can contain group functions

 Emp table
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7839 KING PRESIDENT - 11/17/1981 5000 - 10

7698 BLAKE MANAGER 7839 05/01/1981 2850 - 30

7782 CLARK MANAGER 7839 06/09/1981 2450 - 10

7566 JONES MANAGER 7839 04/02/1981 2975 - 20

7788 SCOTT ANALYST 7566 04/19/1987 3000 - 20

7902 FORD ANALYST 7566 12/03/1981 3000 - 20

7369 SMITH CLERK 7902 12/17/1980 800 - 20

7499 ALLEN SALESMAN 7698 02/20/1981 1600 300 30

7521 WARD SALESMAN 7698 02/22/1981 1250 500 30

7654 MARTIN SALESMAN 7698 09/28/1981 1250 1400 30

7844 TURNER SALESMAN 7698 09/08/1981 1500 0 30

7876 ADAMS CLERK 7788 05/23/1987 1100 - 20

7900 JAMES CLERK 7698 12/03/1981 950 - 30

7934 MILLER CLERK 7782 01/23/1982 1300 - 10

 Dept table
DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON
• Find all the employees whose salary is above average salary.
• Select * from emp where sal > (select avg(sal) from emp);
• Find the name and empno of that employee whose salary is
maximum.
• Select * from emp where sal = (select max(sal) from emp);
• To see second maximum salary
• Select max(sal) from emp where
sal < (select max(sal) from emp);
• Similarly to see the Third highest salary.
• Select max(sal) from emp where
sal < (select max(sal) from emp where
sal < (select max(sal) from emp));
• We want to see how many employees are there whose salary
is above average.
• Select count(*) from emp where
sal > (select AVG(sal) from emp);
• Display those employees who are working in Hyderabad.
• Remember emp and dept are joined on deptno and city
column is in the dept table. Assuming that wherever the
department is located the employee is working in that city.
• Select * from emp where deptno
in (select deptno from dept where city=’HYD’);
• Find the sum of salary deptwise.
Select sum(sal) from emp group by deptno;

• Display employee and their salaries with null salary replaced by ‘zero’.
Select ename, nvl(sal,’zero’ from emp;

• Find all employees whose salary is greater than at least one employee at
department 30.
Select * from emp where sal>any(select sal from emp where deptno=30)

• Display employee details who work in department 10 or 20)

Select * from emp where deptno=10
Union
Select * from emp where deptno=20

• Display location of Smith.

SELECT loc from dept where deptno in (select deptno from emp where
ename=’Smith’);
 GROUP BY QUERIES
• You can group query results on some column values.
• When you give a SELECT statement without group by clause then all the
resultant rows are treated as a single group.
• For Example, we want to see the sum salary of all employees dept wise.
• Then the following query will achieved the result
• Select deptno, sum(sal) from emp group by deptno;
• Similarly we want to see the average salary dept wise
• Select deptno, avg(sal) from emp group by deptno;
• Similarly we want to see the maximum salary in each department.
• Select deptno, max(sal) from emp group by deptno;
• Similarly the minimum salary.
• Select deptno, min(sal) from emp group by deptno;
• Now we want to see the number of employees working in each
department.
• Select deptno, count(*) from emp group by deptno;
• Now we want to see total salary department wise where the
dept wise total salary is above 5000.
• For this you have to use HAVING clause. Remember HAVING
clause is used to filter groups and WHERE clause is used to
filter rows. You cannot use WHERE clause to filter groups.
• select deptno,sum(sal) from emp group by deptno
having sum(sal) >= 5000;

• We want to see those departments and the number of

employees working in them where the number of employees
is more than 2.
• Select deptno, count(*) from emp
group by deptno
having count(*) >=2;
• List the details of the emps whose Salaries more than the
employee BLAKE.
• select * from emp where sal >
(select sal from emp where ename = ‘BLAKE’);

• List the emps whose Jobs are same as ALLEN.

select * from emp where job =
(select job from emp where ename = ‘ALLEN’);

• List the emps who are senior to King.

select * from emp where hiredate <
( select hiredate from emp
where ename = ‘KING’);
• List the Emps whose Sal is same as FORD or SMITH in desc order
of Sal.

Select * from emp where sal in

(select sal from emp where ( ename = ‘SMITH’ or ename =
‘FORD’ ))
order by sal desc;

• List the emps Whose Jobs are same as MILLER or Sal is more
than ALLEN.
• select * from emp where job = (select job from emp where
ename = ‘MILLER’ ) OR sal>(select sal from emp where ename =
‘ALLEN’);
• List the emps whose jobs same as SMITH or ALLEN.
• select * from emp where job in (select job from emp where
ename = ‘SMITH’ or ename = ‘ALLEN’);
• (OR)
• select * from emp where job in (select job from emp where
ename in (‘SMITH’,’ALLEN’);
• List the employees who are senior to most recently hired
employee working under king.
• select * from emp where hiredate <
(select max(hiredate) from emp
where mgr in (select empno from emp where ename = 'KING')) ;
• Find the total sal given to the MGR.
select sum (sal) from emp
where job = ‘MANAGER’;

(OR)

select sum(sal) from emp

where empno in
(select mgr from emp);
• Display the average salaries of all the clerks.
select avg(sal) from emp
where job = ‘CLERK’;
• List the department, details where at least two
employees are working
select deptno ,count(*) from emp
group by deptno
having count(*) >= 2;
• Display the emps whose manager name is jones.
select * from emp where mgr in
(select empno from emp
where ename = ‘JONES’);
(OR)
select * from emp where mgr =
(select empno from emp
where ename = ‘JONES’);
• List the emps who are not working in sales
dept.
select * from emp
where deptno not in
(select deptno from emp
where dname = ‘SALES’);
• Print the details of all the emps who are
sub-ordinates to Blake.
select * from emp where mgr in
(select empno from emp
where ename = 'BLAKE');
• List the Name, Job and Salary of the emps who are not
belonging to the department 10 but who have the same job
and Salary as the emps of dept 10.

select ename,job,sal from emp

where deptno != 10 and job in
(select job from emp where deptno = 10)and sal in
(select sal from emp where deptno = 10);
 Null Values
SQL allows the use of null values to indicate absence of information about the value of
an attribute.
We use the special keyword null in a predicate to test for a null value.
Example:①
Find all loan numbers that appear in the loan relation with null valuefor amount
select loan-number from loan where amount is null
select sum(amount) from loan