Which relational join returns all records when there is a matching key in either the left table (table1) or the right table (table2), filling in NULLs for mismatched rows on both sides?
💻 sql Snippet
SELECT Teacher.TeacherID, Teacher.Name, Class.ClassID
FROM Teacher
FULL OUTER JOIN Class
ON Teacher.TeacherID = Class.TeacherID;
A.Full outer join
B.Inner join
C.Right outer join
D.Left outer join
In which type of relational join are only those rows from two participating tables combined that share matching, identical values in their common join attribute?
💻 sql Snippet
SELECT *
FROM Teacher
INNER JOIN Class
ON Teacher.TeacherID = Class.TeacherID;
A.Inner join
B.Right outer join
C.Left outer join
D.Full outer join
Consider a database with a 'Teacher' table containing 6 rows and a 'Class' table containing 6 rows. When executing a LEFT OUTER JOIN from 'Teacher' to 'Class' on the common 'TeacherID' field, several rows in the final output display 'NULL' values under the class columns (such as ClassID, CourseCode, and DayofWeek). What does a 'NULL' value in these right-table columns signify under Left Outer Join mechanics?
💻 sql Snippet
SELECT Teacher.TeacherID, Teacher.Name, Class.ClassID, Class.CourseCode
FROM Teacher
LEFT OUTER JOIN Class
ON Teacher.TeacherID = Class.TeacherID;
A.The teacher exists in the database but is not currently assigned to teach any class.
B.There is a syntax error or a foreign key constraint violation in the join query.
C.The corresponding Class records have been permanently deleted from the physical storage.
D.The TeacherID values do not match any compatible database data type in either table.
Which relational join returns all records from the left table along with only the matching records from the right table, mapping unmatched right-side attributes to NULL?
💻 sql Snippet
SELECT Teacher.TeacherID, Teacher.Name, Class.ClassID
FROM Teacher
LEFT OUTER JOIN Class
ON Teacher.TeacherID = Class.TeacherID;
A.Left outer join
B.Inner join
C.Right outer join
D.Full outer join
Consider the relations 'Employees' and 'Departments' in an enterprise schema. Write an SQL query to retrieve FirstName, LastName, and DepartmentName for all employees by joining the tables on their shared 'DepartmentID' column.
💻 sql Snippet
SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName
FROM Employees
JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
A.SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName FROM Employees JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
B.SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName FROM Employees LEFT OUTER JOIN Departments ON Employees.DepartmentID!= Departments.DepartmentID;
C.SELECT Employees.FirstName, Employees.LastName, Departments.DepartmentName FROM Employees, Departments WHERE Employees.FirstName = Departments.DepartmentName;
D.SELECT DISTINCT FirstName, LastName, DepartmentName FROM Employees UNION Departments ON Employees.DepartmentID = Departments.DepartmentID;
Which type of relational join returns all records from the right table along with only the matching records from the left table, mapping unmatched left-side fields to NULL?
💻 sql Snippet
SELECT Teacher.TeacherID, Teacher.Name, Class.ClassID
FROM Teacher
RIGHT OUTER JOIN Class
ON Teacher.TeacherID = Class.TeacherID;
A.Full outer join
B.Inner join
C.Left outer join
D.Right outer join
Under standard relational database guidelines, which of the following statements represents a true property and definition of a Self Join operation?
💻 sql Snippet
SELECT a.stId, a.stName, b.stId, b.stName
FROM student a, student b
WHERE a.cr = b.stId;
A.An inner join is performed on two separate physical tables, and then attributes of only one table are retrieved.
B.The left table is joined with the right table on their primary keys, preserving all unmatched records.
C.The right table is joined with the left table using a temporary index on an un-indexed foreign key column.
D.A table is logically joined with itself to resolve unary recursive relationships using table aliases.
Consider the following SQL statement:
```sql
GRANT SELECT, UPDATE(Designation) ON Employee TO Amali, Hiruni WITH GRANT OPTION;
```
What are the exact privileges granted by this statement?
💻 sql Snippet
GRANT SELECT, UPDATE(Designation) ON Employee TO Amali, Hiruni WITH GRANT OPTION;
A.Grant permission to Amali only to retrieve data from the Employee table, and grant permission to Hiruni only to update the designation from the Employee table.
B.Grant permission to Hiruni in order to grant, select, and update permissions to Amali.
C.Grant permission to both Amali and Hiruni to retrieve data (SELECT) and update the Designation column of employees in the Employee table, with the ability to grant these privileges to other users.
D.Grant permission to Amali and Hiruni to update all data except designation in the Employee table.
Given the relational schema with two tables:
1. 'Teacher' table with attributes: {TeacherID, Name, Office, Phone, Email}
2. 'Class' table with attributes: {ClassID, CourseCode, DayofWeek, StartTime, EndTime, Venue, TeacherID}
Write an SQL query to perform an INNER JOIN between these tables to display each teacher's Name alongside their scheduled CourseCode, DayofWeek, and Venue. Show the resulting schema and describe how the database matches records on their common join attribute.
💻 sql Snippet
SELECT Teacher.Name, Class.CourseCode, Class.DayofWeek, Class.Venue
FROM Teacher
INNER JOIN Class
ON Teacher.TeacherID = Class.TeacherID;
A.SELECT Teacher.Name, Class.CourseCode, Class.DayofWeek, Class.Venue FROM Teacher INNER JOIN Class ON Teacher.TeacherID = Class.TeacherID;
B.SELECT Teacher.Name, Class.CourseCode, Class.DayofWeek, Class.Venue FROM Teacher CROSS JOIN Class WHERE Teacher.Name = Class.Venue;
C.SELECT Teacher.Name, Class.CourseCode, Class.DayofWeek, Class.Venue FROM Teacher LEFT JOIN Class ON Teacher.TeacherID!= Class.TeacherID;
D.SELECT DISTINCT Name, CourseCode, DayofWeek FROM Teacher UNION Class ON TeacherID = TeacherID;
Analyze the following SQL access control statement:
```sql
GRANT SELECT, UPDATE(Designation) ON Employee TO Amali, Hiruni WITH GRANT OPTION;
```
Which of the following is correct regarding the privileges granted by this statement?
💻 sql Snippet
GRANT SELECT, UPDATE(Designation) ON Employee TO Amali, Hiruni WITH GRANT OPTION;
A.Grants SELECT privilege to Amali only and UPDATE(Designation) privilege to Hiruni only.
B.Grants Hiruni authorization to grant SELECT and UPDATE privileges directly to Amali.
C.Grants SELECT privilege on the entire Employee table, and UPDATE privilege on the Designation column, to both Amali and Hiruni, along with the ability to grant these privileges to others.
D.Grants privileges to both users to update all columns of the Employee table except for the Designation column.
