IT Fundamentals/Database Use
This lesson introduces database use.
Objectives and Skills
editObjectives and skills for the database use portion of IT Fundamentals certification include:[1]
- Summarize methods used to interface with databases.
- Relational methods
- Data manipulation
- Select
- Insert
- Delete
- Update
- Data definition
- Create
- Alter
- Drop
- Permissions
- Data manipulation
- Database access methods
- Direct/manual access
- Programmatic access
- User interface/utility access
- Query/report builders
- Export/import
- Database dump
- Backup
- Relational methods
Readings
editMultimedia
editActivities
edit- Complete one or more of the following tutorials:
- 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).
- 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
editRelational Databases
edit- A relational database is a digital database where all data is represented in terms of tuples (rows or records), grouped into relations (tables).[2]
- Each row in a table has its own unique key.[3]
- A primary key is a specific choice of a minimal set of attributes (columns) that uniquely specify a tuple (row) in a relation (table).[4]
- 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[5]
- Constraints make it possible to restrict the domain (possible values) of an attribute (column). Constraint options include:[6]
- Primary key
- Foreign key
- Stored procedure (data validation)
- Index
- Most relational databases use the SQL data definition and query language.[7]
Data Definition Language
edit- Data Definition Language (DDL) is a syntax similar to a computer programming language for defining data structures, especially database schemas.[8]
- A database schema is its structure described in a formal language supported by the database management system (DBMS).[9]
- DDL statements create and modify database objects such as tables, indexes, and users.[10]
- Common DDL statements are CREATE, ALTER, and DROP.[11]
- The
CREATE
command is used to establish a new database, table, index, or stored procedure.[12] - The
ALTER
statement modifies an existing database object.[13] - The
DROP
statement destroys an existing database, table, index, or view.[14]
Data Query Language
edit- Data Query Language (DQL) is used for performing queries on the data within schema objects.[15]
- The
SELECT
statement returns a result set of records from one or more tables.[16] - The
SELECT
statement is often considered part of Data Manipulation Language (DML) rather than a separate subset.[17] - The basic
SELECT
statement syntax isSELECT <column> [AS <alias>, ...] FROM <table> [AS <alias>, ...]
.[18][19] - An asterisk ("*") can be used to specify that the query should return all columns of the queried tables.[20]
- A
WHERE
clause in SQL specifies that a SQL Data Manipulation Language (DML) statement should only affect rows that meet specified criteria.[21] - The basic
WHERE
clause syntax is<SQL-DML-Statement> FROM <table> WHERE <predicate>
.[22] - By default, relational database systems may return data rows in any order, or more specifically, without any order.[23]
- 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.[24] - The basic
ORDER BY
clause syntax isSELECT <column(s)> FROM <table(s)> ORDER BY <column> [DESC] [NULLS FIRST], ...
.[25] - The
DESC
keyword will sort a given column in descending order.[26] - A
JOIN
clause combines columns from one or more tables in a relational database.[27]
Data Manipulation Language
edit- Data Manipulation Language (DML) is a computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.[28]
- The
INSERT
statement adds one or more records to any single table in a relational database.[29] - The basic
INSERT
syntax isINSERT INTO <table>(<column(s)>) VALUES(<value(s)>)
[30] - 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.[31] - The basic
UPDATE
syntax isUPDATE <table> SET <column> = <value>, ... [WHERE <condition>]
[32] - 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.[33] - The basic
DELETE
syntax isDELETE FROM <table> [WHERE <condition>]
[34]
Data Control Language
edit- Data Control Language (DCL) is a syntax similar to a computer programming language used to control access (authorization) to data stored in a database.[35]
GRANT
allows specified users to perform specified tasks.[36]REVOKE
removes user accessibility to a database object.[37]- 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).[38]
Database Access Methods
edit- The functionality provided by a DBMS can vary greatly, and often include:[39]
- 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.[40]
- 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.[41]
- 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.[42]
- Query and reporting applications often support different user interface options such as:[43]
- 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
edit- 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.[44]
- 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.[45]
- A database backup operation is performed occasionally or continuously in case it becomes necessary to restore a database back to a previous state.[46]
- 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.[47]
Key Terms
edit- CRUD (Create, Read, Update, Delete)
- The four basic functions of persistent storage.[48]
- DDL (Data Definition Language)
- A syntax similar to a computer programming language for defining data structures, such as tables and indexes.[49]
- DML (Data Manipulation Language)
- A computer programming language used for adding (inserting), deleting, and modifying (updating) data in a database.[50]
- RDBMS (Relational Database Management System)
- A software system used to maintain relational databases.[51]
- SQL (Structured Query Language)
- A domain-specific language used in programming and designed for managing data held in a relational database management system (RDBMS).[52]
Assessments
editSee Also
editReferences
edit- ↑ CompTIA: IT Fundamentals (ITF+) Exam Objectives FC0-U61
- ↑ Wikipedia: Relational model
- ↑ Wikipedia: Relational database
- ↑ Wikipedia: Primary key
- ↑ Wikipedia: Foreign key
- ↑ Wikipedia: Relational database
- ↑ Wikipedia: Relational model
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Database schema
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Data query language
- ↑ Wikipedia: Select (SQL)
- ↑ Wikipedia: Data query language
- ↑ Wikipedia: SQL
- ↑ Wikipedia: Select (SQL)
- ↑ Wikipedia: SQL
- ↑ Wikipedia: Where (SQL)
- ↑ Wikipedia: Where (SQL)
- ↑ Wikipedia: Order by
- ↑ Wikipedia: Order by
- ↑ Wikipedia: Order by
- ↑ Wikipedia: Order by
- ↑ Wikipedia: Join (SQL)
- ↑ Wikipedia: Data manipulation language
- ↑ Wikipedia: Insert (SQL)
- ↑ Wikipedia: Insert (SQL)
- ↑ Wikipedia: Update (SQL)
- ↑ Wikipedia: Update (SQL)
- ↑ Wikipedia: Delete (SQL)
- ↑ Wikipedia: Delete (SQL)
- ↑ Wikipedia: Data control language
- ↑ Wikipedia: Data control language
- ↑ Wikipedia: Data control language
- ↑ Wikipedia: Data control language
- ↑ Wikipedia: Database
- ↑ Wikipedia: Database
- ↑ Wikipedia: Database
- ↑ Wikipedia: Database
- ↑ Go Free: LibreOffice Base Tutorial
- ↑ Wikipedia: Comma-separated files
- ↑ Wikipedia: Comma-separated files
- ↑ Wikipedia: Database
- ↑ Wikipedia: Database dump
- ↑ Wikipedia: Create, read, update and delete
- ↑ Wikipedia: Data definition language
- ↑ Wikipedia: Data manipulation language
- ↑ Wikipedia: Relational database
- ↑ Wikipedia: SQL