Section 1
Preview this deck
How to tell number of characters placed to the left of the decimal?
Front
| 5 | 0 | ||
| 4 | 0 | ||
| 3 | 0 | ||
| 2 | 0 | ||
| 1 | 0 |
Active users
0
All-time users
0
Favorites
0
Last updated
6 years ago
Date created
Mar 1, 2020
Cards (68)
Section 1
(50 cards)
How to tell number of characters placed to the left of the decimal?
use p-s formula (9,2) is 7 characters before decimal
DATA TYPE alternate that only stores actual number of characters inserted into table
VARCHAR(n)
When do you need to use the SELECT FROM WHERE clause?
When you have put restrictions on what the select results will display
DATA TYPE that Store characters and special chars using all space in column
CHAR(n)
Is the BETWEEN inclusive or not?
The BETWEEN operator is inclusive so the query will select value equal to either value in the query and in the range.
DELETE TABLE
DROP TABLE [TABLE_NAME];
How do you find a result that is between two values?
use the SELECT FROM WHERE with AND SELECT [COLUMN_NAME] FROM [TABLE _NAME] WHERE [COLUMN_NAME] = [COMPARISON OPERATOR] AND [COMPARISON OPERATOR]; SELECT CUSTOMER_NUM, BALANCE FROM CUSTOMER WHERE BALANCE >= 2000 AND BALANCE <=5200; OR USE THE BETWEEN OPERATOR with AND SELECT [COLUMN_NAME] FROM [TABLE _NAME] WHERE [COLUMN_NAME] BETWEEN [WHAT] and [WHAT]; SELECT CUSTOMER_NUM, BALANE FROM CUSTOMER WHERE BALANCE BETWEEN 2000 AND 5000
How to find a column one or more specific values
Use the IN clause SELECT COLUMN_NAME FROM [TABLE] WHERE [COLUMN_NAME] IN (value, value, etc,); SELECT CUSTOMER_NUM, CUSTOMER_NAME, CREDIT_LIMIT FROM CUSTOMER WHERE CREDIT_LIMIT IN (5000, 10000, 15000)
How do you find a result when you need no stated result to be found (everything except)?
Use the WHERE NOT condition SELECT [COLUMN_NAME] FROM [TABLE _NAME] WHERE NOT [COLUMN_NAME] = [?]
What command do you use to insert values into a table row?
INSERT INTO [TABLE_NAME] VALUES (place the values here in the order of the columns) Note that CHAR column entries must be enclosed in single quote marks All entries should be entered as you want to search and find them later and appear because the entries inside the tables are case sensitive
Which data type uses integers and no decimals but also uses less space than INT DATA TYPE?
SMALL INT
DATA TYPE for decimal type numbers
DECIMAL [p,s]
Find the number, name and available credit for each customer with at least $5,000 of available credit
SELECT CUSTOMER_NUM CUSTOMER_NAME, CREDIT LIMIT - BALANCE AS AVAILABLE_CREDIT FROM CUSTOMER WHERE CREDIT_LIMIT - BALANCE => 5000;
How do you find a result when you need either or to be found
SELECT [COLUMN_NAME] FROM [TABLE_NAME] WHERE [COLUMN_NAME] [COMPARISON OPERATOR] [?] OR [COLUMN_NAME] [COMPARISON OPERATOR] [?] SELECT DESCRIPTION FROM ITEM WHERE STOREHOUSE = '3' OR ON_HAND > 25;
How do you form a Compound Condition?
You form a compound condition by connecting two or more simple conditions with AND, OR, and NOT operators
Display a table structure
DESCRIBE [TABLE_NAME];
How do you specify a dollar value if SQL
In SQL, numbers included in queries are entered without extra symbols such as dollar signs and commas.
What is the equal to comparison operator?
=
TABLE NAMING RULES
1. NAME CANNOT BE MORE THAN 30 CHARS 2. MUST START WITH A LETTER 3. CAN CONTAIN LETTERS, NUMBERS AND UNDERSCORES 4. CANNOT CONTAIN SPACES
DATA TYPE for dates
DATE [Uses format DD-MM-YYYY]
What is the NOT EQUAL TO comparison operator?
<>
What is the LESS THAN comparison operator?
<
When do you enclose a value you are searching for in quotation marks?
When the text you are searching for is inside a CHAR COLUMN
What is the GREATER THAN or EQUAL TO comparison operator?
>=
What is the LESS THAN or EQUAL TO comparison operator?
<=
What wild card symbol can be used to represent any individual character
the underscore (_) symbol Example SELECT CUSTOMER_NUM FROM CUSTOMER WHERE CUSTOMER_NUM LIKE (7_1);
SELECT EVERY COLUMN AND ROW IN TABL
SELECT * FROM [TABLE_NAME];
How do you delete a specific row from a table?
DELETE FROM [TABLE_NAME] WHERE [PRIMARY KEY COLUMN NAME] = [?];
Which DATA TYPE will produce numbers in sequence automatically if used with AUTO_INCREMENT?
INT AUTO_INCREMENT (Useful for primary key columns)
SELECT CLAUSE
SELECT CLAUSE FOLLOWED BY THE LIST OF COLUMNS, SEPARATED BY COMMAS. THEN FROM AND TABLE NAME
Find the name of the customers located in the city of Grove from the CUSTOMER table?
SELECT CUSTOMER_NAME FROM CUSTOMER WHERE CITY = 'Grove';
How do you subtract two columns in the result
Use the - operator SELECT CUSTOMER_NUM, CUSTOMER_NAME, [CREDIT_LIMIT - BALANCE] FROM CUSTOMER
How do you update a specific cell in a table? / What is the format for using the UPDATE command?
1. Find the primary key entry value for the row to be changed. UPDATE (TABLE_NAME) SET (COLUMN_NAME) = <NEW VALUE> IF NECESSARY, where necessary include the where clause to indicate in which the change is to take place WHERE (PRIMARY_KEY_COLUMN) = <value.
How do you divide two columns in the result
Use the / operator SELECT CUSTOMER_NUM, ITEM, [EXTENDED_PRICE / QTY] AS NET_PRICE FROM CUSTOMER
WHAT operator will work together with WITH to find items similar or matching a pattern?
SELECT FROM WHERE [COLUMN_NAME] LIKE '%?%';
DISTINCT OPERATOR
Prevents duplicates This will prevent the results from showing multiple rows in a table for the same value requested SELECT DISTINCT(COLUMN_NAME) FROM [TABLE_NAME];
How do you add two columns in the result
Use the + operator SELECT CUSTOMER_NUM, CUSTOMER_NAME, [CREDIT_LIMIT + BALANCE] FROM CUSTOMER
Find the name, balance and credit limit for all customers with balances greater than their credit limit CREDIT_LIMIT, BALANCE, CUSTOMER_NAME
SELECT CUSTOMER_NAME, BALANCE, CREDIT_LIMIT FROM CUSTOMER WHERE BALANCE > CREDIT_LIMIT;
CREATING A TABLE COMMAND
CREATE TABLE [TABLE_NAME] (COLUMN_NAME [DATATYPE](WIDTH) PRIMARY KEY, ETC.... );
DATA TYPE that is numeric but has no decimals
INT
How do you add data into a table into specific desired columns only?
Use INSERT INTO [TABLE_NAME] ([COLUMN_NAME], [COLUMN, NAME], [etc.]) VALUES (name the values that you want inserted into those columns in the order specified)
When you use the AND compound what must be true?
All the simple conditions must be true WHERE [COLUMN_NAME] = '3' AND [COLUMN_NAME] > 25;
When you need to retrieve rows to satisfy a restriction condition, how do you?
SELECT [COLUMN NAMES] [etc.] FROM [TABLE_NAME]; WHERE [COLUMN_NAME] = [?]?
What is the GREATER THAN comparison operator?
>
How to select all items from specific columns
SELECT [COLUMN NAMES] [etc.] FROM [TABLE_NAME];
HOW TO SPECIFY IF TABLE COLUMN CAN'T BE NULL
TYPE (WITHOUT QUOTES) "NOT NULL" BEFORE COMMA
How do you multiply two columns in the result
Use the * operator SELECT CUSTOMER_NUM, CUSTOMER_NAME, [CREDIT_LIMIT * BALANCE] AS NET_VALUE FROM CUSTOMER
SELECT A COMPUTED COLUMN [CALCULATE TWO COLUMNS]
SELECT COLUMN NAMES (COLUMN [+ - * OR /] COLUMN) AS [COLUMN_NAME] FROM [TABLE_NAME];
How do you assign a name to a computed column
Specify the computation then add AS [COLUMN_TITLE]
How do you indicate by default that a table can accept NULL values when creating a table?
Don't type NOT NULL before the comma
Section 2
(18 cards)