Description
Objectives:
Learn writing SQL queries with
• Orderby,
• aggregate functions
• Joining tables and subqueries
• Use foreign keys and “check” clause to enforce integrity rules.
Capturing the session and output:
You should capture the session (queries and output) into a file called, lab2_session.lst.
Capturing and Recording your Session
One way to capture your SQL session is to use the spool command that SQLPLUs provides to save query results to a file. At the SQL> prompt, type spool . For example,
SQL> spool foo;
This will create a foo.lst file in the current directory and will capture all input and output during your session at SQLPLUS (until you exit SQL).
You can terminate the capture with the command, spool off;
You can print/submit the foo.lst file for grading.
Oracle SQL Datatypes, Reference: http://www.ss64.com/orasyntax/datatypes.html
PART 1 (20 pts)
In this part, you will create a few tables and load them with given values and values of your choice.
Creating Tables
Assume that a customer can schedule a variety of home delivery services (grocery, movies, books etc).
Create the following tables based on the description given below (primary keys are underlined):
a) Customer: custid (a string of at most 5vcharacters), first name (a string of at most 10 characters) lastname (a string of at most 15 characters), city (a string of at most, 10 characters). Primary key: custid.
b) DeliveryService: serviceid (a string of at most, 10 characters), item (a string of 15 characters), location (a string of at most 15 characters), servicefee (a number with two points of precision after decimal point). Primary key: serviced.
c) Schedule: service id, custid, day (a string of up to 2 char and cannot be null). We will define a rule/constraint for the day attribute, that the day has to be Mon(‘m’) day through Fri (‘f’). The rule is defined using a CHECK clause and in to indicate the set of values that are allowed. Use ‘m’,’t’,’w’,’r’,’f’ to represent the day values.
See the example table below and use the same approach to define the rule for day in Schedule table.
Example table (this is an example to show the usage of CHECK. Do not create this table):
Create table Trip (id varchar(5) PRIMARY KEY,
source varchar(15), dest varchar(15)
CHECK (dest in (‘NY’,’Paris’,’London’)));
Now, define a foreign keys for table, Schedule.
Foreign key: custid referencing the table Customer(custid).
Foreign key: serviceid referencing the table DeliveryService(serviced).
Inserting Tuples
Add the following tuples into the tables (use a script file to add the data).
Customer:
‘c1′,’John’,’Smith’,’SJ’
‘c2’,’Mary’, ‘Jones’,’SFO’
‘a1′,’Vincent’,’Chen’,’SJ’;
‘a12’,’Greg’, ‘King’,’SJ’;
‘c7′,’James’,’Bond’,’LA’;
‘x10′,”Susan’,’Blogg’,’SFO’;
Add a few more tuples of your choice.
DeliveryService:
‘dsg1′,’grocery’,’SJ’,25.0
‘dsb1′,’books’,’SJ’,10.0
‘dsm2′,’movies’,’LA’,10.0
‘dby3′,’babygoods’,’SFO’,15.0
‘dsg2′,’grocery’,’SFO’,20.0
‘dg5′,’greengoods’,’SFO’,30.0
Add a few more tuples of your choice.
Schedule
‘dsg1′,’c1′,’m’
‘dsg1′,’a12′,’w’
‘dby3′,’x10′,’f’
‘dg5′,’c1′,’r’
‘dg5′,’c1′,’t’
‘dg5′,’c32′,’t’
Part 2 (40 pts)
In this part, you will execute SQL commands from a script file.
Step 1: Create a folder structure called COEN178/labs/lab2.
Step 2: Create a text file called lab2_queries1.sql. This file will contain the SQL queries that you want to execute.
Write the SQL for the following queries (5 pts each) (You are free to refer to the class notes and textbook).
a) Show the data in the Customer table as follows:
i. custid, Fullname, city, where fullname is the result of concatenating first name and last (‘||’ is the concatenation operator). Rename the column name as the Fullname.
b) Show data in the Customer, sorted by Customer last name (use order by).
c) Show data in the Schedule table, sorted by service id and then by customer id in descending order (use order by).
d) Show service ids of delivery services that are not in the schedule table (think set difference).
e) The following query is given to show the names of customers who ordered a delivery service on Monday (‘M’). Will it work? If not, fix it and show the query and results.
Select name from customer, schedule where day = ‘M’;
f) Show the last names of the customers who have scheduled delivery services. (What tables is the data coming from?)
g) Show the highest servicefee in Delivery Services (think of the aggregate function, max) renaming the result as Highest_Servicefee.
h) The incomplete query below is given to show pairs of customer ids from the same city.
Select A.custid,B.custid,A.city
from Customer A, Customer B
where A.city = B.city;
Will it work? If not fix it and run it again. Show the result.
Part 3 (40 pts)
Do the following two queries against staff_2010 table that you have created and loaded in lab1.
i) Write a query to show the minimum salary and maximum salary of staff members in staff-_2010 table. Display appropriate headings.
j) Show last name and first name of employees where the last names start with “Ki” followed by string of characters.
k) Show the names (last and first) of employee(s) with highest salary. Note: Think of a query and a subquery using Boolean operators like = , >= etc. and key word “any”, “all” appropriately. Do not use aggregate functions.
l) Show the title(s) of the jobs with the highest salary. Think of a subquery and an aggregate function with Boolean operators like =, >= etc.
