Database Languages n What is SQL n SQL

Database Languages n What is SQL? n SQL Schema Definition Language (SDL) n SQL Data Manipulation Language (DML)

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

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

Database Language Part -1 SDL – Schema Definition Language

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

Schema Definition Language n 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

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

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

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

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

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.

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) commands.

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

Database Language Part - 2 DML - Data Manipulation Language

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.

Data Manipulation Language 0. Basic structure of DML SELECT target_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.

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

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

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

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

Data Manipulation Language 3. 1 Logic Operators ? ? AND, OR, NOT X Y True False X AND Y X OR Y

Data Manipulation Language 3. 1 Logic Operators NOT (COLOUR = ‘blue’) AND (NAME=‘socks’ OR NAME=‘Pantaloons’) PRODUCT_NO NAME COLOUR P 1 Pantaloons blue P 2 Pantaloons khaki P 3 Socks white P 4 Socks harebell P 7 Socks bule P 2 Pantaloons khaki P 3 Socks white P 4 Socks harebell

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

Data Manipulation Language 3. 3 Range Checks SELECT * FROM ORDER_LINE WHERE QUANTITY BETWEEN 20 AND 1000; ORDER_LINE ORDER_NO PRODUCT_NO QUANTITY 01 P 2 20000 01 P 6 20 02 P 2 1000 02 P 6 10000 01 P 6 20 02 P 2 1000

Data Manipulation Language 3. 4 Value set tests SELECT * FROM CUSTOMER WHERE CUST_NO IN (‘C 1’, ’C 2’) Customer Cust_No Name Address C 1 Nippers Ltd 25 High St, Leeds C 2 Tots-Gear 5 Low, Oxford C 3 Super-Brat 30 New St Luton C 6 Tiny-Togs 1 Old Rd, Luton

Data Manipulation Language Summary: 1. What is the Database Language; 2. What is SQL, SDL, DML? 3. What is the facilities of SDL? 4. Structure of DML? 5. WHERE condition and Logic Operators