Lecture 6: SQL - DDL, DML Flashcards
What is SQL?
formal and de facto standard language for relational databases
DDL?
is the language to create, transform and control databases
DML
is the language to access and transform data in the databases
DCL
sets up privileges and roles. (data control language)
Reserved words are a fixed part of SQL and must be spelt exactly as required and cannot be split across lines.
List the reserved words in SQL.
CREATE TABLE SELECT INDEX VIEW
User-defined words are made up by users and represents names of various database objects such as relations, customers, views
CUSTOMER
STUDENT
UNITS
Customer_Id
What are Literals?
literals are constants used in SQL statements
e.g. ‘London’, or 650.00
List database objects
- tables
- indexes
- views
- constraints
- users
- etc
list and explain three DDL commands
- create
- alter
- drop
List a typical process to create tables
- identify the table name
- identify the columns and their data types
- identify the constraints (nulls, PK, FK)
- create tables one by one paying attentions to referential constraints (FK)
List 5 integrity features of SQL
- required data
- domain constraints
- entity integrity (PK rule)
- Referential Integrity (FK Rule)
- General constraints
Why is NOT NULL used?
if certain table columns are important and always require a value we can declare them as not null.
List three constraints
- check constraint
- unique constraint
- default constraint
what is check constraint?
used to limit the value range that can be placed in a column. applies to only single column
what is a unique constraint?
single column, or combination of columns that uniquely defines a row