A VERSION OF THE GUPTA TUTORIAL FOR MICROSOFT SQL SERVER (1997)
Objective

The following is a version of a tutorial originally provided with GUPTA's SQLBASE. This was used very successfully when GUPTA SQLBASE was the preferred college database engine. It was doctored so that it could be read in a stand-alone fashion and so that it could be used in a far more practical fashion than in that which it is delivered. Now we have moved to Microsoft SQL Server, it has been updated to make it compatible with SQL Server. This in-built tutorial covers in a readily understandable fashion:

  • - basic data definition capabilities
  • - row creation capabilities
  • - a very reasonable range of SELECT capabilities
  • - index maintenance
  • - nested queries
  • - views
  • - the basic SYS structures
  • - cursors
  • - privilege maintenance and
  • - housekeeping.

Quite a lot in fact. In fact that was one of the problems of the original versionn of the tutorial- it could take several hours to work through and take notes and there was NO way to stop it and restart it at the point at which it was stopped! It has been 'chopped' up so that it can be worked through in a staged fashion at several sessions. There are now a number of sections defined which are numbered parts 1 through 6. They normally terminate at the point where a table and/or indexes has just been created. Thus if an attempt is made to re-run an earlier section without dropping the table or indexes they may fail or give unpredictable results.

It can be started by clicking the ISQL/w icon which is found within the Microsoft SQL Server program group on the Windows NT screen and running the interactive windows program which it provides. You will be allocated a personal identity from this range by your tutors.

The groups are identified by a single letter. For each letter a database has been created and there are 9 logins for each group which are numbered 01 through 09 and prefixed with the group letter. There is also an initial password definition which has the same value. The 01 user is also identified as the database owner who has the right to create and alter tables and who can grant these privileges to the other members of the group individually or to all members of the group through a collective user identity. Thus group 'a' has:

  • - a database called group_a
  • - a database owner called login_a01 with a password login_a01
  • - users called login_a02 through to login_a09 with respective passwords
  • - a common group identity for the users called group_a.

Before attempting to connect to SQL Server and use the tutorial effectively then you should ensure that you have CREATE TABLE and ALTER TABLE privileges. Your database owner has these privileges or can grant them to the group or can grant them individually. Your database owner will also need to allocate the identities to the members of the group.

Within the connect server dialog box you should enter:

  • - click on the 'LIST SERVERS' to reveal the servers and then click on MSSQLSERVER
  • - enter your login id which is in the form login_g0n where g is the group letter and n a number
  • - enter your login id which is also in the form login_g0n in the PASSWORD field.

Once inside ISQL/w and during the tutorial sessions, one must simply:

  • - highlight the entire SQL COMMAND using the cursor
  • - click the GREEN TRIANGLE ICON to execute the command or press Ctrl+E
  • - click the RESULTS TAB to see the query results
  • - click the QUERY TAB to return to the script.

To exit from the tutorial sessions, then pull down EXIT from the FILE menu. You can now enter the tutorial which is called MSS-TUTL.SQL from the Y drive.

THE MSS-TUTL TUTORIAL - PART 1

Introduction

Like other software suppliers, Microsoft SQL Server claims to adopt the standards defined by the ANSI standards. This conformance is never 100% so that this tutorial does not cover the standard SQL expressions which are to be found in a book such as Chris Date's. Therefore, do not expect it to wholly conform to the terminology defined there. There are several instances where Transact SQL does not conform to normal usage and terminology eg. multi-row insertion is not feasible and dates prior to 1753 are not permitted.

This automated tutorial is intended to illustrate the capabilities of the SQL language as implemented in the Microsoft SQL Server database management system. Since SQL SERVER is intended primarily as a database engine for serious application development on the IBM PC family of computers and networks, a basic level of familiarity with database concepts is assumed.

This tutorial runs under the program called ISQL/w. It is an Interactive Data Manager that enables you to execute SQL commands (either interactively from the keyboard or by executing a batch file). It also contains certain non-SQL commands. For example, SET STATISTICS TIME ON and SET STATISTICS I/O will respectively cause to be displayed the time taken by the database for performing each operation and the input/output operations involved in executing the command..

Other commands include a very wide range of stored procedures are intended to perform functions usually available only through an Application Program Interface such as C. In addition, there are commands that are necessary for database administrators. While all commands issued to SQL must normally be terminated by a semi-colon, this convention is NOT required in ISQL/w.

Table and Column Definition with CREATE TABLE

A start is made by first defining and creating the structure of a table called PRESIDENT.

CREATE TABLE PRESIDENT
(PRES_NAME VARCHAR(20) NOT NULL,
BIRTH_DATE DATETIME NULL,
YRS_SERV INTEGER NULL,
DEATH_AGE INTEGER NULL,
PARTY VARCHAR (20) NULL,
STATE_BORN VARCHAR(20) NULL)

Creation of the table is confirmed by using the following command.

SELECT * FROM SYSOBJECTS WHERE TYPE = 'U'

In the above table, six columns were defined. The data in those columns can be of four types: a character field, a number field, a date field or a longfield. Among the many datatypes which SQL SERVER supports are the following SQL data type keywords:

  • BINARY
  • CHAR
  • VARCHAR
  • TEXT
  • SMALLINT
  • INTEGER
  • FLOAT
  • DECIMAL and
  • DATETIME.

In the PRESIDENT table, PRES_NAME, PARTY and STATE_BORN are variable length character fields with a maximum length of 255. The PRES_NAME is specified to be a NOT NULL field ie. it may not contain a blank. All other fields are specified as NULL. BIRTH_DATE is a date (and may be entered in one of several formats). YRS_SERV and DEATH_AGE are numeric data types and must be entered as integers. No TEXT data field is defined (these may be variable length character fields of unlimited length and can contain text or binary data). The SQL SERVER Transact-SQL Reference contains more information on the rich variety of SQL data types.

Populating the Table with INSERT

The following INSERT command adds one row of data to the PRESIDENT table. Note all character and datetime data must be enclosed in single quotes. Numbers have been entered as integers and the date field is specified in dd-mon-yyyy format.

INSERT INTO PRESIDENT VALUES
('Monroe J','28-Apr-1758',8,73,'Demo-Rep','Virginia')

The contents of the columns of the table can be confirmed using the following star select command.

SELECT * FROM PRESIDENT

In the above SQL INSERT statement, an entire row of data was entered. You may also enter data selectively into some columns only, and not into others. To do this, you must specify the columns into which data is to be inserted. The remaining columns will all contain the null character. The following statement inserts data into the first four and the sixth columns of the PRESIDENTS table by listing them individually.

INSERT INTO PRESIDENT
(PRES_NAME, BIRTH_DATE, YRS_SERV, DEATH_AGE, STATE_BORN)
VALUES
('Adams J Q','11-Jul-1767',4,80,'Massachusetts')

The updated contents of the table can be confirmed and the presence of the null indicator can be confirmed by using the star select command.

SELECT * FROM PRESIDENT

The following UPDATE statement updates data into the fifth column of the PRESIDENTS table.

UPDATE PRESIDENT
SET PARTY = 'Demo-rep'

WHERE PRES_NAME = 'Adams J Q'

Although the above example did not show this, note that SQL SERVER will allow you to use the decimal or scientific notation to enter numbers. Magnitude of the numbers may range from 1.7E-308 to 1.7E+308. Decimal precision up to 18 digits is maintained automatically. Dates (as in BIRTH_DATE above) may be entered in numeric formats (eg. mm-dd-yy or dd/mm/yy) or alpha format (eg. dd-mon-yy or mon/dd/yy). The 'yy' may be expanded to 'yyyy' if the year is not in the current century although values of yy < 50 are interpreted as 20yy.

The bulk of the table can now be populated using the first form of the INSERT statement.

INSERT INTO PRESIDENT VALUES ('Jackson A','15-Mar-1767',8,78,'Democratic','South Carolina')

INSERT INTO PRESIDENT VALUES ('Van Buren M','05-Dec-1782',4,79,'Democratic','New York')

INSERT INTO PRESIDENT VALUES ('Harrison W H','09-Feb-1773',0,68,'Whig','Virginia')

INSERT INTO PRESIDENT VALUES ('Tyler J','29-Mar-1790',3,71,'Whig','Virginia')

INSERT INTO PRESIDENT VALUES ('Polk J K','02-Nov-1795',4,53,'Democratic','North Carolina')

INSERT INTO PRESIDENT VALUES ('Taylor Z','24-Nov-1784',1,65,'Whig','Virginia')

INSERT INTO PRESIDENT VALUES ('Fillmore M','07-Jan-1800',2,74,'Whig','New York')

INSERT INTO PRESIDENT VALUES ('Pierce F','23-Nov-1804',4,64,'Democratic','New Hampshire')

INSERT INTO PRESIDENT VALUES ('Buchanan J','23-Apr-1791',4,77,'Democratic','Pennsylvania')

INSERT INTO PRESIDENT VALUES ('Lincoln A','12-Feb-1809',4,56,'Republican','Kentucky')

INSERT INTO PRESIDENT VALUES ('Johnson A','29-Dec-1808',3,66,'Democratic ','North Carolina')

INSERT INTO PRESIDENT VALUES ('Grant U S','27-Apr-1822',8,63,'Republican','Ohio')

INSERT INTO PRESIDENT VALUES ('Hayes R B','04-Oct-1822',4,70,'Republican','Ohio')

INSERT INTO PRESIDENT VALUES ('Garfield J A','19-Nov-1831',0,49,'Republican','Ohio')

INSERT INTO PRESIDENT VALUES ('Arthur C A','05-Oct-1829',3,56,'Republican','Vermont')

INSERT INTO PRESIDENT VALUES ('Cleveland G','18-Mar-1837',8,71,'Democratic','New Jersey')

INSERT INTO PRESIDENT VALUES ('Harrison B','20-Aug-1833',4,67,'Republican','Ohio')

INSERT INTO PRESIDENT VALUES ('McKinley W','29-Jan-1843',4,58,'Republican','Ohio')

INSERT INTO PRESIDENT VALUES ('Roosevelt T','27-Oct-1858',7,60,'Republican','New York')

INSERT INTO PRESIDENT VALUES ('Taft W H','15-Sep-1857',4,72,'Republican','Ohio')

INSERT INTO PRESIDENT VALUES ('Wilson W','28-Dec-1856',8,67,'Democratic','Virginia')

INSERT INTO PRESIDENT VALUES ('Harding W G','02-Nov-1865',2,57,'Republican','Ohio')

INSERT INTO PRESIDENT VALUES ('Coolidge C','4-Jul-1872',5,60,'Republican','Vermont')

INSERT INTO PRESIDENT VALUES ('Hoover H C','10-Aug-1874',4,90,'Republican','Iowa')

INSERT INTO PRESIDENT VALUES ('Roosevelt F D','30-Jan-1882',12,63,'Democratic','New York')

INSERT INTO PRESIDENT VALUES ('Truman H S','08-May-1884',7,88,'Democratic','Missouri')

INSERT INTO PRESIDENT VALUES ('Eisenhower D D','14-Oct-1890',8,79,'Republican','Texas')

INSERT INTO PRESIDENT VALUES ('Kennedy J F','29-May-1917',2,46,'Democratic','Massachusetts')

INSERT INTO PRESIDENT VALUES ('Johnson L B','27-Aug-1908',5,65,'Democratic','Texas')

The final entries contain null values for death age so these are populated using the second form of the INSERT statement

INSERT INTO PRESIDENT (PRES_NAME, BIRTH_DATE, YRS_SERV, PARTY, STATE_BORN)
VALUES
('Nixon R M','09-Jan-1913',5,'Republican','California')

INSERT INTO PRESIDENT (PRES_NAME, BIRTH_DATE, YRS_SERV, PARTY, STATE_BORN)
VALUES
('Ford G R','14-Jul-1913',2,'Republican','Nebraska')

