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 a GROUP BY clauseR: 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

1. P and R
2. P and S
3. Q and R
4. Q and S

Option 3 : Q and R

Basic SQL Queries MCQ Question 1 Detailed Solution

The correct answer is option 3

EXPLANATION:

GROUP BY clause:

• The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
• The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)

Example:

SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY

Therefore all attributes used in the GROUP BY clause must appear in the SELECT clause

HAVING clause:

• HAVING clause is used to specify a search condition for a group or an aggregate.
• Having is used in a GROUP BY clause.
• If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause.

Syntax:

SELECT column1, column2
FROM table_name
WHERE conditions
GROUP BY column1, column2
HAVING conditions
ORDER BY column1, column2;

Example:
SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY
HAVING COUNT(*)>2;
Therefore an SQL query can contain a HAVING clause only if it has a GROUP BY clause.
Confusion Points
The answer is as per standard SQL

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 SYJ AC 11 XYK AD 12 XYL AD 13 XYM AE SELECT AVG(EC.Num FROM EC WHERE(DeptName, Num) IN           (SELECTED DeptName, COUNT(EmpId)AS                                                     EC(DeptName, Num)            FROM EMP            GROUP BY DeptName) The output of executing the SQL query is _________.

Basic SQL Queries MCQ Question 2 Detailed Solution

Concept:

Count the number of EmpIDs corresponding to a particular department and then write as attribute value of Num in new relational schema EC.

Explanation:

SQL query results

 EC DeptName Num AA 4 AB 3 AC 3 AD 2 AE 1

AVG(EC. Num) will find out the average of Num values in the returned query.,

$$Average = \;\frac{{4 + 3 + 3 + 2 + 1}}{5} = 2.6$$

_______ SQL command changes one or more fields in a record.

1. LOOK-UP
2. INSERT
3. MODIFY
4. CHANGE

Option 3 : MODIFY

Basic SQL Queries MCQ Question 3 Detailed Solution

The correct answer is "option 3".

CONCEPT:

SQL, stands for Structured Query Language, is a query language used for maintaining data in Relational DBMS(RDBMS).

Insertion, deletion & modification of data can be done by using various SQL commands.

In SQL, each row is known as "tuple" or "records" and the column is known as "field" or "attribute".

EXPLANATION:

INSERT command in SQL is used to insert one or more records or tuples in the table.

MODIFY command is used to update the table attribute information.

Hence, the MODIFY command is used to change one or more fields in a record.

Database table by name Overtime_allowance is given below: Employee Department OT_allowance RAMA Mechanical 5000 GOPI Electrical 2000 SINDHU Computer 4000 MAHESH Civil 1500 What is the output of the following SQL query?select count(*) from((select Employee, Department from Overtime_allowance) as Snatural join (select Department, OT_allowance from Overtime_allowance)as T);

1. 16
2. 4
3. 8
4. None of the above

Option 2 : 4

Basic SQL Queries MCQ Question 4 Detailed Solution

Natural join will return the table with four tuples.

 Department OT_allowance Mechanical 5000 Electrical 2000 Computer 4000 Civil 1500

SQL command used to modify attribute values of one or more selected type is:

1. MODIFY
2. UPDATE
3. INSERT
4. ALTER

Option 2 : UPDATE

Basic SQL Queries MCQ Question 5 Detailed Solution

Concepts

SQL command used to modify attribute values of one or more selected types is UPDATE.

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.

Syntax:

The UPDATE statement is used to modify the existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Therefore Option 2 is correct

ALTER TABLE Modify Column command. For Oracle and MySQL, the SQL syntax for ALTER TABLE Modify Column is,

ALTER TABLE "table_name" MODIFY "column_name" "New Data Type";

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

_______ symbol is used to see every column of a table.

1. /
2. _ _
3. *
4. !

Option 3 : *

Basic SQL Queries MCQ Question 6 Detailed Solution

* symbol is used to see every column of a table.

For Example, Let us consider a table Table1

 ID NAME AGE ADDRESS SALARY 1 Akash 24 Bijnor 12000 2 sema 23 Delhi 15000 3 diya 33 Banglore 33000 4 Badal 29 Odisha 40000

If you want to fetch only some specific columns from the table, then we can use this query,

Select ID,NAME,AGE from Table1;    // Syntax is Select (column_name1, coloumn_name2.....coloum_name) from table_name;

 ID NAME AGE 1 Akash 24 2 sema 23 3 diya 33 4 Badal 29

If you want to fetch all the fields of the Table1 table, then you should use the following query

Select * from Table1;    // Syntax is Select * from table_name;

 ID NAME AGE ADDRESS SALARY 1 Akash 24 Bijnor 12000 2 sema 23 Delhi 15000 3 diya 33 Banglore 33000 4 Badal 29 Odisha 40000

Therefore Option 3 is correct

Basic SQL Queries MCQ Question 7

Comprehension:

Consider the following relations A, B and C:

 A Id Name Age 12 Arun 60 15 Shreya 24 99 Rohit 11

 B Id Name Age 15 Shreya 24 25 Hari 40 98 Rohit 20 99 Rohit 11

 C Id Phone Area 10 2200 02 99 2100 01

How many tuples does the result of the following SQL query contain?SELECT A.IdFROM AWHERE A.Age > ALL (SELECT B.Age                                       FROM B WHERE B.Name = ‘Arun’)

1. 4
2. 3
3. 0
4. 1

Option 2 : 3

Basic SQL Queries MCQ Question 7 Detailed Solution

The correct answer is option 2.

Key Points

The ALL keyword specifies that the search condition is TRUE if the comparison is TRUE for every value that the subquery returns. If the subquery returns no value, the condition is TRUE.

There is no Name='Arun' in the B table. So it returns no value then the condition become is TRUE for every row. So Finally it prints the ID of the A table.

 Id 12 15 99

Hence the correct answer is 3.

Basic SQL Queries MCQ Question 8

Comprehension:

Comprehension: Consider the following table structures related to a university for given questions.

EMPLOYEE

 NAME VARCHAR (30) NOT NULL, EID VARCHAR (10) NOT NULL, DEPTNO INT (5) NOT NULL, HODEID VARCHAR (10), SALARY INT (10),

PRIMARY KEY (EID),

FOREIGN KEY (HODEID) REFRENCES EMPLOYEE (EID),

FOREIGN KEY (DEPTNO) REFRENCES DEPARTMENT (DID);

DEPARTMENT

 DID INT (5) NOT NULL, DNAME VARCHAR (30) NOT NULL, HODID VARCHAR (10) NOT NULL, HODNAME VARCHAR (30),

PRIMARY KEY (DID),

UNIQUE (DNAME),

FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID),

