Monday Final

• SQL


  Domain-specific language used in programming and designed for relational databases
• SQL


  • Based upon EF Codd's Relational Model
  • One of the first commercial languages
  • Created in 1974
  • Became international standard in 1987
• SQL Layers


  • DDL
  • DML
• DDL


  1. Statements used to define the database structure or schema

  2. CREATE - to create objects in the database

  3. ALTER - alters the structure of the database

  4. DROP - delete objects from the database

  5. TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

  6. COMMENT - add comments to the data dictionary

  7. RENAME - rename an object
• DML


  1. Statements used for managing data within schema objects

  2. SELECT - retrieve data from the database

  3. INSERT - insert data into a table

  4. UPDATE - updates existing data within a table

  5. DELETE - deletes all records from a table, the space for the records remain

  6. MERGE - UPSERT operation (insert or update)

  7. CALL - call a PL/SQL or Java subprogram

  8. EXPLAIN PLAN - explain the access path to the data

  9. LOCK TABLE - control concurrency
• SQL editor/client


  • Graphical User Interface allows you to view the data and create queries
  • Relationship Charts and Graphs
  • Allows you to view catalogs, schemas, tables, triggers, views, sequences, procedures
• SQL editor/client applications


  • Dbeaver
  • SQuirreL
  • SQL Server Native Client
  • SQL Navigator
  • Sqlectron
• Select


  • Allows you to pull specific columns
  • Allows you to use * in order to pull back all columns
• From


  • Used to identify what tables data is pulled from
  • Used to Identify Table Joins for relationships
  • Separate Multiple Tables with commas
• Where


  • Allows you to filter the results and apply conditions in a SELECT, INSERT, UPDATE, or DELETE statement
  • Allows you to use logical expressions, also known as a predicate
  • Not Mandatory
  • As complex or simple as needed
• Distinct


  • Allows you to remove duplicates in a select statement
  • When only one expression is provided in the DISTINCT clause, the query will return the unique values for that expression
  • When more than one expression is provided in the DISTINCT clause, the query will retrieve unique combinations for the expressions listed
• Comparison Operators


  • An operator is a reserved word or expression
  • Used in WHERE clause in SQL
  • Perform operation(s), such as comparisons and arithmetic operations
  • Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement
• Logical Operators


  • Used to perform some conditional check and comparison check
  • Return a Boolean Data Type (True, False, Unknown)
  • Can combine multiple operands if desired
• Logical Operators


  • LIKE - allows you to match similar values, instead of exact values
  • IN - allows you to specify a list of values you'd like to include
  • BETWEEN - allows you to select only rows within a certain range
  • IS NULL - allows you to select rows that contain no data in a given column
  • AND - allows you to select only rows that satisfy two conditions
  • OR - allows you to select rows that satisfy either of two conditions
  • NOT - allows you to select rows that do not match a certain condition
• Order by


  • Used to order the result set by the specific column list
  • Considered Optional
  • ASC- Ascending-Default
  • DESC- Descending
• Order by


  • SELECT employee_id, dept, name, age, salary FROM employee_info WHERE dept = 'Sales' ORDER BY salary, age DESC
• Group by


  • Divides the query result into groups of rows
  • Typically for purpose of performing one or more aggregations on each group
  • Considered Optional
• Group by


  • SELECT AVG(TotalAmount), FirstName, LastName FROM [Order] O JOIN Customer C ON O.CustomerId =C.id GROUP BY FirstName, LastName HAVING AVG(TotalAmount) BETWEEN 1000 AND 1200
• SQL Functions


  • SUM-Total
  • COUNT-Number of Occurrences
  • AVG- Average
  • MIN- Minimum
  • MAX- Maximum
• SQL Functions


  • SELECT SUM(AMOUNT) FROM Sales
  • SELECT COUNT(Customer) AS CountofCustomer, AVG(Amount) AS AvgOfAmount FROM Sales
• Joins


  Combine rows from two or more tables, based on a related column between them
• Types of Joins


  • Inner Join
  • Left Join
  • Right Join
  • Full Join
• Joins


  • SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID
• Referential Integrity


  • Relational database concept, which states that table relationships must always be consistent
  • The foreign key field must agree with the primary key that is referenced by the foreign key
  • Any primary key field changes must be applied to all foreign keys, or not at all
• Machine learning


  • The area of computer science that aims to build systems and algorithms that learn from data
• Provenance


  Where the data originated and how trustworthy it is for large-scale processing and analysis
• Business intelligence


  The processes and technologies used to obtain timely, valuable insights into business and clinical data
• Big Data


  • Large and ever-increasing volumes of data that adhere to the following attributes: Volume, Velocity, Variety, Veracity
• Clinical Data Warehouse


  Stores and manages large volumes of healthcare data for analytics and reporting
• Accountable Care Organizations (ACOs)


  Provide incentives to deliver high-quality care in cost-efficient ways that will require a robust IT architecture, health information exchange (HIE) plus analytics
• Challenges to Data Analytics


  • Incomplete/Inaccurate Information
  • Censored Information
  • Vague information
  • Non Standardized Information
• Clinical data mostly allows observational and not experimental studies, thus raising issues of cause-and-effect of findings discovered
• Research questions asked of the data tend to be driven by what can be answered, as opposed to prospective hypotheses
• Data are not always as objective as one might like, and "bigger" is not necessarily better
• Descriptive Analytics


  Standard types of reporting that describe current situations and problems
• Predictive Analytics


  Simulation and modeling techniques that identify trends and outcomes of actions taken
• Prescriptive Analytics


  Optimizing clinical, financial, and other outcomes
• Health Information Exchange (HIE)


  The electronic movement of health-related information among organizations according to nationally recognized standards
• Health Information Organization (HIO)


  An organization that oversees and governs the exchange of health-related information among organizations according to nationally recognized standards
• Regional Health Information Organization (RHIO)


  A health information organization that brings together health care stakeholders within a defined geographic area and governs health information exchange among them to improve health and care in that community