DBMS Module 3 - Answers
1. SQL SELECT Commands and Set Operations
The SELECT statement is used to query data from a database.
SQL set operations combine results from multiple queries:
- UNION: Combines results of two queries, removing duplicates.
Example: SELECT name FROM emp1 UNION SELECT name FROM emp2;
- INTERSECT: Returns common rows.
Example: SELECT name FROM emp1 INTERSECT SELECT name FROM emp2;
- EXCEPT: Returns rows from the first query not present in the second.
Example: SELECT name FROM emp1 EXCEPT SELECT name FROM emp2;
2. Nested Query with Aggregate Operators
Nested queries (subqueries) are queries inside another SQL query.
Example:
SELECT sname FROM Sailors WHERE age > (SELECT AVG(age) FROM Sailors);
Aggregate functions include COUNT, SUM, AVG, MIN, MAX.
Example: SELECT COUNT(*) FROM Sailors WHERE rating > 7;
3. Handling NULL Values
NULL represents missing or unknown data.
- IS NULL: Checks if a value is NULL.
- IS NOT NULL: Checks if a value is not NULL.
Aggregate functions ignore NULL values.
Example: SELECT AVG(age) FROM Sailors WHERE age IS NOT NULL;
4. SQL JOIN Operators
- INNER JOIN: Returns records with matching values in both tables.
- LEFT JOIN: Returns all from left table, matching from right if any.
- RIGHT JOIN: Returns all from right table, matching from left if any.
- FULL JOIN: Returns all records when there is a match in either table.
Example: SELECT sname, bname FROM Sailors JOIN Reserves USING(sid);
5. Relational Algebra Operations
Selection (sigma): Chooses rows - sigma(age>25)(Sailors)
Projection (pi): Chooses columns - pi(sname, age)(Sailors)
Join (join): Combines tables - Sailors join Reserves
6. Set Operations in Relational Algebra
Union, Intersection, Difference.
Similar to SQL but operate on sets of tuples, not tables directly.
Example: pi(sname)(Sailors) UNION pi(sname)(Boats)
7. TRC and DRC
Tuple Relational Calculus (TRC): Uses tuple variables.
Example: {t | t in Sailors AND [Link] > 25}
Domain Relational Calculus (DRC): Uses domain variables.
Example: {<sname> | EXISTS sid, rating, age (Sailors(sid, sname, rating, age) AND age > 25)}
8. PL/SQL, Assertions, Triggers
PL/SQL: Procedural extension for SQL.
Example:
BEGIN
FOR s IN (SELECT sname, age FROM Sailors WHERE rating > 7) LOOP
DBMS_OUTPUT.PUT_LINE([Link] || ' ' || [Link]);
END LOOP;
END;
Assertion: Constraint on a table - CREATE ASSERTION CheckAge CHECK (age >= 18);
Trigger: Executes automatically on events - e.g. preventing deletion if reserved.
SQL & Relational Algebra Problems
- SELECT sname, rating FROM Sailors WHERE age > 25;
- SELECT rating, AVG(age) FROM Sailors GROUP BY rating;
- SELECT sname FROM Sailors s, Boats b, Reserves r WHERE [Link]=[Link] AND [Link]=[Link] AND color IN ('red','green');
- SELECT sname FROM Sailors s WHERE [Link] IN (SELECT [Link] FROM Reserves r1, Boats b1 WHERE
[Link]=[Link] AND [Link]='red')
AND [Link] IN (SELECT [Link] FROM Reserves r2, Boats b2 WHERE [Link]=[Link] AND [Link]='green');
- SELECT sname FROM Sailors s WHERE [Link] IN (SELECT [Link] FROM Reserves r1, Boats b1 WHERE
[Link]='red' AND [Link]=[Link])
AND [Link] NOT IN (SELECT [Link] FROM Reserves r2, Boats b2 WHERE [Link]='green' AND [Link]=[Link]);
- SELECT sname FROM Sailors WHERE age > (SELECT AVG(age) FROM Sailors);
- SELECT sname FROM Sailors s WHERE rating > (SELECT AVG(rating) FROM Sailors s2 WHERE [Link]=[Link]);
- SELECT * FROM Reserves WHERE day IS NULL;
- SELECT sname, bname, day FROM Sailors s, Boats b, Reserves r WHERE [Link]=[Link] AND [Link]=[Link];
- SELECT sid FROM Sailors WHERE age > 20 AND sid NOT IN (SELECT [Link] FROM Reserves r, Boats b WHERE
[Link]=[Link] AND color='red');
Relational Algebra:
pi(sname)(sigma(age>25)(Sailors))
pi(sname)(sigma(color='red')(Sailors join Reserves join Boats))
pi(sname)(sigma(color='red')(Sailors join Reserves join Boats)) INTERSECT pi(sname)(sigma(color='green')(Sailors join
Reserves join Boats))
pi(sname)(sigma(color='red')(Sailors join Reserves join Boats)) MINUS pi(sname)(sigma(color='green')(Sailors join
Reserves join Boats))
pi(sname)(Sailors join Reserves)
Trigger:
CREATE OR REPLACE TRIGGER prevent_boat_delete
BEFORE DELETE ON Boats
FOR EACH ROW
WHEN (EXISTS (SELECT 1 FROM Reserves WHERE bid = :[Link]))
BEGIN
RAISE_APPLICATION_ERROR(-20001, 'Boat is currently reserved, cannot delete.');
END;
PL/SQL Block:
DECLARE
CURSOR c IS SELECT sname, age FROM Sailors WHERE rating > (SELECT AVG(rating) FROM Sailors);
v_name [Link]%TYPE;
v_age [Link]%TYPE;
BEGIN
OPEN c;
LOOP
FETCH c INTO v_name, v_age;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Age: ' || v_age);
END LOOP;
CLOSE c;
END;