Lecture 12/13: Intro to SQL Queries

LIKE paired with wildcard characters (______). What are 2 wildcard characters?

( %, _ )

Date operations: What mathematical expressions can be applied to date fields?

(+, -)

Numerical Operations: Use mathematical expressions such as __________ to calculate new columns based on numeric fields

(+,-,*,/)

Some aggregate functions ___________ may be applied to text/character fields

(MIN, MAX, COUNT)

Numerical Operations: Use aggregate functions such as __________ to perform basic mathematical analysis with numeric fields

(MIN, MAX, SUM, ...)

SELECT: Use _____ to select all columns

*

Explain how the WHERE condition works in the Update Data Syntax.

- WHERE condition assigns your update command to a single row (or set of rows) - if you do not include WHERE condition, all rows will be updated.

Insert Data Syntax: Describe step 4 (VALUES)

-list of values in parenthesis -text fields (VARCHAR2, CHAR) and dates must be enclosed in single quotes '..'

*** You will be expected to know set of core functions (MIN, MAX, AVG, COUNT, SUM, ROUND) for exams ***

...

^^above are pretty much a review from lectures 10/111

...

WHERE: What operators generate ranges?

1. BETWEEN ______ AND ______ 2. NOT BETWEEN ______ AND ______

Insert Data Syntax: what are the 5 steps?

1. INSERT INTO 2. Table Name 3. List of Columns you are adding values for (in parenthesis) 4. VALUES (value1, value2, value3,...) 5. Always close your SQL statements with a semicolon ;

If using multiple Booleans, order of precedence is important. What is the correct order?

1. NOT 2. AND 3. OR

Update Data Syntax: what are the 5 steps

1. UPDATE -case-insensitive, usually all caps 2. Table Name -case-sensitive, no spaces 3. SET column_name = new_value 4. WHERE condition 5. Always close your SQL statements with a semicolon;

In order to return the "total majors" from the student table we need to:

1. apply the aggregate function COUNT() -we cannot SUM majors because Major is a text field -by default, COUNT will return the number of rows in the results 2. We must specify DISTINCT/UNIQUE to remove duplicates 3. Always rename new, calculated columns with AS

Boolean Operators: Negate an expression A) NOT B) AND C) OR

A; NOT

Describe what is happening here: INSERT INTO CUSTOMER (CustomerID, CustomerName) VALUES (10002, 'Johnson Brothers Furniture');

Adds a new customer with data values for "some" (but not all) attributes. -fields without value will automatically be filled with NULL

Describe what is happening here: INSERT INTO CUSTOMER VALUES (10003, 'Cozy Corner', '1313 9th St', 'Minneapolis', 'MN', '55111');

Adds a new customer with data values for ALL attributes -If you do not include the list of column names, data values must be in the same order as the table columns

Boolean Operators: Join two or more conditions and returns results only when all conditions are true A) NOT B) AND C) OR

B; AND

___________ operators are used to join multiple WHERE conditions.

Boolean

Combine multiple HAVING conditions with....?

Boolean operators (AND, OR, NOT)

Boolean Operators: Join two or more conditions and returns results when any conditions are true A) NOT B) AND C) OR

C; OR

Add ___________ or ___________ clause to display only non-duplicate rows

DISTINCT or UNIQUE

Describe GROUP BY syntax.

Divides data into subsets -categorize rows into groups based on the values of the specified field -may apply aggregate functions (SUM, COUNT,...) to each group -one row will appear for each group in the results

What if you are asked to show only the top 5, 10, 20 rows ranked? What clause should be used?

FETCH

Which part of the syntax below is a list of table(s) to search? SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostal FROM CUSTOMER;

FROM CUSTOMER;

Which clause combines filtering criteria (WHERE) with grouping criteria (GROUP BY)?

HAVING

WHERE: What syntax is used for handling Null values?

IS NULL, IS NOT NULL

Describe SELECT query syntax

List of column expressions (column names, operators, functions) -can be columns from one table or multiple tables -can select all columns from a table simultaneously with * (asterisk)

Explain the WHERE syntax for SQL queries.

List of logical expression that filter results matching the stated criteria. -each is a test returning a TRUE/FALSE result

Date operations: What aggregate functions can be applied to date fields?

MIN, MAX, COUNT

Functions always have a _________.

Name Ex: (MIN, ROUND, TO_DATE,..)

Multiple Groupings: Will this query work to find the number of customers in each city of each state? If not, why? And what would be correct? SELECT CustomerState, CustomerCity, COUNT(CustomerID) FROM CUSTOMER GROUP BY CustomerState;

