TransactSQL SECTION 6 Based on SQL 1992 Introduction

Transact-SQL SECTION 6 Based on SQL (1992)

Introduction • Most important relational data manipulation language • ANSI • Used by many commercial databases

• SEQUEL • 1992 ANSI standard • SQL 3

• An interactive query language • Or embedded in other languages • SQL is not a programming language • Transact-SQL

• Relations used for SQL examples • STUDENT (SID, Name, Major, Grade. Level, Age) • CLASS (Name, Time, Room) • ENROLLMENT ( Student. Number, Class. Name, Position. Number) Is there a problem with the ENROLLMENT table and the primary key?

Creating a Table CREATE TABLE STUDENT (SID NUMERIC(3) CONSTRAINT student_pk PRIMARY KEY, Name n. VARCHAR(20), Major n. VARCHAR(10), Grade. Level n. VARCHAR(2), Age NUMERIC(3)) CREATE TABLE ENROLLMENT (Student. Number NUMERIC(3) CONSTRAINT student_fk REFERENCES STUDENT (SID), Class. Name n. VARCHAR(6) CONSTRAINT class_fk REFERENCES CLASS (NAME), CONSTRAINT enrollment_pk PRIMARY KEY (Student. Number, Class. Name) , Position. Number NUMERIC(2)) CREATE TABLE CLASS (Name n. VARCHAR(5) CONSTRAINT class_pk PRIMARY KEY, Time n. VARCHAR(5), Room n. VARCHAR(5))

Inserting Values INSERT INTO STUDENT VALUES (100, ‘JONES’, ‘HISTORY’, ‘GR’, 21) INSERT INTO STUDENT VALUES (150, ‘PARKS’, ‘ACCOUNTING’, ‘SO’, 19) INSERT INTO STUDENT VALUES (200, ‘BAKER’, ‘MATH’, ‘GR’, 50) INSERT INTO STUDENT VALUES (250, ‘GLASS’, ‘HISTORY’, ‘SN’, 50) INSERT INTO STUDENT VALUES (300, ‘BAKER’, ‘ACCOUNTING’, ‘SN’, 41) INSERT INTO STUDENT VALUES (350, ‘RUSSELL’, ‘MATH’, ‘JR’, 20) INSERT INTO STUDENT VALUES (400, ‘RYE’, ‘ACCOUNTING’, ‘FR’, 18) INSERT INTO STUDENT VALUES (450, ‘JONES’, ‘HISTORY’, ‘SN’, 24)

INSERT INTO ENROLLMENT VALUES (100, 'BD 445', 1) INSERT INTO ENROLLMENT VALUES (150, 'BA 200', 1) INSERT INTO ENROLLMENT VALUES (200, 'BD 445', 2) INSERT INTO ENROLLMENT VALUES (200, 'CS 250', 1) INSERT INTO ENROLLMENT VALUES (300, 'CS 150', 1) INSERT INTO ENROLLMENT VALUES (400, 'BA 200', 2) INSERT INTO ENROLLMENT VALUES (400, 'BF 410', 1) INSERT INTO ENROLLMENT VALUES INSERT INTO CLASS VALUES ('BA 200', 'M-F 9', 'SC 100') INSERT INTO CLASS VALUES ('BD 445', 'MWF 3', 'SC 213') INSERT INTO CLASS VALUES ('BF 410', 'MWF 8', 'SC 213') INSERT INTO CLASS VALUES ('CS 150', 'MWF 3', 'EA 304') INSERT INTO CLASS VALUES ('CS 250', 'MWF 12', 'EB 210')

Querying a Single Table • SQL is a transform-oriented language • Accepts as input • The result?

• Sample Data STUDENT SID Name Major Grade. Level Age 100 JONES HISTORY GR 21 150 PARKS ACCOUNTING SO 19 200 BAKER MATH GR 50 250 GLASS HISTORY SN 50 300 BAKER ACCOUNTING SN 41 350 RUSSELL MATH JR 20 400 RYE ACCOUNTING FR 18 450 JONES HISTORY SN 24