PROJECT WORE:

 EMPID VARCHAR (10) NOT NULL, PROJNO INT (5) NOT NULL, PROJECTLOC VARCHAR (30) NOT NULL,

PRIMARY KEY (EMPID, PROJNQ),

FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),

In reference to the above given table structures, which of the following query/queries will drop the ‘SALARY’ column from 'EMPLOYEE' table ?(A) ALTER TABLE EMPLOYEE DROP SALARY CASCADE;(B) ALTER TABLE EMPLOYEE DROP SALARY RESTRICT;(C) ALTER EMPLOYEE DROP SALARY:Choose the correct answer from the options given below:

1. (A) and (B) only
2. (A) and (C) only
3. (B) and (C) only
4. (A) only

Option 1 : (A) and (B) only

Basic SQL Queries MCQ Question 8 Detailed Solution

The correct answer is option 1.

Key Points

Alter table Syntax:

ALTER TABLE table-Name
DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ]

•  The keyword COLUMN, CASCADE, RESTRICT are optional. The default is CASCADE. If you use the RESTRICT option, the column drop will be rejected if it would invalidate a dependent schema object. If CASCADE is defined, the column drop should also remove any other schema objects that have become invalid.
• Since the salary field is not present in any other table, CASCADE | RESTRICT has no impact.
• (C) ALTER EMPLOYEE DROP SALARY is incorrect syntax.

∴ Hence the correct answer is  (A) and (B) only.

Basic SQL Queries MCQ Question 9

Comprehension:

