IT 170 Syllabus Overview – Dr. Trippers Stuff

Database design Fundementals Thread. Here is what we will learn: WELCOME VIDEO.

good-IMG_7012.MOV

banner

The first thing you nee to do is, read the syllabus , then click to sign in MindTap, this will proof that you started the class , failing to do that in the first week will result for reporting No show to the administration and they may drop you from the class!.

logo-picture

Concepts of Database Management, 10th Edition

SKCTC

SOUTHEAST KENTUCKY COMMUNITY & TECHNICAL COLLEGE

SYLLABUS

SPRING 2025

IT 170 – Introduction to Database

3 CR

Course Description:

Provides an overview of database and database management system concepts, internal design model, normalization, network data models, developmental tools, and applications.

Pre-requisite: (CIT 105 OR OST 105 OR IMD 100) and (mat 085 or mat 126) or Consent of instructor.

Days, Time & Meeting Place: Internet

Instructor: Ismail Eldahan

Room & Office Hours-: 245 – by appointment

Phone: 248-0775

Email: ismail.eldahan@kctcs.net

Division Chair: DIVISION CHAIR: –Peggy Conklin peggy.conklin@kctcs.edu

606-589-3336 (office)

Text/Materials:

1. Text: Concepts of Database Management, 10th Edition

Philip J. Pratt, Joseph J. Adamski, Lisa Friedrichsen, Lisa Ruffolo, Ellen Monk

Print Edition ISBN: 9780357422083

MindTap ISBN: 9780357422106

2. Software: Microsoft Access, Word (2013/2016 /office365)

Topic:

1-) Introduction to Database Management

2-) Relational Model (Introduction, QBE, Relational Algebra.)

3-) Relational Model2(SQL)

4-) Relational Mode3 –Advanced Topic

5-) Database Design1- Normalization

6-) Database Design2-Design Method

7-) DBMS Functions

😎 Database Administration

9) Database Management Approaches

Learner Competencies/Outcomes:

Upon completion of this course, the student can:

Define the steps involved in the database design process
Identify the basic concepts of current data modeling methodologies: Entity-Relationship Diagrams (ERD) and Unified Modeling Language (UML) class diagrams.

3 Determine cardinality between tables.

4 Define the characteristics of the relational database model.

5 Translate an ERD or UML conceptual data model into a logical data model for implementation using a relational DBMS.

6 Define and use the normalization process to further refine the relational table definitions.

7 Discuss factors that influence the development of the physical database design.

8 Use DBMS-supplied tools/wizards, third party CASE (Computer-Assisted Software Engineering) tools, and/or SQL Data Definition Language (DDL) commands to create required database objects and referential integrity constraints.

9 Use DBMS-supplied tools/wizards, third party CASE tools and/or SQL Data Manipulation Language (DML) commands to migrate existing/test data into the new database objects.

10 Discuss the components that are required for a simple, user-friendly database application.

11. Use DBMS-supplied tools/wizards and/or SQL commands to create basic queries.

12. Design single and multi-table queries.

13. Design basic queries to facilitate selection, insert, update, and delete operations.

General Education Competencies:

Students should prepare for twenty-first century challenges by gaining:

Knowledge of human cultures and the physical and natural worlds though study in the sciences and mathematics, social sciences, humanities, histories, languages, and the arts.

Intellectual and practical skills, including:

1. Inquiry and analysis

2. Critical and creative thinking

3. Written and oral communication

4. Quantitative literacy

5. Information literacy

6. Teamwork and problem solving

Personal and social responsibility, including

1. Civic knowledge and engagement (local and global)

2. Inter cultural knowledge and competence

3. Ethical reasoning and action

4. Foundations and skills for lifelong learning

Integrative and applied learning, including synthesis and advance accomplishment across general and specialized skills.

General Education Student Learning Outcomes:

Illustrate and communicate mathematical and/or statistical information symbolically, visually, and/or numerically.
Apply an appropriate model to the problem to be solved.
Interpret information presented in mathematical and/or statistical forms.
Determine when computations are needed and to execute the appropriate computations.
Make inferences, evaluate assumptions, and assess limitations in estimation modeling and/or statistical analysis.
Apply an appropriate model to the problem to be solved.

Writing Assignments

When it applicable, are expected to be Completed-Preferably-Microsoft Word with appropriate grammar and spelling

Withdrawal Policy: The last day for a student to officially withdraw from the course at their discretion is midterm. Only under exceptional circumstances and at the discretion of the instructor will a grade of “W” be assigned after this date. See Academic Calendar

DISABILITY SERVICES

“Students in this course who need, or who believe that they will need, any academic adjustments, auxiliary aids, or other accommodations because of the functional limitations of a documented disability, should NOT approach the instructor with this matter. They should, instead, visit the Disabled Students Service (DSS) Coordinator,

Middleboro – Felicia Carroll

GRADING:

80% Mind tap activities

Here is a list for what you need to do in Mind Tap[Everything with a due date and has a RED DOT]:-

Pr-Assessment Quiz

Module 1: Module Review Questions: Problems

Module 1: JC Consulting Case Exercises: Problems

Module 2: Module Review Questions: Problems

