Database Languages

1. Database Languages Part – II Data Manipulation Language

2. C3 Super-Brat 30 New St Luton Cust_No Name Address C1 Nippers Ltd 25 High St, Leeds C2 Tots-Gear 5 Low, Oxford C3 Super-Brat 30 New St Luton C6 Tiny-Togs 1 Old Rd, Luton Customer Data Manipulation Language 3.5 String matching • Fuzzy matching: NAME LIKE ‘S%’ • Select the tuples with name starting with S.

3. Data Manipulation Language 3.5 String matching % represents an arbitrary string. - represents a single arbitrary character. [] square brackets may contain any number of letters, each of which is tested individually. B%; B_OWN; B[RLO]OWN; [N_P]%

4. Data Manipulation Language 3.6 Testing for null values SELECT * FROM PRODUCT WHERE COLOUR IS NULL SELECT * FROM PRODUCT WHERE COLOUR = ‘ ’ They are not same!

5. Data Manipulation Language 3.7 Computation abs absolute atan arctangent cos cosine exp exponential log natural logarithm mod module sin sine sqrt square root + add - subtract * multiple / divide ** to the power of

6. Data Manipulation Language 3.8 Summarising functions AVG average MAX largest value of an attribute MIN minimum value of an attribute SUM total of the values of an attributes COUNT count the number of value in an attribute

7. 3 200 WAREHOUSE_NO BIN_NO PRODUCT_NO QTY WH1 1 P1 300 WH1 2 P2 50 WH2 1 P2 250 STOCK Data Manipulation Language 3.8 Summarising functions Example: SELECT AVG(QTY) FROM STOCK SELECT COUNT (*) FROM STOCK

8. WAREHOUSE_NO PRODUCT_NO BIN_NO IN_STOCK PRODUCT_NO QTY P1 WH1 300 1 P1 300 P2 WH1 2 300 P2 50 WH2 1 P2 250 STOCK Data Manipulation Language 3.9 GROUP BY: used to specific groupings of tuples. SELECT PRODUCT_NO, IN_STOCK = SUM(QTY) FROM STOCK GROUP BY PRODUCT_NO

9. PRODUCT_NO PRODUCT_NO NAME NAME COLOUR COLOUR P1 P1 Pantaloons Pantaloons blue blue P7 P2 Socks Pantaloons bule khaki P4 P3 Socks Socks white harebell P2 P4 Pantaloons Socks khaki harebell PRODUCT P7 P3 PRODUCT Socks Socks bule white Data Manipulation Language 3.9 ORDER BY: on groupings of tuples. SELECT * FROM PRODUCT ORDER BY COLOUR

10. Data Manipulation Language 3.10 Retrieval from two relations The join operator of relational algebra makes it possible to define a relation which is made up of data taken from more than one relation.

11. Data Manipulation Language 3.10 Equi-join Combines tuples in two relations when specified attributes have the same value SELECT PRODUCT.*, ORDER_LINE.* FROM PRODUCT, ORDER_LINE WHERE PRODUCT.PRODUCT_NO=ORDER_LINE.PRODUCT_NO;

12. PRODUCT. PRODUCT_NO NAME COLOUR ORDER_NO ORDER_LINE. PRODUCT_NO QUANTITY ORDER_NO PRODUCT_NO NAME PRODUCT_NO COLOUR QUANTITY P1 Pantaloons blue 01 P1 100 01 P1 P1 Pantaloons 100 blue P2 Pantaloons khaki 01 P2 20000 P2 01 P2 Pantaloons khaki 20000 P2 Pantaloons khaki 01 P2 1000 01 P3 Socks P6 white 20 02 P4 P2 Socks 1000 harebell ORDER_LINE PRODUCT 02 P7 P6 Socks 10000 bule Data Manipulation Language

13. Data Manipulation Language 3.11 Correlation Names Sometimes, you may want to use a shorter name SELECT P.*, O.* FROM PRODUCT P, ORDER_LINE O WHERE P.PRODUCT_NO = O.PRODUCT_NO;

14. PRODUCT. PRODUCT_NO NAME COLOUR ORDER_NO QTY P1 Pantaloons blue 01 100 P2 Pantaloons khaki 01 20000 P2 Pantaloons khaki 01 1000 Data Manipulation Language 3.12 Natural join Combines tuples of relations! SELECT P.*, O.ORDER_NO, QTY FROM PRODUCT P, ORDER_LINE O WHERE P.PRODUCT_NO = O.PRODUCT_NO;

15. Data Manipulation Language 3.13 Theta-join SELECT S.*, O.* FROM STOCK_TOTAL S, ORDER_LINE O WHERE S.QUANTITY > O.QUANTITY; 3.14 Outer-join: retain all the tuples (pp.105) SELECT S.*, O.* FROM STOCK_TOTAL S, ORDER_LINE O WHERE S.PRODUCT_NO = O.PRODUCT_NO (+);

