Module 3

[Link] is the difference between the WHERE and HAVING clause? Illustrate with an example. (or) How is
the purpose of where clause is different from that of having clause?

WHERE HAVING
Used to filter rows before any grouping or aggregation Used to filter groups after the data has been grouped
operations. It acts on individual records to limit the and aggregated. It acts on the results derived from
data that will be included in the next steps. aggregate functions
Cannot be used with aggregate functions Can be used with aggregate functions. It evaluates
like SUM or AVG. It's only for filtering rows based on conditions after groups have been formed.
specific conditions.
Applied to Individual rows Applied to Resulting groups from the GROUP
BY clause
Used with SELECT, UPDATE, DELETE Used with SELECT with GROUP BY
Consider a table named employees with the following columns: id, name, department, and salary.

WHERE Clause Example

SELECT * FROM employees

WHERE department = 'Sales';
Explanation: This query uses the WHERE clause to filter all rows from the employees table to include only
those in the 'Sales' department before any group-by operations or aggregations take place.

HAVING Clause Example

SELECT department, AVG(salary) AS avg_salary

FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
Explanation: This query groups employees by their department and calculates the average salary for each
department. The HAVING clause is used to filter these groups, retaining only those departments where the
average salary is greater than 50,000.

2. Explain the difference between Hash indexes and B+-tree indexes.

Feature Hash Indexes B+-Tree Indexes

Definition Uses a hash function to map search keys Uses a balanced, multi-level tree
directly to data locations for fast lookups. structure to organize data for efficient
searches.
Type of Index Hash-based index Tree-based index (Balanced Tree)
Search Operation Very fast for exact matches (= operator) Fast for both exact matches and range
queries
Range Queries Not efficient; cannot efficiently handle Highly efficient; supports range queries
range searches gracefully
Insert/Delete Fast but may require rehashing Efficient; maintains balanced structure
Performance during updates
Memory Usage Usually uses more memory due to hash Uses less memory; hierarchical
table structure organization

3. Give any three uses of a trigger (or) What is the use of a trigger?

1. Enforcing Data Integrity: Ensures that certain rules are followed automatically when data is inserted,
updated, or deleted. For example, automatically updating a related table when a record changes.
1
 2. Auditing: Tracks changes made to data by automatically logging details like who made the change, what
change was made, and when it occurred.

3. Automatically Calculating Values: Performs automatic calculations or updates, such as updating a

total in an order when new items are added or removed.

4.A file has r =20000 STUDENT records of fixed length. Each record has the following fields: NAME (30
bytes), SSN (9 bytes), ADDRESS (40 bytes), PHONE(9 bytes), BIRTHDATE (8 bytes), GENDER (1 byte),
DEPTID (4 bytes), CLASSCODE (4 bytes), and PROGID (3 bytes). An additional byte is used as a deletion
marker. The file is stored on the disk with block size B=512 bytes:

a)Calculate the record size R in bytes.

b)Calculate the blocking factor bfr and the number of file blocks b assuming an unspanned
organization.
c)Calculate the average time it takes to find a record by doing a linear search

5. What is meant by a correlated nested query? Give a suitable example.

A correlated nested query is a type of SQL query where the inner query (subquery) depends on the outer query
for its values. In other words, the subquery is evaluated once for each row processed by the outer query, using
values from the current row of the outer query.

Example: Suppose we have two tables:

• Employees: EmployeeID, Name, Salary, DepartmentID

• Departments: DepartmentID, DepartmentName

SELECT [Link], [Link], [Link]

FROM Employees e
WHERE [Link] > (
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID = [Link]
);
The subquery:
SELECT AVG(Salary) FROM Employees WHERE DepartmentID = [Link]
2
calculates the average salary in each employee’s department.
Since it references [Link] from the outer query, the subquery executes for each employee,
comparing their salary to their department's average

6. Explain the advantage of a multilevel index.

A multilevel index is an index structure that builds multiple levels of indexing, similar to a hierarchy or a tree, to
manage large amounts of data efficiently.

