Database Management: Chapter 6

40 terms by Lauren_Alena 

Ready to study?
Start with Flashcards

Create a new folder

Advertisement Upgrade to remove ads

Introduction to SQL

Relational DBMS

DBMS that manages data as collection of tables in which all data relationships are represented by common values in related tables

Catalog

Set of schemas that, when put together, constitute a description of a database

Schema

Structure that contains descriptions of objects created by a user, such as base tables, views, and constraints, as part of a DB

Data Definition Language

Commands used to define a DB, including those for creating, altering, and dropping tables and establishing constraints

Data Manipulation Language

Commands used to maintain and query a DB, including those for updating, inserting, modifying, and querying data

Data Control Language

Commands used to control a DB, including those for administering priviliges and committing (saving) data

String

Character (CHAR): stores string values containing any characters in a character set; fixed length.
Character Varying (VARCHAR or VARCHAR2): stores string values containing any characters in a character set but of definable variable length.
Binary Large Object (BLOB): stores binary string values in a hexadecimal format; variable length.

Number

Numeric: stores exact numbers with a defined precision and scale.
Integer (INT): stores exact numbers with a predefined precision and scale of zero.

Temporal

Time Stamp: stores a moment an event occurs, using a definable fraction-of-a-second precision; adjusted for user's time zone.

Boolean

Boolean: stores truth values (true, false, unknown).

Basic Syntax for Creating DB

CREATE SCHEMA database_name; AUTHORIZATION owner_user id

CREATE SCHEMA

Used to define the portion of the DB that a particular user owns.

CREATE TABLE

Defines a new table and its columns.

CREATE VIEW

Defines a logical table from one or more tables or views.

CREATE Commands

CREATE CHARACTER SET, CREATE COLLATION, CREATE TRANSLATION, CREATE ASSERTION, CREATE DOMAIN

Example of CREATE TABLE command

CREATE TABLE Customer_T (CustomerID NUMBER (11,0) NOT NULL, CustomerName VARCHAR2(25) NOT NULL, CustomerAddress VARCHAR2(30)...

ALTER command

ALTER TABLE table_name alter_table_action

Alter_Table_Actions

ADD [COLUMN] column_definition
ALTER [COLUMN] column_name SET DEFAULT default_value
ALTER [COLUMN] column_name DROP DEFAULT
DROP [COLUMN] column_name [RESTRICT] [CASCADE]
ADD table_constraint

Example of ALTER TABLE command

ALTER TABLE Customer_T
ADD COLUMN CustomerType VARCHAR2(2) DEFAULT "Commercial"

DROP TABLE command example

DROP TABLE Customer_T

INSERT command example

INSERT INTO Customer_T VALUES
(001, 'Contemporary Casuals', '1355 S. Himes Blvd.', 'Gainesville', 'FL', 32601)

Entering Null Value

When inserting data into a DB and specifying only a few selected attributes to enter data into, while excluding the remainder of attribute fields

DELETE command example

DELETE FROM Customer_T WHERE CustomerState='HI'

UPDATE command example

UPDATE Product_T
SET ProductStandardPrice = 775 WHERE ProductID = 7

CREATE INDEX command example

CREATE INDEX Name_IDX ON Customer_t (CustomerName)

DROP INDEX command

DROP INDEX Name_IDX

SELECT

Lists the columns from based tables, derived tables, or views to be projected into the table that will be the results of the command (lists the data you want to display); necessary

FROM

Identifies that tables, derived tables, or views from which columns will be chosen to appear in the result table and includes the tables, derived tables, or views needed to join tables to process the query; necessary

WHERE

Includes the conditions for row selection within the items in the FROM clause and the conditions between tables, derived tables, or views for joining; use the AND, OR , and NOT operators to customize conditions; optional

SELECT command example

SELECT ProductDescription, ProductStandardPrice FROM Product_T WHERE ProductStandardPrice < 275

Data Manipulation Commands

UPDATE, INSERT, DELETE, SELECT;
UPDATE, INSERT, DELETE allow you to modify tables, while SELECT allows you to query the data contained in the tables.

GROUP BY

Indicate categorization of results

HAVING

Indicate the conditions under which a category (group) will be included

ORDER BY

Sorts the result according to specified criteria

COUNT command example

COUNT (*) FROM Orderline_t WHERE OrderID= 1004

Wildcard

Used in SELECT commands to select everything in that table; example= SELECT * Customer_T

Base Table

Table in the relational data model containing the inserted raw data

Virtual Table

Table constructed automatically as needed by a DBMS; not maintained as real data

Dynamic View

Virtual table that is created dynamically upon request by a user; not temporary, but its definition is stored in the system catalog and the contents of the view are materialized as a result of the SQL query that uses the view

Materialized View

Copies or replicas of data, based on SQL queries created in the same manner as dynamic views

Please allow access to your computer’s microphone to use Voice Recording.

Having trouble? Click here for help.

We can’t access your microphone!

Click the icon above to update your browser permissions above and try again

Example:

Reload the page to try again!

Reload

Press Cmd-0 to reset your zoom

Press Ctrl-0 to reset your zoom

It looks like your browser might be zoomed in or out. Your browser needs to be zoomed to a normal size to record audio.

Please upgrade Flash or install Chrome
to use Voice Recording.

For more help, see our troubleshooting page.

Your microphone is muted

For help fixing this issue, see this FAQ.

Star this term

You can study starred terms together

NEW! Voice Recording

Create Set