Nested or Sub-queries Practice Questions with Answers

By
3 Comments

Hi Guys,
Today we discuss mysql sub-queries questions with answers. Sql queries are useful and used in web application development connected with database tables. We can fetch the data from database, insert the data to database, update the existing data in a database table and also delete the data or table in a database based on conditions or other functionality.




In below there are five tables are available with datas. Based on these five table we perform some sql queries like sub-queries or nested queryy operations.
There are five table are available. They are:

  1. employee
  2. empdept
  3. empsalary
  4. empproject
  5. project

Table 1:employee




EmpId EmpName Department ContactNo EmailId EmpHeadid
101 Isha E-101 1234567890 abc@gmail.com 105
102 Priya E-104 1234567890 abc@gmail.com 103
103 Neha E-101 1234567890 abc@gmail.com 101
104 Rahul E-102 1234567890 abc@gmail.com 105
105 abhishek E-101 1234567890 abc@gmail.com 102




table 2: empdept

DeptId DeptName DeptHead
E-101 HR 105
E-102 Development 101
E-103 House Keeping
E-104 Sales 104
E-105 Purchase 104

Table 3: empproject

EmpId ProjectId
101 p-1
102 p-2
103 p-1
104 p-4
105 p-4




Table 4: empsalary

EmpId Salary Ispermanent
101 2000 Yes
102 10000 Yes
103 5000 No
104 1900 Yes
105 2300 Yes

Table 5: project

ProjectId Duration
p-1 23
p-2 15
p-3 45
p-4 2
p-5 30

Question 1: Select departmentid and department name of the company which is assigned to the employee whose employee id is greater than 103

Answer:


Select DeptId,DeptName from empdept INNER JOIN employee ON empdept.DeptId=employee.Department WHERE employee.EmpId>103




Output as follows:

DeptId DeptName
E-101 HR
E-102 Development

Question 2: Select salary of the employee who is currently working on the project p-2

Answer:


Select Salary from empsalary INNER JOIN empproject ON empsalary.EmpId=empproject.EmpId WHERE empproject.ProjectId='p-2'

Output as follows:

Salary
10000

Question 3: Select name of the employee who is department head is HR

Answer:


Select Empname from employee INNER JOIN empdept ON employee.Department=empdept.DeptId WHERE empdept.Deptname='HR'

Output as follows:

Empname
Isha
Neha
abhishek




Question 4: Select name of the employee who is department head is Sales

Answer:


Select Empname from employee INNER JOIN empdept ON employee.Department=empdept.DeptId WHERE empdept.Deptname='Sales';

Output as follows:

Empname
Isha
Neha
abhishek
Priya




Question 5: Select name of the employee who is department head is Sales or department head is HR

Answer:


Select Empname from employee INNER JOIN empdept ON employee.Department=empdept.DeptId WHERE empdept.Deptname='Sales' OR empdept.DeptName='HR';

Output as follows:

Empname
Priya

Question 6: Select all the details from employee

Answer:


select * from employee;




Output as follows:

EmpId EmpName Department ContactNo EmailId EmpHeadid
101 Isha E-101 1234567890 abc@gmail.com 105
102 Priya E-104 1234567890 abc@gmail.com 103
103 Neha E-101 1234567890 abc@gmail.com 101
104 Rahul E-102 1234567890 abc@gmail.com 105
105 abhishek E-101 1234567890 abc@gmail.com 102




Question 7: Select the project id which duration is greater than 30 months.

Answer:


select ProjectId from project where Duration >30

Output as follows:

ProjectId
p-3

Question 8: Duration of the project which didn’t assigned to any candidates

Answer:


select Duration from project where ProjectId not in (select ProjectId from empproject)

Output as follows:

Duration
30
45

Question 9: Select the name of the candidate who is working in HR department and they are working as permanent.

Answer:


select EmpName from employee where Department in (select deptId from empdept where Deptname='HR') and EmpId in (select EmpId from empsalary where Ispermanent='Yes');

Output as follows:

EmpName
Isha abhishek

If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

