How can we help?

You can also find more resources in our Help Center.

40 terms

Database Management: Chapter 6

Introduction to SQL
STUDY
PLAY
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