IT Fundamentals/Database Use

This lesson introduces database use.

Objectives and Skills

Objectives and skills for the database use portion of IT Fundamentals certification include:

• Summarize methods used to interface with databases.
  • Relational methods
    • Data manipulation
      • Select
      • Insert
      • Delete
      • Update
    • Data definition
      • Create
      • Alter
      • Drop
      • Permissions
  • Database access methods
    • Direct/manual access
    • Programmatic access
    • User interface/utility access
    • Query/report builders
  • Export/import
    • Database dump
    • Backup

Readings

1. Wikipedia: SQL

Multimedia

1. YouTube: Interfacing with Databases
2. YouTube: Querying a Database
3. YouTube: Table Design

Activities

1. Complete one or more of the following tutorials:
   • W3Schools: SQL Tutorial
   • SQLZOO: SQL Tutorial
   • SQLBolt: Learn SQL
   • TutorialsPoint: SQL
   • Khan Academy: Intro to SQL
2. 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).
3. 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).
4. 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).
5. Research SQL statements.
   • Research CREATE, ALTER, and DROP. Note how they differ from INSERT, UPDATE, and DELETE.
   • Research GRANT and REVOKE. Note how they may be applied broadly to tables and queries (views) or specifically to individual fields in a table or query (view).

Lesson Summary

Relational Databases

Database use

MySQL

MariaDB

MongoDB

Oracle Database

PostgreSQL

SQL

Comma-separated values

• A relational database is a digital database where all data is represented in terms of tuples (rows or records), grouped into relations (tables).
• Each row in a table has its own unique key.
• A primary key is a specific choice of a minimal set of attributes (columns) that uniquely specify a tuple (row) in a relation (table).
• A foreign key is a set of attributes (columns) whose values exist in another relation (table), and uniquely identify a tuple (row) in the other table
• Constraints make it possible to restrict the domain (possible values) of an attribute (column). Constraint options include:
  • Primary key
  • Foreign key
  • Stored procedure (data validation)
  • Index
• Most relational databases use the SQL data definition and query language.

Data Definition Language

• Data Definition Language (DDL) is a syntax similar to a computer programming language for defining data structures, especially database schemas.
• A database schema is its structure described in a formal language supported by the database management system (DBMS).
• DDL statements create and modify database objects such as tables, indexes, and users.
• Common DDL statements are CREATE, ALTER, and DROP.
• The CREATE command is used to establish a new database, table, index, or stored procedure.
• The ALTER statement modifies an existing database object.
• The DROP statement destroys an existing database, table, index, or view.

Data Query Language

• Data Query Language (DQL) is used for performing queries on the data within schema objects.
• The SELECT statement returns a result set of records from one or more tables.
• The SELECT statement is often considered part of Data Manipulation Language (DML) rather than a separate subset.
• The basic SELECT statement syntax is SELECT <column> [AS <alias>, ...] FROM <table> [AS <alias>, ...].
• An asterisk ("*") can be used to specify that the query should return all columns of the queried tables.
• A WHERE clause in SQL specifies that a SQL Data Manipulation Language (DML) statement should only affect rows that meet specified criteria.
• The basic WHERE clause syntax is <SQL-DML-Statement> FROM <table> WHERE <predicate>.
• 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.
• The basic ORDER BY clause syntax is SELECT <column(s)> FROM <table(s)> ORDER BY <column> [DESC] [NULLS FIRST], ....
• The DESC keyword will sort a given column in descending order.
• A JOIN clause combines columns from one or more tables in a relational database.

Data Manipulation Language

• Data Manipulation Language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.
• The INSERT statement adds one or more records to any single table in a relational database.
• The basic INSERT syntax is INSERT INTO <table>(<column(s)>) VALUES(<value(s)>)
• The 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.
• The basic UPDATE syntax is UPDATE <table> SET <column> = <value>, ... [WHERE <condition>]
• The DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.
• The basic DELETE syntax is DELETE FROM <table> [WHERE <condition>]

Data Control Language

• Data Control Language (DCL) is a syntax similar to a computer programming language used to control access (authorization) to data stored in a database.
• GRANT allows specified users to perform specified tasks.
• REVOKE removes user accessibility to a database object.
• The operations for which privileges may be granted to or revoked from a user or role apply to both Data Definition Language (DDL) and Data Manipulation Language (DML).

Database Access Methods

• The functionality provided by a DBMS can vary greatly, and often include:
  • Data storage, retrieval and update
  • User accessible catalog or data dictionary describing the metadata
  • Support for transactions and concurrency
  • Facilities for recovering the database should it become damaged
  • Support for authorization of access and update of data
  • Access support from remote locations
  • Enforcing constraints to ensure data in the database abides by certain rules
• It is generally expected the DBMS will provide a set of command line or graphical utilities for direct access to the database for administration.
• A programmer will code interactions to the database (sometimes referred to as a datasource) via an application program interface (API) or via a database language.
• External interaction with the database will be via an application program that can range from a database tool that allows users to execute SQL queries textually or graphically to a web site that uses a database to store and search information.
• Query and reporting applications often support different user interface options such as:
  • Tables to hold or access stored data
  • Queries to select and modify table data for forms and reports
  • Forms to display and modify data on screen
  • Reports to display data on paper or in digital documents.

Export/Import

• A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format.
• A CSV file typically stores tabular data (numbers and text) in plain text, in which case each line will have the same number of fields. CSV files may be used to export data from and import data to a database.
• A database backup operation is performed occasionally or continuously in case it becomes necessary to restore a database back to a previous state.
• A database dump contains a record of the table structure and/or the data from a database and is usually in the form of a list of SQL statements. A database dump is most often used for backing up a database so that its contents can be restored in the event of data loss.

Key Terms

CRUD (Create, Read, Update, Delete)

The four basic functions of persistent storage.

DDL (Data Definition Language)

A syntax similar to a computer programming language for defining data structures, such as tables and indexes.

DML (Data Manipulation Language)

A computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.

RDBMS (Relational Database Management System)

A software system used to maintain relational databases.

SQL (Structured Query Language)

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

Assessments

• Flashcards: Quizlet: IT Fundamentals - Database Use
• Quiz: Quizlet: IT Fundamentals - Database Use

See Also

• Databases

References

← Database Concepts

IT Fundamentals

Networking →