Working with MSSQL Microsoft SQL Server 2005

Working with MSSQL – Microsoft SQL Server 2005

The following is a short review of the steps taken to access SQL Server Management Studio. This is the tool that we will be using this semester to create tables, modify tables, and query the tables of a database.

SQL Server Management Studio is accessed through clicking on the Microsoft listing and the SQL Server 2005 sub-listing found in ‘All Programs’ on Seneca Computer Lab computers. In order to connect to SQL Server Management Studio in this environment, look for, or input the following pieces of information:

Server Type: Database Engine

Server Name: tcp:warp.senecac.on.ca

Authentication: SQL Server Authentication

Login: <your BTD210 login>

Password: <your assigned password>

Once you have connected with the warp server, click on the database symbol so that you can find, and connect to your own personal database account …e.g. btd210_081axx.

SQL uses commands to create and update tables and to retrieve data from tables.

Remember: Your account is a database, and as a consequence, you cannot create a database with a name other than your account name, but ONLY on this server. If you are using SQL Server at home where you are the local host, you are able to create as many databases as you wish. However, there are other databases on this server which we are able to access and use for queries.

SQL uses commands to create and update tables and to retrieve data from tables. Using the Premiere database, we have been able to using retrieve data simply using the

SELECT–FROM –WHERE command as shown below:

Basic Query Command:

SELECT <attributes>

FROM <entity(or entities)>

WHERE <certain conditions exist> - this clause is optional.

Obviously, you can only query an existing database.

To create database tables in your account, you use the SQL CREATE TABLE command to create the table and describe its layout.

If you use Management Studio to create a table, you are introduced to a screen that looks something like this:

Column Name / Data Type / Allow Nulls
O

The Column Name column lists the name of the table attribute(s). The data types that you will most often encounter are listed below:

• INTEGER - uses integers, numbers without a decimal part. You can use the contents of INTEGER fields for calculations.

• SMALLINT – stores integers, but uses less space than do integers. The valid data range is -32768 to +32767. You can use the contents of the SMALLINT column for calculations.

• DECIMAL(p,q) – Stores a decimal number p digits long with q of those digits being decimal places. For example, DECIMAL(5,2) represents a number with 3 places to the left of the decimal point, and 2 places top the right of the decimal point. Use the CURRENCY
• Data type for fields that will contain currency values.

• CHAR(n)-Stores a character string n characters long. You use the CHAR type for fields that contain letters and other special characters and for numbers that will not be used for calculations. For example, since neither the sales rep number nor the customer number will be used in any calculations, it is best to assign both of them the CHAR data type.

• DATE – Stores dates in the form DD-MM-YYYY or MM/DD/YYYY. To enter date data , key in the form of YYYY_MM_DD.

Allowing NULLS means that it is possible for the field to be EMPTY and not destroy the integrity of the database. A NULL does not mean that the value of the field is 0 (zero).

Alternately, you can use SQL scripting to create a table. In the case of the REP table which was created using Management Studio, the following CREATE TABLE script would produce the same result, but would be accomplished through a ‘new query’.

The general CREATE TABLE command for the REP table is as follows:

CREATE TABLE Rep

(RepNumCHAR(2)

LastNameCHAR(15)

FirstNameCHAR(15)

StreetCHAR(15)

CityCHAR(15)

StateCHAR(2)

ZipCodeCHAR(5)

CommissionDECIMAL(7,2)

RateDECIMAL(3,2) )

(Note: there is no rule that says you must write the SQL in the format given above. It is simply written that way to make the command more readable.)

Examples of Simple Retrieval

Recall that the basic form of an SQL retrieval command is simply SELECT-FROM-WHERE. There are so special formatting rules in SQL; putting the SELET, FROM, and WHERE clauses on separate lines is done so to make the commands more readable.

Consider the following examples:

a)List the number, name and balance of all customers.

SELECT custnum, custname, balance

FROM Customer

(there is no WHERE clause because no conditions have to be met)

b)List the complete PART table

SELECT *

FROM Part

c)List the name of every customer with a $10,000 credit limit

SELECT custname

FROM Customer

WHERE creditlimit =10000

d)Find the name of customer 148

SELECT custname

FROM Customer

Where custnum = ‘148’

Note that the customer number is enclosed by single parentheses because it was defined as a CHAR field.

e)Find the customer name for every customer located in Grove.

SELECT custname

FROM Customer

WHERE city = ‘Grove’

f)List the number, name, credit limit and balance for all customers with credit limits that exceed their balances.

SELECT custnumber, custname, creditlimit, balance

FROM Customer

WHERE creditlimit > balance

SQL Commands with Compound Conditions

A compound condition is formed by connecting two or more simple conditions using one both of the following operators: AND and OR. You can also precede a single condition with the NOT operator to negate the condition.

When you connect simple conditions using the AND operator, all simple conditions must be true for the compound condition to be true. When you connect simple conditions using the OR operator, the compound condition will be true whenever any of the simple conditions are true. Preceding a condition with the NOT operator reverses the truth or falsity of the original condition.

Further Examples:

g)List the descriptions of all parts that are located in warehouse 3 and for which there are more than 20 units on hand.

SELECT description

FROM Part

WHERE warehouse = ‘3’ and on hand>20

h)List the description of all parts that are located in warehouse 3 or for which there are more than 20 units on hand

