# Register now to access 7 million high quality study materials (What's Course Hero?) Course Hero is the premier provider of high quality online educational resources. With millions of study documents, online tutors, digital flashcards and free courseware, Course Hero is helping students learn more efficiently and effectively. Whether you're interested in exploring new subjects or mastering key topics for your next exam, Course Hero has the tools you need to achieve your goals.

44 Pages

### Nested_Query

Course: CSE,IT 101, Spring 2012
School: Indian Institute of...
Rating:

Word Count: 1531

#### Document Preview

Query Nested (Sub Query) Nested Query Consider the relations: Employees (ssn, last_name, first_name, salary, d_id) Department (dept_id, d_name) Nested Query Which employees have salaries greater than 'john' salary? 1. find what john earns, and then 2. find Who earns more than that johns amount. Problem can be sloved by placing one query inside another query Sub Queries The inner query or the subquery returns a...

Register Now

#### Unformatted Document Excerpt

Coursehero >> India >> Indian Institute of Technology, Madras >> CSE,IT 101

Course Hero has millions of student submitted documents similar to the one
below including study guides, practice problems, reference materials, practice exams, textbook help and tutor support.

Course Hero has millions of student submitted documents similar to the one below including study guides, practice problems, reference materials, practice exams, textbook help and tutor support.
Query Nested (Sub Query) Nested Query Consider the relations: Employees (ssn, last_name, first_name, salary, d_id) Department (dept_id, d_name) Nested Query Which employees have salaries greater than 'john' salary? 1. find what john earns, and then 2. find Who earns more than that johns amount. Problem can be sloved by placing one query inside another query Sub Queries The inner query or the subquery returns a value that is used by the outer query or the main query. A subquery is a SELECT statement that is within in a clause of another SELECT statement. Subquery is very useful when we need to select rows from a table with a condition that depends on the data in the table itself. Nested Query Syntax: SELECT column_list FROM table WHERE expr operator The subquery can be in a number of SQL clauses, including: The FROM clause The WHERE clause The HAVING clause INSERT, UPDATE, DELETE statements Nested Query Operator includes a comparison condition Comparison conditions fall into two classes: single-row operators (>, =, >=, <, <>, <=) multiple-row operators (IN, ANY, ALL) SELECT last_name FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name = john); Nested Query Types of Subqueries Single-row subqueries: Queries that return only one row from the inner SELECT statement Multiple-row subqueries: Queries that return more than one row from the inner SELECT statement There are also multiple-column Subqueries: Queries that return more than one column from the inner SELECT statement. Nested Query - Guidelines Enlcose subqueries in paranthesis Place subqueries on right side of the comparison condition Use single-row operators with single-row subqueries and multiple-row operators with multiple-row subqueries Single-row Subqueries Query2: D isplay the employees who is working in the department as same as that of employee 141. Single-row Subqueries Query2: D isplay the employees who is working in the department as same as that of employee 141. SELECT last_name, d_id FROM employees WHERE d_id = (SELECT d_id FROM employees WHERE ssn = 141); Single-row Subqueries Query 3: Display employees whose d_id is same as that of employee 141 and whose salary is greater than that of employee 143. Single-row Subqueries Query 3: Display employees whose d_id is same as that of employee 141 and whose salary is greater than that of employee 143. SELECT last_name, d_id, salary FROM employees WHERE d_id = (SELECT d_id FROM employees WHERE ssn = 141) AND salary > (SELECT salary FROM employees WHERE ssn = 143); Using Group functions in Subquery Query 4: Display the details of an employee whose salary is minimum among all employees. Using Group functions in Subquery Query 4: Display the details of an employee whose salary is minimum among all employees. SELECT last_name, d_id, salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees); Subquery in HAVING Query 5: Display all the departments that have a minimum salary greater than department 50. Subquery in HAVING Query 5: Display all the departments that have a minimum salary greater than department 50. Select d_id, MIN(salary) FROM employees GROUP BY d_id HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE d_id = 50); Multiple-row Subqueries What is wrong in the following query? SELECT ssn, last_name FROM employees WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY d_id); Inner query returns more than one row. Use multiple-row comparison operators. The multiple-row operator expects one or more values. Multiple-row Subqueries Operator Meaning IN Equal to any member in the list ANY Compare value to each value returned by the subquery ALL Compare value to every value returned by the subquery Employee ssn last_name first_name 100 Arun Kumar 101 Ram Varma 102 Rajesh Kumar 103 Anil Gupta 104 Shyam Kant 105 Raghu Raman Department dept_id d_name 10 20 Mkt Sales salary d_id 3000 3400 4400 4300 4700 3000 10 10 20 10 20 20 Using IN Query 6: Find the employees whose salary is equal to the minimum salary in their department Using IN Query 6: Find the employees whose salary is equal to the minimum salary in their department SELECT ssn, last_name, salary, d_id FROM employees WHERE salary IN (SELECT MIN(salary) FROM employees GROUP BY d_id); ssn last_name salary d_id 100 105 Arun Raghu 10 20 3000 3000 Using ANY Query 7: Find the employees whose salary is greater than or equal to maximum salary for each department select ssn, last_name, salary,d_id from employees where salary >= ANY (select max(salary) from employees group by d_id) --> (4300 , 4700) ssn last_name salary d_id 102 103 104 Rajesh Anil Shyam 4400 4300 4700 20 10 20 Using ANY Query 8: Find the employees whose salary is greater than the maximum salary for each department select ssn, last_name, salary,d_id employees from where salary > ANY (select max (salary) from employees group by d_id) --> (4300 , 4700) ssn last_name salary d_id 102 Rajesh 4400 20 Using ALL Query 9: Find the employees whose salary is greater than or equal to maximum salary for each department Select ssn,last_name,salary from employee where salary >= ALL (select max(salary) from employee group by d_id) --> (4300 , 4700) ssn last_name salary d_id 104 Shyam 20 4700 Using ALL Query 10: Find the employees whose salary is greater than the maximum salary for each department Select last_name,salary from employee where salary > ALL (select max(salary) from employee group by d_id) --> (4300 , 4700) No rows selected Correlated Sub Queries Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every row of the outer query. A subquery references a column of a table present in the outer query. Nested Vs Correlated Sub Queries Nested: the inner SELECT query runs first and executes once, returning values to be used by the main query Correlated: executes once for each candidate row considered by the outer query. OR inner query is driven by the outer query Nested Vs Correlated Sub Queries Nested: 1. the inner query executes first and finds a value 2. the outer query executes once, using the value from the inner query Correlated: Get a candidate row (fetched by the outer query) Execute the inner query using the value of the candidate row Use the values resulting from inner query to qualify or disqualify the candidate Repeat until no candidate row remains Correlated Sub Queries Syntax: SELECT column1, column2, .... FROM table1 outer WHERE column1 operator (SELECT column1, column2 FROM table2 WHERE expr1=outer.expr2); The WHERE clause of subquery references a column of a table present in the outer query. Correlated Sub Queries Query: Find all employees who earn more than the average salary in their department: SELECT l_name, salary, d_id FROM employee outer WHERE salary > (SELECT AVG(salary) FROM employees WHERE d_id=outer.d_id); Using SET operators SET Operators The SET operators combine the results of two or more component queries into one result Queries containing SET operators are called compound queries All SET operators have equal precedence SET Operators UNION : All distinct rows selected by either query UNION ALL: All rows selected by either query, including all duplicates INTERSECT: All distinct rows selected by both queries MINUS: All distinct rows that are selected by the first SELECT statement and not selected in the second SELECT statement SET Operator Employee e_id l_name job_id hire_date 100 101 102 Raja Kumar Ram SA-REP 10-Jun-80 ST-MAN 14-Aug-84 SA-PRES 08-Apr-80 d_id 10 20 30 SET Operator Job_history e_id start_date end_date job_id d_id 100 101 101 100 10-Jun-80 14-Aug-84 04-Sep-85 29-Mar-82 28-Mar-82 03-Sep-85 30-Jun-87 18-Jan-88 SA-REP ST-MAN ST-CLK SA-MGR 90 20 20 90 UNION SET Operator The UNION operator returns all rows selected by either query. Returns all rows from multiple tables and eliminate any duplicate rows UNION: Guidelines The number of columns and datatypes of the columns being selected must be identical in all the SELECT statements used in the query. The names of columns need not be identical. UNION operates over all of the columns being selected. By default, output is sorted in ascending order of the first column of SELECT clause. UNION Display the current and previous job details of all employees. Display each employee only once. SELECT e_id, job_id FROM employees UNION SELECT e_id, job_id FROM job_history; e_id 100 100 101 101 102 job_id SA-REP SA-MGR ST-MAN ST-CLK SA-PRES 5 rows UNION SELECT e_id, job_id,d_id FROM employees UNION SELECT e_id, job_id,d_id FROM job_history; e_id 100 100 100 101 101 102 job_id SA-REP SA-REP SA-MGR ST-MAN ST-CLK SA-PRES d_id 10 90 90 20 20 30 6 rows UNION ALL Duplicate rows are not eliminated Output is not sorted by default With the exception of above, guidelines for both UNION and UNION ALL are the same UNION ALL Display the current and previous departments of all employees. SELECT e_id, job_id,d_id FROM employees UNION ALL SELECT e_id, job_id,d_id FROM job_history; e_id job_id d_id 100 100 100 101 101 101 102 SA-REP SA-REP SA-MGR ST-MAN ST-MAN ST-CLK SA-PRES 10 90 90 20 20 20 30 7 rows INTERSECT Display the employee Ids and job Ids of employees who currently have a job title that they held before beginning their tenure with the company SELECT e_id, job_id FROM employees INTERSECT SELECT e_id, job_id FROM job_history; e_id job_id 100 SA-REP 101 ST-MAN 2 rows MINUS Display the employee ID of those employees who have not changed their jobs even once. SELECT e_id, job_id,d_id FROM employees MINUS SELECT e_id, job_id,d_id FROM job_history; e_id 103 job_id SA-PRES Thank You
Find millions of documents on Course Hero - Study Guides, Lecture Notes, Reference Materials, Practice Exams and more. Course Hero has millions of course specific materials providing students with the best way to expand their education.

