database chapter 5 quiz review

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

Who proposed the concept of relational databases?

D.D. Chamberlin

E.F. Codd (correct)

Michael Stonebraker

Jim Gray

What was the first SQL standard published by ANSI and ISO?

SQL-1 (correct)

SQL-2

SQL-3

SQL-89

Which SQL component is used for storing, accessing, and modifying data?

Authorization language

Data Manipulation Language (DML) (correct)

Data Definition Language (DDL)

Query optimization language

Which of the following SQL versions included new data types and support for XML and JSON?

SQL-3 Signup and view all the answers

What is the purpose of the Data Definition Language (DDL) in SQL?

To create and modify the database structures Signup and view all the answers

Which of these SQL-based systems was among the earliest to use SQL in the 1970s?

Oracle-Ellison Signup and view all the answers

What does the Authorization language in SQL primarily deal with?

Granting privileges to users Signup and view all the answers

Which two major components does SQL consist of?

DDL and DML Signup and view all the answers

What command is used to remove a column from a table?

ALTER TABLE basetablename DROP COLUMN colname Signup and view all the answers

Which SQL command is used to rename a column in a table?

ALTER TABLE basetablename RENAME COLUMN old-colname TO new-colname Signup and view all the answers

To establish a foreign key relationship, which command would be used to add a constraint?

ALTER TABLE basetablename ADD CONSTRAINT constraint_defn Signup and view all the answers

What command drops an entire table from the database?

DROP TABLE basetablename Signup and view all the answers

Which statement correctly describes the use of the SELECT command?

SELECT returns data based on specified columns from a table and can include conditions. Signup and view all the answers

Which SQL command can modify the properties of a column?

ALTER TABLE basetablename MODIFY COLUMN colname [new specifications] Signup and view all the answers

What does the command RENAME TABLE old-table-name TO new-table-name accomplish?

It changes the name of an existing table. Signup and view all the answers

Which command is used to eliminate an index from a database?

DROP INDEX indexname Signup and view all the answers

Which command is used to create a new table in a database?

CREATE TABLE Signup and view all the answers

In MySQL, how does CREATE SCHEMA relate to CREATE DATABASE?

They perform the same function in MySQL. Signup and view all the answers

Which of the following is a requirement for identifiers in Oracle?

Must begin with an alphanumeric character. Signup and view all the answers

What is the maximum length of identifiers in MySQL?

64 characters Signup and view all the answers

What will the following SQL command do? CREATE DATABASE University;

Create a new database named University. Signup and view all the answers

Which statement is true regarding the use of CREATE INDEX?

CREATE INDEX optimizes database reads. Signup and view all the answers

What does the following SQL command do? ALTER TABLE Student ADD COLUMN age INTEGER;

Adds a new column named age to the Student table. Signup and view all the answers

What is the purpose of the CONSTRAINT clause in a CREATE TABLE statement?

To restrict the types of data that can be stored in a column. Signup and view all the answers

Study Notes

History of Relational DBMS & SQL

E.F. Codd proposed relational databases in 1970
IBM's System R, a research relational database, utilized SQL in the early 1970s
Oracle, INGRES, and IBM's SQL/DS and DB2 all incorporated SQL in the late 1970s and early 1980s
SQL is widely implemented in various database systems including Microsoft SQL Server, MySQL, Informix, Sybase, PostgreSQL, Microsoft Access, Teradata, MariaDB, and others.

Standards

ANSI and ISO established the first SQL standard in 1986, known as SQL-1
Subsequent revisions include SQL-89, SQL-2, and SQL-3 (which underwent multiple updates from 1999 to 2019)
SQL-3 introduced new data types, object-oriented facilities, user-defined data types (UDTs), triggers, support for XML and JSON, window analytic functions, temporal databases, multi-dimensional arrays, row pattern matching, and polymorphic tables
While most vendors support the standard, minor variations may exist.

Components of SQL

Data Definition Language (DDL) is used to create and modify database structures
Data Manipulation Language (DML) is used to store, access, and modify data
Authorization language is used to grant privileges to users

The relational model example

The relational model used in the chapter's examples is designed to illustrate the concepts of relational database management

DDL Commands

CREATE DATABASE is used to create a new database
CREATE TABLE is used to define a new table within a database
ALTER TABLE is used to modify the structure of an existing table
- ADD COLUMN adds a new column to a table
- DROP COLUMN removes a column from a table
- MODIFY COLUMN changes a column's properties
- RENAME COLUMN changes the name of a column
RENAME TABLE is used to change the name of a table.
DROP TABLE is used to delete a table
CREATE INDEX is used to create an index on a table
DROP INDEX is used to delete an index

Other Changes to Tables

ALTER TABLE can also add, drop, or rename a constraint
RENAME TABLE changes the name of an existing table
DROP TABLE deletes an existing table
DROP INDEX deletes an existing index

Worker & Projects Example

The Worker and Projects example demonstrates the relationships between tables
The Worker table contains a foreign key referencing the Dept table
The Dept table contains a foreign key referencing the Worker table
This demonstrates the concept of referential integrity in relational databases

Inserts

The example demonstrates how to insert data into tables using the INSERT statement

SQL DML

SQL DML is a non-procedural, declarative language
SQL DML can be used interactively, embedded in a host language, or as a stand-alone programming language (SQL/PSMs)
Basic commands include:
- SELECT
- UPDATE
- INSERT
- DELETE

SELECT Statement

SELECT statement retrieves data from tables
It can be used to perform various operations such as selecting, projecting, joining, and more
Resulting data can have duplicates, forming a multiset

Simple SELECT Statements

The example demonstrates the basic syntax and usage of the SELECT statement to retrieve data from a table

CREATE SCHEMA

CREATE DATABASE and CREATE SCHEMA are equivalent in MySQL
In other database systems, a database may hold multiple schemas, and CREATE DATABASE is used to create a new database, while CREATE SCHEMA is used to create a new schema.

CREATE TABLE

The CREATE TABLE statement defines a new table within a database
It specifies the table name, column names, data types, and various constraints

Identifiers (table/attribute name)

Restrictions apply to identifiers (table and attribute names)
Identifiers cannot use SQL keywords
Table names must be unique within the database
Column names must be unique within a table
Specific requirements exist for Oracle and MySQL, including length limits, case sensitivity, and permitted characters.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.