- 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 :