No, only the field referenced in the GROUP BY clause (plus any aggregate functions) can be includeed in the SELECT clause. And every field in SELECT (minus aggregate functions) must be included in GROUP BY. SELECT CustomerState, CustomerCity, COUNT(CustomerID) FROM CUSTOMER GROUP BY CustomerState, CustomerCity;

What is the syntax below doing? SELECT COUNT(CustomerID) AS TexasCustomers FROM CUSTOMER WHERE State = 'TX';

Returning the number of customers from Texas.

What is happening in the syntax below? SELECT SalespersonFirst || ' ' || SalespersonLast AS FullName FROM SALESPERSON;

Returns salespersons full name as one field, by joining columns containing first and last name as one column then renames the column as FullName

Describe the following syntax: SELECT ProductDescription, ProductPrice FROM PRODUCT WHERE ProductPrice <400 AND (ProductFinish = 'Cherry' OR ProductFinish= 'Maple') ;

Returns the product description and product price for products that cost less than $400 and are made of cherry or maple wood.

Create syntax for the below info: Return all customers of Pine Valley Furniture Co. whose name includes the word "Furnishings"

SELECT * FROM CUSTOMER WHERE CustomerName LIKE '%Furnishings%' ;

ORDER BY: Pine Valley Furniture Co. is creating a list of customers in CA in alphabetical order. A query returning the customer info might look something like this:

SELECT * FROM CUSTOMER WHERE CustomerState = 'CA' ORDER BY CustomerName ASC;

ORDER BY: Show all customers who live in Florida, Texas, or California, and list the customers alphabetically by state and alphabetically by customer name within each state.

SELECT * FROM CUSTOMER WHERE CustomerState IN ('CA', 'FL', 'TX') ORDER BY CustomerState ASC, CustomerName ASC;