ENROLLMENT Student. Number Class. Name Position. Number 100 BD 445 1 150 BA 200 1 200 BD 445 2 200 CS 250 1 300 CS 150 1 400 BA 200 2 400 BF 410 1 400 CS 250 2 450 BA 200 3 CLASS Name Time Room BA 200 M-F 9 SC 110 BD 445 MWF 3 SC 213 BF 410 MWF 8 SC 213 CS 150 MWF 3 EA 304 CS 250 MWF 12 EB 210

Projections Using SQL • The projection – STUDENT [SID, NAME, MAJOR] • Specified in SQL SELECT FROM SID, Name, Major STUDENT SELECT FROM STUDENT. SID, STUDENT. Name, STUDENT. Major STUDENT SELECT FROM s. SID, s. Name, s. Major STUDENT s

• The result 100 JONES HISTORY 150 PARKS ACCOUNTING 200 BAKER MATH 250 GLASS HISTORY 300 BAKER ACCOUNTING 350 RUSSELL MATH 400 RYE ACCOUNTING 450 JONES HISTORY • The word SELECT

Another Example SELECT FROM Major STUDENT A HISTORY ACCOUNTING MATH ACCOUNTING HISTORY SELECT FROM DISTINCT Major STUDENT B HISTORY ACCOUNTING MATH

Selections Using SQL • The Relational Algebra selection operator is also performed • Specified in SQL SELECT FROM WHERE SID, Name, Major, Grade. Level, Age STUDENT Major = ‘Math’

• SELECT - FROM - WHERE SELECT FROM WHERE * STUDENT Major = ‘Math’ 200 BAKER MATH GR 50 350 RUSSELL MATH JR 20

• Combination of Selection and Projection SELECT FROM WHERE Name, Age STUDENT Major = ‘Math’ BAKER 50 RUSSELL 20

• Conditions in a WHERE clause SELECT FROM WHERE Name, Age STUDENT Major = ‘Math’ AND Age > 21 BAKER 50

• Conditions in a WHERE clause can refer to a set of values SELECT FROM WHERE Name STUDENT Major IN (‘Math’, ‘ACCOUNTING’) PARKS BAKER RUSSELL RYE

• The following expression SELECT FROM WHERE Name STUDENT Major NOT IN (‘Math’, ‘ACCOUNTING’) JONES GLASS JONES

• WHERE clause and ranges of values SELECT FROM WHERE Name, Major STUDENT Age BETWEEN 19 AND 30 JONES HISTORY RUSSELL MATH JONES HISTORY • Equivalent to: SELECT FROM WHERE Name, Major STUDENT Age > 19 AND Age < 30

• WHERE clause and the LIKE keyword SELECT FROM WHERE Name, Grade. Level STUDENT Grade. Level LIKE ‘_R’ JONES GR BAKER GR RUSSELL JR RYE FR

• Finding last names ending with S SELECT FROM WHERE Name STUDENT Name LIKE ‘%S’ JONES PARKS GLASS JONES

• IS NULL keyword SELECT FROM WHERE • Name STUDENT Grade. Level IS NULL What will this return?

Sorting • Rows of a result can be sorted SELECT FROM WHERE ORDER BY Name, Major, Age STUDENT Major = ‘Accounting’ Name BARKER ACCOUNTING 41 PARKS ACCOUNTING 19 RYE ACCOUNTING 18

• Sorting more than one column SELECT FROM WHERE ORDER BY Name, Major, Age STUDENT Grade. Level IN (‘FR’, ‘SO’, ‘SN’) Major ASC, Age DESC BARKER ACCOUNTING 41 PARKS ACCOUNTING 19 RYE ACCOUNTING 18 GLASS HISTORY 50 JONES HISTORY 24