1. Reduces Search Time: Because the index is organized hierarchically, the system needs to access only a
few index blocks to find the data, rather than scanning through a large single-level index.

2. Less Disk I/O: Disk operations are costly; multilevel indexes minimize disk reads by enabling quick
traversal down the hierarchy.

3. Handles Large Files Efficiently: As data grows, a multilevel index remains efficient because its height
increases logarithmically, not linearly.

4. Faster Browsing: Searching for a specific record becomes very fast because fewer disk reads are
required to locate the data.

a multilevel index provides a hierarchical structure that greatly speeds up data retrieval, reduces disk I/O, and
effectively manages large files, making data access faster and more efficient.

7. Illustrate the concept of trigger in SQL with an example

A trigger is a special type of stored procedure that automatically executes (fires) in response to specific events
on a particular table or view in a database. Triggers are used to enforce rules, validate data, maintain audit logs,
or automatically perform actions when data is inserted, updated, or deleted.

Common events that can activate a trigger:

• BEFORE INSERT

• AFTER INSERT

• BEFORE UPDATE

• AFTER UPDATE

• BEFORE DELETE

• AFTER DELETE

Eg: Consider a table Employees (EmployeeID ,Name, HireDate)

When we add a new employee, we need to automatically set their hire date to today.

CREATE TRIGGER AutoHireDate

BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
SET [Link] = CURRENT_DATE;
END;
Whenever you insert a new employee, the HireDate is automatically filled with today’s date.

INSERT INTO Employees (EmployeeID, Name) VALUES (1, 'Alice');

The system will automatically give Alice's HireDate as today. The trigger makes sure that every new employee's
hire date is set automatically when you add them.
3
8. Compare DDL and DML with the help of an example

Aspect DDL (Data Definition Language) DML (Data Manipulation Language)

Purpose Defines and manages database structure Inserts, updates, deletes, and retrieves data
(schema)

Examples of CREATE, ALTER, DROP, TRUNCATE INSERT, UPDATE, DELETE, SELECT

Commands

Effect Changes the structure of database Changes the data stored in the database
objects (tables, indexes) tables

Commit Often auto-commit or requires explicit Usually auto-commit or requires explicit

Required commit commit

Example Create a table called Students Insert data into Students

CREATE TABLE Students ( INSERT INTO Students (StudentID, Name,
StudentID INT, Age) VALUES (1, 'John', 20);
Name VARCHAR(50),
Age INT
);

9. What is an assertion? How they differ from triggers?

• An assertion is a database constraint that defines a condition or rule that must always be true for the
database.
• It acts as a global condition affecting the entire database.
• Assertions are used to enforce constraints that involve multiple tables or complex conditions.
• Example:
"The total salary of all employees in a department must not exceed $1,000,000."
This rule applies to data across multiple tables.

Assertion and Trigger are both mechanisms used to enforce rules and manage data integrity in a database, but
they differ in their purpose and how they operate.

Aspect Assertion Trigger

Purpose Enforces complex or multiple- Performs automatic actions on data
table rules globally changes (insert/update/delete)
Scope Global; affects the whole Specific; affects only certain tables/rows
database or multiple tables
Activation Checked during transactions; Fired automatically in response to data
always enforced modifications
Implementation Typically declared Implemented with trigger commands in
as constraints or rules SQL
Support Not supported in all RDBMS; often Widely supported in RDBMS like MySQL,
more conceptual PostgreSQL, etc.

10. Consider the following relation schema and write SQL queries to find:

• EMPLOYEE(Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary SuperSSN, Dno)
• DEPARTMENT(Dname, Dnumber, MgrSSN, MgrStartDate)
• DEPT_LOCATIONS(Dnumber, Dlocaions)
• PROJECT(Pname, Pnumber, Plocation,Dnum)
4
 • WORKS_ON(ESSN, Pno,Hours)
i. Retrieve the name and address of all employees who work for the 'Research' department.

SELECT Fname, Minit, Lname, Address

FROM EMPLOYEE E
JOIN DEPARTMENT D ON [Link] = [Link]
WHERE [Link] = 'Research';

