1 / 27

Agenda for Class - 02/28/2013

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 : is475create-emp1.sql

ona
Download Presentation

Agenda for Class - 02/28/2013

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. 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.

  2. 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 * FROM emp1;

  3. 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.

  4. Structure of the SELECT statement SELECT[all or distinct] FROM (table) WHERE(condition) GROUP BY (grouping fields) HAVING (condition) ORDER BY (sort fields) Referred to as the “SELECT LIST” Order of Actual Execution: FROM WHERE GROUP BY HAVING SELECT ORDER BY When a SELECT statement is executed, the result is referred to as a “result table”. It is a memory-based table.

  5. Type and execute the following SELECT statements SELECT ename, salary, deptno, hiredate FROM emp1; SELECT lower(ename), salary, deptno, hiredate FROM emp1; Add a function SELECT lower(ename) “Employee Name”, salary, deptno “Department Number”, hiredate FROM emp1; Add column aliases SELECT lower(ename) “Employee Name”, salary, deptno “Department Number”, hiredate FROM emp1 ORDER BY hiredate; Sort the result table

  6. Additional single table queries SELECT lower(ename) “Employee Name”, salary, salary * 1.05 “Salary Boost”, deptno “Department Number”, hiredate FROM emp1 ORDER BY hiredate; Do a calculation in the SELECT list. SELECT lower(ename) “Employee Name”, salary, salary * 1.05 “Salary Boost”, deptno “Department Number”, hiredate FROM emp1 WHERE deptno = ‘10’ ORDER BY hiredate; Add a condition to eliminate rows from the result table

  7. New type of query – SELECT DISTINCT SELECT DISTINCT deptno FROM emp1; SELECT DISTINCT ename FROM emp1; SELECT DISTINCT salary FROM emp1;

  8. New type of query - aggregation 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 SELECT AVG(salary) FROM emp1; SELECT ROUND(AVG(salary),2) FROM emp1; SELECT ROUND(AVG(salary),2), SUM(salary) FROM emp1; Put in another aggregation SELECT ROUND(AVG(salary),2), SUM(salary) FROM emp1 WHERE deptno= 10; Add a condition

  9. New type of query – Grouped Output SELECT deptno, SUM(salary) FROM emp1 GROUP BY deptno; SELECT deptno, SUM(salary) FROM emp1 GROUP BY deptno ORDER BY 2; Sort the result table by position of column rather than column name. SELECT deptno, SUM(salary) FROM emp1 GROUP BY deptno HAVING SUM(salary) > 15000 ORDER BY 2; Eliminate some of the groups with a condition

  10. 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.

  11. Concatenate fields and literals SELECT DATENAME(month, hiredate) + ' is the month that ' + ename + ' joined the company' FROM emp1 Modify the case of a field in the result table: SELECT DATENAME(month, hiredate) + ' is the month that ' + lower(ename) + ' joined the company' FROM emp1 Combine an employee’s last name and job into a single column. SELECT SUBSTRING(ename, 1, CHARINDEX(',' , ename)-1)+' is '+job FROM emp1

  12. 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

  13. CASE Function The 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 > 3000 THEN 'Big Salary' ELSE 'Little Salary' END SalaryRating FROM emp1

  14. Expand the conditions in CASE Imagine 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 > 5000 THEN 'Really Big Salary' WHEN salary >= 3500 THEN 'Big Salary' WHEN salary >= 2000 THEN 'Mediocre Salary' ELSE 'Pittance Pay' END SalaryRating, hiredate FROM emp1

  15. 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 emp1 WHERE salary > 3000;

  16. Standard relational operators SELECT ename, salary, deptno FROM emp1 WHERE salary > 2000; SELECT ename, salary, deptno FROM emp1 WHERE deptno=30 and salary <=3000; SELECT ename, salary, deptno FROM emp1 WHERE salary > 2000 AND deptno = 10; SELECT ename, salary, deptno FROM emp1 WHERE salary > 4000 or deptno = 10;

  17. Additional relational operators SELECT ename, salary, deptno FROM emp1 WHERE hiredate BETWEEN ('01-jan-2008') AND ('31-dec-2012'); Means the same as: SELECT ename, salary, deptno FROM emp1 WHERE hiredate >= ('01-jan-2008') AND hiredate <= ('31-dec-2012'); SELECT ename, salary, deptno FROM emp1 WHERE deptno IN (10, 20); Means the same as: SELECT ename, salary, deptno FROM emp1 WHERE deptno = 10 OR deptno = 20;

  18. Relational operators and dates Match a date in a table to the current year in the GETDATE() function: SELECTename, salary, hiredate FROM emp1 WHERE DATEPART(yyyy, hiredate) = DATEPART(yyyy, GETDATE()); Means the same as: SELECT ename, salary, hiredate FROM emp1 WHERE year(hiredate) = year(GETDATE()) Now match up both the month and a year to the current month and year in GETDATE(): SELECT ename, salary, hiredate FROM emp1 WHERE YEAR(hiredate) = YEAR(GETDATE()) AND MONTH(GETDATE()) = MONTH(hiredate)

  19. 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.

  20. What are the aggregate functions in SQL?

  21. Counting Rows SELECT COUNT(*) FROM emp1; SELECT COUNT(*) FROM emp1 WHERE deptno = 10; SELECT COUNT(*) FROM emp1 WHERE salary > 2000 and deptno = 10; SELECT COUNT(DISTINCT deptno) FROM emp1;

  22. Finding Minimum and Maximum Values SELECT MIN(hiredate) FROM emp1; SELECT MAX(hiredate) FROM emp1; SELECT MIN(ename) FROM emp1; SELECT MAX(hiredate) FROM emp1 WHERE deptno = 10;

  23. Aggregates with Calculations/Functions SELECT MAX(salary + ISNULL(comm,0)) FROM emp1; SELECT MAX(DATEDIFF(mm, hiredate, GETDATE()) FROMemp1; Combining aggregate functions SELECT COUNT(salary), SUM(salary), MIN(salary) FROM emp1 WHERE deptno = 10 and salary < 4000;

  24. Creating summary output by grouping SELECT deptno, SUM(salary) FROM emp1 GROUP BY deptno; SELECT deptno, SUM(salary) FROM emp1 WHERE salary > 2000 GROUP BY deptno; Eliminates rows before the grouping occurs

  25. Summary output with conditions The HAVING statement uses aggregate functions for the condition or grouped attributes It eliminates rows from the group. The HAVING statement must reference either an aggregate function or the field(s) in the GROUP BY statement. SELECT deptno, SUM(salary) FROM emp1 GROUP BY deptno HAVING SUM(salary) > 6000; SELECT deptno, SUM(salary) FROM emp1 GROUP BY deptno HAVING AVG(salary) > 2000; SELECT deptno, SUM(salary) FROM emp1 GROUP BY deptno HAVING deptno = 30

  26. Multi-attribute grouping SELECT deptno, job, SUM(salary), AVG(salary) FROM emp1 GROUP BY deptno, job; You are ready to start your homework assignment!!

  27. 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

More Related