Below is a small sample set of documents:

Indian Institute of Technology, Madras - CSE,IT - 101
SQLSQL refers to query language and defines the structure of thedata, modify the db and specify security constraints.SQL has serval language parts:DDL: Provides commands for defining,deleting,modifyingrelation schemas.DML:Includes commands for inser
Indian Institute of Technology, Madras - CSE,IT - 101
Security ViewsBy:B.Senthil KumarAsst. Prof, CSEDatabase Management Systems IV SemTable of ContentsAn IntroductionWhat is a View?Why use Views?Creating a ViewGuidelines Creating a ViewCreating a View using column aliasQuerying a ViewUpdates on
Indian Institute of Technology, Madras - CSE,IT - 101
Higher NormalizationPresentation by:P.MirunaliniAsst. ProfessorSSNCEB.E. IV Sem Database Management SystemsOverview Multi-valued Dependencies Fourth Normal Form 4NFFirst Normal Form 1NFNormalizng nested relations into PROJS is a nested relation
Indian Institute of Technology, Madras - CSE,IT - 101
Higher NormalizationHigherOverviewOverviewJoin Dependencies [JD]JoinFifth Normal Form 5NFJoin DependencyJoinThere exists relvars that cannot be nonloss-decomposed into tworelvars thatnonloss decomposedprojections but can be nonloss-decomposed
Indian Institute of Technology, Madras - CSE,IT - 101
Concurrency ControlOverviewThree concurrency problemsLockingThree concurrency problems RevisitedDeadlockConcurrencyConcurrency DBMS allow many transactions to access the samedatabase at the same time.A kind of control is needed to ensure that con
Indian Institute of Technology, Madras - CSE,IT - 101
Distributed SystemsP.MirunaliniA.P,CSESSNCEOVERVIEWDistributed SystemsHomogeneous / HeterogeneousDistributed Data StorageData ReplicationData FragmentationHorizontal FragmentationVertical FragmentationTransparencyCentralized DatabaseIn a cen
Indian Institute of Technology, Madras - CSE,IT - 101
Concurrency(Intent Locking)P.Mirunalini,A.P,CSESSNCEIsolation LevelsSerializability guarantees isolation for ACID properties.Protocols used to guarantee serializability reduce the degree ofconcurrency or overall system throughput.System supports
Indian Institute of Technology, Madras - CSE,IT - 101
RecoveryP.MIRUNALINIAP,CSESSNCEOverviewTransactionsTransaction RecoveryWrite Ahead Log [WAL] RuleACID PropertiesSystem RecoveryCheckpointTwo Phase Commit [2PC]Database RecoveryPurpose of Database RecoveryTo bring the database into the last c
Indian Institute of Technology, Madras - CSE,IT - 101
Database RecoveryTechniquesPresentation by:P.MirunaliniA.P/ CSESSNCEB.E. IV Semester BDatabase RecoveryPurpose of Database RecoveryTo bring the database into the last consistent state, which existed prior tothe failure.To preserve transaction p
Indian Institute of Technology, Madras - CSE,IT - 101
SERIALIZABILITYP.MIRUNALINIAP,CSESSNCEOVERVIEWSchedulesConflict OperationsCharacterizing schedulesRecoverable and non-recoverable schedulesCascading rollback and cascadelessSerializability of SchedulesSerial and non-serial schedulesConflict se
Indian Institute of Technology, Madras - CSE,IT - 101
Transaction ProcessingConceptsIntroduction to Transaction ProcessingThe concept of transaction provides a mechanism for describinglogical units of a database Processing .Transaction processing systems include large databases andhundreds of concurren
E. Michigan - ENG - 121
Diabetes Drugs The Adverse effects of Diabetes DrugsEastern Michigan UniversityENG 121Diabetes Drugs The Adverse Effects of Diabetes DrugsAlthough, many medications and/or drugs go through the approval process, not all ofthese medications are approve
Bahria University - MANAGEMENT - 123
World0.6820.6790.6760.6740.670South Asia0.5480.5450.5380.5320.527Low human development0.4560.4530.4480.4430.437Pakistan0.5040.5030.4990.4950.493Year20112010200920082007HDI (Human Development Index) measure longterm progress i
Bahria University - MANAGEMENT - 1456
World Trade OrganizationThe World Trade Organization (WTO) is an international body whose purpose is to promote freetrade by persuading countries to abolish import tariffs and other barriers. As such, it has becomeclosely associated with globalisation.
UMiami - PHYSICS - phy 2049
brown (jmb7226) Home Work V belay (2049)This print-out should have 24 questions.Multiple-choice questions may continue onthe next column or page nd all choicesbefore answering.001 10.0 pointsTo recharge a 12 V battery, a battery chargermust move 2.
Oakland University - PDF - 20204
Introductory Biology BIOS 10161Fall 2006Instructor:Joseph E. O'TousaGalvin Life Sciences Rm. 262Email: jotousa@nd.eduLecture Time and Place: MWF, 12:50-1:40 PM, JORDAN 101.Midterm Exams: 8:00-9:15 AM, DEBARTOLO 101.(Exam dates indicated on right s
Oakland University - PDF - 20204
Chapter 2 Chemistry of Life (Small Molecules)Ch ap t er 2 , pag e 1Ch ap t er 2 , pag e 2Ch ap t er 2 , pag e 3Ch ap t er 2 , pag e 4Ch ap t er 2 , pag e 5Chap t er 2 Key Wo r d s:AcidAnionBaseCationIonFunctional groupChemical reactionProduc
Oakland University - PDF - 20204
Macromolecules and the Origins of Lifep o ly m e r- - - - - - b uilt f r o m - - - - - - - - - &gt;Ch ap t er 3 , pag e 1m o n o m e r ( s)Ch ap t er 3 , pag e 2R+HN3CHCh ap t er 3 , pag e 3C OOCh ap t er 3 , pag e 4Ch ap t er 3 , pag e 5Ch a
Oakland University - PDF - 20204
Chapter 4 Cells: The WorkingUnits of LifePr o k ar y o t ic c ellsChap t er 4 , p ag e 1The euc ar y o t ic c ellChap t er 4 , p ag e 2Chap t er 4 , p ag e 3Mit o c ho nd r iaChlo r o p last sLy so so m esPer o x iso m esVac uo lesChap t er 4
Oakland University - PDF - 20204
Chapter 5 The Dynamic Cell MembraneChap t er 5 , p ag e 1Chap t er 5 , p ag e 2Chap t er 5 , p ag e 3Mem b r ane Tr ansp o r t Pr o c essesI. Passiv e Pr o c essesChap t er 5 , p ag e 4Chap t er 5 , p ag e 5Chap t er 5 , p ag e 6Chap t er 5 , p a
Oakland University - PDF - 20204
Chapter 6 Energy, Enzymes, and Metabolisme x e r g o n ice n d e r g o n icChap t er 6 , p ag e 1\$ \$ = ATPChap t er 6 , p ag e 2Chap t er 6 , p ag e 3Chap t er 6 , p ag e 4lac t o se - - - - - - - - - - - - - - - &gt; g alac t o se( c o l o r l e ss
Oakland University - PDF - 20204
Chapter 7 Pathways That Harvest Chemical EnergyChapt er 7 , page 1Chapt er 7 , page 2Chapt er 7 , page 3Chapt er 7 , page 4NADH + H+CO22C4C6CNADH + H+CO2FADH25CATPNADH + H+NADH + H+CO24CChapt er 7 , page 5Chapt er 7 , page 6Chapt er 7
Oakland University - PDF - 20204
Chapter 8 Photosynthesis: Energy from SunlightChap t er 8 , p ag e 1u se sg ener at esChap t er 8 , p ag e 2e-e-eee-e-Chap t er 8 , p ag e 3e-e-H+H+Chap t er 8 , p ag e 4Chap t er 8 , p ag e 5Chap t er 8 , p ag e 6Chap t er 8 , p ag e 7C
Oakland University - PDF - 20204
Chapter 9 Chromosomes, the Cell Cycle, and Cell DivisionChap t er 9 , p ag e 1Chap t er 9 , p ag e 2d esc r ip t iio n o f Cy c lin D ( 2 1 - 3 0 in lo d ish)Chap t er 9 , p ag e 3Chap t er 9 , p ag e 4Chap t er 9 , p ag e 5Mitosis, Cell DivisionC
Oakland University - PDF - 20204
Chapter 10 Genetics: Mendel and BeyondChap t er 1 0 , p ag e 1Chap t er 1 0 , p ag e 2Chap t er 1 0 , p ag e 3Dominant/recessiveAa genotype has the same phenotypeas AA.Incomplete DominantAa genotype have a phentoype that isintermediate between AA
Oakland University - PDF - 20204
Chapter 12 From DNA to Protein: Genotype to PhenotypeChapt er 1 2 , page 1Chapt er 1 2 , page 2DNARNAChapt er 1 2 , page 3Prot einChapt er 1 2 , page 4Chapt er 1 2 , page 5Chapt er 1 2 , page 6Chapt er 1 2 , page 7Chapt er 1 2 , page 8Chapt er
Oakland University - PDF - 20204
Chapter 13 The Genetics of Viruses and ProkaryotesChap t er 1 3 , p ag e 1Chap t er 1 3 , p ag e 2Chap t er 1 3 , p ag e 3Bringing genetic information into bacterial cell.1. Conjugation2. Transduction3. TransformationChap t er 1 3 , p ag e 4Chap
Oakland University - PDF - 20204
Chapter 14 The Eukaryotic Genome and Its ExpressionOrganism Genome size ( Mbp) # genesE. coli4 .64300yeast1 2 .15800nemat ode9719100f ruit f ly18013500arabidopsis11 926000mouse300024000human320024000Chapt er 1 4 , page 1Chapt er 1
Oakland University - PDF - 20204
Chapter 15 Cell Signaling and Communicationcaffeineadenosinecaffeineadenosinecaffeinedrowsinessheart beatincreasesepinephrineliver releasesglucoseChapt er 1 5 , page 1Chapt er 1 5 , page 2Recept ors:1 . Prot einKinase Recept or( Membrane)
Oakland University - PDF - 20204
Chapter 16 Recombinant DNA and Biotechnology5'-A-G-G-C-T-C-G-G-A-A-T-T-C-T-T-A-C-C-C-G-A- 3'|3'-T-C-G-G-G-T-A-A-G-A-A-T-T-C-C-G-A-G-C-C-T-5'A-A-T-T-C -T-T-A-C-C-C-G-A- 3'|EcoR1: 5' overhang5'5'-T-C-G-G-A-A-T-T-C-T-|3'A-A-T-T-C-T-|-A-G-A-A-
Oakland University - PDF - 20204
Chapter 17 Genome Sequencing, Molecular Biology and MedicineChapt er 1 7 , page 1oncogenes:t umor suppressors:wild t yperecept oroncogenic formof recept orwild t yperecept orChapt er 1 7 , page 2oncogenic formof recept orchronic myolegenous l
Oakland University - PDF - 20204
Chapter 11 DNA and Its Role in HereditySmoothpolysacchrideslipidsRNAproteinsDNAmix each with rough cells, assay in miceonly DNA fraction hadtransforming abilityChapter 11, page 1Chapter 11, page 2T2 bacteriophageDNAproteinChapter 11, page 3
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Chapter 2The Historyof ManagementManagement5th EditionChuck WilliamsChapter 2Copyright 2009 by Cengage Learning Inc. All rights reservedPrepared byDavid &amp; Jackson FerrellB-Books, Ltd.1 What would you do? J eff new CEO/owner Co. in high growt
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Chapter 4Ethics andSocial ResponsibilityManagement5th EditionChuck WilliamsChapter 4Copyright 2009 by Cengage Learning Inc. All rights reservedPrepared byDavid &amp; Jackson FerrellB-Books, Ltd.1Ethical and UnethicalWorkplace BehaviorEthicsThe
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Chapter 16ControlManagement5th EditionChuck WilliamsChapter 16Copyright 2009 by Cengage Learning Inc. All rights reservedPrepared byDavid &amp; Jackson FerrellB-Books, Ltd.1TheControlProcessBegins with establishment of clearBegins with establishm
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Oakland University - PDF - 20204
Chapter 2Economic Models:Trade-offs and TradeI.Chapter 2: ModelsI. PPFII. Comparative advantageIII. Circular Flow DiagramII. Positive vs. NormativeReview:Principles of individual choice Resources are scarce The cost of something is what you ha
Oakland University - PDF - 20204
Chapter 3:Supply and DemandI.II.III.IV.Setting the stageDemandSupplyEquilibriumChapter 3:The model of Supply and Demand The setting Competitive Market: There are many buyers and sellers of the samegood or service No one individual or firm c
Oakland University - PDF - 20204
Chapter 4:The Market Strikes BackWhat happens when thegovernment intervenes in aperfectly competitive market?Introducing Chapter 4Reviewing where we standAssume a perfectly competitive marketPrice is stable at equilibrium, but-Any given firm want
Oakland University - PDF - 20204
Summary: Chapter 4 Four interventions: Price Ceiling Price Floor Quantity Control Excise Tax In a perfectly competitive market, all of theseinterventions will lead to some inefficiency.Note that these results only hold in a perfectly competitivem
Oakland University - PDF - 20204
CHAPTER 6:CONSUMER AND PRODUCER SURPLUSI. Consumer SurplusII. Producer SurplusIII. Imposition of Price floors and ceilingsIV. Impact of taxConsumer Surplus andProducer Surplus Consumer surplus: the net gain an individual getsfrom buying a good P
NJIT - CS - 280