Section 1
Preview this deck
Exception1
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 (83)
Section 1
(50 cards)
Exception1
You cannot, except in certain cases, combine a built-in function with another column name in the SELECT statement
SUM
We can obtain this from a particular column by performing a query like this: Ex: SELECT SUM(OrderTotal) FROM RETAIL_ORDER;
How the GROUP BY Clause Works
The DBMS sorts rows according to the condition set forth in the GROUP BY and then counts the number of rows with the same value in that condition
Exception2
You cannot use SQL built-in functions in the WHERE clause
WHERE Clause
In order to use this, we would simply add a column to limit on and then the row value that we want Ex: SELECT * FROM Employee WHERE EmployeeLastName = 'Smith';
ALTER TABLE
- We can change an existing table to add, delete or change columns and/or constraints - Additionally, we can also add or modify constraints - ADD CONSTRAINT and DROP CONSTRAINT
Column Order
The order in which you include your columns in your query will also dictate the order of the columns in your result set
JOINS
- Pulling information from multiple tables in the database
Wildcards
There may be cases where we want to return records that are only dependent on a portion of the data being searched In order to write this, we would use a % character in the query. We also need to change the column = to column LIKE Ex: SELECT * FROM SKU_DATA WHERE Buyer LIKE 'Pete%';
INSERT Statement
- This statement is used to add rows of data to table
IN / NOT IN
We can use these to shorten the query length and still achieve the same result Ex: SELECT EmployeeID FROM EMPLOYEE WHERE EmployeeLastName IN ('Smith','Phillips'); You can also exclude these results by adding the keyword NOT in front of IN
Specific Queries
We can also narrow columns and rows in our query by simply using the following structure: Ex: SELECT SKU_Description, Department FROM SKU_DATA WHERE Department = 'Climbing';
Concatenation
We can use the + symbol to do this to the results in the SELECT statement to be provided as a single result in the result set Ex: SELECT Buyer +'in' + Department AS Sponsor;
CREATE TABLE
- The SQL command used to create a database table
Built-In SQL Functions
1) SUM 2) AVG 3) MIN 4) MAX 5) COUNT
OUTER JOIN
- This type of join returns all rows from both tables, and includes matches in the same row
ON UPDATE / ON ACTION
- If we have a foreign key reference in a table, we may want to prevent changes from being made if the primary key in the parent table is changed... - We can do this by using ON UPDATE NO ACTION, ON DELETE NO ACTION - If for some reason we want the change to propagate through both tables, we would use ON UPDATE CASCADE, ON DELETE CASCADE
INNER JOIN
- Allows us to pull rows from Table A where Table B contains matching rows
DROP TABLE
- This SQL statement will delete the table from the database and the data continued within the table - Ex: DROP TABLE ORDERS; - If you have any foreign key constraints that references the table, you will not be able to execute the DROP. You will need to DROP the table with the constraint and then the parent table.
CROSS JOIN
- This join is the equivalent of the Cartesian product between two tables, Table A and Table B
Limiting Rows
- Suppose that we want to retrieve all columns from a particular table, but we do not want all of the rows contained - We can use another portion of the basic framework known as the WHERE clause Ex: SELECT * FROM Employee WHERE...
SQL/Persistent Stored Modules (SQL/PSM)
These statements extend SQL by adding the ability to do procedural programming, including the use of variables and flow-of-control statements
Wildcard (*)
In order to select all of the columns within a particular table, we can use this special character in place of listing each column
Calculations In SELECT
We can perform these in the SELECT statement, such as multiplying Quantity and Price together to get ExtendedPrice Ex: SELECT Quantity * Price AS EP FROM ORDER_ITEM;
Data Control Language (CL)
Statements that grant or revoke permissions to users and groups so that those users and groups can perform operations on the database
Ranges
BY using the BETWEEN keyword we can accomplish this The result set will include records that match the lower and upper boundaries, as well as those that are in between Ex: SELECT * FROM ORDER_ITEM WHERE ExtendedPrice BETWEEN 100 AND 200;
TRUNCATE TABLE statement
_ This SQL statement allows you to clear all data out of a table without destroying the table structure - You cannot specify a WHERE clause in this statement - You cannot use this where there is a foreign key constraint because it would break the link between the two tables
DISTINCT Keyword
In cases where you want a single, unique value, use this keyword in your query
GROUP BY Clause
We can use this clause to sort and consolidate the data within a particular table
COUNT Function
This will count the number of rows provided in the query result set
Compound Clauses
1) AND Operator 2) OR Operator 3) IN / NOT IN
Data Extracts
- In order to extract data from a database, we can use SQL, or Structured Query Language
UPDATE Statement
- This statement is used when we need to perform data updates within a table - Caution: You need to make sure that the update is focused in on a particular data set and not multiple rows unless that is your intention
Data Manipulation Language (DML)
Statements used for querying, inserting, modifying and deleting data
RIGHT JOIN
- This join returns all rows from Table B and also matching rows from Table A
FROM
Specifies the tables to be used in the query
SELECT
Selects the columns to be listed in the results of the query
Transaction Control Language (TCL)
Statements that control transaction behavior
LEFT JOIN
- This join returns all rows from Table A (left side), and also returns matching rows from Table B
AND Operator
This gives us the ability to add two or more restrictions on the result set Ex: SELECT * FROM SKU_DATA WHERE Department = 'Water Sports' AND Buyer = 'Nancy Meyers';
SQL Statement Categories
1) DDL 2) DML 3) SQL/PSM 4) TCL 5) DCL
Database Schema
A complete logical view of the database
Compound WHERE Clauses
1) Compound Clauses 2) Ranges 3) Wildcards
Data Definition Language (DDL)
Statements used for creating tables, relationships and other structures
SQL Comments
In order to add this in SQL, simply contain within /* and */
WHERE
Specifies the rows that should be included in the results of the query
AS Keyword
We can use this to provide a name for the result column in our table
ORDER BY
- The ORDER BY clause defaults the results set in ascending order - In order to enforce the particular method of sorting after specifying the column, use the keywords ASC or DESC - Ex: SELECT OrderID, OrderAmount FROM ORDERS ORDER BY OrderID DESC
OR Operator
There may be cases where we want to be less restrictive on the result set Ex: SELECT * FROM SKU_DATA WHERE Department = 'Camping' OR Department = 'Climbing';
SELECT / FROM / WHERE Framework
The most basic form of SQL queries
Section 2
(33 cards)