1. JOINS

Explain the action of, and show the expected result set for the following SQL statements (Ansii and Non Ansii code is supplied), using the tables and data below as an example.

Authors TitleAuthors

Au_id	Au_Name
1	White
2	Whirry
3	Watson
4	O’Leary
5	O’Dear

 

Au_id	Title_id	Title_Name
1	1	The Joy of Rex - dinosaur fun
1	2	Zen in Programming
2	3	cc
3	4	Diesel Maintenance at home
Null	5	Great Expectorations

Equi Join (aka join, normal join, equal join)

Select authors.au_name, titleauthor.title_id

titleauthor.titleName

FROM authors, titleauthor

WHERE authors.au_id =titleauthor.au_id

 

Au_Name	Title_id	Title_Name
White	1	The Joy of Rex - dinosaur fun
White	2	Zen in Programming
Whirry	3	A short history of bolts
Watson	4	Diesel Maintenance at home

Right Outer Join (=*)

Select authors.au_name, titleauthor.title_id

FROM authors, titleauthor

WHERE authors.au_id =*titleauthor.au_id

Au_Name	Title_id	Title_Name
	5	Great Expectorations
White	1	The Joy of Rex - dinosaur fun
White	2	Zen in Programming
Whirry	3	A short history of bolts
Watson	4	Diesel Maintenance at home

Left Outer Join (*=)

SELECT authors.au_lname, titleauthor.title_id

FROM authors, titleauthor

WHERE titleauthors.au_id *= author.au_id

Au_Name	Title_id	Title_Name
White	1	The Joy of Rex - dinosaur fun
White	2	Zen in Programming
Whirry	3	A short history of bolts
Watson	4	Diesel Maintenance at home
O’leary
O’dear

1. OPTIMISER

Given the following structure

Create Table (test)

(

Col_1 integer not null

Col_2 Smalldatetime Not Null

Col3 float Null Default 1.0

)

Create Index Test_index_1 on test Col_1

1. Explain the different effect on performance expected from these 2 similar SQL statements

Select * from test where Col_1 > 10000

Select * from test where convert(float,Col_1) > 10000.0

Statement "Select * from test where Col_1 > 10000" is performed faster Than the other since:

Col1_1 is defined as index and for each data index no need to perform convert before comparing

Cluster index: special index that force the SQL Server to store the table data in the exact order of the index (improve access performance on the table)

Non cluster index: (default) an index whose pages of index data containing pointer to the actual pages of table data in the database.

 

1. GENERAL SQL/STORED PROCEDURE QUESTIONS

1. What do you use triggers for?

A trigger is a special type of stored procedure that is executed by the SQL Server when an Insert, Modify or Delete operation is performed Against a given table. Triggers are run AFTER the operation take effect to ensure data integrity

2. What are stored procedures? Why use them? Advantages? Disadvantages?

Stored procedures are the way to create routines and procedures that Are run on the server.

Why use them?

The execution time is much less than at the workstation

Advantages?

1/ Stored procedures are compiled the first time they’re run and are stored in a system table of the current database

2/ We can execute a stored procedure on either local or remote SQL Server

3/ An application can also execute stored procedure

3. What is the difference between WHERE and HAVING clauses?

Where clauses form a row selection expression that specifies the rows Should be included in the query.

Having clauses is used to determine the groups to be displayed in the output of the SELECT statement

4. Describe the function of the "select into" statement?

Retrieve rows and columns from one source table to a target table