SQL Exercise 3

Use schema Operating System Schema

-- ================================================
-- SQL EXERCISE: Working with a Linux File System Schema
-- ================================================

USE operatingsystem;

-- ========================================================
-- Task 1: List all users with their main group.
-- Output columns: user_name, main_group_label
-- ========================================================
-- (HINT: JOIN users_bt with groups)
-- ========================================================


-- ========================================================
-- Task 2: Find all files and their sizes (memory_allocation).
-- Output columns: file_name, memory_allocation
-- ========================================================
-- (HINT: JOIN files with storage_objects_bt)
-- ========================================================


-- ========================================================
-- Task 3: Show all files that "alice" has direct read permission on.
-- Output columns: file_name, permission_code
-- ========================================================
-- (HINT: JOIN users_bt, allocations_jt, storage_objects_bt)
-- ========================================================


-- ========================================================
-- Task 4: Find all groups where "alice" is a member (including secondary groups).
-- Output columns: user_name, group_label
-- ========================================================
-- (HINT: Consider both main_group_id and groups_has_users_jt)
-- ========================================================


-- ========================================================
-- Task 5: List all files under the /etc directory (non-recursive).
-- Output columns: file_name, parent_directory_name
-- ========================================================
-- (HINT: Look at storage_objects_bt where parent_dir_id = /etc)
-- ========================================================


-- ========================================================
-- Task 6: Use a recursive CTE to list all files and directories under "/home/alice".
-- Output columns: level, object_name, object_type
-- ========================================================
-- level = depth starting at 0 for /home/alice
-- object_type = 'directory' or 'file'
-- ========================================================
-- (HINT: Start with /home/alice in CTE, recurse down parent_dir_id)
-- ========================================================


-- ========================================================
-- Task 7: Find the deepest directory in the entire file system.
-- Output columns: dir_name, depth
-- ========================================================
-- (HINT: Recursive CTE to traverse from root (/) downward)
-- ========================================================


-- ========================================================
-- Task 8: Find the longest file path in the file system.
-- Output columns: full_path, depth
-- ========================================================
-- Example output: /home/alice/docs/notes.txt
-- ========================================================
-- (HINT: Recursive CTE, build path as you recurse, filter only "files")
-- ========================================================


-- ========================================================
-- Task 9: Show the file system tree for "/usr" formatted with indentation.
-- Output columns: indented_name
-- ========================================================
-- Example output:
-- /usr
--   bin
--     ls
--     cat
--   lib
--     libc.so
-- ========================================================
-- (HINT: Recursive CTE, repeat('  ', depth) for indentation)
-- ========================================================


-- ========================================================
-- Task 10: List all files accessible by the "students" group,
-- including inherited group memberships.
-- Output columns: file_name, permission_code, granted_to
-- ========================================================
-- granted_to should show whether permission was direct to user or group.
-- ========================================================
-- (HINT: JOIN allocations_jt with both users and groups, UNION if needed)
-- ========================================================