Database Languages

1. Database Languages • What is SQL? • SQL Schema Definition Language (SDL) • SQL Data Manipulation Language (DML)

2. Database Language • Why Data Model and then Language? What is the relational model?? • RESTRICT? • PROJECT? Which three parts?

3. Database Language • What is SQL? SQL -- Structured Query Language. Includes two parts: SDL – Schema Definition Language DML– Data Manipulation Language

4. Database Language Part -1 SDL – Schema Definition Language

5. Schema Definition Language • What is SDL? • SDL is to declare statements for DB: • Database structures; • Integrity Constraints; • Constraints on the use of the database; • Detail of its physical implementation.

6. Schema Definition Language • The facility of SDL 1. Creating a new database Name of the New Relation CREATE SCHEMA AUTHORISATION Fair_Childs CREATEDB Fair_Childs Name of the New Relation

7. ORDER_NO PRODUCT_NO QUANTITY ORDER_LINE Schema Definition Language 2. Creating a new base relation CREATE TABLE ORDER_LINE (ORDER_NO CHARACTER (5) NOT NULL, PRODUCT_NO CHARACTER (5) NOT NULL, QUANTITY INTEGER, UNIQUE (ORDER_NO, PRODUCT_NO)); ??

8. ORDER_NO PRODUCT_NO QUANTITY ORDER_LINE Schema Definition Language 3. Removing a base relation DROP TABLE ORDER_LINE;

9. ORDER_NO PRODUCT_NO QUANTITY ORDER_LINE Schema Definition Language What is the use of an index? 4. Creating an index CREAT INDEX PROD_INDEX ON ORDER_LINE (PRODUCT_NO DESC) CLUSTER; DESC index in descending sequence. ASC index in ascending sequence (default). CLUSTER to put logically related records together.

10. Schema Definition Language 4. Creating an index CREAT UNIQUE INDEX NA_INDEX ON CUSTOMER (NAME, ADRESS); What does it mean? 5. Removing an index DROP INDEX NA_INDEX

11. Schema Definition Language 6. Adding new attributes ALTER TABLE ORDER_LINE ADD LINE_NO INTEGER; • Adding new integer attribute LINE_NO. • Its value is initialized to NULL.

12. Schema Definition Language 7. Restriction on database use GRANT SELECT, INSERT, UPDATE(QUANTITY) ON ORDER_LINE TO WAREHOUSE_MAN, DELIVERIES WITH GRANT OPTION; Two users, WAREHOUSE_MAN and DELIVERIES may access the ORDER_LINE relation. They are allowed to use SELECT, INSERT, and UPDATE (QUANTITY) (QUANTITY) commands.

13. Schema Definition Language 8. SDL summary Create a database schema Add a relation to the database schema Remove an existing relation Add attributesto an existing relation Add or remove indexes; Restrict the use of the above structures;

14. Database Language Part - 2 DML - Data Manipulation Language

15. Data Manipulation Language 0. What is DML? • DML statements: to retrieve, alter, insert or delete data values. • A DML statement: operation on relations and defines a new relation from them.

16. Data Manipulation Language 0. Basic structure of DML SELECTtarget_list FROM list_of_relations WHERE condition Which means: Retrieve a table containing the columns of target_list, taking values FROM list_of_relations, where the condition is true.

17. A B C C 1 A B 3 E 1 F D 1 1 1 2 3 2 9 3 3 9 1 3 2 3 2 X Y Data Manipulation Language 0. Basic structure of DML Example: SELECT A,B,C FROM X,Y WHERE D=E; What is the associated relational algebra?

18. A A 1 1 A A B B D D 1 1 1 2 2 3 3 1 1 3 3 2 2 SELECT A FROM X; SELECT DISTINCT A FROM X; X X Data Manipulation Language 1. Retrieval of attributes

19. A B D 1 A B 2 3 D 1 2 3 1 3 2 X Data Manipulation Language 2. Retrieval of selected tuples SELECT * FROM X WHERE D=3; ?? SELECT A,D FROM X WHERE B >= 2;

20. Data Manipulation Language 3. The WHERE Condition • Components of a WHERE condition: • Condition constructed from true/false terms. • Logic operators: AND, OR, NOT; • Brackets Example: (NAME=‘John’ ) OR (No_BOOK > 3)

21. X Y X AND Y X OR Y True True True False False True False False Data Manipulation Language 3.1 Logic Operators ?? AND, OR, NOT

22. P2 Pantaloons khaki PRODUCT_NO P3 NAME Socks white COLOUR P4 Socks harebell P1 Pantaloons blue P2 Pantaloons khaki P3 Socks white P4 Socks harebell PRODUCT P7 Socks bule Data Manipulation Language 3.1 Logic Operators NOT (COLOUR = ‘blue’) AND (NAME=‘socks’ OR NAME=‘Pantaloons’)

23. Data Manipulation Language 3.2 Arithmetic Comparisons Not equal: != Less than: < Greater than: > Less than or equal to: <= Greater than or equal to: >=

24. 01 P6 20 02 ORDER_NO PRODUCT_NO P2 1000 QUANTITY 01 P2 20000 01 P6 20 02 P2 1000 02 P6 10000 ORDER_LINE Data Manipulation Language 3.3 Range Checks SELECT * FROM ORDER_LINE WHERE QUANTITY BETWEEN 20 AND 1000;

25. C2 C1 Nippers Ltd Tots-Gear 5 Low, Oxford 25 High St, Leeds 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.4 Value set tests SELECT * FROM CUSTOMER WHERE CUST_NO IN (‘C1’,’C2’)

26. Data Manipulation Language Summary: • What is the Database Language; • What is SQL, SDL, DML? • What is the facilities of SDL? • Structure of DML? • WHERE condition and Logic Operators