CAST & CONVERT • Both of these functions change data values CAST Syntax CAST(expression as [datatype]) COURSE SID Name Course Mark 100 JONES Actg 4 P 87 82. 4 SELECT SID, Name, Course CAST(Mark as Integer) Int_Mark FROM COURSE SID Name Course Int_Mark 100 JONES Actg 4 P 87 82

CONVERT Syntax CCONVERT(datatype, expression, [style]) COURSE SID Name Course Mark 100 JONES Actg 4 P 87 82. 4 SELECT SID, Name, Course CONVERT(Integer, Mark) Int_Mark FROM COURSE SID Name Course Int_Mark 100 JONES Actg 4 P 87 82

CAST & CONVERT with Datetime 2 datatype DATETIME 2 can store century, year, month, date, hour, minute, and second, represented in both character and number datatypes. Date. Of. Bith is a Datetime 2 datatype Declare as: Date. Of. Birth DATETIME 2 Stored as YYYY-MM-DD hh: mm: ss. n STUDENT SID Name Date. Of. Birth 100 JONES 1985 -03 -16 16: 25: 30. 0000000

Datetime 2 datatypes can easily be modified using CAST or CONVERT Select SID, Name, CAST(Date. Of. Birth as n. VARCHAR(10)) Date FROM STUDENT SID Name Date 100 JONES 1985 -03 -16 Or: Select SID, Name, CONVERT(n. VARCHAR(10), Date. Of. Birth)) Date FROM STUDENT.

CONVERT using styles

Select SID, Name, CONVERT(n. VARCHAR(10), Date. Of. Birth), 107) Date FROM STUDENT SID Name Date. Of. Birth 100 JONES Mar 6, 1985 Select SID, Name, CONVERT(n. VARCHAR(10), Date. Of. Birth), 111) Date FROM STUDENT SID Name Date. Of. Birth 100 JONES 1985/03/16

Datetime Functions • GETDATE – Returns the current database system date and time – E. G. SELECT GETDATE() as ‘Current Date & Time’ Current Date & Time 2015 -03 -23 14: 43: 33. 470 • GETUTCDATE – Returns the Coordinated Universal Time (UTC) – E. G. SELECT GETUTCDATE() as ‘GMT Date & Time’ GMT Date & Time 2015 -03 -23 18: 43: 33. 470

• DATEDIFF – Returns the time between two dates as a signed integer – Syntax: DATEDIFF (datepart, startdate, enddate)

SELECT DATEDIFF (dy, ‘ 2015 -02 -15’, ‘ 2015 -03 -20’) as ‘No. of Days’ No. of Days 33 • • DATEADD − Adds or subtracts a specified time interval from a date. – Syntax: DATEADD (datepart, number, date) SELECT DATEADD(dy, 30, GETDATE()) (NO COLUMN NAME) 2015 -04 -22 15: 00: 07. 043

• DATEPART – Returns a single part of a datetime field. – Syntax: DATEPART (datepart, date) SELECT DATEPART (yyyy, GETDATE()) as Year, DATEPART (mm, GETDATE()) as Month, DATEPART (dd, GETDATE()) as Day Year Month Day 2015 3 23

SQL Built-In Functions • Functions – COUNT, SUM, AVG, MAX, and MIN SELECT FROM COUNT (*) STUDENT 8

• Consider the following SELECT FROM vs. SELECT FROM COUNT (Major) STUDENT COUNT (DISTINCT Major) STUDENT 8 3

Built-In Functions and Grouping • To increase built-in function utility • Formed by collecting rows that have the same value of a specified column • GROUP BY

• An example SELECT FROM GROUP BY Major, COUNT (*) STUDENT Major HISTORY 3 ACCOUNTING 3 MATH 2

• Grouping subsets SELECT FROM GROUP BY HAVING Major, COUNT (*) STUDENT Major COUNT (*) > 2 HISTORY 3 ACCOUNTING 3

