Title: Lecture 5: SQL Server 2005
1Lecture 5 SQL Server 2005
- SQL DDL/DML statements, functions, and examples
2SQL (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
3SQL 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
4Management Studio GUI
5CREATE 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)
6Examples 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))
7Other 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
8Examples 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)
9SELECT 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
10Examples 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
11WHERE 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')
12More 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
13Use 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
14SELECT 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
15Qualified 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
16Queries 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
17Use 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)
18Use 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
19Common 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
20Format 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.
21Date/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.
22Example 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
23INSERT 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')
24INSERT 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
25DELETE 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
26Example 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
27UPDATE 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
28Example 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')
29CREATE 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.
30More 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 ...
31CREATE 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
32PROCEDURE 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
33User-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')
34Script 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
35Example 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
36Main 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.