UCI Basic SQL Query Questions

Write queries to extract the information for Q1 and Q2 (both 7 parts).• Your queries should extract the answers directly. If you need additional steps
(e.g., hand counting the number of rows) to get the answer after running a query,
the query is not considered as the right query for the question.
• Copy and paste your queries and result table to a Word file and upload it on
Canvas.
Q1. Use TopBabyNamesbyState.csv1 and. (45 points)
1) How many rows are there in this dataset? (5 points)
2) How many unique names are there in this dataset? (5 points)
3) Which name had the highest number of occurrences. Report the name,
occurrences, state and year. (5 points)
4) What is the average occurrence of “Jessica” as a top name in the state of
CA? (5 points)
5) How many years was “Mary” the top name in California? Name the
computed column as “Mary_topinCA”. (5 points)
6) The three most popular female names in WA state since 2000. Popularity
is based on the total number of occurrences. Report the names along with
the total number of occurrences. (10 points)
7) Make a list of names that have an average occurrence greater than 1500.
Report the names along with the average occurrences. Order the result
table by average occurrence in an ascending order. (10 points)
Q2. Use Employee.sql and Office.sql. (55 points)
1) Report the average salaries for each of the following three groups: poorperforming account reps, poor-performing managers, and poor-performing
trainees. Show the title and performance along with the average salaries.
(5 points)
2) Report the IDs of employees whose first name starts with “pa”, followed
by three characters and then ends with “a”. Show the first name along with
the employee ID. (5 points)
3) How many employees are there with a hired date is 31 and whose last name
consists of exactly five characters. (5 points)
4) How many employees at non-regional offices were hired after 2000? (10
points)
1
The dataset includes the most popular male and female names in each state for each year from
1910-2012 via the Social Security Administration.
5) Report the Employee ID of the newest Account Rep who is working in a
location with audit code=100. (10 points)
6) How many female employees have a salary higher than the average salary
of all employees? (10 points)
7) Make a list of all employees who have a salary more than the average
salary of all employees. (10 points)