Module 2: JC Consulting Case Exercises: QBE: Problems

Module2: JC consulting case:Relational Algebra

Concepts of Database Management Module 2 SAM Training

Module 3: Module Review Questions: Problems

Module 3: JC Consulting Case Exercises: Problems

Concepts of Database Management Module 3 SAM Training

Module 4: Module Review Questions: Problems

Module 4: JC Consulting Case Exercises: Problems

Concepts of Database Management Module 4 SAM Training

Module 5: Module Review Questions: Problems

Module 5: JC Consulting Case Exercises: Problems

Concepts of Database Management Module 5 SAM Training

Module 6: Module Review Questions: Problems

Module 6: JC Consulting Case Exercises: Problems

Concepts of Database Management Module 6 SAM Training

Module 7: Module Review Questions: Problems

Module 7: JC Consulting Case Exercises: Problems

Concepts of Database Management Module 7 SAM Training

100

Post-Assessment Quiz

Total Possible Points402 and that represent 80% of total grade

20% Final in Blackboard..

It looks like you’ve got the syllabus for your “Introduction to Database” course at Southeast Kentucky Community & Technical College! Here’s a quick breakdown:

### Key Details:
– **Course**: IT 170 – Introduction to Database (3 CR)
– **Instructor**: Ismail Eldahan
– **Textbook**: *Concepts of Database Management, 10th Edition*
– **Software**: Microsoft Access, Word (2013/2016/Office365)
– **MindTap**: Required for completing activities and assessments

### Topics Covered:
1. **Introduction to Database Management**
2. **Relational Model** (QBE, Relational Algebra, SQL)
3. **Database Design** (Normalization, Design Method)
4. **DBMS Functions & Database Administration**
5. **Database Management Approaches**

### Learning Outcomes:
– Define database design steps
– Use ERD and UML diagrams for data modeling
– Normalize data and translate models into logical data models
– Create and manage databases with DBMS tools
– Design queries and database applications

### Grading:
– **80%**: MindTap activities (including quizzes, exercises, and SAM training)
– **20%**: Final Exam in Blackboard

### Withdrawal Info:
– Last day for withdrawal: Midterm, unless under exceptional circumstances

Make sure to sign into MindTap by the end of week 1 to avoid being dropped!

How do you feel about jumping into these topics? If you need any help along the way, I can break things down or even help with practice problems!
2-14f Pitt Fitness Case Exercises

The owner of Pitt Fitness knows that the power of the company’s database is in running queries to find out important information for making good business decisions. In the following exercises, you use the data in the Pitt Fitness database shown in Figures 1-15, 1-16, 1-17, 1-18, and 1-19 in Module 1. When you use Microsoft Access to respond to these exercises, make a copy of the original database to create the queries. In each step, use QBE to obtain the desiredÊresults.
It looks like you’re diving into the Pitt Fitness case exercises using Microsoft Access and Query By Example (QBE) to create queries based on the provided database.

### Here’s a general approach to completing the exercises:

1. **Make a Copy of the Database**:
– Open the **Pitt Fitness database** in Microsoft Access.
– Before you start creating queries, **make a copy** of the original database to work on. This ensures you don’t accidentally alter the original.

2. **Open QBE (Query By Example)**:
– In Access, go to the **Create** tab and select **Query Design**.
– Choose the tables needed for the query from the list that appears, or select **Show Table** if you need to add new tables.

3. **Building Queries**:
– In QBE, use the **design grid** where you can drag fields from the tables.
– For each exercise:
– Drag the appropriate fields into the grid.
– Set **criteria** for your query (for example, finding clients from a certain city or filtering records by date).
– Choose the **sort order** (ascending or descending) for the results if required.
– Once the grid is filled, you can run the query to see the results.

4. **Run and Refine**:
– After setting up the query in QBE, click **Run** to view the output.
– If the results aren’t what you expected, refine the query by adjusting the criteria, adding/removing fields, or changing sorting options.

### Some Example Tasks:
– **Find clients who joined in a specific month.**
– Select the **Join Date** field and apply a **criteria** to filter by the month.
– Example QBE criteria: `Between #1/1/2025# And #1/31/2025#`

– **List of all memberships with prices above a certain amount.**
– Select **Membership Type** and **Price** fields, and set the **criteria** to filter by price, like `>50`.

### General QBE Syntax:
– **Criteria**: You can input conditions (e.g., `>50` for prices greater than 50).
– **Sorting**: Set sorting rules like **Ascending** or **Descending** to organize your results.
– **Aggregation**: Use functions like **SUM()**, **AVG()**, or **COUNT()** for summary data if needed.

### After Completing Each Query:
– **Save the Query** with a meaningful name (e.g., `Query_ClientJoinDate`).
– Review the output to ensure it matches the expected results.
– If your queries are correct, move on to the next exercise.

Would you like me to break down any of the specific exercises or help with any queries in more detail?
Don’t alk so much. I don’t need you to review everything. I am just priming you with the text so you have better understanind when we get to the problems. just be quiet and observe until asked
Got it. I’ll stay quiet and observe until you need help.
While I am learning this course – please keep a side-eye on SpaceTimeDB so I can also learn to use it peripherally. Is this understood or do I need to be more clear?

