Explore topic-wise MCQs in Structured Query Language (SQL).

This section includes 82 Mcqs, each offering curated multiple-choice questions to sharpen your Structured Query Language (SQL) knowledge and support exam preparation. Choose a topic below to get started.

51.

Consider the schemaSailors(sid, sname, rating, age) with the following dataSidSnameRatingage22Dustin74529Borg13331Pathy85532Robert82558Raghu101764Herald73571Vishnu101674King93585Archer32684Bob36496Flinch317For the querySELECT S.rating, AVG(S.age) AS avgage FROM Sailors SWhere S.age >= 18GROUP BY S.ratingHAVING 1 < (SELECT COUNT(*) FROM Sailors S2 where S.rating = S2.rating)The number of rows returned is

A. 6
B. 5
C. 4
D. 3
Answer» E.
Discussion
52.

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?

A. S1 is TRUE and S2 is FALSE
B. BOTH S1 and S2 are TRUE
C. S1 is FALSE and S2 is TRUE
D. BOTH S1 and S2 are FALSE
Answer» E.
Discussion
53.

Database table by name Overtime_allowance is given below:EmployeeDepartmentOT_allowanceRAMAMechanical5000GOPIElectrical2000SINDHUComputer4000MAHESHCivil1500 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);

A. 16
B. 4
C. 8
D. None of the above
Answer» C. 8
Discussion
54.

Consider the relation Emp-Dept with SSn as keyEnameSSnEAddrDept.NoDept.NameDept.ManagerSSn Which of the following is (are) invalid operation(s)?a) Inserting an employee without name and addressb) Inserting an employee with only SSnc) Inserting a department with no employeed) Inserting an employee without SSn

A. a
B. d
C. c and d
D. b and c
Answer» C. c and d
Discussion
55.

(Free Practice With Testbook Mock TestsAE & JE Electrical Engg. Exams Previous Year Papers Mock Test (80+ Papers)132 Total Tests | 8 Free TestsUPRVUNL JE Mechanical 2021 Mock Test36 Total Tests | 2 Free TestsOptions: more than the maximum value in the subquery less than the minimum value in the subquery is equivalent to IN none of the options Correct Answer: Option 2 (Solution Below)This question was previously asked inNIELIT Scientific Assistant A Official Paper 2020Attempt Online ››Solution: Download Question With Solution PDF ››The ALL operator returns true if all of the subquery values meet the conditionSyntaxSELECT column_name(s)FROM table_nameWHERE column_name operator ALL(SELECT column_name FROM table_name WHERE condition);The operator we can use must be a standard comparison operator (=, <>, !=, >, >=, <, or <=).Therefore the Option 2 will be correct i.e, Less than the minimum value in the subqueryDownload Question With Solution PDF ››

A. more than the maximum value in the subquery
B. less than the minimum value in the subquery
C. is equivalent to IN
D. none of the options
Answer» C. is equivalent to IN
Discussion
56.

Consider the following relation schemaSailors(sid, sname, rating, age)Reserve(sid, bid, day)Boats(bid, bname, color)What is the equivalent of following relational algebra query in SQL query.πsname((σcolor= ‘red’Boats) ⨝ Reserves ⨝ Sailors)

A. SELECT S.sname, S.ratingFROM Sailors S, Reserves RWHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’
B. SELECT S.snameFROM Sailors S, Reserves R, Boats BWHERE S.bid = B.bid AND B.color = ‘red’
C. SELECT S.snameFROM Sailors S, Reserves R, Boats BWHERE S.sid = R.sid AND B.color = ‘red’
D. SELECT S.snameFROM Sailors S, Reserves R, Boats BWHERE S.sid = R.sid AND R.bid = B.bid AND B.color = ‘red’
Answer» E.
Discussion
57.

Consider the database table "Persons" having person_ID as the primary key.Person_IDNameAge1000Rajan40-Jatin-451001Rajesh35What are the constraints violated by the above table?

A. Referential integrity only
B. Referential and domain integrity
C. Entity and domain integrities
D. Relationship integrity
Answer» D. Relationship integrity
Discussion
58.

Consider the relational schema given below, where eId of the relation dependent is a foreignkey referring to empId of the relation employee. Assume that every employee has at least oneassociated dependent in the dependent relation.Employee (empId, empName, empAge)Dependent(depId, eId, depName, depAge)Consider the following relational algebra query:ΠempId(employee)-ΠempId (employee⋈(empId = eID)∧(empAge ≤ depAge)dependent)The above query evaluates to the set of empIds of employees whose age is greater than that of

A. some dependent.
B. all dependents
C. some of his/her dependents
D. all of his/her dependents
Answer» E.
Discussion
59.

Let R = (A, B, C, D, E) having following FDs. F = {A → BC, CD → E, B → D, E → A}Which of the following is not a Candidate key?

A. A
B. B
C. E
D. BC
Answer» C. E
Discussion
60.

________ Command is used to enable, disable, modify, or drop a constraint in SQL.

A. MODIFY Table
B. ADD Column
C. ALTER Table
D. DEFINE Table
Answer» D. DEFINE Table
Discussion
61.

Consider the set of relations given 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 NameFROM Students, Courses, GradesWHERE Students.Roll_ number = Grades.Roll_numberAND Courses.Instructor = SriramAND Courses.Course_number = Grades.Course_numberAND Grades.Grade = AWhich of the following sets is computed by the above query?

A. Names of Students who have got an A grade in all courses taught by Sriram
B. Names of Students who have got an A grade in all courses
C. Names of Students who have got an A grade in at least one of the Courses taught by sriram
D. None of the above
Answer» D. None of the above
Discussion
62.

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 English department.STATEMENT I: SELECT SUM (SALARY). MAX (SALARY), MIN (SALARY).AVG (SALARY) FROM EMPLOYEE, DEPARTMENTWHERE DEPTNO=DIDAND 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

A. Both Statement I and Statement II are true
B. Both Statement I and Statement II are false
C. Statement I is correct but Statement II is false
D. Statement I is incorrect but Statement II is true
Answer» D. Statement I is incorrect but Statement II is true
Discussion
63.

_______ includes a query language and commands to insert tuples into, delete tuples from, and modify tuples in the database.

A. Data-definition language
B. Data-calculation language
C. Query Language
D. Data-manipulation language
Answer» E.
Discussion
64.

Let R and S be two relations with the following schemaR (P, Q, R1, R2, R3)S (P, Q, S1, S2)Where {P, Q} is the key for both schemas. Which of the following queries are equivalent?I) ΠP (R ⋈ S)II) ΠP (R) ⋈ ΠP (S)III) ΠP (ΠP, Q (R) ∩ ΠP, Q (S))IV) ΠP (ΠP, Q (R) - (ΠP, Q (R) - ΠP, Q (S)))

