Podcast Beta
Questions and Answers
Who proposed the concept of relational databases?
What was the first SQL standard published by ANSI and ISO?
Which SQL component is used for storing, accessing, and modifying data?
Which of the following SQL versions included new data types and support for XML and JSON?
Signup and view all the answers
What is the purpose of the Data Definition Language (DDL) in SQL?
Signup and view all the answers
Which of these SQL-based systems was among the earliest to use SQL in the 1970s?
Signup and view all the answers
What does the Authorization language in SQL primarily deal with?
Signup and view all the answers
Which two major components does SQL consist of?
Signup and view all the answers
What command is used to remove a column from a table?
Signup and view all the answers
Which SQL command is used to rename a column in a table?
Signup and view all the answers
To establish a foreign key relationship, which command would be used to add a constraint?
Signup and view all the answers
What command drops an entire table from the database?
Signup and view all the answers
Which statement correctly describes the use of the SELECT command?
Signup and view all the answers
Which SQL command can modify the properties of a column?
Signup and view all the answers
What does the command RENAME TABLE old-table-name TO new-table-name accomplish?
Signup and view all the answers
Which command is used to eliminate an index from a database?
Signup and view all the answers
Which command is used to create a new table in a database?
Signup and view all the answers
In MySQL, how does CREATE SCHEMA relate to CREATE DATABASE?
Signup and view all the answers
Which of the following is a requirement for identifiers in Oracle?
Signup and view all the answers
What is the maximum length of identifiers in MySQL?
Signup and view all the answers
What will the following SQL command do? CREATE DATABASE University;
Signup and view all the answers
Which statement is true regarding the use of CREATE INDEX?
Signup and view all the answers
What does the following SQL command do? ALTER TABLE Student ADD COLUMN age INTEGER;
Signup and view all the answers
What is the purpose of the CONSTRAINT clause in a CREATE TABLE statement?
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.