3 thoughts on “Nested or Sub-queries Practice Questions with Answers”

  1. http://protostim.hu/thumbnails/cust/winstrol__before_and_after_female.html

    HGH should be used for a minimal of 16 weeks at 4iu daily to
    get the full profit, with testosterone at 400mg weekly for the second half of the cycle only.
    I would aim for at least 12 weeks if muscle growth is your primary goal; in any other case, it’s not worth the
    price. For fat loss, basic rejuvenation, recovery, and
    other low-level benefits are your goal? Nonetheless, those utilizing HGH with a longer-term outlook
    will have a glance at using it for six months and beyond.
    Sure, it gets very costly, however that’s why I all the
    time say development hormone is only for THE most severe of customers.

    Yes, authorized steroids may help increase metabolism and cut back physique fats, especially when used in conjunction with a healthy diet
    and exercise routine. They help in regulating cholesterol levels and help with pink blood
    cell manufacturing to construct muscle as rapidly as attainable.

    Platforms like Steroidmart provide a wide range of authentic anabolic
    steroids, together with top-quality oral and injectable options, all out
    there with out the need for a prescription. These steroids come from trusted producers and are
    supplied at highly competitive costs, permitting you to save each money and
    time. HGH-X2 does this by naturally stimulating the release
    of human growth hormone without you having
    to use any synthetic hormones. In current years, authorized steroids have turn out to be well-liked
    alternatives to anabolic steroids for these trying to acquire muscle mass, lose
    weight, and boost vitality ranges. These dietary dietary
    supplements, which may be purchased online, in gyms, and at
    shops. They are authorized to purchase, sell, and use as they
    do not require FDA approval or a prescription.
    Do Not neglect to incorporate a good-quality
    multivitamin in your complement stack. Some of one of the best authorized steroids in 2025 include TestoPrime for testosterone help and CrazyBulk Anadrole
    as a protected Anadrol alternative. These shops often have knowledgeable workers members who can information prospects towards the best
    dietary supplements for their objectives.
    Reliable producers will sometimes observe stringent security measures and often have their products tested by accredited third-party laboratories.
    This helps to guarantee that the products are secure to make use of and devoid of harmful substances.
    At All Times research the brand and its status, as well
    as confirm the certifications and licenses they possess to make sure you’re buying a secure and reliable product.

    If you are in search of secure, legal steroids weight Loss (http://protostim.hu/thumbnails/cust/winstrol__before_and_after_female.html), and efficient alternatives to anabolic steroids, CrazyBulk is the #1 trusted brand in the USA.
    Unlike black-market steroids, CrazyBulk offers FDA-compliant, pure formulation that deliver actual muscle positive aspects with out unwanted effects.
    Anabolic steroids are grouped into oral (tablets/ pills) and injectables based on their mode of
    administration. Primarily Based on their usage/ purpose,
    there are anabolic steroids for bulking, performance, and slicing cycles.
    Bulking refers to muscle growth, and the favored anabolic steroids used are
    Dianabol, Anadrol, and Deca-Durabolin. Anabolic androgenic steroids (AAS) are actively used in sports drugs to construct muscle mass,
    increase power and endurance, and burn fat. These
    medicine are used by bodybuilders, weightlifters, boxers, wrestlers, MMA
    fighters, swimmers, observe and subject athletes, and
    cyclists.
    Most of them are licensed pharmacies and their products adjust to GMP
    standards. By incorporating these dietary supplements into your every day routine, anticipate to realize spectacular outcomes, paving the way to
    your dream physique. The strong method in Hunter Take A Look At incorporates ingredients, such as
    3000mg of D-Aspartic Acid,(11) Vitamin D3,(12) and Zinc,
    which are known for their testosterone-boosting properties.
    Are you tired of spending hours at the health club with little to no results?
    Are all those grueling workout sessions leaving you drained and frustrated?

    You are certain to reap the advantages of AAS supplied you combine your
    steroid use with a good dietary plan and excessive
    interval depth coaching (HIIT). Injuries can critically disrupt a scheduled training program and adversely affect
    the psyche of an athlete. This group can help you recuperate physically, mentally, and psychologically.
    The use of recommended injectable steroids can tremendously assist in your bodily recovery from bodybuilding training-related accidents.
    Teens who take unlawful anabolic steroids are in danger for a similar problems as adults who
    use them. Frequent anabolic steroid medicines embrace fluoxymesterone (such as Halotestin) and nandrolone (such as Durabolin).

    The dose of illegal anabolic steroids is usually 10 to one hundred occasions greater than the dose
    a physician prescribes for medical problems. We know you want
    dependable injectable testosterone and efficient oral steroids, and
    we’ve received you coated. Our store is your go-to supply for getting the best
    steroid products online that ship real outcomes.

    Our prices are incredible, and we regularly have nice gross sales that make it even simpler
    to replenish on what you want. We ship discreetly all throughout America, so you
    possibly can shop with confidence, figuring out your order will arrive safely and privately.
    Yes, at Domestic Supply, you should purchase steroids on-line with no
    doctor’s go to. We provide a secure platform so that you just can choose from a extensive range of high quality products, ensuring a convenient and discreet shopping for experience.

  2. Pritty Post author

    Hi Gaurav,
    Example:Select DeptId,DeptName from empdept INNER JOIN employee ON empdept.DeptId=employee.Department WHERE employee.EmpId>103
    I just convert the inner join query to subqueries in simple method:
    select d.DeptId,d.DepatName,e.Department,e.EmpId from empdept d, employee e where e.Department=d.DeptId and e.empId>103

  3. gaurav

    really nice, new way to write sub queries by joining the two tables from which we want the required information.

Leave a Reply

Your email address will not be published.