lecture 5 sql continued
DESCRIPTION
Lecture 5 SQL Continued. SUBQUERIES. Queries inside query There are times when you need information from a table to answer query related to the same table or another table. Format: SQL>SELECT …. FROM WHERE colname1 condition(SELECT col2 . - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/1.jpg)
INSS 651 1
Lecture 5
SQL Continued
![Page 2: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/2.jpg)
INSS 651 2
SUBQUERIES.
Queries inside queryThere are times when you need information from a table to answer query related to the same table or another table
![Page 3: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/3.jpg)
INSS 651 3
Format:SQL>SELECT ….FROM WHERE colname1condition(SELECT col2 . . (SELECT . . ))
![Page 4: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/4.jpg)
INSS 651 4
List customers whose balance is greater than the average balance of all customers
Logic:
• To answer this query we need to know average balance of all customers
• We will have to put this in a sub query
![Page 5: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/5.jpg)
INSS 651 5
List customers whose balance are greater than the average balance of all customers
SQL> select *
from customer
where cust_balance >
(Select avg(cust_balance)
From customer);
![Page 6: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/6.jpg)
INSS 651 6
give the customer balance of customer whose order is 123Logic:
Before we can find balance of customer whose order is 123 we need to find the cust_numb first
![Page 7: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/7.jpg)
INSS 651 7
JOINING TABLES
when information needed is in more than one table, we need to join tables;
WHERE clause in the select SQL statement creates a join. Note some queries can also be answered using sub query
![Page 8: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/8.jpg)
INSS 651 8
Rules FOR joining
WHERE attribute1 condition attribute2
Ex: where employee.ssn=student.ssn
Value(s) from one table are matched value(s) from other tables all matching values are attached
allows joining of tables based on common attribute domains
without the WHERE clause it will produce a Cartesian product also
![Page 9: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/9.jpg)
INSS 651 9
Give the names of salesperson and their customers in maryland
SQL>Select cust_name, Sales_name from Customer C, salesperson Swherec.sales_numb= s. sales_numb’AndUpper(c.cust_st) =‘MD’;
C & S are aliases for tables Customer and Salesperson respectively
![Page 10: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/10.jpg)
INSS 651 10
BUILT-IN-FUNCTIONS
• AVG(value)• MAX(value)• MIN(value)• SUM(value)• STDDEV(value)• VARIANCE(value)• COUNT(value)• Etc…
![Page 11: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/11.jpg)
INSS 651 11
Nested functions
Select max (avg(grades)) etc..is allowed
![Page 12: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/12.jpg)
INSS 651 12
String functions
Several strings can be concatenated
Use string1||string 2
|| implies +
select custname|| ‘,’ || cust_street as address
From customer;
![Page 13: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/13.jpg)
INSS 651 13
Substrings
Returns substrings
Format:
Substr(stringvalue,m,n)
Where m is the starting value and n is the length of characters
![Page 14: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/14.jpg)
INSS 651 14
Assume orders have the format:”
Abc1234
cdf2345etc..
Get the first and last part of the order
Select substr (order_numb, 1,3),substr (order_numb,4,4)
From order;
![Page 15: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/15.jpg)
INSS 651 15
Product (p_code, P_price) table• P_CODE P_PRICE• -------- ----------• 11QER/31 109.99• 13-Q2/P2 14.99• 14-Q1/L3 17.49• 1546-QQ2 39.95• 1558-QW1 43.99• 2232/QTY 109.92• 2232/QWE 99.87• 2238/QPD 38.95• 23109-HB 9.95• 23114-AA 14.4• 54778-2T 4.99
• P_CODE P_PRICE• -------- ----------• 89-WRE-Q 256.99• PVC23DRT 5.87• SM-18277 6.99• SW-23116 8.45• WR3/TT3 119.95
• 16 rows selected.
![Page 16: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/16.jpg)
INSS 651 16
Select three characters of price• SQL> select substr(p_price,1,3) from product;
• SUB• ---• 109• 14.• 17.• 39.• 43.• 109• 99.• 38.• 9.9• 14.• 4.9
• SUB• ---• 256• 5.8• 6.9• 8.4• 119
• 16 rows selected.
![Page 17: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/17.jpg)
INSS 651 17
Padding charactersRpad (string, length,’set’)Lpad (string,length,’set’)
1* select rpad (p_code,15,'.') as rightpad,p_price from product• SQL> /
• RIGHTPAD P_PRICE• --------------- ----------• 11QER/31....... 109.99• 13-Q2/P2....... 14.99• 14-Q1/L3....... 17.49• 1546-QQ2....... 39.95• 1558-QW1....... 43.99• 2232/QTY....... 109.92• 2232/QWE....... 99.87• 2238/QPD....... 38.95• 23109-HB....... 9.95• 23114-AA....... 14.4• 54778-2T....... 4.99
• RIGHTPAD P_PRICE• --------------- ----------• 89-WRE-Q....... 256.99• PVC23DRT....... 5.87• SM-18277....... 6.99• SW-23116....... 8.45• WR3/TT3 ....... 119.95
• 16 rows selected.
![Page 18: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/18.jpg)
INSS 651 18
Length of string
formatlength (string)
Returns length of the string
![Page 19: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/19.jpg)
INSS 651 19
SQL> select P_code, length(P_code) from product;
• P_CODE LENGTH(P_CODE)• -------- --------------• 11QER/31 8• 13-Q2/P2 8• 14-Q1/L3 8• 1546-QQ2 8• 1558-QW1 8• 2232/QTY 8• 2232/QWE 8• 2238/QPD 8• 23109-HB 8• 23114-AA 8• 54778-2T 8
• P_CODE LENGTH(P_CODE)• -------- --------------• 89-WRE-Q 8• PVC23DRT 8• SM-18277 8• SW-23116 8• WR3/TT3 8
• 16 rows selected.
![Page 20: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/20.jpg)
INSS 651 20
Trimming data
LTrim, Rtrim..remove unwanted characters
Format:
RTRIM (string, ‘set’)
Ltrim (string, ‘set’)
Set is the collection of characters you want to trim
![Page 21: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/21.jpg)
INSS 651 21
SQL> select P_code, length(rtrim (P_code,' '))
from product;
• P_CODE LENGTH(RTRIM(P_CODE,''))• -------- ------------------------• 11QER/31 8• 13-Q2/P2 8• 14-Q1/L3 8• 1546-QQ2 8• 1558-QW1 8• 2232/QTY 8• 2232/QWE 8• 2238/QPD 8• 23109-HB 8• 23114-AA 8• 54778-2T 8
• P_CODE LENGTH(RTRIM(P_CODE,''))• -------- ------------------------• 89-WRE-Q 8• PVC23DRT 8• SM-18277 8• SW-23116 8• WR3/TT3 7
• 16 rows selected.
![Page 22: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/22.jpg)
INSS 651 22
Remove . From price• SQL> select ltrim(p_price,'.'), p_code from product;
• LTRIM(P_PRICE,'.') P_CODE• ---------------------------------------- --------• 109.99 11QER/31• 14.99 13-Q2/P2• 17.49 14-Q1/L3• 39.95 1546-QQ2• 43.99 1558-QW1• 109.92 2232/QTY• 99.87 2232/QWE• 38.95 2238/QPD• 9.95 23109-HB• 14.4 23114-AA• 4.99 54778-2T
• LTRIM(P_PRICE,'.') P_CODE• ---------------------------------------- --------• 256.99 89-WRE-Q• 5.87 PVC23DRT• 6.99 SM-18277• 8.45 SW-23116• 119.95 WR3/TT3
• 16 rows selected.
![Page 23: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/23.jpg)
INSS 651 23
INSTR function
Allows searching for a string of characters, gives the position of the string but does Not cut off anything
Format:
Instr(string, start,occurrence)Start is the start of the stringOccurrence is the position of occurrence
that you want to search
![Page 24: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/24.jpg)
INSS 651 24
Search for first “/” in p_code
SQL> select p_code, instr(p_code,'/') from product;;
• P_CODE INSTR(P_CODE,'/')• -------- -----------------• 11QER/31 6• 13-Q2/P2 6• 14-Q1/L3 6• 1546-QQ2 0• 1558-QW1 0• 2232/QTY 5• 2232/QWE 5• 2238/QPD 5• 23109-HB 0• 23114-AA 0• 54778-2T 0
• P_CODE INSTR(P_CODE,'/')• -------- -----------------• 89-WRE-Q 0• PVC23DRT 0• SM-18277 0• SW-23116 0• WR3/TT3 4
• 16 rows selected.
![Page 25: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/25.jpg)
INSS 651 25
Separate P_code in two parts: before _ and after _ for names that contain -
SQL> select p_code, substr(P_code, 1, instr(p_code,'/')) part1from product where p_code like '%/%';• 2 3• P_CODE PART1• -------- --------• 11QER/31 11QER/• 13-Q2/P2 13-Q2/• 14-Q1/L3 14-Q1/• 2232/QTY 2232/• 2232/QWE 2232/• 2238/QPD 2238/• WR3/TT3 WR3/
• 7 rows selected.
![Page 26: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/26.jpg)
INSS 651 26
SQL> select p_code, substr(P_code, 1, instr(p_code,'/')-1) part1from productwhere p_code like '%/%'; 2 3• P_CODE PART1• -------- --------• 11QER/31 11QER• 13-Q2/P2 13-Q2• 14-Q1/L3 14-Q1• 2232/QTY 2232• 2232/QWE 2232• 2238/QPD 2238• WR3/TT3 WR3
• 7 rows selected.
![Page 27: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/27.jpg)
INSS 651 27
Get the right partSQL> select p_code, substr(P_code, instr(p_code,'/')) part2from productwhere p_code like '%/%'; 2 3
• P_CODE PART2• -------- --------• 11QER/31 /31• 13-Q2/P2 /P2• 14-Q1/L3 /L3• 2232/QTY /QTY• 2232/QWE /QWE• 2238/QPD /QPD• WR3/TT3 /TT3
• 7 rows selected.
![Page 28: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/28.jpg)
INSS 651 28
SQL> select p_code, substr(P_code, instr(p_code,'/')+1) part2from productwhere p_code like '%/%';2 3• P_CODE PART2• -------- --------• 11QER/31 31• 13-Q2/P2 P2• 14-Q1/L3 L3• 2232/QTY QTY• 2232/QWE QWE• 2238/QPD QPD• WR3/TT3 TT3
• 7 rows selected.
![Page 29: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/29.jpg)
INSS 651 29
SQL> select p_code, substr(P_code, 1, instr(p_code,'/')-1) part1, substr(P_code, instr(p_code,'/')+1) part2from productwhere p_code like '%/%'; 2 3
• P_CODE PART1 PART2• -------- -------- --------• 11QER/31 11QER 31• 13-Q2/P2 13-Q2 P2• 14-Q1/L3 14-Q1 L3• 2232/QTY 2232 QTY• 2232/QWE 2232 QWE• 2238/QPD 2238 QPD• WR3/TT3 WR3 TT3
• 7 rows selected.
![Page 30: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/30.jpg)
INSS 651 30
Remove the period from price
![Page 31: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/31.jpg)
INSS 651 31
SQL> select (substr(P_price, 1, instr(p_price,'.')-1)|| substr (P_price, instr(p_price,'.')+1)) as pricefrom product; 2 3
• PRICE• --------------------------------------------------------------------------------• 10999• 1499• 1749• 3995• 4399• 10992• 9987• 3895• 995• 144• 499
• PRICE• --------------------------------------------------------------------------------• 25699• 587• 699• 845• 11995
• 16 rows selected.
![Page 32: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/32.jpg)
INSS 651 32
COMMON ERRORS
![Page 33: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/33.jpg)
INSS 651 33
INTEGRITY CONSTRAINT VIOLATION
SQL> select * from trial1;
SN SCITY--- --------------------111 baltimore
SQL> insert into trial2 values (234,222);insert into trial2 values (234,222)• *ERROR at line 1:ORA-02291: integrity constraint
(AGGARWAL.SYS_C0026818) violated - parent key not found
![Page 34: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/34.jpg)
INSS 651 34
TOO MANY VALUES
• SQL> l• 1 select * from invoice where cust_code in• 2* (select inv-num, inv_date from invoice)• SQL> /• (select inv-num, inv_date from invoice)• *• ERROR at line 2:• ORA-00913: too many values
![Page 35: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/35.jpg)
INSS 651 35
LEASE TABLE
• SQL> desc lease;• Name Null? Type• ----------------------------------------- --------
----------------------------• L_NO NOT NULL CHAR(6)• P_NO CHAR(5)• RENTER_NO CHAR(4)• RENT NUMBER(5)• PAYMENT CHAR(5)• START_DATE DATE• FIN_DATE DATE
![Page 36: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/36.jpg)
INSS 651 36
MISMATCH COMPARISON
SQL> /
• select * from lease where rent in (select payment from lease)
*
ERROR at line 1:
ORA-01722: invalid number
![Page 37: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/37.jpg)
INSS 651 37
UNIQUE CONSTRAINT VIOLATION
SQL> select * from trial1;SN SCITY--- --------------------111 baltimoreSQL> insert into trial1 values (111,'mass');insert into trial1 values*ERROR at line 1:ORA-00001: unique constraint
(AGGARWAL.SYS_C0026745) violated
![Page 38: Lecture 5 SQL Continued](https://reader035.vdocuments.us/reader035/viewer/2022070406/568140c0550346895dac862a/html5/thumbnails/38.jpg)
INSS 651 38
NOT a Single_Group Function
• select distinct l_no, sum(rent)
• *
• ERROR at line 1:
• ORA-00937: not a single-group group function