DATABASE LANGUAGES
INTRODUCTION TO SQL
INTRODUCTION
DATABASE QUERY LANGUAGES
SQL
Databases require two sorts of languages : the data definition
SQL SELECT STATEMENT
language and the data manipulation language.
ORACLE ENHANCEMENTS TO SELECT STATEMENT
CONCLUSIONS The data definition language is used to create databases, tables, to
authorize users etc
The data manipulation language is used to retrieve data to add and
delete data and to modify data.
SQL IN PERSPECTIVE
> SQL: Structured Query Language. The SQL SELECT statement consists of a core which all
implementations of SQL support:
> Sequel
> 1976 - System R - IBM Research database SELECT <column names>
> ANSI Standard 1986 FROM <table names>
WHERE <conditions>
> Supported by many vendors: GROUP BY <column names>
HAVING <conditions>
DB2, SQL/DS, Rdb, Non Stop SQL, Oracle, Ingres, ORDER BY <column name>
Informix, Sybase, Empress, dBase IV, Paradox,
...................
> Benefits of SQL
- Availability of transferable skills.
- Reduced retraining effort.
- Increased portability of applications.
- Increased possibility of data sharing.
Relational representation of the The ‘WHERE’ clause provides the same operation
Summerhill database as restrict in relational algebra
CUSTOMER
Get the customer details for customer number 12212
place
ORDER Q1 SELECT CUST_NUM, CUST_NAME, CUST_ADDRESS
has_invoice has_oline FROM CUSTOMER
WHERE CUST_NUM >= 12212;
INVOICE ORDER_LINE
CUST_NUM CUST_NAME CUST_ADDRESS
has_iline porderl 46751 QUIKPLUMB SPINKHILL
INVOICE_LINE PART 12212 ESIDRAIN KILAMARSH
pinl
Case sensitivity
Quoted strings are case sensitive The ORDER BY clause controls the sequence that is used to present the
Q2 SELECT CUST_NUM, CUST_NAME, CUST_ADDRESS results of queries.
FROM CUSTOMER
WHERE CUST_NUM >= 12212; List the customer details ordered by the customer name.
Is the same as:
Q3 select cust_num, cust_name, cust_address Q6 SELECT CUST_NUM, CUST_NAME, CUST_ADDRESS
from customer FROM CUSTOMER
where cust_num >= 12212; ORDER BY CUST_NAME;
But:
Q4 SELECT CUST_NUM, CUST_NAME, CUST_ADDRESS
FROM CUSTOMER CUST_NUM CUST_NAME CUST_ADDRESS
WHERE CUST_ADDRESS = ‘SPINKHILL’; 12212 ESIDRAIN KILAMARSH
Is different from:
SELECT CUST_NUM, CUST_NAME, CUST_ADDRESS 12211 NUGENT SH RENISHAW
Q5
FROM CUSTOMER 46751 QUIKPLUMB SPINKHILL
WHERE CUST_ADDRESS = ‘Spinkhill’;
The ‘GROUP BY’ clause allows tuples to be returned in groups, typically List each date on which payments were made and the total
with some function being applied to the group payment for each of those dates.
Q7 SELECT Cust_num, SUM( Value)
FROM order PAYMENT
Invoice Payment
GROUP BY Cust_num; No Date
Amount
33908 10-OCT-10 250.00
33911 10-OCT-10 350.00
Cust_Num Order_Num Order_Date Value 33911 2-DEC-10 250.00
12211 1190 23-APR-10 123.00
12211 1296 12-SEP-10 90.00
46751 2343 15-AUG-10 78.90 Select Payment_Date, sum(Amount)
12211 2131 12-OCT-10 2303.00
From Payment
46751 2132 15-NOV-10 1151.10 Group By Payment_Date
Q8
Cust_num SUM (VALUE)
12211 2516
46751 1230
RELATING TWO TABLES BY A JOIN
CUSTOMER
The ‘HAVING’ clause specifies which groups are returned. Cust_num, Cust_name,Cust_address
ORDER
Q9 SELECT Cust_num, SUM( Value) Order_num, Cust_num, Order_date, Value
FROM order
GROUP BY Cust_num; SELECT Customer.Cust_name, Order_num, Order_date, Value
HAVING SUM (Value) < 2000; Q10
FROM customer, order
WHERE customer.Cust_num = order.Cust_num
Cust_num SUM (VALUE)
46751 1230 CUST_NAME ORDER_NUM ORDER_DATE VALUE
NUGENT SH 1190 24-APR-10 123
NUGENT SH 1296 13-SEP-10 90
NUGENT SH 2343 15-AUG-10 78.9
QIKPLUMB 2131 12-OCT-10 2303
QIKPLUMB 2132 15-NOV-10 1151.1
Additional conditions can be placed in the ‘WHERE’ clause
to further restrict the result.
JOINS ARE SIMPLIFIED WHEN THE JOIN ATTRIBUTES
ARE NAMED DIFFERENTLY:
Q12 SELECT Customer.Cust_name, Order_num, Order_date, Value
SELECT CUST_NUM, CUST_NAME, INVOICE_NO FROM customer, order
Q11
FROM CUSTOMER, INVOICE WHERE customer.Cust_num = order.Cust_num
WHERE CUST_NUM = CNO; AND value < 600;
CUST_NAME ORDER_NUM ORDER_DATE VALUE
NUGENT SH 1190 24-APR-10 123
NUGENT SH 1296 13-SEP-10 90
QIKPLUMB 2343 15-AUG-10 78.9
Other group functions available in SQL:
PART INVOICE_ITEM AVG () COUNT()
Part Invoice Part No
No
Description Price No
Quant MAX() MIN()
223A 22mm clip 0.10 33908 223A 2500 STDEV() SUM()
1212 22mm con 1.50 33911 6341SS 100
100 VARIANCE()
6341SS 3m tube 4.50 33911 1212
Queries returning group functions are relationally closed ie. a query such as:
List the details of each invoice item together with the description and
SELECT MAX(Value)
price of the item.
FROM order;
Q14
Select Invoice_No, Part.Part_No, Description, Price, Quant
returns a relation with one tuple in it: MAX(Value)
Q13 From Part, Invoice_Item
1754
Where Part.Part_No = Invoice_item.Part_No;
CONDITIONS IN SQL:
ORACLE ENHANCEMENTS OF THE SELECT STATEMENT
1. ANY SELECT DISTINCT Cust_name
FROM customer, order 1. DISTINCT
Q15
WHERE customer.cust_num = order.cust_num 2. UNION, INTERSECT, MINUS
AND Order_date = ANY ( '13-SEP-10', '15-AUG-10')
SELECT DISTINCT Cust_name, Customer.Cust_num
2. IN SELECT DISTINCT Cust_name FROM customer, order
Q16 FROM customer, order WHERE customer.Cust_num = order.Cust_num
WHERE customer.cust_num = order.cust_num AND order_date >= ‘1-JAN-10’
AND Order_date IN ( '13-SEP-10', '15-AUG-10') INTERSECT
3. BETWEEN SELECT DISTINCT Cust_name Q19 SELECT DISTINCT Cust_name, customer.Cust_num
FROM customer, order FROM customer, order
Q17
WHERE customer.cust_num = order.cust_num WHERE customer.Cust_num = order.Cust_num
AND Order_date BETWEEN ‘24-JUL-10’ AND’30-OCT-10’; AND order_date <= ’31-DEC-10’;
4. LIKE SELECT DISTINCT Cust_name SELECT DISTINCT Cust_name
Q18 FROM customer FROM customer, order
WHERE Cust_name LIKE ‘N%’; WHERE customer.Cust_num = order.Cust_num
AND Order_date BETWEEN ‘1-JAN-10’ AND ’31-DEC-10’;
Q20
FOR UPDATE
SELECT *
FROM order
WHERE Order_date = ’12-SEP-10’ OUTER JOIN IN ORACLE:
Q21 AND Cust_num= 12211
FOR UPDATE OF Order_number;
SELECT *
ORDER
FROM customer, order
Cno
Order
Date Q22 WHERE customer.Cust_num = order.Cust_num +;
Number
12211 1190 23-4-90
12211 1296 12-9-90
46751 234321 15-8-90 Customer is the dominant table. Null order details will be added to
rows where there are no orders corresponding to a specific customer.
Joining tables together Joining tables together
Putting some of the bits together: Show the name of each person and Putting some of the bits together: Show the name of each person and
how many phone numbers they have. how many phone numbers they have.
SELECT name, COUNT(*) phones
FROM people, phonenumbers
WHERE phonenumbers.id = people.id
GROUP BY name;
SUMMARY AND CONCLUSION
SELECT STATEMENT IS THE CORE OF SQL
SELECT STATEMENT HAS THE BASIC STRUCTURE:
SELECT <ATTRIBUTES>
FROM <TABLES>
WHERE <CONDITIONS>
GROUP BY <ATTRIBUTE>
HAVING <CONDITION>
ORDER BY <ATTRIBUTE>
THERE IS SOME CONSISTENCY IN THE STRUCTURE OF THIS
STATEMENT BETWEEN THE PRODUCTS OF DIFFERENT
VENDORS