INSERT INTO PRESIDENT (PRES_NAME, BIRTH_DATE, YRS_SERV, PARTY, STATE_BORN)
VALUES
('Carter J E','01-Oct-1924',4,'Democratic','Georgia')

INSERT INTO PRESIDENT (PRES_NAME, BIRTH_DATE, YRS_SERV, PARTY, STATE_BORN)
VALUES
('Reagan R','6-Feb-1911',4,'Republican','Illinois')

The entire contents of the table can be displayed using the star select.

SELECT * FROM PRESIDENT

The first doctored tutorial MSS-TUT1 terminates at this point. Re-running the MSS-TUT1 tutorial without dropping the PRESIDENT table will cause problems since the tutorial will be attempting to create a table which already exists. However, the table and its contents can be removed with via DROP TABLE. The format of this command is DROP TABLE tablename and to drop the President table you would use the command DROP TABLE PRESIDENT.

THE MSS-TUTL TUTORIAL - PART 2

Querying the database using select

The SELECT command is used to query the database. The following is an example of a very simple SELECT command that requests all of the columns and rows in the PRESIDENT table.

SELECT * FROM PRESIDENT 

In the following example, only the PRES_NAME, BIRTH_DATE, DEATH_AGE and STATE_BORN columns are requested.

SELECT PRES_NAME, BIRTH_DATE, DEATH_AGE, STATE_BORN
FROM PRESIDENT

Here, in addition to limiting the request to the president's name and his birth state, only those rows are selected where the birth state is Ohio. Note that since Ohio is a character constant, it must be enclosed in quotes.

SELECT PRES_NAME, STATE_BORN
FROM PRESIDENT
WHERE STATE_BORN = 'Ohio'

Here, only the presidents not born in Ohio are selected. The ! symbol qualifies the = sign so that it is treated as not equal.

SELECT PRES_NAME, STATE_BORN
FROM PRESIDENT
WHERE STATE_BORN != 'Ohio'

Fuzzy Selection

The query doesn't always need to be based on an exact match. For example, in the following query, one wishes to see the names of all the presidents whose name contains the suffix-like characters 'son'.

SELECT PRES_NAME
FROM PRESIDENT
WHERE PRES_NAME LIKE '%son%'

The % sign signifies a wild card character. In the above query, it means that any name that contains the character string 'son' ( regardless of what comes before or after it ) will be retrieved. The following query performs a more exact match. Only those names are requested where the string 'son'is preceded by any four characters.

SELECT PRES_NAME
FROM PRESIDENT
WHERE PRES_NAME LIKE '____son%'

The _ character is thus a more restrictive wild card character than the %. Both of them may be intermixed in all sorts of combinations. The following is an example of such intermixing.

SELECT PRES_NAME
FROM PRESIDENT
WHERE PRES_NAME LIKE 'J___son%'

The above were examples where character strings were matched either exactly or partially. The following two queries perform numeric comparisons. They display the names and ages of all the presidents who died at age less than 60 and at age greater than or equal to 60.

SELECT PRES_NAME, DEATH_AGE
FROM PRESIDENT

WHERE DEATH_AGE < 60

SELECT PRES_NAME, DEATH_AGE
FROM PRESIDENT
WHERE DEATH_AGE >= 60

Null Values and Dates

Note that the names of living presidents did not show up in the above two queries. That's because in the database, their death_age is represented by a null character. A null implies that the value of a particular field is 'unknown'. To retrieve a field on the basis of a null match, the IS NULL function must be used.

SELECT PRES_NAME, DEATH_AGE
FROM PRESIDENT
WHERE DEATH_AGE IS NULL

SQL SERVER also allows comparisons to be made for date fields. In the following query, the names and birth dates of all the presidents who were born after the year 1900 are requested.

SELECT PRES_NAME, BIRTH_DATE FROM PRESIDENT

WHERE BIRTH_DATE > '1-Jan-1900'

Note that while date output is always in dd-mon-yyyy format, SQL SERVER will accept input in:

mm-dd-yy AND mm/dd/yy formats too.

By specifying a column as a DATE, you can make intelligent queries that dates follow their own

special logic and arithmetic. The following query results in an error because February did not have 29

days in the year 1900 (centenary years have a leap year only once every four hundred years).

SELECT PRES_NAME, BIRTH_DATE

FROM PRESIDENT

WHERE BIRTH_DATE > '29-Feb-1900'

But the following query works just fine.

SELECT PRES_NAME, BIRTH_DATE

FROM PRESIDENT

WHERE BIRTH_DATE > '29-Feb-1904'

Other Predicates

Up to this point dates have used the alphabetic format. Dates which use a wholly numeric format can

be mis-interpreted according to whether US (mdy) or English (dmy) conventions are used. To clarify

this the SET DATEFORMAT command is used. The following sets it to US format.

SET DATEFORMAT mdy

The BETWEEN predicate is used to check if the data in given field belongs to a range of values. The

following query retrieves the names and ages of those presidents who were born between January 1,

1901 and December 31, 1925.

SELECT PRES_NAME, BIRTH_DATE

FROM PRESIDENT

WHERE BIRTH_DATE BETWEEN '1/1/1901' and '12/31/1925'

Note that the use of this convention without specifying the year in full searches for birth dates between

2001 and 2025 since values of YY less than 50 are interpreted as 20YY. It returns NO rows!

SELECT PRES_NAME, BIRTH_DATE

FROM PRESIDENT

WHERE BIRTH_DATE BETWEEN '1/1/01' and '12/31/25'

The IN (list) predicate is used to retrieve rows based on data in a given set of values. The following

query returns the names of all the presidents who were born in the states of New York, California or

Texas.

SELECT PRES_NAME, STATE_BORN

FROM PRESIDENT

WHERE STATE_BORN IN ('New York', 'California', 'Texas')

The above queries (BETWEEN and IN) could also have been constructed by ORing together two or

more search conditions. Queries that contain many more complex conditions can be constructed by

using a combination of ORs, ANDs and parentheses. In the following example, only

the currently living presidents

who either:

belong to the democratic party or

were born in the state of Illinois

are selected.

SELECT PRES_NAME, DEATH_AGE, PARTY, STATE_BORN

FROM PRESIDENT

WHERE DEATH_AGE IS NULL

AND (PARTY = 'Democratic'

OR STATE_BORN = 'Illinois')

Here the entire search condition of the previous query is negated by the NOT operator.

SELECT PRES_NAME, PARTY, STATE_BORN

FROM PRESIDENT

WHERE NOT (DEATH_AGE IS NULL

AND (PARTY = 'Democratic'

OR STATE_BORN = 'Illinois'))

Syntax and Expressions

Note also that even though DEATH_AGE is referenced in the WHERE clause, it does not appear in

the list of columns being selected. SQL does not require that the columns participating in a search

condition also be in the select list or that they appear in the same sequence as they were entered into

the table. In fact, the select list may contain arithmetic expressions that contain any combination of

column names and constants. The following query computes the number of terms a president has

served (by dividing the number of years served by 4).

SELECT PRES_NAME, YRS_SERV, YRS_SERV/4

FROM PRESIDENT

Arithmetic expressions may also appear in the WHERE clause.

SELECT PRES_NAME, YRS_SERV, YRS_SERV/4

FROM PRESIDENT

WHERE YRS_SERV/4 > 2

Arithmetic expressions can also be used for DATE data types. SQL SERVER understands that date

arithmetic is not the same as ordinary arithmetic and will perform the appropriate calculations

automatically. Date expressions can also include certain built-in functions such as DATEADD(),

DATEDIFF(), DATENAME() and DATEPART(). The following query selects the name and birthdate of each president, and then calculates the approximate date of his death.

ie. BIRTHDATE plus 365 * DEATH_AGE.

SELECT PRES_NAME, BIRTH_DATE, DATEADD(DAY, 365*DEATH_AGE , BIRTH_DATE)

FROM PRESIDENT

Date expressions and functions of the above type can be included anywhere that arithmetic expressions

can exist (ie.in a SELECT list or in the WHERE Clause). These functions can compute the year, quarter,

month or weekday of a given date. The following query computes the year of each president's birth and

death.

SELECT PRES_NAME, DATEPART (YEAR, BIRTH_DATE),

DATEPART (YEAR, BIRTH_DATE)+DEATH_AGE

FROM PRESIDENT

Sorting Output with ORDER BY clause

A SQL query may contain an ORDER BY clause to enables rows to be sorted by a specified order. The following query selects the names of all the presidents table and displays them in alphabetical order.

SELECT PRES_NAME

FROM PRESIDENT

ORDER BY PRES_NAME

ASCending or DESCending order is ok with ASC the default. DESC must be defined if desired.

SELECT PRES_NAME, DEATH_AGE

FROM PRESIDENT

ORDER BY DEATH_AGE DESC

Note from the above example that if the sort field contains any null values then those rows containing

nulls are displayed following the remaining rows. One primary sort and multiple secondary sorts may

be specified. The following query sorts them first by their state of birth, and then in descending order

by their date of birth.

SELECT PRES_NAME, STATE_BORN, BIRTH_DATE

FROM PRESIDENT

ORDER BY STATE_BORN, BIRTH_DATE DESC

Sorting may be done by a column that does not appear in the select list. It may also reference a

computed column (ie. an expression) by using its relative position number in the select list. The

following query computes the year of death of each president and orders the result by that field.

SELECT PRES_NAME, DATEPART(YEAR,BIRTH_DATE)+DEATH_AGE

FROM PRESIDENT

ORDER BY 2

The second doctored tutorial terminates at this point. If you only wish to re-run Part 2 then you DO NOT need to execute the command DROP TABLE PRESIDENT since no further tables were created. However, re-running Part 1 without dropping the PRESIDENT table WILL cause problems so if you DO wish to re-run Part 1 then you should execute the command DROP TABLE PRESIDENT..

THE MSS-TUTL TUTORIAL - PART 3

Special Functions for Column Aggregrations

It is also necessary to demonstrate the ability of SQL SERVER to perform aggregations on various

columns of a table. To illustrate this effectively, one must create another table called ELECTION.

This table will contain the names of all the candidates in each presidential election since

independence, and data regarding electoral votes cast in his favour, and a one character code

indicating whether he won or lost the election.

CREATE TABLE ELECTION

(ELECTION_YEAR SMALLINT,

CANDIDATE VARCHAR(20),

VOTES SMALLINT,

WINNER_LOSER_INDIC CHAR(1))

Now the table is defined it can be populated.

INSERT INTO ELECTION VALUES (1789,'Washington G',69,'W')

INSERT INTO ELECTION VALUES (1789,'Adams J',34,'L')

INSERT INTO ELECTION VALUES (1789,'Jay J',9,'L')

INSERT INTO ELECTION VALUES (1789,'Harrison R H',6,'L')

INSERT INTO ELECTION VALUES (1789,'Rutledge J',6,'L')

INSERT INTO ELECTION VALUES (1789,'Hancock J',4,'L')

INSERT INTO ELECTION VALUES (1789,'Clinton G',3,'L')

INSERT INTO ELECTION VALUES (1789,'Huntington S',2,'L')

INSERT INTO ELECTION VALUES (1789,'Milton J',2,'L')

INSERT INTO ELECTION VALUES (1789,'Armstrong J',1,'L')

INSERT INTO ELECTION VALUES (1789,'Lincoln B',1,'L')

INSERT INTO ELECTION VALUES (1789,'Telfair E',1,'L')

INSERT INTO ELECTION VALUES (1792,'Washington G',132,'W')

INSERT INTO ELECTION VALUES (1792,'Adams J',77,'L')

INSERT INTO ELECTION VALUES (1792,'Clinton G',50,'L')

INSERT INTO ELECTION VALUES (1792,'Jefferson T',4,'L')

INSERT INTO ELECTION VALUES (1792,'Burr A',1,'L')

INSERT INTO ELECTION VALUES (1796,'Adams J',71,'W')

INSERT INTO ELECTION VALUES (1796,'Jefferson T',68,'L')

