Database and SQL

Consider the following relational schema: Suppliers(sid:integer, sname:string, city:string, street:string) Parts(pid:integer, pname:string, color:string) Catalog(sid:integer, pid:integer, cost:real) Consider the following relational query on the above database: SELECT S.sname FROM Suppliers S WHERE S.sid NOT IN (SELECT C.sid FROM Catalog C WHERE C.pid NOT (SELECT P.pid FROM Parts P WHERE P.color<> 'blue'))

Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?

A relational schema for a train reservation database is given below: Passenger (pid, pname, age)

Table: passenger

Table: Reservation

What pids are returned by the following SQL query for the above instance of the tables?

SELECT           pid
FROM  Reservation
WHERE           class 'AC' AND
                        EXISTS (SELECT *
                        FROM Passenger
                        WHERE age 65 > AND
                        Passenger. pid = Reservation. pid)

Consider a relational table with a single record for each registered student with the following attributes.

1. Registration_Number: Unique registration number for each registered student
2. UID: Unique Identity number, unique at the national level for each citizen
3. BankAccount_Number: Unique account number at the bank. A student can have multiple accounts or joint accounts. This attributes stores the primary account number
4. Name: Name of the Student
5. Hostel_Room: Room number of the hostel Which of the following options is INCORRECT?

Consider a relational table $r$ with sufficient number of records, having attributes $A_1, A_2, \dots ,A_n$ and let $1 \leq p \leq n$. Two queries $Q1$ and $Q2$ are given below.

$Q1: \pi_{A_1, \dots ,A_p} \left(\sigma_{A_p=c}\left(r\right)\right)$ where $c$ is a constant $Q2: \pi_{A_1, \dots ,A_p} \left(\sigma_{c_1 \leq A_p \leq c_2}\left(r\right)\right)$ where $c_1$ and $c_2$ are constants. The database can be configured to do ordered indexing on $A_p$ or hashing on $A_p$. Which of the following statements is TRUE?

Database table by name Loan_Records is given below.

What is the output of the following SQL query?

SELECT count(*)
FROM(
(SELECT Borrower. Bank_Manager FROM Loan_Records) AS S
NATURAL JOIN
(SELECT Bank_Manager, Loan_Amount FROM Loan_Records) AS T
);

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? SELECT Y FROM T WHERE X = 7;

Which of the following statements are TRUE about an SQL query? P: An SQL query can contain a HAVING clause even if it does not have a GROUP BY clauseQ: An SQL query can contain a HAVING clause only if it has GROUP BY clause R: All attributes used in the GROUP BY clause must appear in the SELECT clauseS: Not all attributes used in the GROUP BY clause need to appear in the SELECT clause

Suppose $R_{1} (\underline{A}, B)$ and $R_{2} (\underline{C}, D) $ are two relation schemas. Let $r_{1}$ and $r_{2}$ be the corresponding relation instances. $B$ is a foreign key that refers to $C$ in $R_{2}$. If data in $r_{1}$ and $r_{2}$ satisfy referential integrity constraints, which of the following is ALWAYS TRUE?

Consider the following relational schema.

Students(rollno: integer, sname: string) Courses(courseno: integer, cname: string) Registration(rollno: integer, courseno: integer, percent: real)

Which of the following queries are equivalent to this query in English?

“Find the distinct names of all students who score more than 90% in the course numbered 107”

(I)

SELECT DISTINCT S.sname 
FROM Students as S, Registration as R 
WHERE R.rollno=S.rollno AND R.courseno=107 AND R.percent >90

(II) $ \prod_{sname}(σ_{courseno=107 ∧ percent > 90} (Registration ⋈ Students)) $

(III) $\left\{T \mid ∃S \in Students, ∃R \in Registration ( S.rollno=R.rollno \land R.courseno=107 ∧ R.percent>90 ∧T.sname=S.sname)\right \}$

(IV) $\left\{ \langle S_N\rangle \mid ∃S_R∃R_P (\langle S_R,S_N\rangle ∈Students ∧ \ \langle S_R,107,R_P\rangle ∈Registration ∧ R_P>90) \right\}$