DBMS Assignment 3 - SQL Solutions
[Link]. (CS) 2025-26
Question 1: LOGIN Table
Schema: LOGIN = ⟨userid : integer, username : string, logindate : date⟩
(i) Find the name of the user who logged in last.
SELECT username
FROM LOGIN
WHERE logindate = ( SELECT MAX ( logindate ) FROM LOGIN )
LIMIT 1;
(ii) Find the names of users who logged in exactly twice on at least one day.
SELECT DISTINCT username
FROM LOGIN
GROUP BY username , logindate
HAVING COUNT (*) = 2;
(iii) Find the names of users who logged in for two or more consecutive days.
SELECT DISTINCT L1 . username
FROM LOGIN L1
JOIN LOGIN L2
ON L1 . username = L2 . username
AND L2 . logindate = L1 . logindate + INTERVAL ’1 day ’;
Question 2: PLACEMENT Table
Schema: PLACEMENT = ⟨companyid : integer, companyname : string, email : string⟩
(i) Find companies without a valid email ID.
SELECT companyname
FROM PLACEMENT
WHERE email NOT LIKE ’% _@_ %. _ % ’
OR email IS NULL ;
(ii) Find companies with multiple comma-separated email IDs.
SELECT companyname
FROM PLACEMENT
WHERE email LIKE ’% ,% ’;
(iii) Find all duplicate email IDs present.
SELECT email
FROM PLACEMENT
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT (*) > 1;
1
Question 3: STUDY Table
Schema: STUDY = ⟨studentid : integer, name : string, age : integer, instructorid : integer⟩
(i) Find students whose surnames start with a vowel.
SELECT name
FROM STUDY
WHERE SUBSTRING ( name FROM POSITION ( ’ ’ IN name ) + 1 FOR 1)
IN ( ’A ’ , ’E ’ , ’I ’ , ’O ’ , ’U ’ , ’a ’ , ’e ’ , ’i ’ , ’o ’ , ’u ’) ;
(ii) Find students with the second highest age.
SELECT name
FROM STUDY
WHERE age = (
SELECT DISTINCT age
FROM STUDY
ORDER BY age DESC
LIMIT 1 OFFSET 1
);
(iii) Find students who are older than their instructors.
SELECT S1 . name
FROM STUDY S1
JOIN STUDY S2 ON S1 . instructorid = S2 . studentid
WHERE S1 . age > S2 . age ;
Question 4: FACEBOOK and X Tables
Schema:
FACEBOOK = ⟨employeeid : integer, ssn : integer, position : string, salary : real⟩
X = ⟨employeeid : integer, ssn : integer, position : string, salary : real⟩
(i) Find SSNs of employees who worked in both Facebook and X.
SELECT DISTINCT F . ssn
FROM FACEBOOK F
INNER JOIN X ON F . ssn = X . ssn ;
(ii) Find SSNs of employees who worked in multiple positions for Facebook but never in X.
SELECT ssn
FROM FACEBOOK
WHERE ssn NOT IN ( SELECT ssn FROM X )
GROUP BY ssn
HAVING COUNT ( DISTINCT position ) > 1;
2
Question 5: NATURAL Table
Schema: NATURAL = ⟨number : integer⟩
(i) Find the maximum number present.
SELECT MAX ( number ) AS max_number
FROM NATURAL ;
(ii) Find the median of all numbers present.
SELECT PERCENTILE_CONT (0.5) WITHIN GROUP ( ORDER BY number ) AS median
FROM NATURAL ;
(iii) Find the missing numbers in the sequence.
WITH RECURSIVE NumberSequence AS (
SELECT MIN ( number ) AS num FROM NATURAL
UNION ALL
SELECT num + 1
FROM NumberSequence
WHERE num < ( SELECT MAX ( number ) FROM NATURAL )
)
SELECT num AS missing_number
FROM NumberSequence
WHERE num NOT IN ( SELECT number FROM NATURAL ) ;
Question 6: Trigger for Insert Operations
Create a trigger to set control before the insert operations on a table. In case an insert
operation happens on it, a separate log table will keep a record of the inserted tuple along
with the time of insertion.
MainTable = ⟨id : integer, data : string⟩
InsertLog = ⟨log id : integer, table name : string, inserted id : integer, insert timestamp
: timestamp⟩
-- Assuming a log table named InsertLog exists
CREATE OR REPLACE FUNCTION log_insert_operation ()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO InsertLog ( table_name , inserted_id , insert_timestamp )
VALUES ( TG_TABLE_NAME , NEW . id , CURRENT_TIMESTAMP ) ;
RETURN NEW ;
END ;
$$ LANGUAGE plpgsql ;
CREATE TRIGGER be fore _inse rt_t rigge r
BEFORE INSERT ON MainTable
FOR EACH ROW
EXECUTE FUNCTION log_insert_operation () ;
3
Question 7: Cursor to Collect Comma-Separated Names
Show the usage of a cursor to collect a list a names, which are separated by comma, from a
table with the following schema:
UNIVERSITY = ⟨studentid : integer, studentname : string, address : string⟩
DO $$
DECLARE
student_cursor CURSOR FOR SELECT studentname FROM UNIVERSITY ORDER BY
studentid ;
student_rec RECORD ;
name_list TEXT := ’ ’;
is_first BOOLEAN := TRUE ;
BEGIN
OPEN student_cursor ;
LOOP
FETCH student_cursor INTO student_rec ;
EXIT WHEN NOT FOUND ;
IF is_first THEN
name_list := student_rec . studentname ;
is_first := FALSE ;
ELSE
name_list := name_list || ’ , ’ || student_rec . studentname ;
END IF ;
END LOOP ;
CLOSE student_cursor ;
RAISE NOTICE ’ Student Names : % ’ , name_list ;
END ;
$$ ;