CS401A's Finals: Info Management Module 08 Flashcards
For final exams. (67 cards)
1
Q
SQL Functions
performs a calculation on a set of values and returns a single value.
A
- An aggregate function
2
Q
SQL Functions
- The basic aggregate functions are the following:
A
○ MIN
○ MAX
○ COUNT
○ SUM
○ AVG
3
Q
SQL Functions
- The basic
\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are the following:
— the minimum attribute value encountered in a given column
A
aggregate functions
○ MIN
4
Q
SQL Functions
- The basic
\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are the following:
— the maximum attribute value encountered in a given column
A
aggregate functions
○ MAX
5
Q
SQL Functions
- The basic
\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are the following:
— the number of rows containing non-null values
A
aggregate functions
○ COUNT
6
Q
SQL Functions
- The basic
\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are the following:
— the sum of all values for a given column
A
aggregate functions
○ SUM
7
Q
SQL Functions
- The basic
\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are the following:
— the arithmetic mean (average) for a specified column
A
aggregate functions
○ AVG
8
Q
SQL Functions
- The basic
\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are the following:
Table 2. Orders
Amount 130.50 297.50 794.25 332.75
-
\_\_\_\_\_
○ Example: SELECT\_\_\_\_\_(Amount) FROM Orders;
○ Result: 130.50
A
- The basic aggregate functions are the following:
Table 2. Orders
Amount 130.50 297.50 794.25 332.75
-
MIN
○ Example: SELECT MIN(Amount) FROM Orders;
○ Result:\_\_\_\_\_\_\_
9
Q
SQL Functions
- The basic
\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are the following:
Table 2. Orders
Amount 130.50 297.50 794.25 332.75
-
\_\_\_\_\_
○ Example: SELECT\_\_\_\_\_(Amount) FROM Orders;
○ Result: 794.25
A
- The basic aggregate functions are the following:
Table 2. Orders
Amount 130.50 297.50 794.25 332.75
-
MAX
○ Example: SELECT MAX(Amount) FROM Orders;
○ Result:\_\_\_\_\_\_\_
10
Q
SQL Functions
- The basic
\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are the following:
Table 2. Orders
OrderID 1 2 3 4
-
\_\_\_\_\_
○ Example 1: SELECT\_\_\_\_\_(OrderID) FROM Orders;
○ Result: 4
A
- The basic aggregate functions are the following:
Table 2. Orders
OrderID 1 2 3 4
-
COUNT
○ Example 1: SELECT COUNT(OrderID) FROM Orders;
○ Result:\_\_
11
Q
SQL Functions
- The basic
\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are the following:
Table 2. Orders
OrderID | Amount 1 | 130.50 2 | 297.50 3 | 794.25 4 | 332.75
○ Example 2: SELECT \_\_\_\_\_(OrderID) FROM Orders WHERE Amount > 300;
○ Result: 2
A
- The basic aggregate functions are the following:
Table 2. Orders
OrderID | Amount 1 | 130.50 2 | 297.50 3 | 794.25 4 | 332.75
-
COUNT
○ Example 2: SELECT COUNT(OrderID) FROM Orders WHERE Amount > 300;
○ Result:\_\_\_
12
Q
SQL Functions
- The basic
\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are the following:
Table 2. Orders
Amount 130.50 297.50 794.25 332.75
-
\_\_\_\_\_
○ Example: SELECT\_\_\_\_\_(Amount) FROM Orders;
○ Result: 1555
A
- The basic aggregate functions are the following:
Table 2. Orders
~~~
Amount
130.50
297.50
794.25
332.75
```
* SUM
○ Example: SELECT SUM(Amount) FROM Orders;
○ Result: \_\_\_\_\_\_\_
13
Q
SQL Functions
- The basic
\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are the following:
Table 2. Orders
Amount 130.50 297.50 794.25 332.75
-
\_\_\_\_\_
○ Example: SELECT\_\_\_\_\_(Amount) FROM Orders;
○ Result: 388.75
A
- The basic aggregate functions are the following:
Table 2. Orders
Amount 130.50 297.50 794.25 332.75
-
AVG
○ Example: SELECT AVG(Amount) FROM Orders;
○ Result:\_\_\_\_\_\_\_\_\_
14
Q
SQL Functions
is often used with aggregate functions to \_\_\_\_\_\_\_\_ the result-set by one or more columns
A
- The GROUP BY command option
15
Q
SQL Functions
Table 2. Orders
OrderID | OrderDate 1 | 2020-02-14 2 | 2020-02-14 3 | 2020-02-14 4 | 2020-02-15
-
\_\_\_\_\_ \_\_command option…
○ Example: SELECT OrderDate, COUNT(OrderID) FROM Orders\_\_\_\_\_ \_\_OrderDate;
○ Result:
OrderDate | (No column name) 2020-02-14 | 3 2020-02-15 | 1
A
Table 2. Orders
OrderID | OrderDate 1 | 2020-02-14 2 | 2020-02-14 3 | 2020-02-14 4 | 2020-02-15
-
GROUP BY command option…
○ Example: SELECT OrderDate, COUNT(OrderID) FROM Orders GROUP BY OrderDate;
○ Result:
16
Q
SQL Functions
to assign a temporary name to the column. It can also be given to tables.
A
-
GROUP BY command option…
Use an alias to
17
Q
SQL Functions
Table 2. Orders
OrderID | OrderDate 1 | 2020-02-14 2 | 2020-02-14 3 | 2020-02-14 4 | 2020-02-15
-
\_\_\_\_\_ \_\_command option\_\_\_\_\_
○ Example: SELECT OrderDate, COUNT(OrderID)\_\_Quantity FROM Orders\_\_\_\_\_ \_\_OrderDate;
○ Result:
OrderDate | Quantity 2020-02-14 | 3 2020-02-15 | 1
A
Table 2. Orders
OrderID | OrderDate 1 | 2020-02-14 2 | 2020-02-14 3 | 2020-02-14 4 | 2020-02-15
-
GROUP BY command option
alias
○ Example: SELECT OrderDate, COUNT(OrderID) AS Quantity FROM Orders GROUP BY OrderDate;
○ Result:
18
Q
SQL Functions
is used to restrict the output of a GROUP BY query by applying conditional criteria to the grouped rows.
A
- The HAVING command option
19
Q
SQL Functions
Table 2. Orders
OrderID | OrderDate 1 | 2020-02-14 2 | 2020-02-14 3 | 2020-02-14 4 | 2020-02-15
- The
\_\_\_\_\_\_command option\_\_\_\_\_
○ Example: SELECT OrderDate, COUNT(OrderID)\_\_Quantity FROM Orders\_\_\_\_\_ \_\_OrderDate\_\_\_\_\_\_Quantity > 2;
○ Result:
OrderDate | Quantity 2020-02-14 | 3
A
Table 2. Orders
OrderID | OrderDate 1 | 2020-02-14 2 | 2020-02-14 3 | 2020-02-14 4 | 2020-02-15
- The HAVING command option
alias
○ Example: SELECT OrderDate, COUNT(OrderID) AS Quantity FROM Orders GROUP BY OrderDate HAVING Quantity > 2;
○ Result:
20
Q
SQL Functions
- The most commonly used date functions are as follows:
A
- The most commonly used
\_\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are as follows:
○ YEAR
○ MONTH
○ DAY
○ GETDATE
○ DATEADD
○ DATEDIFF
21
Q
SQL Functions
- The most commonly used
\_\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are as follows:
— returns a four-digit\_\_\_\_
A
- The most commonly used date functions are as follows:
○ YEAR —
22
Q
SQL Functions
- The most commonly used
\_\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are as follows:
— returns the number of the month
A
- The most commonly used date functions are as follows:
○ MONTH
23
Q
SQL Functions
- The most commonly used
\_\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are as follows:
— returns the number of the day
A
- The most commonly used date functions are as follows:
○ DAY
24
Q
SQL Functions
Table 2. Orders
OrderID | OrderDate 1 | 2020-02-14 2 | 2020-02-14 3 | 2020-02-14 4 | 2020-02-15
- The most commonly used
\_\_\_\_\_\_\_\_\_ \_\_\_\_\_\_\_\_\_are as follows:
○\_\_\_\_\_\_\_\_
○\_\_\_\_\_\_\_\_
○\_\_\_\_\_\_\_\_
Example: SELECT OrderID,\_\_\_\_\_\_\_\_(OrderDate) AS Year,\_\_\_\_\_\_\_\_(OrderDate) AS Month,\_\_\_\_\_\_\_\_(OrderDate) AS Day FROM Orders;
Result of query:
OrderID | Year | Month | Day 1 | 2020 | 2 | 14 2 | 2020 | 2 | 14 3 | 2020 | 2 | 14 4 | 2020 | 2 | 15
A
Table 2. Orders
OrderID | OrderDate 1 | 2020-02-14 2 | 2020-02-14 3 | 2020-02-14 4 | 2020-02-15
- The most commonly used date functions are as follows:
○ YEAR
○ MONTH
○ DAY
Example: SELECT OrderID, YEAR(OrderDate) AS Year, MONTH(OrderDate) AS Month, DAY(OrderDate) AS Day FROM Orders;
Result of query:
25
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
— returns the current date and time
* The most commonly used date functions are as follows:
○ **GETDATE** —
26
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
*Syntax:* **SELECT** `________`();
*Result:*
```
(No column name)
2025-05-27 23:10:26.623
```
* The most commonly used date functions are as follows:
○ **GETDATE**
GETDATE()
27
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
***Example 1**:* SELECT `________`(`_______`, 2, '2020/12/14');
*Result:* 2021-02-14 00:00:00.000
* The most commonly used date functions are as follows:
○ **DATEADD**
month
28
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
***Example 2**:* SELECT `________`(`_______`, 2, '2020/02/14');
*Result:* 2020-08-14 00:00:00.000
* The most commonly used date functions are as follows:
○ **DATEADD**
quarter
29
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
***Example 3**:* SELECT `________`(`_______`, -2, '2020/02/14');
*Result:* 2019-12-14 00:00:00.000
* The most commonly used date functions are as follows:
○ **DATEADD**
month
30
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
***Example 4**:* SELECT `________`(`_______`, 2, `________`(`_______`, 30, '2020/02/14'));
*Result:* 2020-02-14 02:30:00.000
* The most commonly used date functions are as follows:
○ **DATEADD**
DATEADD
hour
DATEADD
minute
31
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
— returns the difference between two (2) dates
* The most commonly used date functions are as follows:
○ **DATEDIFF**
32
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
*Syntax:* `_________`(datepart, date1, date2);
*Example:* SELECT `_________`(`_______`, '1989-07-25', '2020/02/14');
*Result:* 367
* The most commonly used date functions are as follows:
○ **DATEDIFF**
DATEDIFF
DATEDIFF
month
33
# **SQL Functions**
* The following are some of the most used numeric functions:
○ **ABS**
○ **ROUND**
○ **CEILING**
○ **FLOOR**
34
# **SQL Functions**
* The following are some of the most used `_________ _________`:
— returns the absolute value of a number
* The following are some of the most used numeric functions:
○ **ABS**
35
# **SQL Functions**
* The following are some of the most used `_________ _________`:
*Example:* SELECT `_________`(-234.5);
*Result:* 234.5
* The following are some of the most used numeric functions:
○ **ABS**
ABS
36
# **SQL Functions**
* The following are some of the most used `_________ _________`:
— rounds a number to a specified number of demical places
* The following are some of the most used numeric functions:
○ **ROUND**
37
# **SQL Functions**
* The following are some of the most used `_________ _________`:
*Syntax:* `________`*(number, decimal places)*;
***Example 1:*** SELECT `_________`(234.459, 1);
*Result:* 234.500
* The following are some of the most used numeric functions:
○ **ROUND**
**ROUND**
**ROUND**
38
# **SQL Functions**
* The following are some of the most used `_________ _________`:
***Example: 2*** SELECT `_________`(234.459, 2);
*Result:* 234.460
* The following are some of the most used numeric functions:
○ **ROUND**
**ROUND**
39
# **SQL Functions**
* The following are some of the most used `_________ _________`:
— returns the smallest integer value that is greater than or equal to a number
* The following are some of the most used numeric functions:
○ **CEILING**
40
# **SQL Functions**
* The following are some of the most used `_________ _________`:
*Example:* SELECT `_________`(234.1);
*Result:* 235
* The following are some of the most used numeric functions:
○ **CEILING**
41
# **SQL Functions**
* The following are some of the most used `_________ _________`:
— returns the largest value that is less than or equal to a number
* The following are some of the most used numeric functions:
○ **FLOOR**
42
# **SQL Functions**
* The following are some of the most used `_________ _________`:
*Example:* SELECT `_________`(234.5);
*Result:* 234
* The following are some of the most used numeric functions:
○ **FLOOR**
43
# **SQL Functions**
* The most commonly used string functions are as follows:
○ **CONCAT**
○ **LOWER**
○ **UPPER**
○ **SUBSTRING**
○ **LEN**
○ **TRIM**
44
# **SQL Functions**
```
CustomerID | LastName | FirstName | ContactNum
3446 | Velasco | Marco | 09167614778
```
* * The most commonly used `_________ _________` are as follows:
*Syntax:* **CONCAT***(string1, string2, ...);*
*Example:* SELECT `_________`(FirstName + ' ' , LastName) FROM Customers WHERE CustomerID = 3446;
*Result:* 234
string functions
○ **CONCAT**
45
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
— returns a string in lowercase letters
— returns a string in all capital letters
string functions
○ **LOWER**
○ **UPPER**
46
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
*Example:* SELECT `_________`('sql') + ' ' + `_________`('FUNCTIONS');
*Result:* SQL functions
string functions
○ **LOWER**
○ **UPPER**
UPPER
LOWER
47
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
— returns a part of a string
string functions
○ **SUBSTRING**
48
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
*Syntax:* `_________`*(string, start position, length);*
*Example:* SELECT `_________`('SQL Functions', 1, 3);
*Result:* SQL
string functions
○ **SUBSTRING**
**SUBSTRING**
SUBSTRING
49
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
— returns the number of characters in a string
○ **LEN**
50
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
*Example 1:* SELECT `_________`('SQL Functions');
*Result:* 13
○ **LEN**
LEN
51
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
*Example 2:* SELECT `_________`('SQL Functions ');
*Result:* 13
○ **LEN**
LEN
52
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
— removes the spaces or specific characters from start or end of a string
○ **TRIM**
53
# **SQL Functions**
* The most commonly used `_________ _________` are as follows:
*Syntax:* `_________`([*characters* FROM ]*string*);
*Example:* `SELECT `_________`(' ' FROM ' ');`
*Result:* 234
○ **TRIM**
**TRIM**
54
# **SQL Joins**
combines rows from two or more tables based on a common column.
* A **JOIN** clause
55
# **SQL Joins**
* The types of join are as follows:
○ **INNER JOIN**
Outer Joins
○ **LEFT JOIN**
○ **RIGHT JOIN**
○ **FULL JOIN**
56
# **SQL Joins**
* The types of join are as follows:
— removes rows that have matching values in both tables
○ **INNER JOIN**
57
# **SQL Joins**
* The types of join are as follows:
Outer Joins
○ **LEFT JOIN**
○ **RIGHT JOIN**
○ **FULL JOIN**
58
# **SQL Joins**
* The types of join are as follows:
— returns all rows from the left table and the matched records from the right table.
○ **LEFT JOIN**
59
# **SQL Joins**
* The types of join are as follows:
If there is no match from the right side, the result is NULL.
○ **LEFT JOIN**
60
# **SQL Joins**
* The types of join are as follows:
— returns all rows from the right table and the matched records from the left table.
○ **RIGHT JOIN**
61
# **SQL Joins**
* The types of join are as follows:
If there is no match from the left side, the result is NULL.
○ **RIGHT JOIN**
62
# **SQL Joins**
* The types of join are as follows:
— returns all records when there is a match on either the left or right table.
○ **FULL JOIN**
63
# **SQL Joins**
* The types of join are as follows:
If there is no match, the missing side will contain NULL.
○ **FULL JOIN**
64
# **SQL Joins**
**Table 1. Customers**
```
CustomerID | FirstName
3425 | Mark
3446 | Marco
3458 | Markus
3464 | Miguel
3472 | Martin
```
**Table 2. Orders**
```
OrderID | CustomerID
1 | 3446
2 | 3425
3 | 3472
4 | 3420
```
* * `_____ ____`
○ *Example:* SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID FROM Customers `_____ ____` Orders ON Customers.CustomerID = Orders.CustomerID;
○ *Result of query:*
```
CustomerID | FirstName | OrderID
3425 | Mark | 2
3446 | Marco | 1
3472 | Martin | 3
```
**Table 1. Customers**
```
CustomerID | FirstName
3425 | Mark
3446 | Marco
3458 | Markus
3464 | Miguel
3472 | Martin
```
**Table 2. Orders**
```
OrderID | CustomerID
1 | 3446
2 | 3425
3 | 3472
4 | 3420
```
* * **INNER JOIN**
○ *Example:* SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
○ *Result of query:*
65
# **SQL Joins**
**Table 1. Customers**
```
CustomerID | FirstName
3425 | Mark
3446 | Marco
3458 | Markus
3464 | Miguel
3472 | Martin
```
**Table 2. Orders**
```
OrderID | CustomerID
1 | 3446
2 | 3425
3 | 3472
4 | 3420
```
* * `_____ ____`
○ *Example:* SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID FROM Customers `_____ ____` Orders ON Customers.CustomerID = Orders.CustomerID;
○ *Result of query:*
```
CustomerID | FirstName | OrderID
3425 | Mark | 2
3446 | Marco | 1
3458 | Markus | NULL
3464 | Miguel | NULL
3472 | Martin | 3
```
**Table 1. Customers**
```
CustomerID | FirstName
3425 | Mark
3446 | Marco
3458 | Markus
3464 | Miguel
3472 | Martin
```
**Table 2. Orders**
```
OrderID | CustomerID
1 | 3446
2 | 3425
3 | 3472
4 | 3420
```
* * **LEFT JOIN**
○ *Example:* SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
○ *Result of query:*
66
# **SQL Joins**
**Table 1. Customers**
```
CustomerID | FirstName
3425 | Mark
3446 | Marco
3458 | Markus
3464 | Miguel
3472 | Martin
```
**Table 2. Orders**
```
OrderID | CustomerID
1 | 3446
2 | 3425
3 | 3472
4 | 3420
```
* * `_____ ____`
○ *Example:* SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID FROM Customers `_____ ____` Orders ON Customers.CustomerID = Orders.CustomerID;
○ *Result of query:*
```
CustomerID | FirstName | OrderID
3446 | Marco | 1
3458 | Mark | 2
3472 | Martin | 3
NULL | NULL | 4
```
**Table 1. Customers**
```
CustomerID | FirstName
3425 | Mark
3446 | Marco
3458 | Markus
3464 | Miguel
3472 | Martin
```
**Table 2. Orders**
```
OrderID | CustomerID
1 | 3446
2 | 3425
3 | 3472
4 | 3420
```
* * **RIGHT JOIN**
○ *Example:* SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
○ *Result of query:*
67
# **SQL Joins**
**Table 1. Customers**
```
CustomerID | FirstName
3425 | Mark
3446 | Marco
3458 | Markus
3464 | Miguel
3472 | Martin
```
**Table 2. Orders**
```
OrderID | CustomerID
1 | 3446
2 | 3425
3 | 3472
4 | 3420
```
* * `_____ ____`
○ *Example:* SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID FROM Customers `_____ ____` Orders ON Customers.CustomerID = Orders.CustomerID;
○ *Result of query:*
```
CustomerID | FirstName | OrderID
3458 | Mark | 2
3446 | Marco | 1
3458 | Markus | NULL
3464 | Miguel | NULL
3472 | Martin | 3
NULL | NULL | 4
```
**Table 1. Customers**
```
CustomerID | FirstName
3425 | Mark
3446 | Marco
3458 | Markus
3464 | Miguel
3472 | Martin
```
**Table 2. Orders**
```
OrderID | CustomerID
1 | 3446
2 | 3425
3 | 3472
4 | 3420
```
* * **FULL JOIN**
○ *Example:* SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID FROM Customers FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
○ *Result of query:*