INSERT INTO ELECTION VALUES (1796,'Pinckney T',59,'L')

INSERT INTO ELECTION VALUES (1796,'Burr A',30,'L')

INSERT INTO ELECTION VALUES (1796,'Adams S',15,'L')

INSERT INTO ELECTION VALUES (1796,'Ellsworth O',11,'L')

INSERT INTO ELECTION VALUES (1796,'Clinton G',7,'L')

INSERT INTO ELECTION VALUES (1796,'Jay J',5,'L')

INSERT INTO ELECTION VALUES (1796,'Iredell J',3,'L')

INSERT INTO ELECTION VALUES (1796,'Henry J',2,'L')

INSERT INTO ELECTION VALUES (1796,'Johnson S',2,'L')

INSERT INTO ELECTION VALUES (1796,'Washington G',2,'L')

INSERT INTO ELECTION VALUES (1796,'Pinckney C C',1,'L')

INSERT INTO ELECTION VALUES (1800,'Jefferson T',73,'W')

INSERT INTO ELECTION VALUES (1800,'Burr A',73,'L')

INSERT INTO ELECTION VALUES (1800,'Adams J',65,'L')

INSERT INTO ELECTION VALUES (1800,'Pinckney C C',64,'L')

INSERT INTO ELECTION VALUES (1800,'Jay J',1,'L')

INSERT INTO ELECTION VALUES (1804,'Jefferson T',162,'W')

INSERT INTO ELECTION VALUES (1804,'Pinckney C C',14,'L')

INSERT INTO ELECTION VALUES (1808,'Madison J',122,'W')

INSERT INTO ELECTION VALUES (1808,'Pinckney C C',47,'L')

INSERT INTO ELECTION VALUES (1808,'Clinton G',6,'L')

INSERT INTO ELECTION VALUES (1812,'Madison J',128,'W')

INSERT INTO ELECTION VALUES (1812,'Clinton G',89,'L')

INSERT INTO ELECTION VALUES (1816,'Monroe J',183,'W')

INSERT INTO ELECTION VALUES (1816,'King R',34,'L')

INSERT INTO ELECTION VALUES (1820,'Monroe J',231,'W')

INSERT INTO ELECTION VALUES (1820,'Adams J Q',1,'L')

INSERT INTO ELECTION VALUES (1824,'Adams J Q',84,'W')

INSERT INTO ELECTION VALUES (1824,'Jackson A',99,'L')

INSERT INTO ELECTION VALUES (1824,'Crawford W H',41,'L')

INSERT INTO ELECTION VALUES (1824,'Clay H',37,'L')

INSERT INTO ELECTION VALUES (1828,'Jackson A',178,'W')

INSERT INTO ELECTION VALUES (1828,'Adams J',83,'L')

INSERT INTO ELECTION VALUES (1832,'Jackson A',219,'W')

INSERT INTO ELECTION VALUES (1832,'Clay H',49,'L')

INSERT INTO ELECTION VALUES (1832,'Floyd J',11,'L')

INSERT INTO ELECTION VALUES (1832,'Wirt W',7,'L')

INSERT INTO ELECTION VALUES (1836,'Van Buren M',170,'W')

INSERT INTO ELECTION VALUES (1836,'Harrison W H',73,'L')

INSERT INTO ELECTION VALUES (1836,'White H L',26,'L')

INSERT INTO ELECTION VALUES (1836,'Webster D',14,'L')

INSERT INTO ELECTION VALUES (1836,'Mangum W P',11,'L')

INSERT INTO ELECTION VALUES (1840,'Harrison W H',234,'W')

INSERT INTO ELECTION VALUES (1840,'Van Buren M',60,'L')

INSERT INTO ELECTION VALUES (1844,'Polk J K',170,'W')

INSERT INTO ELECTION VALUES (1844,'Clay H',105,'L')

INSERT INTO ELECTION VALUES (1848,'Taylor Z',163,'W')

INSERT INTO ELECTION VALUES (1848,'Cass L',127,'L')

INSERT INTO ELECTION VALUES (1852,'Pierce F',254,'W')

INSERT INTO ELECTION VALUES (1852,'Scott W',42,'L')

INSERT INTO ELECTION VALUES (1856,'Buchanan J',174,'W')

INSERT INTO ELECTION VALUES (1856,'Fremont J C',114,'L')

INSERT INTO ELECTION VALUES (1856,'Fillmore M',8,'L')

INSERT INTO ELECTION VALUES (1860,'Lincoln A',180,'W')

INSERT INTO ELECTION VALUES (1860,'Breckinridge J',72,'L')

INSERT INTO ELECTION VALUES (1860,'Bell J',39,'L')

INSERT INTO ELECTION VALUES (1860,'Douglas S',12,'L')

INSERT INTO ELECTION VALUES (1864,'Lincoln A',212,'W')

INSERT INTO ELECTION VALUES (1864,'McClellan G B',21,'L')

INSERT INTO ELECTION VALUES (1868,'Grant U S',214,'W')

INSERT INTO ELECTION VALUES (1868,'Seymour',80,'L')

INSERT INTO ELECTION VALUES (1872,'Grant U S',286,'W')

INSERT INTO ELECTION VALUES (1872,'Hendricks T A',42,'L')

INSERT INTO ELECTION VALUES (1872,'Brown B G',18,'L')

INSERT INTO ELECTION VALUES (1872,'Jenkins C J',2,'L')

INSERT INTO ELECTION VALUES (1872,'Davis D',1,'L')

INSERT INTO ELECTION VALUES (1876,'Hayes R B',185,'W')

INSERT INTO ELECTION VALUES (1876,'Tilden S J',184,'L')

INSERT INTO ELECTION VALUES (1880,'Garfield J A',214,'W')

INSERT INTO ELECTION VALUES (1880,'Hancock W S',155,'L')

INSERT INTO ELECTION VALUES (1884,'Cleveland G',219,'W')

INSERT INTO ELECTION VALUES (1884,'Blaine J G',182,'L')

INSERT INTO ELECTION VALUES (1888,'Harrison B',233,'W')

INSERT INTO ELECTION VALUES (1888,'Cleveland G',168,'L')

INSERT INTO ELECTION VALUES (1892,'Cleveland G',277,'W')

INSERT INTO ELECTION VALUES (1892,'Harrison B',145,'L')

INSERT INTO ELECTION VALUES (1892,'Weaver J B',22,'L')

INSERT INTO ELECTION VALUES (1896,'McKinley W',271,'W')

INSERT INTO ELECTION VALUES (1896,'Bryan W J',176,'L')

INSERT INTO ELECTION VALUES (1900,'McKinley W',292,'W')

INSERT INTO ELECTION VALUES (1900,'Bryan W J',155,'L')

INSERT INTO ELECTION VALUES (1904,'Roosevelt T',336,'W')

INSERT INTO ELECTION VALUES (1904,'Parker A B',140,'L')

INSERT INTO ELECTION VALUES (1908,'Taft W H',321,'W')

INSERT INTO ELECTION VALUES (1908,'Bryan W J',162,'L')

INSERT INTO ELECTION VALUES (1912,'Wilson W',435,'W')

INSERT INTO ELECTION VALUES (1912,'Roosevelt T',88,'L')

INSERT INTO ELECTION VALUES (1912,'Taft W H',8,'L')

INSERT INTO ELECTION VALUES (1916,'Wilson W',277,'W')

INSERT INTO ELECTION VALUES (1916,'Hughes C E',254,'L')

INSERT INTO ELECTION VALUES (1920,'Harding W G',404,'W')

INSERT INTO ELECTION VALUES (1920,'Cox W W',127,'L')

INSERT INTO ELECTION VALUES (1924,'Coolidge C',382,'W')

INSERT INTO ELECTION VALUES (1924,'Davis J W',136,'L')

INSERT INTO ELECTION VALUES (1924,'La Follette R M',13,'L')

INSERT INTO ELECTION VALUES (1928,'Hoover H C',444,'W')

INSERT INTO ELECTION VALUES (1928,'Smith A E',87,'L')

INSERT INTO ELECTION VALUES (1932,'Roosevelt F D',472,'W')

INSERT INTO ELECTION VALUES (1932,'Hoover H C',59,'L')

INSERT INTO ELECTION VALUES (1936,'Roosevelt F D',523,'W')

INSERT INTO ELECTION VALUES (1936,'Landon A M',8,'L')

INSERT INTO ELECTION VALUES (1940,'Roosevelt F D',449,'W')

INSERT INTO ELECTION VALUES (1940,'Wilkie W L',82,'L')

INSERT INTO ELECTION VALUES (1944,'Roosevelt F D',432,'W')

INSERT INTO ELECTION VALUES (1944,'Dewey T E',99,'L')

INSERT INTO ELECTION VALUES (1948,'Truman H S',303,'W')

INSERT INTO ELECTION VALUES (1948,'Dewey T E',189,'L')

INSERT INTO ELECTION VALUES (1948,'Thurmond J S',39,'L')

INSERT INTO ELECTION VALUES (1952,'Eisenhower D D',442,'W')

INSERT INTO ELECTION VALUES (1952,'Stevenson A',89,'L')

INSERT INTO ELECTION VALUES (1956,'Eisenhower D D',457,'W')

INSERT INTO ELECTION VALUES (1956,'Stevenson A',73,'L')

INSERT INTO ELECTION VALUES (1956,'Jones W B',1,'L')

INSERT INTO ELECTION VALUES (1960,'Kennedy J F',303,'W')

INSERT INTO ELECTION VALUES (1960,'Nixon R M',219,'L')

INSERT INTO ELECTION VALUES (1960,'Byrd N',15,'L')

INSERT INTO ELECTION VALUES (1964,'Johnson L B',486,'W')

INSERT INTO ELECTION VALUES (1964,'Goldwater B',52,'L')

INSERT INTO ELECTION VALUES (1968,'Nixon R M',301,'W')

INSERT INTO ELECTION VALUES (1968,'Humphrey H H',191,'L')

INSERT INTO ELECTION VALUES (1968,'Wallace G C',46,'L')

INSERT INTO ELECTION VALUES (1972,'Nixon R M',520,'W')

INSERT INTO ELECTION VALUES (1972,'McGovern G S',17,'L')

INSERT INTO ELECTION VALUES (1972,'Hospers J',1,'L')

INSERT INTO ELECTION VALUES (1976,'Carter J E',297,'W')

INSERT INTO ELECTION VALUES (1976,'Ford G R',240,'L')

INSERT INTO ELECTION VALUES (1980,'Reagan R',489,'W')

INSERT INTO ELECTION VALUES (1980,'Carter J E',49,'L')

INSERT INTO ELECTION VALUES (1984,'Reagan R',528,'W')

INSERT INTO ELECTION VALUES (1984,'Mondale W F',10,'L')

Column Aggregations

A user may compute the MIN, MAX, AVG, SUM and COUNT of the data in a specified column of a

table. These are known as aggregate functions and may be applied to an entire table or to groups of

rows within a table.

The following query returns the count of all the candidates in the ELECTION table.

SELECT COUNT (*)

FROM ELECTION

The * is interpreted to mean "all the rows". It may only be used in the COUNT aggregate function.

Strictly speaking, the query should have requested count(candidate). That's because all rows were

counted even if some columns in a row were null values. If an election ever had occurred where no

candidates ran, then count(candidate) would have ignored those rows and returned a correct result.

Since no such election has takenplace, count(candidate) is the same as count(*). The above query may

be further restricted to count only those candidates who won.

SELECT COUNT (CANDIDATE)

FROM ELECTION

WHERE WINNER_LOSER_INDIC = 'W'

Aggregate functions ignore null values in a column.

You may also specify that duplicate values of the data in a column be ignored. To count the number

of successful presidential winners (note this need not be the same as the number of presidents - some

gain office due to the death of an elected president) one can modify the query and use the following.

SELECT COUNT (DISTINCT CANDIDATE)

FROM ELECTION

WHERE WINNER_LOSER_INDIC = 'W'

