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