2.1: First Steps To Database Management: Handling Tables

Question 1

Databases are a collection of ___________

Answer 1

tables

Question 2

Create a Table header (table schema) using DDL

Answer 2

create table Employee
(EmpId integer,
Name varchar(25) not null,
Birthdate date,
Salary numeric (8,2),
primary key (EmptId);

Question 3

What do different relational database management systems share?

Answer 3

SQL and underlying data model

Question 4

Create the table body (table instance) using the DML

Answer 4

insert into Employee
values (567, ‘Meyer’, date ‘1975-03-25’, 23000);

Question 5

Update record using DML

Answer 5

update Employee set Salary = 37000 where EmpId = 456;

Question 6

Show the employee table (syntax)

Answer 6

select *
from Employee

Question 7

Example of a PROJECTION on a selected list of attributes
Show (only) the employee IDs and their salaries.

Answer 7

select EmpId, Salary
from Employee;

Question 8

varchar(25)

Answer 8

variable character string
25 max characters allowed

Question 9

not null (schema constraint)

Answer 9

cannot be empty

Question 10

numeric(8,2)

Answer 10

numeric - type for decimal numbers
8 digits long max
2 decimal places

Question 11

primary key (EmpId)

Answer 11

unique. 2 records cannot have the same primary key

Question 12

*

Answer 12

Selects all fields to be shown

Question 13

Example of a selection with a filter condition (together with projection)
Show the IDs and salaries of all employees whose alary exceeds $27000.

Answer 13

select EmpId, Salary
from Employee
where Salary > 27000;

Question 14

select (queries)

Answer 14

Select fields to be considered,
* if all fields are to be considered

Question 15

where (queries)

Answer 15

filter condition
ex. Salary > 27000

Question 16

from (queries)

Answer 16

tables to be considered

Question 17

Aggregation Query
Determine how many employees work in the company and store the result under attribute “total”

Answer 17

select count(*) as total
from Employee;

Question 18

What are projection attributes?

Answer 18

comma-separated list of attributes after the SELECT clause