SQL

Consider the following database tables of a sports league.

player(pid,pname,age)        team(tid,tname,city,cid)
coach(cid,cname)              members(pid,tid)

An instance of the table and an SQL query are given.

player                  coach            team                         members
pid  pname    age       cid  cname       tid  tname  city    cid      pid  tid
1    Jasprit  31        101  Ricky       10   MI     Mumbai  102      1    10
2    Atharva  24        102  Mark        20   DC     Delhi   101      2    30
3    Ishan    26        103  Trevor      30   PK     Mohali  103      3    10
4    Axar     30                                                      4    20

SELECT MIN(P.age)
FROM player P
WHERE P.pid IN (
    SELECT M.pid
    FROM team T, coach C, members M
    WHERE C.cname = 'Mark'
          AND T.cid = C.cid
          AND M.tid = T.tid
    )

The value returned by the given SQL query is ______. (Answer in integer)

Consider the following relational schema:

Students (rollno: integer, name: string, age: integer, cgpa: real)
Courses (courseno: integer, cname: string, credits: integer)
Enrolled (rollno: integer, courseno: integer, grade: string)

Which of the following options is/are correct SQL query/queries to retrieve the names of the students enrolled in course number (i.e., courseno) 1470?

Consider the following table named Student in a relational database. The primary key of this table is rollNum.

Student
rollNum  name    gender  marks
1        Naman   M       62
2        Aliya   F       70
3        Aliya   F       80
4        James   M       82
5        Swati   F       65

The SQL query below is executed on this database.

SELECT *
FROM Student
WHERE gender = 'F' AND
      marks > 65;

The number of rows returned by the query is __________

Consider the relational database with the following four schemas and their respective instances (see figure). Student(sNo, sName, dNo) Dept(dNo, dName) Course(cNo, cName, dNo) Register(sNo, cNo) SQL Query:

SELECT * FROM Student AS S WHERE NOT EXIST
  (SELECT cNo FROM Course WHERE dNo = "D01"
     EXCEPT
   SELECT cNo FROM Register WHERE sNo = S.sNo)

The number of rows returned by the above SQL query is __________

The relation scheme given below is used to store information about the employees of a company, where empId is the key and deptId indicates the department to which the employee is assigned. Each employee is assigned to exactly one department. emp(empId, name, gender, salary, deptId) Consider the following SQL query:

select deptId, count(*)
from emp
where gender = "female" and salary > (select avg(salary) from emp)
group by deptId;

The above query gives, for each department in the company, the number of female employees whose salary is greater than the average salary of

Consider a relational database containing the following schemas (see figure: Catalogue, Suppliers, Parts). The primary key of each table is indicated by underlining the constituent fields.

SELECT s.sno, s.sname
FROM   Suppliers s, Catalogue c
WHERE  s.sno = c.sno AND
       cost > (SELECT AVG (cost)
               FROM Catalogue
               WHERE pno = 'P4'
               GROUP BY pno);

The number of rows returned by the above SQL query is

A relational database contains two tables Student and Performance as shown below: The primary key of the Student table is Roll_no. For the Performance table, the columns Roll_no. and Subject_code together form the primary key. Consider the SQL query given below:

SELECT S.Student_name, sum(P.Marks)
FROM Student S, Performance P
WHERE P.Marks > 84
GROUP BY S.Student_name;

The number of rows returned by the above SQL query is _____.

Consider the following two tables and four queries in SQL. Book (isbn, bname), Stock (isbn, copies)

Query 1:  SELECT B.isbn, S.copies
          FROM Book B INNER JOIN Stock S
          ON B.isbn = S.isbn;

Query 2:  SELECT B.isbn, S.copies
          FROM Book B LEFT OUTER JOIN Stock S
          ON B.isbn = S.isbn;

Query 3:  SELECT B.isbn, S.copies
          FROM Book B RIGHT OUTER JOIN Stock S
          ON B.isbn = S.isbn;

Query 4:  SELECT B.isbn, S.copies
          FROM Book B FULL OUTER JOIN Stock S
          ON B.isbn = S.isbn;

Which one of the queries above is certain to have an output that is a superset of the outputs of the other three queries?

Consider a database that has the relation schema EMP (EmpId, EmpName, and DeptName). An instance of the schema EMP and a SQL query on it are given below.

EMP
EmpId  EmpName  DeptName
1      XYA      AA
2      XYB      AA
3      XYC      AA
4      XYD      AA
5      XYE      AB
6      XYF      AB
7      XYG      AB
8      XYH      AC
9      XYI      AC
10     XYJ      AC
11     XYK      AD
12     XYL      AD
13     XYM      AE

SELECT AVG(EC.Num)
FROM EC
WHERE (DeptName, Num) IN
      (SELECT DeptName, COUNT(EmpId) AS
                 EC(DeptName, Num)
       FROM EMP
       GROUP BY DeptName)

The output of executing the SQL query is ________.

Consider the following database table named *top_scorer*.

top_scorer
player       country     goals
Klose        Germany     16
Ronaldo      Brazil      15
G Müller     Germany     14
Fontaine     France      13
Pelé         Brazil      12
Klinsmann    Germany     11
Kocsis       Hungary     11
Batistuta    Argentina   10
Cubillas     Peru        10
Lato         Poland      10
Lineker      England     10
T Müller     Germany     10
Rahn         Germany     10

Consider the following SQL query:

SELECT ta.player FROM top_scorer AS ta
WHERE ta.goals >ALL (SELECT tb.goals
                     FROM top_scorer AS tb
                     WHERE tb.country = 'Spain')
AND ta.goals >ANY (SELECT tc.goals
                     FROM top_scorer AS tc
                     WHERE tc.country = 'Germany')

The number of tuples returned by the above SQL query is ________.

Consider the following database table named water_schemes:

water_schemes
scheme_no   district_name   capacity
1           Ajmer           20
1           Bikaner         10
2           Bikaner         10
3           Bikaner         20
1           Churu           10
2           Churu           20
1           Dungargarh      10

The number of tuples returned by the following SQL query is ________.

with total(name, capacity) as
    select district_name, sum(capacity)
    from water_schemes
    group by district_name
with total_avg(capacity) as
    select avg(capacity)
    from total
select name
    from total, total_avg
    where total.capacity ≥ total_avg.capacity

SELECT operation in SQL is equivalent to

Consider the following relations: Consider the following SQL query.

SELECT S.Student_Name, sum(P.Marks)
FROM Student S, Performance P
WHERE S.Roll_No = P.Roll_No
GROUP BY S.Student_Name

The number of rows that will be returned by the SQL query is _____.

Consider the following relation Cinema(theater, address, capacity) Which of the following options will be needed at the end of the SQL query

SELECT P1.address
FROM Cinema P1

such that it always finds the addresses of theaters with maximum capacity?

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?

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?

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 ($\underline{\text{empId}}$, empName, empDept) customer ($\underline{\text{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
                  FROM customer C
                  WHERE C.salesRepId = E.empId
                  AND C.rating <> 'GOOD');

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) $\Pi_{sname}(\sigma_{courseno=107 \land percent>90}(\text{Registration} \bowtie \text{Students}))$ (III) $\{T \mid \exists S \in \text{Students}, \exists R \in \text{Registration}\ (S.rollno = R.rollno \land R.courseno = 107 \land R.percent > 90 \land T.sname = S.sname)\}$ (IV) $\{\langle S_N \rangle \mid \exists S_R \exists R_P\ (\langle S_R, S_N \rangle \in \text{Students} \land \langle S_R, 107, R_P \rangle \in \text{Registration} \land R_P > 90)\}$

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 need to appear in the SELECT clause