DS700 Quiz 10

UPDATE

Modifies an attribute's values in one or more table's rows in SQL Server *Caution: Focusing UPDATE with WHERE- Be cautious when issuing an UPDATE statement, as it is highly unlikely that every row in a table needs updating. Therefore, always consider including a WHERE clause with every UPDATE statement. If WHERE is not included, you could accidentally update every row, which could cost you your job.

Outer Joins

Retrieve all rows that satisfy join condition, plus unmatched rows in one or both tables. Left outer join: includes all rows from the first table (left table) plus matching rows from the second table (right table). Right outer join: includes all rows from the second table (right table) plus matching rows from the first table (left table). Full outer join: includes all rows from both tables

Left outer Join

Returns complete set of records (rows) from the left table (table A) and the matching records (that meet the condition) from the right table (Table B)

Right Outer Join

Returns complete set of records (rows) from the right table (Table B) and the matching records (that meet the condition) from the left table (Table A)

Example 5: Full Outer Join

SELECT Customers.LastName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID; *NOTE: FULL OUTER JOIN not supported in Access. Use UNION instead.

Example 6: UNION

SELECT Customers.LastName, Orders.OrderID FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID UNION SELECT Customers.LastName, Orders.OrderID FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID; *NOTE: Supported in Access

INSERT INTO

Adds one row or multiple rows to a table. You can insert data into all columns or specific columns.

CREATE

Basic Format database CREATE DATABASE dbname; table CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... );

Example 12: Create Table

CREATE TABLE Persons (table name) ( PersonID int, LastName varchar(255), FirstName, varchar(255), Address varchar(255), City varchar(255) );

UNION

Combines two SELECT statements into one result for application or end user. Two variations: UNION: removes any duplicate rows in result set and UNION ALL: includes duplicates

Example 4: Right outer join

SELECT Customers.LastName, Orders.OrderID FROM Customers RIGHT OUTER JOIN Orders (second table: returns all records) ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.LastName;

Example 10: if, else

IF(DATENAME(M, GETDATE()) = 'December') BEGIN SELECT 'Time for the Holidays!' Results END ELSE BEGIN SELECT 'Not sure what's going on now' Results END

Example 3: Left outer join

SELECT Customers.LastName, Orders.OrderID FROM Customers (first table: returns all records) LEFT OUTER JOIN Orders (second table: returns only matching records) ON Customers.CustomerID = Orders.CustomerID ORDER BY Customers.LastName;

Left outer join syntax

SELECT column_name(s) FROM table A LEFT JOIN table B ON join_condition;

Inner Joins Syntax

SELECT column_name(s) FROM table_1 INNER JOIN table_2 ON join_condition_1 INNER JOIN table_3 ON join_condition_2 *NOTE: Joining too many tables can slow database performance

Inner Joins

Simple, most common join clause type. Returns only rows that satisfy join condition. Joins that display data if there were a corresponding record in each table queried. Equality joins, non-equality joins, and self-joins are all classified as inner joins.

WHILE

Looping mechanism based on a Boolean expression. As long as the expression evaluates to true, the specified T-SQL statement executes Two optional keywords assist in controlling loop logic: Break: When causes query to exit, executes any SQL code following END Continue: Causes the loop to restart. Any statements after CONTINUE are ignored Ex. WHILE boolean_expression{sql_statement | statement_block | BREAK | CONTINUE}

Weapons of Math Destruction Chapter 3

Single national diet WMD (and higher education) - Scale Agronomy-inputs-soil,sunshine,fertilizer outputs-specific traits in the resulting crops. They create WMDs by neglecting to consider long-term and wide-ranging effects of pesticides and due to their models that are focused on clear outcomes, they are ideal for scientific experimentation 53-54 education WMD greatest shortcoming of US news college ranking is what they chose not to count: tuition and fees (student financing) The US news WMD fed on the beliefs of a degree from a highly ranked school can catapult a student into a life of power and privilege, fears, and neuroses. It created powerful incentives that have encouraged spending while turning a blind eye to skyrocketing tuitions and fees.

Conditional Statement: If, else

Tells the programming language which SQL statement or set of statements to perform if the specified condition is met or not IF can exist without ELSE, but ELSE cannot exist without IF. EX. IF boolean_expression{sql_statement | statement_block} [ELSE{sql_statement | statement_block}]