• Greater generality using WHERE SELECT FROM WHERE GROUP BY HAVING Major, AVG (Age) STUDENT Grade. Level = ‘SN’ Major COUNT (*) > 1 HISTORY 37

OVER and PARTITIONING ORDERS Order. Num Customer. No Cost 345 1 7. 50 346 1 8. 50 347 1 9. 50 348 2 8. 00 Try the following: SELECT SUM(Cost) as Sum. Cost FROM ORDERS Sum. Cost 33. 50 SELECT SUM(Cost) as Sum. Cost, Order. Num FROM ORDERS This does not work SELECT SUM(Cost) OVER () AS Sum. Cost, Order. Num FROM Orders Sum. Cost Order. Num 33. 50 345 33. 50 346 33. 50 347 33. 50 348

OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. Quick translation: SUM(cost) – get me the sum of the COST column OVER – for the set of rows…. () – …that encompasses the entire result set. OVER exposes the entire resultset to the aggregation…”Sum. Cost” was the sum of all [Cost] in the resultset. We can break up that resultset into partitions with the use of PARTITION BY: SELECT SUM(Cost) OVER (PARTITION BY Customer. No) AS Sum. Cost, Order. Num, Customer. No FROM Orders The partition is by Customer. No – each “window” of a single customer’s orders will be treated separately from each other “window”. This will give the sum of cost for Customer 1, and then the sum for Customer 2. Sum. Cost Order. Num Customer. No 25. 50 345 1 25. 50 346 1 25. 50 347 1 8. 00 348 2 The translation here is: SUM(cost) – get me the sum of the COST column OVER – for the set of rows…. (PARTITION BY Customer. No) – …that have the same Customer. No.

Querying Multiple Tables • Study now extended to two or more tables • Will use the data from the following tables • STUDENT (SID, Name, Major, Grade. Level, Age) • CLASS (Name, Time, Room) • ENROLLMENT ( Student. Number, Class. Name, Position. Number)

Retrieval Using Subquery • Want to know the names of the students enrolled in class BD 445 SELECT FROM WHERE Name STUDENT SID IN (100, 200) Student. Number ENROLLMENT Class. Name = ‘BD 445’ 100 200

• Use it as a subquery SELECT FROM WHERE Name STUDENT SID IN (SELECT FROM WHERE JONES BAKER Student. Number ENROLLMENT Class. Name = ‘BD 445’)

• Subqueries can consist of three or more tables SELECT FROM WHERE ENROLLMENT. Student. Number ENROLLMENT. Class. Name IN (SELECT CLASS. Name FROM CLASS WHERE Class. Time = ‘MWF 3’) 100 200 300

• To get the names of the students SELECT FROM WHERE STUDENT. Name STUDENT. SID IN (SELECT ENROLLMENT. Student. Number FROM ENROLLMENT WHERE ENROLLMENT. Class. Name IN (SELECT CLASS. Name FROM CLASS WHERE JONES BAKER Class. Time = ‘MWF 3’))

Joining With SQL – The Inner Join • A join that displays only the rows that have a match in both the joined tables • Want to produce SID, Student. Name, and Class. Name for every student • What two tables must be joined? • Looking for equality between STUDENT. SID and ENROLLMENT. Student. Number SELECT STUDENT. SID, STUDENT. Name, ENROLLMENT. Class. Name FROM STUDENT, ENROLLMENT WHERE STUDENT. SID = ENROLLMENT. Student. Number

• Alternative Syntax SELECT s. SID, s. Name, e. Class. Name FROM STUDENT s -- left join table INNER JOIN ENROLLMENT e --right join table ON s. SID = e. Student. Number SELECT s. SID, s. Name, e. Class. Name FROM STUDENT s -- left join table JOIN ENROLLMENT e --right join table ON s. SID = e. Student. Number http: //www. codeproject. com/Tips/712941/Types-of-Join-in-SQL-Server