Comprehension: Consider the following table structures related to a university for given questions.

EMPLOYEE

 NAME VARCHAR (30) NOT NULL, EID VARCHAR (10) NOT NULL, DEPTNO INT (5) NOT NULL, HODEID VARCHAR (10), SALARY INT (10),

PRIMARY KEY (EID),

FOREIGN KEY (HODEID) REFRENCES EMPLOYEE (EID),

FOREIGN KEY (DEPTNO) REFRENCES DEPARTMENT (DID);

DEPARTMENT

 DID INT (5) NOT NULL, DNAME VARCHAR (30) NOT NULL, HODID VARCHAR (10) NOT NULL, HODNAME VARCHAR (30),

PRIMARY KEY (DID),

UNIQUE (DNAME),

FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID),

PROJECT WORE:

 EMPID VARCHAR (10) NOT NULL, PROJNO INT (5) NOT NULL, PROJECTLOC VARCHAR (30) NOT NULL,

PRIMARY KEY (EMPID, PROJNQ),

FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),

Which of the following query/queries return the employee ID and name of employees whose salary is greater than the salary of all employees in department number 20 of university. Order result by employee ID (refer table structures given above).(A)SELECT EID, NAMEFROM EMPLOYEEWHERE SALARY > (SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20) ORDER BY EID:(B)SELECT EID, NAMEFROM EMPLOYEEWHERE SALARY > (SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20);(C)SELECT EID, NAMEFROM EMPLOYEEWHERE SALARY > ALL(SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20)ORDER BY EIDChoose the correct answer from the options given below:

1. (A) and (B) only
2. (A) and (C) only
3. (B) only
4. (C) only

Option 4 : (C) only

Basic SQL Queries MCQ Question 9 Detailed Solution

The correct answer is option 4.

Key Points

SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20

The above inner query will be written the salary of an employee whose department number is 20. This will match with, the salary of the employee > salary of at least one employee.

Query:

SELECT EID, NAME

FROM EMPLOYEE

WHERE SALARY > ALL(SELECT SALARY FROM EMPLOYEE WHERE DEPTNO=20)

ORDER BY EID

The query returns the employee ID and name of employees whose salary is greater than the salary of all employees in department number 20 of the university. Order result by employee ID

Basic SQL Queries MCQ Question 10

Comprehension:

Comprehension: Consider the following table structures related to a university for given questions.

EMPLOYEE

 NAME VARCHAR (30) NOT NULL, EID VARCHAR (10) NOT NULL, DEPTNO INT (5) NOT NULL, HODEID VARCHAR (10), SALARY INT (10),

PRIMARY KEY (EID),

FOREIGN KEY (HODEID) REFRENCES EMPLOYEE (EID),

FOREIGN KEY (DEPTNO) REFRENCES DEPARTMENT (DID);

DEPARTMENT

 DID INT (5) NOT NULL, DNAME VARCHAR (30) NOT NULL, HODID VARCHAR (10) NOT NULL, HODNAME VARCHAR (30),

PRIMARY KEY (DID),

UNIQUE (DNAME),

FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID),

PROJECT WORE:

 EMPID VARCHAR (10) NOT NULL, PROJNO INT (5) NOT NULL, PROJECTLOC VARCHAR (30) NOT NULL,

PRIMARY KEY (EMPID, PROJNQ),

FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),

Refer table, structures given above, University decided to give all employees in the ‘SCIENCE’ department a 20% rise in salary. Which of the following query/queries will compute the above results?(A)UPDATE EMPLOYEESET SALARY = SALARY*1.20WHERE DEPT NO. IN (SELECT DID FROM DEPARTMENT WHERE DNAME='SCIENCE');(B)UPDATE TABLE EMPLOYEESET SALARY = SALARY*1.20WHERE DNAME='SCIENCE’;(C)ALTER TABLE EMPLOYEESET SALARY=SALARY*1.20WHERE DEPTNO. IN (SELECT DNAME FROM DEPARTMENT WHERE DNAME ='SCIENCE’)Choose the correct answer from the options given below:

1. (A) and (B) only
2. (A) only
3. (B) and (C) only
4. (C) only