ii. For each employee, retrieve the employee's name, and the name of his or her immediate supervisor.
SELECT
[Link] AS Employee_Fname, [Link] AS Employee_Minit, [Link] AS Employee_Lname,
[Link] AS Supervisor_Fname, [Link] AS Supervisor_Minit, [Link] AS Supervisor_Lname
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE S ON [Link] = [Link];
iii. Retrieve the name of each employee who works on all the projects controlled by department number
5.

SELECT Fname, Lname

FROM EMPLOYEE
WHERE NOT EXISTS (
SELECT Pnumber FROM PROJECT WHERE Dnum = 5
AND Pnumber NOT IN (
SELECT Pno FROM WORKS_ON WHERE ESSN = [Link]
)
);
iv. Make a list of all project numbers for projects that involve an employee whose last name is 'Smith' as
a worker or as a manager of the department that controls the project.
SELECT DISTINCT [Link]
FROM PROJECT P
JOIN WORKS_ON W ON [Link] = [Link]
JOIN EMPLOYEE E ON [Link] = [Link]
WHERE [Link] = 'Smith'

UNION

SELECT DISTINCT [Link]

FROM PROJECT P
JOIN DEPARTMENT D ON [Link] = [Link]
JOIN EMPLOYEE M ON [Link] = [Link]
WHERE [Link] = 'Smith';
v. Retrieve the SSN of all employees who work on project number 1, 2, or 3

SELECT DISTINCT ESSN

FROM WORKS_ON
WHERE Pno IN (1, 2, 3);

11. Consider a disk with block size B =512 bytes. A block pointer is P=6 bytes long and a record pointer is
PR =7 bytes long. A file has r=30,000 EMPLOYEE records of fixed length. Each record has the following
fields: Name (30 bytes),Ssn (9 bytes),Department_code (9 bytes), Address (40 bytes), Phone (10 bytes),
Birth_date (8bytes), Sex (1 byte), Job_code (4 bytes), and Salary (4 bytes, real number). An additionalbyte
is used as a deletion marker.

5
i. Calculate the record size R in bytes.
ii. Suppose that the file is ordered by the key field Ssn and we want to construct a primary index on Ssn.
Calculate The number of first-level index entries and the number of first-level index blocks
iii. Calculate the number of levels needed if we make it into a multilevel index.

12. What is a grid file? What are its advantages and disadvantages?

A grid file is a type of multilevel index designed for multidimensional data, especially useful in spatial and
scientific databases. It organizes data based on multiple attributes (dimensions) into a grid-like structure to
facilitate efficient data retrieval. A grid file divides data space into a grid of cells (or blocks) based on multiple
attribute ranges. It contains two main components:

o Linear scales for each attribute (like axes in a graph).

o A directory that points to data buckets (storage areas) for each grid cell.

When a query is made, the grid file quickly locates the relevant grid cells that satisfy the condition, reducing
search time for multidimensional queries.

Advantages of a Grid File

1. Efficient multidimensional query processing:

Quickly finds data points based on multiple attributes without scanning entire datasets.

2. Good handling of data distribution:

Adaptable to data with various distributions, unlike space-filling curves or tree structures that may
perform poorly with uneven data.

3. Supports dynamic data insertion:

Can efficiently add data points and subdivide cells to maintain performance.

4. Less storage overhead for sparse data:

Only non-empty grid cells are stored, saving space in sparse datasets.

Disadvantages of a Grid File

6
 1. High maintenance cost:
When data distribution changes significantly, grid cells may need frequent subdivision or merging,
increasing overhead.

2. Less effective for highly skewed data:

If data is clustered in specific areas, many grid cells may be empty or overloaded, reducing efficiency.

3. Complex implementation:
Managing multiple dimensions and dynamic subdivision is more complicated compared to simpler
index structures.

4. Limited support in standard DBMS:

Not widely supported or implemented in common database management systems.

13. For the relation schema below, give an expression in SQL for each of the queries that follows:

• employee (ID, person_name, street, city)

• works (ID, company_name, salary)
• company ( company_name, city)
• manages (ID, manager_id)