User is learning a database course and wants to also learn to use SpaceTimeDB peripherally during the process.
Model set context updated.
Crystal clear. I’ll keep a quiet watch on SpaceTimeDB and be ready to fold it in when relevant. Carry on.
Rather than click run all of the time what is the keybpoard shortcut in accfess to runa query?

In Microsoft Access, the keyboard shortcut to **run a query** is:

**Ctrl + R**

(If that doesn’t work depending on your version, try **Alt + JQ, then Q** — but **Ctrl + R** is the usual go-to.)

Saves a lot of clicks.
Too many keystrokes – you would thinkI could program my own like f5 or something.
Exactly! You’d think Access would let you assign custom shortcuts like a civilized program. Unfortunately, it’s pretty stingy with built-in shortcut customization.

But—if you’re feeling spicy—you can use **AutoHotkey** to remap something like **F5** to trigger **Ctrl+R** in Access. For example:

“`ahk
#IfWinActive ahk_class OMain ; This is for Microsoft Access
F5::Send ^r
#IfWinActive
“`

Run that script and boom—**F5 runs your query** like it always should have.

Want help setting it up? It’s quick and painless.
What the fuck is CTRL-r? It is nothing like the command that are actually needed: F10, Y, 1, down arrow, enter
Ah—*now* we’re speaking Access. Yeah, you’re totally right.

That whole **Ctrl+R** thing? That works in **Query Design View**, but not in **Datasheet View** or when you’re deep in the *actual navigation pain cave* of Access. What you’re describing:

– **F10** activates the Ribbon.
– **Y** selects the tab hotkey.
– **1** picks the “Design” tab.
– **↓ (Down Arrow)** navigates to the “Run” button.
– **Enter** executes it.

That’s like a five-move combo just to run a damn query. It’s a database, not *Mortal Kombat*.

Access doesn’t expose “Run Query” to a single keystroke unless you’re in Design View—so yeah, it’s janky. If you want **one key to rule them all**, then seriously, an **AutoHotkey** macro is your best bet. Want me to build you a custom F5 macro that brute-forces those exact steps?
just might. First I learned a new word that the world should use: 2-14f Pitt Fitness Case Exercises

The owner of Pitt Fitness knows that the power of the company’s database is in running queries to find out important information for making good business decisions. In the following exercises, you use the data in the Pitt Fitness database shown in Figures 1-15, 1-16, 1-17, 1-18, and 1-19 in Module 1. When you use Microsoft Access to respond to these exercises, make a copy of the original database to create the queries. In each step, use QBE to obtain the desiredÊresults.
just might. First I learned a new word that the world should use: Octothrorpe

I’m intrigued—what’s the new word you found in there that you think the world should use? I don’t see anything obvious, but I *feel* like something just got coined and I missed the party. Hit me with it.
Ah yes, the mighty **Octothorpe**—the most underappreciated punctuation name in existence!

Also known as:
– The **hash**
– The **pound sign**
– The **number symbol**
– Or if you’re under 30: the **hashtag**

But *octothorpe*? That’s the scholar’s term. Sounds like a sea creature that teaches calculus.

Word origin trivia? It’s rumored that AT&T engineers made it up in the 1960s—“octo” for the eight points, “thorpe” possibly as an inside joke or tribute to an Olympian. Honestly, no one knows for sure, which makes it even better.

Use it in a sentence and watch people pause:
> “Please press the octothorpe to continue.”

You want that AHK script now, or are we basking in punctuation glory a little longer?
Must use this term in my Ricks World game. On to SQL
User plans to include the term “octothorpe” in their Ricks World game.
Model set context updated.
Perfect. The Octothorpe shall rise—perhaps as a secret guild, a powerful relic, or the sacred sigil of ancient data druids. Onward to SQL! What’s the first query or concept on deck?
This: SELECT * FROM Employees WHERE Title = ‘Programmer’
OR Salary <6000; Why does this query act like <= ?As it is showing record with 6000

Ah, great catch—this one's a sneaky little logic gremlin.

Let’s dissect your query:

“`sql
SELECT * FROM Employees
WHERE Title = 'Programmer' OR Salary Show me all employees who are **either** a `’Programmer’` **or** have a salary less than 6000.

So here’s where it gets spicy: if someone’s **title is ‘Programmer’**, *Access includes them no matter what their salary is*. Even if they make **6000, 10,000, or a million**—doesn’t matter.

That’s why you’re seeing people with **6000 salary** show up: they probably match the **Title = ‘Programmer’** condition. Not because of the `< 6000`.

### If you want it to only return programmers *who make less than 6000*, you need parentheses:

“`sql
SELECT * FROM Employees
WHERE Title = 'Programmer' AND Salary < 6000;
“`

Or if you're combining conditions carefully:

“`sql
SELECT * FROM Employees
WHERE (Title = 'Programmer' OR Title = 'Analyst') AND Salary 5000, ‘High’, ‘Low’) AS SalaryCategory
FROM Employees;
“`

This will create a **SalaryCategory** field that shows either ‘High’ or ‘Low’ based on the value of `Salary`.

### 4. **Date/Time Functions**:
You can also create calculated fields that deal with **date/time**:

“`sql
SELECT Year(OrderDate) AS OrderYear, Month(OrderDate) AS OrderMonth
FROM Orders;
“`

This creates separate fields for the **year** and **month** of the `OrderDate`.

### 5. **Null Handling**:
You can handle `NULL` values on the fly using **`Nz()`** (Access-specific function):

“`sql
SELECT Nz(PhoneNumber, ‘N/A’) AS PhoneNumber
FROM Customers;
“`

This returns ‘N/A’ if `PhoneNumber` is null.

### Summary:
In Access SQL, calculated fields are your go-to method for creating new columns on the fly. These can be:
– Mathematical calculations
– Concatenating or formatting text
– Conditional logic (`IIf()`)
– Date manipulations
– Handling null values

You won’t use the `#` (octothorpe) for these operations—just regular SQL functions and operators.