Option 2 : (A) only

Basic SQL Queries MCQ Question 10 Detailed Solution

The correct answer is option 1.

Key Points

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Option A:

UPDATE EMPLOYEE

SET SALARY = SALARY*1.20

WHERE DEPT NO. IN (SELECT DID FROM DEPARTMENT WHERE DNAME='SCIENCE');

Option B:

UPDATE TABLE EMPLOYEE SET SALARY = SALARY*1.20 WHERE DNAME='SCIENCE’;

Here, DNAME is not an attribute of an employee table it's an incorrect query.

Option C:

ALTER TABLE EMPLOYEE

SET SALARY=SALARY*1.20

WHERE DEPTNO. IN (SELECT DNAME FROM DEPARTMENT WHERE DNAME ='SCIENCE’)

Here, the ALTER TABLE statement is used to add, delete, or modify columns in an existing table and used to add and drop various constraints on an existing table. Alter table is mainly used for changing table structure or constraints. Here DNAME is not an attribute of an employee table it's an incorrect query.

∴ Hence the correct answer is (A).

Basic SQL Queries MCQ Question 11

Comprehension:

Comprehension: Consider the following table structures related to a university for given questions.

EMPLOYEE

 NAME VARCHAR (30) NOT NULL, EID VARCHAR (10) NOT NULL, DEPTNO INT (5) NOT NULL, HODEID VARCHAR (10), SALARY INT (10),

PRIMARY KEY (EID),

FOREIGN KEY (HODEID) REFRENCES EMPLOYEE (EID),

FOREIGN KEY (DEPTNO) REFRENCES DEPARTMENT (DID);

DEPARTMENT

 DID INT (5) NOT NULL, DNAME VARCHAR (30) NOT NULL, HODID VARCHAR (10) NOT NULL, HODNAME VARCHAR (30),

PRIMARY KEY (DID),

UNIQUE (DNAME),

FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID),

PROJECT WORE:

 EMPID VARCHAR (10) NOT NULL, PROJNO INT (5) NOT NULL, PROJECTLOC VARCHAR (30) NOT NULL,

PRIMARY KEY (EMPID, PROJNQ),

FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),

On the basis of above given table structures, retrieve the distinct employee ID (EMPID) of all employees of university who are working on project No.20, 30 and 40.

1. SELECT EMPID

FROM PROJECTWORK

WHERE PROJNO=(20, 30, 40);

2. SELECT EMPID

FROM PROJECTWOREK

WHERE PROJNO IN (20, 30, 40);

3. SELECT DISTINCT EMPID

FROM PROJECTWORK

WHERE PROJNO IN (20, 30, 40);

4. SELECT DISTINCT EMPID

FROM PROJECTWORK

WHERE PROJNO=20, 30, 40;

Option 3 :

SELECT DISTINCT EMPID

FROM PROJECTWORK

WHERE PROJNO IN (20, 30, 40);

Basic SQL Queries MCQ Question 11 Detailed Solution

The correct answer is option 3.

Key Points

Since the primary key is a combination of two keys (EMPID, PROJNQ) so duplicate values may occur in EMID and PROJNQ. So distinct keys are required to eliminate duplicates in the combined table. And IN is a set operator it matches all table values to the set values.

∴ Hence the correct answer is the below query.

SELECT DISTINCT EMPID

FROM PROJECTWORK

WHERE PROJNO IN (20, 30, 40);

Basic SQL Queries MCQ Question 12

Comprehension:

Comprehension: Consider the following table structures related to a university for given questions.

EMPLOYEE

 NAME VARCHAR (30) NOT NULL, EID VARCHAR (10) NOT NULL, DEPTNO INT (5) NOT NULL, HODEID VARCHAR (10), SALARY INT (10),

PRIMARY KEY (EID),

FOREIGN KEY (HODEID) REFRENCES EMPLOYEE (EID),

FOREIGN KEY (DEPTNO) REFRENCES DEPARTMENT (DID);

DEPARTMENT

 DID INT (5) NOT NULL, DNAME VARCHAR (30) NOT NULL, HODID VARCHAR (10) NOT NULL, HODNAME VARCHAR (30),

