CSCI133 SQL Database Concepts Assignments

This document contains a series of SQL assignments for the course CSCI133 - DATABASE CONCEPTS (SQL). It includes worksheets and chapter exams covering various SQL concepts such as data retrieval, formatting, conditional logic, date manipulation, aggregate functions, and joins. The assignments are structured to provide practical experience in writing SQL queries to solve real-world database problems. The content is divided into chapters, each focusing on specific SQL functionalities, and includes exercises that require students to apply their knowledge to different scenarios. The assignments cover topics like selecting data, formatting output, using functions, joining tables, and using subqueries.

CSCI133 - DATABASE CONCEPTS (SQL)
Chapter 4 SQL WORKSHEET
1. Display the name of each employee and the date they were hired. When displaying the name
of the employees, display the names in mixed case characters.
2. Display the name of each employee and the person’s yearly salary rounded to the nearest
thousand dollars. Note the SALARY in the table is the individual’s monthly salary.
3. Display each employee’s name and monthly pay. If the employee is a salesman, then
include the individual’s commission in the month pay, otherwise display display a dash (-)
symbol.
4. List Employees who's first names are four characters long. Display their Employee number,
name and job title
5. List the Departments and the cities they are located in. Format the department name so
that it is RIGHT padded with dots (…) and each name is 20 spaces long. Format the City the
department is located in so that it is LEFT padded with asterisks (***) and 15 characters
long.
6. Display a list with the names of each employee, a single space and their department,
enclosed in parenthesis as a single column. Order the list by the employee’s Job Title and
then Name.
Example: SMITH (CLERK)
7. Display a list of employees whose last name sounds similar to 'JANS'. Include both their
name and what the SOUNDEX value of their last name is.
8. List all employees and their salaries and “TIER” column. If the employee makes less than
$1000 per month, then give a comment of TIER 1. If an employee makes from $1000-$2000
per month then TIER 2. If an employee makes more than $2000 then use a comment of
TIER 3.
9. Put together a list of bonuses for all employees in department 30. List the employees name
and their bonus amount. Sort the list from highest to lowest bonus amount. Bonus will be
calculated as (Salary divided by two) plus Commission. If the employee does not receive a
commission then calculate their bonus as (Salary divided by two) plus $100.

10. List a unique list of Job Titles in the employee table, along with the number of characters in
the job title. Order the list with the job with the longest title at the top of the list.
CSCI133 - DATABASE CONCEPTS (SQL)
Chapter 4 CHAPTER EXAM
1. A user needs to know which customer was referred by another customer whose customer#
is 1006. List the last name with the first character capitalized and the rest of the last name
lower case. List the first name in all lower case and the state in all upper case. The column
headings should be Last, First, and ST. You do not know how the data is stored in the data
base.
2. The company is going to raise the cost of the books by 23%. A user need a list of the book
titles, authors first and last name, and the new cost of those books that have a new cost
greater than $50.00. The column headings should be Book Titles, Author Name, and
New Cost. The new cost should be displayed with a dollar sign and carried out to 3 decimal
positions. The list should be in ascending sequence on the author’s last name.
3. A users needs to know how many characters are in the longest city name in the orders
table. The column heading should be City and # Characters. List each city only once. Sort
the list in descending sequence on # characters.
4. Management is proposing to give each publisher a bonus on the profit generated for each
book based on the following information:
Publisher Id Percent
1 5%
2 10%
3 15%
4 20%
5 20%
Profit is calculated as Retail less cost. Column heading should be ISBN, Title, Profit and
Bonus. Order the list by the Books Publisher ID Ascending, and then the Books ISBN
Ascending.

5. A user is developing a code for the Customers that live in NJ and NY. The code consists of
the first 2 character of the customer’s first name, the last 3 characters of the customer’s
last name, and the third and forth characters of the zip code. List the customer’s first name,
last name, zip code, and the code. Column heading should be First Name, Last Name,
Zip, and Code.
CSCI133 - DATABASE CONCEPTS (SQL) Chapter 5 SQL WORKSHEET
1. Display the name of each employee and how many months he or she has been employed
with the company. When displaying the name of the employees, display the names in mixed
case characters.
2. Determine how long each employee has until retirement if an employee must remain with
the company for at least 25 years to be eligible for retirement benefits.
3. If each employee must be with the company for at least 25 years to receive benefits,
display the earliest possible retirement date for each employee. Display the retirement date
in the same format as January 1, 2013.
4. List Employees who were hired in 1980. Display their Employee number, name and job title
5. List the Employees who were hired in February of any year.
6. List the Employees, date they were hired, and their Salary. Format the date in the same
format as 2013-11-22 and format their salary as dollars with two decimal places. Order the
list by the date they were hired.
CSCI133 - DATABASE CONCEPTS (SQL) Chapter 5 CHAPTER EXAM
1. Determine the calendar date of the next occurrence of Friday, based on today’s date. The
column heading should be DAY. .
2. An employee has worked for the company since December 03, 1968. How many months
has the employee worked for the company? The column heading should be Months Worked.
Round the answer to 0 decimal positions. HINT: DUAL
3. A users needs a list of the books and dates they were published. They want the Title, ISBN
and date published, and the date should be formatted in Month, Day Year format. Order the
list with the most recently published books first.

