STRUCTURED QUERY LANGUAGE (SQL)

1. INTRODUCTION
 A language to specify queries for a relational database (in a
structured manner)

 DECLARATIVE LANGUAGE :
  The language which Specifies what to do, but not how to do.

 Example:
 User must find male students in a class. To do this with
procedural languages like C++, Java, etc., need to write code
that how to select those records. (Using control structures).

 - SQL has evolved widely after its first inception

 - Supports lots of extra operations, which are non-standard 2
2. DISTINCTION WITH RELATIONAL
ALGEBRA
 SQL relations are multi-sets or bags of tuples and not sets

 Multi-sets
 Example: {A, A, B}
 It is distinct from {A, B} but equivalent to {A, B, A}

 Consequently, there may be two identical tuples

 The set behavior can be enforced by the keyword unique

 In a query, keyword distinct achieves the same effect

3
 Opposite is keyword all, which is default
3. THE SQL LANGUAGE HAS SEVERAL PARTS:

 • Data-definition language (DDL)

 • Data-manipulation language (DML).

 • Integrity.

 • View definition.

 • Transaction control.

 • Embedded SQL and dynamic SQL.

4
 • Authorization.
4. SELECT CLAUSE

 The select clause corresponds to the projection operation of

the relational algebra.
It is used to list the attributes desired in the result of a query.

5
5. FROM CLAUSE

 The from clause corresponds to the Cartesian-product

operation of the relational algebra. It lists the relations to
be scanned in the evaluation of the expression.

6
6. WHERE CLAUSE

The where clause corresponds to the selection predicate

of the relational algebra.

7
 A TYPICAL SQL QUERY HAS THE FORM

select A1, A2, . . .,An

from r1, r2, . . . , rm
where P
- Each ri is a relation
- Each Aj is an attribute from one of r1…. rm
- P is a predicate involving attributes and constants where can be left out,
which then means true

Result is a relation with the schema (A1… An)

Is equivalent to the relational algebra query

8
ΠA1, A2,...,An(σP (r1 × r2 × · · · × rm))
 PROBLEMS
1. Consider the CUSTOMERS table having the following
records:

9
 QUERIES

 1. Fetch all the fields of CUSTOMERS table

 2. Fetch ID, Name and Salary fields of the customers available in

CUSTOMERS table

 3. Fetch ID, Name and Salary fields from the CUSTOMERS table where
salary is greater than 2000

 4. Fetch ID, Name and Salary fields from the CUSTOMERS table for a
customer with name ramesh.

10
 SQL> CREATE TABLE CUSTOMERS( ID INT NOT
NULL, NAME VARCHAR (20) NOT NULL, AGE INT
NOT NULL, ADDRESS CHAR (25) , SALARY
DECIMAL (18, 2), PRIMARY KEY (ID) );

11
 1. SELECT * FROM customers;
 2. SELECT ID, NAME, SALARY FROM CUSTOMERS;
 3. SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000;
 4. SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE NAME = ‘ramesh';

12
BANKING ENTERPRISE - EXAMPLE

13
RELATIONS

14
15
 DISTINCT KEYWORD

SQL (like most other commercial query languages) allows

duplicates in relations as well as in the results of SQL
expressions.

Fetch all the branches from the relation, see that you must
branch name only once

select distinct branch-name

from loan
16
DISTINCT – SINGLE COLUMN

17
DISTINCT – TWO COLUMN

18
DISTINCT THREE COLUMNS

19
DISTINCT ALL COLUMNS

20
 ALL KEYWORD

 Find the names of all branches in the loan relation.

select all branch-name

from loan

 This will retain all the duplicates in the resulting relation

21
 CLASS WORK
 Display salaries of all customers without duplications.

22
 SOLUTIONS
 SELECT Distinct SALARY FROM CUSTOMERS;

23
 ARITHMETIC EXPRESSIONS

 Select clause may also contain arithmetic expressions

involving the operators +, −, ∗, and /

Select loan number, branch name and multiply amount