SELECT description

FROM Part

WHERE warehouse = ‘3’ or on hand>20

i)List the description of all parts that are not in warehouse 3

SELECT description

FROM Part

WHERE NOT Warehouse =’3’

j)List the number, name, and balance of all customers with balances greater than or equal to $1,000 and less than or equal to $5,000

SELECT custnum, custnam, balance

FROM Customer

WHERE balance BETWEEN 1000 and 5000

COMPUTED FIELDS

You can include fields in queries that are not in a database, but whose values you can compute from existing database fields. A field whose values you derive from existing fields is called a computed or calculated field. Computed fields can include addition (+), subtraction (-), multiplication (*), or division(/).

More Examples:

k)List the number, name and available credit for all customers.

SELECT custnum, custnam, creditlimit – balance as availablecredit

FROM Customer

There is no field in the database that stores available credit, but you can compute it using two fields that are available in the database: Credit Limit and Balance.

l)List the number, name, and available credit for all customers with credit limits that exceed their balances

SELECT custnum, custname, creditlimt-balance as availablecredit

FROM Customer

WHERE creditlimit > balance

USING SPECIAL OPERATORS

In most cases, your conditions will involve exact matches such as finding customers located in a particular city. In some cases however, exact matches will not work. For example, you might know only that the desired value contains a certain collection of characters. In such cases, you use the LIKE operator with a wildcard as shown below:

Example:

m)List the number, name and exact address of every customer located on a street that contains the letters oxford.

SELECT custnum, custname, street, city, state, zip

FROM customer

WHERE street LIKE ‘%oxford%’

Another operator, IN, provides a concise way of phrasing certain conditions;

n)List the number, name, and credit limit for every customer with a credit limit of $7,500, $10,000 or $15,000

SELECT custnum, custname, creditlimit

FROM customer

WHERE credit limit IN (7500, 10,000, 15,000)

SORTING

It was earlier indicated that the order of the rows in a table is immaterial. For all practical purposes, this means that when you query a relational database, there are no guarantees in what order the results will be displayed. The results might appear in the order in which the data was originally entered, but that is not a certainty. If the order in which the data is displayed IS important, you should specifically request that the results be displayed in the desired order.

o)List the number, name, street and credit limit for all customers. Order (sort) the customers by name.

SELECT custnum, custname, street, creditlimit

FROM customers

ORDER BY custname

p) List the number, name, street and credit limit for all customers. Order the customers by name within a descending credit limit. (In other words, sort the customers by credit limit in descending order. Within each group of customers that have a common credit limit, sort the customers by name.

When you need to sort data on two fields, the more important sort key is called the major sort key or the primary sort key, and the less important sort key is called the minor sort key or the secondary sort key. In this example, because you need to sort the output by name within credit limit, the CreditLimit field is the major sort key, and the CustomerName field is the minor sort key. You can specify to sort the output in descending order (high to low) by following the major sort key by DESC minor sort key.

SELECT custnum, custname, street, creditlimit

FROM customers

ORDER BY creditlimit , customername

BUILT-IN FUNCTIONS

SQL has built-in functions, also called aggregate functions to calculate the number of entries, the sum or average of all the entries in a given column, and the largest or smallest value in a given column. In SQL, these functions are called COUNT, SUM, AVG, MAX or MIN, respectively.

q)How many items are in item class HW?

SELECT COUNT(*)

FROM Part

WHERE Class = ‘HW”

r)Find the number of customers and the total of their balances.

SELECT COUNT(*), SUM(Balance)

FROM Customer

s) Find the number of customers and the total of their balances. Change the column names for the number of customers and the total of their balances to Customer Count and Balance Total respectively.

SELECT COUNT(*) AS Customer Count , SUM(Balance) AS Balance Total

FROM Customer

JOINING TABLES

Many queries require date from more than one table. If this is the case, it is necessary to be able to join tables so that you can find rows in two or more tables that have identical values in matching fields. In SQL, this is accomplished by entering the appropriate conditions in the WHERE clause.

t)List the number and name of each customer together with the number, last name and first name of the sales rep who represents the customer. Order the records by customer number.

SELECT customer.custnum, customer.custname, rep.repnum, rep.lastname, rep.firstname

FROM customer, rep

WHERE customer.repnum = rep.repnum

ORDER BY customer.custnum

u)List the number and name of each customer whose credit limit is $10,000 together with the number, last name, first name of the sales rep who represents the customer. Order the records by customer number.

SELECT customer.custnum, customer.custname, rep.repnum, rep.lastname, rep.firstname

FROM customer, rep

WHERE customer.repnum = rep.repnum

And creditlimit =10000

ORDER BY customer.custnum

It is possible to join more than two tables. For each pair of tables that you join, you must include a condition indicating how the tables are related.

v)For every order, list the order number, order date, customer number , and customer name. In addition, for each order line within the order, list the part number, description, number ordered and quoted price. Order the records by order number.

SELECT order.ordernum, order.orderdate, order.customernum, customer.custname, orderline.ordernum, orderline.partnum, part.descrip, orderline.numberordered, orderline.quotedprice

FROM order, customer, orderline, part

WHERE order.custnum = customer.custnum

And orderline.ordernum = order.ordernum

And orderline.partnum = part.partnum

ORDER By order.ordernum

Page 1 of 8