Agenda for Class - 02/28/2013

Introduce SQL data access queries (1 table):

– All rows.– Aggregate functions and summary output

with the GROUP BY statement.Login to SQL Server 2012 Management Studio.

Using the “file” tab, open a file called:k: drive - classdata:\is475\create-emp1.sql

Look at and then Execute the SQL code in that file in Management Studio.

1

Familiarize yourself with the data

Look at the table called “emp1” in Object Explorer.– Check out the columns.– There is no primary key.

Execute the SQL SELECT statement to look at all the rows and columns in emp1.

SELECT *FROMemp1;

2

Get the file from the class website

This lab exercise uses a handout available on the class website.– SQL Query Overview (linked to 02/26)

I recommend a printed version, but electronic will work.

Turn to pg. 6 on the SQL Query Overview handout.

3

4

Structure of the SELECT statement

SELECT [all or distinct] FROM (table) WHERE (condition)GROUP BY (grouping fields)HAVING (condition)ORDER BY (sort fields)

Order of Actual Execution:1) FROM2) WHERE3) GROUP BY4) HAVING5) SELECT6) ORDER BY

Referred to as the “SELECT LIST”

When a SELECT statement is executed, the result is referred to as a “result table”. It is a memory-based table.

Type and execute the following SELECT statements

5

SELECT ename, salary, deptno, hiredate

FROM emp1;

SELECT lower(ename), salary, deptno, hiredate

FROM emp1;

SELECT lower(ename) “Employee Name”, salary, deptno “Department Number”, hiredate

FROM emp1;

SELECT lower(ename) “Employee Name”, salary, deptno “Department Number”, hiredate

FROM emp1ORDER BY hiredate;

Add a function

Add column aliases

Sort the result table

Additional single table queries

6

SELECT lower(ename) “Employee Name”, salary, salary * 1.05 “Salary Boost”, deptno “Department Number”, hiredate

FROM emp1WHERE deptno = ‘10’ORDER BY hiredate;

SELECT lower(ename) “Employee Name”, salary, salary * 1.05 “Salary Boost”, deptno “Department Number”, hiredate

FROM emp1ORDER BY hiredate;

Do a calculation in the SELECT list.

Add a condition to eliminate rows from the result table

New type of query – SELECT DISTINCT

7

SELECT DISTINCT deptnoFROM emp1;

SELECT DISTINCT enameFROM emp1;

SELECT DISTINCT salaryFROM emp1;

New type of query - aggregationSELECT AVG(salary)FROM emp1;

SELECT ROUND(AVG(salary),2)FROM emp1;

Use the ROUND function to perform both a

mathematical rounding operation and truncate the result to a set number of digits after the decimal

point

8

SELECT ROUND(AVG(salary),2), SUM(salary)FROM emp1WHERE deptno = 10; Add a

condition

SELECT ROUND(AVG(salary),2),SUM(salary)

FROM emp1; Put in another aggregation

New type of query – Grouped Output

SELECT deptno, SUM(salary)

FROM emp1GROUP BY deptno;

9

SELECT deptno, SUM(salary)

FROM emp1GROUP BY deptnoHAVING SUM(salary) > 15000ORDER BY 2;

SELECT deptno, SUM(salary)

FROM emp1GROUP BY deptnoORDER BY 2;

Sort the result table by position of column rather than column name.

Eliminate some of the groups with a condition

Time to learn much more on your own!

Start on pg. 10 of the handout SQL Query Overview handout.

Type in the code as shown on the handout and view the results.

Ask Robert or me why something works (or doesn’t work) if you have questions along the way. Feel free to ask questions of us!!!

Go through pgs. 10-18 of the handout, just typing in the code, looking at the output, comparing it to the output on the handout, and becoming familiar with the syntax.

After completing through pg. 18 come back to this handout. 10

Concatenate fields and literals

SELECT DATENAME(month, hiredate) + ' is the month that ' + ename + ' joined the company'FROM emp1

SELECT DATENAME(month, hiredate) + ' is the month that ' + lower(ename) + ' joined the company'FROM emp1

Modify the case of a field in the result table:

11

Combine an employee’s last name and job into a single column.

SELECT SUBSTRING(ename, 1, CHARINDEX(',' , ename)-1)+' is '+jobFROM emp1

Functions can be nested

Parsing out only the last name and first initial of the employee name and putting a period after the first initial:

SELECT empno "Employee Number",SUBSTRING(ename,1, CHARINDEX(',', ename)-1) + ', ' + SUBSTRING(ename, CHARINDEX(',', ename) +2, 1)+ '. '"Employee Name",deptno "Department Number",phone "Phone Number"

FROM emp1

12

CASE FunctionThe CASE function evaluates a condition, or a list of conditions, and returns a value. For example, let’s say you want to create a new column called “SalaryRating”. You want the column to say “Big Salary” if a person’s salary is greater than 3000 and “Little Salary” if a person’s salary is less than or equal to 3000.

SELECT ename,salary,CASE

WHEN salary > 3000THEN 'Big Salary'ELSE 'Little Salary'END SalaryRating

FROM emp1

13

Expand the conditions in CASEImagine that “SalaryRating” should have four possibilities. A salary greater than 5000 should be “Really Big Salary”, between 3500 to 5000 should be “Big Salary,” between 2000 to 3499.99 should be “Mediocre Salary,” and less than 2000 should be “Pittance Pay”

select ename,salary,

