Lecture 5: SQL Server 2005

1
Lecture 5 SQL Server 2005

• SQL DDL/DML statements, functions, and examples

2
SQL (Structured Query Language)

• Several standards and versions exist
• ANSI SQL4 (2003) is the latest
• XML and identity columns were introduced
• T-SQL is the SQL Servers SQL dialect
• SQL DDL statements
• CREATE DATABASE, TABLE, INDEX, VIEW,
• ALTER TABLE, VIEW, FUNCTION, PROCEDURE, TRIGGER
• DROP DATABASE, TABLE, INDEX, VIEW,
• SQL DML statements
• SELECT, INSERT, UPDATE, DELETE

3
SQL Server Management Studio

• SQL Server client tool that allows you to
• Create SQL Server objects
• Write, load, save, and execute SQL queries
• Import and export objects
• Create and view database diagrams
• Modify SQL server objects (e.g., update columns)
• Select it from the list of tools for SQL Server
• You must select the SQL Server MSSQL for this
  course when a dialog box is presented
• Connect to SQL Server using Windows authentication

4
Management Studio GUI
5
CREATE TABLE

• Basic syntax
• CREATE TABLE table_name
• (column_name_1 data_type column_attributes
• , column_name_2 data_type column_attributes...
  
• , table_attributes)
• Column attributes
• NULL NOT NULL attributes acceptance of NULL
  values
• PRIMARY KEY UNIQUE defines prim/cand keys
• IDENTITY assign automatic values to surrogate
  keys
• DEFAULT default_value specify default values
• CHECK Impose some limit to values for a column
• Table attributes
• PRIMARY KEY, UNIQUE, CHECK, REFERENCES (for
  foreign keys)

6
Examples for CREATE TABLE

• Example 1 With column attributes
• CREATE TABLE Invoices
• (InvoiceID INT NOT NULL IDENTITY PRIMARY KEY,
• VendorID INT NOT NULL REFERENCES
  VENDOR(vID),
• InvoiceDate SMALLDATETIME NULL,
• InvoiceTotal MONEY NULL DEFAULT 0)
• Example 2 With table attributes
• CREATE TABLE Invoices2
• (InvoiceID INT NOT NULL IDENTITY,
• InvoiceTotal MONEY NOT NULL,
• PaymentTotal MONEY NOT NULL DEFAULT 0,
• CHECK ((InvoiceTotal gt 0) AND (PaymentTotal gt
  0))
• PRIMARY KEY (InvoiceID))

7
Other DML statements

• Statement to drop table XYZ
• DROP TABLE XYZ
• Syntax of statement to alter a table
• ALTER TABLE table_name WITH CHECKWITH NOCHECK
• ADD new_column_name data_type column_attributes
  
• DROP COLUMN column_name
• ALTER COLUMN column_name new_data_type NULLNOT
  NULL
• ADD CONSTRAINT new_constraint_definition
• DROP CONSTRAINT constraint_name

8
Examples of ALTER statements

• Drop a column from a table
• ALTER TABLE Vendor
• DROP COLUMN LastTranDate
• Add a foreign key constraint
• ALTER TABLE InvoiceX WITH CHECK
• ADD FOREIGN KEY (AccNo)
• REFERENCES YAccounts(AcctNo)
• Change a data type of an attribute
• ALTER TABLE InvX
• ALTER COLUMN InvDescr VARCHAR(200)
• Add a CHECK constraint
• ALTER TABLE Invoice WITH NOCHECK
• ADD CHECK (InvoiceTotal gt 1)

9
SELECT statement

• Used to query a database
• Basic syntax
• SELECT attribute_list_1
• FROM table_list
• WHERE search_conditionORDER BY
  attribute_list_2
• Example Select three attributes from Invoice,
  ordered by InvoiceTotal
• SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
• FROM Invoice
• ORDER BY InvoiceTotal DESC

10
Examples of WHERE clause

• Vendors located in Iowa
• WHERE VendorState 'IA'
• Invoices with a balance due (two variations)
• WHERE InvoiceTotal PaymentTotal CreditTotal
  gt 0
• WHERE InvoiceTotal gt PaymentTotal CreditTotal
• Vendors with names from A to L
• WHERE VendorName lt 'M'
• Invoices on or before a specified date
• WHERE InvoiceDate lt '2006-05-31'
• A search condition that uses the AND operator
• WHERE VendorState 'NJ' AND YTDPurchases gt 200
• Invoices with credits that dont equal zero
• WHERE CreditTotal ltgt 0

11
WHERE clause with IN operator

• IN is used to test if a value is IN or is NOT IN
  a set
• Examples
• An IN phrase with a list of numeric literals
• WHERE TermsID IN (1, 3, 4)
• An IN phrase preceded by NOT
• WHERE VendorState NOT IN ('CA', 'NV', 'OR')
• An IN phrase with a subquery
• WHERE VendorID IN
• (SELECT VendorID
• FROM Invoices
• WHERE InvoiceDate '2006-05-01')

