Data Analytics Question

The topic is:

Single Table Queries and Search Conditions:

Use the Northwoods DB; do NOT change the database in any way.  Only provide the queries for this HW (do NOT include the Northwoods script!).  Provide a single .sql file for the HW 

The instructions on the HW assignment is attached in a Word document calls “SingleTableHW”
in order to do the homework you need the Northwoods data, so attached is the SQL code for it calls “script to create NORTHWOODS database.”. and also there is for you to make it easier the file “NorthwoodsERD (in Word).docx”

Some basic Information about how the professor wants the Queries in general

We start with simple queries using only one table (so no joins).  We also introduce one of the key proficiencies required for queries, which are search conditions.  We rarely want to do a query that returns ALL rows. Instead, we only want to see certain rows returned.  We might want to see not all customers, just the ones that live in Conway.  We might want to see employees who make over $50,000 a year, not all employees.  To restrict the rows that are returned in a query, we use search conditions.  These provide the limitations for our query.  Search conditions require operators, and there are only a few of them (but we must know and learn them all!):

Comparison operators–there are six:  =    >    >=    <    <=    <>

BETWEEN ____ AND ____ (or NOT BETWEEN ____ AND ____)

IN (xx, yy) (or NOT IN (xx, yy))

IS NULL (or IS NOT NULL)

LIKE

We use these operators to limit which rows we wish to include in a query.  We introduce them in this first class, but will use them for all queries.  Most are fairly simple–but pay attention to the hardest one–using LIKE.  Read it carefully, because it is easy to misuse.

In this class we cover the following:

Single-table queries

Search conditions (detailed above)

Sorting output

Formatting output (alternate column headings)

TO_CHAR function

Northwoods DB:

• You will need to run the script (Northwoods.sql) in Oracle.  It will work, so long as you do not already have tables with the same name as these (such as student, faculty, course, etc.).  If you do, DROP YOUR OLD TABLES!!  You should do that anyway, to declutter your schema.  You may NOT change this script in any way.  All query HWs will be done using this DB.
• When we get to joins (which comprise almost all our queries), you will need the ERD to see how tables are joined.  The ERD is a little different–I took out the relationship names (thinking they were obvious) and instead put in the field that relates the the two tables (the PK and FK–which have the same name in this DB).  You will need the ERD throughout, so keep it handy.