A further example is provided by the following query which counts the total number of presidential

elections ever held. It counts only the distinct values of an election_year.

SELECT COUNT (DISTINCT ELECTION_YEAR)

FROM ELECTION

The * is only valid for the COUNT function but the keyword DISTINCT may be used with any

aggregate function. The following query computes the minimum and maximum electoral votes ever

cast for a candidate.

SELECT MIN (VOTES), MAX (VOTES)

FROM ELECTION

Use of GROUP BY and HAVING Clauses

Sometimes, you may want to compute an aggregate for a specified group of rows. For example,

suppose you would like to know the count of all the candidates in each presidential election ever held,

and the minimum, maximum, average and sum of all the electoral votes cast in that election. The

following query will return the desired result.

SELECT ELECTION_YEAR, COUNT(*), MIN(VOTES),

MAX(VOTES), AVG(VOTES), SUM(VOTES)

FROM ELECTION

GROUP BY ELECTION_YEAR

You cannot compute an aggregate of an aggregrate with SQL Server so as to display the maximum and

and minimum number of electoral votes ever cast in an election. However, the following query returns

these values can be calculated by using the aggregrate within the ORDER BY clause.

SELECT DISTINCT SUM(VOTES)

FROM ELECTION

GROUP BY ELECTION_YEAR

ORDER BY SUM (VOTES)

Sometimes it is desirable to be able to group the results of a query by an expression. This can be done

by specifying in the GROUP BY clause, a number denoting the relative position of the expression in

the SELECT list. This syntax is similar to the one used for the ORDER BY clause when expressions

are involved. The following query computes the count and the sum of the years served by presidents,

grouped by the year of the month of their birth.

SELECT DATEPART (MONTH,BIRTH_DATE), COUNT(YRS_SERV), SUM(YRS_SERV)

FROM PRESIDENT

GROUP BY DATEPART (MONTH,BIRTH_DATE)

When a GROUP BY clause is used in conjunction with a WHERE clause, SQL SERVER first selects

the rows that meet the WHERE condition, and then computes the requested aggregates for the

specified group.

If however, you would like to restrict the output of the aggregate rows after they have been computed,

you may use a HAVING clause to accomplish this result. The following query computes the minimum

and maximum votes for each election, but restricts the output to only those rows where the maximum

was at least ten times the minimum.

SELECT ELECTION_YEAR, MIN(VOTES), MAX(VOTES)

FROM ELECTION

GROUP BY ELECTION_YEAR

HAVING MAX(VOTES)/MIN(VOTES) > 10

The GROUP BY capability is a very powerful feature of SQL SERVER and can be used to perform a

number of queries that otherwise may require a procedural language capability. Any number of

columns may participate in a GROUP BY and any number of conditions may be present in a

HAVING clause. The only restrictions on their use are that both the SELECT list and the HAVING

clause must contain only aggregate functions (MIN, MAX, AVG, SUM, COUNT) or column names

(or expressions) that are present in the GROUP BY clause.

Thus the following queries are illegal.

SELECT ELECTION_YEAR, COUNT(*)

FROM ELECTION

SELECT ELECTION_YEAR, COUNT(*)

FROM ELECTION

GROUP BY ELECTION_YEAR

HAVING WINNER_LOSER_INDIC = 'W'

Use of CREATE INDEX and Performance

In all of the above queries, SQL SERVER scans the entire table looking for a match based on the

search criteria. Sometimes, especially for large tables, it is useful to create an index for one or more

columns. To determine the impact of an index one must monitor their impact using statistics. This is done by issuing the following command.

SET STATISTICS IO ON

Creating an index has only one effect. It CAN improve query performance ie response time.

It has NO effect on the results of the query. Lets run the following query without an index. It will

search the entire ELECTION table looking for those rows where the election year is 1984 and the

candidate is 'Reagan R'.

SELECT ELECTION_YEAR, CANDIDATE

FROM ELECTION

WHERE ELECTION_YEAR = 1984 AND CANDIDATE = 'Reagan R'

The following statement creates an index for the ELECTION_YEAR column.

CREATE INDEX X_ELECTION_YEAR

ON ELECTION (ELECTION_YEAR)

The previous query MIGHT now runs faster than before.

SELECT ELECTION_YEAR, CANDIDATE

FROM ELECTION

WHERE ELECTION_YEAR = 1984 AND CANDIDATE = 'Reagan R'

Indices can be created on every column in a table. Indices can be specified to be in ASCending or

DESCending order (ASC is the default). Indices can also be specified to be UNIQUE (to ensure

unique data in each row) so as to support the concept of a primary key. Indices may also be created for

a concatenation of two or more columns. The maximum size of an index must not however exceed

250 characters. The following SQL statement creates a unique index on the concatenation of

ELECTION_YEAR and CANDIDATE.

CREATE UNIQUE INDEX X_YEAR_CANDIDATE

ON ELECTION (ELECTION_YEAR, CANDIDATE)

If the concatenation of ELECTION_YEAR and CANDIDATE were found to be not unique in the

ELECTION table, the above statment would have returned an error. Also, from now on, any insertion

or updating of the data in the ELECTION table will only be allowed if it does not violate the

uniqueness condition imposed by the above CREATE INDEX command.

An index may be removed with the DROP INDEX command.

DROP INDEX ELECTION.X_YEAR_CANDIDATE

Multi-Table Manipulation

Now let us demonstrate SQL SERVER's capability to JOIN two or more tables of data. Prior to doing

that, the following table called PRES_MARRIAGE needs to be created. This table will contain

information about the spouses and children of each president.

CREATE TABLE PRES_MARRIAGE

(PRES_NAME VARCHAR(20),

SPOUSE_NAME VARCHAR(20) NULL,

PR_AGE INTEGER NULL,

SP_AGE INTEGER NULL,

NR_CHILDREN INTEGER NULL,

MAR_YEAR INTEGER NULL)

It can now be populated.

INSERT INTO PRES_MARRIAGE VALUES ('Washington G','Custis M D',26,27,0,1759)

INSERT INTO PRES_MARRIAGE VALUES ('Adams J','Smith A',28,19,5,1764)

INSERT INTO PRES_MARRIAGE VALUES ('Jefferson T','Skelton M W',28,23,6,1772)

INSERT INTO PRES_MARRIAGE VALUES ('Madison J','Todd D D P',43,26,0,1794)

INSERT INTO PRES_MARRIAGE VALUES ('Monroe J','Kortright E',27,17,3,1786)

INSERT INTO PRES_MARRIAGE VALUES ('Adams J Q','Johnson L C',30,22,4,1797)

INSERT INTO PRES_MARRIAGE VALUES ('Jackson A','Robards R D',26,26,0,1794)

INSERT INTO PRES_MARRIAGE VALUES ('Van Buren M','Hoes H',24,23,4,1807)

INSERT INTO PRES_MARRIAGE VALUES ('Harrison W H','Symmes A T',22,20,10,1795)

INSERT INTO PRES_MARRIAGE VALUES ('Tyler J','Christian L',23,22,8,1813)

INSERT INTO PRES_MARRIAGE VALUES ('Tyler J','Gardiner J',54,24,7,1844)

INSERT INTO PRES_MARRIAGE VALUES ('Polk J K','Childress S',28,20,0,1824)

INSERT INTO PRES_MARRIAGE VALUES ('Taylor Z','Smith M M',25,21,6,1810)

INSERT INTO PRES_MARRIAGE VALUES ('Fillmore M','Powers A',26,27,2,1826)

INSERT INTO PRES_MARRIAGE VALUES ('Fillmore M','McIntosh C C',58,44,0,1858)

INSERT INTO PRES_MARRIAGE VALUES ('Pierce F','Appleton J M',29,28,3,1834)

INSERT INTO PRES_MARRIAGE VALUES ('Lincoln A','Todd M',33,23,4,1842)

INSERT INTO PRES_MARRIAGE VALUES ('Johnson A','McCardle E',18,16,5,1827)

INSERT INTO PRES_MARRIAGE VALUES ('Grant U S','Dent J B',26,22,4,1848)

INSERT INTO PRES_MARRIAGE VALUES ('Hayes R B','Webb L W',30,21,8,1852)

INSERT INTO PRES_MARRIAGE VALUES ('Garfield J A','Rudolph L',26,26,7,1858)

INSERT INTO PRES_MARRIAGE VALUES ('Arthur C A','Herndon E L',29,22,3,1859)

INSERT INTO PRES_MARRIAGE VALUES ('Cleveland G','Folson F',49,21,5,1886)

INSERT INTO PRES_MARRIAGE VALUES ('Harrison B','Scott C L',20,21,2,1853)

INSERT INTO PRES_MARRIAGE VALUES ('Harrison B','Dimmick M S L',62,37,1,1896)

INSERT INTO PRES_MARRIAGE VALUES ('McKinley W','Saxton I',27,23,2,1871)

INSERT INTO PRES_MARRIAGE VALUES ('Roosevelt T','Lee A H',22,19,1,1880)

INSERT INTO PRES_MARRIAGE VALUES ('Roosevelt T','Carow E K',28,25,5,1886)

INSERT INTO PRES_MARRIAGE VALUES ('Taft W H','Herron H',28,25,3,1886)

INSERT INTO PRES_MARRIAGE VALUES ('Wilson W','Axson E L',28,25,3,1885)

INSERT INTO PRES_MARRIAGE VALUES ('Wilson W','Galt E B',58,43,0,1915)

INSERT INTO PRES_MARRIAGE VALUES ('Harding W G','De Wolfe F K',25,30,0,1891)

INSERT INTO PRES_MARRIAGE VALUES ('Coolidge C','Goodhue G A',33,26,2,1905)

INSERT INTO PRES_MARRIAGE VALUES ('Hoover H C','Henry L',24,23,2,1899)

INSERT INTO PRES_MARRIAGE VALUES ('Roosevelt F D','Roosevelt A E',23,20,6,1905)

INSERT INTO PRES_MARRIAGE VALUES ('Truman H S','Wallace E V',35,34,1,1919)

INSERT INTO PRES_MARRIAGE VALUES ('Eisenhower D D','Doud G',25,19,2,1916)

INSERT INTO PRES_MARRIAGE VALUES ('Kennedy J F','Bouvier J L',36,24,3,1953)

INSERT INTO PRES_MARRIAGE VALUES ('Johnson L B','Taylor C A',26,21,2,1934)

INSERT INTO PRES_MARRIAGE VALUES ('Nixon R M','Ryan T C',27,28,2,1940)

INSERT INTO PRES_MARRIAGE VALUES ('Ford G R','Warren E B',35,30,4,1948)

INSERT INTO PRES_MARRIAGE VALUES ('Carter J E','Smith R',21,18,4,1946)

INSERT INTO PRES_MARRIAGE VALUES ('Reagan R','Wyman J',28,25,2,1940)

INSERT INTO PRES_MARRIAGE VALUES ('Reagan R','Davis N',41,28,2,1952)

Finally the only president who was a bachelor.

INSERT INTO PRES_MARRIAGE (PRES_NAME) VALUES ('Buchanan J')

Let us create indices on the PRES_NAME column of the PRESIDENT and PRES_MARRIAGE tables

and the MAR_YEAR column of the PRES_MARRIAGE table. Indices are not essential for doing a

join. But as in single table queries, if an index is present, performance of a join query (especially

involving a large number of rows) can be improved.

CREATE INDEX X_PRES_NAME

ON PRESIDENT(PRES_NAME)

CREATE INDEX Y_PRES_NAME

ON PRES_MARRIAGE(PRES_NAME)

CREATE INDEX X_MAR_YEAR

ON PRES_MARRIAGE(MAR_YEAR)

The third tutorial terminates at this point. Re-running earlier tutorials without dropping their associated tables or indexes will cause problems. This can be done with the following commands.

DROP TABLE tablename

DROP INDEX tablename.indexname 

THE MSS-TUTL TUTORIAL - PART 4

Multi-Table Joins

