1 / 36

Lecture 5: SQL Server 2005

Lecture 5: SQL Server 2005. SQL DDL/DML statements, functions, and examples. 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:

taya
Download Presentation

Lecture 5: SQL Server 2005

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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.

More Related