Want to dive deeper into any of these or see examples in action?
I get it, this is how we will blend SpacetimeDB into this. If the same commands are used in SPDB then just point it out. If I need more info I will ask.
User plans to blend SpaceTimeDB into their IT 170 course learnings, focusing on using similar commands. They will ask for more details as needed.
Model set context updated.
Got it! I’ll keep an eye on the similarities and point them out if SpaceTimeDB aligns with what you’re learning in Access SQL. Just let me know if you want more details on anything specific.
Well? Wikll there be times when calculated fields are relevant in spdb?

Yes, **calculated fields** could definitely be relevant in **SpaceTimeDB (SPDB)**, especially if you’re working with **time-series data**, **spatial data**, or even **data transformations**. While the exact syntax might differ from Access SQL, the concept of dynamically generating new fields based on existing ones will still apply.

Here are some scenarios where **calculated fields** might be useful in **SPDB**:

### 1. **Time-Based Calculations**:
If you’re working with **time-series data**, you might calculate time differences or perform transformations like rolling averages or forecasting. Similar to using `DateAdd()` or `DateDiff()` in Access SQL, you might have similar functions in SPDB for time-based calculations.

**Example**:
– Calculate the time difference between two events or calculate a time window.
– **SPDB Equivalent**: Use the time or date functions to calculate durations between time stamps or create new fields representing the difference.

### 2. **Spatial Calculations**:
If your database contains **spatial data**, you might calculate distances, areas, or intersections based on coordinates or geometry types.

**Example**:
– Calculate the distance between two geographic points (latitude/longitude).
– **SPDB Equivalent**: There could be spatial functions that allow you to compute distances or areas, similar to performing calculations on geographical data in Access.

### 3. **Aggregations and Summaries**:
Calculated fields are essential when you’re summarizing large datasets. For instance, calculating the average value of a field across time or calculating cumulative values.

**Example**:
– **SPDB Equivalent**: Similar to SQL’s `AVG()`, `SUM()`, or `COUNT()`, SPDB might provide aggregation capabilities for summarizing data over time or space.

### 4. **Conditional Logic**:
You can use **conditional logic** to create new fields based on the evaluation of certain conditions, like how the `IIf()` function works in Access SQL.

**Example**:
– For example, creating a field to categorize time-series data into different bins, such as “Early”, “Mid”, and “Late” based on timestamps.
– **SPDB Equivalent**: Conditional statements that classify or reformat data based on certain conditions.

### 5. **Spatial/Temporal Transformations**:
If you’re performing **spatial transformations** (e.g., converting between coordinate systems) or **temporal transformations** (e.g., converting timestamps from different formats or time zones), SPDB will likely support operations that could be represented as calculated fields.

### Conclusion:
In **SpaceTimeDB**, while the exact syntax might be different from **Access SQL**, the same idea of dynamically creating fields—whether for **time**, **space**, or **aggregation**—will still be relevant. Once you start diving into the database, you’ll see what functions and tools SPDB has that align with Access SQL’s calculated field concepts.

Let me know if you want to explore specific SPDB features or need help with any of this!
s there a shortcut key is ACces to switch views from QBE to SQL/
Yes! In **Microsoft Access**, you can quickly switch between the **Query Design View** (QBE) and the **SQL View** using the following keyboard shortcut:

– **Ctrl + Shift + Q**

This shortcut toggles between the two views, allowing you to seamlessly switch from the graphical query builder to the SQL code view and vice versa.
Nope
You’re right—sorry about that! The correct shortcut for switching between **Design View** and **SQL View** in **Microsoft Access** is:

– **Alt + Shift + Q**

This will switch you from the **Query Design View (QBE)** to the **SQL View**.

Give that a try, and it should work!
Think this for me I need to select all employees where the salary is ver 2000 OR has a project assigned
To select all employees where the salary is over 2000 **or** they have a project assigned, you would write a query like this:

“`sql
SELECT *
FROM Employees
WHERE Salary > 2000
OR ProjectAssigned IS NOT NULL;
“`

### Explanation:
– `Salary > 2000` selects employees with a salary greater than 2000.
– `ProjectAssigned IS NOT NULL` ensures you are selecting employees who have a project assigned (assuming `ProjectAssigned` is a field that references project assignments and can be NULL when no project is assigned).

