UIowa Database Management SQL Practice || A+ Graded Already.

UIowa Database Management SQL Practice || A+ Graded
Already.
Insert a record for the Art department (ID 24, office 102 Art Building) correct answers INSERT
INTO DEPARTMENT VALUES ('024', 'Art', '102 Art Building');

Delete the record for the Music department correct answers DELETE FROM DEPARTMENT
WHERE Department = 'Music';

The Dean wants to return a report with the names, hometowns, and home states for all students:
correct answers SELECT FirstName, LastName, Hometown, HomeState
FROM STUDENT;

The Dean wants to return a list of course IDs for courses taken this semester, with no duplicates:
correct answers SELECT DISTINCT CourseID
FROM GRADE;

Return each student's full name (first and last together as one field) correct answers SELECT
FirstName || ' ' || LastName AS FullName
FROM STUDENT;

Return the average percentage over all students/courses correct answers SELECT
AVG(Percentage) AS AvgPercent
FROM GRADE;

Return the birthdate of the oldest student correct answers SELECT MIN(Birthdate) AS
OldestBirthdate
FROM STUDENT;

Return the grade percentages rounded to 1 decimal correct answers SELECT
ROUND(Percentage, 1) AS RoundedPercentage
FROM GRADE;

Return the title and number of credits (squared) for each course correct answers SELECT Title,
POWER(Credits, 2) AS CreditSquared
FROM COURSE;

Return the ID and birth year of each student correct answers SELECT StudentID,
EXTRACT(Year from Birthdate) AS Year
FROM STUDENT;

Return the ID and age of each student (in days) correct answers SELECT StudentID, SYSDATE
- Birthdate AS AgeInDays
FROM STUDENT;

, Return the age of each student (in years) correct answers SELECT FLOOR((SYSDATE -
Birthdate)/365) AS Age
FROM STUDENT;

Return the course title and number of letters in each title correct answers SELECT Title,
LENGTH(Title) AS NumLetters
FROM COURSE;

Return each student's ID and last initial correct answers SELECT StudentID,
SUBSTR(LastName, 1,1) AS LastInitial
FROM STUDENT;

Return each student's ID and birthdate formatted (e.g., May 11, 1995) correct answers SELECT
StudentID, TO_CHAR(Birthdate, 'Month DD, YYYY') AS Birthdate2 FROM STUDENT;

What are Iowa's richest counties? Find the top 5 counties by median income. For each, return the
county ID, name, and median income (formatted as a currency value, e.g., $68,019) correct
answers SELECT CountyID, County, TO_CHAR(MedianIncome, '$999,999,999') AS Income
FROM COUNTY
ORDER BY MedianIncome DESC
FETCH FIRST 5 ROWS ONLY;

Return all information about counties whose median age is between 18 to 35 years old
(inclusive) correct answers SELECT *FROM COUNTY
WHERE MedianAge >= 18 AND MedianAge <= 35;

Return the name and population for Iowa's 3 biggest towns. Format the populations with commas
(e.g., 216,500) correct answers SELECT Town, TO_CHAR(Population, '999,999') AS Residents
FROM TOWN
ORDER BY Population DESC
FETCH FIRST 3 ROWS ONLY;

Return the min, max, and median budget amount spent on Public Safety & Legal Services over
all counties. (Do not apply currency formatting) correct answers SELECT MIN(Expenditure) AS
Minimum,
MAX(Expenditure) AS Maximum, MEDIAN(Expenditure)
AS Median
FROM BUDGET
WHERE Category = 'Public Safety & Legal Services';

Assume that western Iowa includes all counties west of Des Moines (i.e., county location is less
than -93.75 degrees of longitude). Return the total number of counties and total population of
western Iowa. correct answers SELECT COUNT(CountyID) AS Counties,
SUM(Population) AS Population
FROM COUNTY
WHERE Longitude < - 93.75;