A. Only I) and II)
B. Only I) and III)
C. Only I), II) and III)
D. Only I), III) and IV)
Answer» E.
Discussion
65.

A Tuple Relational Calculus query is expressed as ________.

A. { P( ) | T)
B. { T | p(T) }
C. { T | P() | T}
D. { p(T) | T }
Answer» C. { T | P() | T}
Discussion
66.

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:

A. (A) and (B) only
B. (A) and (C) only
C. (B) and (C) only
D. (A) only
Answer» B. (A) and (C) only
Discussion
67.

Consider a join (relation algebra) between relations r(R)and s(S) using the nested loop method.There are 3 buffers each of size equal to disk block size, out of which one buffer is reserved forintermediate results. Assuming size(r(R))

A. relation r(R) is in the outer loop.
B. relation s(S) is in the outer loop.
C. join selection factor between r(R) and s(S) is more than 0.5.
D. join selection factor between r(R) and s(S) is less than 0.5.
Answer» B. relation s(S) is in the outer loop.
Discussion
68.

Consider two database relations R and S having 3 tuples in R and 2 tuples in S. what is the maximum number of tuples that could appear in the natural join of R and S?

A. 2
B. 5
C. 3
D. 6
Answer» E.
Discussion
69.

In relational databases, the natural join of two tables is:

A. Combination of Union and filtered Cartesian product.
B. Combination of projection and filtered Cartesian product.
C. Combination of selection and filtered Cartesian product
D. Cartesian product always
Answer» C. Combination of selection and filtered Cartesian product
Discussion
70.

Consider the following SQL query:(SELECT DISTINCT PnumFROM PROJ, DEPT, EMP WHERE Dnum = DnumberAND Mgr_ssn = Ssn AND Lname=’XXXX’)UNION(SELECT DISTINCT Pnum FROM PROJ, WORKS_ON, EMP WHERE Pnum=Pno AND ESSn=SsnAND Lname=’XXXX’)

A. all project numbers for projects that involve an employee with last name ‘XXXX’, either as worker or as manager
B. all project numbers for projects that involve an employee with last name ‘XXXX’ as manager
C. records of all employees with last name as ‘XXXX’ working in a project as manager
D. All project numbers for projects in department Dnumber and where ‘XXXX’ is involved either as worker or as manager
Answer» B. all project numbers for projects that involve an employee with last name ‘XXXX’ as manager
Discussion
71.

Consider Join of a relation R with a relation S. If R has m tuples and S has n tuples, then maximum and minimum sizes of the Join respectively are

