SQL Query Techniques

Quiz 1: SQL Focus

• SELECT * FROM staff ORDER BY lastname ASC lists staff details with those having last names alphabetically at the beginning of the list.
• SELECT name FROM course WHERE level IN (3, 4) AND credits > 10 AND semester != 2 finds courses at level 3 or 4 with more than 10 credits, excluding semester 2.
• SELECT lastname FROM staff WHERE phone IS NULL OR office IS NULL identifies staff members who have a missing phone number or office number.
• SELECT bc, name FROM allmarks17, course WHERE allmarks17.bc = course.bc finds courses taken by students in 2017 by joining the allmarks17 table with the course table.
• SELECT name FROM course WHERE bc IN (SELECT bc FROM allmarks17) AND cid NOT IN (SELECT cid FROM lecturing WHERE year = 2017) lists courses with exams taken in 2017 but not taught in 2017.
• SELECT lastname FROM staff WHERE phone IS NOT NULL EXCEPT SELECT lastname FROM staff WHERE office IS NOT NULL finds staff with a phone number but no office number.
• SELECT cid FROM lecturing WHERE year = 2015 AND cid NOT IN (SELECT cid FROM lecturing WHERE year < 2015) lists courses new in 2015 that were not taught earlier.
• SELECT cid, AVG(numbers), COUNT(DISTINCT sid) FROM lecturing GROUP BY cid lists average enrollment numbers and distinct staff count for courses
• SELECT cid, year FROM lecturing GROUP BY cid, year HAVING COUNT(*) > 1 finds courses repeated in the same year.
• SELECT COUNT(DISTINCT student), COUNT(DISTINCT bc) FROM allmarks17 computes the number of distinct students and courses in allmarks17.
• SELECT bc, AVG(CASE WHEN year = 2016 THEN mark END), AVG(CASE WHEN year = 2017 THEN mark END) FROM allmarks WHERE year IN (2016, 2017) GROUP BY bc HAVING COUNT(DISTINCT year) = 2 lists courses with average marks for both 2016 and 2017.
• SELECT lastname FROM staff WHERE sid NOT IN (SELECT sid FROM lecturing) lists staff who did not lecture.

Quiz 2: SQL & Relational Algebra Focus

• SELECT name, COUNT(student), AVG(mark), MAX(mark), MIN(mark), STDDEV(mark) FROM course, allmarks18 WHERE course.bc = allmarks18.bc GROUP BY name ORDER BY name shows course statistics (count of students, average mark, max mark, min mark, standard deviation of mark) ordered by name.
• SELECT s1.lastname FROM staff s1, staff s2 WHERE s1.office = s2.office AND s1.sid != s2.sid finds staff members who share an office, using a self join to compare staff records.
• The relational algebra expression σ_{D=p}(π_{X,Y}(T1) ⋈ π_{X,D}(T2)) results in 2 tuples, after projecting, joining, and filtering.
• π_{name}(σ_{lastname='Jung' ∧ (year=2013 ∨ year=2014)}(staff ⋈ lecturing ⋈ course)) relational algebra expression retrieves the names of staff members with the last name 'Jung' who lectured in either 2013 or 2014 and joins staff, lecturing, and course tables.
• SELECT l1.cid FROM lecturing l1, lecturing l2 WHERE l1.cid = l2.cid AND l1.year = 2016 AND l2.year = 2017 AND l1.numbers < l2.numbers lists the courses with increased enrollment from 2016 to 2017 by comparing the number of students enrolled in the same course in those years.
• SELECT bc, COUNT(CASE WHEN mark < 40 THEN 1 END) / COUNT(*)::FLOAT FROM allmarks18 GROUP BY bc calculates the hard-failure rate for courses in allmarks18.
• π_{lastname}(σ_{numbers > 100}(staff ⋈ lecturing)) returns the last names of staff teaching courses with over 100 students through joining the staff and lecturing tables.
• SELECT name FROM lecturing, course WHERE lecturing.cid = course.cid AND numbers > 100 EXCEPT SELECT name FROM course WHERE level = 1 SQL query matches the relational algebra expression π_{name}(σ_{numbers > 100}(lecturing ⋈ course)) - π_{name}(σ_{level=1}(course)).
• The natural inner join of tables T1 and T2 results in 2 tuples.
• The natural full outer join of tables T1 and T2 results in 7 tuples.

Quiz 3: ER Modeling & Logical Design Focus

• In a tourist information system, the multiplicity between City and County is (1,1) from City to County, and (0,many) from County to City.
• For a hotel database, the Room relationship to Accommodation is best described as a weak entity with a (1,many) relationship to Accommodation.
• In a car rental system, the multiplicity between Customer and Vehicle Class is (0,many) both ways.
• The schema that correctly represents the Attraction table is Attraction(attraction_id, name, city_id°, street_id).
• CREATE TABLE Street (street_id INT PRIMARY KEY, name VARCHAR(50), length INT, city_id INT REFERENCES City(city_id) ON DELETE CASCADE) is the SQL CREATE statement thatmatches the Street table.
• In the hotel system, the multiplicity between Hotel and Meeting Room is (0,many) from Hotel to Meeting Room, and (1,1) from Meeting Room to Hotel.
• In the UN system, the multiplicity between Country and Alliance is (0,many) both ways.
• In the car rental system, Employee eligibility is modeled as a relationship between Commercial Customer and Employee.
• The Rental table is represented by the schema Rental(rental_id, customer_id, vehicle_id, class, start_date, end_date).
• The Commercial Customer’s eligible Employees is represented by the schema Eligible(customer_id, employee_id).