Note that the data contained in the PRESIDENT table and PRES_MARRIAGE tables is different from

each other. The only common column in them is the PRES_NAME. If one needs to find out the date

of birth of each president and the names(s) of his spouse(s), one will need to look up the PRESIDENT

table for the birth date and the PRES_MARRIAGE table for the spouse name(s) and relate these two

pieces of information via their common element -- ie. the president's name. The following query will

accomplish this objective. The operation is known as a JOIN.

SELECT PRESIDENT.PRES_NAME, BIRTH_DATE, SPOUSE_NAME

FROM PRESIDENT, PRES_MARRIAGE

WHERE

PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME

In the above example, the PRES_NAME column had to be prefixed with a tablename to ensure that

the query was unambiguous (a column that is unique to a table does not need to be prefixed by its

table name). The WHERE clause specified that only those rows should be returned where the

PRES_NAME in the PRESIDENT table is the same as the PRES_NAME in the PRES_MARRIAGE

table. If this condition (known as the JOIN condition) were not supplied, the results would consist of

all possible combinations of the rows of the two tables (ie. a total of 39 times 44 rows). This

combination is also known as the cartesian product of two tables.

More on Joins

A JOIN condition does not have to be based on an equality. Any of the SQL comparison operators

(=,<,>,<=,>=) may be used. The joining columns do not have to have the same name either. More

than two tables may participate in a join (the maximum number is 250), and as in a single table query,

the WHERE clause can contain any number of conditions. The following query finds (from the

PRESIDENT, ELECTION and PRES_MARRIAGE tables) the name of each president (and his

spouse) who was married after he took office.

SELECT PRESIDENT.PRES_NAME, SPOUSE_NAME, ELECTION_YEAR, MAR_YEAR

FROM PRESIDENT, ELECTION, PRES_MARRIAGE

WHERE

PRESIDENT.PRES_NAME = ELECTION.CANDIDATE AND

PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME AND

PRES_MARRIAGE.MAR_YEAR > ELECTION.ELECTION_YEAR AND

WINNER_LOSER_INDIC = 'W'

Joins may also be performed in conjunction with groupings and aggregates. The results may be

displayed in any order. The following query displays the name of each president, the number of times

he was married, and the total number of children he had.

SELECT PRESIDENT.PRES_NAME, COUNT(SPOUSE_NAME), SUM(NR_CHILDREN)

FROM PRESIDENT, PRES_MARRIAGE

WHERE

PRESIDENT.PRES_NAME = PRES_MARRIAGE.PRES_NAME

GROUP BY PRESIDENT.PRES_NAME

ORDER BY PRESIDENT.PRES_NAME

A table may be joined to itself. This is useful for queries involving the 'nesting' of conditions within

the same table. In the following query, one requests the names of the presidents (and their birth states)

who were born in the same state as General Grant. To accomplish this type of query, one gives the

PRESIDENT table two temporary names (called labels) - A & B. Then tables A & B are joined to

each other based on the criteria contained below in the query. The type of join is known as a self-join.

SELECT B.PRES_NAME, B.STATE_BORN

FROM PRESIDENT A, PRESIDENT B

WHERE A.STATE_BORN = B.STATE_BORN

AND A.PRES_NAME = 'Grant U S'

Query Nesting

The above result could also have been accomplished by executing what is known as a 'nested query'.

In this type of statement, the results of one query (called a subselect) are used to drive another query.

The example below illustrates a nested query to find all the presidents who were born in the same

state as General Grant.

SELECT PRES_NAME, STATE_BORN

FROM PRESIDENT

WHERE STATE_BORN =

(SELECT STATE_BORN

FROM PRESIDENT

WHERE PRES_NAME = 'Grant U S')

Nested queries can be used with the IN predicate also. That is, they maybe used to compare a value to

a collection of values returned from the inner query. In the following example, the names of those

presidents are requested who were born in any of the states in which either General Grant or President

Lincoln were born.

SELECT PRES_NAME, STATE_BORN

FROM PRESIDENT

WHERE STATE_BORN IN

(SELECT STATE_BORN

FROM PRESIDENT

WHERE PRES_NAME IN ('Grant U S', 'Lincoln A'))

Nested Queries can be used to construct even more complex database requests. For example, the

following query requests the names of those candidates in each election year who obtained greater

than the average number of electoral college votes cast per candidate. This type of query is called a

correlated subquery because the inner query is executed once for each row of the outer query (ie. for

each candidate of each election year). This is a fairly complex query and may take sometime.

Note the use of the correlation-name X and its similarity to the temporary table name given to tables

in a self-join.

SELECT ELECTION_YEAR, CANDIDATE, VOTES

FROM ELECTION X

WHERE VOTES >

(SELECT AVG(VOTES)

FROM ELECTION

WHERE ELECTION_YEAR = X.ELECTION_YEAR)

Joins and Table Create via Insert

Nested queries can also be used to select rows from one table and insert them into another. The

following statement creates a temporary table called WINNER that has three columns:

the president's NAME

the YEAR he was elected in and

the VOTES he attained from the electoral college.

The INSERT statement following the table creation causes the names of all the candidates from the

election table who have a winner_loser_indic of W to be inserted into the WINNER table.

CREATE TABLE WINNER

(NAME CHAR(20) NOT NULL,

THEYEAR SMALLINT,

VOTES SMALLINT)

INSERT INTO WINNER

SELECT CANDIDATE, ELECTION_YEAR, VOTES

FROM ELECTION

WHERE WINNER_LOSER_INDIC = 'W'

You may have noticed that the above table doesn't necessarily contain all US presidents. For example,

Gerald Ford was not elected to the office but attained it as a result of the rules of presidential

succession. In order to find the presidents who were not elected to office, one must use a correlated

subquery but this time with an EXISTS predicate. The latter may only be used in a subquery. It does

not return any values. Instead it returns to the outer query a binary YES/NO response for the inner

query.

SELECT PRES_NAME

FROM PRESIDENT X

WHERE NOT EXISTS

(SELECT *

FROM WINNER

WHERE NAME = X.PRES_NAME)

Sometimes it is useful to query two tables and merge the rows that are returned into one set of results.

This merging is called a UNION and a special operator exists in SQL to accomplish it. In the

following query, one requests to see the names of all presidents who were elected to office by popular

vote and who were born in the state of Nebraska or Illinois. This involves a UNION of two queries,

one on the WINNER table, and the other on the PRESIDENT table.

SELECT NAME

FROM WINNER

UNION

SELECT PRES_NAME

FROM PRESIDENT

WHERE STATE_BORN IN ('Nebraska','Illinois')

The above query will display all of the winners and the name of Gerald Ford who was born in

Nebraska. Notice also that all duplicate rows are automatically suppressed. UNION also requires that

both queries have the same number of columns in the select list and that they be identical in data type.

Views

You may have noticed that the results of all SQL queries, regardless of complexity, are always

presented as a table of data. Sometimes, it is desirable to define the results of a query to be a VIEW.

By giving a name to the result table of a query, this subsequent VIEW can then be treated as if it were

a table. You can retrieve data from it without having to specify all the search criteria that went into

the original query.

The following statement creates a view called WINNER_VIEW.

CREATE VIEW WINNER_VIEW

(NAME,

THEYEAR,

VOTES)

AS

SELECT CANDIDATE, ELECTION_YEAR, VOTES

FROM ELECTION

WHERE WINNER_LOSER_INDIC = 'W'

Note that the above view contains the same results as the WINNER table. However, the only

difference between them is that while the latter is defined as physical table in the database, and

contains real data, WINNER_VIEW does not actually exist in the database except as a name assigned

to a query. When the view is invoked in a subsequent query, the underlying query that comprises

WINNER_VIEW is executed, and the resultant table is used to retrieve the desired data. In the

following example, we select the name of all the presidents who were not elected. Instead of using the

WINNER table, we use the the WINNER_VIEW view.

SELECT PRES_NAME

FROM PRESIDENT X

WHERE NOT EXISTS

(SELECT *

FROM WINNER_VIEW

WHERE NAME = X.PRES_NAME)

VIEWS can be used to perform queries that simply cannot be done with one SQL statement alone.

For example, suppose we wish to know the name of each winner in a presidential election, the number

of votes he got, and the percentage of total votes obtained by him in the election. The following two

SQL statements will provide the results desired.

CREATE VIEW VOTES_TOTAL

(THEYEAR,

TOTAL)

AS

SELECT ELECTION_YEAR, SUM(VOTES)

FROM ELECTION

GROUP BY ELECTION_YEAR

SELECT * FROM VOTES_TOTAL

The above view contains the total votes cast in each election. We now join

it to the ELECTION table.

SELECT CANDIDATE, VOTES, VOTES/TOTAL

FROM ELECTION, VOTES_TOTAL

WHERE ELECTION.ELECTION_YEAR = VOTES_TOTAL.THEYEAR AND

ELECTION.WINNER_LOSER_INDIC = 'W'

VIEWS can also be updated, deleted from or inserted into, as long as they contain the name of only

one table in the FROM clause, and do not contain any derived columns (based on group by functions

or arithmetic expressions). The following view simply contains all the rows and columns in the

PRESIDENT table and is updatable.

CREATE VIEW PRES_VIEW

AS

SELECT * FROM PRESIDENT

Integrity Checking

PRES_VIEW could also have been specified as updatable with a CHECK option. This means that if

an UPDATE, DELETE or INSERT operation is performed on the view, the system will check to see

that the view definition is not violated as a result of this operation. The USE of the CHECK option on

Views has significant ramifications for implementing what are known in database terminology as

Integrity Constraints. These can be best illustrated by means of an example.

Let us first create a table called STATES.

CREATE TABLE STATES

(STATE CHAR(20))

We will insert the names of all the 50 states in the union.

INSERT INTO STATES VALUES ('Alabama')

INSERT INTO STATES VALUES ('Florida')

INSERT INTO STATES VALUES ('Louisiana')

INSERT INTO STATES VALUES ('Nevada')

INSERT INTO STATES VALUES ('Oregon')

INSERT INTO STATES VALUES ('Virginia')

INSERT INTO STATES VALUES ('Alaska')

INSERT INTO STATES VALUES ('Georgia')

INSERT INTO STATES VALUES ('Maryland')

INSERT INTO STATES VALUES ('New Hampshire')

INSERT INTO STATES VALUES ('Pennsylvania')

INSERT INTO STATES VALUES ('Washington')

INSERT INTO STATES VALUES ('Arizona')

INSERT INTO STATES VALUES ('Hawaii')

INSERT INTO STATES VALUES ('Massachusetts')

INSERT INTO STATES VALUES ('New Jersey')

INSERT INTO STATES VALUES ('Rhode Island')

INSERT INTO STATES VALUES ('West Virginia')

INSERT INTO STATES VALUES ('Arkansas')

INSERT INTO STATES VALUES ('Idaho')

INSERT INTO STATES VALUES ('Michigan')

INSERT INTO STATES VALUES ('New Mexico')

INSERT INTO STATES VALUES ('South Carolina')

INSERT INTO STATES VALUES ('Wisconsin')

INSERT INTO STATES VALUES ('California')

INSERT INTO STATES VALUES ('Illinois')

INSERT INTO STATES VALUES ('Minnesota')

INSERT INTO STATES VALUES ('New York')

INSERT INTO STATES VALUES ('South Dakota')

INSERT INTO STATES VALUES ('Wyoming')

INSERT INTO STATES VALUES ('Colorado')

INSERT INTO STATES VALUES ('Indiana')

INSERT INTO STATES VALUES ('Mississippi')

INSERT INTO STATES VALUES ('North Carolina')

INSERT INTO STATES VALUES ('Tennessee')

INSERT INTO STATES VALUES ('Connecticut')

INSERT INTO STATES VALUES ('Iowa')

INSERT INTO STATES VALUES ('Missouri')

INSERT INTO STATES VALUES ('North Dakota')

INSERT INTO STATES VALUES ('Texas')

