Lecture 6 Structured Query Language SQL

1. Lecture 6Structured Query LanguageSQL Instructor: Haya Sammaneh

2. Tables • create table account (account_number varchar(15) not null, branch_name varchar(15) not null, balance number not null, primary key(account_number)); • create table branch (branch_name varchar(15) not null , branch_city varchar(15) not null, assets number not null, primary key(branch_name)); • account(account_number, branch_name, balance); • branch(branch_name, branch_city, assets);

3. Tables • create table customer (customer_name varchar(15) not null , customer_street varchar(12) not null, customer_city varchar(15) not null, primary key(customer_name)); • create table loan (loan_number varchar(15) not null , branch_name varchar(15) not null, amount number not null, primary key(loan_number)); • customer(customer_name, customer_street, customer_city ); • Loan(loan_number, branch_name, amount);

4. Tables • create table depositor (customer_name varchar(15) not null, account_number varchar(15) not null, primary key(customer_name, account_number), foreign key(account_number) references account(account_number), foreign key(customer_name) references customer(customer_name)); • create table borrower (customer_name varchar(15) not null, loan_number varchar(15) not null, primary key(customer_name, loan_number), foreign key(customer_name) references customer(customer_name), foreign key(loan_number) references loan(loan_number)); • depositor(customer_name, account_number); • borrower(customer_name, loan_number);

5. Basic Structure • A typical SQL query has the form:select A1, A2, …, Anfrom R1, R2, …, Rmwhere condition - Ai represent attributes - Ri represent relations This query is equivalent to the relational algebra expression:A1, A2, …, An(P(R1  R2  …  Rm)) • The result of an SQL query is a relation.

6. Projection • The select corresponds to the projection operation of the relational algebra. It is used to list the attributes desired in the result of a query. • Find the names of all branches in the loan relationselect branch-namefrom loanEquivalent to: branch-name(loan) • An asterisk in the select clause denotes “all attributes”select *from loan

7. Duplicate Removal • SQL allows duplicates in relations as well as in query results. • Use select distinct to force the elimination of duplicates.Find the names of all branches in the loan relation, and remove duplicatesselectdistinct branch-namefrom loan • The keyword all specifies that duplicates not be removed.selectall branch-namefrom loan force theDBMS to remove duplicates force theDBMS not to remove duplicates

8. Arithmetic Operations on Retrieved Results • The select clause can contain arithmetic expressions involving the operators,,, and , and operating on constants or attributes of tuples. • The query:select branch-name, loan-number, amount * 100 from loanwould return a relation which is the same as the loan relations, except that the attribute amount is multiplied by 100

9. The where Clause • The where clause specifies conditions that tuples in the relations in the fromclause must satisfy. • Find all loan numbers for loans made at the Nablus branch with loan amounts greater than $1200.select loan-numberfromloanwherebranch-name=“Nablus” and amount >1200 • SQL allows logical connectives and, or, and not. Arithmetic expressions can be used in the comparison operators.

10. The where Clause (Cont.) • SQL includes the between operator • Find the loan number of those loans with loan amounts between $90,000 and $100,000 (that is,  $90,000 and  $100,000) select loan-numberfrom loanwhere amount between 90000and 100000

11. The from Clause • The from clause corresponds to the Cartesian product operation of the relational algebra. • Find the Cartesian product borrower  loanselect *from borrower, loanIt is rarely used without a where clause. • Find the name and loan number of all customers having a loan at the Nablus branch.select distinct customer-name, borrower.loan-number from borrower, loanwhere borrower.loan-number = loan.loan-number and branch-name = “Nablus”

12. loan branch-name loan-number amount Nablus L-170 3000 Perryridge L-260 1700 borrower LoanBorrower branch-name loan-number amount cust-name Nablus L-230 4000 Nablus L-170 3000 Jones cust-name loan-number Nablus L-230 4000 Smith Jones L-170 Smith L-230 Hayes L-155 Table

