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