Advanced Practice Problems - Mixed-up Code
Let’s practice what we have learned about SQL.
System Message: ERROR/3 (/books/py4e-int/_sources/database/db-hp-single-line-adv.rst , line 6)
Duplicate ID – see database/db-hp-single-line, line 32
.. hparsons:: db-hp-get-and-p1
:language: sql
:randomize:
:blockanswer: 0 1 2 3 4 5
In the ``grades`` table:
.. image:: https://i.ibb.co/r6qShy5/practice-grade.png
Create a SELECT statement to retrieve the ``student_id``, ``test_name``,
and ``english`` for all entries whose ``english`` value is lower than 60 and ``math`` value is higher than 90.
~~~~
--blocks--
SELECT
student_id, test_name, english
FROM
grades
WHERE
english < 60 AND math > 90
english < 60 & math > 90
GET
TABLE
System Message: ERROR/3 (/books/py4e-int/_sources/database/db-hp-single-line-adv.rst , line 30)
Duplicate ID – see database/db-hp-single-line, line 54
.. hparsons:: db-hp-change-p2
:language: sql
:randomize:
:blockanswer: 0 1 2 3 4
In the ``grades`` table:
.. image:: https://i.ibb.co/r6qShy5/practice-grade.png
A student completed an extra assignment and got some additional points.
Create an UPDATE statement to change the entry with a ``student_id`` of 1 and set the math score for the ``test_name`` of ``final`` to 90.
~~~~
--blocks--
UPDATE grades
SET
math = 90
WHERE
student_id = 1 AND test_name = "final"
LET
student_id = 1 AND test_name = final
CHANGE grades
System Message: ERROR/3 (/books/py4e-int/_sources/database/db-hp-single-line-adv.rst , line 55)
Duplicate ID – see database/db-hp-single-line, line 79
.. hparsons:: db-hp-update-p3
:language: sql
:randomize:
:blockanswer: 0 1 2 3 4 5
In the ``grades`` table:
.. image:: https://i.ibb.co/r6qShy5/practice-grade.png
One test was too difficult so the instructors decided to give everyone extra points.
Create an UPDATE statement to add 10 points to the ``english`` score
of the entries with a ``test_name`` of ``midterm``
~~~~
--blocks--
UPDATE
grades
SET
english = english + 10
WHERE
test_name = "midterm"
test_name == "midterm"
WHEN
In addition to the grades
table:
there is also a students
table:
Create a SELECT statement with a JOIN to: select the test_name
and math
from the grades
table and name
from the students
table, where the student_id
in the grades
table is the same as the id
in the students
table.
In addition to the grades
table:
there is also a students
table:
Create a SELECT statement with a JOIN to: select the date
and name
from the tests
table and student_id
from the grades
table,
where the test_name
in the grades
table is the same as the name
in the tests
table.
You have attempted
of
activities on this page