Note: Test all your SQL statements by executing them.
STEP INSTRUCTION SUBMIT THE
FOLLOWING
MARK
1 Write the necessary SQL statements to create the
database and associated tables as defined by the
business rules and ERD on the previous page.
SQL statements 10
2 Write the necessary SQL statements to insert relevant
data into the database tables created in Step 1. For each
of the tables listed below, insert at least the minimum
number of records stated. You may insert more records
than the minimum stated.
Author table: At least 5 records
Genre table: At least 3 records
Books table: At least 20 records
Competition table: at least 15 records
Ensure that at least one book has been entered in
more than 1 competition
SQL statements 10
3 Write a SQL statement to demonstrate your knowledge of
the UPDATE statement.
SQL statements 10
4 Write a SQL statement to demonstrate your knowledge of
the DELETE statement.
SQL statements 10
5 Write a SQL statement to display a report that lists all
book titles, their authors, and their genres. The report
should be sorted in alphabetical ascending order by
genre, followed by alphabetical ascending order by book
titles.
SQL statements
Report output
15
6 Write a SQL statement to demonstrate your knowledge of
the GROUP BY clause. The SQL statement should display
an appropriate report.
SQL statements
Purpose of report
Report output
15
7 Write a SQL statement to demonstrate your knowledge of
the HAVING clause. The SQL statement should display an
appropriate report.
SQL statements
Purpose of report
Report output
15
8 Write a SQL statement to demonstrate your knowledge
of using JOINS. The SQL statement should display an
appropriate report.
SQL statements
Purpose of report
Report output
15
1 answer
1 answer