Ch 31: SQL Subqueries (Nested & Correlated)

Consider the following relational database tables mapped from an e-commerce Entity Relationship Diagram (ERD): - Customer (CusID, CusName, CusAddress) - Order (OrderID, OrderDate, Details, CusID, EmpID) - Bill (BillID, TotalPrice, CusID, OrderID) - DeliveryMan (EmpID, EmpName) Write an SQL query to retrieve the OrderID, OrderDate, CusName, and TotalPrice for all orders placed by the customer named 'Ali Tahir'.

SELECT o.OrderID, o.OrderDate, c.CusName, b.TotalPrice
FROM [Order] o
INNER JOIN Customer c ON o.CusID = c.CusID
INNER JOIN Bill b ON o.OrderID = b.OrderID
WHERE c.CusName = 'Ali Tahir';

Consider the following Teacher and Class tables in MS SQL Server: Teacher Table: | TeacherID | Name | Office | Phone | Email | |---|---|---|---|---| | T001 | Dr. Najma Waheed | F001 | 9125 | najma@uni.edu.pk | | T002 | Dr. Nouman Masood | F003 | NULL | nayyer@uni.edu.pk | | T003 | Dr. Farhana Khan | F002 | 9222 | farhana@uni.edu.pk | Class Table: | ClassID | CourseCode | DayofWeek | TimeStart | TimeEnd | Venue | TeacherID | |---|---|---|---|---|---|---| | A001 | CS403 | Monday | 09:00 | 10:00 | A Block | T003 | | B002 | CS301 | Tuesday | 09:00 | 10:00 | B Block | T005 | Predict the exact table output of executing the following SQL join query: ```sql SELECT t.TeacherID, t.Name, c.ClassID, c.CourseCode, c.Venue FROM Teacher t LEFT OUTER JOIN Class c ON t.TeacherID = c.TeacherID; ```

SELECT t.TeacherID, t.Name, c.ClassID, c.CourseCode, c.Venue
FROM Teacher t
LEFT OUTER JOIN Class c ON t.TeacherID = c.TeacherID;