i). Find the employees whose name starts with ‘C’

SELECT person_name
FROM employee
WHERE person_name LIKE 'C%';
ii) Find the name of managers of each company

SELECT DISTINCT e2.person_name AS manager_name, w.company_name

FROM manages m
JOIN employee e1 ON [Link] = [Link] -- employee being managed
JOIN employee e2 ON m.manager_id = [Link] -- the manager
JOIN works w ON [Link] = [Link];
iii) Find the ID, name, and city of residence of employees who works for “First Bank Corporation” and
earns more than Rs50000

SELECT [Link], e.person_name, [Link]

FROM employee e
JOIN works w ON [Link] = [Link]
WHERE w.company_name = 'First Bank Corporation'
AND [Link] > 50000;
iv) Find the name of companies whose employees earn a higher salary, on average, than the average
salary at “First Bank Corporation”

SELECT w.company_name
FROM works w
GROUP BY w.company_name
HAVING AVG([Link]) > (
SELECT AVG(salary)
FROM works
WHERE company_name = 'First Bank Corporation'
);

7
[Link] correlated and non-correlated nested queries with suitable Examples (or) Illustrate
correlated and non-correlated nested queries with real examples.

Difference Correlated Nested Query Non-Correlated Nested Query

Dependency Depends on data from outer query (uses Does not depend on outer query;
outer row values). runs independently.

Evaluation Evaluated once for each row of the outer Evaluated only once in the entire
query. execution.

Performance Typically slower due to repeated Faster because it runs just once.
execution.

Use Case Used when the inner query needs Used for calculations or filters that
information from each outer row (e.g., are the same for all outer rows (e.g.,
compare to group averages). overall average).

Examples

[Link] Subquery

Goal: Find employees whose salary is greater than the average salary in their department.

SELECT [Link], [Link], [Link]

FROM Employee E1
WHERE [Link] > (
SELECT AVG([Link])
FROM Employee E2
WHERE [Link] = [Link]
);
The subquery depends on [Link] from the outer query. For each employee, it calculates the average salary of
their department.

2. Non-Correlated Subquery
Goal: Find employees with salary greater than the overall average salary of all employees.
SELECT Fname, Lname, Salary
FROM Employee
WHERE Salary > (
SELECT AVG(Salary)
FROM Employee
);
The subquery computes the overall average salary once; the outer query then finds employees earning more
than this overall average.

15. What is multi-level indexing? How does it improve the efficiency of searching an index file?

Multi-level indexing is a hierarchical indexing technique used in databases to improve search efficiency. It
involves creating multiple levels of index files, where each level points to the next, larger, level of indexes or
directly to data blocks.

How it works:

• The top level (root) index points to several second-level indexes.

• Each second-level index points to even lower-level indexes or directly to data blocks.

• This creates a tree-like structure that organizes data in multiple layers.

8
How it improves search efficiency:

• Instead of searching through a large index file from the beginning, the search starts at the top (root) and
quickly traverses down the levels.

• Because each step reduces the search space by a factor, the number of disk reads needed is
minimized.

• For example, searching in a multi-level index takes approximately logarithmic time relative to the
number of data blocks, which is much faster.

16. Insert the following keys, in the order given, into a B -tree of order 3: {10, 50, 20, 5, 22, 25}

17. Consider the following relations:

• Employee (Employee-Id, Employee-Name, Salary, Department-No)

• Department (Department-No, Department-Name)

Write SQL queries for the following:

(i) Retrieve the employee names and their department names
SELECT [Link]-Name, [Link]-Name
FROM Employee
JOIN Department ON [Link]-No = [Link]-No;
(ii) Retrieve department names and the average salary given by them

9
 SELECT [Link]-Name, AVG([Link]) AS Avg_Salary
FROM Employee
JOIN Department ON [Link]-No = [Link]-No
GROUP BY [Link]-Name;
(iii) Retrieve the ids of employees getting salary greater than the average salary of their department
SELECT [Link]-Id
FROM Employee e
WHERE [Link] > (
SELECT AVG(Salary)
FROM Employee
WHERE Department-No = [Link]-No
);
(iv) For each department that has more than 4 employees, retrieve the department-No and the number of
employees getting salary more than Rs. 50000
SELECT [Link]-No, COUNT(*) AS Count_Employees
FROM Employee
WHERE Salary > 50000
GROUP BY [Link]-No
HAVING COUNT(*) > 4;