The STUDENT table contains information about students, including ID, first name, last name, birthdate, and major (assume each student has one major). Write queries to: 2. Return the information for every student who is older than 20, but does not have a major declared (Today's Date = 05/05/2019)

SELECT * FROM STUDENT WHERE Birthdate <= '05/05/1999' AND Major IS NULL; or.. SELECT * FROM STUDENT WHERE (SYSDATE - Birthdate)/365.2425 >= 20 AND Major IS NULL;

Create syntax for the info below: The advising office needs a list of all students who have not declared a major.

SELECT * FROM STUDENT WHERE MAJOR IS NULL;

Create an equivalent syntax for the below: SELECT * FROM STUDENT WHERE Major IN ('English', 'Journalism', 'Communications') ; Hint: use OR

SELECT * FROM STUDENT WHERE Major = 'English' OR Major = 'Journalism' OR Major = 'Communications' ;

Create syntax to: Return all students whose major is English, Journalism, or Communications Hint: Set operators

SELECT * FROM STUDENT WHERE Major IN ('English', 'Journalism', 'Communications') ;

Show how to answer: What is the average price of all products?

SELECT AVG(ProductPrice) AS AveragePrice FROM PRODUCT;

What is the syntax to return the previous slide ^^ Total Majors? (Table = STUDENT)

SELECT COUNT(DISTINCT Major) AS Num_Majors FROM STUDENT;

Show how to select the CustomerName column and rename it as "Name"

SELECT CustomerName AS Name

Pine Valley Furniture Co. wants to send a targeted advertisement to all customers from California, New York, and New Jersey. Write a SQL query to accomplish this without using a set operator.

SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState FROM CUSTOMER WHERE CustomerState= 'CA' OR CustomerState= 'NY' OR CustomerState= 'NJ' ;

Which part of the below syntax is a list of fields to return in output? SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostal FROM CUSTOMER;

SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostal

Pine Valley Furniture Co. wants to print out customer mailing labels, including name, street address, city, state, and zip code. A query returning the customer info might look like what?

SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostal FROM CUSTOMER;

GROUP BY: Pine Valley Furniture Co. wants to count the number of customers it has in each state. A query returning the customer info might look something what?

SELECT CustomerState, COUNT(CustomerID) FROM CUSTOMER GROUP BY CustomerState;

HAVING: Find states with more than one customer and sort the states based on the number of customers from high to low

SELECT CustomerState, COUNT(CustomerID) AS TotalCustomers FROM CUSTOMER GROUP BY CustomerState HAVING COUNT(CustomerID) >1 ORDER BY TotalCustomers DESC;

HAVING: Return the state name and total customers for each state where Pine Valley Furniture has more than one customer

SELECT CustomerState, COUNT(CustomerID) AS TotalCustomers FROM CUSTOMER GROUP BY CustomerState HAVING COUNT(CustomerID) >1;

The STUDENT table contains information about students, including ID, first name, last name, birthdate, and major (assume each student has one major). Write queries to: 3. Find the birthdates of the oldest and youngest students enrolled in the Marketing, Finance, or Informatics major

SELECT MAX(Birthdate) AS Oldest, MIN(Birthdate) AS Youngest FROM STUDENT WHERE Major IN ('Marketing', 'Finance', 'Informatics');

Create a syntax to return the most recent order.

SELECT MAX(OrderDate) AS MostRecent FROM ORDER; By using MAX, it returns the "oldest" date in the system, AKA most recent

Show how to return the number of days since an order was placed. Columns needed: - OrderID - OrderDate Table = ORDER

SELECT OrderID, SYSDATE - OrderDate AS DaysSinceOrder FROM ORDER;

Create syntax to: Return the description and price for each product between $200 and $300 (inclusive)

SELECT ProductDescription, ProductPrice FROM PRODUCT WHERE ProductPrice BETWEEN 200 AND 300;

FETCH Top-K Results: Find the top 2 most expensive products made with natural ash wood.

SELECT ProductID, ProductDescription, ProductStandardPrice FROM PRODUCT WHERE ProductFinish = 'Natural Ash' ORDER BY ProductStandardPrice DESC FETCH FIRST 2 ROWS ONLY;

Show how to List the standard price and new price (+10%) for each product. Need columns: -ProductID -ProductPrice Table = PRODUCT

SELECT ProductID, ProductPrice, ProductPrice * 1.1 AS NewPrice FROM PRODUCT

The STUDENT table contains information about students, including ID, first name, last name, birthdate, and major (assume each student has one major). Write queries to: 1. List the student ID and name for every student whose last name begins with "Mc"

SELECT StudentID, FirstName, LastName FROM STUDENT WHERE LastName LIKE 'Mc%' ; Note: Mc% finds a name that starts with Mc. If for example, the name was Kramer and I wanted to find last names ending in 'er' then i would use '%er'

Drinking Age (2): Create syntax given the information below -Comparing dates is not very flexible (will be out of date tomorrow) -Instead calculate age as today's date - birthdate -Will return age as number of days (divide by 365 to get an estimate of years)

SELECT StudentID, FirstName, LastName FROM STUDENT WHERE (SYSDATE - Birthdate)/365 >=21 ;

Drinking Age: Create syntax given the information below -SELECT the fields specified in the problem description (ID, name) -Add criteria to WHERE clause of query -Use comparison operators <, =, !=, > (all people born on or before 2/27/98 would be at least 21 years old)

SELECT StudentID, FirstName, LastName FROM STUDENT WHERE Birthdate <= '02/27/1998' ;

Create a query for the below info: The Accounting department needs a list of the IDs, names and emails of currently declared majors. (Table = STUDENT)

SELECT StudentID, FirstName, LastName, Email FROM STUDENT WHERE Major = 'Accounting';

WHERE: _________ operators are used to match a list of values.

Set (IN, NOT IN)

What does the ORDER BY clause do?

Sorts results in ascending or descending order

Can combine HAVING with ORDER BY (T/F)?

TRUE

ORDER BY can be applied to numbers, dates, and text (t/f)?

TRUE -can specify multiple ordering criteria

Text Operations: How do I join two fields of text?

Use concatenation operation ||

HAVING clause allows logical tests based on.....?

aggregate function (MIN, MAX, SUM,...)

Use ______________ to rename columns in the output

alias to (AS clause)

DISTINCT (or UNIQUE) removes __________ from results

duplicates Ex: SELECT DISTINCT Major FROM STUDENT;

WHERE uses ______ or __________ operators

equality or comparison such as: (=, <>, <, >)

HAVING clause provides conditions for __________ __________.

group selection

Functions take in 0 or more ________ ___________, listed in parenthesis after the function name

input parameters

Define "Function"

named section of programming that performs a specific task

When using multiple booleans, use ____________ to avoid confusion.

parenthesis -without them, statements with same syntax generate different outputs.

Functions return one or more outputs (T/F)?

true

Insert Data Syntax: step 3 in Insert Data Syntax is optional if....?

you are adding values for every column, in the same order of the columns