• What operations make up a join? • FROM equates to • WHERE expresses 100 JONES BD 445 150 PARKS BA 200 BAKER BD 445 200 BAKER CS 250 300 BAKER CS 125 400 RYE BA 200 400 RYE BF 410 400 RYE CS 250 450 JONES BA 200

• The WHERE clause and qualifiers SELECT STUDENT. SID, ENROLLMENT. Class. Name FROM STUDENT, ENROLLMENT WHERE STUDENT. SID = ENROLLMENT. Student. Number AND STUDENT. Name = ‘RYE’ AND ENROLLMENT. Position. Number = 1 400 RYE BF 410

• Data from more than two tables SELECT STUDENT. SID, CLASS. Name, CLASS. Time ENROLLMENT. Class. Name FROM STUDENT, ENROLLMENT, CLASS WHERE STUDENT. SID = ENROLLMENT. Student. Number AND ENROLLMENT. Class. Name = CLASS. Name AND STUDENT. Name = ‘BAKER’ 200 BD 445 MWF 3 2 200 CS 250 MWF 12 1 300 CS 150 MWF 3 1

Other type of Joins Need to create two new tables: EMPLOYEE: CREATE TABLE EMPLOYEE (Employee. Number INTEGER IDENTITY(100, 1) CONSTRAINT emp_pk PRIMARY KEY, First. Name n. VARCHAR(20), Last. Name n. VARCHAR(20), Depart. ID INTEGER, Manager. Id INTEGER) DEPARTMENT: CREATE TABLE DEPARTMENT (Dept. Id INTEGER IDENTITY(1, 1) CONSTRAINT dept_pk PRIMARY KEY, Department. Name n. VARCHAR(10))

What is IDENTITY? Syntax IDENTITY (seed, increment) A function that generates an Identity Column. An Identity Column is a column in a table that is made up of values generated by the database. This is much like an autonumber Field in Microsoft Access or a sequence in Oracle. It is an important concept in database, implemented by many RDBMS, although each has its own terminology. Identity is an auto-increment function that allows a unique number to be generated when a new record is inserted into a table.

INSERT INTO EMPLOYEE VALUES ('Sheldon', 'White', 1, NULL) INSERT INTO EMPLOYEE VALUES ('Geraldine', 'Summers', 1, 107) INSERT INTO EMPLOYEE VALUES ('Gillian', 'Hall', 1, 107) INSERT INTO EMPLOYEE VALUES ('William', 'Hancock', 2, 100) INSERT INTO EMPLOYEE VALUES ('Sheila', 'Tunning', 3, 105) INSERT INTO EMPLOYEE VALUES ('Waylon', 'Hart', 3, 100) INSERT INTO EMPLOYEE VALUES ('Jennifer', 'Albacore', 3, 105) INSERT INTO EMPLOYEE VALUES ('Harold', 'Spring', 1, 100) INSERT INTO EMPLOYEE VALUES ('Lenny', 'Carling', NULL, 100) INSERT INTO DEPARTMENT VALUES ('Accounting') INSERT INTO DEPARTMENT VALUES ('Marketing') INSERT INTO DEPARTMENT VALUES ('Finance') INSERT INTO DEPARTMENT VALUES ('Sales') INSERT INTO DEPARTMENT VALUES ('IT') Dept. Id Department. Name 1 Accounting 2 Marketing 3 Finance Employee. Number First. Name Last. Name Depart. Id Manager. Id 4 Sales 100 Sheldon White 1 NULL 5 IT 101 Geraldine Summers 1 107 102 Gillian Hall 1 107 103 William Hancock 2 100 104 Sheila Tunning 3 105 Waylon Hart 3 100 106 Jennifer Albacore 3 105 107 Harold Spring 1 100 108 Lenny Carling NULL 100