18. What is meant by a heap file? Explain how insert, update, delete and search operations can be
performed in a heap file.

A heap file is a simple, unordered storage structure used to store records in a database. Records are inserted at
the end of the file without any specific order. It’s a basic way of storing data where no particular indexing or
sorting is maintained.
Operations in a Heap File:
1. Insert:
• New records are added at the end of the file.
• Insertion is straightforward—append the record to the file.
• If space is limited, free space can be reused or the file can be extended.
2. Search (Linear Search):
• To find a record, scan through the entire file sequentially (from the beginning to the end).
• For each record, check if it matches the search criteria.
• Efficient for small files but slow for large files.
3. Update:
• Search for the record (using linear search).
• Once found, overwrite the record (if it fits in the same space).
• If the new data is larger, the record might need to be moved or updated in place if space allows;
otherwise, a new space is allocated, and the old record may be marked as deleted.
4. Delete:
• Search for the record.
• Mark the record as deleted (e.g., set a deleted flag in the record) rather than physically removing it.
• The space can be reclaimed later during cleanup or compacting processes.

19. What are the advantages of Views? Explain two view implementation techniques.

Advantages of Views:

• Data Security and Privacy:

Views allow users to see only specific parts of the data, hiding sensitive information like salaries or
personal details. Users can be given access to views instead of full tables, enhancing security.
10
• Simplified Data Access:
Views can present complex data from multiple tables in a simplified, understandable way. Users can
perform operations on views without knowing the underlying complexity.

• Data Independence:
Views help shield users from changes in the underlying data structure. If table structures change, views can
be updated without affecting user applications.

• Consistent Data Representation:

Views ensure that everyone sees data in a uniform format, enforcing some level of consistency across
reports or user interfaces.

Two View Implementation Techniques

1. Virtual Views (Stored Queries):

• The most common type of views.
• Defined using a CREATE VIEW statement.
• The data isn’t stored separately; instead, the view is dynamically generated by executing the stored
query when accessed.

CREATE VIEW HighSalaryEmployees AS

SELECT EmployeeID, EmployeeName, Salary
FROM Employee
WHERE Salary > 100000;
• Advantages: Saves space, always shows up-to-date data.

2. Materialized Views:

• Stores the query result physically in the database.

• Acts like a table that contains the data based on a query.
• Needs periodic refresh to keep data current.
CREATE MATERIALIZED VIEW DeptSalarySummary AS
SELECT DepartmentNo, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY DepartmentNo;
• Advantages: Faster access since data is precomputed; useful for large, complex aggregations.

[Link] a disk with block size 512 bytes. A block pointer is 6 bytes long, and a record pointer is 7
bytes long. A file has 30,000 EMPLOYEE records of fixed-length. Each record has the following fields:
NAME (30 bytes), SSN (9bytes), DEPARTMENTCODE (9 bytes), ADDRESS (40 bytes), PHONE (9
bytes),BIRTHDATE (8 bytes), SEX (1 byte), JOBCODE (4 bytes), SALARY (4 bytes, real number). An
additional byte is used as a deletion marker. Assume that file is not ordered by the key field SSN and we
need to create a secondary index on SSN.

(i) Find the number of levels needed, if we make it into a multilevel index.
(ii) Find the number of block accesses needed to retrieve a record from this file if we use the multilevel
index.

