What is DDL?
Data definition language - concerned w creating and editing tables, not w/ contents of data (CREATE)
What is DML, DQL?
DML - data manipulation language - insert, delete, update
DQL - data quert language - select
How would you create a student table containing a number, name, username, and email? Ensure proper constraints
CREATE TABLE students {
studentNum numeric primary key,
name text check(length(name) > 0),
username text unique,
email text not null);
How to autogenerate an ID? What’s the warning?
CREATE TABLE student (SID integer primary key autoincrement, …);
0 since ID autoincrements, you need to manually look for duplicates
Convert this to DDL:
Employee(EID, Name, Address)
Project(PID, Title, Budget, ManagerID) FK ManagerID REF Employee(EID)
- WorksOn(EID, PID, Start, Task(
CREATE TABLE employee(
eID integer primary key,
name text not null,
address text);
CREATE TABLE project(
pID integer primary key autoincrement,
title text not null,
budget numeric,
managerID integer references employee(eID));
CREATE TABLE worksOn(
eID integer references employee(eID),
pID integer references project(pID),
start date not null,
task text,
primary key(eID, pID) );
How to delete a table named city?
drop table city;
In table city, how to insert new city?
insert into city
values (“Winnipeg”, 1234, 49.1234, 312.1234, “Bowman”);
If we don’t know the mayor, what to put in the table to auto-set it?
mayor text default “Mayor: todo”
How to show entire city table?
select * from city;
How to query for populations in a city over 10000+?
select * from city
where population >10000;
(inclusive)
- returns trues
How to delete cities w/ population >=10000+ and latitude is more than 49?
delete from city
where population >10000
and latitude > 49;
Describe the different possible actions for deletes
no action/restrict - default and normal - do nothing
- wont let you delete if it has references
cascade - if X is delete, deleted every record with a FK pointing to X
set null/set default - autoset it to something else or null
How to set a domain in a key?
studentID integer primary key check (studentID >0)
How do you add a columnn for highest table in a city table, then drop it?
alter table city
add column highestBuilding numeric,
alter table city drop highestBuilding;
How could you rename all students to “bob”
update students set name=”bob”
How could you rename student with ID 67 to cool
update students
set name=”cool”
where studentID= 67;
How could we ensure consistency for insertion, updates, and delete?
insertion + update: we shouldn’t be able to add an entity which references an entity that DNE
delete: if we delete a referenced entity, must account for entities that reference them
