GATE CS Tuple Calculus and SQL questions

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)

Consider the following relational schema: employee (empId,empName,empDept) customer (custId,custName,salesRepId,rating) salesRepId is a foreign key referring to empId of the employee relation. Assume that each employee makes a sale to at least one customer. What does the following query return? SELECT empName FROM employee E WHERE NOT EXISTS (SELECT custId …

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” …

Consider the following relations $A, B$ and $C:$ …

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 clause Q : An SQL query can contain a HAVING clause only if it has a GROUP BY clause R : All attributes used in the GROUP BY clause must appear in the SELECT clause S : Not all attributes used in the GROUP BY clause …

Database table by name $\text{Loan\_Records}$ is given below. …

A relational schema for a train reservation database is given below. passenger(pid, pname, age) reservation(pid, class, tid) …

Consider the following relational schema: $\text{Suppliers}(\underline{\text{sid:integer}},\text{ sname:string, city:string, street:string})$ $\text{Parts}(\underline{\text{pid:integer}}, \text{ pname:string, color:string})$ $\text{Catalog}(\underline {\text{sid:integer, pid:integer}}, \text{cost:real})$ Consider the following relational query on the …

Let $R$ and $S$ be relational schemes such that $R=\{a,b,c\}$ and $S=\{c\}.$ Now consider the following queries on the database: $\pi_{R-S}(r) - \pi_{R-S} \left (\pi_{R-S} (r) \times s - \pi_{R-S,S}(r)\right )$ …

Which of the following tuple relational calculus expression(s) is/are equivalent to $\forall t \in r \left(P\left(t\right)\right)$? $\neg \exists t \in r \left(P\left(t\right)\right)$ $\exists t \notin r \left(P\left(t\right)\right)$ $\neg \exists t \in r \left(\neg P\left(t\right)\right)$ $\exists t \notin r \left(\neg P\left(t\right)\right)$

Consider the table employee(empId, name, department, salary) and the two queries $Q_1, \, Q_2$ 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? $Q_1:$ Select e.empId From …

Consider the relation employee(name, sex, supervisorName) with name as the key, supervisorName gives the name of the supervisor of the employee under consideration. What does the following Tuple Relational Calculus query produce? …

Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Assume that amounts $6000, 7000, 8000, 9000$ and $10000$ were each paid by $20\%$ of the students. Consider these …

Consider the relation enrolled (student, course) in which (student, course) is the primary key, and the relation paid (student, amount) where student is the primary key. Assume no null values and no foreign keys or integrity constraints. Given the following four queries: Query1: select student from enrolled where student in (select student from paid) Query2: …

Consider the relation account (customer, balance) where the 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 …

The relation book (title, price) contains the titles and prices of different books. Assuming that no two books have the same price, what does the following SQL query list? select title from book as B where (select count(*) from book as T where T.price>B.price) < 5

The employee information in a company is stored in the relation Employee (name, sex, salary, deptName) Consider the following SQL query Select deptName From Employee Where sex = ‘M’ Group by deptName Having avg(salary) > (select avg (salary) from Employee) It returns the names of the department in which

Let $R_1 \left(\underline{A}, B, C\right)$ and $R_2\left(\underline{D}, E \right)$ be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in $R_1$ referring to $R_2$. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances $r_1$ and $r_2$. Which …

Consider the set of relations shown below and the SQL query that follows. Students: (Roll_number, Name, Date_of_birth) Courses: (Course_number, Course_name, Instructor) Grades: (Roll_number, Course_number, Grade) Select distinct Name from Students, Courses, Grades where Students.Roll_number=Grades.Roll_number and Courses.Instructor = 'Korth' and …

With regards to the expressive power of the formal relational query languages, which of the following statements is true?