16. ORDER_NO PRODUCT_NO NAME PRODUCT_NO QUANTITY COLOUR 01 P1 P1 Pantaloons 100 blue P2 01 Pantaloons P2 khaki 20000 P3 01 P6 Socks 20 white P4 02 Socks P2 harebell 1000 ORDER_LINE PRODUCT P7 02 P6 Socks 10000 bule Data Manipulation Language 3.15 Example SELECT O.* FROM PRODUCT P, ORDER_LINE O WHERE P.PRODUCT_NO = O.PRODUCT_NO AND P.NAME = ‘Pantaloons’;

17. F PRODUCT_NO S PRODUCT_NO S NAME P2 P1 Pantaloons P4 P3 Socks P7 P3 Socks P9 P3 Socks P7 P4 Socks P9 P4 Socks P9 P7 Socks Data Manipulation Language 3.16 Comparing Rows SELECT F.PRODUCT_NO S.PRODUCT_NO, NAME FROM PRODUCT F, PRODUCT S WHERE F.NAME = S.NAME AND F.PRODUCT_NO > S.PRODUCT_NO;

18. Data Manipulation Language 3.17 Queries on more than two relations SELECT P.NAME, COLOUR FROM CUSTOMER C, SALES_ORDER S, ORDER_LINE O, PRODUCT P WHERE CUSTOMER.NAME = ‘Nippers Ltd’ AND C.CUSTOMER_NO = S.CUSTOMER_NO AND S.ORDER_NO = O.ORDER_NO AND S.PRODUCT_NO= P.PRODUCT_NO;

19. Data Manipulation Language 3.18 Nested SELECTs A SELECT statement is included within the WHERE clause. SELECT P.NAME, COLOUR FROM PRODUCT P, ORDER_LINE O WHERE P.PRODUCT_NO=O.PRODUCT_NO AND O.ORDER_NO=’01’ SELECT NAME, COLOUR FROM PRODUCT WHERE PRODUCT_NO IN (SELECT PRODUCT_NO FROM ORDER_LINE WHERE ORDER_NO = ’01’);

20. Data Manipulation Language 3.18.1 Qualified comparison SELECT target_list_1 FROM List_of_relations_1 WHERE attribute IN (SELECT target_list_2 FROM list_of_relations_2 WHERE condition); The inner SELECT retrieves a set of values, and the outer SELECT retrieves tuples where (using IN) specific attribute values are member of that set.

21. Data Manipulation Language 3.18.1 Qualified comparison SELECT * FROM Employee WHERE DEPT_NO <> ‘D1’ AND AGE < ANY (SELECT AGE FROM Employee WHERE DEPT_NO=‘D1’); SELECT * FROM Employee WHERE AGE > ALL (SELECT AGE FROM Employee WHERE DEPT_NO=‘D1’); Understand the ALL and ANY!

22. Data Manipulation Language 3.18.2 Nested SELECT on primary key =, <, >, !=, <=, >= Could be applied for primary key. SELECT * FROM Employee WHERE NAME = (SELECT NAME FROM PRODUCT WHERE PRODUCT_NO=‘P1’); Primary key

23. Data Manipulation Language 3.19 Changing values in the database UPDATE PRODUCT SET COLOUR = ‘violet’, PRICE = PRICE+0.2 WHERE COLOUR =‘blue’; UPDATE DELETE INSERT INSERT INTO PRODUCT (PRODUCT_NO, NAME, PRICE) VALUES (‘P10’, ‘Shirt’, 4.95); DELETE FROM PRODUCT WHERE COLOUR = ‘blue’;

24. Data Manipulation Language 3.20 Views of database A virtual relation is a relation that appears to the user to exist, and can be manipulated as if it were a base relation, but which does not actually exist. It is a relation retrieved from other relations in the database. A virtual relation is called a View, and SQL include CREATE VIEW and DROP VIEW to create and delete it.

25. Data Manipulation Language 3.20 Views of database CREATE VIEW WH1_STOCK_A AS SELECT BIN_NO, PRODUCT_NO, QTY FROM STOCK WHERE WAREHOUSE_NO = ‘WH1’; STOCK WH1_STOCK_A

26. Data Manipulation Language 3.20 Views of database CREATE VIEW WH1_STOCK_C AS SELECT WAREHOUSE_NO, BIN_NO, PRODUCT_NO, NAME, QTY FROM STOCK PRODUCT WHERE STOCK.PRODUCT_NO=PRODUCT.PRODUCT_NO AND WAREHOUSE_NO = ‘WH1’;

27. Data Manipulation Language 3.21 Integrity constraints Integrity constraints are rules that must be obeyed by all tuples in the database. CREATE INTEGRITY ON ORDER_LINE IS QUANTITY > 0 AND QUALTITY <= 10000; Value out of range (0,10000) will be not allowed.

28. Database Language 3.22 SUMMARY • SQL has two parts: • SDL: schema definition language • DML: data manipulation language Reading: Chapter 3 Exercises 3.7-3.13