Section 1
Preview this deck
FOREIGN KEY constraint
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 (59)
Section 1
(50 cards)
FOREIGN KEY constraint
Ensures the foreign key values only reference values in the primary key, ensuring referential integrity.
COUNT and AVG and SUM
COUNT counts the number of rows selected. Key to remember it doesn't return number of columns. SELECT COUNT(column_name) FROM table_name; AVG returns the numeric average of column(s). SELECT AVG(column_name) FROM table_name; SUM returns total sum of column(s). SELECT SUM(column_name) FROM table_name;
DELETE
DELETE FROM table WHERE condition;
LIKE
SELECT column_name FROM table_name LIKE %string%; SELECT column_name FROM table_name LIKE 'str_ng';
GROUP BY
Groups the selected rows based on one or more attributes, as opposed to ORDER BY which changes the order of rows presented SELECT first_name FROM employees GROUP BY first_name HAVING Count(first_name) >1
SELECT DISTINCT
SELECT DISTINCT column_name FROM table_name;
Default ports to secure SQL server
1433 and 1434
First normal form
Eliminate repeating groups Requirements: -cells may not have more than one piece of data -each record must be unique --> both of these are solved by primary key
Referential integrity
Ensures that all relationships within a database are maintained throughout updates—enforced through the foreign key constraint
IN
Allows you to select multiple statements in a WHERE query — shorthand for multiple OR statements SELECT column_name FROM table_name WHERE column_name IN (value1, value2); also works with NOT IN
Second normal form
Eliminate redundant data Requirements: -record should only depend on primary key --> separate tables if not
DDL clauses
USE, CREATE, ALTER, DROP, DELETE TRUNCATE
Adding duplicate IDs to a db
Permitted as long as the ID is not specified PRIMARY KEY
DML clauses
SELECT, UPDATE, INSERT, DELETE, MERGE
Indexes, foreign keys, and stored procedures _________ when added
Improve performance
BETWEEN
SELECT column_name FROM table_name WHERE column_name BETWEEN value1 AND value2;
MIN and MAX
SELECT MAX(column_name) FROM table WHERE condition; SELECT MIN(column_name) FROM table WHERE condition;
INSERT INTO
INSERT INTO table_name(column1, column2) VALUES (value1, value2);
GROUP BY
A SQL clause that formats results when combined with any of the aggregate functions in a SELECT statement. Aggregate functions will not work without GROUP BY.
Create a primary key
CREATE TABLE name (column 1 INT PRIMARY KEY)
AND
SELECT column_name FROM table_name WHERE condition1 AND condition 2;
Cascading deletes remove data but not ________
database objects
PRIMARY KEY constraint
Ensures all values in columns are unique and not null—a combination of the UNIQUE and NOT NULL constraints Each table can only have one primary key constraint
DEFAULT constraint
Sets a default value for columns where no value is specified
Trigger
A trigger is a stored procedure that automatically fires when an action is taken.
Add columns into table for employee info
INSERT INTO employee (id, first_name, last_name)
NOT NULL constraint
Ensures that no values in columns are NULL
Transaction syntax
BEGIN TRANSACTION ColumnClause DELETE from condition WHERE condition COMMIT TRANSACTION ColumnClause; BEGIN TRANSACTION VolunteerDelete DELETE from Volunteer WHERE id = 13 COMMIT TRANSACTION VolunteerDelete;
WHERE
SELECT column_name FROM table_name WHERE condition;
OR
SELECT column_name FROM table_name WHERE condition1 OR condition2;
Add values into table columns
VALUES (3424, hector, smith)
NULL
SELECT column_name FROM table WHERE column_name IS NULL; SELECT column_name FROM table WHERE column_name IS NOT NULL;
Subquery
A query that is inside another select or action query.
UPDATE
UPDATE table_name SET column1 =value1, column2 =value2 WHERE condition; The WHERE is especially important after SET before it indicates which cell is replaced . UPDATE employee SET last_name = 'jones' WHERE employee__id = 1314
NOT
SELECT column_name FROM table_name WHERE NOT condition1;
UNIQUE constraint
Ensures all values in columns are unique
CHECK constraint
Limits the range of values that can go in a column or table If a CHECK is defined for a table, it can impact values in columns based on existing columns
Create a composite primary key
CREATE TABLE name (column1 INT column2 varchar(20) column 3 INT) PRIMARY KEY (column 1, column3)
Add columns from a previous table into the new table
The INSERT INTO clause must still be first, same in standard syntax. INSERT INTO employee SELECT * FROM old_employee
Function
aggregate operations return a single value, calculated from values in a column. Useful aggregate functions: AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum
Functionally dependent
The relationship between column values and their primary key value
operators used with WHERE
>, <, =, <=, >=, <>, _ BETWEEN, LIKE, IN
Two keys that establish relationship between tables
primary + foreign
SELECT
SELECT column_name(s) FROM table_name;
Third normal form
Eliminate columns that are not dependent on only the primary key Requirements: -eliminate transitive dependencies
Create a view
CREATE VIEW vwPlanets AS SELECT planets, diameter
Create a table that saves employee info
CREATE employee (id INT, first_name varchar(20), last_name varchar (20))
Index
object that speeds select queries from a db
COUNT DISTINCT
SELECT COUNT (DISTINCT column_name) FROM table_name;
ORDER BY
Alters the order that column results appear SELECT column_name FROM table_name ORDER BY column_name; [ASC or DESC to sort]
Section 2
(9 cards)