ISA NOTES ( SQL)

Tables in the stay well database

OWNER RESIDENT PROPERTY SERVICE_REQUEST

null

doesn't exist if Null has no than it must exist

If the data field is a text

put the number in ' '

field name

column name

what do you different with groups in the where slot

use HAVING instead

Is not

<>

What is a nested subquery? In which order does SQL evaluate nested subqueries?

A subquery can contain another subquery. The innermost subquery is executed first.

What information is contained in the CUSTOMER table in the KIMTAY database?

Customer name, contact information, and customer representative. ( C N R) aka ( Customers are Never Right)

entity

things ?

Which two conditions must be met in order to use UNION operators?

The two table must have the column datatypes and same number of columns.

How do you form a simple condition?

column name, comparison operator, column name or value.

fields

columns

Comparison Operators

<, >, <=, >=, ==, != indicate a Boolean expression

what is an invoice number?

simply a unique record number assigned to each invoice you issue.

What removes a table from a database?

Drop

records

rows

Which table in the KIMTAY database is ITEM ID found?

. ITEM and INVOICE_LINE tables

field

can only have one of

What is an alias? How do you specify an alias in SQL? Why would you use an alias?

The name of a table in a FROM clause can be followed by an alias, which is an alternate name for a table. You can use the alias in place of the table name throughout the SQL command. By using two different aliases for the same table in a single SQL command, you can join a table to itself.

primary key

"is the parents number"

all

*

What means equal

. NOT b. ^= c. !=

invoice

A form describing the goods or services sold, the quantity, and the price

On hand

is a value

is like

'letter%

Which of the following statements is valid? a. ALL operator: the condition is true only if it satisfies all values. ANY operator: the condition is true if it satisfies any value. b. ALL operator: the condition is true only if it satisfies all values. ANY operator: the condition is true if it satisfies more than one value. c. ALL operator: the condition is true only if it satisfies more than one value. ANY operator: the condition is true if it satisfies all value. d. ALL operator: the condition is true only if it satisfies any value. ANY operator: the condition is true if it satisfies all value. e. All of the above f. None of the above

. ALL operator: the condition is true only if it satisfies all values. ANY operator: the condition is true if it satisfies any value.

What is a database?

A database is a structure that contains different categories of information and the relationships between these categories.

compound operator

AND b. OR c. NOT

Which rows are included in a left outer join? What clause can you use to perform a left outer join in SQL?

All rows from the table on the left. The " LEFT JOIN " clause.

What is an EXISTS operator?

An operator that checks the existence of rows that satisfies some criterion

In SQL, which operator do you use to determine whether a value is between two other values without using an AND condition?

BETWEEN

How do you form a compound condition?

By combining simple conditions using AND, NOT, and OR operators V

How do you join a table to itself in SQL?

By using two different aliases for the same table in a single command.

What is the formal name for the product of two tables? How do you form a product in SQL?

Cartesian product. Include both tables in the FROM clause and omit the WHERE clause.

Which SQL command do you use to delete rows from a table?

DELETE

To plan a relational database

Determine the categories of information that your relational database will need. These categories will be tables in FileMaker Pro Advanced. For example, a Sales database might include these tables: Customers, which includes customer information; Invoices, which includes order information; and Products, which includes product information. 2.Determine how the tables are related to each other. You can do this by writing simple sentences that describe how the categories interact, such as "customers order products" and "invoices track customers' orders." 3.Connect one table to another to indicate a relationship between them. For example, customers can have invoices and invoices can have products. If a table has no relationship to another, it's probably unnecessary. In this example, an Employees table doesn't fit into this relational database.

How do you sort data in descending order?

Follow the name of the sort key with the DESC operator

how do you group data

GROUP BY clause

When grouping data in a query, how do you restrict the output to only those groups satisfying some condition?

HAVING clause

List two operators that you can use with subqueries as an alternate way of performing joins.

IN or EXISTS

Which SQL command do you use to add a row to a table?

INSERT

cartisan product

Is the combination of all rows in the first table and all rows in the second table.

How do you use the ANY operator with a subquery?

It precedes a subquery and the condition is true if it is satisfied by any value (one or more) produced by the subquery.