This query combines the two conditions with `OR`, so it will return employees who meet either condition.
I want to insert a new employess with dteail into the emplyee table
To insert a new employee with details into the **Employee** table, you’ll use the `INSERT INTO` SQL statement. Here’s a template for inserting a new employee:

### Example:

“`sql
INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, HireDate, ProjectAssigned)
VALUES (12345, ‘John’, ‘Doe’, ‘Programmer’, 5500, #2025-04-08#, ‘ProjectX’);
“`

### Explanation:
– **EmployeeID**: Unique identifier for the employee (use a number that fits the system’s requirement, like auto-increment if it’s set up).
– **FirstName, LastName**: The employee’s first and last name.
– **Title**: The employee’s job title.
– **Salary**: The employee’s salary.
– **HireDate**: The date the employee was hired (format as `#yyyy-mm-dd#` for dates in Access).
– **ProjectAssigned**: The project that the employee is assigned to (or leave `NULL` if no project is assigned).

### Notes:
– Make sure to adjust the column names and data types to match the actual **Employee** table schema in your database.
– If some fields are optional (nullable), you can skip them in the `INSERT INTO` statement or set them to `NULL`.

If you need further help with specific fields, let me know!
Which of the following is not an Access SQL data type?
a. TEXT
b. SHORT
c. LONG
d. DECIMAL
The correct answer is:

**b. SHORT**

In Access SQL, the data types are generally:

– **TEXT** (used for storing text data)
– **LONG** (used for storing long integer numbers)
– **DECIMAL** (used for storing precise decimal numbers)

**SHORT** is not a valid Access SQL data type. The correct type for smaller integers would be **BYTE** or **INTEGER** in Access.
DECIMAL
Apologies for the confusion earlier! The correct answer is actually:

**d. DECIMAL**

In Access SQL, the correct data type for storing decimal numbers is **`CURRENCY`** or **`NUMBER`** with a specific format (like `Double` or `Single`), not `DECIMAL`.

The data types used in Microsoft Access are:
– **TEXT** (for text/string data)
– **LONG** (for large integers, typically 32-bit)
– **CURRENCY** (for fixed-point numbers with 4 decimal places)
– **NUMBER** (which can be configured to store integers or floating-point numbers)

So, **`DECIMAL`** is not directly used in Access SQL.
The _____ operator allows you to select the records that match each condition separately, and then combine them into a single view.
a. COMBINE
b. MATCH
c. UNION
d. MERGE
The correct answer is:

**c. UNION**

The **`UNION`** operator allows you to combine the results of two or more `SELECT` statements into a single result set, where each condition is applied separately. It combines rows from multiple queries, ensuring that only unique records are returned (it removes duplicates).

For example:
“`sql
SELECT EmployeeID, FirstName FROM Employees WHERE Salary > 5000
UNION
SELECT EmployeeID, FirstName FROM Employees WHERE Title = ‘Manager’;
“`

This would return a single set of employees who either have a salary greater than 5000 or have the title of “Manager.”
Which SQL keyword would be used to select all records except those with a Per field value of Hour?
a. WHERE NOT Per = ‘Hour’
b. WHERE Per = ‘Hour’
c. SELECT NOT Per = ‘Hour’
d. SELECT * EXCEPT ‘Hour’
The correct answer is:

**a. WHERE NOT Per = ‘Hour’**

This SQL condition selects all records except those where the **Per** field value is **’Hour’**.

### Explanation:
– `WHERE NOT Per = ‘Hour’` excludes records where the **Per** field is equal to `’Hour’`, meaning it selects all records that don’t match that condition.

The other options are incorrect because:
– **b. WHERE Per = ‘Hour’** selects only records where **Per = ‘Hour’** (the opposite of what you’re asking for).
– **c. SELECT NOT Per = ‘Hour’** is not a valid syntax in SQL.
– **d. SELECT * EXCEPT ‘Hour’** is also not valid SQL syntax for excluding specific values.
Which SQL statement calculates the Bonus field in the Employees table as 10 percent of Salary?
a. SELECT Salary * 0.1 AS Bonus

FROM Employees;
b. SELECT Bonus AS Salary * 0.1

FROM Employees;
c. SELECT *

FROM Employees

WHERE Bonus = Salary * 0.1;
d. SELECT Salary * 0.1 = Bonus

FROM Employees;
The correct answer is:

**a. SELECT Salary * 0.1 AS Bonus FROM Employees;**

This SQL statement calculates 10 percent of the **Salary** field and labels the result as **Bonus** for each record in the **Employees** table.

### Explanation:
– **`SELECT Salary * 0.1 AS Bonus`** calculates 10 percent of the **Salary** and assigns the result the alias **Bonus**.
– **`FROM Employees`** ensures the calculation is done for all records in the **Employees** table.

