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

SQL - Exercise 1

Topics of Exercise 1 :

- Query Analyzer Introduction and How to Use It
- Statement Execution
- Create Table
- Primary Key and Foreign Key

*****

SQL Query Analyzer Introduction :

SQL Query Analyzer is a graphical tool that allows you to:

• Create queries and other SQL scripts and execute them against SQL Server databases. (Query window)
  
  
• Quickly create commonly used database objects from predefined scripts. (Templates)
  
  
• Quickly copy existing database objects. (Object Browser scripting feature)
  
  
• Execute stored procedures without knowing the parameters. (Object Browser procedure execution feature)
  
  
• Debug stored procedures. (T-SQL Debugger)
  
  
• Debug query performance problems. (Show Execution Plan, Show Server Trace, Show Client Statistics, Index Tuning Wizard)
  
  
• Locate objects within databases (object search feature), or view and work with objects. (Object Browser)
  
  
• Quickly insert, update, or delete rows in a table. (Open Table window)
  
  
• Create keyboard shortcuts for frequently used queries. (custom query shortcuts feature)
  
  
• Add frequently used commands to the Tools menu. (customized Tools menu feature)

You can run SQL Query Analyzer directly from the Start menu, or you run it from inside SQL Server Enterprise Manager. You can also run SQL Query Analyzer from the command prompt by executing the isqlw utility.

Source : http://msdn2.microsoft.com/en-us/library/aa216945(SQL.80).aspx

*****

Exercise 1 :

A university library needs a database. Please make tables for the university library database which the tables are employee_master, book_master, member_master, and loaning_transaction using these below commands of SQL Server.

CREATE TABLE table name

( field1_name data_type(field_size),

field2_name data_type(field_size),

field3_name data_type(field_size),

....

PRIMARY KEY(field_name),

FOREIGN KEY (field_name) REFERENCES table_name

);

*****

Illustration of Tables :



*****

Answer 1 :

CREATE TABLE employee_master
(
emp_id char(5) NOT NULL,
emp_name varchar(20),
position char(20),
address varchar(50),

PRIMARY KEY (emp_id)
);


CREATE TABLE book_master
(
book_id char(6) NOT NULL,
book_name varchar(60),
amount int,
constraint [ck_amt] check ([amt]>0),
price numeric(10,3),


PRIMARY KEY (book_id)

);


CREATE TABLE member_master
( member_id char(5),
constraint [ck_id] check (len(member_id)=5),
member_name varchar(50),
major char(20),
status char(10),

PRIMARY KEY (member_id)
);


CREATE TABLE loaning_transaction
(
loaning_id char(4),
constraint ck_loanid check (len(loaning_id)=4),
emp_id char(5) NOT NULL,
book_id char(6) NOT NULL,
member_id char(5),
constraint cek_id check (len(member_id)=5),
loaning_duration integer,
constraint ck_duration check(loaning_duration<365),
loaning_date datetime,

PRIMARY KEY (loaning_id),
FOREIGN KEY (emp_id) REFERENCES employee_master,
FOREIGN KEY (book_id) REFERENCES book_master,
FOREIGN KEY (member_id) REFERENCES member_master
);

*****

Syntax to See The Result :

select * from employee_master

select * from book_master

select * from member_master

select * from loaning_transaction

*****

The Result :

Flash - How to Make A Simple Rotary Text

1. Make text “Simple Rotation” by pressing

2. Use Comic Sans MS font of the size 25.


3. Press Esc to terminate writing of text.

4. Press F8 (Modify – Convert to Symbol).

Give name Rotation in Name box and choose Graphic for Type.



5. Determining position of last frame. Bring mouse pointer to frame 24 and Insert Keyframe.



6. Then Insert Keyframe at frame 12.



7. Press Free Transform Tool

8. Bring mouse pointer on the right go to circular middle dot, shift to left to minimize text.



9. Create Motion Tween at frame 6.



10. Then Create Motion Tween at frame 16

11. Choose File – Save. We are done.

12. Press Ctrl + Enter to see the result.