How do you use the ALL operator with a subquery?

It precedes it and the condition is true only if it is satisfied by all values produced by the subquery.

What are the steps to join two table?

List columns to be displayed list of tables containing displayed columns restrict rows from the two tables that have common values in matching columns.

How do you sort data on more than one sort key? What is the more important key called? What is the less important key called?

List the keys in order of importance in the ORDER BY clause. The more important one is called the major sort key or the primary sort key and the less important on is the minor sort key or the secondary sort key.

Which rows are included in an inner join? What clause can you use to perform an inner join in SQL?

Only matching rows from both tables. The " INNER JOIN " clause "

Which of the following would you use to produce a combination of all rows from two tables?

PRODUCT

How do you avoid including duplicate values in a query's results?

Proceed the column name with a DISTINCT operator

Which of the following statements is true?

Right outer join, all rows from the table on the right are included regardless of whether they match rows from the table on the left.

Which rows are included in a right outer join? What clause can you use to perform a right outer join in SQL?

Rows from the table on the right and only when they match. Use a "RIGHT JOIN" clause.

Which SQL command do you use to view the data in a table?

SELECT

List all details about all items. Order the output by item ID within location. (That is, order the output by location and then by item ID.) 0

SELECT * FROM ITEM ORDER BY LOCATION, ITEM_ID;

5-1 LAB Task 11: Find the ID, first name, and last name of each customer that currently has an invoice on file for Wild Bird Food (25 lb).

SELECT CUSTOMER.CUST_ID, FIRST_NAME, LAST_NAME FROM CUSTOMER JOIN INVOICES ON CUSTOMER.CUST_ID = INVOICES.CUST_ID JOIN INVOICE_LINE ON INVOICES.INVOICE_NUM = INVOICE_LINE.INVOICE_NUM JOIN ITEM ON ITEM.ITEM_ID = INVOICE_LINE.ITEM_ID WHERE DESCRIPTION = 'WILD BIRD FOOD (25 LB)';

Task 4: Use the IN operator to find the ID, first name, and last name of each customer for which as invoice was created on November 15, 2021.

SELECT CUST_ID, FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE CUST_ID IN (SELECT CUST_ID FROM INVOICES WHERE INVOICE_DATE='2021-11-15')

Task 5: Repeat Task 4, but this time use the EXISTS operator in your answer.

SELECT CUST_ID, FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE EXISTS (SELECT * FROM INVOICES WHERE INVOICES.CUST_ID=CUSTOMER.CUST_ID AND INVOICE_DATE='2021-11-15');

List the customer ID, the first name, and the last name of each customer represented by sales rep 10 or sales rep 15.

SELECT CUST_ID, FIRST_NAME, LAST_NAME FROM CUSTOMER WHERE REP_ID = '10' OR REP_ID= '15';

Task 3: For each invoice, list the invoice number, invoice date, item ID, quantity ordered, and quoted price for each invoice line that makes up the invoice.

SELECT I.INVOICE_NUM,INVOICE_DATE, ITEM_ID, QUANTITY, QUOTED_PRICE FROM INVOICES AS I, INVOICE_LINE AS IL WHERE I.INVOICE_NUM=IL.INVOICE_NUM;

*** Task 14: List the invoice number and invoice date for each invoice that contains an invoice line for a Wild Bird Food (25 lb).

SELECT INVOICES.INVOICE_NUM, INVOICE_DATE FROM INVOICES JOIN INVOICE_LINE ON INVOICES.INVOICE_NUM = INVOICE_LINE.INVOICE_NUM JOIN ITEM ON INVOICE_LINE.ITEM_ID = ITEM.ITEM_ID WHERE DESCRIPTION = 'WILD BIRD FOOD (25 lb)';

Task 7: For each invoice, list the invoice number, invoice date, item ID, description, and category for each item that makes up the invoice.

SELECT INVOICES.INVOICE_NUM, INVOICE_DATE, INVOICE_LINE.ITEM_ID, DESCRIPTION, CATEGORY FROM INVOICES, ITEM, INVOICE_LINE WHERE INVOICES.INVOICE_NUM = INVOICE_LINE.INVOICE_NUM AND INVOICE_LINE.ITEM_ID = ITEM.ITEM_ID;

