Section 1
Preview this deck
<> or ! =
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 (168)
Section 1
(50 cards)
<> or ! =
SQL operator for not equal to
;
Indicates SQL Statement is Complete
COUNT
returns the count of values in a column
'%ITEM%'
Contains the following word, use with LIKE in WHERE clause
BETWEEN
selects all keyword within a range; Used in WHERE statement
ALTER TABLE celebs ADD COLUMN twitter_handle TEXT;
Add a new column 'twitter_handle' to the table in TEXT format
DESC
sort by descending (largest on top)
ALL
selects all data
ORDER BY
optional clause to sort
INSERT INTO celebs (id, name, age) VALUES (1, 'Micheal Fasbander', 38);
1 is an integer that will be inserted into the id column 'Micheal Fasbander' is text that will be inserted into the name column 38 is an integer that will be inserted into the age column
< =
SQL operator for less than or equal to
GROUP BY
Gathers Data
IN
operator that tests whether or not a value (stated before the keyword) is part of the list of values provided after the keyword; replaces compound operator need to use AND/OR
*
Selects all columns to return
HAVING
GROUP BY clause: Satisfies a condition in "quotes", used for COUNT or plural clauses
OR
Join two or more conditions in the WHERE clause, at least condition must be true
How to run a query to see the result?
SELECT * FROM celebs;d
SELECT
Select columns
' '
Used around text strings for conditions for easier code reading and machine understanding
>, <
SQL operator for greater or less than
FROM
Choose from a table
To view the row you just created, under the INSERT statement type
SELECT * FROM celebs;
-
used for subtraction
AND
Joins two or more conditions in the WHERE clause; both conditions must be true
SELECT name FROM celebs;
SELECT statements are used to fetch data from a database. Here, SELECT returns all data in the name column of the celebs table. 1. SELECT is a clause that indicates that the statement is a query. You will use SELECT every time you query data from a database. 2. name specifies the column to query data from. 3. FROM celebs specifies the name of the table to query data from. In this statement, data is queried from the celebs table.
Update the table to include Taylor Swift's twitter handle. In the code editor type:
UPDATE celebs SET twitter_handle = '@taylorswift13' WHERE id = 4; SELECT * FROM celebs;
___ perform specific tasks in SQL. By convention, __ are written in capital letters. ___ can also be referred to as commands. ex) CREATE TABLE
Clauses
DISTINCT
selects unique records, discards duplicate records
Add a row to the table
INSERT INTO celebs (id, name, age) VALUES (1, 'Justin Bieber', 21);
=
SQL operator for equal
WHERE
create a condition from a table
ASC
sort by ascending (smallest on top)
SUM
returns the sum of values in a column
MAX
returns largest value in a column
NOT BETWEEN
To display the keyword outside the range
> =
SQL operator for greater than or equal to
SELECT name FROM celebs;
only name column shows from celebs table
MIN
returns smallest value in a column
LIKE
SQL operator for string comparison test, used in where clause
COUNT(*)
returns the number of rows in a table
Semicolon ;
Goes at the end of a SQL statement so it will execute
AVG
returns the average value in column
+
used for addition
NOT IN
will exclude keywords from a list
CREATE TABLE celebs (id INTEGER, name TEXT, age INTEGER);
1. CREATE TABLE is a clause that tells SQL you want to create a new table. 2. celebs is the name of the table. 3. (id INTEGER, name TEXT, age INTEGER) is a list of parameters defining each column in the table and its data type.
parameter ex: (column_1 data_type, column_2 data_type, column_3 data_type)
is a list of columns, data types, or values that are passed to a clause as an argument
PRO%
used for what a command starts with, located in LIKE for WHERE clause
*
used for multiplication
()
Use these types of brackets around conditions to make code easier to read
LIMIT
Section 2
(50 cards)
NOT LIKE
used to show what is not contained in the word
value (1, 2, 3)
Used to add data with the INSERT INTO function
/
used for division
SIGN(x)
returns the sign of input x as -1, 0, or 1 (negative, zero, or positive respectively)
SELECT TOP 2/50 PERCENT
Selects the first 2, or first 50 percent of data from a table
set
Command used to make updates to a table
DELETE, where
statement to remove rows from a table
Ex. for INNER Join - FROM customers, items
Most common join "equijoin" or where two tables data are being pulled
INNER JOIN, ON
Returns all rows when there is at least one match in BOTH tables
Example Between Statements
WHERE ProductName BETWEEN 'C' AND 'M'; BETWEEN #07/04/1996# AND #07/09/1996#;
UPDATE
Statement used to update records
DROP table "tablename"
Delete a table to start from beginning
%
used for modulo Boolean
INSERT INTO
Add Columns or data to a table INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
_
wildcard used to substitute for any single character, multiple can be combined together
CREATE TABLE/Database "table name" (col1 constraint1, col2 constraint2, etc)
SQL Statement to Create a new table/database
FLOOR(x)
returns the largest integer value that is less than or equal to x
Case SenSiTive
SQL is NoT Case Sensitive
SQL Injection 2
uName = getRequestString("UserName"); uPass = getRequestString("UserPass"); sql = "SELECT * FROM Users WHERE Name ='" + uName + "' AND Pass ='" + uPass + "'"
number(size, d)
Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal.
LEFT JOIN
Return all rows from the left table, and the matched rows from the right table
purchases.customer_info
nomenclature used for naming tables when joins for multiple tables is present
CEILING(x) or CEIL(x)
returns the smallest integer value that is greater than or equal to x
Not Null
Constraint that specifics a column cannot be empty
Select column1 AS column
Aliases "AS" used to make columns more readable
FULL OUTER JOIN or FULL JOIN
Adds columns from one table to another by way of a join
30 characters
Maximum number of characters allowed for a SQL statement
SQL Injection
Add to webpage, like: txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId;
Batch SQL Code
Code statement; Code Statement2; Using semicolons allows multiple statements
ALTER [Table, Database]
modifies a table/database
FROM customer_info INNER JOIN purchases
Another option to write inner join
MOD(x,y)
modulo - returns the integer remainder of x divided by y (same as x%y)
WHERE city LIKE '[a-c]%';
lets you find a search with any character in a range
POWER(x,y)
returns the value of x raised to the power of y
UPDATE, set age= age+1 where firstname="Mary"
Clause used with UPDATE where you make an update to a column and then list a condition
ROUND(x)
returns the value of x rounded to the nearest whole integer
varchar(size)
Variable-length character string. Max size is specified in parenthesis.
CREATE/DROP INDEX index_name ON table_name (column_name)
Creates or Drops an Index as a search key
char(size)
Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
ROUND(x,d)
returns the value of x rounded to the number of decimal places specified by the value d
ABS(x)
returns the absolute value of x
Address+
Character allowed as a space for when combining phrases together
Avoid SQL Injection
txtNam = getRequestString("CustomerName"); txtAdd = getRequestString("Address"); txtCit = getRequestString("City"); txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)"; db.Execute(txtSQL,txtNam,txtAdd,txtCit);
date(size)
Date value
SQRT(x)
returns the square-root value of x
CONCAT()
Combines text together
WHERE city LIKE '[bsp]%'; or WHERE city NOT LIKE '[!bsp]%';
lets you find a search with any character beginning that is not the following
WHERE city LIKE '[bsp]%';
lets you select to begin with any of the following characters
Primary Key
A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
number(size)
Number value with a max number of column digits specified in parenthesis.
Section 3
(50 cards)
DATEDIFF()
Returns the number of days between two dates
TO_DATE ( String, [Format], [Optional Setting] )
Converts a Date to a string
UNION ALL
Combine both tables and show all duplicates
SELECT * INTO newtable FROM table1 WHERE 1=0;
Create a new empty table
DATE_FORMAT()
Displays date/time data in different formats
FIRST() or TOP 1 + ASC/DESC
function that returns the first value
CURDATE()
Returns to the current date
DUAL table
One row and one column
CREATE SEQUENCE [name] STARTS WITH [num] ... INCREMENT BY [num]
Creates a sequence with increments
|
Default headsep character in SQL plus
ROUND()
Rounds a numeric field to the number of decimals specified
CURTIME()
Returns the current time
DESCRIBE
provides context about the columns in a SQL table
ISNULL(), NVL(), IFNULL(), COALESCE()
function to specify how we want to treat NULL Values
Join Two Tables
select Salesperson.Name from Salesperson, Orders where Salesperson.ID = Orders.salesperson_id and cust_id = '4';
DATE()
Extracts the date part of a date or date/time expression
DATE_SUB()
Subtracts a specified time interval from a date
INITCAP
returns first character of each word upper case
MID()
Extract characters from a text field
Data types
http://www.w3schools.com/sql/sql_datatypes_general.asp http://www.w3schools.com/sql/sql_datatypes.asp
TRANSLATE
function that let's you change how the context is written
/ /
Used to comment multiple lines in SQL
AUTO_INCREMENT
Data type to auto increase the entries for a column in table
DATE_ADD()
Adds a specified time interval to a date
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) ALTER TABLE Persons ADD UNIQUE (P_Id)
Add constraint
REFERENCES
points to a different column
____ programming language designed to manipulate and manage data stored in relational databases.
SQL
EXTRACT()
Returns a single part of a date/time
ALTER TABLE Persons DROP CONSTRAINT/INDEX uc_PersonID
Drop constraint
FORMAT()
Formats how a field is to be displayed
Check
Ensures that the value in a column meets a specific condition
____ is a string of characters that the database recognizes as a valid command.
A statement
Insert Rows
insert into highAchiever (name, age) (select name, age from salesperson where salary > 100000);
UCASE()
function that Converts a field to upper case
column IS NULL; column IS NOT NULL;
query used to search for null values
Foreign key
Ensure the referential integrity of the data in one table to match values in another table
LCASE()
function that Converts a field to lower case
UNION
The UNION operator is used to combine the result-set of two or more SELECT statements.
TRUNCATE TABLE
delete data in table, not table
___ is a condition in SQL that returns true when the value is NULL and false otherwise.
IS NULL
Unique
Ensures that each row for a column must have a unique value
How dates work
select User.name, User.phone_num, max(UserHistory.date) from User, UserHistory where User.user_id = UserHistory.user_id and UserHistory.action = 'logged_on' and UserHistory.date >= date_sub(curdate(), interval 30 day) group by (User.user_id);
SELECT INTO
statement selects data from one table and inserts it into a new table. SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2013 FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID;
LAST()
function that returns the last value
LEN()
Returns the length of a text field
RIGHT JOIN
Return all rows from the right table, and the matched rows from the left table
Default 'Food'
Specifies a default value when specified none for this column
NOW()
Returns the current date and time
____ creates a new table.
CREATE TABLE creates a new table.
CREATE or REPLACE VIEW
Makes a view to see data
Section 4
(18 cards)