INSERT INTO STATES VALUES ('Delaware')

INSERT INTO STATES VALUES ('Kansas')

INSERT INTO STATES VALUES ('Montana')

INSERT INTO STATES VALUES ('Ohio')

INSERT INTO STATES VALUES ('Utah')

INSERT INTO STATES VALUES ('D.C.')

INSERT INTO STATES VALUES ('Kentucky')

INSERT INTO STATES VALUES ('Nebraska')

INSERT INTO STATES VALUES ('Oklahoma')

INSERT INTO STATES VALUES ('Vermont')

We will confirm that all 50 states are present.

SELECT COUNT(DISTINCT STATE) FROM STATES

We will then drop PRES_VIEW.

DROP VIEW PRES_VIEW

We can now redefine it to include an integrity constraint specifying that no presidents in that view

can be from non-existent states ie. the STATE_BORN column in PRES_VIEW must exist in the STATES table.

PRES_VIEW will be defined with a CHECK option.

CREATE VIEW PRES_VIEW

AS

SELECT *

FROM PRESIDENT

WHERE STATE_BORN IN

(SELECT STATE

FROM STATES)

WITH CHECK OPTION

In the following SQL statement, since PRES_VIEW is identical to the PRESIDENT table in terms of

its contents, inserting a record into PRES_VIEW is the same as inserting it into PRES_VIEW. Since

PRES_VIEW is identical to the PRESIDENT table in terms of its contents, inserting a record into

PRES_VIEW is the same as inserting it into PRESIDENT about with one added benefit. The

integrity constraint that ensures the validity of STATE_BORN is enforced during any data

manipulation operation. This type of integrity checking is a form of referential integrity.

INSERT INTO PRES_VIEW (PRES_NAME, STATE_BORN)

VALUES ('Ferdinand Marcos','Philippines')

As you can see, VIEWS can be a powerful means to perform queries that otherwise might require significant amounts of programming. The benefit they have over creating temporary tables is that they are always up-to-date. They do not require storage of redundant data and are automatically maintained current by SQL SERVER. BY using the CHECK option on updatable views, they can be used to enforce a wide variety of integrity constraints including a form of limited referential integrity. They do, however, have performance implications since the SQL must be executed and the tables created at run time.

Later in this tutorial, you will also see how views can be used to implement field and row level security

within a table by assigning access privileges on views. Now we will consider how true referential integrity

is implemented.

Referential Integrity and Primary and Foreign Key Definition

In the table definition of the winner table which has been shown above there is no single column value which can uniquely identify any particular row of the table. It do not have the property of being a primary key. In the president the surname does not have this property since there were two presidents named 'Roosevelt'. While the full name does have this property, it has not been defined as such because, in the future, there may be a further president called 'Ronald Reagan',

Below, the table definition of the state table has been changed to support a primary key. The primary key is usually a means for referring to a single state in a shorthand fashion eg. a code or a number. The standard shorthand abbreviation has been added to act as the primary key. First we drop the existing table definition.

DROP TABLE STATES

Then the new table definition is submitted.

CREATE TABLE STATES

(S_AB CHAR (2) PRIMARY KEY,

STATE CHAR(20))

We can now insert the abbreviations and the full names of all the 50 states in the union. Note that we have arranged that the final entry is purposefully in error in order to show the fact that we cannot now add a duplicate abbreviation for a state. First we add the 49 correct entries.

INSERT INTO STATES VALUES ('AL','Alabama')

INSERT INTO STATES VALUES ('FL','Florida')

INSERT INTO STATES VALUES ('LA','Louisiana')

INSERT INTO STATES VALUES ('NV','Nevada')

INSERT INTO STATES VALUES ('OR','Oregon')

INSERT INTO STATES VALUES ('VA','Virginia')

INSERT INTO STATES VALUES ('AK','Alaska')

INSERT INTO STATES VALUES ('GA','Georgia')

INSERT INTO STATES VALUES ('MD','Maryland')

INSERT INTO STATES VALUES ('NH','New Hampshire')

INSERT INTO STATES VALUES ('PA','Pennsylvania')

INSERT INTO STATES VALUES ('WA','Washington')

INSERT INTO STATES VALUES ('AZ','Arizona')

INSERT INTO STATES VALUES ('HI','Hawaii')

INSERT INTO STATES VALUES ('MA','Massachusetts')

INSERT INTO STATES VALUES ('NJ','New Jersey')

INSERT INTO STATES VALUES ('RI','Rhode Island')

INSERT INTO STATES VALUES ('WV','West Virginia')

INSERT INTO STATES VALUES ('AR','Arkansas')

INSERT INTO STATES VALUES ('ID','Idaho')

INSERT INTO STATES VALUES ('MI','Michigan')

INSERT INTO STATES VALUES ('NM','New Mexico')

INSERT INTO STATES VALUES ('SC','South Carolina')

INSERT INTO STATES VALUES ('WI','Wisconsin')

INSERT INTO STATES VALUES ('CA','California')

INSERT INTO STATES VALUES ('IL','Illinois')

INSERT INTO STATES VALUES ('MN','Minnesota')

INSERT INTO STATES VALUES ('NY','New York')

INSERT INTO STATES VALUES ('SD','South Dakota')

INSERT INTO STATES VALUES ('WY','Wyoming')

INSERT INTO STATES VALUES ('CO','Colorado')

INSERT INTO STATES VALUES ('IN','Indiana')

INSERT INTO STATES VALUES ('MS','Mississippi')

INSERT INTO STATES VALUES ('NC','North Carolina')

INSERT INTO STATES VALUES ('TN','Tennessee')

INSERT INTO STATES VALUES ('CT','Connecticut')

INSERT INTO STATES VALUES ('IA','Iowa')

INSERT INTO STATES VALUES ('MO','Missouri')

INSERT INTO STATES VALUES ('ND','North Dakota')

INSERT INTO STATES VALUES ('TX','Texas')

INSERT INTO STATES VALUES ('DE','Delaware')

INSERT INTO STATES VALUES ('KS','Kansas')

INSERT INTO STATES VALUES ('MT','Montana')

INSERT INTO STATES VALUES ('OH','Ohio')

INSERT INTO STATES VALUES ('UT','Utah')

INSERT INTO STATES VALUES ('DC','D.C.')

INSERT INTO STATES VALUES ('KY','Kentucky')

INSERT INTO STATES VALUES ('NE','Nebraska')

INSERT INTO STATES VALUES ('OK','Oklahoma')

Then we add the duplicated abbreviation 'AL' for 'Vermont' rather than 'VT'.

INSERT INTO STATES VALUES ('AL','Vermont')

This gives us the 'Violation of PRIMARY KEY constraint' message which notifies us of an attempt to insert a duplicate key in the table object 'STATES'. We shall now correct this.

INSERT INTO STATES VALUES ('VT','Vermont')

We can now build or re-build other tables to reference this primary key value as a means of linking the data in the two tables or maintaining integrity between two tables. Below we have defined a city table and used the state abbreviation as the foreign key so as to identify in which state a city is located. This is done using the REFERENCES constraint.

CREATE TABLE CITIES

(CITY_NAME CHAR (20),

S_AB CHAR (2) REFERENCES STATES(S_AB))

We can now populate the table.

INSERT INTO CITIES VALUES ('Gary','IN')

INSERT INTO CITIES VALUES ('Indianapolis','IN')

INSERT INTO CITIES VALUES ('Atlanta','GA')

INSERT INTO CITIES VALUES ('Salem','MA')

INSERT INTO CITIES VALUES ('Boston','MA')

INSERT INTO CITIES VALUES ('Reno','NV')

INSERT INTO CITIES VALUES ('Carson City','NV')

INSERT INTO CITIES VALUES ('Houston','TX')

INSERT INTO CITIES VALUES ('Austin','TX')

INSERT INTO CITIES VALUES ('Dallas','TX')

INSERT INTO CITIES VALUES ('Los Angeles','CA')

INSERT INTO CITIES VALUES ('San Francisco','CA')

INSERT INTO CITIES VALUES ('Sacramento','CA')

INSERT INTO CITIES VALUES ('Santa Barbara','CA')

INSERT INTO CITIES VALUES ('Montgomery','AL')

INSERT INTO CITIES VALUES ('Honolulu','HI')

INSERT INTO CITIES VALUES ('Chicago','MI')

Once again, to show integrity support, an insert which references an invalid state abbreviation is included. 'Lansing', the capital of Michigan, has been shown as located in a state identified by 'MX'

INSERT INTO CITIES VALUES ('Lansing','MX')

This conflict with the FOREIGN KEY constraint is reported by the error message and it is corrected by referring to a valid state definition 'MI'.

INSERT INTO CITIES VALUES ('Lansing','MI').

One can now list the city names and link them to the full name of the states in which they are located.

SELECT CITY_NAME, STATE

FROM CITIES, STATES

WHERE CITIES.S_AB = STATES.S_AB

The TEXT datatype

Now let us demonstrate the use of the TEXT datatype. In order to do so, we must first create a table

called BIOGRAPHY. The first column in the table is called PRES_NAME and the second is the B_TEXT column. B_TEXT is defined as a TEXT column.

CREATE TABLE BIOGRAPHY

(PRES_NAME VARCHAR(20),

B_TEXT TEXT)