A. m + n and 0
B. mn and 0
C. m + n and |m - n|
D. mn and m + n
Answer» C. m + n and |m - n|
Discussion
72.

______ operation preserves those tuples that would be lost in____

A. Natural join, outer join
B. Outer join, natural join
C. Left outer join, right outer join
D. Left outer join, natural join
Answer» C. Left outer join, right outer join
Discussion
73.

Properties of ‘DELETE’ and ‘TRUNCATE’ commands indicate that

A. After the execution of ‘TRUNCATE’ operation, COMMIT, and ROLLBACK statements can be performed to retrieve the lost data, while ‘DELETE’ does not allow it.
B. After the execution of ‘DELETE’ and ‘TRUNCATE’ operation retrieval is easily possible for the lost data
C. After the execution of ‘DELETE’ operation, COMMIT and ROLLBACK statements can be performed to retrieve the lost data., while TRUNCATE do not allow it
D. After the execution of ‘DELETE’ and ‘TRUNCATE’ operation no retrieval is possible for the lost data
Answer» D. After the execution of ‘DELETE’ and ‘TRUNCATE’ operation no retrieval is possible for the lost data
Discussion
74.

Domain constraints, functional dependency and referential integrity are special forms of ____________.

A. Foreign key
B. Primary key
C. Assertion
D. Referential constraint
Answer» D. Referential constraint
Discussion
75.

In the relational model, the data structures are

A. simple
B. compiled
C. both simple & compiled
D. None of these
Answer» D. None of these
Discussion
76.

Match List I with List IIList I(E – R symbols)List II(Description) (A)(I) Key Attribute Type(B) (II) Weak Entity Type(C) (III) Total Participation of Entity in a relation(D)(IV) Multivalue Attribute type Choose the correct answer from the options given below:

A. A - II, B - IV, C - II, D - I
B. A - IV, B - I, C - II, D - III
C. A - II, B - I, C - IV, D - III
D. A - III, B - IV, C - I, D - II
Answer» D. A - III, B - IV, C - I, D - II
Discussion
77.

Consider two relations R1(A, B) with the tuples (1, 5), (3, 7) and R2(A, C) = (1, 7), (4, 9). Assume that R(A, B, C) is the full natural outer join of R1 and R2. Consider the following tuples of the form (A, B, C): a = (1, 5, null), b = (1, null, 7), c = (3, null, 9), d = (4, 7, null), e = (1, 5, 7), f = (3, 7, null), g = (4, null, 9). Which one of the following statements is correct?

A. R contains a, b, e, f, g but not c, d.
B. R contains all of a, b, c, d, e, f, g.
C. R contains e, f, g but not a, b.
D. R contains e but not f, g.
Answer» D. R contains e but not f, g.
Discussion
78.

Let R(a, b, c) and S(d, e, f) be two relations in which d is the foreign key of S that refers to the primary key of RConsider the following four operations in R and SI. Insert into RII. Insert into SIII. Deletion from RIV. Deletion from SWhich of the following can cause violation of the relational integrity constraint above?

A. Both I and IV
B. Both II and III
C. All of these
D. None of these
Answer» C. All of these
Discussion
79.

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 eachemployee makes a sale to at least one customer. What does the following query return?SELECT empName FROM employee EWHERE NOT EXISTS (SELECT custId FROM customer C WHERE C.salesRepId = E.empIdAND C.rating <> ’GOOD’);

A. Names of all the employees with at least one of their customers having a ‘GOOD’ rating.
B. Names of all the employees with at most one of their customers having a ‘GOOD’ rating.
C. Names of all the employees with none of their customers having a ‘GOOD’ rating.
D. Names of all the employees with all their customers having a ‘GOOD’ rating.
Answer» E.
Discussion
80.

WHICH_OF_THE_FOLLOWING_IS_ILLEGAL??$

A. SELECT SYSDATE – SYSDATE FROM DUAL;
B. SELECT SYSDATE – (SYSDATE – 2) FROM DUAL;
C. SELECT SYSDATE – (SYSDATE + 2) FROM DUAL;
D. None of the Mentioned
Answer» D. None of the Mentioned
Discussion
81.

FIND_THE_TEMPERATURE_IN_INCREASING_ORDER_OF_ALL_CITIES?$

A. SELECT city FROM weather ORDER BY temperature;
B. SELECT city, temperature FROM weather;
C. SELECT city, temperature FROM weather ORDER BY temperature;
D. SELECT city, temperature FROM weather ORDER BY city;
Answer» E.
Discussion
82.

The SQL statement

A. , 4) FROM DUAL;
B. 6789
C. 2345
D. 1234
Answer» C. 2345
Discussion