# Internet Fundamentals/Databases

This lesson introduces database concepts.

## Objectives and Skills

Objectives and skills for this lesson include:
* Recognize essential database concepts.

## Readings

* Wikipedia: Database
* Wikipedia: Relational database
* Wikipedia: SQL

## Multimedia

* YouTube: Relational Database Concepts
* YouTube: Intro to SQL
* YouTube: SQL JOIN Statement
* YouTube: SQL GROUP BY, HAVING & ORDER BY
* YouTube: SQL INSERT, UPDATE, and DELETE

## Activities

* Complete one or more of the following tutorials:
** W3Schools: SQL Tutorial
** SQLZOO: SQL Tutorial
** SQLBolt: Learn SQL
** TutorialsPoint: SQL
** Khan Academy: Intro to SQL
* Practice writing single-table SELECT statements using W3Schools SQL TryIt Editor.
** Select all customers (SELECT *).
** Select only customer id and customer name fields (SELECT fields).
** Select customer name, address, city, and postal code for all customers from the United Kingdom (WHERE field = 'value').
** Select contact name and customer name for all customers, sorted alphabetically by contact name (ORDER BY field).
** Count the total number of customers (COUNT(*)).
** Count the number of customers from each country (GROUP BY).
** Count the number of customers from each country and sort the list in descending order by count and ascending order by country in case of a tie (GROUP BY, ORDER BY).
** Count the number of customers from each country and sort the list in descending order by count and ascending order by country in case of a tie, listing only those countries with more than 10 customers (GROUP BY, HAVING, ORDER BY).
* Practice writing multiple-table SELECT statements using W3Schools SQL TryIt Editor.
** Select customer name, order ID, and order date for all customers (INNER JOIN).
** Select customer name, order number, and order date for all customers, renaming the OrderID field as Order Number (INNER JOIN, AS).
** Select customer name, order number, and order date for all customers, sorted by customer name and order number (INNER JOIN, AS, ORDER BY).
** Select order number, order date, product name, and quantity ordered for all customers (INNER JOIN, AS).
** Select order number, order date, product name, quantity ordered, and extended price (quantity * price) for all customers (INNER JOIN, AS, calculated field).
** Select order number, order date, product name, quantity ordered, and extended price for customer 2 (INNER JOIN, AS, calculated field, WHERE).
** Select order number, order date, product name, quantity ordered, and extended price for customer 'Around the Horn' (INNER JOIN, AS, calculated field, WHERE).
* Practice writing INSERT, UPDATE, and DELETE statements using W3Schools SQL TryIt Editor.
** Add a new shipper with ID 4, name 'On Time Delivery', and phone '(503) 555 0123' (INSERT).
** Increase prices on all products by 1 (UPDATE).
** Reduce prices on all products by 1 (UPDATE).
** Change the new shipper's name from 'On Time Delivery' to 'On-Time Delivery' (UPDATE, WHERE).
** Delete the new shipper (DELETE, WHERE).

## Lesson Summary

* A database is an organized collection of data.
* A relational database is a collection of schemas, tables, queries, reports, views, and other elements.
* A database-management system (DBMS) is a computer-software application that interacts with end-users, other applications, and the database itself to capture and analyze data.
* A general-purpose DBMS allows the definition, creation, querying, update, and administration of databases.
* Well-known DBMSs include MySQL, PostgreSQL, EnterpriseDB, MongoDB, MariaDB, Microsoft SQL Server, Oracle Database, Sybase, SAP HANA, MemSQL, SQLite and IBM DB2.
* Virtually all relational database systems use SQL (Structured Query Language) as the language for querying and maintaining the database.
* SQL (Structured Query Language) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).
* Data Manipulation Language (DML) commands include SELECT, INSERT, UPDATE, and DELETE.
* The SQL SELECT statement returns a result set of records from one or more tables.
* An asterisk ("*") can be used to specify that the query should return all columns of the queried tables.
* SQL statements are terminated with a semicolon (";").
* A WHERE clause in SQL specifies that a SQL Data Manipulation Language (DML) statement should only affect rows that meet specified criteria.
* By default, relational database systems may return data rows in any order, or more specifically, without any order.
* An ORDER BY clause in SQL specifies that a SQL SELECT statement returns a result set with the rows being sorted by the values of one or more columns.
* A SQL JOIN clause combines columns from one or more tables in a relational database.
* An SQL INSERT statement adds one or more records to any single table in a relational database.
* An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.
* An SQL DELETE statement removes one or more records from a table. Either all records are removed, or a subset may be chosen using a condition.

## Key Terms
### AJAX (Asynchronous JavaScript And XML)

    A set of Web development techniques using multiple Web technologies on the client side to create asynchronous Web applications that can send and retrieve data from a server asynchronously (in the background) without interfering with the display and behavior of the existing page.

### data modeling

    A process used to define and analyze data requirements needed to support the business processes within the scope of corresponding information systems in organizations.

### database

    An organized collection of data.

### database management system (DBMS)

    A computer-software application that interacts with end-users, other applications, and the database itself to capture and analyze data.

### field

    A set of data values of a particular simple type, one for each row of the table.

### foreign key

    A field (or collection of fields) in one table that uniquely identifies a row of another table or the same table.

### index

    A data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.

### many-to-many relationship

    A type of cardinality that refers to the relationship between two entities A and B in which A may contain a parent instance for which there are many children in B and vice versa.

### one-to-many relationship

    A type of cardinality that refers to the relationship between two entities (see also entity–relationship model) A and B in which an element of A may be linked to many elements of B, but a member of B is linked to only one element of A.

### Open Database Connectivity (ODBC)

    A standard application programming interface (API) for accessing database management systems (DBMS).

### primary key

    A field (or collection of fields) in one table that uniquely identifies a row in the table.

### query

    A precise request for information retrieval with database and information systems.

### record

    A single, implicitly structured data item in a table.

### relational database

    A collection of schemas, tables, queries, reports, views, and other elements.

### Structured Query Language (SQL)

    A domain-specific language used in programming and designed for managing data held in a relational database management system

### table

    A collection of related data held in a structured format within a database, consisting of columns and rows.

## Assessments

* Flashcards: Quizlet: Information Systems - Databases
* Quiz: Quizlet: Information Systems - Databases

## See Also

* Databases
* Training.gov.au: Operate database applications