Project 1: SQL Refresher
This assignment is to refresh your memory (or update your memory) on SQL and core relational database concepts. No group projects are allowed. You can use one of two different platforms for this project:And write a set of SQL queries for a given database. You will submit your queries and answers.
Install Oracle SQLDeveloper on a Windows machine to use in this assignment. Installation instructions are described here.
Next, use the following SQL scripts, which will create or drop a
set of relations and populate them with tuples. You will use this
database to process the following queries.
Now write SQL queries to answer the following questions. Useful and short tutorials on SQL can be found here and here, should you need them.
What is the price of the part named "Dirty Harry"?
What orders have been shipped after date '03-feb-95'?
What are the ono and cname values of customers whose orders have not been shipped (i.e., the shipped column has a null value)?
Retrieve the names of parts whose quantity on hand (QOH) is between 20 and 70.
Get all unique pairs of cno values for customers that have the same zip code.
Create a nested SQL select statement that returns the cname values of customers who have placed orders with employees living in Fort Dodge.
What orders have been shipped to Wichita?
Get the pname values of parts with the lowest price.
What is the name of the part with the lowest price? (use qualified comparison in your predicate, i.e., <=all).
What parts cost more than the most expensive Land Before Time part? (Hint: you should use pattern-matching, e.g., pname like 'Land Before Time%').
Write a correlated query to return the cities of zipcodes from which an order has been placed.
Get cname values of customers who have placed at least one part order through employee with eno = 1000.
Get the total number of customers.
Get the pname values of parts that cost more than the average cost of all parts.
For each part, get pno and pname values along with the total sales in dollars.
For each part, get pno and pname values along with the total sales in dollars, but only for total sales exceeding $200.
Repeat the last 2 queries, except this time create a view to simplify your work. Define the view and each query on that view.
Delete order 1021 and its order details.
Increase the cost of all parts by 5%.
Retrieve employees by name in reverse alphabetical order.
What tuples of Employees and Zipcodes do not participate in a join of these relations? Use the outerjoin and minus operations.
-- query 1: what is the price of the part named "Dirty Harry"?
-- query 2: What orders have been shipped after date '03-feb-95'?