CASE WHEN salary > 5000THEN 'Really Big Salary'WHEN salary >= 3500THEN 'Big Salary'WHEN salary >= 2000THEN 'Mediocre Salary'ELSE 'Pittance Pay'

END SalaryRating,hiredate

FROM emp1

14

Explore conditional queries

The WHERE clause allows you to eliminate rows in the result table from the underlying table. The underlying table (emp1) remains unchanged.

Example:SELECT empno,

ename,deptno,salary “Current Salary",salary* 1.15 “New Salary“

FROM emp1WHERE salary > 3000;

15

Standard relational operatorsSELECT ename, salary, deptnoFROM emp1WHERE salary > 2000;

SELECT ename, salary, deptnoFROM emp1WHERE deptno=30 and salary <=3000;

SELECT ename, salary, deptnoFROM emp1WHERE salary > 2000AND deptno = 10;

SELECT ename, salary, deptnoFROM emp1WHERE salary > 4000 or deptno = 10;

16

Additional relational operators

SELECT ename, salary, deptnoFROM emp1WHERE hiredate BETWEEN ('01-jan-2008') AND ('31-dec-2012');

Means the same as:

SELECT ename, salary, deptnoFROM emp1WHERE hiredate >= ('01-jan-2008') AND hiredate <= ('31-dec-2012');

SELECT ename, salary, deptnoFROM emp1WHERE deptno IN (10, 20);

Means the same as:

SELECT ename, salary, deptnoFROM emp1WHERE deptno = 10 OR deptno = 20;

17

Relational operators and datesMatch a date in a table to the current year in the GETDATE() function:

SELECT ename, salary, hiredate

FROM emp1WHERE DATEPART(yyyy, hiredate) = DATEPART(yyyy, GETDATE());

Means the same as:

SELECT ename, salary, hiredateFROM emp1WHERE year(hiredate) = year(GETDATE())

Now match up both the month and a year to the current month and year in GETDATE():

SELECT ename, salary, hiredateFROM emp1WHERE YEAR(hiredate) = YEAR(GETDATE())AND MONTH(GETDATE()) = MONTH(hiredate)

18

What is an Aggregate Function?

A way to summarize data and provide more meaningful and informative output from the database. Sometimes referred to as “summary queries.”

Aggregate/group functions differ from single row SELECT statements:– A SELECT statement processes every row in the underlying table. The

result table (unless a WHERE clause is used) contains one row per row in the underlying table.

– An aggregate function collects data from multiple rows and produces summarized data in the result table. There should be one row in the result table per aggregate group.

If an aggregate function is run on the whole table, without grouping, it generates a single row result table.

If an aggregate function is run with grouping, then it generates one row per group in the result table.

19

Function Description of What is ReturnedAVG Average value of a numeric column; ignores

null values

COUNT Number of rows. When * is used, all rows are returned (including null values and duplicate rows)

MAX Maximum value of a column; ignores null values

MIN Minimum value of a column; ignores null values

SUM Totals the value of a numeric column; ignores null values

20

What are the aggregate functions in SQL?

Counting Rows

 

SELECT COUNT(*)FROM emp1;

SELECT COUNT(*)FROM emp1WHERE deptno = 10;

 SELECT COUNT(*)FROM emp1WHERE salary > 2000 and deptno = 10;

SELECT COUNT(DISTINCT deptno)FROM emp1;  21

Finding Minimum and Maximum Values

SELECT MIN(hiredate)FROM emp1;  SELECT MAX(hiredate)FROM emp1;

SELECT MIN(ename)FROM emp1;  SELECT MAX(hiredate)FROM emp1WHERE deptno = 10;

22

Aggregates with Calculations/Functions

SELECT MAX(salary + ISNULL(comm,0))FROM emp1;

SELECT MAX(DATEDIFF(mm, hiredate, GETDATE())FROM emp1;

SELECT COUNT(salary), SUM(salary), MIN(salary)

FROM emp1WHERE deptno = 10 and salary < 4000;

23

Combining aggregate functions

Creating summary output by grouping

SELECT deptno, SUM(salary)

FROM emp1GROUP BY deptno;

SELECT deptno, SUM(salary)

FROM emp1WHERE salary > 2000GROUP BY deptno;

Eliminates rows before the grouping

occurs

24

Summary output with conditions

SELECT deptno, SUM(salary)FROM emp1GROUP BY deptnoHAVING SUM(salary) > 6000;

SELECT deptno, SUM(salary)FROM emp1GROUP BY deptnoHAVING AVG(salary) > 2000; SELECT deptno, SUM(salary)FROM emp1GROUP BY deptnoHAVING deptno = 30

The HAVING statement uses

aggregate functions for the condition or

grouped attributesIt eliminates rows from

the group. The HAVING statement

must reference either an aggregate function or the field(s) in the

GROUP BY statement.

25

Multi-attribute grouping

SELECT deptno, job, SUM(salary), AVG(salary)

FROM emp1GROUP BY deptno, job;

26

You are ready to start your homework assignment!!

Challenge exercise: make the phone number “prettier”

If you got this far in the exercise you are either an experienced SQL programmer, or you can type at a frightenly fast rate!!

Write a query that lists the ename, salary, commission, and phone of each employee. Sort the result table by ename.

Think about how you would change the output format of the telephone number so that it is in the format (hint – there is no special code, you have to use SUBSTRING, CHARINDEX and concatenation to format it correctly):

(775) 784-1234

27