EXERCISE 1

CREATE DATABASE TEMP
NAME =TEMP_EMPLOYEE
FILENAME=S:\WEERAPOL\DDL_DEMO\APP_DATA\TempEmployee.mdf

EXERCISE 2

create table publishers
(
pub_id char(4) primary key check(pub_id like'[1-2][0-9][0-9][0-9]'),
pub_name varchar(40),
suburb varchar(40),
state char(3),
country varchar(30) default 'Australia',

)   

create table jobs
(
job_id smallint primary key identity (1,1),
job_desc varchar(50) not null default 'New Position',
min_lvl tinyint not null check(min_lvl >= 10),
max_lvl tinyint not null check(max_lvl <= 250),
)      

create table employees
(
emp_id char(90) primary key check(emp_id like '[A-Z][0-9][0-9][0-9][0-9][GS]'),
fname varchar(20) not null,
minit char(1),
lname varchar(30) not null,
job_id smallint not null default 1,
job_lvl tinyint default 10,
pub_id char(4) not null default 1000,
hire_date datetime not null default getdate(),

)      

EXERCISE 3

alter table employees
add constraint FKEmployeesjobs foreign key (job_id) references jobs(job_id)
on update no action
on delete cascade

alter table employees
add constraint FKEmployeespublishers foreign key (pub_id) references publishers(pub_id)
on update no action
on delete cascade

EXERCISE 4

TABLE PUBS

//INSERT ONE AT A TIME
1>
INSERT INTO employees
                        (pub_id)
VALUES        ('1000')

2>

INSERT INTO employees
                        (pub_id)
VALUES        ('1001')

3>
INSERT INTO employees
                        (pub_id)
VALUES        ('2000')

//FROM W3SCHOOLS (DOES NOT WORK)

INSERT INTO employees
                        (pub_id)
VALUES        ('2000'),('2002'),('2003')


TABLE JOBS
1>
INSERT INTO jobs
                        (job_desc, min_lvl, max_lvl)
VALUES        ('clerk', '10', '30')

2>
INSERT INTO jobs
       (job_desc, min_lvl, max_lvl)
VALUES         ('Director','200','250')

3>
INSERT INTO jobs
       (min_lvl, max_lvl)
VALUES         ('30','100')


TABLE EMPLOYEES
1>
INSERT INTO employees
                        (emp_id, fname, lname, job_id, pub_id, hire_date)
VALUES        ('JA1000G', 'Joe', 'Abra', '1', '1000', '10/1/2008')


INSERT INTO employees
                        (emp_id, fname, lname, hire_date)
VALUES        ('JA1000G', 'Joe', 'Abra', '10/1/2008')



2>

INSERT INTO employees
values('BA2000S','Jack','S','Majo','2','220','1001')

INSERT INTO employees
                        (emp_id, fname, minit, lname, job_id, job_lvl, pub_id)
VALUES        ('BA2000S', 'Jack', 'S', 'Majo', '2', '220', '1001')

EXERCISE 5

UPDATE jobs
SET job_desc = 'Receptionist'
WHERE job_desc = 'New Position'

UPDATE employees
SET job_lvl = ceiling(job_lvl + (job_lvl *10)/100)

EXERCISE 6

DELETE
FROM pubs
WHERE pub_id = 2000

EXERCISE 7

SELECT        employees.fname + '  ' + employees.lname + ' is a ' + jobs.job_desc + ' hired  in ' + CAST(DATEPART(yyyy, employees.hire_date) AS char) AS Expr1
FROM            employees LEFT OUTER JOIN
                        jobs ON employees.job_id = jobs.job_id
WHERE        (employees.emp_id = 'JA1000G')

SELECT        employees.fname + ' ' + ISNULL(employees.minit, '') + ' ' + employees.lname + ' is a ' + jobs.job_desc + ' hired  in ' + CAST(DATEPART(yyyy,
                        employees.hire_date) AS char) AS Expr1
FROM            employees LEFT OUTER JOIN
                        jobs ON employees.job_id = jobs.job_id
EXERCISE 8

SELECT        employees.emp_id, employees.fname, employees.minit, employees.lname, employees.job_id, employees.job_lvl, employees.pub_id, employees.hire_date,
                        jobs.job_id AS Expr1, jobs.job_desc, jobs.min_lvl, jobs.max_lvl
FROM            employees LEFT OUTER JOIN
                        jobs ON employees.job_id = jobs.job_id
WHERE        (employees.job_lvl = jobs.min_lvl)