1 / 11

SQL

SQL. Course_nm. credit_hrs. staff_name. logic. 3. Smith. semantics. 5. Peters. programming. 5. Jones. physics. 3. Hewlett. Faculty_name. Name. rank. Science. Hewlett. Prof. Maths. Smith. SL. Maths. Peters. SL. Maths. Jones. PL. Sub-query Course. Faculty.

saman
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. Course_nm credit_hrs staff_name logic 3 Smith semantics 5 Peters programming 5 Jones physics 3 Hewlett Faculty_name Name rank Science Hewlett Prof. Maths Smith SL Maths Peters SL Maths Jones PL Sub-query Course Faculty select course_nm from course where credit_hrs<=4 and staff_name in (select name from faculty where rank='Prof.'); 'What are the course names where the credit hours are four or less and staffed by professors?'

  3. Science Hewlett Prof. Maths Smith SL Maths Peters SL Maths Jones PL Science Hewlett Prof. Hewlett subquery first from faculty where rank='Prof. select name

  4. logic 3 Smith semantics 5 Peters programming 5 Jones physics 3 Hewlett Hewlett physics 3 Hewlett physics then outer query from course wherecredit_hrs<=4 and staff_name in result of sub query select course_nm

  5. Correlated Subqueries SELECT customer.name FROM customer WHEREEXISTS (SELECT city FROM SUPPLIER WHERE supplier.city=customer.city); Often a sub-query can be processed independently of the main query.In correlated sub-queries this is not possible. Processing of the sub-query relies on some value from the main query as above i.e.customer.city. Processing must start with the main query. FROM clause produces an intermediate table identical to CUSTOMER. For each row in this intermediate table the WHERE clause is executed. So there are probably multiple executions of the sub-query unless the intermediate table consists of a single row.

  6. SELECT customer.name FROM customer WHEREEXISTS (SELECT city FROM SUPPLIER WHERE supplier.city=customer.city; • Sub-query FROM clause produces a table identical to SUPPLIER. •  Sub-query WHERE clause picks out all rows satisfying the sub-query condition. •  That condition uses the value of city in the current row of the main query being considered. •  Sub-query SELECT clause projects CITY column to form final table for sub-query. • EXISTS returns TRUE if the final table from sub-query contains any values. FALSE otherwise. •  If TRUE returned then main query SELECT clause chooses the current value of customer row to be included in final result. • SELECT projects from this row the customer.name. •  If EXISTS returns FALSE current customer row not selected for final table. •  Sub-query then executed for next row of customer table.

  7. Name City United Biscuits Newcastle General Food Oils Hull Name Tate and Lyle York Name City General Flour Mills Cambridge Eastern Crops Hull RHM York Customer Supplier select customer.name from customer whereexists (select city from supplier where supplier.city=customer.city); 'Find the customers that have a supplier in the same city.'  Compares the first row in customer with all the rows in supplier.  If one or more rows in supplier match the where condition the customer name from customer is placed in result table. None match where condition so- Partial Result

  8. Name Name City City General Flour Mills United Biscuits Newcastle Cambridge General Food Oils General Food Oils Hull Eastern Crops Hull RHM York Tate and Lyle York Then takes next row of customer and compares with all the rows in supplier- Supplier Customer Second row of supplier matches condition so 'exists' returns true and customer.name selected for partial result. Partial Result Name select customer.name from customer whereexists (select city from supplier where supplier.city=customer.city);

  9. Name General Food Oils Name City Tate and Lyle United Biscuits Newcastle GeneralFood Oils Hull Tate and Lyle York Name City General Flour Mills Cambridge Eastern Crops Hull RHM York Customer Supplier Result

  10. IN, ALL, ANY IN Lists a set of values and tests if a value is or is not in the list. SELECT * FROM SUPPLIER WHERE PRICE IN (2.00, 3.00, 4.00); This list could be a set of column values returned from a sub-query. ALL/ANY ALL has the meaning 'every member of the set.'. So all can be used to test every value in a column against some condition. SELECT S_NAME FROM SUPPLIER WHERE PRICE < ALL (SELECT PRICE FROM SUPPLIER WHERE CITY='Leeds'); 'names of suppliers whose prices are lower than all the suppliers in Leeds'.

  11. SELECT S_NAME FROM SUPPLIER WHERE PRICE< ANY (SELECT PRICE FROM SUPPLIER WHERE CITY='Leeds'); 'find all the suppliers with a price lower than any supplier in Leeds'.

More Related