Left Outer Join Displays all the rows from first table and matched rows from second table Syntax: SELECT * FROM Employee e LEFT OUTER JOIN Department d ON e. Depart. ID = d. Dept. Id Employee. Number First. Name Last. Name Depart. Id Manager. Id Dept. Id Department. Name 100 Sheldon White 1 NULL 1 Accounting 101 Geraldine Summers 1 107 1 Accounting 102 Gillian Hall 1 107 1 Accounting 103 William Hancock 2 100 2 Marketing 104 Sheila Tunning 3 105 3 Finance 105 Waylon Hart 3 100 3 Finance 106 Jennifer Albacore 3 105 3 Finance 107 Harold Spring 1 100 1 Accounting 108 Lenny Carling NULL 100 NULL

Right Outer Join Displays all the rows of second table and matched rows from first table SELECT * FROM EMPLOYEE e RIGHT OUTER JOIN Department d ON e. Depart. ID = d. Dept. Id Employee. Number First. Name Last. Name Depart. Id Manager. Id Dept. Id Department. Name 100 Sheldon White 1 NULL 1 Accounting 101 Geraldine Summers 1 107 1 Accounting 102 Gillian Hall 1 107 1 Accounting 107 Harold Spring 1 100 1 Accounting 103 William Hancock 2 100 2 Marketing 104 Sheila Tunning 3 105 3 Finance 105 Waylon Hart 3 100 3 Finance 106 Jennifer Albacore 3 105 3 Finance NULL NULL 4 Sales NULL NULL 5 IT

Full Outer Join Full outer join returns all the rows from both tables whether it has been matched or not SELECT * FROM Employee e FULL OUTER JOIN Department d ON e. Depart. ID = d. Dept. Id Employee. Number First. Name Last. Name Depart. Id Manager. Id Dept. Id Department. Name 100 Sheldon White 1 NULL 1 Accounting 101 Geraldine Summers 1 107 1 Accounting 102 Gillian Hall 1 107 1 Accounting 103 William Hancock 2 100 2 Marketing 104 Sheila Tunning 3 105 3 Finance 105 Waylon Hart 3 100 3 Finance 106 Jennifer Albacore 3 105 3 Finance 107 Harold Spring 1 100 1 Accounting 108 Lenny Carling NULL 100 NULL NULL 4 Sales NULL NULL 5 IT

Cross Join Cartesian product The size of a Cartesian product is the number of the rows in the first table multiplied by the number of rows in the second table SELECT * FROM EMPLOYEE CROSS JOIN DEPARTMENT This table will have 45 rows and seven columns

Self Joining the table itself called self join or theta join. A self join is used to retrieve the records having some relation or similarity with other records in the same table. Need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause. SELECT e 1. Employee. Number, e 1. First. Name, e 1. Last. Name, e 2. Employee. Number as Manager. Id, e 2. First. Name, e 2. Last. Name FROM EMPLOYEE e 1 INNER JOIN EMPLOYEE e 2 ON e 1. Manager. Id = e 2. Employee. Number First. Name Last. Name Manager. Id First. Name Last. Name 101 Geraldine Summers 107 Harold Spring 102 Gillian Hall 107 Harold Spring 103 William Hancock 100 Sheldon White 104 Sheila Tunning 105 Waylon Hart 100 Sheldon White 106 Jennifer Albacore 105 Waylon Hart 107 Harold Spring 100 Sheldon White 108 Lenny Carling 100 Sheldon White

SELECT CONCAT(e 1. Employee. Number, ' - ', e 1. First. Name, ' ', e 1. Last. Name) as Employee, CONCAT(e 2. Employee. Number, ' - ', e 2. First. Name, ' ', e 2. Last. Name) as Manager FROM EMPLOYEE e 1 INNER JOIN EMPLOYEE e 2 ON e 1. Manager. Id = e 2. Employee. Number Employee Manager 101 – Geraldine Summers 107 – Harold Spring 102 – Gillian Hall 107 – Harold Spring 103 – William Hancock 100 – Sheldon White 104 – Sheila Tunning 105 – Waylon Hart 100 – Sheldon White 106 – Jennifer Albacore 105 – Waylon Hart 107 – Harold Spring 100 – Sheldon White 108 – Lenny Carling 100 – Sheldon White