To insert data into a TEXT column, you use the normal INSERT command. The following inserts the name 'Reagan R' into the PRES_NAME column, and then a short biography into the B_TEXT column. Note that you can use single quotes (') if the text does not contain an apostrophe and double quotes (") if it does.The latter convention is used since reference is made to the Screen Actor's guild in paragraph two and the party's 1976 presidential nomination in paragraph 3.

INSERT INTO BIOGRAPHY

VALUES

('Reagan R'," Ronald Wilson Reagan, 40th president, Republican, was born Feb.6,1911,in Tapico, Ill.,

the son of John Edward Reagan and Nellie Wilson. Reagan graduated from Eureka (Ill) Cllege in 1932.

Following his graduation, he worked for 5 years as a sports announce in Des Moines, Iowa.

Reagan began a successful career as a film actor in 1937, and starred in many movies, and

later television, until the 1960s. He was a captain in the Army Air Force during World War II. He

served as President of the Screen Actor's guild from 1947 to 1952, and in 1959.

Once a liberal democrat, Reagan became active in Republican politics during the 1964

presidential camapaign of Barry Goldwater. He was elected governor of California in 1966, and

reelected in 1970. Following his retirement as governor, Reagan became the leading spokesman for

the conservative wing of the Republican Party, and made a strong bid for the party's 1976 presidential

nomination.

In 1980 he gained the Republican nomination and won a landslide victory over Jimmy

Carter. As president, he successfully forged a bipartisan coalition in Congress which led to enactment

of an economic program which included the largest budget and tax cuts in U.S. history, and a Social

Security reform bill designed to ensure the longterm solvency of the system.

He was reelected by an overwhelming margin in the 1984 presidential elections. His

opponent, Walter Mondale managed to carry only his home state of Minnesota while Reagan won all

the other 49 states.")

The following query displays the name and partial contents (255 bytes) of the text of the BIOGRAPHY table.

SELECT PRES_NAME, B_TEXT

FROM BIOGRAPHY

The block of text is stored at a location indicated by a pointer held in the B_TEXT column location. The

contents of the pointer can be displayed using the function TEXTPTR. This command displays the pointer.

SELECT PRES_NAME, TEXTPTR(B_TEXT)

FROM BIOGRAPHY

To view all or part of the column one must define a variable to hold the pointer and use the READTEXT command. There is also an UPDATETEXT to modify a text field and a WRITETEXT to completely re-write the field. See the Transact-SQL manual or help for details.

Data Amendment using UPDATE and DELETE

Until now, we have demonstrated the creation of tables and indices (CREATE), data entry (INSERT) and data query (SELECT) and UPDATE has only been mentioned briefly in passing. Now we look at examples of data modification using the commands UPDATE and DELETE.

The following command updates the PRESIDENT table and changes the name 'Carter J E' to 'Carter Jimmy E'

UPDATE PRESIDENT

SET PRES_NAME = 'Carter Jimmy E'

WHERE PRES_NAME = 'Carter J E'

Note the use of the WHERE clause in an UPDATE statment. Any WHERE clause that can be

constructed for a single table SELECT statement can be used in an UPDATE statement. If no

WHERE clause is specified, then all the rows of the table that contain data in the specified column are

updated. An UPDATE can only be applied to a single table. The SET clause in an UPDATE

statement may contain constants, column names or arithmetic expressions. More than one column

may be updated in a single SET clause. The following command confirms the update.

SELECT PRES_NAME

FROM PRESIDENT

WHERE PRES_NAME LIKE '%Carter%'

To delete an entire row from a table, use the DELETE command. In the following example, the entry

for 'Carter Jimmy' in the BIOGRAPHY table is deleted.

DELETE

FROM PRESIDENT

WHERE PRES_NAME = 'Carter Jimmy E'

replik breitling polo ralph lauren wholesale rolex replicas swiss made replica audemars piguet omega replica watches replica watches breitling replica watches hublot replica watches tag heuer audemars piguet replica watches tag heuer replica watches ralph lauren polo outlet online polo ralph lauren sale ralph lauren outlet store cheap ralph lauren shirts ralph lauren style cheap polo ralph lauren shirts ralph lauren outlet online ralph lauren outlet online ralph lauren t shirt ralph lauren eyewear ralph lauren leather jacket ralph lauren long sleeve polo ralph lauren leather jacket one shoulder cocktail dresses taffeta bridesmaid dresses plus size evening dress polo ralph lauren wholesale

If no WHERE clause is specified in a DELETE or UPDATE statment, all rows in table are deleted or

updated! The following statement deletes all rows from the BIOGRAPHY table.

DELETE

FROM BIOGRAPHY

This is confirmed with the following command.

SELECT *

FROM BIOGRAPHY

To completely remove a table from the database, you must use the DROP TABLE command. Even

though we have deleted all the rows in the BIOGRAPHY table, the database still contains an entry for

this table.

SELECT * FROM SYSOBJECTS WHERE NAME = 'BIOGRAPHY'

The following command removes the entry for this table from the database.

DROP TABLE BIOGRAPHY

We can now confirm this by repeating the star select of SYSOBJECTS.

SELECT * FROM SYSOBJECTS WHERE NAME = 'BIOGRAPHY'

THE MSS-TUTL TUTORIAL - PART 5

The next section of the tutorial assumes that, apart from the biography table, that all the major tables,

views and indices have been created and are populated. It assumes, therefore, that parts 1 through 4 of the tutorial have been run and that no tables have been dropped at any point. The SYS structures which are explained below provide an indication of whether this is the case or not.

The SYS Structures

SQL SERVER keeps track of information regarding all the tables in the database and their associated

columns and indices. This information comprises what is known as the data dictionary or meta-database ie.a database about the database.

The SQL SERVER data dictionary is built up aroound and includes the following internal tables:

systypes

sysprocedures

syscomments

syssegments

syslogs

sysprotects

sysusers

sysalternates

sysdepends

syskeys

sysreferences

sysconstraints.

These are documented and defined in the following structures:

SYSOBJECTS contains a list of all the system and user tables, views and other database objects

SYSCOLUMNS contains a list of all columns, their associated tables, column numbers and datatypes

SYSINDEXES contains a list of all indexes in the database and their associated tables

SYSKEYS contains a list of all primary keys and their associated columns

SYSREFERENCES contains a list of all foreign key references and their associated columns

SYSCOMMENTS contains the text of all the views in the database.

As demonstrated above, these tables may be queried using SQL commands just like any user defined table in the database. Other dictionary tables exist in SQL SERVER that contain information about datatypes, users and their access privileges, procedures and constraints. The Transact-SQL Reference contains data about all the SYS structure tables, their columns and their definition. The sample commands below display a sample of the range of data which can be retrieved and displayed. The first displays the objects.

SELECT NAME, ID, TYPE, CRDATE

FROM SYSOBJECTS

The next command displays, somewhat esoterically, the details of the user defined columns. This includes the name, the id of the table to which it belongs, the column position in the table, the code indicating the datatype of the column and the length of the column in bytes.

SELECT NAME, ID, COLID, USERTYPE, LENGTH

FROM SYSCOLUMNS

WHERE ID > 18

Using a somewhat long-winded select then one can join table values and make this a little less cryptic.

SELECT SYSCOLUMNS.NAME, SYSOBJECTS.ID, SYSOBJECTS.NAME, COLID,

SYSCOLUMNS.USERTYPE, SYSTYPES.NAME,SYSCOLUMNS.LENGTH

FROM SYSCOLUMNS, SYSOBJECTS, SYSTYPES

WHERE SYSCOLUMNS.ID > 18 AND

SYSCOLUMNS.ID = SYSOBJECTS.ID AND

SYSCOLUMNS.USERTYPE = SYSTYPES.USERTYPE

Even more cryptic is the SYSINDEXES structure. This contains valuable information on the composition of both tables and indexes. It defines what indexes exist, their type and the tables to which they relate. The full content is shown even though it means relatively little at this stage.

SELECT *

FROM SYSINDEXES

WHERE ID > 18

The views that exist in a database are listed in the SYSOBJECTS table and designated as type = 'V'.

SELECT NAME, ID, TYPE, CRDATE

FROM SYSOBJECTS

WHERE TYPE = 'V'

The text associated with a view is contained in the SYSCOMMENTS table.

SELECT * FROM SYSCOMMENTS

Dynamic Maintenance of the SYS structures

Data in the dictionary tables is updated by SQL SERVER whenever appropriate. The following sequence of SQL commands demonstrates this automatic dictionary maintenance facility of SQL SERVER. Let us drop the index X_PRES_NAME on PRES_NAME in the PRESIDENT table.

DROP INDEX X_PRES_NAME 

Now, if we query the SYSINDEXES table, we will see that no indices exist for the PRESIDENT table.

SELECT *

FROM SYSINDEXES

WHERE ID > 18

You can alter the structure of a table by adding or dropping columns. The following command adds a

column called B_TEXT to the PRESIDENT table. Perhaps this column could be used to contain the text

we previously stored in the BIOGRAPHY table. Note that this must be defined as a NULL column.

ALTER TABLE PRESIDENT

ADD BIO_DATA TEXT NULL

Note that the data dictionary will automatically reflect this additional column.

SELECT SYSCOLUMNS.NAME, SYSOBJECTS.ID, SYSOBJECTS.NAME, COLID,

SYSCOLUMNS.USERTYPE, SYSTYPES.NAME,SYSCOLUMNS.LENGTH

FROM SYSCOLUMNS, SYSOBJECTS, SYSTYPES

WHERE SYSOBJECTS.NAME = 'PRESIDENT' AND

SYSCOLUMNS.ID = SYSOBJECTS.ID AND

SYSCOLUMNS.USERTYPE = SYSTYPES.USERTYPE

More than one column can be added with a single ALTER TABLE statement.

ALTER TABLE PRESIDENT

ADD NEWCOL1 VARCHAR(30) NULL,

NEWCOL2 INTEGER NULL

Columns can be added to any of the dictionary tables too. Thus if you'd like to store information in

the data dictionary that is not maintained by SQL SERVER, you may do so by adding the appropriate

columns to the relevant dictionary table. The following command adds the column 'REMARKS' column to the SYSTABLES table. It could be used to store comments or remarks regarding the deinition of the specified table ie. the description of what the purpose of the table might be. Thus the comment on the table PRESIDENT might be that this stores data concerning all American Presidents. It could be used to provide help to new system user.

ALTER TABLE SYSOBJECTS

ADD REMARKS VARCHAR(50) NULL

The presence of this can be confirmed using the command which was constructed above..

SELECT SYSCOLUMNS.NAME, SYSOBJECTS.ID, SYSOBJECTS.NAME, COLID,

SYSCOLUMNS.USERTYPE, SYSTYPES.NAME,SYSCOLUMNS.LENGTH

FROM SYSCOLUMNS, SYSOBJECTS, SYSTYPES

WHERE SYSOBJECTS.NAME = 'SYSOBJECTS' AND

SYSCOLUMNS.ID = SYSOBJECTS.ID AND

SYSCOLUMNS.USERTYPE = SYSTYPES.USERTYPE

After update it could then reported upon. Note, for convenience, this has not been updated.

SELECT NAME, REMARKS

FROM SYSOBJECTS

WHERE NAME = 'PRESIDENT'

Similar remarks fields could be added concerning column definitions.

ALTER TABLE SYSCOLUMNS

ADD REMARKS VARCHAR(50) NULL

This could then be reported upon using the following command.

SELECT SYSOBJECTS.NAME, SYSCOLUMNS.NAME, SYSCOLUMNS.REMARKS

FROM SYSCOLUMNS, SYSOBJECTS

WHERE SYSOBJECTS.NAME = 'PRESIDENT'

AND SYSCOLUMNS.NAME = 'DEATH_AGE'

AND SYSCOLUMNS.ID = SYSOBJECTS.ID

This is only an introduction to the information which is held in the SYS structures. The Transact-SQL

manual contains data on their content and structure.

Columns cannot be dropped or renamed via the ALTER table command. This is a serious restriction but it offers performance advantages and can be worked around by unloading data from the database, dropping and then redefining the table, column or index names and then, finally, reloading the data to the modified table.

Recovery with BEGIN, COMMIT and ROLLBACK TRANSACTION

The default mode of ISQL is that the SQL statments are committed to the database after it is executed.

However, you may set a mode in which the SQL will only be committed when explicit instructions are issued.

We will now demonstrate this very key feature of SQL SERVER---its capability for transaction level

rollback and recovery. A transaction is any logical unit of work. In SQL SERVER terms, it may

consist of an implicit single SQL command or an explicitly defined logical group of SQL commands.

The explicitly defined transaction starts when one issues the BEGIN TRANSACTION statement and terminates when one issues the COMMIT TRANSACTION statement. In the event of a failure during the transaction, then the effects of the intermediate changes can be elimated with the ROLLBACK TRANSACTION.

At this point, all changes to the database made since the tutorial session started have been

committed to disk. In the event the computer goes down or you shut it off, SQL SERVER guarantees

you that the data you have entered or modified since the start of the session will not be affected by this

shutdown. The data has been written to the disk and the log file. Now, let us consider the possibility that

for the election table, we wish to introduce a check that the total votes cast totals 538. This can only be done via inputting the data via two rows. There is a need for one logical transaction comprising two SQL statements and the start of this logical transaction is marked by the BEGIN TRANSACTION.

BEGIN TRANSACTION

Now execute the two INSERT commands

INSERT INTO ELECTION VALUES (1988,'Bush G', 370,'W')

INSERT INTO ELECTION VALUES (1988,'Dukakis G',158, 'L')

Check whether the value of the VOTES cast is 538.

SELECT *

FROM ELECTION

WHERE ELECTION_YEAR = 1988

SELECT SUM(VOTES)

FROM ELECTION

WHERE ELECTION_YEAR = 1988

The values input total to 528 so the insert data should be corrected and the effects of the two inserts will

need to be eliminated. The database at this point is still capable of being "rolled back" to the state it

was in at the time of the BEGIN TRANSACTION. This can be demonstrated by issuing the ROLLBACK TRANSACTION.

ROLLBACK TRANSACTION

If you NOW query the database, you will see that it no longer reflects the changes which were made above.

SELECT *

FROM ELECTION

WHERE ELECTION_YEAR = 1988

Correct operation results in the following sequence of statements.

BEGIN TRANSACTION

INSERT INTO ELECTION VALUES (1988,'Bush G', 370,'W')

INSERT INTO ELECTION VALUES (1988,'Dukakis G',168, 'L')

SELECT *

FROM ELECTION

WHERE ELECTION_YEAR = 1988

SELECT SUM(VOTES)

FROM ELECTION

WHERE ELECTION_YEAR = 1988

COMMIT TRANSACTION

In the previous sequence of statements, by issuing a ROLLBACK TRANSACTION one effectively undoes the SQL commands that updated the tables and restored the database to a state as if those statements had never been executed. This occurs automatically in the event of unanticipated system failure. An abnormal exit from SQL SERVER will also cause a rollback to occur.

Stored Procedures

There are two phases involved in the processing of a SQL statement. The first involves statement syntax checking and the determination and compilation of the optimal access plan. The second involves the execution of the compiled statement. An important feature of SQL SERVER is that it allows you to store and retrieve what are called stored procedures and which represent a 'precompiled' query macro. This not only represents an advantage in so far as it enables the ability to store and re-use complex queries with minimal typing, but a saving on the overall execution time. There is no need to repeat the first phase statement syntax checking and the determination and compilation of the optimal access plan.

The CREATE PROCEDURE and EXECUTE are commands used extensively in TRANSACT SQL. There is an extensive inbuilt library of some 200 or more for the reasons outlined above. You should, however, be aware that difficulties have been encountered with these pre-compiled commands since table structure can change and effect the ability of the command. The RECOMPILE options can overcome these difficulties.

These facilities provide SQL with dynamic capabilities to provide variables at run time and procedural

capabilities to string together a logical sequence of SQL statements. They provide an intermediate

step to the full use of a high level language interface.

The first option of such procedures is to provide a short hand facility for a long winded command. The

procedure 'table_columns' which was used to list the columns of SYSOBJECTS demonstrates this capability.

CREATE PROCEDURE TABLE_COLUMNS

AS

SELECT SYSCOLUMNS.NAME, SYSOBJECTS.ID, SYSOBJECTS.NAME, COLID,

SYSCOLUMNS.USERTYPE, SYSTYPES.NAME,SYSCOLUMNS.LENGTH

FROM SYSCOLUMNS, SYSOBJECTS, SYSTYPES

WHERE SYSOBJECTS.NAME = 'SYSOBJECTS' AND

SYSCOLUMNS.ID = SYSOBJECTS.ID AND

SYSCOLUMNS.USERTYPE = SYSTYPES.USERTYPE

It can be executed with the following command.

EXECUTE TABLE_COLUMNS

The second option which these procedures provide is to enter parametera dynamically at run-time. Before we do this, however, the existing procedure must be dropped.

DROP PROCEDURE TABLE_COLUMNS

Within the create procedure variables are defined and replace the constant values in the SELECT statements. The procedure can then be redefined and used for displaying the contents of any table.

CREATE PROCEDURE TABLE_COLUMNS

@OBJNAME VARCHAR (30)

AS

SELECT SYSCOLUMNS.NAME, SYSOBJECTS.ID, SYSOBJECTS.NAME, COLID,

SYSCOLUMNS.USERTYPE, SYSTYPES.NAME,SYSCOLUMNS.LENGTH

FROM SYSCOLUMNS, SYSOBJECTS, SYSTYPES

WHERE SYSOBJECTS.NAME = @objname AND

SYSCOLUMNS.ID = SYSOBJECTS.ID AND

SYSCOLUMNS.USERTYPE = SYSTYPES.USERTYPE

We can now issue the execute command for the generalised procedure with the run time parameter.

EXECUTE TABLE_COLUMNS 'PRESIDENT'

Using this we can create a generalised update facility which can also report the updated values.

DROP PROCEDURE UPD_PRES_D_AGE

CREATE PROCEDURE UPD_PRES_D_AGE

@DEATH_AGE INTEGER,

@PRES_NAME VARCHAR (20)

AS

UPDATE PRESIDENT

SET DEATH_AGE = @DEATH_AGE

WHERE PRES_NAME = @PRES_NAME

SELECT DEATH_AGE, PRES_NAME

FROM PRESIDENT

WHERE PRES_NAME = @PRES_NAME

The above command is now stored in the database. It may now be executed for specific values of

DEATH_AGE and PRES_NAME.

EXECUTE UPD_PRES_D_AGE 99,'REAGAN R'

The above feature means that SQL commands that are used often in a program may be compiled and

stored in a denser representation than in their text form, and executed with a different bind variable

each time. Executing stored commands is faster than compiling and executing them each time,

because parsing is done only once, ie. at precompilation time.

Cursors

We will now illustrate the use of multiple cursors within a database. SQL is referred to as a set

oriented language since it will return a 'set' of matching rows in response to a particular SELECT.

In the following SQL statement, four rows are returned which match the required criteria.

SELECT *

FROM ELECTION

WHERE CANDIDATE = 'ROOSEVELT F D'

If we wish, we can further refine our selection and retrieve a single row ie. a singleton can be

retrieved if we know the election year. However,this is not always possible since not all tables

are defined with a unique primary key. or a unique set of column values and, moreover, it is not

always possible to use the unique primary key as the basis for the 'where' selection criteria.

Cursors provide an alternative, operational means for selecting the singleton.

The cursor is defined using the cursor declare statement for a particular query. This defines

the cursor and precompiles the command.

DECLARE my_cursor SCROLL CURSOR

FOR

SELECT *

FROM ELECTION

WHERE CANDIDATE = 'ROOSEVELT F D'

The cursor is then opened. The command OPEN retrieves a results table ie. a table of four candidate rows.

OPEN my_cursor

We can then issue a series of commands to navigate this results table one row at a time. The fetch

next and prior navigates the rows in turn from head to tail or from tail to head respectively.

FETCH NEXT FROM my_cursor

The fetch first and last goes to the head and tail of the table resectively.

FETCH FIRST FROM my_cursor

The fetch absolute n goes to the nth row in the table and the fetch relative n moves n rows from the

current row. The n in both of the command can be negative and in this case the fetch moves backwards

n rows from the last row in the table or n rows backwards relative to the current row.

FETCH ABSOLUTE 3 FROM my_cursor

The cursor can then be freed with the deallocate command.

DEALLOCATE my_cursor

As well as using the cursor for browsing, read only purposes, the cursor can also be defined for update

of particular columns. The cursor must be opened and the required row fetched using fetch next only.

DECLARE my_cursor CURSOR

FOR

SELECT *

FROM ELECTION

WHERE CANDIDATE = 'ROOSEVELT F D'

FOR UPDATE

OPEN my_cursor

FETCH NEXT FROM my_cursor

FETCH NEXT FROM my_cursor

Having ascertained that we have retrieved the correct row, it is now possible to update or delete

that row of the table.

UPDATE ELECTION

SET VOTES = 999

WHERE CURRENT OF my_cursor

We can now now query our table to see whether the change has been effected.

SELECT *

FROM ELECTION

WHERE CANDIDATE = 'ROOSEVELT F D'

We can also delete that row using the following command.

DELETE FROM ELECTION

WHERE CURRENT OF my_cursor

The cursor can now be deallocated.

DEALLOCATE my_cursor

THE MSS-TUTL TUTORIAL - PART 6

This is the final tutorial. It essentially covers the covers the data control facilities which are provided

within SQL SERVER via the predefined stored procedures, the GRANT and the REVOKE commands.

Authority Administration

The Systems Administrator (SA) has the authority to create a database with the CREATE DATABASE command and to create associated logins and users. This has been done using the following command.

CREATE DATABASE group_a

The entire tutorial so far has assumed the existence of a single login and user. The name of this login

and user was, for example, login_a01 which was assigned after the corresponding group database (eg. group_a) was created. The command defines the login identity and the required password.

sp_addlogin login_a01,login_a01

In addition a procedure has been run to provide the login_a01 identity with the status of a database owner, eight additional login_ids have been provided, and single user group identitied. The eight logins identities numbered (02 through 09) are then associated with a corresponding user identity and the group identity.

sp_adduser login_a01, login_a01, group_a

Prior to creating any table, a user should change their password since the original password is known to

everyone. A password may be modified by specifying the new password in a the following stored procedure.

sp_password login_a01, newpassword

Privileges via GRANT command

We shall now review the security features of SQL SERVER whereby the original creator of a database may grant differing users with different levels of access authority to the database. The original creator has what is called Systems Administrator (SA) authority ie. unrestricted access to all the tables in the database and the ability to create new tables or drop them. As outlined above, the SA may also create new users and grant them various levels of authority over the database. These users, if they have the authority to create new tables, may selectively grant access privileges to users for a given table, or in some cases, even to individual columns within a table. All of these capabilities are implemented through a single SQL command called GRANT. The privileges and authority levels may be selectively revoked through the REVOKE command.

Sometimes, the SA, the original creator of a database may wish to grant the authority to a user to be

able to access, modify or drop not only his own tables, but also to be able to do so with all other

tables in the database. This is known as DBO authority and is very similar to SA authority. The only

difference is that DBO authority does no allow a user to create new user logins. The only person

who can do that is the SA. The is done from within a database via the USE database and a stored procedure.

USE group_a

sp_changedbowner login_a01

User Command Privileges

To connect to the database and access any tables a user must have SELECT privileges. This is achieved with the following command which grants the user group the select privileges.

GRANT SELECT

ON PRESIDENT

TO group_a

Note that one must use a prefix for the name of the PRESIDENT table if this is created by another user.

This is because each table, when being accessed by anybody other than the creator of that table, must

have the name of the creator as its prefix in order to properly identify it. This enables two users

to create a table with the same name.

SELECT *

FROM dbo.PRESIDENT

To update or to create tables or views then the user must have update or create privileges. An example of the creation of select and update privileges for two specific users on two tables is the following.

GRANT SELECT, INSERT, DELETE

ON PRESIDENT, ELECTION

TO login_a08, login_a09

Note that since the two users already had a SELECT privilege on PRESIDENT via the group grant, then

repeating the privilege in another GRANT command has no additional effect. Another privilege that

may be granted to a user is UPDATE. This is granted for an entire table or for specified columns

within a table. The following command grants the UPDATE privilege to two users on the DEATH_AGE

and YRS_SERV columns of the PRESIDENT table.

GRANT UPDATE (DEATH_AGE, YRS_SERV)

ON PRESIDENT

TO login_a08

Rather than listing out all the privileges available for a given table, you may use the keyword ALL to

signify all privileges. Also, rather than listing individual users, the keyword PUBLIC may be used to

signify all users.

GRANT ALL

ON ELECTION

TO PUBLIC

The SA or DBO can grant privileges to CREATE, ALTER or DROP TABLES and privileges can be granted on CREATE or DROP INDEX. Many other privileges will be found in the Transact-SQL reference manual.

SYS authority Structures

To verify that all of the above authorities and privileges have been granted as specified one can

look at the system dictionary tables to see if they accurately reflect the results of the above commands.

The object table has a column called UID that displays the name of the user who created the table.

SELECT NAME, UID

FROM SYSOBJECTS

In addition, the system dictionary contains the privileges which are to be found in the SYSPROTECTS

and the users which are to be found in SYSUSERS..

SELECT * FROM SYSPROTECTS

SELECT * FROM SYSUSERS

Privilege Removal via REVOKE

So far, we have seen examples only of how to grant privileges and authority levels to users. For each

GRANT command option, there is a corresponding REVOKE option. Thus the SA may revoke any of the authorities or logins granted to any user, a DBO may revoke any privileges of any user of the database, and the creator of a table may revoke any privileges previously granted to users.

The following are examples of revocation of privileges..

REVOKE UPDATE

ON PRESIDENT

FROM login_a09

REVOKE ALL

ON ELECTION

FROM PUBLIC

As you can see, SQL SERVER has a security scheme that is comprehensive and easy to administer. It

is compatible with the scheme employed by other relational database products such as ORACLE and

DB2 though there are some syntactical differences between SQL SERVER and the above mentioned

products.

Home
Home

Last updated: 21/03/97 19:55:07
Authored and Designed by:
Bernard W Bennetto   bennetto@tt-ltd.com

© Bernard W Bennetto 1996 - 1997