SQL - Exercise 2

Topics of Exercise 2 :

- Basic SQL Statements
- Insert Data
- Update Data
- Delete Data
- Where Clause
- Drop Table
- Alter Table

*****

Syntax :

- INSERT
INSERT into table_name
VALUES (‘value1’, ‘value2’,...)

- UPDATE
UPDATE table_name
SET column_name = ‘new_value’
WHERE column_name = ‘some_value’

- DELETE
DELETE FROM table_name
WHERE column_name = ‘some_value’

- ALTER TABLE
ALTER TABLE table_name add column_name data type

- DROP TABLE

DROP TABLE table_name

*****

Exercise 2 :

By using previous tables on Exercise 1, do data manipulation to tables.

A. Insert data into tables like below :

B. Update data of field which still empty or update data which there are.

- At employee_master table,

- fill the position field with “Librarian”

- and address field is

emptied which emp_name is “Bill”.

- From loaning_transaction table,

- change loaning_date “11/08/2003” with ”01/18/2003 13:16:15”
which loaning_id is “C23F”.

- Change book_id into “bk02’ which the transaction is on “February”

C. Delete Data

- Delete record which loaning_duration is smaller or equal to 10 at loaning_transaction table.

- Delete record which the year of loaning_date is smaller than 2003.

D. Alter Table

- Add one column ‘telephone’ at employee_master table which data type is char and field size is 15.

E. Drop Table

- Drop the table of loaning_transaction

*****

Answer 2 :

A.

INSERT INTO employee_master VALUES ('emp01', ' ' , 'Data Entry' , 'Jurong 12')

INSERT INTO employee_master VALUES ('emp02', 'Bill' , ' ' , 'Pasir Ris 23’)

INSERT INTO employee_master VALUES ('emp03’, 'Chen' , 'Librarian' , 'Suntec 34')

INSERT INTO book_master VALUES ('bk01', ' ' , '15' , '45')

INSERT INTO book_master VALUES ('bk02', 'Database' , '3' , '50')

INSERT INTO book_master VALUES ('bk03', 'Hardware' , '27' , '55')

INSERT INTO member_master VALUES ('mb01', 'Ramesh' , ' ' , 'Lecturer')

INSERT INTO member_master VALUES ('mb02', 'John' , 'Information Systems' , 'Student')

INSERT INTO member_master VALUES ('mb03', 'Enya' , ‘Arts’ , 'Lecturer')

INSERT INTO loaning_transaction VALUES ('A12B', 'emp03' , 'bk01' , 'mb001' , '4' , '02/14/2004 15:00:35')

INSERT INTO loaning_transaction VALUES ('C23F', 'emp02', 'bk02', 'mb002' , '15' , '11/08/2003)

INSERT INTO loaning_transaction VALUES ('D45T', 'emp01' , 'bk03', 'mb003' , '2' , '3/27/2002')

INSERT INTO loaning_transaction VALUES ('A32R', 'emp01' , 'bk03' , 'mb003' , '11' , '8/09/2002')

B.

UPDATE employee_master SET position = 'Librarian', address = ' '

WHERE emp_name = 'Bill'




UPDATE loaning_transaction SET loaning_date = '01/18/2003 13:16:15' WHERE loaning_id = 'C23F'



UPDATE loaning_transaction SET book_id = 'bk02' WHERE month (loaning_date) = '2'

C.

DELETE FROM loaning_transaction WHERE loaning_duration <= '10'

DELETE FROM loaning_transaction WHERE year (loaning_date) < '2003'




D.

ALTER TABLE employee_master ADD Telephone char(15)

E.

DROP TABLE loaning_transaction