4. The same user needs a new version of that list. They want you to add a column that shows
the total number of months that the book has been in publication.
5. A user would like a list of orders numbers for orders that were processed in April of 2003.
Include the Order number, Customer number and Date of the Order, Date the Order was
shipped. Show the dates in MM, DD, YYYY format. Sort the list by Order date with the
oldest at the top.
CSCI133 - DATABASE CONCEPTS (SQL) Chapter 6 CHAPTER EXAM
1. A users needs to know how many characters are in the longest book title. The column
heading should be Longest Title.
2. A user needs to know how many books (Quantity) were sold for each category. Sort the
result on Category in Descending sequence. The column heading should be Category and #
Sold.
3. A user needs to know the highest ISBN number in the books table.
4. A user needs to know the lowest order number in the Orderitems table.
5. A user needs to know how many customers live in the state of New York and New Jersey. The
column heading should be # in NY and NJ.
6. A user needs to know the total cost and total retail for all of the books in the books table.
The column headings should be Total Cost and Total Retail. The totals should be displayed as
dollar and cents amounts.
CSCI133 - DATABASE CONCEPTS (SQL) Chapter 6 SQL WORKSHEET
1. Determine how many different jobs employees contained in the EMP table hold.
2. Determine the average salary of employees by department.
3. Determine the total salary of all sales people employed by the company.
4. Determine the average salary of all employees in department 10.
5. List all departments whose employees have an average salary greater than $2000.
6. Determine how many sales people the company employs.
7. Determine how many departments employ more than four people.
8. Determine the total annual salary earned by employees of the company.
9. Determine the total annual salary earned by employees of the company, grouped by

department.
10. Determine the average monthly salary of all employees, grouped by job title.
DATABASE CONCEPTS (SQL) Chapter 7 CHAPTER EXAM
1. A user needs to know which books were published by American Publishing.
2. List the customer first name and last name as NAME, State, and Category for those
customers who live in the state of Idaho or Washington and have ordered books about
Cooking.
3. List the author first name and last name as Authors and the book titles as Book Titles that
were sold in the state of Wyoming. Sort the display on author’s last name and then first
name.
4. The author James Austin would like to send a thank you note to each customer that order a
book he help write. Create a list of all customers = that order books written by James Austin.
5. List the contact person as Contact Name, telephone number as Telephone and the author’s
first and last name as Author Name. Sort= the list on contact person in descending
sequence
- DATABASE CONCEPTS (SQL) Chapter 7 SQL WORKSHEET
1. Display the name of each employee, the name of the department to which the person is
assigned, and the location of each department. Join the EMP and DEPT tables through the
WHERE clause.
2. Display the name of each employee, the name of the department to which the person is
assigned, and the location of each department. Join the EMP and DEPT tables using the
NATURAL JOIN keywords.
3. Display the name of each employee, the name of the department to which the person is
assigned, and the location of each department. Join the EMP and DEPT tables using the
JOIN…ON keywords.
4. Display the name of each employee, the name of the department to which the person is
assigned, and the location of each department. Join the EMP and DEPT tables using the
JOIN…USING keywords.
5. Display the name and job title of each employee and the name of the department they work

in. Use the WHERE clause to create the join.
6. Display the name and job title of each employee and the name of the department they work
in. Use the JOIN…ON keywords to create the join.
7. Display the name and job title of each employee and the name of the department they work
in. Use the JOIN…USING keywords to create the join.
8. Display the name and job title of each employee and the name of the employee’s
department. Include any employee that does not have a department in the results.
9. Display the name of any department that does not have any employees currently assigned
to it.
10. Display any department that does not have a salesman assigned to it.
These are your assignments for Chapter 8:
You need to read Chapter 8.1 and complete the assignments in the
sequence shown.
 Read Chapter 8 section 1
o
 Do Lab 8.1 Exercises on page 332. Return to me via
the Chapter 8 Figures Assignments Dropbox.
 Exercises:
 1. Determine which sections the student Henry
Masser is enrolled in.
 2. Display the course number and description
of courses with no enrollment.
 Submit your solutions to the Chapter 8 SQL
worksheet dropbox