11
21. Illustrate structure of B-Tree and B+ Tree and explain how they are different?
Structure of B-Tree and B+ Tree
1. B-Tree
• A self-balancing search tree.
• Each node contains multiple keys and child pointers.
• All keys are stored in internal nodes and leaves.
• Keys within a node are sorted.
• All leaf nodes are at the same level.
• Data records are typically stored in the leaves, but in a basic B-tree, keys and records can be in internal
nodes too.
• Nodes contain multiple keys.
• Searching navigates based on key comparisons.
• Nodes have minimum and maximum number of keys based on order. Leaf nodes contain all data
records.
• Internal nodes are just indexes guiding searches.
• Leaves are linked: 8 <-> 12 <-> 15 <-> 22...
2. B+ Tree
• A variation of B-Tree.
• All data records are stored only in the leaf nodes.
• Internal nodes contain only keys (no data records).
• Leaf nodes are linked together to form a linked list for efficient range queries.
• The height of the tree is minimized because all data is in the leaves, making searches faster for range
scans.
• Leaf nodes contain all data records.
• Internal nodes are just indexes guiding searches.
• Leaves are linked: 8 <-> 12 <-> 15 <-> 22...
Differences between B-Tree and B+ Tree
Aspect B-Tree B+ Tree
Data storage Data can be stored in internal nodes or Data stored only in leaves; internal
leaves nodes are index-only
Leaves Not necessarily at the same level; All leaves are at the same level and
internal nodes can contain data contain data
Linked leaves Not linked → less efficient for range Leaves linked with pointers, making
queries range queries efficient
Search Slightly slower for range searches due to Faster range queries due to linked
efficiency lack of linking leaves
Height Slightly taller in general Slightly shorter or equal; better for
sequential access

22. What are the different types of single-level ordered indices? Explain.
1. Primary Index
• Definition: An index built on the primary key of a data file.
• Characteristics:
o The data records are stored in order based on the primary key.
o The index contains index key and pointer to the data record.
o Type: Usually dense (each index entry points to a record).
• Purpose: To enable quick retrieval of data based on the primary key.
• Example: Employee table sorted by Employee ID; index on Employee ID allows fast search.
2. Secondary Index
• Definition: An index built on a non-primary key attribute.
• Characteristics:
12
 o Data may not be stored sorted on the indexed attribute unless clustered.
o The index contains the attribute value and pointers to data records.
o Can be clustering or non-clustering:
▪ Clustering index: Data records are stored in order of this index.
▪ Non-clustering index: Data records are stored in arbitrary order; index points to the
physical location.
• Purpose: To improve search efficiency on attributes other than the primary key.
• Example: Index on Employee's department or salary.
3. Clustered Index
• Definition: A type of index that determines the physical order of data records in storage.
• Characteristics:
o Data records are stored in order of the index.
o There is typically only one clustered index per data file because data can be physically ordered
only once.
o The index and data are stored together or in a way that the index defines the data order.
• Purpose: To optimize range queries and sequential access on the clustered attribute.
• Example: Store employee records sorted by Department No; index on Department No creates a
clustered index.

23. Differentiate between static hashing and dynamic hashing.

Aspect Static Hashing Dynamic Hashing

Hash Table Fixed size; set at the time of creation. Size can change dynamically as data
Size grows/shrinks.
Handling Uses methods like chaining or open addressing; Uses techniques like extendible or
Collisions fixed table size limits collision management. linear hashing to handle growth.
Growth Not scalable; if number of records exceeds size, Scales automatically; expands/shrinks
overflow occurs. as needed.
Efficiency Efficient when size is known and data size is Maintains efficiency even with data
stable. growth or deletion.
Overflow May lead to overflow or chain overflow if table is Automatically adapts to store more
Handling full. records; reduces overflows.
Use Case Suitable for small, stable datasets. Suitable for large, dynamic datasets
with unpredictable size.

24. Write short notes on Nested queries

A nested query (also called a subquery) is a SQL query written inside another SQL query. The inner query is
executed first, and its result is used by the outer query. Nested queries are useful for performing complex
operations such as filtering, aggregation, or comparison based on data from other tables or calculations.

• Can be used in SELECT, INSERT, UPDATE, or DELETE statements.

• Usually enclosed within parentheses.
• The inner query (subquery) can be correlated (depends on outer query) or non-
correlated (independent).
Example: Find employees who earn more than the average salary:

SELECT EmployeeName
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);
Uses:
• Filtering data based on aggregate functions.
13
 • Comparing values across tables.