mod 5 task 2 For each invoice placed on November 15, 2021, list the invoice number along with the ID, first name, and last name of the customer for which the invoice was created.

SELECT INVOICE_NUM, CUSTOMER.CUST_ID, FIRST_NAME, LAST_NAME FROM INVOICES, CUSTOMER WHERE CUSTOMER.CUST_ID=INVOICES.CUST_ID AND INVOICE_DATE = '2021-11-15'

Task 2: For each invoice placed on November 15, 2021, list the invoice number along with the ID, first name, and last name of the customer for which the invoice was created.

SELECT INVOICE_NUM, INVOICE_DATE, CUSTOMER.CUST_ID, FIRST_NAME, LAST_NAME FROM INVOICES JOIN CUSTOMER ON INVOICE.CUST_ID = CUSTOMER.CUST_ID;

Task 1: For each invoice, list the invoice number and invoice date along with the ID, first name, and last name of the customer for which the invoice was created.

SELECT INVOICE_NUM, INVOICE_DATE, CUSTOMER.CUST_ID, FIRST_NAME, LAST_NAME FROM INVOICES, CUSTOMER WHERE INVOICES.CUST_ID = CUSTOMER.CUST_ID;

Task 18: List the item ID, description, unit price, and category for each item that has a unit price greater than the unit price of every item in category CAT. Use the ALL operator in your query.

SELECT ITEM.ITEM_ID, DESCRIPTION, PRICE, CATEGORY FROM ITEM WHERE PRICE > ALL (SELECT PRICE FROM ITEM WHERE CATEGORY = 'CAT')f

Task 21: List the item ID, description, and category of all items that are in the DOG or CAT category and contain the word Small in the description.

SELECT ITEM_ID, DESCRIPTION, CATEGORY FROM ITEM WHERE CATEGORY AND('CAT', 'DOG') AND DESCRIPTION LIKE '%SMALL%';

: List the item ID, description, and on-hand value for each item where on-hand value is at least $1,500. Assign the name ON_HAND_VALUE to the computed column

SELECT ITEM_ID, DESCRIPTION, ON_HAND * PRICE AS ON_HAND_VALUE FROM ITEM_ID WHERE ON_HAND*PRICE >=1500;

List the item ID, description, and on-hand value of each item whose number of units on hand is more than the average number of units on hand for all items.

SELECT ITEM_ID, DESCRIPTION, ON_HAND*PRICE AS ON_HAND_VALUE FROM ITEM WHERE ON_HAND > (SELECT AVG(ON_HAND)FROM ITEM);

List the item ID, description, and on-hand value (units on hand * unit price) of each item in category CAT. (On-hand value is technically units on hand * cost, but there is no COST column in the ITEM table). Assign the name ON_HAND_VALUE to the computed column.

SELECT ITEM_ID, DESCRIPTION, ON_HAND, SUM( ON_HAND * PRICE) AS ON_HAND_VALUE FROM ITEM WHERE CATEGORY = 'CAT';

What is the item ID, description, and price of the least expensive item in the database?

SELECT ITEM_ID, DESCRIPTION, PRICE FROM ITEM WHERE PRICE + ( SELECT MIN(PRICE) FROM ITEM);

Task 22: KimTay Pet Supplies is considering discounting the price of all items by 10 percent. List the item ID, description, price, and discounted price for all items. Use DISCOUNTED_PRICE as the name for the computed column.

SELECT ITEM_ID, DESCRIPTION,PRICE, PRICE - (.1*PRICE) AS DISCOUNTED_PRICE FROM ITEM;

List the REP_ID and the corresponding sum of the balances, as BALANCE, of all customers for each sales rep. Order and group the results by sales rep ID.

SELECT REP_ID, SUM(BALANCE) AS BALANCE FROM CUSTOMER GROUP BY REP_ID ORDER BY REP_ID;

Task 20: List the sum of the balances of all customers for each sales rep but restrict the output to those sales reps for which the sum is more than $150. The results should display the REP_ID and BALANCE column headers. Order the results by sales REP_ID.

