Advanced SQL
When you try to execute the below query you get an error. Explain the error and provide a solution. The query is trying to display the number of books every author has published with every publisher.
SELECT Publisher, Author, count(*) 'Num Books'
FROM Product
GROUP BY publisher
You cannot have an attribute in the select clause that is unaggregated or not in the group by clause.
We need to group by author as well.
This join returns all rows from the left table and matching rows from the right table; unmatched rows on the right show as NULL.
LEFT JOIN (or LEFT OUTER JOIN)
This type of object represents a person, place, thing, or concept in a database and is typically shown as a rectangle in an ER diagram
Entity
These are represented in a physical design table's rows
Table/Relation columns
This concern could convince a database designer to move, into a separate table, some of the attributes of an entity, even if they map to the entity 1 to 1.
Security of the data
The below query is attempting to return the authors who have more than 2 books published by 'Penguin'. Explain what the problem is and provide a solution.
SELECT Author FROM Product
GROUP BY Author
HAVING Publisher = 'Penguin' and count(*) >1
SELECT author FROM Product
WHERE publisher = 'Penguin'
GROUP BY author
HAVING count(*) >1
This join returns all rows when there is a match in either table, and fills in NULLs when there is no match.
FULL JOIN (or FULL OUTER JOIN)
This describes a property or characteristic of an entity, like “Name” or “Date of Birth”, and is represented as an ellipse in an ER diagram.
Attribute
Solid line relationships connect these in physical designs
A primary key in one table to a foreign key that is a primary key in the other table.
This rule ensures that no Primary Key value is NULL
Entity Integrity
Explain the query:
SELECT Department, COUNT(*) AS '# Employees'
FROM Employee
WHERE Salary > 50000
GROUP BY Department
HAVING COUNT(*) > 5;
Displays departments that have more than five employees with a salary above $50,000
When we execute the below query we get an error:
SELECT StationId
FROM Station S, DropOffStation D
WHERE S.StationId = D.StationId
Explain the problem and Provide a solution
Ambiguous column name 'StationId'.
Need to specify the StationId from which table, since it is a common field.
What is the primary key of the child in an isa relationship
The primary key of the parent
What does the physical design look like for an entity A with a M-1 relationship with entity B
The physical design table for A has a column with a FK column for the PK of the table for B
Seeing a value of _______ in a FK column indicates a modality of ________
NULL, modality part of the multiplicity is 0, since NULLs are allowed.
Running this query gives many more results than expected. What could be the issue?
SELECT Course.Name, Teacher.Name
FROM Course, Teacher
WHERE Course.Semester = 'F2024';
The table matching condition is missing (FK match to PK)
(Use JOIN with ON instead!)
A - In this join, all rows from both tables are included, with NULL values where no match is found in either table.
B - This type of join retrieves only the rows where there is a match in both tables.
A- FULL OUTER JOIN
B- INNER JOIN
In a university database, Student and Course are two entities connected by the EnrollsIn relationship. Each enrollment also records the Grade the student received. Where should the Grade attribute be added in the ER model?
The Grade attribute should be added to the relationship EnrollsIn, not to either Student or Course
How many physical design tables are needed to represent an entity Car that is an entity Vehicle (inheritance), when the NULL method is used?
One Vehicle table
This rule forces us to add an entity to a table before being able to use the entity info in a FK column
Referential integrity
Specify the Order of EXECUTION OF these clauses:
SELECT
Top
DISTINCT
FROM and JOINs
WHERE
GROUP BY
HAVING
ORDER BY
1. FROM and JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. Top
Describe what this SELECT statement does:
SELECT I.supplier, COUNT(D.quantity)
FROM Inventory AS I
LEFT OUTER JOIN Details AS D ON I.productId = D.productId
GROUP BY I.supplier
This statement will display, by supplier, the number of orders each containing products sold by the company. Because of the left outer join, this will include suppliers whose items were not yet purchased.
What is the relationship type between: a Folder being in another Folder
recursive
In a university database, Student and Course are two entities connected by the EnrollsIn relationship. Each enrollment also records the Grade the student received. Where should the Grade column be added in the PHYSICAL design?
In the middle enrolls in table
This part of multiplicity indicates the maximum number of times a row in a relation is associated to rows in the other relation.
Cardinality