‘orm of SQL Query SQL DML commands allows the user to manipulate the data in database. Different DML commands are explained in following sections. Basic Structure The basic structure of an SQL expression consists of three clauses : select, from and where. * The. select clause corresponds to projection operation of the relational algebra. It is used to list the attributes desired in the result of a query. + The from clause corresponds to the cartesian product operation of the relational algebra. It lists the relations to be scanned in the evaluation of the expression. «The where clause corresponds to the selection predicate of the relational algebra. It consists of a predicate involving attributes of the relations that appear in the from clause. A typical SQL query has the form : where pi Where, ‘A; - represents an attribute a 1, - represents relation ~~ p - isapredicate (4-1) Scanned with CamScannerDatabase Management Systems 4-2 SQL forms the cartesian product of the relations named in 4 petforms a relational algebra selection using the where clause pre edicate projects the result onto the attributes of the select clause. 4.1.1 select Clause This command is usec records from a relation. > Different forms of select clause are given below : + Form 1: Use of select clause for displaying selected fields Example : Find the names of all publishers in the book relation, SQL> Select publisher_name from book; Output : Above query displays all publisher_names, Publishers name will get displayed repeatedly, from Book relation. Th well as in the results erefore, some * Form 2: Use of select for displaying distinct values For elimination of duj ~ he from, | and d to display all fields/or set of selected fields for all/s, n Pele SQL allows duplicates in relations 3s of SQL expressions, plicates the keyword distinct it . tbo query is rewritten as, ‘word distinct is used. The above SOL> select distinct publisher_name from book; Output ; Scanned with CamScannerment Systems . onase ManED fs sat SQL. allows us to use the keyword a 7 " are not removed. ‘yword all to specify explicitly that duplicates go> select all publishor name from book; Output: —— PUBLISHER_NAME PHI Technical Nirali Technical SciTech + Form 3: Use of select for displaying all fields The asterisk symbol “*” can be used to denote “all attributes”. A select dlause of the form select * indicates that all attributes of all relations appearing in the from clause are selected. Example : SQL> select * from book; Output : iseN |. TITLE | PUB_YEAR | UNIT_PRICE | AUTHOR_NAME PUBLISHER_NAME | 1001 | Oracle 2004 399 Arora PHI 102 | DBMS 2004 400 Basu Technical 2001 Dos 2003, 250 Sinha Nirali 2002 | ADBMS 2004 450 Basu Technical 2003 Unix 2000 300 Kapoor SciTech SQL> select * from author; Output + ee AUTHOR_NAME COUNTRY Arora Us. Kapoor Canada Basu India Sinha Inia Scanned with CamScannerDatabase Management Systems 4-4 7 SQL> select * from publisher; PUBLISHER_NAME PUB_ADD Output : PHI Delhi Technical Pune MainBazar Nira Mumbai SciTech Chennai * Form 4: Select clause with arithmetic expression The select clause may also contain arithmetic expressions involving the operators +, -, *, and/operating on constants or attributes of tuples, Example SQL> select title, unit_price * 10 from book, Output : TITLE UNIT. PRICE * 10 Oracle 3990 DBS a Dos 2500 ADBMS 4500 Unix 3000 : The above query returns a relation that is the same as the book relation with attributes title as it is and unit_price, will get multiplied by 10. 4.1.2 where Clause The where clause is used to select specific rows satisfying given predicate. Example : “Find the titles of books published in year 2004”, This query can be written in SQL as : SQL> select title from book where pub, year ‘2004’; Scanned with CamScanneri gomont Systems 4-5 pst! Oracle DBms uses the logical connectives and, or and not in the where clause. The vnds of logical connectives can be expressions involving the comparison operators _ and <>. SQL allows us to use the comparison operators to compare id arithmetic expressions, as well as special types, such as date types. ger ings om > Between QL includes a between comparison operator to specify that a value be less than cregul to some value and greater than or equal to some other value. xample : “Find the titles of book having price between 300 to 400”. SQL> select Title from Book where Unit_price between 300 and 400; output : Or SQL> select Title from Book where Unit_price >= 300 and Unit_price <= 400; Output : Silay, we can use the not between comparison operator. Scanned with CamScanner4-6 Sau 4.1.3 from Clause ; The from clause defines a cartesian product of the relations in the clause, Example : “Find the titles of books with author name and country published in year 2004” SQL> select Title, Book.author_name, Country from Book, Author, where Book.author_name = Author.author_name and Pub_year = ‘2004’; . Output : TITLE AUTHOR_NAME COUNTRY ‘Oracle Arora us DBMS Basu India ADBMS Basu India Notice that SQL uses the notation relation_name.attribute_name to avoid ambiguity in cases where an attribute appears in the schema of more than one relation. 4.2 Examples of Basic SQL Queries More examples of SQL queries are given in this section. Consider following three relations : 1) Sailors(sid : integer, sname: string, rating: integer, age: real) 2) Boats(bid : integer, bname: string, color: string) 3) Reservesisid : integer, bid: integer, day: date) Query 1) Find the names of sailors who have reserved boat 103. This query can be written in SQL as follows : SELECT sname FROM Sailors S, Reserves R WHERE S.sid sid AND bid= 103; Query 2) Find the sids of sailors who have reserved a red boat. This query can be written in SQL as follows : SELECT Rsid FROM Boats B, Reserves R WHERE B.bid = R.bid AND B.color = ‘red’ i Query 3) Find the names of sailors who have reserved a red boat. This query can be written in SQL as follows : Scanned with CamScanneri LOT ‘Set Operations ' j tks The SQL operations union, intersect and except operate on relations 4, correspond to the relational-algebra operations U, 9 and -- Consider two tables : i) Depositor (Customer_name, Account_no) ii) Borrower (Customer_name, Loan_no) SQL> select * from Depositor; Output : Customer_name Account_no John 1001 Sita 1002 ‘Vishal 1003 Ram 1004 Pf SQL> select * from Borrower; Output : Customer_name Loan_no John 2001 Tonny 2003 Rohit 2004 Scanned with CamScanneraa Management Systems 4- patabas' WW saL ed union Operations union clause merges the output of two or more cries | , a ola queries into a single set of rows ‘Common records. in both queries Records only in query one Records only in query two Fig. 4.1 Output of union clause Output = Records only in query one + Ri only sing! cutee aihich is common ia beth co sigh ReCoras Only in query two + A single set of Baample : Find all customer having a loan, an account or both at the bank. SOL> select Customer_name from Borrower union select Customer_name from Depositor; Output : Customer_name John Ram Rohit Sita Tonny Vishal The union operation automatically eliminates duplicates. "Ewe want to retain all duplicates, we must write union all in place of union. SQL> select Customer_name from Borrower ‘union all Select Customer_name from Depositor; Scanned with CamScanner-12 Database Management Systems 4 CUSTOMER_NAME John SQ. Output : Tonny Rohit Vishal John Sita Vishal Ram The restrictions on using a union are as follows : * Number of columns in all the queries should be same. * The datatype of the column in each query must be same. * Union cannot be used in subqueries. © You cannot use aggregate functions in union. 4.6.2 Intersect Operation The intersect clause outputs only rows produced by both the queries intersected ie. the intersect operation returns common records from the output of both queries. Fig. 4.2 Output of intersect clause Output = A single set of records which are common in both queries. Example : Find all customers who have an account and loan at the bank. SQL> select Customer_name from Depositor intersect select Customer_name from Borrower; Scanned with CamScannerpatabase Management Systoms 4-43 Output : The intersect operation automatically eliminates duplicates. If we want to retain all duplicates we must use intersect all in place of intersect. SOL > select Customer_name from Depositor intersect all select Customer_name from Borrower ; 4.6.3 Except Operation The Except also called as Minus outputs rows that are in first table but not in second table. Output = Records only in query one. Fig. 4.3 Output of the except (Minus) clause Example : Find all customers who have an account but no loan at the bank. SQL> select Customer_name from Depositor minus select Customer_name from Borrower; Customer_name Scanned with CamScanner