Database Management/Advanced SQL

From Wikiversity
Jump to navigation Jump to search

This lesson introduces advanced SQL, including data definition language and data control language.

Objectives and Skills[edit | edit source]

Objectives and skills for this lesson include:

  • Understand advanced SQL concepts
  • Create data definition language SQL queries using a database engine
  • Create data control language SQL queries using a database engine

Readings[edit | edit source]

  1. Wikipedia: Database engine
  2. Wikipedia: Data definition language
  3. Wikipedia: Data control language
  4. Wikipedia: Database dump
  5. Wikibooks: Structured Query Language/Create Table
  6. Wikibooks: Structured Query Language/Views
  7. Wikibooks: Structured Query Language/Managing Indexes
  8. Wikibooks: Structured Query Language/Managing Rights

Multimedia[edit | edit source]

  1. YouTube: Constraints - SQL
  2. YouTube: What Are SQL Views And How Can You Create Them?
  3. YouTube: Advanced SQL Tutorial | Stored Procedures + Use Cases
  4. YouTube: SQL Index
  5. YouTube: Generating Scripts for Database Objects in SQL Server

Activities[edit | edit source]

  1. Select a database management system to use for this lesson. SQLite is recommended. Other options include Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL.
  2. Select a graphic user interface to connect with the database management system. Tools for specific database systems include DB Browser for SQLite, Microsoft SQL Server Management Studio, MySQL Workbench, Oracle SQL Developer, and Postgre GUI tools. Generic database administration tools that work with multiple database systems include DBeaver.
  3. Use a Northwind script to create the Northwind database in your selected DBMS.
  4. Complete the Constraints, Views, Stored Procedures, and Indexes activities below.
  5. Dump your completed database schema to an SQL script and review the results.

Microsoft SQL Server[edit | edit source]

  1. For Linux and Windows. Download a free edition of Microsoft: SQL Server DBMS.
  2. Download and install Microsoft: SQL Server Management Studio (Windows) or Microsoft: Azure Data Studio (Linux).
  3. Complete one or more of the following tutorials using SQL Server and SQL Server Management Studio:
  4. Review Microsoft: Generate and Publish Scripts Wizard and practice dumping a database to SQL statements. Use the schema only option to exclude data.

MySQL[edit | edit source]

  1. For Linux, MacOS, and Windows. Download and install the free and open MySQL Community Server DBMS.
  2. Download and install the free and open MySQL Workbench.
  3. Complete one or more of the following tutorials using MySQL and MySQL Workbench:
  4. Review MySQL: mysqldump and practice dumping a database to SQL statements. Use the -d or --no-data options to exclude data.

SQLite[edit | edit source]

  1. For Linux, MacOS, and Windows. Download and install the free and open SQLite DBMS.
  2. Download and install the free and open DB Browser for SQLite.
  3. Complete one or more of the following tutorials using SQLite and DB Browser for SQLite:
  4. Review SQLite Tutorial: How To Use The SQLite Dump Command and practice dumping a database to SQL statements. Use the .schema command to exclude data. Alternatively, use DB Browser for SQLite and select File / Export to export the database to an SQL file. Practice exporting the schema only.

Constraints[edit | edit source]

  1. Review the Northwind SQL script for foreign key constraints in the CREATE TABLE statements. Create an INSERT statement to insert a new product using an invalid supplier and/or category ID to verify that foreign key constraints are being enforced. Create a DELETE statement to delete a supplier with existing products to verify that foreign key constraints are being enforced.
  2. Review each of the Northwind database tables and modify the CREATE TABLE statements to enforce the listed column constraints (NOT NULL, > 0, etc.). Create INSERT statements to test column constraints and verify that they are being enforced.
  3. Modify the CREATE TABLE statement for the Orders table to include a default value for Order Date. Create an INSERT statement to test that the default value is automatically added to new orders.

Views and Stored Procedures[edit | edit source]

  1. Create views for each of the Northwind SQL SELECT queries. Create stored procedures for each of the Northwind SQL INSERT, UPDATE, and DELETE statements.
    Note: SQLite doesn't support saving stored procedures. Saved SQL statements may be executed using the sqlite3 command line instead.

Indexes[edit | edit source]

  1. Review each of the Northwind database tables and create appropriate indexes for the indicated columns.
  2. For the UNIQUE indexes, create an INSERT statement to insert a duplicate product name and verify that the UNIQUE constraints are being enforced.

Lesson Summary[edit | edit source]

  • A database engine (or storage engine) is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.[1]
  • The term "database engine" is frequently used interchangeably with "database server" or "database management system". A "database instance" refers to the processes and memory structures of the running database engine.[2]
  • A database, while in operation, resides simultaneously in several types of storage, forming a storage hierarchy. This hierarchy includes the processor, processor cache, RAM, and non-volatile storage systems.[3]
  • Internal database storage structures include flat files, hash tables, B+ trees, ISAM, and heaps.[4]
  • A view is the result set of a stored query on the data, which the database users can query just as they would in a table.[5]
  • A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS).[6]
  • Indexing is a technique some storage engines use for improving database performance.[7]
  • Data definition language (DDL) is a syntax for creating and modifying database objects such as tables, indices, and users. Common examples of DDL statements include CREATE, ALTER, and DROP.[8]
  • The format of the CREATE TABLE statement is:[9]
    CREATE TABLE table_name (column_definitions) table_options
  • Column definitions include[10]:
    • column_name data_type {NULL|NOT NULL} {column options}
    • DEFAULT value
    • CONSTRAINT constraint_definition
    • PRIMARY KEY columns
  • A check constraint is a type of integrity constraint in SQL which specifies a requirement that must be met by each row in a database table.[11]
  • The format of the CHECK constraint statement is:[12]
    CHECK (expression)
  • A foreign key is a set of attributes in a table that refers to the primary key of another table. Foreign key constraints prevent inserting, updating, or deleting records that would violate the referenced key value.[13]
  • The format of the FOREIGN KEY constraint statement is:[14]
    FOREIGN KEY (columns) REFERENCES table_name (columns) [ON UPDATE option] [ON DELETE option]
  • The format of the CREATE VIEW statement is:[15]
    CREATE VIEW view_name AS select_statement
  • The format of the CREATE PROCEDURE statement is:[16]
    CREATE PROCEDURE procedure_name sql_statement
  • The format of the CREATE INDEX statement is:[17]
    CREATE [UNIQUE] INDEX index_name ON table_name(columns)
  • A data control language (DCL) is a syntax similar to a computer programming language used to control access (authorization) to data stored in a database. DCL statements include GRANT and REVOKE.[18]
  • The format of the GRANT statement is:[19]
    GRANT action ON object TO user_or_role
  • The format of the REVOKE statement is:[20]
    REVOKE action ON object FROM user_or_role

Key Terms[edit | edit source]

constraint
Restricts the domain of an attribute.[21]
Data Control Language (DCL)
A syntax used to manage access (authorization) to data stored in a database.[22]
Data Definition Language (DDL)
A syntax for creating and modifying database objects such as tables, indices, and users.[23]
database engine
The underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.[24]
foreign key
A set of attributes in a table that refers to the primary key of another table.[25]
index
Provides quicker access to data.[26]
stored procedure
Executable code that is associated with, and generally stored in, the database.[27]
view
A stored query.[28]

See Also[edit | edit source]

References[edit | edit source]