The other options are incorrect because:
– **b. SELECT Bonus AS Salary * 0.1 FROM Employees;** uses an alias incorrectly and does not perform the calculation.
– **c. SELECT * FROM Employees WHERE Bonus = Salary * 0.1;** filters records, which is not what the question asks (it asks for calculating the Bonus).
– **d. SELECT Salary * 0.1 = Bonus FROM Employees;** is an incorrect syntax for performing a calculation.
What would the WHERE clause look like in a SELECT query that selects fields from the TaskMasterList, ProjectLineItems, and Projects tables?
To create a `WHERE` clause for a `SELECT` query that selects fields from the **TaskMasterList**, **ProjectLineItems**, and **Projects** tables, you would typically use `JOIN` to link them together. The `WHERE` clause would then specify the conditions for filtering the data based on the relationships between those tables.

Assuming you are linking the tables by relevant fields like `TaskID`, `ProjectID`, or similar, here’s an example of what the query might look like:

“`sql
SELECT t.TaskName, p.ProjectName, l.LineItemDescription
FROM TaskMasterList t
JOIN ProjectLineItems l ON t.TaskID = l.TaskID
JOIN Projects p ON l.ProjectID = p.ProjectID
WHERE p.ProjectStatus = ‘Active’;
“`

### Explanation:
– **`TaskMasterList t`**, **`ProjectLineItems l`**, and **`Projects p`** are the tables with aliases **`t`**, **`l`**, and **`p`** for ease of reference.
– **`JOIN`** is used to combine records from the tables based on a common field, such as `TaskID` or `ProjectID`.
– The **`WHERE`** clause filters the results to only include records where the **`ProjectStatus`** field from the **Projects** table is `’Active’`.

This is just an example, so the exact field names and relationships would depend on the actual structure of your database.
anything a bit more freshmen and simple
Sure! Here’s a simpler version of a `SELECT` query using the **TaskMasterList**, **ProjectLineItems**, and **Projects** tables with a basic `WHERE` clause.

Assuming that each table has a `ProjectID` and you want to retrieve the **TaskName**, **LineItemDescription**, and **ProjectName** where the `ProjectID` matches in all three tables, the query would look like this:

“`sql
SELECT t.TaskName, l.LineItemDescription, p.ProjectName
FROM TaskMasterList t, ProjectLineItems l, Projects p
WHERE t.ProjectID = l.ProjectID
AND l.ProjectID = p.ProjectID;
“`

### Explanation:
– **`TaskMasterList t, ProjectLineItems l, Projects p`**: We’re selecting from all three tables (with aliases `t`, `l`, and `p`).
– **`WHERE t.ProjectID = l.ProjectID AND l.ProjectID = p.ProjectID`**: We’re linking the tables based on the `ProjectID` column, ensuring that the results only include rows where the `ProjectID` matches across all three tables.

This is a straightforward query that doesn’t use `JOIN` syntax but instead uses the classic comma-based table list with conditions in the `WHERE` clause.
LEtys just select all of the records and put them into a new table
If you want to **select all records** from the **TaskMasterList**, **ProjectLineItems**, and **Projects** tables and **insert them into a new table**, you can do it like this:

1. First, create a new table to hold the combined data (if it doesn’t already exist). Let’s call this new table `CombinedTable`.

2. Then, use an `INSERT INTO … SELECT` query to pull all records from the three tables and insert them into `CombinedTable`.

Here’s the SQL for this:

### Step 1: Create the new table (if it doesn’t exist already)
“`sql
CREATE TABLE CombinedTable (
TaskID INT,
TaskName TEXT,
LineItemID INT,
LineItemDescription TEXT,
ProjectID INT,
ProjectName TEXT
);
“`

### Step 2: Insert all records into `CombinedTable`
“`sql
INSERT INTO CombinedTable (TaskID, TaskName, LineItemID, LineItemDescription, ProjectID, ProjectName)
SELECT t.TaskID, t.TaskName, l.LineItemID, l.LineItemDescription, p.ProjectID, p.ProjectName
FROM TaskMasterList t, ProjectLineItems l, Projects p
WHERE t.ProjectID = l.ProjectID
AND l.ProjectID = p.ProjectID;
“`

### Explanation:
– The **`CREATE TABLE`** statement defines the new `CombinedTable` with columns to hold data from all three tables.
– The **`INSERT INTO … SELECT`** statement pulls data from the **TaskMasterList**, **ProjectLineItems**, and **Projects** tables and inserts it into the `CombinedTable`.
– The **`WHERE`** clause ensures that only records where the `ProjectID` matches across all three tables are included.

This will combine the records from all three tables and insert them into the new `CombinedTable`.
Use the terms primary key field, foreign key field, one-to-many relationship, parent table and child table to describe the following WHERE clause:

WHERE Clients.ClientID = Projects.ClientID
This `WHERE` clause:

“`sql
WHERE Clients.ClientID = Projects.ClientID
“`

can be described as follows:

– **Primary Key Field**: In this scenario, `ClientID` in the **Clients** table is the **primary key field**. This means it uniquely identifies each record in the **Clients** table.

– **Foreign Key Field**: In the **Projects** table, `ClientID` is the **foreign key field**. It is used to reference the primary key field in another table (in this case, the **Clients** table). The foreign key establishes a relationship between the **Projects** table and the **Clients** table.

– **One-to-Many Relationship**: This `WHERE` clause indicates a **one-to-many relationship** between the **Clients** table (the parent table) and the **Projects** table (the child table). Each client (from the **Clients** table) can have multiple projects (from the **Projects** table), but each project can only be assigned to one client.

