Search for notes by fellow students, in your own course and all over the country.
Browse our notes for titles which look like what you need, you can preview any of the notes via a sample of the contents. After you're happy these are the notes you're after simply pop them into your shopping cart.
Title: Structured Query Language(SQL)
Description: This note all about SQL (structured Query Language)
Description: This note all about SQL (structured Query Language)
Document Preview
Extracts from the notes are below, to see the PDF you'll receive please use the links above
Chapter 2
Structured Query Language
LEARNING OBJECTIVES
Relational algebra
Select operator
Project operator
Set operators
Union compatible relations
Union operation
Aggregate operators
Correlated nested queries
Relational calculus
Tuple relational calculus
Tuple relational calculus
DML
Super key
SQL commands
RelaTional algeBRa
Relational operations
1
...
2
...
Forms the core component of a relational query engine
4
...
Provides a framework for query optimization
SQL query
Relational algebra expression
Query expression plan
Executable code
Figure 1 Role of relational algebra in DBMS:
Unary operators
1
...
Project
Binary operators
1
...
Intersection
3
...
Join
5
...
Cartesian product
Select Operator (s)
Select operator is an unary operator
...
Notation: sθ (r)
s : Select operator(read as sigma)
θ : Selection condition
r : Relation name
Result is a relation with the same scheme as r consisting of the
tuples in r that satisfy condition θ
Syntax: scondition (relation)
Example:
Relational Operations
A collection of simple ‘low-level’ operations used to manipulate
relations
...
It provides a procedural way to query a database
...
Input is one (or) more relations
...
Output is one relation
...
1 Person
Id
Name
Address
Hobby
112
John
12, SP Road
Stamp collection
113
John
12, SP Road
Coin collection
114
Mary
16, SP Road
Painting
115
Brat
18, GP Road
Stamp collection
4
...
Collection’(person)
The above given statement displays all tuples (or) records
with hobby ‘stamp collection’
...
2
...
2
...
Example: (Experience > 3) AND (Age < 58)
4
...
σ Id > 112 OR Hobby = ‘paint’ (person)
The above given relational algebra expression gives Ids,
names of a person whose hobby is either stamp collection
(or) coin collection
...
σ Id > 112 AND Id < 115 (person)
Set Operators
It displays tuples whose ID is greater than 112 and less
than 115
3
...
σ Hobby ≠ ‘paint’ (person)
It displays tuples whose hobby is not paint, displays all
tuples other than hobby paint
...
Union (∪), Intersection (∩), set difference (–) are called
set operators
...
Hence scope of set operations is limited
to union compatible relations
...
It can be used to
keep only the required attributes of a relation instance and
throw away others
...
Result = { ( V1, V2, … VK)/Vi ∈ DOM (Ai), 1< i < k and
there is some tuple t in r, such that t
...
A2
= v2, … t
...
1 as reference
...
pName (person)
Output:
Name
John
Mary
Bart
Union Compatible Relations
Two relations are union compatible if
1
...
Names of attributes are same
3
...
Attributes with the same name in both relations have
same domain
...
Union compatible relations can be combined using
Union, Intersection, and set difference
...
Person (SSN, Name, Address, Hobby)
Professor (Id, Name, office, phone)
person and professor tables are not union compatible
...
Relations
cannot contain a mixture of different kinds of tuples, they
must be ‘tuple – homogeneous’
...
Chapter 2 • Structured Query Language | 4
...
The
union of those two relations, a union b, is a relation of the
same type, with body consisting of all tuples ‘t’ such that ‘t’
appears in a or b or both
...
Here is a different but equivalent definition:
Given are two relations ‘a’ and ‘b’ of the same type
...
e
...
Union Operation (U)
When union operation is applied on two tables it gives all
the tuples in both without Repetition
...
no
...
Name
Semester
Percentage
28
Suresh
4
65%
31
Bindu
6
55%
44
Pinky
4
75%
58
Sita
5
35%
S
R∪S
Like union and intersection, the relational difference operator also requires its operands to be of the same type
...
1
...
g
...
Redundant duplicate rows are always eliminated from
the result of UNION, INTERSECTION, EXCEPT
operations
...
SQL also provides the qualified variants UNION ALL,
INTERSECT ALL and EXCEPT ALL, where duplicates are retained
Set difference operation returns the tuples in the first
table which are not matching with the tuples of other table
...
Name
Semester
Percentage
22
Arun
7
45%
Table 5 Result of S – R
S–R=
Roll no
...
Name
Semester
Percentage
22
Arun
7
45%
31
Bindu
6
55%
58
Sita
5
35%
Supplier number
Supplier name
Status
City
44
Pinky
4
75%
SN1
MAHESH
40
HYDERABAD
28
Sita
5
35%
SN3
SURESH
40
HYDERABAD
Status
City
Example:
A
Intersection
B
Like union, Intersection operator requires its operands to
be of the same type
...
Intersection operation returns tuples which are common
to both tables
Table 3 Result of intersection operation
R∩S=
Roll no
...
24 | Unit 4 • Databases
Aggregate Operators
DIFFERENCE (A – B)
Supplier name
Supplier name
Status
City
SN1
MAHESH
40
HYDERABAD
Supplier name
Supplier name
Status
City
SN4
RAMESH
30
CHENNAI
DIFFERENCE (B – A)
Cartesian Product
The Cartesian product of two sets is the set of all ordered
pairs such that in each pair, the first element comes from
the first set and the second element comes from second
set
...
We define the Cartesian product of two
relations ‘a’ and ‘b’, as
‘a’ times ‘b’, where a and b have no common attribute names (If we need to construct the Cartesian product
of two relations that do have any such common attribute
names, therefore, we must use the RENAME operator first
to rename attributes appropriately)
...
This is also a binary set operation, but the relations on which it is applied need not to be union compatible
...
Example:
R
A
B
X1
X2
X3
X4
C
D
Y1
Y2
Y3
Y4
SQL Supports the usual aggregate operators COUNT,
SUM, AVG, MAX, MIN, EVERY and ANY, but there are a
few SQL-specific points
...
The argument can optionally be preceded by the keyword DISTINCT, for example SUM (DISTINCT
column -name) to indicate that duplicates are to be
eliminated before the aggregation is done
...
2
...
3
...
4
...
The other
operators return NULL
...
1
...
Solution: SELECT AVG (balance) FROM account
WHERE branch
...
Find the number of tuples in customer relation
...
Find the number of depositors for each branch
...
name, COUNT (distinct
customer-name) FROM depositor, account WHERE
depositor
...
Nested Queries
Example: Transcript (StuId, coursecode, semester, grade)
Teaching (ProfId, coursecode, semester)
pstuId,coursecode (Transcript) × pprofId,coursecode (Teaching)
Some queries require that existing values in the database be
fetched and then used in a comparison condition
...
The other query is called the outer query
...
...
...
Chapter 2 • Structured Query Language | 4
...
DNO = D
...
NO = ENO AND
LNAME = ‘RAMYA’)
The first query selects
...
If a nested returns a single value, in such cases, it is permissible to use = instead of IN for the comparison operator
...
* SQL allows the use of tuples of values in comparisons by
placing them within parentheses
...
This query will select the employee numbers of all
employees who work on the same (PROJECT, HOURS)
combination on some project a particular employee whose
ENO = ‘929’ works on
...
Correlated nested queries: Nested queries can be evaluated
by executing the sub query (or) Inner query once and substituting the resulting value (or) values into the WHERE
clause of the outer query
...
In co-related nested queries, the inner query depends
on the outer query for its value
...
Sub-query is executed repeatedly, once for each row
that is selected by the outer query
...
A correlated subquery is a sub query that contains a
reference to a table that also appears in the outer query
...
col2 =
table1
...
The subquery contains reference to a column of table1,
even though the sub-queries FROM clause does not
mention a table table1
2
...
Suppose that Table 1 contains a row where col1 = 3 and
col2 = 4 and Table 2 contains a row where col1 = 5 and
col2 = 4
4
...
col2 = table2
...
5
...
In this, there is no specific series of operations
...
Relational algebra defines the sequence of operations
...
Relational calculus is closer to how users would formulate queries, in terms of information requirements,
rather than in terms of operations
...
Relational calculus is based on predicate logic, gives
the usual quantifiers to construct complex queries
...
26 | Unit 4 • Databases
Tuple Relational Calculus
Example: Employee
E Id
F Name
L Name
S alary
201
202
203
204
205
John
Brat
Mary
Adam
Smith
James
Frank
Jennifer
Borg
Joyce
3000
2000
3000
2000
1000
Example: ∃ × ((X ∈ Boats) ∧ X
...
(or)
∃ × ∈ Boats (X
...
Find all sailors with rating above 8
...
{E | ∃ E ∈ Employee(E
...
{P | ∃ E ∈ Employee ((E
...
salary <
3000) ∧ P
...
EId)}
P is a table, in which EIds are stored, from tuples which
satisfies the given condition
...
It is a set of all tuples t such that predicate p
is true for t, t is a tuple variable, t [A] denotes the value of
tuple ‘t’ on attribute A
...
1
...
Only true if p(X) is true for every X in the universe
...
color = ‘Red’)
means everything that exists is red
...
rating > 8}
Output
R=
Sid
Sname
Rating
Age
28
58
yuppy
9
35
rusty
10
35
2
...
| ∃ S ∈ sailors (s
...
sname =
s
...
age = s
...
Find sailors rated > 7 who have reserved boat = 103
...
rating > 7 ∧ ∃ R (R ∈ reserves
∧ R
...
sid ∧ R
...
Find sailors rated > 7 who have reserved a red boat
...
rating > 7 ∧ ∃ R (R ∈ reserves ∧
R
...
sid ∧ ∃ B( Boats ∧ B
...
bid ∧ B
...
color = ‘Red’))
‘⇒’ is a logical implication
...
color = ‘Red’)
For every ‘x’ in the boats relation, the color must be
red
...
∃ × (P(X))
∃ is called the existential or ‘there exists’ quantifier
because any tuple that exists in ‘the universe of’ tuples
may take F true, to make the quantified formula true
...
Find sailors who have reserved all boats
...
sid = R
...
bid = R
...
Tuple relational and domain relational are semantically
similar
...
In TRC, tuples share an equal status as variables, and
field referencing can be used to select tuple parts
...
27
3
...
4
...
{
Result included all tuples
That make the formula p(
...
Formula given in DRC is recursively defined
...
6
...
7
...
Example: Consider the employee table given in the above
Example
...
A query is a user-request to
retrieve data (or) information with a certain condition
...
The program will go
through all the records in the database file and select those
records that satisfy the condition
...
Features of SQL
1
...
It includes database
creation, deletion, fetching rows and modifying rows
...
SQL is a structured query language for storing, manipulating and retrieving data stored in relational database
...
It allows users to describe the data
...
It allows users to create and drop database and tables
...
It allows users to create view, functions in a database
...
Allows users to set permissions on tables and views
...
The standard SQL commands to interact with relational database are CREATE, SELECT, UPDATE,
INSERT, DROP and DELETE
...
The commands can be classified as follows:
•• Data query language: SELECT – It retrieves particular rows which satisfies the given condition
...
•• Data manipulation language: INSERT, UPDATE,
DELETE
Features
1
...
Robust transactional support
3
...
High availability
5
...
Easy to manage
7
...
FROM
...
DML (data manipulation language)
SELECT: Extracts data from a database table
...
DELETE: Deletes data from a database table
...
2
...
ALTER TABLE - Alters a database table
...
CREATE INDEX - Creates an index (search key)
...
RENAME – Changes the name of the table
...
Candidate key
2
...
Super key
4
...
Composite primary key
In relational database, ‘keys’ play a major role
...
Keys are used to ensure that each record within a table
can be uniquely identified by combining one or more fields
(or) column headers within a table
...
Each table may have one or more candidate keys, among
the available candidate keys, one key is preserved for primary key
...
Example: Student
StudentId
First
name
Last name
Course Id
CS00345
CS00254
CS00349
CS00196
CS00489
CS00553
Jim
Carry
Peter
John
Brat
Mary
Black
Norris
Murray
Mc Cloud
Holland
Smith
C2
C1
C1
C3
C4
C5
4
...
This would be a candidate
key
...
1
...
2
...
•• Uniquely identify each record in the table
...
The candidate keys which are not selected for primary
key are known as secondary keys or alternative keys
...
1
...
Primary key main features are
•• It must contain a unique value for each row of data
...
Example: We can choose primary key as studentId which
is mentioned in the table given in above example
...
Example: Customer
Cust-Id
Order-Id
Sale-details
C1
O–2
Sold
C1
O–3
Sold
C2
O–2
Sold
C2
O–3
Sold
1
...
Example: Customer
SSN
Address
Example: Emp
EId
EName
Dept – No
Dept
Dept-No
DName
In the above specified tables, Dept-No is common to both
the tables, In Dept table it is called as primary key and in
Emp table it is called as foreign key
...
For any column acting as a foreign key, a corresponding value should exist in the link (or) connecting table
...
While inserting data and removing data from the foreign key column, a small incorrect insertion or deletion destroys the relationship between the two tables
...
The SQL SELECT statement is used to query or retrieve
data from a table in the database
...
To create a simple SQL SELECT statement, you
must specify the column(s) names and the table name
...
A table
can have any number of super keys
...
The majority of tables in a relational database system
adhere to the concept of foreign key
...
In complex databases, data must be added across multiple tables, thus the link or connectivity has to be
maintained among the tables
...
The concept of Referential Integrity constraint is
derived from Foreign key
...
DOB
Assume that we can guarantee uniqueness only for SSN
field, then the following are some of the super keys possible
...
{Name, SSN, DOB}
2
...
SELECT lastname FROM persons
Output:
Lastname
Hansen
Svendson
Petterson
Chapter 2 • Structured Query Language | 4
...
SELECT lastname, firstname FROM persons
Output:
Lastname
Firstname
Hansen
Ola
Svendson
Tove
Petterson
Kari
DISTINCT statement
Returns distinct values
...
Syntax: Select DISTINCT column_name (s) from table-name
Example: Orders
BETWEEN - Between an inclusive range
...
SELECT * FROM persons
Output: It displays the entire table
2
...
No
IBM
3412
DELL
5614
WIPRO
4412
Lastname
Firstname
DELL
4413
Hansen
Smith
1
...
SELECT DISTINCT company FROM orders
Company
IBM
DELL
WIPRO
WHERE statement
The WHERE clause is used when you want to retrieve specific information from a table excluding other irrelevant
data
...
The condition provided in the WHERE clause
filters the rows retrieved from the table and gives only those
rows which were expected
...
The WHERE clause is used to specify a selection condition
...
Syntax: SELECT column FROM table WHERE column
operator value
...
It is useful when
you want to search rows to match a specific pattern, or when
you do not know the entire value
...
The LIKE condition is used to specify a search for a pattern in a column
...
Syntax: SELECT column FROM table WHERE column
LIKE pattern
1
...
SELECT * FROM persons WHERE Firstname LIKE
‘%a’
Solution: SQL statement will return persons whose first
name ends with letter ‘a’
...
bad
1977
4
...
SELECT * FROM persons WHERE firstname LIKE
‘%la%’
UPDATE
Solution: SQL statement returns persons whose firstname
contains ‘la’
...
Syntax: UPDATE table_name
SET Column_name = new_value
WHERE column_name = some_value
...
Add a first name (Nine) to the person whose last name
is ‘Rasmussen’?
Last name
First Name
Address
City
Year
Hansen
Ola
SPRoad, 16
Hyd
1956
String operations
Solution: UPDATE person SET Firstname = ‘Nine’
WHERE Lastname = ‘Rasmussen’
1
...
2
...
3
...
INSERT INTO statement
This statement is used to insert new rows into a table
...
But you need to make sure the order of the values is in the same order as the columns in the table
...
One can specify the columns
for which you want to insert data
Syntax: INSERT INTO table-name (column1, column2
...
)
1
...
P Road 16
Hyd
Svesdon
Tiva
GP Road 18
Secbad
Smith
Ole
RP Road 19
Hyd
Petterson
Kari
SP Road 17
Secbad
Hetlan
Camilla
HPRoad, 20
Hyd
2
...
Change the address and add the name of the city as
Hyd of a person with last name Rasmussen?
Solution: UPDATE person
SET Address = ‘street 12’,
city = ‘Hyd’
WHERE Lastname = ‘Rasmussen’
DELETE statement
The DELETE statement is used to delete rows from a table
...
If
you do not include the WHERE clause, all the rows in the
table will be deleted
...
Delete all rows?
Solution: DELETE * FROM table_name
Cartesian product
The Cartesian product of two sets is the set of all ordered
pairs of elements such that the first element in each pair
belongs to the first set and the second element in each pair
belongs to the second set
...
For example, given two sets:
S1 = {1, 2, 3} and S2 = {4, 5, 6}
The Cartesian product S1 × S2 is the set
{(1, 4), (1, 5), (1, 6), (2, 4), (2, 5), (2, 6), (3, 4), (3, 5),
(3, 6)}
Example:
Last name
First Name
Address
City
Hansen
Ola
SP Road 16
Hyd
Svesdon
...
Street 67
Female
Male
Name
Job
Name
Job
Komal
Clerk
Rohit
Clerk
Ankita
Sales
Raju
Sales
Assume that the tables refer to male and female staff,
respectively
...
Chapter 2 • Structured Query Language | 4
...
Find the Cartesian product of borrower and loan?
Solution: SELECT * FROM borrower, loan
2
...
loan_
number, amount FROM borrower, loan WHERE borrower
...
loan_no AND branch_name = ‘perryridge’
3
...
name = ‘perryridge’ AND amount > 1200
Comparison operator
Relation algebra includes six comparison operators (=, < >,
<, >, < =, > =)
...
For example, x < > 0 asserts that x is not equal to 0
...
These are proposition forming operators on propositions
...
- ‘asc’ is default
...
Join (⋈)
SQL Join is used to get data from two (or) more tables,
which appear as single table after joining
...
Join is used for combining columns from two or more
tables by using values common to both tables
...
Self Join: A table can also join to itself is known as self
join
...
INNER JOIN
2
...
INNER JOIN (or) EQUI JOIN
It is a simple JOIN in which result is based on matching
tuple, depending on the equality condition specified in the
query
...
Column
name = table name 2
...
Example: Class
Example: x > 0 and x < 8
Comparison results can be combined using the logical connections AND, OR NOT
1
...
SQL allows renewing
relations and attributes using ‘AS’ clause
Solution: SELECT customer
...
loan no AS
loan
...
loan-no = loan
...
Find the name, loan-no and loan amount of all customers, rename the column name loan-no as loan
...
SID = Info
...
12
Bala
12
Delhi
13
Sudha
13
Hyderabad
Example: List in alphabetic order, the names of all customers
having a loan in Perryridge branch?
Solution: SELECT customer-name FROM borrower
WHERE branch
...
4
...
SID = Info1
...
Dangling tuple: When NATURAL JOIN is performed on
two tables, there would be some missing tuples in the result
of NATURAL JOIN
Those missing tuples are called Dangling tuples
...
Example: Consider the tables class 1 and Info1
Query:
SELECT *
FROM
class1 FULL OUER JOIN Info1
ON(class1
...
SID)
Result:
SID
Adam
Name
City
16
Arun
Chennai
17
Kamal
NULL
18
NULL
Noida
OUTER JOIN: Outer Join is based on both matched and
unmatched data
...
Example: Consider the table’s class and Info
SELECT *
FROM
class LEFT OUTER JOIN Info
ON(class
...
SID)
Result:
SID
Name
City
11
Ana
Banglore
12
Bala
Delhi
13
Sudha
Hyderabad
14
adam
NULL
1
...
2
...
3
...
4
...
Add new column: By using alter command, we can add a
new column to the table
...
Example: Consider a student table
...
Add a new column called address
Example: Class 1
Example: Add multiple columns, parent-name, courseName, date-of-birth to student table
...
33
Syntax: Drop table table-name
Example: Rename a column address to Location
Rename: This command is used to rename a table
...
TRUNCATE command: Truncate command removes all
tuples from a table, this command will not destroy the tables
structure
...
This command will destroy the table structure
...
DROP a column: Alter command can be combined with
DROP command to remove columns from a table
...
1
...
Write a query to select only the persons with last name
‘Svendson’ and the first name equal to ‘Tove’ or ‘ola’?
(A) SELECT *
FROM Persons
WHERE last-name=’svendson’
AND first-name=’tove’
(B) SELECT *
FROM Persons
WHERE last-name=’svendson’
AND (first-name=’tove’ OR first-name=’ola’)
(C) SELECT *
FROM Persons
WHERE last-name=’svendson’
AND (first-name=’tove’ AND first-name=’ola’)
(D) SELECT *
FROM Persons
WHERE last-name=’svendson’
OR (first-name=’tove’ AND first-name=’ola’)
3
...
Write an SQL statement:
(i) To select the persons living in a city that starts
with ‘S’ from the ‘Persons’ table?
(A) SELECT *
FROM Persons
WHERE city LIKE ‘s__’
...
(C) SELECT *
FROM Persons
WHERE city LIKE ‘%s’
...
(ii) To select the persons living in a city that contains
the pattern ‘tav’ from ‘Persons’ table?
(A) SELECT *
FROM Persons
WHERE city LIKE ‘_tav_’
...
4
...
(D) SELECT *
FROM Persons
WHERE city LIKE ‘%tav%’
...
Consider the given table called ‘Persons’
P-Id
Last-name
First-name
Address
City
1
Hansen
ola
Timoteivn-10
Sandnes
2
Svendson
Tove
Brazil-50
Sandnes
3
Petterson
Kari
Storgt-20
Stavanger
and the ‘Orders’ table
O-Id
Order No
P-Id
11
77895
3
12
44678
3
13
22456
1
14
24562
1
15
34764
5
perform NATURAL JOIN operation on both the tables
and what is are the O_Id’s displayed in the result?
(A) 11, 12, 13
(B) 11, 13, 14
(C) 11, 12, 13, 14
(D) 12, 13, 14
6
...
Consider the given table ‘Result’
...
(A) SELECT student-name
FROM Result
ORDERBY marks DESC > 3
(B) SELECT student-name
FROM Result
ORDERBY marks DESC = 3
(C) SELECT student-name
FROM Result
ORDERBY marks DESC limit 3
(D) None of these
8
...
(A) SELECT COUNT(*)
FROM Result
WHERE marks > = 80
(B) SELECT COUNT
FROM Result
WHERE marks > = 80
(C) SELECT SUM(*)
FROM Result
WHERE marks > = 80
(D) SELECT SUM
FROM Result
WHERE marks > = 80
Chapter 2 • Structured Query Language | 4
...
Consider the given tables:
Customer
Customer name
Customer street
Customer city
Sonam
Mirpurroad
Dhaka
Sonam
Aga KhaRoad
Bogra
Anusha
XYZRoad
Kanchi
Nandy
MirpurRoad
Dhaka
Account
Account number
Customer name
Balance
A-101
Anusha
1000
A-102
Anusha
1500
A-103
Sonam
2000
A-104
Nandy
2500
From the customer table, find out the names of all the
customers who live in either Dhaka or Bogra?
(A) SELECT customer-name
FROM customer
WHERE customer-city=’dhaka’ OR
customer-city=’bogra’
(B) SELECT customer-name
FROM customer
WHERE customer-city=dhaka OR
customer-city=’bogra’
(C) SELECT customer-name
FROM customer
WHERE customer-city=’dhaka’ AND
customer-city=’bogra’
(D) SELECT customer-name
FROM customer
WHERE customer-city=’dhaka’ EXIST
customer-city=’bogra’
10
...
(i)
Find all customers who have only accounts but no
loans
...
customer-name=Borrower
...
customer-name = Borrower
...
customer-name=Borrower
...
customer-name=Borrower
...
Borrower
Customer name
Loan no
...
Anusha
A-102
Sonam
A-103
Nandy
A-104
4
...
customer-name=Borrower
...
customer-name = Borrower
...
customer-name = Borrower
...
customer-name = Borrower
...
Consider the following ‘employee’ table
(C) SELECT SUM(salary)
FROM Employee
GROUP BY Branch-name
(D) SELECT branch-name, SUM(salary)
FROM Employee
(iii) Find branch city, branch name Wise total salary,
average salary and also number of employees
...
(A) SELECT COUNT(branch-name)
FROM Employee
(B) SELECT COUNT(DISTINCT branch-name)
FROM Employee
(C) SELECT DISTINCT COUNT(branch-name)
FROM Employee
(D) SELECT COUNT(*)
FROM Employee
(ii)
Find the total salary of all employees at each
branch of the bank
...
0
13
...
2
14
...
3
14
...
37
SHIPMENTS
Supplier number Part number Project number
Quantity
SN1
PN1
PJ1
300
SN1
PN1
PJ4
400
SN2
PN3
PJ1
350
SN2
PN3
PJ2
450
SN2
PN3
PJ3
640
SN2
PN3
PJ4
320
SN2
PN3
PJ5
330
SN2
PN3
PJ6
520
SN2
PN3
PJ7
480
SN2
PN5
PJ2
460
SN3
PN3
PJ1
440
SN3
PN4
PJ2
410
SN4
PN6
PJ3
310
SN4
PN6
PJ7
320
SN5
PN2
PJ2
340
SN5
PN2
PJ4
350
SN5
PN5
PJ5
360
SN5
PN5
PJ7
370
SN5
PN6
PJ2
380
SN5
PN1
PJ4
420
SN5
PN3
PJ4
440
SN5
PN4
PJ4
450
SN5
PN5
PJ4
400
SN5
PN6
PJ4
410
13
...
part-number, SUM (shipments
...
part-number
(B) SELECT SUM(shipments
...
part-number
(C) SELECT shipments
...
quantity)
FROM Shipments
GROUP BY shipments
...
part-number, SUM (shipments
...
part-number
(ii) Get part numbers for parts supplied by more than
two suppliers?
(A) SELECT shipments
...
part-number
HAVING COUNT(shipments
...
part-number
FROM Shipments
GROUP BY shipments
...
supplier-number)>=2
(C) SELECT shipments
...
part-number>2
(D)
SELECT shipments
...
supplier-number)>2
FROM Shipments
GROUP BY shipments
...
supplier-name
FROM Supplier
WHERE suppliers
...
supplier-number
FROM Shipments
WHERE Shipments
...
supplier-name
FROM Supplier
WHERE suppliers
...
supplier-number
FROM Shipments
WHERE Shipments
...
supplier-name
FROM Supplier
WHERE suppliers
...
supplier-number
FROM Shipments
WHERE Shipments
...
supplier-number
UNION
SELECT Shipments
...
part-number=’PN3’
14
...
(A) SELECT DISTINCT suppliers
...
supplier-number
IN (SELECT Shipments
...
part-number
IN (SELECT Parts
...
color=’Blue’))
(B) SELECT DISTINCT suppliers
...
supplier-number
IN (SELECT Shipments
...
38 | Unit 4 • Databases
WHERE Shipments
...
part-number
FROM Parts
WHERE Parts
...
supplier-name
FROM Suppliers
WHERE suppliers
...
supplier-number
FROM Shipments
WHERE Shipments
...
part-number
FROM Parts
WHERE Parts
...
supplier-name
FROM Suppliers
WHERE suppliers
...
supplier-name
FROM Shipments
WHERE Shipments
...
part-number
FROM Parts
WHERE Parts
...
supplier-number
FROM suppliers
WHERE Suppliers
...
status)
FROM Suppliers)
(B) SELECT Suppliers
...
status<=(SELECT MAX
(Suppliers
...
supplier-number,
MAX (Suppliers
...
status
(D) SELECT Suppliers
...
status=MAX(Suppliers
...
supplier-name
FROM Suppliers
WHERE EXIST(SELECT *
FROM Shipments
WHERE Shipments
...
supplier-number
AND
Shipments
...
supplier-name
FROM Suppliers
WHERE NOT EXIST(SELECT *
FROM Shipments
WHERE Shipments
...
supplier-number
AND
Shipments
...
supplier-name
FROM Suppliers
WHERE EXIST(SELECT *
FROM Shipments
WHERE Shipments
...
supplier-number
OR
Shipments
...
supplier-name
FROM Suppliers
WHERE EXIST(SELECT *
FROM Shipments
WHERE Shipments
...
supplier-number
UNION
Shipments
...
(i) Get supplier names for suppliers who do not supply part PN2
...
supplier-name
FROM Suppliers
WHERE NOT EXIST(SELECT *
FROM Shipments
WHERE Shipments
...
supplier-number
AND
Shipments
...
supplier-name
FROM Suppliers
WHERE EXIST(SELECT *
FROM Shipments
WHERE Shipments
...
supplier-number
AND
Shipments
...
supplier-name
FROM Suppliers
WHERE EXCEPT(SELECT *
FROM Shipments
WHERE Shipments
...
supplier-number
AND
Shipments
...
supplier-name
FROM Suppliers
WHERE NOT EXIST(SELECT *
FROM Shipments
Chapter 2 • Structured Query Language | 4
...
supplier-number = suppliers
...
part-number=’PN2’)
(ii) Get supplier names for suppliers who supply all
parts
...
supplier-name
FROM Suppliers
WHERE NOT EXIST(SELECT *
FROM Part
WHERE NOT EXIST(SELECT * FROM
Shipments
WHERE Shipments
...
supplier-number
AND
Shipments
...
part-number))
(B) SELECT DISTINCT suppliers
...
supplier-number = suppliers
...
part-number=Parts
...
supplier-name
FROM Suppliers
WHERE NOT EXIST(SELECT *
FROM Part
WHERE EXIST(SELECT * FROM Shipments
WHERE Shipments
...
supplier-number
AND
Shipments
...
part-number))
(D) SELECT DISTINCT suppliers
...
supplier-number = suppliers
...
part-number=Parts
...
part-number
FROM parts
WHERE Parts
...
part-number
FROM shipments
WHERE Shipments
...
part-number
FROM parts
WHERE Parts
...
supplier-name
FROM shipments
WHERE Shipments
...
part-number
FROM parts
WHERE Parts
...
part-number,Shipments
...
supplier-number=’SN2’
(D) SELECT parts
...
color
FROM parts
WHERE Parts
...
part-number
FROM shipments
WHERE Shipments
...
The teacher with name Zohar teaching the course MS?
(A)
sName = ‘Zohar’ teach = MS
...
(C)
sname = ‘Zohar’ and course = ‘MS’ (teach)
...
17
...
Consider the join of a relation A with a relation B
...
Then the maximum and
minimum sizes of the join respectively are
...
Match the following:
I Set intersection
1 R | × | S
II Natural join
2 r – (r – s)
III Division
3 ←
IV Assignment
4 (π R−S (r ) × s)
pR – S(r) – pR –S
−π R−S , s(r )
4
...
Which one is correct for division operations for relation r and s
(A)
r÷s
π R − S (r ) − π R − S ((π R − S (r ) × s ) − π R − S ), s (r )
(B)
(C) Temp 1 ← pR – S (r)
Temp 2 ← pR – S(temp1 × s) –pR – S, s(r)
result = temp 1 – temp 2
(D) All the above
Practice Problems 2
7
...
(A)
sHobby = ‘painting’ OR Hobby = ‘singing’ (person)
(B)
sHobby = ‘painting’,’ singing’ (person)
(C)
sHobby = ‘painting’ OR ‘singing’ (person)
(D) All are correct
8
...
1
...
Which one is not a query language?
(A) SQL
(B) QBE
(C) Data log
(D) MySQL
3
...
‘_ _ _%’ matches any string of
(A) At least three characters
(B) At most three characters
(C) Exactly three characters
(D) exactly three characters ending with %
5
...
What is the purpose of project operation?
(A) It selects certain columns
(B) It selects certain rows
(C) It selects certain strings
(D) It selects certain integers
Common data for questions 7 and 8: Person
Id
Name
Age
Hobby
11
Anu
21
Stamp Collection
22
Kamal
32
Painting
33
Ravi
24
Dancing
44
Ram
22
Singing
Common data for questions 9 and 10: Consider the following relation: Teach
Name
course
Rating
Age
Zohar
MD
7
35
Nisha
BDS
8
27
Zohar
MS
7
34
Ravi
MBA
9
33
9
...
Select the courses with rating above 7?
(A)
pcourse (srating > 7 (Teach))
(B)
scourse (p rating > 7(Teach))
(C)
pname, course (srating > 7 (Teach))
(D) None
Common data for questions 11 and 12: Consider the following schema of a relational database employee (empno, ename,
eadd) project (pno, pname) Work–on (empno, pno) Part(partno,
partname, qty-on-hand, size) Use (empno, pno, partno, number)
11
...
sname(employee ⋈ (spname = ‘VB’ project) ⋈ worked on)
(A)
(B)
sname (employee ⋈ (ppname = ‘VB’ (project) ⋈ work on)
pname (employee ⋈ (spname = ‘VB’ (project) ⋈ work on)
(C)
(D)
pname (employee ⋈ (ppname = ‘VB’ (project) ⋈ work on)
Chapter 2 • Structured Query Language | 4
...
Display the names of the people who are not working
for any project
...
Consider the following tables:
(A) Names of the students who are working in either
projects ‘MS’ or ‘MD’
(B) Names of the students who are working in both the
projects ‘MS’ or ‘MD’
(C) Names of the students who are not working in any of
the projects ‘MS’ or ‘MD’
(D) None of the above
b
c
g
h
b
c
a
d
16
...
List the names of the students who are participating in
every project and have used every part
...
The following query gives pname (employee ⋈(work on ÷
ppro (sPname = ‘MS’ AND ‘MD’(project)))
17
...
2,
GROSS = basic * 1
...
Given two union compatible relations R1(A, B) and R2(C,
D), what is the result of the operation
R1 A = CAB = DR2?
(A)
R1 ∪ R2
(B) R1 × R2
(C)
R1 – R2 (D)
R1 ∩ R2
19
...
cname
...
cname = customer c name (sBanker
...
c city (sBanker name = ‘Aggasi’(client × customer)
(C)
pclient
...
Cucity(sclient
...
name = Cutomer
(client × customer)
(D)
p
...
Cucity(sBankers name = name (sBanker
...
Consider the following schema pertaining to students data
Student (rno, name, add)
Enroll (rno, Cno, Cname) Where the primary keys are
shown Underlined
...
of tuples in the student and
Enroll tables are 120 and 8 respectively
...
of tuples that can be present in (student * Enroll) where ‘*’ denotes natural join
...
42 | Unit 4 • Databases
Previous Years’ Questions
1
...
We would like to rank customers according
to decreasing balance
...
Query 1: select A
...
customer) from
account A, account B where A
...
balance
group by A
...
customer, 1 + count (B
...
balance <
B
...
customer Consider these statements about Query1 and Query2
...
Query1 will produce the same row set as Query2
for some but not all databases
...
Both Query1 and Query2 are correct implementation of the specification
...
Query1 is a correct implementation of the specification but Query2 is not
...
Neither Query1 nor Query2 is a correct implementation of the specification
...
Assigning rank with a pure relational query takes
less time than scanning in decreasing balance order assigning ranks using ODBC
...
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
...
Given the following four
queries:
Query1: select student from enrolled where student in
(select student from paid)
Query2: select student from paid where student in (select student from enrolled)
Query3: select E
...
student = P
...
student = paid
...
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 that amounts
6000, 7000, 8000, 9000 and 10000 were each paid by
20% of the students
...
Which of the following
statements is correct?
[2006]
(A) Plan 1 and Plan 2 will not output identical row
sets for all databases
(B) A course may be listed more than once in the
output of Plan 1 for some databases
(C) For x = 5000, Plan 1 executes faster than Plan 2
for all databases
(D) For x = 9000, Plan 1 executes slower than Plan 2
for all databases
4
...
The relation enroll (studId, courseId) gives which student has
enrolled for (or taken) what course(s)
...
What does the following relational algebra expression represent?
Πcourseld((Πstudid(ssex = ‘female’(studInfo))
× Πcourseld (enroll)) - enroll)
[2007]
(A) Courses in which all the female students are enrolled
(B) Courses in which a proper subset of female students are enrolled
...
43
(C)
Courses in which only male students are enrolled
...
Consider the relation employee (name, sex, supervisorName) with name as the key
...
What does the following Tuple
Relational Calculus query produce?
e ⋅ name | employee(e) ∧
(∀x)[¬employee( x) ∨ x ⋅ supervisor Name ≠ e ⋅ name ∨
x ⋅ sex = "male"]}
[2007]
(A) Names of employees with a male supervisor
...
(C) Names of employees with no immediate female
subordinates
...
6
...
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?
Q1: SELECT e
...
department = ‘5’
and s
...
salary)
Q2: SELECT e
...
salary > Any
(Select distinct salary From employee s Where
s
...
(D) Neither Q1 nor Q2 is the correct query
7
...
Which of
the following queries are equivalent?
I
...
ΠP (R) ⋈ ΠP (S)
III
...
ΠP (ΠP, Q (R) – (ΠP, Q (R) – (ΠP, Q (S))) [2008]
(A) Only I and II
(C) Only I, II and III
(B) Only I and III
(D) Only I, III and IV
8
...
Now consider the following queries on the database:
I
...
{t | t ∈ π R − S (r ) ∧ ∀u ∈ s (∃v ∈ r (u = v[ s ] ∧ t
= v[ R − S ]))}
{t | t ∈ π R − S (r ) ∧ ∀v ∈ r (∃u ∈ s (u = v[ s ] ∧ t
III
...
SELECT R
...
b
FROM R, S
WHERE R
...
c
Which of the above queries are equivalent?
(A) I and II
(B) I and III
(C) II and IV
(D) III and IV
[2009]
Common data for questions 9 and 10: Consider the following relational schema: Suppliers (sid: integer, sname:
string, city: string, street: string) Parts(pid: integer, pname:
string, color: string) Catalog (sid: integer, pid: integer,
cost: real)
9
...
sname
FROM Suppliers S
WHERE S
...
sid
FROM Catalog C
WHERE C
...
pid FROM Parts P
WHERE P
...
Which one of the following is
the correct interpretation of the above query? [2009]
(A) Find the names of all suppliers who have supplied a non-blue part
...
(C) Find the names of all suppliers who have supplied only blue parts
...
10
...
44 | Unit 4 • Databases
3
‘Anil’
69
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 pid
FROM Reservation
WHERE class = ‘AC’ AND
EXISTS (SELECT *
FROM Passenger
WHERE age > 65 AND
Passenger
...
pid)
[2010]
(A) 1, 0
(B) 1, 2
(C) 1, 3
(D) 1, 5
11
...
Two queries Q1 and Q2 are given below
...
Q2: π A1 … An (σ c1 ≤ Ap ≤ c2 (r )) where c1 and c2 are constants
...
Which of the following statements is TRUE?
[2011]
(A) Ordered indexing will always outperform hashing for both queries
(B) Hashing will always outperform ordered indexing for both queries
...
(D) Hashing will outperform ordered indexing on
Q2, but not on Q1
...
Database table by name Loan_Records is given below
...
Using MX and MY, new records are inserted
in the table 128 times with X and Y values being MX
+ 1, 2 * MY + 1 respectively
...
What will be the output of the following SQL query
after the steps mentioned above are carried out?
SELECT Y FROM T WHERE X = 7;
[2011]
(A) 127
(B) 255
(C) 129
(D) 257
14
...
Suppose R1(A, B) and R2(C, D) are two relation
schemas
...
B is a foreign key that refers to C in R2
...
00
Id
Name
Age
Suresh
Ramgopal
5000
...
00
15
Shreya
24
99
Rohit
11
Id
Name
Age
15
Shreya
24
What is the output of the following SQL query?
SELECT count ( * )
FROM (Select Borrower, Bank_Manager FROM
Loan Records) AS S
NATURAL JOIN
(SELECT Bank_Manager, Loan_Amount FROM
Loan_Records) AS T;
[2011]
(A) 3
(B) 9
(C) 5
(D) 6
13
...
After the creation of the
table, one record (X = 1, Y = 1) is inserted in the table
...
45
16
...
Id
FROM A
WHERE A
...
Age
FROM B
WHERE B
...
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
...
Id > 40 V C
...
Consider the following relational schema
...
sname FROM Students
as S, Registration as R WHERE R
...
rollno
AND R
...
percent>90
(II) psname(σcourseno=107^percent>90 Registration⑅Students)
(III) {T |∃ S ∈ Students, ∃R∈ Registration (S
...
rollno ∧ R
...
percent>90∧T
...
sname)}
(IV) {
[2013]
(A) I, II, III and IV
(B) I, II and III only
(C) I, II and IV only
(D) II, III and IV only
19
...
S2: Given the table R (a, b, c) where a and b together
form the primary key, the following is a valid table definition
...
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
...
(B) It executes and gives the correct result
...
(D) It generates an error because the GROUP BY
clause cannot be used with table joins in a subquery
...
Given an instance of the STUDENTS relation as
shown below:
Student ID
Student
Name
Student Email
Student
Age
CPI
2345
Shankar
shaker @ math
X
9
...
5
7853
Shankar
shankar @ cse
19
9
...
3
8765
Ganesh
ganesh@ civil
19
8
...
[2014]
22
...
There
are three buffers each of size equal to disk block size,
out of which one buffer is reserved for intermediate
results
...
5
(D) Join selection factor between r(R) and s(S) is less
than 0
...
SQL allows duplicate tuples in relations, and correspondingly defines the multiplicity of tuples in the
result of joins
...
46 | Unit 4 • Databases
Select * from R where a in (select S
...
* from R, S where R
...
a
(B) Select distinct R * from R, S where R
...
a
(C) Select R
...
a = S1
...
* from R, S where R
...
a and is unique
R
24
...
(D) Names of all the employees with all their customers
having a ‘GOOD’ rating
...
SELECT operation in SQL is equivalent to [2015]
(A) The selection operation in relational algebra
(B) The selection operation in relational algebra,
except that SELECT in SQL retains duplicates
...
(D) The projection operation in relational algebra,
except that SELECT in SQL retains duplicates
...
Consider the following relations:
Student
Roll No
Student Name
1
Raj
2
Rohit
3
Raj
(C)
π A2 (σ ( F ∧ F )( r ) )
1
2
(D)
π A2 (σ ( F ∨ F )( r ) )
1
2
25
...
Assume that
every employee has at least one associated dependent
in the dependent relation
...
(B) all dependents
...
(D) all of his/her dependents
...
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
...
What does the following query return?
SELECT empName
FROM employee E
WHERE NOT EXISTS
(SELECT custId
FROM customer C
WHERE C
...
empId
AND C
...
(B) Names of all the employees with at most one of
their customers having a ‘GOOD’ rating
...
SELECT S
...
Marks)
FROM Student S, Performance P
WHERE S
...
Roll_No
GROUP BY S
...
Consider two relations R1(A, B) with the tuples (1, 5),
(3, 7) and R2(A, C) = (1, 7), (4, 9)
...
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)
...
(B)
R contains all of a, b, c, d, e, f, g
...
(D)
R contains e but not f, g
...
Consider the following relation
Cinema (theater, address, capacity)
Which of the following options will be needed at the
end of the SQL query
Chapter 2 • Structured Query Language | 4
...
address
FROM Cinema P1
such that it always finds the addresses of theaters with
maximum capacity?
[2015]
(A) WHERE P1
...
Capacity
from Cinema P2)
(B) WHERE P1
...
Capacity
from Cinema P2)
(C) WHERE P1
...
capacity) from Cinema P2)
(D) WHERE P1
...
capacity) from Cinema P2)
31
...
Consider a database that has the relation schema EMP
(EmpId, EmpName and DeptName)
...
EMP
EmpId
EmpName
DeptName
1
XYA
AA
SELECTIVE AVG ( EC
...
[2017]
33
...
Consider the following queries on the database
expressed in tuple relational calculus
...
Consider a database that has the relation schema CR
(studentName, CourseName)
...
CR
StudentName
CourseName
SA
CA
SA
CB
SA
CC
SB
CB
SB
CC
SC
CA
SC
CB
SC
CC
SD
CA
SD
CB
SD
CC
SD
CD
SE
CD
SE
CA
SE
CB
SF
CA
SF
CB
SF
CC
4
...
T 1 ← pCouraseName (sStudentName =' SA ' (CR))
T 2 ← CR ÷ T 1
The number of rows in T2 is
...
Consider the following database table named
top_scorer
...
player FROM top_scorer AS ta
WHERE ta
...
goals
FROM top_scorer AS tb
WHERE tb
...
goals >ANY (SELECT tc
...
country = ‘Germany’)
The number of tuples returned by the above SQL
query is _________
...
Consider the following two tables and four queries in
SQL
...
isbn, S
...
isbn = S
...
isbn, S
...
isbn = S
...
isbn, S
...
isbn = S
...
isbn, S
...
isbn = S
...
Consider the relations r(A, B) and s(B, C), where s ⋅ B
is a primary key and r ⋅ B is a foreign key referencing
s ⋅ B
...
Assume that r and s contain no null values
...
A
2
...
C
4
...
(i) B (ii) A
9
...
A
11
...
(i)
A (ii)
A (iii)
A 14
...
A
18
...
A
20
...
C
6
...
(i) A (ii) C
12
...
(i)
A (ii)
A (iii)
A 16
...
B
11
...
D
12
...
D
13
...
A
14
...
C
15
...
A
16
...
A
17
...
A
18
...
A
19
...
A
20
...
C
2
...
C
4
...
C
6
...
D
11
...
C
13
...
C
15
...
B
17
...
19
22
...
C
24
...
D
26
...
D
31
...
2
...
D 34
...
7 36
...
C
8
...
A
28
...
A
19
...
C
10
...
B
30
Title: Structured Query Language(SQL)
Description: This note all about SQL (structured Query Language)
Description: This note all about SQL (structured Query Language)