Comparison of SQL Subquery and Join • A join is an alternative to expressing many subqueries • For example, “Classes taken by undergrads? ” SELECT FROM WHERE AND DISTINCT ENROLLMENT. Class. Name ENROLLMENT, STUDENT ENROLLMENT. Student. Number = STUDENT. SID STUDENT. Grade. Level NOT = ‘GR’

• As a subquery SELECT FROM WHERE DISTINCT Class. Name ENROLLMENT Student. Number IN (SELECT SID FROM STUDENT WHERE Grade. Level NOT = ‘GR’)

• If we want to know: “Class names and grade levels of undergrads? ” SELECT FROM WHERE AND DISTINCT ENROLLMENT. Class. Name, SUDENT. Grade. Level ENROLLMENT, STUDENT ENROLLMENT. Student. Number = STUDENT. SID STUDENT. Grade. Level NOT = ‘GR’ BA 200 SO CS 150 SN BA 200 FR BF 410 FR CS 250 FR BA 200 SN • Why cannot a subquery be used?

• Thus in the previous subquery example: SELECT FROM WHERE DISTINCT Class. Name ENROLLMENT Student. Number IN (SELECT SID FROM STUDENT WHERE Grade. Level NOT = ‘GR’) • This would not work: SELECT FROM WHERE DISTINCT ENROLLMENT. Class. Name, STUDENT. Grade. Level ENROLLMENT Student. Number IN (SELECT SID FROM STUDENT WHERE Grade. Level NOT = ‘GR’)

Creating a View A view is a virtual table based on the result of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. A view can consist of table columns, aggregates, constants, and computed columns. SYNTAX: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

SELECT STUDENT. SID, STUDENT. Name, ENROLLMENT. Class. Name FROM STUDENT, ENROLLMENT 100 JONES BD 445 WHERE STUDENT. SID = ENROLLMENT. Student. Number 150 PARKS BA 200 BAKER BD 445 200 BAKER CS 250 300 BAKER CS 125 400 RYE BA 200 SELECT STUDENT. SID, STUDENT. Name, ENROLLMENT. Class. Name 400 RYE BF 410 FROM STUDENT, ENROLLMENT 400 RYE CS 250 WHERE STUDENT. SID = ENROLLMENT. Student. Number 450 JONES BA 200 CREATE VIEW Student. Class AS SELECT STUDENT. SID, STUDENT. Name, ENROLLMENT. Class. Name FROM Server 1. Compant. Data. dbo. STUDENT, Server 2. Compant. Data. dbo. ENROLLMENT WHERE STUDENT. SID = ENROLLMENT. Student. Number

SELECT DISTINCT Name, MIN(Rate) OVER (PARTITION BY edh. Department. ID) AS Min. Salary, MAX(Rate) OVER (PARTITION BY edh. Department. ID) AS Max. Salary, AVG(Rate) OVER (PARTITION BY edh. Department. ID) AS Avg. Salary, COUNT(edh. Business. Entity. ID) OVER (PARTITION BY edh. Department. ID) AS Emps. Per. Dept FROM HR. Employee. Pay. History eph, HR. Employee. Department. History edh, HR. Department d WHERE eph. Business. Entity. ID = edh. Business. Entity. ID AND d. Department. ID = edh. Department. ID AND edh. End. Date IS NULL ORDER BY Name Min. Salary Max. Salary Avg. Salary Emps. Per. Dept -----------------------------Executive 39. 06 125. 50 68. 3034 4 Finance 13. 4615 43. 2692 23. 935 10 Human Resources 13. 9423 27. 1394 18. 0248 6 Information Services 27. 4038 50. 4808 34. 1586 10 Marketing 13. 4615 37. 50 18. 4318 11 Production 6. 50 84. 1346 13. 5537 195 Purchasing 9. 86 30. 00 18. 0202 14 Research and Development 40. 8654 50. 4808 43. 6731 4 Sales 23. 0769 72. 1154 29. 9719 18 Shipping and Receiving 9. 00 19. 2308 10. 8718 6