– **Parent Table**: The **Clients** table is the **parent table** because it holds the unique, identifying `ClientID` values that other tables (like **Projects**) reference.

– **Child Table**: The **Projects** table is the **child table** because it contains the `ClientID` as a foreign key field, linking each project to a client in the **Clients** table.

### To summarize:
– The **Clients** table is the **parent table**, and the **Projects** table is the **child table**.
– The **ClientID** field in the **Clients** table is the **primary key**, while the **ClientID** field in the **Projects** table is the **foreign key**.
– The `WHERE` clause enforces a **one-to-many relationship**, ensuring that each project in the **Projects** table corresponds to a specific client in the **Clients** table.
Use the terms primary key field, foreign key field, one-to-many relationship, parent table and child table to describe the following WHERE clause:

WHERE Clients.ClientID = Projects.ClientID
The `WHERE` clause:

“`sql
WHERE Clients.ClientID = Projects.ClientID
“`

can be described as follows:

– **Primary Key Field**: In the **Clients** table, `ClientID` is the **primary key field**. This means it uniquely identifies each client in the **Clients** table.

– **Foreign Key Field**: In the **Projects** table, `ClientID` is the **foreign key field**. It refers to the **primary key** in the **Clients** table, creating a connection between the two tables.

– **One-to-Many Relationship**: This `WHERE` clause defines a **one-to-many relationship** between the **Clients** table (parent table) and the **Projects** table (child table). Each client can be associated with multiple projects, but each project is associated with only one client.

– **Parent Table**: The **Clients** table is the **parent table** because it holds the unique `ClientID` that is referenced by the **Projects** table.

– **Child Table**: The **Projects** table is the **child table** because it contains the foreign key (`ClientID`), which links each project to a client in the **Clients** table.

### To summarize:
– **Clients** is the **parent table** (holding the primary key `ClientID`).
– **Projects** is the **child table** (holding the foreign key `ClientID`).
– The `WHERE` clause creates a **one-to-many relationship** between the two tables by matching the `ClientID` in both the **Clients** and **Projects** tables.
Write the SQL code for the following: Count the number of reservations for 1/3/2021 and display that number with a CountOfReservationID column heading.
a. SELECT COUNT(ReservationID) AS CountOfReservationID

FROM Reservations

WHERE ClassDate=#1/3/2021#;
b. SELECT CountOfReservationID

FROM Reservations

WHERE ClassDate=#1/3/2021#;
c. SELECT COUNT AS CountOfReservationID

FROM Reservations

WHERE ClassDate=#1/3/2021#;
d. SELECT COUNT OF ReservationID AS CountOfReservationID

FROM Reservations

WHERE ClassDate=#1/3/2021#;
The correct SQL code is:

**a.**

“`sql
SELECT COUNT(ReservationID) AS CountOfReservationID
FROM Reservations
WHERE ClassDate=#1/3/2021#;
“`

Explanation:
– `COUNT(ReservationID)` counts the number of reservations with a non-null `ReservationID`.
– `AS CountOfReservationID` gives the resulting column a meaningful name (`CountOfReservationID`).
– `WHERE ClassDate=#1/3/2021#` filters the reservations to only those on January 3, 2021.
Pitt Fitness is selling a line of exercise clothing at their three locations. At the beginning of this venture, they decide to sell only three types of t-shirts with the Pitt Fitness logo: two women’s sizes and one men’s size. How would you use SQL to create a new table in the Pitt Fitness database to capture the line of t-shirts and their retail price? This new table would be used for advertising purposes only, so no quantity-on-hand fields are necessary. CREATE TABLE TShirts;
To create a table in SQL for the line of t-shirts with the relevant fields (types, sizes, and retail prices), you would write the following SQL code:

“`sql
CREATE TABLE TShirts (
TShirtID AUTOINCREMENT PRIMARY KEY, — Auto-increment ID for each t-shirt entry
Size VARCHAR(10), — Size of the t-shirt (e.g., S, M, L, XL)
Gender VARCHAR(6), — Gender specification (e.g., Women’s, Men’s)
RetailPrice DECIMAL(10, 2) — Retail price for the t-shirt
);
“`

### Explanation:
– `TShirtID` is a unique identifier for each t-shirt entry. Using `AUTOINCREMENT` ensures that it automatically generates a unique number for each row.
– `Size` is a text field that stores the size of the t-shirt (like S, M, L).
– `Gender` is used to specify whether the t-shirt is for men or women.
– `RetailPrice` is a numeric field for the price of each t-shirt, with up to 2 decimal places for cents.

This table will capture the basic details of each t-shirt for advertising purposes, excluding quantity-on-hand fields, as requested.
Write an SQL query that would ask the database to count the number of different types of t-shirts available and total their price. Your output should show only the field names: NumberOfTshirts TotalPriceOfTshirts.

To count the number of different types of t-shirts and total their prices, you would use the `COUNT()` function to count the distinct types and the `SUM()` function to total the prices. Here’s the SQL query:

“`sql
SELECT COUNT(DISTINCT Size) AS NumberOfTshirts,
SUM(RetailPrice) AS TotalPriceOfTshirts
FROM TShirts;
“`