• Implementing complex logic that cannot be expressed in a single query.

25. For the relation schema below, give an expression in SQL for each of the queries that follows:
employee(employee-name, street, city)

• works(employee-name, company-name, salary)

• company(company-name, city)
• manages(employee-name, manager-name)

a) Find the names, street address, and cities of residence for all employees who work for the Company
‘RIL Inc.' and earn more than $10,000.

SELECT e.employee_name, [Link], [Link]

FROM employee e
JOIN works w ON e.employee_name = w.employee_name
WHERE w.company_name = 'RIL Inc.'
AND [Link] > 10000;
b) Find the names of all employees who live in the same cities as the companies for which they work.

SELECT e.employee_name
FROM employee e
JOIN works w ON e.employee_name = w.employee_name
JOIN company c ON w.company_name = c.company_name
WHERE [Link] = [Link];
c) Find the names of all employees who do not work for ‘KYS Inc.’. Assume that all people work for exactly
one company.

SELECT employee_name
FROM works
WHERE company_name != 'KYS Inc.';
d) Find the names of all employees who earn more than every employee of ‘SB Corporation'. Assume that
all people work for at most one company.

SELECT w.employee_name
FROM works w
WHERE [Link] > ALL (
SELECT salary
FROM works
WHERE company_name = 'SB Corporation'
);
e) List out number of employees company-wise in the decreasing order of number of employees.

SELECT company_name, COUNT(*) AS num_employees

FROM works
GROUP BY company_name
ORDER BY num_employees DESC;

26. Consider an EMPLOYEE file with 10000 records where each record is of size 80 bytes. The file is sorted
on employee number (15 bytes long), which is the primary key. Assuming un-spanned organization and
block size of 512 bytes compute the number of block accesses needed for selecting records based on
employee number if,

14
i. No index is used

ii. Single level primary index is used

iii. Multi-level primary index is used Assume a block pointer size of 6 bytes.

27. In the following tables foreign keys have the same name as primary keys except DIRECTED-BY, which
refers to the primary key ARTIST-ID. Consider only single-director movies.

• MOVIES(MOVIE-ID, MNAME, GENRE, LENGTH, DIRECTED-BY)

• ARTIST(ARTIST-ID, ANAME)
• ACTING(ARTIST-ID, MOVIE-ID)

Write SQL expressions for the following queries:

(a) Name(s) and director name(s) of movie(s) acted by ‘Jenny’.

SELECT DISTINCT [Link], [Link] AS Director_Name

FROM MOVIES M
JOIN ACTING A ON M.MOVIE_ID = A.MOVIE_ID
JOIN ARTIST Jenny ON A.ARTIST_ID = Jenny.ARTIST_ID AND [Link] = 'Jenny'
JOIN ARTIST D ON M.DIRECTED_BY = D.ARTIST_ID;

15
(b) Names of actors who have never acted with ‘Rony’

SELECT DISTINCT [Link]

FROM ARTIST A1
WHERE [Link] != 'Rony'
AND A1.ARTIST_ID NOT IN (
SELECT DISTINCT A2.ARTIST_ID
FROM ACTING A2
WHERE A2.MOVIE_ID IN (
SELECT MOVIE_ID
FROM ACTING A3
JOIN ARTIST R ON A3.ARTIST_ID = R.ARTIST_ID
WHERE [Link] = 'Rony'
)
);
(c) Count of movies genre-wise.

SELECT GENRE, COUNT(*) AS Movie_Count

FROM MOVIES
GROUP BY GENRE;
(d) Name(s) of movies with maximum length

SELECT MNAME
FROM MOVIES
WHERE LENGTH = (
SELECT MAX(LENGTH)
FROM MOVIES
);

28. Consider an EMPLOYEE file with 10000 records where each record is of size 80 bytes. The file is sorted
on employee number (15 bytes long), which is the primary key. Assuming unspanned organization, block
size of 512 bytes and block pointer size of 5bytes. Compute the number of block accesses needed for
retrieving an employee record based on employee number if (i) No index is used (ii) Multi-level primary
index is used.

16