Advantages of views: 1. Database Views can represent a subset of the data contained in a table and thus can limit the degree of exposure of the underlying tables to the outside world. A user may have permission to query the view, while denied access to the rest of the base table. 2. Views can join and simplify multiple tables into a single virtual table. 3. Views can act as aggregated tables, where the database engine aggregates data ( sum , average, etc. ) and presents the calculated results as part of the data. 4. Views can hide the complexity of data. For example, a view could appear as Sales 2000 or Sales 2001, transparently partitioning the actual underlying table. 5. Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents. 6. Depending on the SQL engine used, views can provide extra security.

EXISTS and NOT EXISTS • Logical operators SELECT FROM WHERE DISTINCT Student. Number ENROLLMENT A EXISTS (SELECT * FROM ENROLLMENT B WHERE A. Student. Number = B. Student. Number AND A. Class. Name NOT = B. Class. Name)

• Both query and subquery refer to the same table • Two uses assigned a different name • What is the meaning of the subquery?

• General procedure: • First row in A is compared with first row in B • First Row in A compared with second row in B 200 400

• NOT EXISTS operator SELECT FROM WHERE STUDENT. Name STUENT NOT EXISTS (SELECT * FROM ENROLLMENT WHERE NOT EXISTS (SELECT * FROM CLASS WHERE CLASS. Name = ENROLLMENT. Class. Name AND ENROLLMENT. Student. Number = STUDENT. SID)) • Query has three parts

CONCAT Enables the concatenation, or linking together, of two or more string values into a single string. The output is VCHAR or n. VARCHAR. Syntax: CONCAT(string_value 1, string_value 2, ……string_value. N) STUDENT SID Name Major Grade. Level Age 100 JONES HISTORY GR 21 150 PARKS ACCOUNTING SO 19 200 BAKER MATH GR 50 250 GLASS HISTORY SN 50 300 BAKER ACCOUNTING SN 41 350 RUSSELL MATH JR 20 400 RYE ACCOUNTING FR 18 450 JONES HISTORY SN 24

SELECT CONCAT(SID, ‘-’, Name) as ‘Number & Name’, Major FROM STUDENT Number & Name Major 100 -JONES HISTORY 150 -PARKS ACCOUNTING 200 -BAKER MATH 250 -GLASS HISTORY 300 -BAKER ACCOUNTING 350 -RUSSELL MATH 400 -RYE ACCOUNTING 450 -JONES HISTORY

Changing Data Inserting Data • Inserting a single row INSERT INTO ENROLLMENT VALUES (400, ‘BD 445’, 44)

• Some data in a row not known INSERT INTO ENROLLMENT (Student. Number, Class. Name) VALUES (400, ‘BD 445’) • Mass copy rows INSERT INTO JUNIOR (SID, Name, Major) SELECT SID, Name, Major FROM STUDENT WHERE Grade. Level = ‘JR’

Deleting Data • Can be deleted one at a time DELETE WHERE STUDENT. SID = 100

• Groups of rows can be deleted DELETE WHERE ENROLLMENT. Student. Number IN (SELECT STUDENT. SID FROM STUDENT WHERE STUDENT. Major = ‘Accounting’) STUDENT. Major = ‘Accounting’

Modifying Data • Rows can be modified one at a time UPDATE SET WHERE ENROLLMENT Position. Number = 44 SID = 400 UPDATE SET WHERE ENROLLMENT Position. Number = MAX (Position. Number) + 1 SID = 400