©THESTAR EXAM SOLUTIONS 2024/2025
ALL RIGHTS RESERVED.
5. SQL - Queries, Programming, Triggers.
Exam Questions With Verified And Updated
Answers
What are the parts of a basic SQL query? - answer✔SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification
relation-list - answer✔A list of relation names (possibly with a range-variable after each name).
target-list - answer✔A list of attributes of relations in relation-list
qualification - answer✔Comparisons (Attr op const or Attr1 op Attr2, where op is one of <, >, =,
<=, >=, != ) combined using AND, OR and NOT.
DISTINCT - answer✔is an optional keyword indicating that the answer should not contain
duplicates. Default is that duplicates are not eliminated!
Are the input and result tables of an SQL query sets or multisets? - answer✔If not using
DISTINCT, the result tables are a set, else, multiset.
How can you obtain a set of tuples as the result of a query? - answer✔Tables are multisets of
tuples.
What are range variables in SQL? - answer✔Really needed only if the same relation appears
twice in the FROM clause. It is good style, however, to use range variables always!
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND bid=103
1|Page
, ©THESTAR EXAM SOLUTIONS 2024/2025
ALL RIGHTS RESERVED.
How can you give names to output columns in a query that are defined by arithmetic or string
expressions? - answer✔Each item in a select-list can be of the form expression AS
column_name, where expression is any arithmetic or string expression over column names
(possibly prefixed by range variables) and constants, and column_name
is a new name for this column in the output of the query.
SELECT S.age, age1=S.age-5, 2*S.age AS age2
FROM Sailors S
WHERE S.sname LIKE 'B_%B'
What support does SQL offer for string pattern matching? - answer✔SQL provides support for
___________ through the LIKE operator, along with the use of the wild-card symbols % (which
stands for zero or more arbitrary characters) and ~ (which stands for exactly one, arbitrary,
character). Thus, '_AB%' denotes a ___________ every string that contains at least three
characters, with the second and third characters being A and B respectively.
What operations does SQL provide over (multi)sets of tuples, and how would you use these in
writing queries? - answer✔UNION, INTERSECT, and EXCEPT
What are nested queries? - answer✔A very powerful feature of SQL: a WHERE clause can itself
contain an SQL query! (Actually, so can FROM and HAVING clauses.)
Find names of sailors who have reserved boat #103:
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid=103)
What is correlation in nested queries? - answer✔SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (( SELECT B.bid
FROM Boats B )
2|Page
ALL RIGHTS RESERVED.
5. SQL - Queries, Programming, Triggers.
Exam Questions With Verified And Updated
Answers
What are the parts of a basic SQL query? - answer✔SELECT [DISTINCT] target-list
FROM relation-list
WHERE qualification
relation-list - answer✔A list of relation names (possibly with a range-variable after each name).
target-list - answer✔A list of attributes of relations in relation-list
qualification - answer✔Comparisons (Attr op const or Attr1 op Attr2, where op is one of <, >, =,
<=, >=, != ) combined using AND, OR and NOT.
DISTINCT - answer✔is an optional keyword indicating that the answer should not contain
duplicates. Default is that duplicates are not eliminated!
Are the input and result tables of an SQL query sets or multisets? - answer✔If not using
DISTINCT, the result tables are a set, else, multiset.
How can you obtain a set of tuples as the result of a query? - answer✔Tables are multisets of
tuples.
What are range variables in SQL? - answer✔Really needed only if the same relation appears
twice in the FROM clause. It is good style, however, to use range variables always!
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND bid=103
1|Page
, ©THESTAR EXAM SOLUTIONS 2024/2025
ALL RIGHTS RESERVED.
How can you give names to output columns in a query that are defined by arithmetic or string
expressions? - answer✔Each item in a select-list can be of the form expression AS
column_name, where expression is any arithmetic or string expression over column names
(possibly prefixed by range variables) and constants, and column_name
is a new name for this column in the output of the query.
SELECT S.age, age1=S.age-5, 2*S.age AS age2
FROM Sailors S
WHERE S.sname LIKE 'B_%B'
What support does SQL offer for string pattern matching? - answer✔SQL provides support for
___________ through the LIKE operator, along with the use of the wild-card symbols % (which
stands for zero or more arbitrary characters) and ~ (which stands for exactly one, arbitrary,
character). Thus, '_AB%' denotes a ___________ every string that contains at least three
characters, with the second and third characters being A and B respectively.
What operations does SQL provide over (multi)sets of tuples, and how would you use these in
writing queries? - answer✔UNION, INTERSECT, and EXCEPT
What are nested queries? - answer✔A very powerful feature of SQL: a WHERE clause can itself
contain an SQL query! (Actually, so can FROM and HAVING clauses.)
Find names of sailors who have reserved boat #103:
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid=103)
What is correlation in nested queries? - answer✔SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (( SELECT B.bid
FROM Boats B )
2|Page