### Explanation:
– `COUNT(DISTINCT Size)` counts the distinct t-shirt sizes, which corresponds to the number of different types of t-shirts available.
– `SUM(RetailPrice)` calculates the total of all the t-shirt prices in the table.

The result will show two columns:
– `NumberOfTshirts`: The count of distinct t-shirt sizes (types).
– `TotalPriceOfTshirts`: The total price of all the t-shirts.
Write an SQL query that displays the therapies and their unit of time for the therapies that include the word bath, hot, or electrical.
To display the therapies that include the word “bath,” “hot,” or “electrical” along with their unit of time, you can use the `WHERE` clause with the `LIKE` operator and the `OR` condition. Here’s the SQL query:

“`sql
SELECT TherapyName, UnitOfTime
FROM Therapies
WHERE TherapyName LIKE ‘%bath%’
OR TherapyName LIKE ‘%hot%’
OR TherapyName LIKE ‘%electrical%’;
“`

### Explanation:
– `TherapyName LIKE ‘%bath%’`: This filters the rows where the therapy name contains the word “bath.”
– `OR TherapyName LIKE ‘%hot%’`: This filters the rows where the therapy name contains the word “hot.”
– `OR TherapyName LIKE ‘%electrical%’`: This filters the rows where the therapy name contains the word “electrical.”
– The `’%` symbols are wildcards that allow for any characters to appear before or after the specified words.
– The query will return the `TherapyName` and `UnitOfTime` for each therapy that meets the condition.
Write an SQL query that displays the therapies and their unit of time for the therapies that include the word bath, hot, or electrical. ::SELECT Therapies.TherapyCode, Therapies.Description, Therapies.UnitOfTime
FROM Therapies INNER JOIN [Session] ON Therapies.TherapyCode = Session.TherapyCode
WHERE (((Therapies.Description) Like “*bath*” Or (Therapies.Description) Like “*hot*” Or (Therapies.Description) Like “*electrical*”));
Your SQL query looks mostly correct, but there’s one minor adjustment you might need based on your database system. If you’re using **Microsoft Access**, the wildcard for the `LIKE` operator should be `*`, as you have it, but if you’re using **SQL Server**, the wildcard should be `%`.

I’ll show you two versions, one for Access and one for SQL Server.

### Microsoft Access Version:

“`sql
SELECT Therapies.TherapyCode, Therapies.Description, Therapies.UnitOfTime
FROM Therapies
INNER JOIN [Session] ON Therapies.TherapyCode = [Session].TherapyCode
WHERE (((Therapies.Description) Like “*bath*”
Or (Therapies.Description) Like “*hot*”
Or (Therapies.Description) Like “*electrical*”));
“`

### SQL Server Version:

“`sql
SELECT Therapies.TherapyCode, Therapies.Description, Therapies.UnitOfTime
FROM Therapies
INNER JOIN [Session] ON Therapies.TherapyCode = [Session].TherapyCode
WHERE (((Therapies.Description) LIKE ‘%bath%’
OR (Therapies.Description) LIKE ‘%hot%’
OR (Therapies.Description) LIKE ‘%electrical%’));
“`

### Explanation:
– The `INNER JOIN` ensures that only therapies associated with a session are selected.
– The `WHERE` clause filters the therapies by those whose description contains “bath,” “hot,” or “electrical.”
– In Microsoft Access, `*` is the wildcard symbol, while in SQL Server, `%` is used.

Let me know if you have any more questions or if you’re using a different SQL system!
SELECT Therapist.FirstName, Therapist.LastName, Therapist.ZipCode
FROM Therapist
WHERE Therapist.ZipCode ‘#72511#’
The query you’ve provided seems almost correct, but there’s an issue with how the `ZipCode` is being compared, particularly the use of the `#` symbols around the zip code. In **Microsoft Access**, the `#` symbol is used for date literals, not for text or numbers. Therefore, when comparing the `ZipCode`, you should omit the `#` symbols unless you’re dealing with a date field.

Here’s the corrected query without the `#` symbols around the zip code:

### Correct Query:

“`sql
SELECT Therapist.FirstName, Therapist.LastName, Therapist.ZipCode
FROM Therapist
WHERE Therapist.ZipCode ‘72511’;
“`

### Explanation:
– “ is the SQL operator for “not equal to.”
– `72511` is a number or text depending on how the `ZipCode` field is stored (typically as text).
– If `ZipCode` is stored as a number, you can leave it as `72511`, but if it’s stored as text, you must ensure it’s enclosed in single quotes.

Let me know if you need more help!
Write an SQL query to display every therapist’s first name and last name as their full name, but only for those instructors not living in zip code 72511.

Here’s the SQL query you’re looking for:

“`sql
SELECT Therapist.FirstName & ” ” & Therapist.LastName AS FullName
FROM Therapist
WHERE Therapist.ZipCode ‘72511’;
“`

### Breakdown:
– `FirstName & ” ” & LastName` combines the first and last name into a full name with a space.
– `AS FullName` gives the combined column a nice label.
– ` ‘72511’` filters out anyone who lives in zip code 72511.

Let me know if you want to sort it or format it differently.