Scribd
Upload
101 views3 pages

SQL Lab: SELECT & Data Sorting

This document provides instructions for a lab assignment on simple SELECT statements and sorting data in Oracle SQL. Students are asked to write a single SQL script that meets the requirements of the problem set, which includes displaying agent data sorted by last name, finding vacation tours containing "Paris" in the description sorted by description, and showing US and Canadian destinations sorted by country and description. The document provides tips on formatting the output and the marking criteria which awards points based on correct results, formatting, flexibility of the solution, and inclusion of required files.

Uploaded by

gomez.mafe
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
101 views3 pages

SQL Lab: SELECT & Data Sorting

This document provides instructions for a lab assignment on simple SELECT statements and sorting data in Oracle SQL. Students are asked to write a single SQL script that meets the requirements of the problem set, which includes displaying agent data sorted by last name, finding vacation tours containing "Paris" in the description sorted by description, and showing US and Canadian destinations sorted by country and description. The document provides tips on formatting the output and the marking criteria which awards points based on correct results, formatting, flexibility of the solution, and inclusion of required files.

Uploaded by

gomez.mafe
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Student Name: Weight: 2.

3%

Student ID: Marks: /15

Lab: Simple SELECT and Sorting Data


Equipment and Materials
For this lab, you will need:
· A Windows computer with a minimum of 16GB RAM and 250GB of free disk space,
capable of nested virtualization
· Access to ORACLE SQL*PLUS
· Really Cheap Vacations Database created as part of previous learning activities
· Physical model for Really Cheap Vacations as created during previous learning activities
· A copy of the Really Cheap Vacations database
o Create the Really Cheap Vacations database by referring to the file
[Link] in the course resource section of Brightspace.
· The file [Link], which is provided on Brightspace.

Instructions
1. First, work through stages 2-5 individually. Then arrange a time to come together with your
small group. As a group, create one solution based on the best individual script for each
problem. Further refine the solution as a group as needed.
2. Review the Really Cheap Vacations Physical Model.
3. Review the Really Cheap Vacation Database.
4. Using [Link] as a starting point, write a single script that satisfies all the
requirements outlined in the Problem Set.
5. Review the Tips for Success and Marking Criteria sections. Adjust your script as needed.
6. See Brightspace for exact due dates.
7. Only one submission is required per group. The submission should include:
a. One script file
b. One spool file showing all results
c. One attribution list that outlines the activities associated with completing this
assignment. A sample attribution list is provided on Brightspace.

© 2022, Southern Alberta Institute of Technology 1


Problem Set
1. Display all agents with an agent level of III or IV who specialize in non-Canadian (CA)
vacations. Sort by last name.

2. Find all easy (E) or moderate (M) vacation tours that have ‘Paris’ in their description. Sort by
the description.

3. Show all US and Canadian destinations. Sort by country, and then description.

Tips for Success


1. Use column aliases to create appropriate column headers.
2. Use set linesize xxx, where xxx is a number to set the width for the output.
3. Use the column command to set the size of the columns, e.g.:
column “aliasname” format A40
column stagename format A30
“A” means alphanumeric field, with a length of 30 characters in the example above.
4. For number columns use

© 2022, Southern Alberta Institute of Technology 2


column “aliasname” format 9999.99
This will show four digits before the decimal point and two digits after the decimal point.
5. Use clear columns; at the end of each query to reset the column size.

Marking Criteria
Missing Needs Good
Excellent
Categories 0 Improvement 2 Score
3
1

N/A 2+ questions 1 question Yes


Correct results incorrect incorrect
(e.g. # of rows, /3
values)

No attempt 3+ formatting 1-2 Formatted Yes


Output is to issues, issues
formatted to format lines wrap /3
match what is output in output
provided

Solution will 4 questions 2-3 questions 1 question will not Yes


work on all will not will not work work for all
datasets (e.g. work for all for all datasets datasets /3
no hard-coded datasets
values)

Attribution list No N/A N/A Yes


provided
/3

No Yes
Spool file
provided with /3
commands
included

/15

© 2022, Southern Alberta Institute of Technology 3

You might also like