Week 7 - SQL Part 1

Question 1

What are the two categories that SQL functions fit into?

Answer 1

• Data definition language (DDL)
  Includes SQL commands to create database objects such as tables, indexes, and views
• Data manipulation language (DML)
  Includes SQL commands to insert, update, delete and retrieve data within the database

Question 2

What are the two main tasks to be completed when creating a new database?

Answer 2

The two main tasks are:

1. Create database structure

• RDBMS creates the physical files that will hold the database
• differs from on RDBMS to another
• Usually includes authentication process

2. Create tables that will hold the end-user data

Question 3

What is authentication?

Answer 3

Authentication is the process through which the DBMS verifies that only registered users access the database created. Users should log on to the RDBMS using a user ID and a password created by the database administrator.

Question 4

What is a schema?

Answer 4

A schema is Group of database objects such as tables and indexes that are related to each other

Question 5

What are some SQL data types?

Answer 5

• Numeric: number, integer, smallint, decimal
• Character: char, varchar
• Date

Question 6

What are the different types of SQL constraints?

Answer 6

• NOT NULL
  Ensures that a column does not accept nulls
• UNIQUE
  Ensures that all values in a column are unique
• DEFAULT
  Assigns a value to an attribute when a new row is added to a table
• CHECK
  Validates data when an attribute value is entered

Question 7

What are the different data manipulation commands in SQL?

Answer 7

The data manipulation commands in SQL are:

• INSERT: Adding table rows
• COMMIT: Saving table changes
• SELECT: Listing table rows
• UPDATE: Updating table rows
• ROLLBACK: Restoring table contents to state prior to changes
• DELETE: Deleting table rows
• INSERT + SELECT - Inserting table rows with a select subquery

Question 8

How do you add a new table row into a table using SQL commands?

Answer 8

This is the structure of a command that inserts a new table:

INSERT INTO tablename VALUES (v1, V2,…);

Question 9

What is the syntax for updating table rows?

Answer 9

Syntax for updating table rows:

UPDATE tablename
SET columnname = expression [, columnname = expression]
[WHERE conditionlist];

Question 10

What is the syntax for deleting table rows?

Answer 10

Syntax for deleting table rows:

DELETE FROM tablename
[WHERE conditionlist]