Enrollments table ID varchar (255) Class varchar (255) Section varchar (255) Cam
ID: 3848390 • Letter: E
Question
Enrollments table
ID varchar (255)
Class varchar (255)
Section varchar (255)
Campus varchar (255)
Hours Int (11)
School varchar (255)
Supplier table
supplier_id Int (11)
supplier_name varchar (50)
Parts table
part_id Int (11)
part_name varchar (50)
supplier_id Int (11)
1. Write a query which selects rows from the enrollments table and filters rows containing 'Biology' in the class_name column.
2. Write a query which selects rows from the enrollments table and filters rows containing '101' in the class_name column.
3. Write a query which returns a count of the rows from the enrollments table containing 'Intro' in the class_name column. (Tip: you need to use an aggregate function.)
4. Write a query which returns a sum of the hours from the enrollments table containing 'Intro' in the class_name column. (Tip: you need to use an aggregate function.)
5. Write a SQL statement which joins the parts table with the suppliers table and lists the part_id, name, and the supplier name for all parts in the part table. The part_id column in the parts table is the primary key, and this key has been exported to the supplier table where it is a foreign key.
6. Write a SQL statement which joins the parts table with the suppliers table and lists the part_id, name, and the supplier_name for all parts in the part table. Only return the parts supplied by 'Marys Quilts'.
Explanation / Answer
Query 1:
SELECT * FROM Enrollments -- Select rows from enrollments table
WHERE Class like 'Biology%' -- filter rows with biology class
Query 2:
SELECT * FROM Enrollments -- Select rows from enrollments table
WHERE Class like '%101%' -- filter rows with 101 in class name
Query 3:
SELECT COUNT(1) FROM Enrollments -- Count rows, using count aggregate method
WHERE Class like '%Intro%' -- Filter rows with Intro in class name
Query 4:
SELECT sum(Hours) FROM Enrollments -- Sum of hours using aggregate function
WHERE Class like '%Intro%' -- Filter rows with Intro in class name
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.