One-to-One and Recursive relationship
Recursive relationship is a bit more complex and it won’t work on the website. To try queries in this section, we will use Workbench.
You can also run them in your Python program.
One-to-One Relationship
A one-to-one (1:1) relationship is a type of association between two entities where each instance of one entity is related to exactly one instance of the other, and vice versa. This setup is used when specific data is best organized across two tables to reduce redundancy or improve security. For example, in a database, a Person entity might have a one-to-one relationship with a Passport entity—each person has one unique passport, and each passport is assigned to only one person.
Modelling one-to-one relationship
Let’s consider this organizational chart. We can clarely see that each (one) department has a (one) head.
- 1:1 relationship is labeled
- A relationship descriptor
- Obvious relationships are not labeled
Querying One-to-One Relationship
GOOD NEWS! Querying one-to-one relationship works EXACTLY like the queries you already know :D .
We will use these new tables:
List the hotel name, first name and title of each hotel’s contact employee.
SELECT hotelName, employeeFName, employeeTitle
FROM employee JOIN hotel USING(employeeId);For reviews with a response, find the total number of reviews posted and their average response time in days for all reviews posted on Valentine’s day (2011-02-14). ((DATEDIFF(date2, date1) – calculates the difference between two dates)
SELECT COUNT(reviewDate), AVG(DATEDIFF(responseDate, reviewDate))
FROM review JOIN response USING(reviewId)
WHERE reviewDate = '2011-02-14';Recursive Relationship
A recursive relationship occurs when an entity is related to itself. This happens when instances of the same entity type are connected in a hierarchical or referential way. For example, in an Employee entity, an employee might manage another employee, creating a recursive “manages” relationship within the same table. This is useful for modeling organizational charts, family trees, or any structure with self-referencing links.
Modelling recursive relationship
Let’s go back to our organizational chart. There is more here…
- A recursive relationship relates an entity to itself
- Label recursive relationships
Querying recursive relationship
A recursive relationship in a database, also known as a self-join, is when a table is joined with itself to represent relationships within the same entity. This technique is often used to model hierarchical or parent-child relationships, such as employees managing other employees. To perform a self-join, table aliasing is required so that the same table can be referenced multiple times in a query. For instance, using the alias boss in:
FROM employee AS boss
allows the query to distinguish between the employee acting as the manager and the one being managed. This approach is essential for querying and understanding internal relationships within a single table.
Open your Workbench and find the name of Leo Lee’s boss.
SELECT boss.employeeFName, boss.employeeLName
FROM employee AS wrk JOIN employee AS boss
ON wrk.reportsTo = boss.employeeId
WHERE wrk.employeeLName = 'Lee'
AND wrk.employeeFName='Leo';Handling NULL values
Don’t confuse with blank or zero
Multiple meanings: - Unknown data - Inapplicable data - No value supplied - Value undefined
NULL creates confusion because the user must make an inference
Try the following and observe the difference:
SELECT "a" IN ("a", "b", ""); SELECT "c" IN ("a", "b", ""); SELECT "" IN ("a", "b", ""); SELECT NULL IN ("a", "b", "");Handling NULL values in SQL requires special care, as they represent unknown or missing data.
Comparisons with NULL always return NULL, not TRUE or FALSE, which can lead to unexpected results. For example, NULL = NULL is not TRUE, but NULL, making the query:
SELECT * FROM hotel WHERE hotelRooms = NULL;incorrect.
Instead, the correct way to check for NULL values is with the IS NULL operator, like:
SELECT * FROM hotel WHERE hotelRooms IS NULL;Exercise
List the independent hotels (independent hotels are those properties not affiliated with any brand).
SELECT hotelId, hotelName
FROM hotel
WHERE brandId IS NULL;BETWEEN and LIMIT
In SQL, BETWEEN, LIMIT, and OFFSET are commonly used clauses that help control and filter the data returned from queries. They provide ways to work with ranges, limit the amount of data retrieved, and manage how data is presented, especially when dealing with large datasets.
BETWEEN
BETWEEN is used to filter query results within a specific range. It works with numbers, dates, and text.
For example:
- Report all reviews between two dates
- Report all review and responses that occurred within one day of the review being posted
LIMIT
LIMIT restricts the number of rows returned by a query.
For example:
- For all the hotels that received at least 50 review. Report the first ten hotels ranked by overall rating.
LIMIT and OFFSET
OFFSET skips a specified number of rows before starting to return rows.
For example:
- Report the 10th hotel in the ranking above (not the first ten)
Examples
Report all review that occurred from 4.08.2011 to 24.08.2011. Order by review date
SELECT * FROM review
WHERE reviewDate BETWEEN '2011-08-04' AND '2011-08-24'
ORDER BY reviewDate DESC;Report all review that occurred from 4.08.2011 to 24.08.2011. Order by review date Show only 10 first rows.
SELECT * FROM review
WHERE reviewDate BETWEEN '2011-08-04' AND '2011-08-24'
ORDER BY reviewDate DESC
LIMIT 10;Report all review that occurred from 4.08.2011 to 24.08.2011. Order by review date. Show only 10 rows. Make sure your results start from the 9th row.
SELECT * FROM review
WHERE reviewDate BETWEEN '2011-08-04' AND '2011-08-24'
ORDER BY reviewDate DESC
LIMIT 10 OFFSET 8;Images and GIFs Disclaimer: Some of the images and GIFs used on this website are not owned by me. They are used for educational and illustrative purposes only. All rights belong to their respective owners. If you believe any content violates copyright, please contact me for prompt removal.