12
More SELECT statements
A SELECT statement that retrieves all invoices
between given dates SELECT InvoiceNumber,
InvoiceDate, InvoiceTotal FROM Invoices WHERE
InvoiceDate BETWEEN '2006-05-01' AND
'2006-05-31' ORDER BY InvoiceDate A SELECT
statement with renaming columns SELECT
InvoiceNumber AS Invoice Number,
InvoiceDate AS Date, InvoiceTotal AS Total FROM
Invoices How to format strings and include
apostrophes in literal values SELECT VendorName
'''s Address AS Name, VendorCity ', '
VendorState ' ' VendorZipCode AS Address FROM
Vendors
13
Use of string functions

• A SELECT statement that uses the LEFT function
• SELECT VendorContactFName, VendorContactLName,
• LEFT(VendorContactFName, 1)
• LEFT(VendorContactLName, 1) AS Initials
• FROM Vendors
• Other string functions
• LEN (str) returns the number of characters in str
• LTRIM(str) remove leading spaces in string
• RTRIM(str) remove trailing spaces in string
• RIGHT(str, l) returns l characters from start of
  string
• SUBSTRING(str, st, l) returns l characters from
  str at st position
• REVERSE(str) returns str in reverse order
• LOWER(str) convert str to lowercase letters
• UPPER(str) convert str to uppercase letters

14
SELECT with TOP and Percent

• A SELECT statement with the TOP clause
• SELECT TOP 5 VendorID, InvoiceTotal
• FROM Invoices
• ORDER BY InvoiceTotal DESC
• The same statement with the PERCENT keyword
• SELECT TOP 5 PERCENT VendorID, InvoiceTotal
• FROM Invoices
• ORDER BY InvoiceTotal DESC

15
Qualified table names

• A join with fully-qualified table names
• SELECT VendorName, CustLastName, CustFirstName,
• VendorState AS State, VendorCity AS City
• FROM DBServer.AP.dbo.Vendors AS Vendors
• JOIN DBServer.ProductOrders.dbo.Customers AS
  Customers
• ON Vendors.VendorZipCode Customers.CustZip
• ORDER BY State, City
• The same join with partially-qualified table
  names
• SELECT VendorName, CustLastName, CustFirstName,
• VendorState AS State, VendorCity AS City
• FROM Vendors
• JOIN ProductOrders..Customers AS Customers
• ON Vendors.VendorZipCode Customers.CustZip
• ORDER BY State, City

16
Queries with aggregate functions

• A summary query that uses the COUNT(), AVG, and
  SUM functions
• SELECT 'After 1/1/2006' AS SelectionDate,
• COUNT() AS NumberOfInvoices,
• AVG(InvoiceTotal) AS AverageInvoiceAmount,
• SUM(InvoiceTotal) AS TotalInvoiceAmount
• FROM Invoices
• WHERE InvoiceDate gt '2006-01-01
• A summary query that works on non-numeric columns
• SELECT MIN(VendorName) AS FirstVendor,
• MAX(VendorName) AS LastVendor,
• COUNT(VendorName) AS NumberOfVendors
• FROM Vendors

17
Use of ALL, ANY keywords

• A query that returns invoices that are larger
  than the largest invoice for vendor 34
• SELECT VendorName, InvoiceNumber, InvoiceTotal
• FROM Invoices JOIN Vendors ON Invoices.VendorID
  
• Vendors.VendorID
• WHERE InvoiceTotal gt ALL
• (SELECT InvoiceTotal FROM Invoices WHERE
  VendorID 34)
• ORDER BY VendorName
• A query that returns invoices smaller than the
  largest invoice for vendor 115
• SELECT VendorName, InvoiceNumber, InvoiceTotal
• FROM Vendors JOIN Invoices ON Vendors.VendorID
• Invoices.InvoiceID
• WHERE InvoiceTotal lt ANY
• (SELECT InvoiceTotal FROM Invoices WHERE
  VendorID 115)

18
Use of CONVERT function

• A SELECT statement that uses the CONVERT function
• SELECT CONVERT(varchar, InvoiceDate) AS
  varcharDate,
• CONVERT(varchar, InvoiceDate, 1) AS
  varcharDate_1,
• CONVERT(varchar, InvoiceDate, 107)
• AS varcharDate_107,
• CONVERT(varchar, InvoiceTotal) AS
  varcharTotal,
• CONVERT(varchar, InvoiceTotal, 1) AS
  varcharTotal_1
• FROM Invoices
• no commas to the left commas to the left

19
Common CONVERT codes
Data type Code Output format
Date/time 0 or 100 (def) Mon dd yyyy hhmiAM/PM
1 or 101 mm/dd/yy or mm/dd/yyyy
7 or 107 Mon dd, yy or Mon dd, yyyy
8 or 108 hhmiss
10 or 110 mm-dd-yy or mm-dd-yyyy
Real 0 (default) 6 digits (maximum)
1 8 digits must use scientific notation
2 16 digits must use scientific notation
Money 0 (default) 99,999,999.99 format
1 99999999.99 format
2 99999999.9999 format
20
Format real numbers

• SELECT ID, R, CAST(R AS decimal(9,2)) AS
  R_decimal,
• CAST(CAST(R AS decimal(9,2)) AS varchar(9))
• AS R_varchar,
• LEN(CAST(CAST(R AS decimal(9,2)) AS
  varchar(9)))
• AS R_LEN,
• SPACE(9 - LEN(CAST(CAST(R AS decimal(9,2))
• AS varchar(9))))
• CAST(CAST(R AS decimal(9,2)) AS varchar(9))
• AS R_Formatted
• FROM RealSample
• CAST(e, d) function converts an expression to a
  data type. SPACE(n) function returns n spaces.

21
Date/time functions
Function Description
GETDATE() Return systems date and time.
DAY(date) Returns the day of the month as an integer.
MONTH(date) Returns the month as an integer.
YEAR(date) Returns the 4-digit yeas as an integer.
DATENAME(datepart, date) Returns part of the date specified by datepart as string.
DATEADD(datepart, , date) Returns part of the date specified by datepart as int.
DATEDIFF(datepart, startdate,enddate) Returns the number of datepart units between the specified start and end dates.
ISDATE(expression) Returns 1 if the expression is a valid date/time else 0.
22
Example of date/time functions
Function Result
MONTH(2007-10-22) 10
DAY(2007-10-22) 22
DATEPART(m, 2007-10-22) 10
DATENAME(month, 2007-10-22) October
ISDATE(2007-10-22) 1 (if todays date is Oct. 22, 2007)
DATEPART(week, 2007-10-22) 43
DATENAME(quarter, 2007-10-22) 4
DATENAME(weekday, 2007-10-22) Monday
DATEADD(day, 1, 2007-10-20) 2007-10-21 000000.000
DATEADD(year, 1.5, 2006-02-22) 2007-02-22 000000.000
DATEDIFF(month, 2005-12-06, 2006-09-30) 9
23
INSERT statement

• Inserts one or more tuples into a database table
• User and model constraints are enforced during
  INSERT
• Example of inserting a single tuple
• INSERT INTO EMPLOYEE VALUES
  ('Richard','K','Marini', '653298653',
  '30-DEC-52', '98 Oak Forest,Katy,TX', 'M',
  37000,'987654321', 4 )
• An alternate form of INSERT specifies explicitly
  the attribute names that correspond to the values
  in the new tuple
• Attributes with NULL values can be left out
• Example Insert a tuple for a new EMPLOYEE for
  whom we only know the FNAME, LNAME, and SSN
  attributes.
• INSERT INTO EMPLOYEE (FNAME, LNAME, SSN)
  VALUES ('Richard', 'Marini', '653298653')

24
INSERT inserting multiple tuples

• Example Suppose we want to create a temporary
  table that has the name, number of employees, and
  total salaries for each department.
• A table DEPTS_INFO is created by I1, and is
  loaded with the summary information retrieved
  from the database by the query in I2.
• I1 CREATE TABLE DEPTS_INFO (DEPT_NAME
  VARCHAR(10), NO_OF_EMPS INTEGER,
  TOTAL_SAL INTEGER)
• I2 INSERT INTO DEPTS_INFO (DEPT_NAME,
  NO_OF_EMPS, TOTAL_SAL) SELECT DNAME, COUNT
  (), SUM (SALARY) FROM DEPARTMENT,
  EMPLOYEE WHERE DNUMBERDNO GROUP BY DNAME

25
DELETE statement

• Removes tuples from a relation
• Includes a WHERE-clause to select the tuples to
  be deleted
• Referential integrity should be enforced
• Tuples are deleted from only one table at a time
  (unless CASCADE is specified on a referential
  integrity constraint)
• A missing WHERE-clause specifies that all tuples
  in the relation are to be deleted the table then
  becomes an empty table
• The number of tuples deleted depends on the
  number of tuples in the relation that satisfy the
  WHERE-clause

26
Example of DELETE

• D1 DELETE FROM EMPLOYEE WHERE LNAME'Brown
• D2 DELETE FROM EMPLOYEE WHERE SSN'123456789
  
• D3 DELETE FROM EMPLOYEE WHERE DNO IN
  (SELECT DNUMBER FROM DEPARTMENT
  WHERE DNAME'Research')
• D4 DELETE FROM EMPLOYEE

27
UPDATE statement

• Used to modify attribute values of one or more
  selected tuples
• A WHERE-clause selects the tuples to be modified
• An additional SET-clause specifies the attributes
  to be modified and their new values
• Each command modifies tuples in the same relation
• Referential integrity should be enforced

28
Example of UPDATE

• Example 1 Change the location and controlling
  department number of project number 10.
• U1 UPDATE PROJECT SET PLOCATION
  'Bellaire', DNUM 5 WHERE PNUMBER10
• Example 2 Give all employees in the 'Research'
  department a 10 raise in salary.
• U2 UPDATE EMPLOYEE SET SALARY SALARY
  1.1 WHERE DNO IN
• (SELECT DNUMBER FROM
  DEPARTMENT WHERE DNAME'Research')

29
CREATE VIEW

• A CREATE VIEW statement for a view named VendX
• CREATE VIEW VendX AS SELECT VendorName,
  VendorState, VendorPhone FROM Vendors
• A SELECT statement that uses the VendX view
• SELECT FROM VendXWHERE VendorState
  'CA'ORDER BY VendorName
• A view can be based on another view.
• SELECT statement can include joins, sub-queries,
  unions.

30
More commands on VIEWs

• A CREATE VIEW statement that summarizes invoices
  by vendor
• CREATE VIEW InvoiceSummary
• AS
• SELECT VendorName, COUNT() AS InvoiceQty,
• SUM(InvoiceTotal) AS InvoiceSum
• FROM Vendors JOIN Invoices ON Vendors.VendorID
• Invoices.VendorID
• GROUP BY VendorName
• You can ALTER and/or DROP VIEWs
• DROP VIEW InvoiceSummary
• ALTER VIEW InvoiceSummary
• WITH ENCRIPTION Hide VIEW definition from
  others
• AS SELECT ...

31
CREATE PROCEDURES

• A procedure is program code stored in a DB
• CREATE PROCPROCEDURE procedure_name
• parameter_declarations
• WITH RECOMPILE , ENCRYPTION ,
  EXECUTE_AS_clause
• AS sql_statements
• Example
• CREATE PROC spInvRept
• AS
• SELECT InvoiceNumber, InvoiceDate, InvoiceTotal
• FROM Invoices JOIN Vendors
• ON Invoices.VendorID Vendors.VendorID
• WHERE InvoiceTotal CreditTotal PaymentTotal gt
  0
• ORDER BY VendorName
• Execute it with command EXEC spInvRept

32
PROCEDURE with parameters

• Procedure with input and output parameters
• CREATE PROC spInvTotal1
• _at_DateVar smalldatetime,
• _at_InvTotal money OUTPUT
• AS
• SELECT _at_InvTotal SUM(InvoiceTotal)
• FROM Invoices
• WHERE InvoiceDate gt _at_DateVar
• Pass parameters to the procedure by position
• DECLARE _at_MyInvTotal money
• EXEC spInvTotal1 '2006-06-01', _at_MyInvTotal OUTPUT
• Parameters can be passed by name

33
User-Defined Functions

• User can create functions similar to procedures
• CREATE FUNCTION fnVendorID
• (_at_VendorName varchar(50))
• RETURNS int
• BEGIN
• RETURN (SELECT VendorID FROM Vendors
• WHERE VendorName _at_VendorName)
• END
• Invoking this function
• SELECT InvoiceDate, InvoiceTotal
• FROM Invoices
• WHERE VendorID dbo.fnVendorID('IBM')

34
Script files

• You can store SQL statements in a file for
• Creating objects all at once, or
• Populating or querying the database.
• Several batches are stored in a script file
• A batch is a set of SQL statements executed as a
  unit
• There is a GO statement at the end of each batch
• It signals to execute the statements of a batch
  immediately
• The first statement is USE database_name
• It indicates the current database

35
Example of a small script file

• USE SalesDB
• GO
• -- DROP TABLES
• if exists (select from sysobjects where id
  object_id('ORDER1'))
• drop table ORDER1
• if exists (select from sysobjects where id
  object_id('CUSTOMER'))
• drop table CUSTOMER
• GO
• / CREATE CUSTOMER TABLE /
• CREATE TABLE CUSTOMER (
• CustomerID bigint NOT NULL ,
• CustName varchar (100) NOT NULL ,
• CreditLimit money NOT NULL CHECK (CreditLimit gt
  500),
• AcctBalance money NOT NULL CHECK (AcctBalance gt
  0),
• DateRegistered datetime NOT NULL ,
• CONSTRAINT CustomerPK PRIMARY KEY CLUSTERED
  (CustomerID)
• )
• GO

36
Main reference for these slides

• These slides have been adapted mainly from the
  set of PowerPoint presentations provided to
  instructors for the textbook below
• Murachs SQL for SQL Server for developers,
  written by Bryan Syverson, 2005, Mike Murach
  Associates, Inc.