SQL
Q21.
Given the following statements: S1: A foreign key declaration can always be replaced by an equivalent check assertion in SQL S2: Given the table R(a,b,c) where a and b together form the primary key, the following is a valid table definition. CREATE TABLE S ( a INTEGER, d INTEGER, e INTEGER, PRIMARY KEY (d), FOREIGN KEY (a) references R) Which one of the following statements is CORRECT?Q22.
SQL allows duplicate 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: Select * from R where a in (select S. a from S)Q23.
Given the following schema: employees(emp-id, first-name, last-name, hire-date, dept-id, salary) departments(dept-id, dept-name, manager-id, location-id) 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: SQL>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); What is the outcome?Q24.
Consider the following relational schema: Suppliers (sid:integer, sname:string, saddress:string) Parts (pid:integer, pname:string, pcolor:string) Catalog (sid:integer, pid:integer, pcost:real) What is the result of the following query? (SELECT Catalog.pid from Suppliers, Catalog WHERE Suppliers.sid = Catalog.sid) MINUS (SELECT Catalog.pid from Suppliers, Catalog WHERE Suppliers.sname <> 'Sachin' and Suppliers.sid = Catalog.sid)Q25.
Consider the following relations A, B and C: How many tuples does the result of the following SQL query contain? SELECT A.Id FROM A WHERE A.Age > ALL (SELECT B.Age FROM B WHERE B.Name = 'Arun')Q27.
A table T1 in a relational database has the following rows and columns: \begin{array}{|c|c|c|} \hline \text {Roll no. } & \text {Marks} \\\hline 1& 10 \\\hline 2 & 20 \\\hline3 & 30 \\\hline 4 & \text{NULL}\\\hline \end{array} The following sequence of SQL statements was successfully executed on table T1. Update T1 set marks = marks + 5 Select avg(marks) from T1 What is the output of the select statement?Q28.
Consider the relation account (customer, balance) where customer is a primary key and there are no mall values. We would like to rank customers according to decreasing balance. The customer with the largest balance gets rank 1. Ties are not broken but ranks are skipped: if exactly two customers have the largest balance they each get rank 1 and rank 2 is not assigned. Consider these statements about Query 1 and Query 2. 1. Query 1will produce the same row set as Query 2 for some but not all databases 2. Both Query 1 Query 2 are correct implementations of the specification 3. Query 1 is a correct implementation of the specification but Query 2 is not 4. Neither query 1 nor Query 2 is a correct implementation of the specification 5. Assigning rank with a pure relational Query takes less time than scanning in decreasing balance order the assigning ranks using ODBC Which two of the above statements are correct?Q29.
Consider two tables in a relational database with columns and rows as follows: \overset{\text{Table: Student}}{\begin{array}{|c|c|c|} \hline \textbf {Roll\_no} & \textbf{Name} & \textbf{Dept\_id} \\\hline 1& \text{ABC} & 1\\\hline 2& \text{DEF} & 1 \\\hline 3 & \text{GHI} & 2\\\hline 4 & \text{JKL} & 3\\\hline \end{array}} \qquad \overset{\text{Table: Department}}{\begin{array}{|c|c|c|} \hline \textbf {Dept\_id} & \textbf{Dept\_name} \\\hline 1& \text{A} \\\hline 2& \text{B} \\\hline 3 & \text{C} \\\hline \end{array}} Roll_no is the primary key of the Student table, Dept_id is the primary key of the Department table and Student.Dept_id is a foreign key from Department.Dept_id What will happen if we try to execute the following two SQL statements? update Student set Dept_id = Null where Roll_on = 1 update Department set Dept_id = Null where Dept_id = 1Q30.
A relational schema for a train reservation database is given below What pids are returned by the following SQL query for the above instance of the tables? SELECT pid FROM Re servation WHERE class 'AC' AND EXISTS (SELECT * FROM Passenger WHERE age > 65 AND Passenger.pid Reservation.pid)