PRIMARY KEY (DID),

UNIQUE (DNAME),

FOREIGN KEY (HODID) REFERENCES EMPLOYEE (EID),

PROJECT WORE:

 EMPID VARCHAR (10) NOT NULL, PROJNO INT (5) NOT NULL, PROJECTLOC VARCHAR (30) NOT NULL,

PRIMARY KEY (EMPID, PROJNQ),

FOREIGN KEY (EMPID) REFERENCES EMPLOYEE (EID),

Given below are two statements to find the sum of salaries of all employees of the English department as well as the maximum. minimum and average salary in the English department.STATEMENT I: SELECT SUM (SALARY). MAX (SALARY), MIN (SALARY), AVG (SALARY) FROM EMPLOYEE, DEPARTMENTWHERE DEPTNO=DID AND DNAME='ENGLISH';STATEMENT II: SELECT SUM (SALARY), MAX (SALARY), MIN (SALARY), AVG (SALARY) FROM EMPLOYEE, DEPARTMENTWHERE DNAME='ENGLISH';In the light of the above statements, choose the correct answer from the options given below

1. Both Statement I and Statement II are true
2. Both Statement I and Statement II are false
3. Statement I is correct but Statement II is false
4. Statement I is incorrect but Statement II is true

Option 3 : Statement I is correct but Statement II is false

Basic SQL Queries MCQ Question 12 Detailed Solution

The correct answer is option 3.

Key Points

Two table tables (join condition) must be matched so that records can be linked uniquely.

Query:

SUM (SALARY). MAX (SALARY), MIN (SALARY), AVG (SALARY) FROM EMPLOYEE, DEPARTMENT WHERE DEPTNO=DID AND DNAME='ENGLISH';

Without DEPTNO=DID condition as in statement II, there will be the cartesian product of two tables (EMPLOYEE, DEPARTMENT) and it will appear many duplicate rows with DNAME='ENGLISH'.

Hence Statement I is correct but Statement II is false.

Given two relations R1(A, B) and R2(C, D), the result of following querySelect distinct A, Bfrom R1, R2is guaranteed to be same as R1 provided one of the following condition is satisfied.

1. R1 has no duplicates and R2 is empty.
2. R1 has no duplicates and R2 is non-empty.
3. Both R1 and R2 have no duplicates.
4. R2 has no duplicates and R1 is non - empty

Option 1 : R1 has no duplicates and R2 is empty.

Basic SQL Queries MCQ Question 13 Detailed Solution

CONCEPT:

• SQL Relations are MULTISET, not SET. So, R1 or R2 can have duplicated.
• If S is empty, then R1 × R2 = empty and hence it will not equal R
• If R1 has duplicates, in that case, due to distinct keywords those duplicates will be eliminated in the final result and SQL query will not be equal to R1. So, R1 cannot have duplicates.

EXPLANATION:

Option 1: R1 has no duplicates and R2 is non-empty

This is true:

Option 2: R and S has same number of tuples

Since R1 can be duplicate it is false

Option 3: R1 and R2 have no duplicates

Since S can be empty ∴ R × S = 0 it is false

Option 4: R2 has no duplicates and R1 is non-empty

Since R can be duplicate it is false

Example:

R1(x, y)

 A B 1 2 1 2

R2(A, B)

 y z 3 4 5 2

R1 × R2:

 A B C D 1 2 3 4 1 2 5 2 1 2 3 4 1 2 5 2

SELECT DISTINCT w, x from R, S

Output:

 w x 1 2

This proves option 2, 3 and 4 an incorrect statement

In SQL, _______ is an Aggregate function.

1. SELECT
2. CREATE
3. AVG
4. MODIFY

Option 3 : AVG

Basic SQL Queries MCQ Question 14 Detailed Solution

Concept:

Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses.

Key Points

 Function Description COUNT COUNT returns the number of rows returned by the query AVG AVG returns average value of expression MIN MIN returns minimum value of expression SUM SUM returns the sum of values of expression

In SQL, AVG is an aggregate function

With the following syntaxINSERT INTO table [(column [, column...])]VALUES (value [, value...]);you can:

