Guide to Oracle SQL (University at Buffalo Version)
Restricting and Sorting Data
In Chapter 2 I introduced the
SELECT statement and provided examples for retreiving all the fields or just certain fields in a record. SQL also allows you to restrict your query to return on certail records in a table using condtitionals.
WHERE Conditional on a
WHERE keyword can ne used to provide a condition (restriction) so the records retreived (result set) contains only the records you are interested in. The
WHERE can use operators and wildcards.
|!>||Not Greater Than|
|!<||Not Less Than|
|>=||Greater Than or Equal To|
|<=||Less Than or Equal To|
|<>||Not Equal To|
|!=||Not Equal To|
|ALL||Compares all the values in one set to another set|
|AND||Returns values if all the contitinals are TRUE|
|ANY||Compares one value tomore than one value|
|BETWEEN||Returns values within a range|
|EXISTS||Determines if a record is found matching the criteria|
|IN||Determines if the value is in a list of values|
|LIKE||Compare values using wildcards|
|NOT||Used to negate any conditional operator|
|OR||Returns values if at least one criteria is TRUE|
|IS NULL||Returns a value if the field contains a NULL value|
|SOME||Returns values from a subquery|
Using Wildcards in
|%||Will match any sequence of charaters and numbers|
|_||Will match any single character in a certain position|
Note, when using the
LIKE operator the string needs to be surroiunded with single quotes (
LIKE operator is case-sensitive.
Sorting Your Result Set
You can use the
ORDER BY clause to sort the result set from your
SELECT statement. By default, the result set is sorted ascending based on the field (or fields) you supply on the
clause. You can add the
DESC to order your result set in descending order.
Create, Populate, and Display a departments and a jobs Table
- Open SQL Developer
- Connect to your database instance
- Open a new SQL editor window
- Enter code to CREATE a "departments" table with these fields:
departent_id NUMBER(4) NOT NULL PRIMARY KEY, department_name VARCHAR2(30) NOT NULL, manager_id NUMBER(6), location_id NUMBER(4)
- INSERT these records into the "departments" table:
- 20, Marketing, NULL, NULL
- 80, Purchasing, NULL, NULL
- 90, Shipping, NULL, NULL
- 60, Accounting, NULL, NULL
- 40, Manufacturing, NULL, NULL
- 50, Contracting, NULL, NULL
- 30, Recruiting, NULL, NULL
- Display the departments table date (use SELECT).
- Save your SQL query as "create_departments.sql".
- Open a new SQL editor window
- Enter code to CREATE a "jobs" table with these fields:
job_id VARCHAR2(10) PRIMARY KEY, job_title VARCHAR2(20), min_salary NUMBER(10,2), max_salary NUMBER(10,2)
- INSERT these records into the "jobs" table:
- SA_MAN, Srvc Acct Manager, 35000, 49999
- IT_PROG, IT Programmer, 55000, 79999
- MK_REP, Marketing Rep, 22000, 34999
- ST_CLERK, Srvc Tech Clerk, 19000, 21999
- AD_ASST, Admin Assistant, 17000, 18999
- Display the jobs table date (use SELECT).
- Save your SQL query as "create_jobs.sql".
When you have this Assignment ready for me to view and grade, you should click on this assignment and then choose "Add a File" and upload your create_departments.sql and create_jobs.sql files, so I can download them and grade your work.