with 100

select
loan-number,
branch-name,
amount * 100 24
from loan
 CLASS WORK
 1. Select all from customer where prebal + curbal is greater
than 5000.

2. Select all from customer where outstanding_amt' - 'payment
_ amt' is equal to the  'receive _ amt‘

 3. Select all teachers with added dewali gift amount of Rs.

1000 with salary

 4. Show employee salary with a deduction of rs 500.

25
 SOLUTIONS
1. SELECT  *
FROM customer  
WHERE (opening_amt + receive_amt)>15000;  

2. SELECT *
FROM customer   
WHERE(outstanding_amt-payment_amt)=receive_amt;  

3. Select name, salary*1000 from employee.

4. Select name, salary – 500 from employee

26
 OPERATORS

 Find all loan numbers for loans made at the Perryridge

branch with loan amounts greater that $1200.

 - SQL uses the logical connectives and, or, and not

 - It uses comparison operators <, <=, >, >=, =, and <>.

select loan-number
from loan
where branch-name = ’Perryridge’ and amount > 1200
27
 CLASS WORK
 Fetch ID, Name and Salary fields from the
CUSTOMERS table where salary is greater than 2000
AND age is less tan 25 years

 Fetch ID, Name and Salary fields from the

CUSTOMERS table where salary is greater than 2000
OR age is less tan 25 years

28
 SOLUTIONS
 SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE SALARY > 2000 AND age < 25;

 SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS

WHERE SALARY > 2000 OR age < 25;

29
 BETWEEN CLAUSE

 Find the loan number of those loans with loan amounts between $90,000 and $100,000

select loan-number
from loan 30
where amount between 90000 and 100000
PROBLEM

31
 MULTIPLE RELATIONS

For all customers who have a loan from the bank, find their
names, loan numbers and loan amount.

select customer-name,
borrower.loan-number, amount

from borrower, loan

where borrower.loan-number = loan.loan-number

32
 MULTIPLE RELATIONS WITH
MULTIPLE CONDITIONS
Find the customer names, loan numbers, and loan amounts for all
loans at the Perryridge branch

select customer-name,
borrower.loan-number, amount

from borrower, loan

where borrower.loan-number = loan.loan-number

and
33
branch-name = ’Perryridge’
 CLASS WORK
 1. Find the agents where their commission not less than
1000 and not more than 2000

 2. Find the agents where their commission not less than

1000 and not more than 2000

 3. Find details from order table where  'ord_date' must be

a date before '15-Feb-08' or after '30-Jul-08'

34
 SOLUTIONS

1.

SELECT * FROM agents   
WHERE commission BETWEEN 1000 AND 2000;    

2.
SELECT * FROM agents   
WHERE commission NOT BETWEEN 1000 AND 2000; 

3. SELECT *
FROM orders  
WHERE ord_date NOT BETWEEN '15-Feb-08' AND '30-Jul-08';  
    35
UNIVERSITY SCHEMA – EXAMPLE 2

36
 FIND QUESTIONS FOR THE FOLLOWING

1.

SELECT * FROM Faculty;

2.

SELECT *
FROM Faculty
WHERE FacNo = '543-21-0987';
37
3.
SELECT FacFirstName, FacLastName, FacSalary
FROM Faculty
WHERE FacSalary > 65000 AND FacRank = 'PROF';
 
4.
SELECT FacCity, FacState
FROM Faculty;

5.
SELECT DISTINCT FacCity, FacState
FROM Faculty;
38
 CLASS WORK

eid F_name L_name sal doj dept

39

employee
 QUERIES

1. Get all employee details from the employee table

2. Get First_Name,Last_Name from employee table

3. Get First_Name from employee table using alias name

“Employee Name”

4. Get unique DEPARTMENT from employee table

40
 SOLUTIONS

1. Select * from employee

2. Select first_name, Last_Name from employee

3. Select first_name as Employee_Name from employee

4. Select distinct DEPARTMENT from EMPLOYEE

41