SELECT REP_ID, SUM(BALANCE) AS BALANCE FROM CUSTOMER GROUP BY REP_ID HAVING SUM(BALANCE) > 150 ORDER BY REP_ID

Describe the basic form of the SQL SELECT command.

SELECT-FROM-WHERE So it should go "SELECT" specify columns and the table name that contains these columns after the word "FROM" and then if you need to you can put conditions after the word "WHERE."

invoice lines

Sometimes called line items. Each invoice line contains an item ID, an item description, the number of units of the item ordered, and the quoted price for the item. Each invoice line also contains a total, usually called an extension, which is the result of multiplying the number ordered by the quoted price

In which clause would you use a wildcard in a condition

The WHERE CLAUSE The LIKE condition allows wildcards to be used in the WHERE clause

Parent Table

The main table of a relationship. When creating a relationship, this is the "one" side of the relationship and contains the primary key.

Child Table

The second table of a relationship. When creating a relationship, this is generally the "many" side of the relationship. One record from the parent table (such as clients) can be related to one or more records of the child table (such as sales).

What are the two wild cards in SQL?

The percentage ( % ) wildcard matches any string of zero or more characters. The underscore ( _ ) wildcard matches any single character.

normalization

The process of applying rules to a database design to ensure that information is divided into the appropriate tables. parent to child relationship

Aggergate Functions

These can be used in a SELECT clause in place of a column. COUNT, MAX, MIN, SUM, AVG.

Aggergate Functions

They bring together items These can be used in a SELECT clause in place of a column. COUNT, MAX, MIN, SUM, AVG.

IN operator

Trying to find a specific word in the where category like dog or cat

What command would you use to show all rows of two tables? How would you use it? What command would you use to show only common rows between two tables? How would you use it?

UNION. You use it by putting it after the FROM line. You would use the intersect command to show the common rows between two tables you use it after the where line.

Which SQL command do you use to change the value in a column in a table?

UPDATE

How do you sort data?

Use an ORDER BY clause

How do you determine whether a column contains one of a particular set of values without using an AND condition?

Use the IN operator like if you are trying to find the date of which something was not created and do the where and then the cust_id ( or whatever you put after select) and then IN and then a sub query so parenthesis

How do you use a computed column in SQL? How do you name the computed column?

Using arithmetic operators and by writing the computation in place of a column name. You can name it by following the computation with "AS" and then the name you want.

When you need to retrieve rows that satisfy some condition, you include a

WHERE clause in the SELECT command, as shown in Example 3.

When must you qualify names in SQL commands? How do you qualify a column name?

When referring to matching columns in different tables, you must qualify the column names to avoid confusion. You qualify column names using the following format: table name.column name.

What does it mean for two tables to be union compatible?

When they have the same number of columns and their corresponding columns have identical data types and lengths.

Which of the following is not true about joining a table to itself? a. Known as SELF-JOIN. b. Must use columns aliases. c. Must join the same table using primary key. d. Known as INNER-JOIN e. All of the above

a

What is an alias?

an alternate name for a table in the FROM clause

on hand quantities

are always a number ( without '' quotes )

unique identifiers

are things like ID numbers

order by automates to

ascending a,b,c to change put DESC after ORDER BY

Which of the following is not a SET operator in MS SQL Server? a. UNION b. INTERSECT c. MINUS d. EXCEPT e. All of the above f. None of the above

b

attribute

behavior properties

Which of the following is not a valid JOIN command? a. INNER JOIN b. OUTER JOIN c. FULL INNER JOIN d. FULL OUTER JOIN e. All of the above f. None of the above

c

What are the tables for the KIMTAY database?

customers sales rep item invoices invoice line

dates

full year-month-day

where is used for

groups idk

Between

is inclusive 1-6 includes 1 and 6

A sub query

is referred to as a query within a query. It is evaluated first. It is referred to as the inner query. Its results are used by the outer query.

any time you do a function

it starts and ends with parentheses

An item is sold to how many customers per invoice

one

For the KimTay database a customer may have how many relationships.

one

b. An ITEM is sold to how many customers in an invoice?

only one

Basic form of SQL

select, from, where. Always need the first two.

when you do a subquery

the inside is evaluated first

primary key must be

unique