Cartesian Product

The combination of all rows in the first table and all rows in the second table Usually referred to as a cross join

self-join

The process of joining a table to itself often used to replace statements using subqueries that retrieve data from the same table as the outer statement. end result is usually the same, but DBMSs process joins far more quickly than they do subqueries.

Example 11: WHILE

This query uses a variable in the WHILE loop. During each execution of the loop, the variable is incremented by 1, and once it reaches 10, the loop should exit. However, since additional IF..ELSE logic causes routes the loop to a BREAK statement if the counter is not less than five, the loop iterates only four times. DECLARE @count int=0 WHILE (@count < 10) BEGIN SET @count = @count + 1; IF(@count < 5) BEGIN SELECT @count AS Counter CONTINUE; END ELSE BREAK; END

Conversion Functions

Two Categories: Cast- Converts data from one data type to another. Convert- Offers some flexibility over CAST. You can format output of your result set with the style argument. You can apply styles to date, time, real, float, money, xml, and binary data types.

Example 7: INSERT INTO

USE AdventureWorks2014; INSERT INTO HumanResources.Department(Name, GroupName, ModifiedDate) VALUES('Payroll','Executive General and Administration;.'/12/2012');

Example 9: CAST

USE AdventureWorks2014; SELECT TOP(10) SalesOrderNumber, TotalDue, CAST(TotalDue AS decimal(10,2)) AS TotalDueCast FROM Sales.SalesOrderHeader;

Example 8: UPDATE

USE AdventureWorks2014; UPDATE HumanResources.Department SET Name = Name + 'Europe' WHERE DepartmentID = 19 (Prevents UPDATE from changing all departments)

SQL Joins (DML)

Used to combine rows from two or more tables, based on a common field between them or join condition you specify *Note: Qualified Column Names- Joined columns from different tables may have the same column name. Therefore, include the table names with the column names to create qualified column names.

Example 2: Join and Sort

Using the Northwind database in Access, retrieve the last name of the customer with orderID and the date of the order placed SELECT Orders.OrderID, Orders.OrderDate, Customers.FirstName + ' ' + Customers.LastName AS CustomerName FROM Orders (first table) INNER JOIN Customers (second table) ON Orders.CustomerID=Customers.CustomerID ORDER BY Customers.FirstName+ ' ' +Customers.LastName;

Example 1: Basic Inner Join

Using the Northwind database in Access, retrieve the last name of the customer with orderID and the date of the order placed SELECT Orders.OrderID, Orders.OrderDate, Customers.LastName FROM Orders (first table) INNER JOIN Customers (second table) ON Orders.CustomerID=Customers.CustomerID;

Data Definition Language (DDL)

Vocabulary used to create, alter, or drop data structures in an instance of SQL Server CREATE: creates objects in database ALTER: alters database structure DROP: deletes objects from database TRUNCATE: removes all records from a table, including space allocated to them COMMENT: adds comments to data dictionary RENAME: renames an object

Equijoin

a join based on the testing of equality between two tables. Also called an inner join. But both may use slightly different syntax. Inner (select, from, on) equijoin (select, from, where)

unions

combined queries

Natural Join

eliminates those multiple occurrences so that only one of each column is returned. Its a join in which you select only columns that are unique, typically done using a wild card (SELECT *) for one table and explicit subsets of the columns for all other tables. no AS in Oracle

Weapons of Math Destruction Chapter 4

lead generation:online targeting..goal is to come up w/ lists of prospects which can be sold to for profit universities. 20-30% of promo budgets at for profit colleges go to lead generation

Full Outer Join

returns all rows from the left table (table1) and from the right table (table2).

outer join

the join includes table rows that have no associated rows in the related table. (left and right outer join)

UNION vs UNION ALL

union removes duplicates union all does not remove duplicates

Compound Queries

use boolean logic to combine multiple statements also called union

ALTER TABLE

used to add, delete, or modify columns in an existing table Add Column ALTER TABLE table_name ADD column_name datatype; Delete Column ALTER TABLE table_name DROP COLUMN column_name; Change data type ALTER TABLE table_name ALTER COLUMN column_name datatype;