Lecture 6: SQL - DDL, DML

Question 1

What is SQL?

Answer 1

formal and de facto standard language for relational databases

Question 2

DDL?

Answer 2

is the language to create, transform and control databases

Question 3

DML

Answer 3

is the language to access and transform data in the databases

Question 4

DCL

Answer 4

sets up privileges and roles. (data control language)

Question 5

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.

Answer 5

CREATE
TABLE
SELECT
INDEX
VIEW

Question 6

User-defined words are made up by users and represents names of various database objects such as relations, customers, views

Answer 6

CUSTOMER
STUDENT
UNITS
Customer_Id

Question 7

What are Literals?

Answer 7

literals are constants used in SQL statements

e.g. ‘London’, or 650.00

Question 8

List database objects

Answer 8

1. tables
2. indexes
3. views
4. constraints
5. users
6. etc

Question 9

list and explain three DDL commands

Answer 9

1. create
2. alter
3. drop

Question 10

List a typical process to create tables

Answer 10

1. identify the table name
2. identify the columns and their data types
3. identify the constraints (nulls, PK, FK)
4. create tables one by one paying attentions to referential constraints (FK)

Question 11

List 5 integrity features of SQL

Answer 11

1. required data
2. domain constraints
3. entity integrity (PK rule)
4. Referential Integrity (FK Rule)
5. General constraints

Question 12

Why is NOT NULL used?

Answer 12

if certain table columns are important and always require a value we can declare them as not null.

Question 13

List three constraints

Answer 13

1. check constraint
2. unique constraint
3. default constraint

Question 14

what is check constraint?

Answer 14

used to limit the value range that can be placed in a column. applies to only single column

Question 15

what is a unique constraint?

Answer 15

single column, or combination of columns that uniquely defines a row

Question 16

what is default constraint?

Answer 16

used to insert a default value into a column

Question 17

What is database object index

Answer 17

an index is a structure that is created for fast data retrievals in a query. By default an index will always be created on PK columns

Question 18

What is database object VIEW

Answer 18

a virtual table that does not necessarily exist, in the database but is produced upon request, at time of request (aka named query)

e..g create a view so manager at branch B003 can only see details for staff who work in his or her office

Question 19

List three DML commands

Answer 19

1. insert
2. update
3. delete