Homechevron_rightEngineeringchevron_rightRPSC Programmerchevron_rightProgrammer DBMSchevron_right<p><span style=" ">Consider the following SQL qu...

Consider the following SQL query: ...

  • Q.

    Consider the following SQL query:

    PROJ(Pnum, Dnum)

    DEPT(Dnumber, Mgr_Ssn)
    EMP(fname, lname, Ssn)

    Works_ON(Pno, ESSn)

    (SELECT DISTINCT Pnum

    FROM PROJ, DEPT, EMP WHERE Dnum = Dnumber

    AND Mgr_ssn = Ssn AND Lname=’XXXX’)

    UNION

    (SELECT DISTINCT Pnum FROM PROJ, WORKS_ON, EMP WHERE Pnum=Pno AND ESSn=Ssn

    AND Lname=’XXXX’)

  • filter_dramaExplanation
    Answer is : A

    The main Query has two Subquery 

    Subquery 1

    SELECT DISTINCT Pnum FROM PROJ, DEPT, EMP WHERE Dnum=Dnumber AND Mgr_ssn=Ssn AND Lname=’XXXX’)

    This query result as unique Pnum which is project number from the table PROJ, DEPT, EMP 

    where three Conditions will follow 

    Dnum=Dnumber

    Mgr_ssn=Ssn 

    Lname=’XXXX’

    As AND operator is used it will be treated as mandatory to follow all the conditions

    So here we will get the project number for those Whose last name is "XXXX"  and Mgr_ssn is the same as Ssn and Dnum is the same as Dnumber  (Assume Mgr_SSn is for Manager (who is also an employee))

    Subquery 2

    (SELECT DISTINCT Pnum FROM PROJ, WORKS_ON, EMP WHERE Pnum=Pno AND ESSn=Ssn

    AND Lname=’XXXX’)

    This query result as unique Pnum which is project number from the table PROJ, WORKS_ON, EMP

    where again three Condition will follow 

    Pnum=Pno 

    ESSn=Ssn

    Lname=’XXXX’

    Again AND operator is used it will be treated as mandatory to follow all the conditions

    So here we will get the project number for those Whose last name is "XXXX"  and ESSn_ssn is the same as Ssn and Pnum is the same as Pno. (Assume ESSn is for the worker  (who is also an employee))

    and At the End, The Union of both Subquery displayed as a result

    All project numbers for projects that involve an employee with the last name ‘XXXX’, either as a worker or as a manager  

    Therefore option 1 is correct

Discussion

    No one started the discussion yet. Break the ice and start the conversation.
    Please Login to be part of the discussion.

Similar Questions

  • 1. A data communication system has ______ components.
  • filter_dramaExplanation
    Answer is : C
    • Message: The message is the information (data) to be communicated; Popular forms of information include text, numbers, pictures, audio, and video
    • Sender: The sender is the device that sends the data message; It can be a computer, workstation, telephone handset, video camera, and so on
    • Receiver: The receiver is the device that receives the message; It can be a computer, workstation, telephone handset, television, and so on
    • Transmission medium: The transmission medium is the physical path by which a message travels from sender to receiver; Some examples of transmission media include twisted-pair wire, coaxial cable, fiber-optic cable and radio waves
    • Protocol: A protocol is a set of rules that govern data communication
  • 2. The protocol data unit (PDU) for the application layer in the Internet stack is
  • filter_dramaExplanation
    Answer is : C

    The correct answer is option 3

    CONCEPT

    PDU for the application layer is messages.

    Additional Information

    • A protocol data unit (PDU) is a single unit of information transmitted among peer entities of a computer network.
    • PDU for the physical layer is bits.
    • PDU for the data link layer is a frame.
    • PDU for the network layer is packets.
    • PDU for the transport layer is a segment.
  • 3. Which of the following occurs when a transaction cannot proceed for an indefinite period of time while other transactions in the system continue normally?
  • filter_dramaExplanation
    Answer is : A

    Concept:

    • Wound- wait and wait- die use timestamps to prevent deadlock.
    • Deadlock  occurs when each transaction t in a set of two or more transaction is waiting for some item that is locked by some other transaction  in the set
    • Starvation is nothing but one transaction is getting chances again and again and other are waiting for their turn and we cannot say when will they get chance to execute this is
      nothing but indefinite period of time waiting.


    Hence option 1 is the correct answer.

  • 4. Truncating tables shall ________.
  • filter_dramaExplanation
    Answer is : A

    The correct answer is option 1.

    Concept:

    TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

    SQL Truncate:

    The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

    Syntax:

    TRUNCATE TABLE table_name;

    SQL Drop:

    The DROP TABLE command deletes a table in the database.

    Syntax:

    DROP TABLE table_name;

    So truncating tables shall empty a table completely but drop table deletes a table in the database.

    Hence the correct answer is empty a table completely.

  • 5. Which is the most reliable topology
  • filter_dramaExplanation
    Answer is : C

    the correct answer is option 3

    CONCEPT:

    Mesh topology:

    A mesh topology provides a connection from each node to every other node on the network. This provides a fully redundant network and it is the most reliable of all networks. If any link or node in the network fails, then there will be another path that will allow network traffic to continue.

    What is Mesh Topology?

    Additional Information

    Ring topology:

    A ring topology is a in which each node connects to exactly two other nodes, forming a single continuous pathway for signals through each node that makes a ring. Data travels from node to node, with each node along the way handling every packet.

    Star topology:

    A star topology is a topology for a Local Area Network(LAN) in which all nodes are individually connected to a central connection point, like a hub or a switch. A star takes more cable than bus topology, but the benefit is that if a cable fails, only one node will be brought down.

    Bus topology:

    A bus topology is a topology for a LAN in which all the nodes are connected to a single cable. The cable to which the nodes connected is called a backbone. if the backbone is broken, the entire segment fails. Bus topology is easy to install and doesn't require much cabling compared to the alternatives.

Programmer DBMSTopics

leaderboardLeaderboard
  • Rahul Kumar

    191 Points

  • VIKRAM JEET

    54 Points

  • GEETHIKA CHOWDARY

    53 Points

  • sunita saini

    52 Points

  • Zain

    49 Points