1 / 12

SQL

SQL. S_NAME. P_No. P_NAME. CITY. PRICE. Parsons. 23. Part-1. Newcastle. 2. R/R. 545. Part-2. Newcastle. 4. Vickers. 342. Part-3. Newcastle. 3. Vickers. 23. Part-1. Leeds. 4. Babcocks. 456. Part-6. Leeds. 6. AMEC. 213. Part-1. Leeds. 5. GM Eng. 215.

judson
Download Presentation

SQL

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. SQL

  2. S_NAME P_No P_NAME CITY PRICE Parsons 23 Part-1 Newcastle 2 R/R 545 Part-2 Newcastle 4 Vickers 342 Part-3 Newcastle 3 Vickers 23 Part-1 Leeds 4 Babcocks 456 Part-6 Leeds 6 AMEC 213 Part-1 Leeds 5 GM Eng. 215 Part-10 Bristol 7 Bae 321 Part-11 Bristol 4 GKN 342 Part-3 York 2 Group By select * from supplier;

  3. CITY Bristol Leeds Newcastle York select * from supplier group by city; ERROR at line 1:ORA-00979: not a GROUP BY expression select s_name, city from supplier group by city ERROR at line 1:ORA-00979: not a GROUP BY expression select city from supplier group by city select s_name from supplier group by city; ERROR at line 1:ORA-00979: not a GROUP BY expression

  4. name city part_no part_name city unit_price Newcastle Parsons 23 Part-1 Newcastle 2.00 Leeds R/R 545 Part-2 Newcastle 4.00 Bristol Vickers 342 Part-3 Newcastle 3.00 York Vickers 23 Part-1 Leeds 4.00 Babcocks 456 Part-6 Leeds 6.00 AMEC 213 Part-1 Leeds 5.00 G M 215 Part-10 Bristol 7.00 Bae 321 Part-11 Bristol 4.00 GKN 342 Part-3 York 2.00 SELECT CITY FROM SUPPLIER GROUP BY CITY

  5. name part_no part_name city unit_price Parsons 23 Part-1 Newcastle 2.00 R/R 545 Part-2 Newcastle 4.00 Vickers 342 Part-3 Newcastle 3.00 Vickers 23 Part-1 Leeds 4.00 Babcocks 456 Part-6 Leeds 6.00 AMEC 213 Part-1 Leeds 5.00 G M 215 Part-10 Bristol 7.00 Bae 321 Part-11 Bristol 4.00 GKN 342 Part-3 York 2.00 city count(*) Newcastle 3 Leeds 3 Bristol 2 York 1 SELECT CITY, COUNT(*) FROM SUPPLIER GROUP BY CITY;

  6. S_NAME P_No P_NAME CITY PRICE Parsons 23 Part-1 Newcastle 2 R/R 545 Part-2 Newcastle 4 Vickers 342 Part-3 Newcastle 3 Vickers 23 Part-1 Leeds 4 Babcocks 456 Part-6 Leeds 6 AMEC 213 Part-1 Leeds 5 GM Eng. 215 Part-10 Bristol 7 Bae 321 Part-11 Bristol 4 GKN 342 Part-3 York 2 SELECT CITY, COUNT(*) FROM SUPPLIER WHERE P_NAME='Part-1' GROUP BY CITY;

  7. name part_no part_ name city unit_price Vickers name part_no 23 part_name Part-1 Leeds city 4.00 unit_price AMEC Vickers 213 23 Part-1 Part-1 Leeds Leeds 5.00 4.00 Parsons 23 Part-1 Newcastle 2.00 Parsons 23 Part-1 Newcastle 2.00 AMEC 213 Part-1 Leeds 5.00 city count(*) Leeds 2 Newcastle 1 WHERE... GROUP BY....

  8. Grouping Rule Can only select columns in group by clause. Other columns could contain a different number of values than the grouping column. select * from supplier group by city; Will not work as grouping column city has less values than other columns

  9. HAVING HAVING is to Groups is what Where is to rows. SELECT CITY,COUNT(*) FROM SUPPLIER WHERE p_name ='Part-1' GROUP BY CITY HAVING COUNT(*)>1; FROM clause produces intermediate table the same as Supplier. WHERE clause evaluated next. GROUP BY clause divides intermediate table into groups based on City values. HAVING clause tests every Group against the condition i.e. count(*)>1. Eliminates any Group not satisfying the condition. SELECT clause projects the chosen columns to produce the final table. 'Provide the names of the cities where there is more than one supplier of part-1, and count the number of suppliers.'

  10. name part_ no part_ name city unit_ price Vickers 342 Part-3 Newcastle 3.00 R/R 545 Part-2 Newcastle 4.00 GM 215 Part-10 Bristol 7.00 BAe 321 Part-11 Bristol 4.00 Vickers 23 Part-1 Leeds 4.00 Babcocks 456 Part-6 Leeds 6.00 Parsons 23 Part-1 Newcastle 2.00 AMEC 213 Part-1 Leeds 5.00 GKN 342 Part-3 York 2.00 name part_ no part_ name city unit_ price Parsons 23 Part-1 Newcastle 2.00 Vickers 23 Part-1 Leeds 4.00 AMEC 213 Part-1 Leeds 5.00 Where... Then...Group By...

  11. name part_ no part_ name city unit_ price Parsons 23 Part-1 Newcastle 2.00 Vickers 23 Part-1 Leeds 4.00 AMEC 213 Part-1 Leeds 5.00 name part_ no part_ name city unit_ price Vickers 23 Part-1 Leeds 4.00 AMEC 213 Part-1 Leeds 5.00 city count(*) Leeds 2 Having COUNT(*)>1 Select..... CITY,COUNT(*)

  12. Summary and Order of Processing The following are the basic clauses used for SQL queries, in order they are processed in a query- From- defines tables to use and produces Cartesian products in join queries Where- picks rows from tables or Cartesian product based on specified criteria. Can employ sub-queries Group By- arranges rows into groups based on common values in specified column(s) Having- picks out groups satisfying specified criteria, Can apply aggregate functions and use sub-queries Select-picks columns and can apply aggregate functions Distinct-eliminates duplicate values in columns Order By- sorts rows based on specified column values The following set operators are also available for use in Where and Having clauses IN, ALL, ANY, EXISTS

More Related