1. Insert one row at a time
2. Insert multiple rows at a time
3. Insert one column at a time
4. Insert multiple columns at a time

Option 1 : Insert one row at a time

Basic SQL Queries MCQ Question 15 Detailed Solution

Concept:

The INSERT INTO statement is used to insert new records in a table.

It inserts one row at a time

Example

SQL command:

INSERT INTO PLACEMENT [Company, IIT, Hired]

VALUES (Testbook, Kanpur, 14);

Output:

What result set is returned from the following SQL query?A. Select customer_name, telephoneFrom customersWhere city in ('Jaipur', Delhi', Agra)

1. The customer_name and telephone of all customers.
2. The customer_name and telephone of all customers Living in Jaipur, Delhi and Agra.
3. The customer_name and telephone of all customers Living in either Jaipur Delhi or Agra.
4. The customer_name and telephone of all customers who are not living in Jaipur, Delhi or Agra.

Option 3 : The customer_name and telephone of all customers Living in either Jaipur Delhi or Agra.

Basic SQL Queries MCQ Question 16 Detailed Solution

Concept:

The SQL IN condition allows to easily test if an expression matches any value in a list of values. It reduces the need for multiple OR conditions in statements.

Explanation:

Given SQL query :

Select customer_name, telephone

From customers

Where city IN ('Jaipur', Delhi', 'Agra')

As the operator used is IN so, it will result in the customer name and telephone of all customers living in the city either Jaipur, Delhi or Agra.

A relational schema for a train reservation database is given belowPassenger (pid, pname, age)Re servation (pid, cass, tid)Table : Passenger pid ‘pname Age 0 ‘Sachin’ 65 1 ‘Rahul’ 66 2 ‘Sourav’ 67 3 ‘Anil’ 69 Table : Reservation pid class tid 0 ‘AC’ 8200 1 ‘AC’ 8201 2 ‘SC’ 8201 5 ‘AC’ 8203 1 'SC' 8204 3 'AC' 8202 What pids are returned by the following SQL query for the above instance of the tables?SELECT pidFROM ReservationWHERE class 'AC ' AND               EXISTS (SELECT *                           FROM Passenger                            WHERE age > 65 AND                             Passenger.pid = Reservation.pid)

1. 1, 0
2. 1, 2
3. 1, 3
4. 1, 5

Option 3 : 1, 3

Basic SQL Queries MCQ Question 17 Detailed Solution

Key Points

Pid=0 fails to meet condition age>65. pid=2 fails to meet condition class='AC'

Pid=1(tid=8201) and pid=3 (tid=8202) meet conditions age>65 and class ='AC', also join condition passenger.pid=reservation.pid.

Hence the correct answer is 1, 3.

Basic SQL Queries MCQ Question 18

Comprehension:

Consider the following relations A, B and C:

 A Id Name Age 12 Arun 60 15 Shreya 24 99 Rohit 11

 B Id Name Age 15 Shreya 24 25 Hari 40 98 Rohit 20 99 Rohit 11

 C Id Phone Area 10 2200 02 99 2100 01

How many tuples does the result of the following relational algebra expression contain? Assume that the schema of A∪B is the same as that of A.(A∪B) ⋈ A.Id > 40 ∨ C.Id < 15 C

1. 7
2. 4
3. 5
4. 9

Option 1 : 7

Basic SQL Queries MCQ Question 18 Detailed Solution

The correct answer is option 1.

Key Points

Apply first cross-product then apply filter. Cross product yields 10 rows, then you play filter A.ID>40 or C.ID<15 produces 7 rows.

AUB= A.Id

12,15,25,98,99

|x| is cross product followed selection and projection.

 A.Id     B.Id  X gives(cross product) 10rows Condition A.Id B.Id A.Id>40 V C.ID<15 5 rows  2 rows           the result contains 7 rows 12 10 True 12 99 False 15 10 True 15 99 False 25 10 True 25 99 False 98 10 True 98 99 True 99 10 True 99 99 True

Hence the correct answer is 7.

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.snameFROM Students as S, Registration as RWHERE R.rollno=S.rollno AND R.courseno=107 AND R.percent >90(II) Πsname(σcourseno=107 percent>90 (Registration Students))(III) {T | tudents, Registration ( S.rollno=R.rollnoR.courseno=107 R.percent>90 T.sname=S.sname)} (IV) {N> | ∃SR∃RP ( R, SN> ∈ Students ∧ R, 107, RP>∈ Registration ∧ RP>90)}

1. I, II, III and IV
2. I, II and III only
3. I, II and IV only
4. II, III and IV only

Option 1 : I, II, III and IV

Basic SQL Queries MCQ Question 19 Detailed Solution

The correct answer is option 1.

Key Points

Structured Query Language, Relational algebra, Tuple relational calculus, and domain relational calculus all are representing the same. i.e, all these expressions representing finding 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

The above query is Structured Query Language, It takes two tables are student and registration and cross-product the tuples based on Rollno attribute those tuple course number should be 107 and percentage more than 90%.

(II) Πsnamecourseno = 107 percent>90 (Registration Students))

The above query is Relational algebra. It first performs a natural join of Students and Registration and natural join implicitly join on the basis of the common attribute, which here is roll no. Select operation( sigma) keeps only those rows where the student is registered for course no 107, and the percentage is more than 90%. And then the projection operation (pi) projects only distinct student names from the set.

(III) {T | tudents, Registration ( S.rollno=R.rollno R.courseno=107 R.percent>90 T.sname=S.sname)}

The above query is Tuple relational calculus. It is not a procedural language. It just represents a declarative mathematical expression. It finding the distinct names of all students who score more than 90 % in the course numbered 107.

(IV) {N> | ∃SR∃RP ( R, SN> ∈ Students ∧ R, 107, RP>∈ Registration ∧ RP>90)}​

The above query is domain relational calculus. It is not a procedural language. It just represents a declarative mathematical expression. And SN is a domain variable. It finding the distinct names of all students who score more than 90 % in the course numbered 107.
Hence the correct answer is I, II, III, and IV.

When retrieving data in a particular table in PostgreSQL, we use the ____________ statement.

1. \dt
2. ORDER BY
3. SELECT FROM
4. \i

Option 3 : SELECT FROM

Basic SQL Queries MCQ Question 20 Detailed Solution

Concept

Basic form of the SELECT statement that retrieves data from a single table.

Syntax

SELECT select_list FROM table_name;
• First, specify a select list that can be a column or a list of columns in a table from which you want to retrieve data. If you specify a list of columns, you need to place a comma (,) between two columns to separate them. If you want to select data from all the columns of the table, you can use an asterisk (*) shorthand instead of specifying all the column names. The select list may also contain expressions or literal values.
• Second, specify the name of the table from which you want to query data after the FROM keyword.

The FROM clause is optional. If you do not query data from any table, you can omit the FROM clause in the SELECT statement.
{"extraction_info": {"found_math": true, "script_math_tex": 0, "script_math_asciimath": 0, "math_annotations": 0, "math_alttext": 0, "mathml": 0, "mathjax_tag": 0, "mathjax_inline_tex": 0, "mathjax_display_tex": 1, "mathjax_asciimath": 0, "img_math": 0, "codecogs_latex": 0, "wp_latex": 0, "mimetex.cgi": 0, "/images/math/codecogs": 0, "mathtex.cgi": 0, "katex": 0, "math-container": 0, "wp-katex-eq": 0, "align": 0, "equation": 0, "x-ck12": 0, "texerror": 0, "math_score": 0.15974442660808563, "perplexity": 12260.71013335013}, "config": {"markdown_headings": false, "markdown_code": true, "boilerplate_config": {"ratio_threshold": 0.18, "absolute_threshold": 10, "end_threshold": 15, "enable": true}, "remove_buttons": true, "remove_image_figures": true, "remove_link_clusters": true, "table_config": {"min_rows": 2, "min_cols": 3, "format": "plain"}, "remove_chinese": true, "remove_edit_buttons": true, "extract_latex": true}, "warc_path": "s3://commoncrawl/crawl-data/CC-MAIN-2022-05/segments/1642320303868.98/warc/CC-MAIN-20220122164421-20220122194421-00420.warc.gz"}