SQL - Sample Questions

<div class="QuizQuestionCard_quizCard__quizQuestionTextContainer__question__tv5de"> Consider the table employee(empId, name, department, salary) and the two queries Q1 ,Q2 below. Assuming that department 5 has more than one employee, and we want to find the employees who get higher salary than anyone in the department 5, which one of the statements is TRUE for any arbitrary employee table? <pre>Q1 : Select e.empId From employee e Where not exists (Select * From employee s where s.department = “5” and s.salary &gt;=e.salary) Q2 : Select e.empId From employee e Where e.salary &gt; Any (Select distinct salary From employee s Where s.department = “5”) </pre></div>

<div class="QuizQuestionCard_quizCard__quizQuestionTextContainer__question__tv5de">Given the following schema: <pre> employees(emp-id, first-name, last-name, hire-date, dept-id, salary) departments(dept-id, dept-name, manager-id, location-id) </pre> You want to display the last names and hire dates of all latest hires in their respective departments in the location ID 1700. You issue the following query: <pre>SQL&gt; SELECT last-name, hire-date FROM employees WHERE (dept-id, hire-date) IN ( SELECT dept-id, MAX(hire-date) FROM employees JOIN departments USING(dept-id) WHERE location-id = 1700 GROUP BY dept-id); </pre> What is the outcome? </div>

<div class="QuizQuestionCard_quizCard__quizQuestionTextContainer__question__tv5de">Consider the relation account (customer, balance) where customer is a primary key and there are no null values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. ties are not broke but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned <pre><strong>Query1:</strong> select A.customer, count(B.customer) from account A, account B where A.balance &lt;=B.balance group by A.customer <strong>Query2:</strong> select A.customer, 1+count(B.customer) from account A, account B where A.balance &lt; B.balance group by A.customer </pre> Consider these statements about Query1 and Query2. <pre>1. Query1 will produce the same row set as Query2 for some but not all databases. 2. Both Query1 and Query2 are correct implementation of the specification 3. Query1 is a correct implementation of the specification but Query2 is not 4. Neither Query1 nor Query2 is a correct implementation of the specification 5. Assigning rank with a pure relational query takes less time than scanning in decreasing balance order assigning ranks using ODBC. </pre> Which two of the above statements are correct?</div>

Which SQL statement is used to extract data from a database?

<p>Consider a database table T containing two columns X and Y each of type integer. After the creation of the table, one record (X=1, Y=1) is inserted in the table. Let MX and My denote the respective maximum values of X and Y among all records in the table at any point in time. Using MX and MY, new records are inserted in the table 128 times with X and Y values being MX+1, 2*MY+1 respectively. It may be noted that each time after the insertion, values of MX and MY change. What will be the output of the following SQL query after the steps mentioned above are carried out?</p><p>&nbsp;</p><p><strong>SELECT Y FROM T WHERE X=7;</strong> </p>

A tuple is a ?

<div class="QuizQuestionCard_quizCard__quizQuestionTextContainer__question__tv5de">SQL allows tuples in relations, and correspondingly defines the multiplicity of tuples in the result of joins. Which one of the following queries always gives the same answer as the nested query shown below: <pre> select * from R where a in (select S.a from S) </pre> </div>

Which SQL function is used to count the number of rows in a SQL query?