DMDD HW-3
pdf
keyboard_arrow_up
School
Northeastern University *
*We aren’t endorsed by this school
Course
DAMG6210
Subject
Information Systems
Date
Apr 3, 2024
Type
Pages
20
Uploaded by MajorLoris4143
DMDD HW-1
Part 1. Solution:
1. a.
This list doesn't meet the requirements of 1NF because it breaks the atomicity rule. The Email, Phone1, PhoneType1, Phone2, and PhoneType2 fields can hold multiple values for one student, making them repeating groups. Moreover, having multiple email and phone fields suggests a lack of normalization.
b.
To achieve 1NF, you'd establish a separate table for contact information. This table would include fields such as Student_ID, Email, Phone, and MajorOrSchool. Each student would be assigned a unique Student_ID, and their contact details along with their major or school information would be stored in this normalized table.
2. a.
This list doesn't meet 1NF criteria because the Location and Items fields hold multiple values. Ideally, each row should denote a single item at a single location, but in this dataset, multiple items at one location are grouped together. b.
To achieve 1NF, you'd establish a single table with fields such as Errand_ID (as the primary key), Location, and Item. Each entry in this table would represent a single errand, specifying one item at one location.
3. a.
This table fails to meet 2NF standards due to partial dependencies. While the primary key (Errand_ID) determines the Location, the Item is reliant on both the Location and the Errand_ID.
b.
To achieve 2NF, you'd divide the table into two separate tables: one for Errands (Errand_ID, Location) and another for Errand_Items (Errand_ID, Item). This restructuring ensures each table represents a distinct entity, eliminating any partial dependencies.
4. a. The table fails to meet the criteria of 3NF due to the presence of transitive dependencies. Specifically, the Employee column determines both the Project and the Department, although these two attributes are unrelated to each other. b. To achieve 3NF, you'd divide the table into three distinct tables: one for Employees (Employee), another for Projects (Project, Department), and a third for Employee_Project_Assignment (Employee, Project). This restructuring ensures each table represents a unique entity, eliminating any transitive dependencies.
DMDD HW-1
5. a.
The table's structure doesn't comply with 5NF due to multi-valued dependencies. As each individual can prepare multiple dishes, and each dish necessitates various utensils, it establishes a many-to-many correlation.
b.
To achieve 5NF, you'd establish distinct tables for Person, Food, and Tool, alongside an additional table to articulate their associations (Person_Food_Tool). This approach ensures that each table signifies a singular entity, effectively eradicating multi-valued dependencies.
6. - First Normal Form:
Columns cannot contain repeating groups.
- Second Normal Form:
Every determinant is a candidate key.
- Third Normal Form:
No transitive dependencies.
- Boyce/Codd Normal Form:
Every determinant is a candidate key.
- Fourth Normal Form:
The table contains no multi-valued dependencies.
- Fifth Normal Form:
It does not contain unrelated multi-valued dependencies.
- Domain/Key Normal Form:
Each column must contain a single value.
DMDD HW-1
Part 2. Solution:
1. What are the components that make up a table in the relational model?
In the relational model, a table comprises rows and columns. Each column denotes a characteristic of the entity under consideration, while each row signifies a distinct occurrence or entry of that entity. Example: -
Columns:
Depict attributes of the entity. For instance, in a table for "Students," columns could represent data like "StudentID," "FirstName," "LastName," etc. -
Rows:
Represent individual instances or records of the entity. For instance, a row within the "Students" table might hold information for a particular student, such as (002474177, "Hritik", "Singh").
2. What requirements must a relation meet to be in compliance to the first normal form?
To meet the requirements of the first normal form (1NF), a relation needs to ensure that its values are atomic, meaning each cell holds only one value, not a collection. Additionally, every column must have a distinct name, and the arrangement of rows and columns should not impact the data's integrity. Example: -
Atomic values:
Every cell within the table should contain a solitary value. For instance, in a "PhoneNumbers" table, each entry in the "PhoneNumber" column ought to contain only one phone number, not multiple numbers separated by commas. -
Unique column names:
Each column should possess a unique name. For example, in a "Customers" table, it's inappropriate to have two columns labeled "Name".
3.
How does a one-to-many relationship differ from a many-to-many relationship?
In a one-to-many relationship, one entity instance can relate to multiple instances of another entity, while each instance of the second entity relates to only one instance of the first entity. Conversely, in a many-to-many relationship, multiple instances of one entity can connect with multiple instances of another entity, and vice versa. This often necessitates an intermediary table, referred to as a junction or associative table, to manage the relationship.
Example:
-
One-to-many:
In this type of relationship, one entity instance can have associations with several instances of another entity, whereas each instance of the second entity is linked to just one instance of the first entity. For instance, a teacher can instruct multiple students, but each student has only one teacher.
-
Many-to-many:
In this scenario, numerous instances of one entity can be linked with various instances of another entity, and vice versa. For example, students can enroll in multiple courses,
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
DMDD HW-1
and each course can have multiple students. To represent such a relationship, a junction table is commonly utilized.
4. You are creating a data model for a MySQL database. You identify the entities that you found in
the business rules. You then group those entities into categories of related data. What step
should you take next?
After identifying and grouping entities, the subsequent step involves defining the connections between these entities. This includes specifying the nature of their relationships (such as one-to-one, one-to-
many, or many-to-many) and implementing foreign keys to uphold these connections. For instance, if we have entities like "Customers" and "Orders," we would establish a one-to-many relationship where each order is associated with a single customer.
5.
How are many-to-many relationships implemented in MySQL?
In MySQL, many-to-many relationships are commonly implemented through a junction table. For instance, in a library database where books may have multiple authors and authors can contribute to multiple books, we'd create three tables: "Books," "Authors," and a junction table like "BooksAuthors." This junction table would include columns such as "BookID" and "AuthorID," facilitating the many-to-
many relationship between books and authors. Each entry in this table signifies a connection between a book and an author.
DMDD HW-1
Part 3. Solution:
1. Create an SQL statement that exports data from the Produce table to a text file named Apples.txt. The file should be saved to the folder associated with the test database. The rows exported should include only the ProdName, Variety, and InStock columns and only those rows that contain a ProdName value of Apples. The exported data should be copied to the text file in a default format.
Solution:
SELECT
ProdName, Variety, InStock
INTO OUTFILE '/path/to/test_database_folder/Apples.txt'
FIELDS TERMINATED BY '\t' LINES TERMINATED BY
'\n'
FROM
Produce
WHERE
ProdName = 'Apples';
2. Create an SQL statement that exports data from the Produce table to a text file named Oranges.txt. The file should be saved to the folder associated with the test database. The rows exported should include only the ProdName, Variety, and InStock columns and only those rows that contain a ProdName value of Oranges. The exported data should be copied to the text file in a default format, except that the fields should be terminated by a comma (,) and should be enclosed by an asterisk (*).
Solution: SELECT CONCAT (ProdName, '*', Variety, '*', InStock)
INTO OUTFILE '/path/to/test_database_folder/Oranges.txt'
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
FROM
Produce
WHERE
ProdName = 'Oranges';
3. Create an SQL statement that creates a table named Produce2. The table should be identical to the Produce table, except that it should not include the ProdID column. When creating the table, copy data from the Produce table to the Produce2 table. The copied data should include only the ProdName, Variety, and InStock columns and only those rows that contain a ProdName value of Cherries.
Solution:
CREATE TABLE Produce2 AS
SELECT
ProdName, Variety, InStock
FROM Produce
WHERE
ProdName = 'Cherries';
DMDD HW-1
4. Create an SQL statement that adds data to the Produce2 table that you created in Step 3. The data should be made up of ProdName, Variety, and InStock values from the Produce table. In addition, the data should include only those rows that contain a ProdName value of Mushrooms.
Solution:
INSERT INTO Produce2 (ProdName, Variety, InStock)
SELECT ProdName, Variety, InStock
FROM Produce
WHERE ProdName = 'Mushrooms';
5. Create an SQL statement that adds data to the Produce2 table that you created in Step 3. The data should be retrieved from the Apples.txt file that you created in Step 1.
Solution:
LOAD DATA INFILE
'/path/to/test_database_folder/Apples.txt'
INTO TABLE
Produce2
FIELDS TERMINATED
BY
'\t'
LINES TERMINATED BY '\n';
6. Create an SQL statement that adds data to the Produce2 table that you created in Step 3. The data should be retrieved from the Oranges.txt file that you created in Step 2.
Solution:
LOAD DATA INFILE
'/path/to/test_database_folder/Oranges.txt'
INTO TABLE
Produce2
FIELDS TERMINATED BY
','
LINES TERMINATED BY '\n';
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
DMDD HW-1
Part 4. Solution: 1.
Your friend runs a bookstore and uses a simple database to store details of books, authors, and
booksellers. She thinks the database might need a bit of a tune-up. Figure 4-2 shows its current
structure, which consists of just one table containing two columns: Column Name and Data Type.
Using what you learned about normalization, see if you can improve the current structure.
Table: Books
BookID (PK, AI) BookTitle (varchar) ISBN (varchar) YearPublished (int) PublisherID (FK) BookSellerID (FK) Table: Publishers
PublisherID (PK, AI) PublisherCode (int) PublisherName (varchar)
PublisherAddress (varchar)
Table: Books_Authors BookID (FK) AuthorID (FK) Table: Authors AuthorID (PK, AI) AuthorName (varchar) AuthorAddress (varchar)
Table: Booksellers
BookSellerID (PK, AI) BookSellerName (varchar)
BookSellerTelNo (varchar)
DMDD HW-1
In this representation:
- (PK) denotes Primary Key
- (FK) denotes Foreign Key
- (AI) denotes Auto Increment
In this revised structure:
-
The Books table contains details about various books, such as their titles, ISBNs, publication years, and references to publishers and booksellers via foreign keys.
-
The Authors table contains information regarding authors, including their names and addresses. -
The Publishers table contains data about publishers, including their codes, names, and addresses.
-
The Books_Authors table establishes a many-to-many relationship between books and authors, enabling multiple authors to be linked with multiple books.
-
The Booksellers table includes information about booksellers, including their names and phone numbers.
DMDD HW-1
2. When improving the Film Club database, you added various constraints as well as primary and
foreign keys. You didn’t, however, add a FOREIGN KEY constraint to the FavCategory table.
Create and execute the SQL required to enforce all its relationships with the other tables.
Solution:
To enforce the relationships between the FavCategory
table and other tables in the Film Club database, we need to add a FOREIGN KEY constraint in the FavCategory
table that references the primary keys of the related tables. Assuming that FavCategory
has a column named CategoryID
as its primary key, and it has relationships with other tables such as Films
, ClubMembers
, Screenings
, etc., we can add FOREIGN KEY constraints as follows:
ALTER TABLE
FavCategory
ADD CONSTRAINT
FK_FavCategory_Films
FOREIGN KEY
(FilmID) REFERENCES
Films(FilmID);
ALTER TABLE
FavCategory
ADD CONSTRAINT
FK_FavCategory_ClubMembers
FOREIGN KEY
(MemberID) REFERENCES ClubMembers(MemberID);
ALTER TABLE
FavCategory
ADD CONSTRAINT
FK_FavCategory_Screenings
FOREIGN KEY
(ScreeningID
) REFERENCES
Screenings(ScreeningID);
This script adds FOREIGN KEY constraints to the FavCategory
table, ensuring that the values in the FilmID
, MemberID
, and ScreeningID
columns correspond to existing primary key values in the related tables (
Films
, ClubMembers
, Screenings
, etc.).
After executing these SQL statements, the database will enforce referential integrity between the FavCategory
table and other related tables, preventing orphaned records and ensuring data consistency across the database.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
DMDD HW-1
Part 5. Solution: Sales Orders Database
1. “List the customers who ordered a helmet together with the vendors who provide helmets.” (Hint: This involves creating a UNION of two complex JOINs.)
Solution:
SELECT
FirstName, LastName, 'Customer' AS Type FROM Customers
UNION
SELECT
FirstName, LastName, 'Employee' AS Type FROM Employees;
Entertainment Agency Database
1. “Display a combined list of customers and entertainers.” (Hint: Be careful to create an expression for one of the names so that you have the same number of columns in both SELECT statements.)
Solution: SELECT
FirstName, LastName, 'Customer' AS Type
FROM
Customers
UNION
SELECT
EntertainerName AS FirstName, NULL AS LastName, 'Entertainer' AS Type
FROM
Entertainers;
2. “Produce a list of customers who like contemporary music together with a list of entertainers who play contemporary music.” (Hint: You need to UNION two complex JOINs to solve this one.)
Solution: SELECT
FirstName, LastName, 'Customer' AS Type
FROM
Customers
INNER JOIN CustomerPreferences ON
Customers.CustomerID = CustomerPreferences.CustomerID
WHERE
CustomerPreferences.MusicPreference = 'Contemporary'
UNION
SELECT
EntertainerName AS
FirstName, NULL AS LastName, 'Entertainer' AS
Type
FROM
Entertainers
INNER JOIN EntertainerSpecialties ON
Entertainers.EntertainerID = EntertainerSpecialties.EntertainerID
WHERE
EntertainerSpecialties.Specialty = 'Contemporary Music';
DMDD HW-1
School Scheduling Database
1. “Create a mailing list for students and staff, sorted by ZIP Code.” (Hint: Try using a relative column number for the sort.) Solution:
SELECT
StudentFirstName AS
FirstName, StudentLastName AS
LastName, StudentZipCode AS
ZIPCode, 'Student' AS Type
FROM
Students
UNION
SELECT
StaffFirstName AS
FirstName, StaffLastName AS
LastName, StaffZipCode AS
ZIPCode, 'Staff' AS
Type
FROM
Staff
ORDER BY
3;
Bowling League Database
1. “Find the bowlers who had a raw score of 165 or better at Thunderbird Lanes combined with bowlers who had a raw score of 150 or better at Bolero Lanes.”
Solution:
(
SELECT
BowlerName, RawScore
FROM Bowlers
WHERE
Lane = 'Thunderbird' AND RawScore >= 165)
UNION
(
SELECT
BowlerName, RawScore
FROM
Bowlers
WHERE
Lane = 'Bolero' AND RawScore >= 150);
2. Can you explain why the row counts are different in the previous solution queries?
Solution:
(SELECT BowlerName, RawScore
FROM
Bowlers
WHERE
Lane = 'Thunderbird' AND RawScore >= 165)
UNION ALL
DMDD HW-1
(
SELECT
BowlerName, RawScore
FROM
Bowlers
WHERE
Lane = 'Bolero' AND RawScore >= 150);
When employing UNION, duplicate rows are automatically eliminated by the database engine. For instance, if a bowler achieves a score of 165 or higher at Thunderbird Lanes and also scores 150 or better at Bolero Lanes, their details would show up in both result sets. However, with UNION, duplicates are omitted, leading to a smaller combined result set. To retain all rows, including duplicates, we can opt for UNION ALL instead. This ensures that all rows from both sets are included, resulting in a higher total row count. Therefore, using UNION ALL in the initial query would likely yield a row count closer to that of the solution using a WHERE clause.
Recipes Database
1. “Display a list of all ingredients and their default measurement amounts together with ingredients used in recipes and the measurement amount for each recipe.”
Solution:
SELECT
Ingredients.IngredientName, Ingredients.DefaultMeasurement AS
Measurement
FROM
Ingredients
UNION ALL
SELECT
Ingredients.IngredientName, Recipe_Ingredients.Measurement
FROM
Ingredients
INNER JOIN Recipe_Ingredients ON Ingredients.IngredientID = Recipe_Ingredients.IngredientID;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
DMDD HW-1
Part 6. Solution: Sales Orders Database
1. “Display products and the latest date each product was ordered.”
Solution:
SELECT
ProductID, MAX
(OrderDate) AS
LatestOrderDate
FROM
OrderDetails
GROUP BY
ProductID;
2. “List customers who ordered bikes.”
Solution:
SELECT DISTINCT Customers.CustomerID, Customers.FirstName, Customers.LastName
FROM
Customers
INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails ON
Orders.OrderNumber = OrderDetails.OrderNumber
INNER JOIN Products ON
OrderDetails.ProductID = Products.ProductID
WHERE
Products.Category = 'Bikes';
3. “Find all customers who ordered a bicycle but did not order a helmet.”
Solution:
SELECT DISTINCT
C.CustomerID, C.FirstName, C.LastName
FROM
Customers C
INNER JOIN
Orders O ON C.CustomerID = O.CustomerID
INNER JOIN
OrderDetails OD ON O.OrderNumber = OD.OrderNumber
INNER JOIN
Products P ON OD.ProductID = P.ProductID
WHERE
P.Category = 'Bikes'
AND NOT EXISTS
(
DMDD HW-1
SELECT 1
FROM
OrderDetails OD2
INNER JOIN
Products P2 ON OD2.ProductID = P2.ProductID
WHERE
OD2.OrderNumber = O.OrderNumber
AND
P2.Category = 'Helmets'
);
4. “What products have never been ordered?”
Solution:
SELECT
ProductID, ProductName
FROM Products
WHERE
ProductID NOT IN (
SELECT DISTINCT ProductID
FROM
OrderDetails
);
Entertainment Agency Database
1. “Show me all entertainers and the count of each entertainer’s engagements.”
Solution:
SELECT
Entertainers.EntertainerID, Entertainers.EntertainerName, COUNT(Engagements.EngagementID) AS
EngagementCount
FROM
Entertainers
LEFT JOIN
Engagements ON Entertainers.EntertainerID = Engagements.EntertainerID
GROUP BY
Entertainers.EntertainerID, Entertainers.EntertainerName;
2. “List customers who have booked entertainers who play country or country rock.”
Solution:
SELECT DISTINCT Customers.CustomerID, Customers.FirstName, Customers.LastName
FROM Customers
INNER JOIN Engagements ON Customers.CustomerID = Engagements.CustomerID
DMDD HW-1
INNER JOIN Entertainers ON Engagements.EntertainerID = Entertainers.EntertainerID
WHERE
Entertainers.MusicGenre IN ('Country', 'Country Rock');
3. “Find the entertainers who played engagements for customers Berg or
Hallmark.”
Solution:
SELECT DISTINCT Entertainers.EntertainerID, Entertainers.EntertainerName
FROM
Entertainers
INNER JOIN Engagements ON Entertainers.EntertainerID = Engagements.EntertainerID
INNER JOIN
Customers ON Engagements.CustomerID = Customers.CustomerID
WHERE
Customers.LastName IN ('Berg', 'Hallmark');
4. “Display agents who haven’t booked an entertainer.”
Solution:
SELECT
Agents.AgentID, Agents.AgentName
FROM
Agents
WHERE
Agents.AgentID NOT IN (
SELECT DISTINCT AgentID
FROM
Engagements
);
School Scheduling Database
1. “List all staff members and the count of classes each teaches.”
Solution:
SELECT
Staff.StaffID, Staff.FirstName, Staff.LastName, COUNT
(ClassSchedules.ClassID) AS
ClassCount
FROM Staff
LEFT JOIN ClassSchedules ON
Staff.StaffID = ClassSchedules.StaffID
GROUP BY Staff.StaffID, Staff.FirstName, Staff.LastName;
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
DMDD HW-1
2. “Display students enrolled in a class on Tuesday.”
SELECT DISTINCT
Students.StudentID, Students.FirstName, Students.LastName
FROM
Students
INNER JOIN
StudentSchedules ON
Students.StudentID = StudentSchedules.StudentID
INNER JOIN
ClassSchedules ON
StudentSchedules.ClassID = ClassSchedules.ClassID
WHERE DAYOFWEEK
(ClassSchedules.ClassDate) = 3; -- Assuming Tuesday is represented as the third day of the week (MySQL syntax)
3. “Show me the students who have an average score of 85 or better in Art and who also have an average score of 85 or better in Computer Science.”
Solution:
SELECT
StudentID, FirstName, LastName
FROM
(
SELECT
StudentID, AVG
(Score) AS
AvgScore
FROM
StudentScores
WHERE
Subject IN
('Art', 'Computer Science')
GROUP BY
StudentID
HAVING
AvgScore >= 85
) AS
AvgScores
GROUP BY
StudentID, FirstName, LastName
HAVING COUNT
(*) = 2; -- Ensure students have averages for both Art and Computer Science
4. “List the subjects taught on Wednesday.”
Solution:
SELECT DISTINCT Subjects.SubjectID, Subjects.SubjectName
FROM
Subjects
INNER JOIN ClassSchedules ON
Subjects.SubjectID = ClassSchedules.SubjectID
WHERE DAYOFWEEK
(ClassSchedules.ClassDate) = 4; -- Assuming Wednesday is represented as the fourth day of the week (MySQL syntax)
DMDD HW-1
Bowling League Database
1. “Show me all the bowlers and a count of games each bowled.”
Solution:
SELECT
Bowlers.BowlerID, Bowlers.FirstName, Bowlers.LastName, COUNT
(BowlerScores.GameID) AS
GamesBowled
FROM Bowlers
LEFT JOIN BowlerScores ON Bowlers.BowlerID = BowlerScores.BowlerID
GROUP BY Bowlers.BowlerID, Bowlers.FirstName, Bowlers.LastName;
2. “Show me tournaments that haven’t been played yet.”
Solution:
SELECT
TournamentID, TournamentName
FROM
Tournaments
WHERE
TournamentID NOT IN
(
SELECT DISTINCT
TournamentID FROM
TournamentScores);
3. “Find the bowlers who had a raw score of 170 or better at both Thunderbird Lanes and Bolero Lanes
.”
Solution:
SELECT
DISTINCT
BowlerID, FirstName, LastName
FROM
Bowlers AS B
INNER JOIN BowlerScores AS
BS1 ON
B.BowlerID = BS1.BowlerID
INNER JOIN Lanes AS L1 ON
BS1.LaneID = L1.LaneID AND
L1.LaneName = 'Thunderbird Lanes'
WHERE
BS1.RawScore >= 170
AND
EXISTS (
SELECT
1
FROM
BowlerScores AS
BS2
INNER JOIN Lanes AS L2 ON
BS2.LaneID = L2. LaneID AND
L2.LaneName = 'Bolero Lanes'
WHERE
B.BowlerID = BS2.BowlerID AND
BS2.RawScore >= 170
);
DMDD HW-1
4. “List all the bowlers who have a raw score that’s less than all of the other bowlers on the same team.”
Solution:
SELECT
DISTINCT B1.BowlerID, B1.FirstName, B1.LastName
FROM Bowlers AS
B1
INNER JOIN BowlerScores AS
BS1 ON
B1.BowlerID = BS1.BowlerID
WHERE
BS1.RawScore < ALL (
SELECT BS2.RawScore
FROM Bowlers AS B2
INNER JOIN BowlerScores AS BS2 ON
B2.BowlerID = BS2.BowlerID
WHERE
B1.TeamID = B2.TeamID AND B1.BowlerID != B2.BowlerID
);
Recipes Database
1. “Show me the types of recipes and the count of recipes in each type.”
Solution:
SELECT
RecipeType, COUNT
(RecipeID) AS
RecipeCount
FROM Recipes
GROUP BY RecipeType;
2. “Show me the recipes that have beef and garlic.”
Solution:
SELECT
RecipeTitle
FROM
Recipes
WHERE
RecipeID IN (
SELECT
RecipeID
FROM
RecipeIngredients
WHERE
IngredientID IN (
SELECT
IngredientID
FROM
Ingredients
WHERE IngredientName = 'Beef'
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
DMDD HW-1
)
AND RecipeID IN (
SELECT RecipeID
FROM
RecipeIngredients
WHERE
IngredientID IN (
SELECT IngredientID
FROM Ingredients
WHERE IngredientName = 'Garlic'
)
)
);
3. “List the ingredients that are used in some recipe where the measurement amount in the recipe is not the default measurement amount.”
Solution:
SELECT
DISTINCT IngredientName
FROM
Ingredients
WHERE
IngredientID IN (
SELECT IngredientID
FROM RecipeIngredients
WHERE MeasurementAmount <> (
SELECT
DefaultMeasurementAmount
FROM
Ingredients
WHERE
RecipeIngredients.IngredientID = Ingredients.IngredientID
)
);
DMDD HW-1
4. “List ingredients not used in any recipe yet.”
Solution:
SELECT
IngredientName
FROM
Ingredients
WHERE
IngredientID NOT IN (
SELECT
IngredientID
FROM
RecipeIngredients
);