Download
lecture 5 sql server 2005 n.
Skip this Video
Loading SlideShow in 5 Seconds..
Lecture 5: SQL Server 2005 PowerPoint Presentation
Download Presentation
Lecture 5: SQL Server 2005

Lecture 5: SQL Server 2005

349 Vues Download Presentation
Télécharger la présentation

Lecture 5: SQL Server 2005

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  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 Server’s 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 – attribute’s 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 >= 0) AND (PaymentTotal >= 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 CHECK|WITH NOCHECK] {ADD new_column_name data_type [column_attributes] | DROP COLUMN column_name | ALTER COLUMN column_name new_data_type [NULL|NOT 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 >= 1)

  9. SELECT statement • Used to query a database • Basic syntax: SELECT attribute_list_1 FROM table_list [WHERE search_condition][ORDER 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 > 0 WHERE InvoiceTotal > PaymentTotal + CreditTotal • Vendors with names from A to L WHERE VendorName < 'M' • Invoices on or before a specified date WHERE InvoiceDate <= '2006-05-31' • A search condition that uses the AND operator WHERE VendorState = 'NJ' AND YTDPurchases > 200 • Invoices with credits that don’t equal zero WHERE CreditTotal <> 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 > '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 > 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 < 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

  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

  22. Example of date/time functions

  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 DNUMBER=DNO 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 PNUMBER=10 • 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 {PROC|PROCEDURE} 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 > 0 ORDER BY VendorName • Execute it with command: EXEC spInvRept

  32. PROCEDURE with parameters • Procedure with input and output parameters CREATE PROC spInvTotal1 @DateVar smalldatetime, @InvTotal money OUTPUT AS SELECT @InvTotal = SUM(InvoiceTotal) FROM Invoices WHERE InvoiceDate >= @DateVar • Pass parameters to the procedure by position DECLARE @MyInvTotal money EXEC spInvTotal1 '2006-06-01', @MyInvTotal OUTPUT • Parameters can be passed by name

  33. User-Defined Functions • User can create functions similar to procedures CREATE FUNCTION fnVendorID (@VendorName varchar(50)) RETURNS int BEGIN RETURN (SELECT VendorID FROM Vendors WHERE VendorName = @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 >= 500), AcctBalance money NOT NULL CHECK (AcctBalance >= 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: • Murach’s SQL for SQL Server for developers,written by Bryan Syverson, 2005, Mike Murach & Associates, Inc.