Gate 2005

Duration: 5 min

This video lesson is available to enrolled students.

Return to /learn/UPPSC-COMPUTER-OPERATOR-GRADE-B/database-management-system/sql/nested-correlated-subqueries/asset-gate-2005-3439 after enrolling

Inside: a video lesson and guided study material.

Module outline

  1. English Comprehension: Comprehension / Reading Comprehension / Unseen Passages (Critical Reasoning) (Paragraph Questions), Reading Comprehension, Vocabulary, Idioms and Phrases, Narrative Sequencing, Critical Reasoning
  2. Reasoning and Aptitude: Series (Number and Letter Series) (Numerical Relations and Reasoning), Coding Decoding, Analogy, Non Verbal Reasoning (Spatial Aptitude) (Spatial Reasoning) (Visual Reasoning), Blood Relations, Directions (Direction Test), Ranking, Order and Sequence, Logical Sequence Of Words, Seating Arrangements, Puzzle Solving (Puzzles), Syllogisms, Deductive and Inductive Reasoning (Logical Deduction and Induction) (Prepositional Reasoning), Data Sufficiency, Selection Decision Table (Decision Making), Calendar, Clock, Cubes & Dices, Data Arrangement, Venn Diagram, Analytical Reasoning (Counting Figures Reasoning), Number System, HCF LCM, Percentage, Ratio and Proportion (Ratios), Average, Profit and Loss, Simple Interest and Compound Interest, Speed Time and Distance, Mensuration and Geometry, Arithmetic, Mixture and Alligation, Age Problems, Data Interpretation, Alphabet and Word Tests, Numerical Reasoning, Number System, Averages, Mixtures and Alligation, Percentages, Series Completion, Analogy and Classification, Patterns in Two-Dimensional Figures, Coding and Decoding, Mathematical Operations, Direction Sense, Logical Deduction and Induction, Simplification and Approximation, Assembling and Grouping of Shapes, Transformation of Shapes, Three-Dimensional Patterns and Visualization, Cubes and Dice, Algebra, Traverse Surveying, Clocks and Calendars, Order, Ranking and Sequence, Constraint Puzzles, Syllogisms and Venn Diagrams, Ratio and Proportion, Mensuration, Permutations and Combinations, Project Monitoring, Resources and Safety, Profit, Loss and Discount, Sequences and Series, Powers, Surds and Logarithms, Subject-Verb Agreement, Sentence Completion, Geometry, Probability, Trigonometry, Measurement and Quantity Take-Off, Statistics, Data Sufficiency, Blood Relations, Speed, Time and Distance, Time and Work, Data Interpretation, Sequence and Series (Series), Algebra, Permutation and Combination, Combinatorics
  3. General Awareness: Physics, Chemistry, Biology, Geography, History, Polity, Indian Economy
  4. Computer Fundamentals: Basics of COA, Number System, Number Representation, Semiconductor Memories and Programmable Logic, Timing, Hazards and Testing, Processor Organization and Instructions, Number Systems and Codes, Combinational Logic Design, Combinational Logic Circuits, Number Systems and Codes
  5. Operating System: Introduction to OS, Unix OS, Linux OS
  6. C Programming: C Fundamentals, Control Flow, Functions, Arrays & Pointers, Storage Classes, Structures & Enums, DMA, Macros, Scoping & File Handling
  7. OOPS with C++ & Java: OOPS with C++, Java
  8. Database Management System: Basics of DBMS, SQL
  9. MS Office: MS Office Applications
  10. Internet & Web Technologies: Website, Browser & Services, Internet Fundamentals, Web Design & Development, Protocols & Services
AI summary & chapters

AI Summary

An AI-generated summary of this video lecture.

The video presents a detailed analysis of a SQL query problem from the GATE 2005 exam. The problem involves a relation named `book` with attributes `title` and `price`. The core of the lesson is interpreting a nested SQL query that selects titles based on a condition involving a subquery. The instructor explains that the subquery counts the number of books in the relation that have a price strictly greater than the price of the book currently being processed in the outer query. To make this concept clear, he draws two tables representing the outer alias `B` and the inner alias `T`. He populates these tables with a sequence of prices (10, 20, 30, 40, 50, 60, 70) to simulate the database state. This visual aid helps in tracing the execution of the query for each row.

Chapters

  1. 0:00 – 2:00 00:00-02:00

    The instructor begins by displaying the problem statement and the SQL code on the screen. He reads the query aloud, highlighting the outer query `select title from book as B` and the correlated subquery. He explains that for every row in the outer table `B`, the subquery runs to count rows in table `T` where `T.price > B.price`. To illustrate this, he draws two vertical tables on the whiteboard. He labels them `B` and `T` and starts writing down sample price values like 10, 20, 30, 40, 50, 60, and 70 in both tables. This setup is intended to create a concrete scenario to test the logic of the query.

  2. 2:00 – 4:38 02:00-04:38

    With the tables drawn, the instructor proceeds to calculate the count for each price in the outer table. He explains that for the book with price 70, there are zero books with a higher price, so the count is 0. For price 60, the count is 1 (only 70 is higher). He continues this pattern, showing that for prices 50, 40, and 30, the counts are 2, 3, and 4 respectively. Since the condition is `count < 5`, all these books satisfy the condition. He notes that for price 20, the count is 5, which fails the condition. Thus, the query returns the titles of the books with prices 70, 60, 50, 40, and 30. He identifies this set as the five most expensive books and selects option (D) as the correct answer, eliminating options suggesting four books or the fifth most inexpensive book.

The lecture demonstrates a powerful technique for understanding correlated subqueries: using a small, ordered dataset to trace execution. By manually calculating the count of more expensive books for each price point, the instructor proves that the query filters for the top 5 most expensive items. This step-by-step breakdown transforms an abstract SQL statement into a logical ranking problem, making it easier for students to grasp how the `count(*)` function interacts with the `where` clause in a nested context.

Loading lesson…