1 / 38

Lecture 5 SQL Continued

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 .

gen
Download Presentation

Lecture 5 SQL Continued

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture 5SQL Continued 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 INSS 651

  3. Format: SQL>SELECT …. FROM WHERE colname1 condition(SELECT col2 . . (SELECT . . )) 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 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); INSS 651

  6. give the customer balance of customer whose order is 123 Logic: Before we can find balance of customer whose order is 123 we need to find the cust_numb first 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 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 INSS 651

  9. Give the names of salesperson and their customers in maryland SQL>Select cust_name, Sales_name from Customer C, salesperson S where c.sales_numb= s. sales_numb’ And Upper(c.cust_st) =‘MD’; C & S are aliases for tables Customer and Salesperson respectively INSS 651

  10. BUILT-IN-FUNCTIONS • AVG(value) • MAX(value) • MIN(value) • SUM(value) • STDDEV(value) • VARIANCE(value) • COUNT(value) • Etc… INSS 651

  11. Nested functions Select max (avg(grades)) etc..is allowed INSS 651

  12. String functions Several strings can be concatenated Use string1||string 2 || implies + select custname|| ‘,’ || cust_street as address From customer; INSS 651

  13. Substrings Returns substrings Format: Substr(stringvalue,m,n) Where m is the starting value and n is the length of characters 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; 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. 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. INSS 651

  17. Padding characters Rpad (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. INSS 651

  18. Length of string formatlength (string) Returns length of the string 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. 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 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. 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. 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 string Occurrence is the position of occurrence that you want to search INSS 651

  24. Search for first “/” in p_codeSQL> 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. 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,'/')) part1 from 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. INSS 651

  26. SQL> select p_code, substr(P_code, 1, instr(p_code,'/')-1) part1 from 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. INSS 651

  27. Get the right part SQL> select p_code, substr(P_code, instr(p_code,'/')) part2 from product where 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. INSS 651

  28. SQL> select p_code, substr(P_code, instr(p_code,'/')+1) part2 from product where 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. 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. INSS 651

  30. Remove the period from price 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. INSS 651

  32. COMMON ERRORS 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 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 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 INSS 651

  36. MISMATCH COMPARISON SQL> / • select * from lease where rent in (select payment from lease) * ERROR at line 1: ORA-01722: invalid number INSS 651

  37. UNIQUE CONSTRAINT VIOLATION SQL> select * from trial1; SN SCITY --- -------------------- 111 baltimore SQL> insert into trial1 values (111,'mass'); insert into trial1 values * ERROR at line 1: ORA-00001: unique constraint (AGGARWAL.SYS_C0026745) violated 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 INSS 651

More Related