13. The Rename Operation • Renaming relations and attributes using the as clause: old-name as new-name • Find the name and loan number of all customers having a loan at the Nablus branch; replace the column name loan-number with the name loan-id. selectdistinct customer-name, borrower.loan-number as loan-idfrom borrower, loanwhere borrower.loan-number = loan.loan-number and branch-name = “Nablus”

14. Tuple Variables/Alias • Tuple variables are defined in the from clause via the use of the “as” clause. • Find the customer names and their loan numbers for all customers having a loan at some branch.selectdistinct customer-name, T.loan-numberfrom borrower as T, loan as Swhere T.loan-number = S.loan-number

15. String Operations • Character attributes can be compared to a pattern: % matches any substring. • Find the name of all customers whose street includes the substring ‘Main’. (Eg Mainroad, Smallmain Road, AMainroad,…)select customer-namefrom customerwhere customer-street like “%Main%” • How to match the name “Main%”: (Eg abcMain%, MainMain%,…)

16. Ordering the Display of Tuples • List in alphabetic order the names of all customers having a loan at Nablus branch select distinct customer-namefrom borrower, loanwhere borrower.loan-number = loan.loan-number and branch-name = “Nablus”order by customer-name • order by customer-name desc, amount ascdesc for descending order; asc for ascending order (default)

17. Set operations • Find all customers who have a loan, an account, or both: (select customer-name from depositor)union (select customer-name from borrower) • Find all customers who have both a loan and an account. (select customer-name from depositor)intersect (select customer-name from borrower) • Find all customers who have an account but no loan. (select customer-name from depositor)except (select customer-name from borrower)

18. SQLAggregate Functions

19. Aggregate Functions • Operates on a column of a relation, and return a valueavg: average valuemin: minimum valuemax: maximum valuesum: sum of valuescount: number of values

20. balance account select balance from account where branch-name =“Nablus” Avg() 120,000 Aggregate Functions(cont.) • Find the average account balance at the Nablus branch. select avg(balance)from accountwhere branch-name=“Nablus”

21. Aggregate Functions(cont.) • Find the numbers of tuples in the customer relation.select count(*)from customer • remember * stands for all attributes • compare to: select count(customer-city)from customer • Find the number of depositors in the bankselectcount (distinct customer-name)from depositor

22. Aggregate functions - Group by • Find the number of accounts for each branch. select branch-name, count( distinct account-number)from accountgroup by branch-name • For each group of tuples with the same branch-name, applyaggregate function count and distinct to account-number account table

23. Null values • It is possible for tuples to have a null value, denoted by null, for some of their attributes; null signifies an unknown value or that a value does not exist. • The result of any arithmetic expression involving null is null. • More precisely, • Any comparison with null returns unknown (caution: Oracle treats it as false!) • (true or unknown) = true, (false or unknown) = unknown (unknown or unknown) = unknown,(true and unknown) = unknown, (false and unknown) = false (unknown and unknown) = unknown • Result of where clause predicate is treated as false if it evaluates to unknown

24. Null Values (cont.) • Find all loan numbers which appear in the loan relation with null values for amount. select loan-numberfrom loanwhere amount is null • Total of all loan amountsselect sum(amount)from loan Above statement ignores null amounts;

25. Check for each borrower if he is also a depositor Example Nested Query • Find all customers who have both an account and a loan in the bank.selectdistinctcustomer-namefromborrowerwherecustomer-namein (selectcustomer-namefromdepositor)

26. Example Query • Find all customers who have a loan at the bank but do not have an account at the bank.select distinctcustomer-namefromborrowerwhere customer-namenot in (selectcustomer-namefromdepositor)

27. Views • Provide a mechanism to hide certain data from the view of certain users. To create a view we use the command:create view view-name as <query expression>where: • <query expression> is any legal SQL query • the name of the view is represented by view-name

28. Example Queries • A view consisting of branches and their customerscreate viewall-customeras (select branch-name, customer-namefrom depositor, accountwhere depositor.account-number = account.name-number)union (select branch-name, customer-namefrom borrower, loanwhere borrower.loan-number = loan.loan-number) • Find all customers of the Nablus branchselect customer-namefromall-customerwhere branch-name = “Nablus”