Lecture 6

Question 1

SQL vs. NoSQL

Answer 1

SQL is tables

NoSQL no tables (other forms, like graph)

Question 2

SQL is a transform-oriented language with 4 components

Answer 2

Data Definition Language (DDL) - defining database structure

Data Manipulation language (DML) - data retrieval, updating, deletion, etc.

Data Control language (DCL) - access control

Transaction Control Language - managing transactions

Question 3

some important SQL commands

Answer 3

SELECT - extracts data from a database

UPDATE - updates data in a database

DELETE - deletes data from a database

INSERT INTO - inserts new data into a database

CREATE DATABASE - creates a new database

ALTER DATABASE - modifies a database

CREATE TABLE - creates a new table

ALTER TABLE - modifies a table

DROP TABLE - deletes a table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

Question 4

SQL: literal value

Answer 4

constants; a fixed data value

• character* literal: ‘JACK’, ‘BLUE ISLAND’, and ‘101’ (single quotes)
• numeric* literal: 5001 (no quotes)

Question 5

SQL: drop means…

Answer 5

delete

Question 6

CREATE TABLE: column paramters & datatype parameters

Answer 6

column parameters - specify the names of the columns of the table

datatype parameter - specifies the type of data the column can hold (e.g. varchar, integer, date, etc.)

Question 7

creating a copy of a table in SQL

Answer 7

using CREATE TABLE

• The new table gets the same column definitions, where all columns or specific columns can be selected
• If you create a new table using an existing table, the new table will be filled with the existing values from the old table

Question 8

TRUNCATE TABLE

Answer 8

deletes the content of the table, but not the table itself (defintions stay)

TRUNCATE TABLE table_name;

Question 9

PRIMARY KEY and null values

Answer 9

Primary keys must contain UNIQUE values, and cannot contain NULL values; hence add the statement NOT NULL PRIMARY KEY

Question 10

test for a NULL value in SQL

Answer 10

The IS NOT NULL condition

It returns TRUE if a non-NULL value is found, otherwise it returns FALSE

It can be used in a SELECT, INSERT, UPDATE, or DELETE statement

Question 11

FOREIGN KEY

Answer 11

used to link two tables together

A FOREIGN KEY is a field (or collection of fields) in one table (child table) that refers to the PRIMARY KEY in another table (parent table)

Use REFERENCES to link the foreign key to another table

FOREIGN KEY (personID) REFERENCES Persons(personID)

Question 12

where and what is returned from SELECT

Answer 12

The data returned is stored in a result table, called the result-set

Question 13

SQL Query parameters

Answer 13

SELECT what elements, i.e. which fields to shown

FROM what datasets, i.e. which tables to search

WHERE condition, i.e. which values match (filter)