Скачать презентацию Advanced SQL Aggregations Grouping SQL Functions DDL Svetlin
06-Advanced-SQL.pptx
- Количество слайдов: 55
Advanced SQL Aggregations, Grouping, SQL Functions, DDL Svetlin Nakov Manager Technical Training http: //nakov. com Telerik Software Academy http: //academy. telerik. com
Table of Contents 1. Nested SELECT Statements 2. Aggregating Data Group Functions and GROUP BY 3. Microsoft SQL Server Functions 4. SQL Server Data Types 5. Data Definition Language (DDL) 6. Creating Tables in MS SQL Server 7. Naming Conventions 2
SQL Language Nested SELECT Statements SQL SQL
Nested SELECT Statements SELECT statements can be nested in the where clause SELECT First. Name, Last. Name, Salary FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM Employees) SELECT First. Name, Last. Name, Department. ID, Salary FROM Employees WHERE Department. ID IN (SELECT Department. ID FROM Departments WHERE Name='Sales') Note: always prefer joins to nested SELECT statements for better performance 4
Nested SELECT Statements with Table Aliases Tables from the main SELECT can be referred in the nested SELECT by aliases Example: Find the maximal salary for each department and the name of the employee that gets it SELECT First. Name, Last. Name, Department. ID, Salary FROM Employees e WHERE Salary = (SELECT MAX(Salary) FROM Employees WHERE Department. ID = e. Department. ID) ORDER BY Department. ID 5
Using the EXISTS Operator Using the EXISTS operator in SELECT statements Find all employees with managers from the first department SELECT First. Name, Last. Name, Employee. ID, Manager. ID FROM Employees e WHERE EXISTS (SELECT Employee. ID FROM Employees m WHERE m. Employee. ID = e. Manager. ID AND m. Department. ID = 1) 6
SQL Language Aggregating Data with Group Functions
Group Functions Group functions operate over sets of rows to give one single result (per group) Employee. ID Salary 1 2 3 12500, 00 13500, 00 43300, 00 4 5. . . 29800, 00 25000, 00. . . MAX(Salary) 125500, 00 8
Group Functions in SQL COUNT(*) – count of the selected rows SUM(column) – sum of the values in given column from the selected rows AVG(column) – average of the values in given column MAX(column) – the maximal value in given column MIN(column) – the minimal value in given column 9
AVG() and SUM() Functions You can use AVG() and SUM() only for numeric data types SELECT AVG(Salary) [Average Salary], MAX(Salary) [Max Salary], MIN(Salary) [Min Salary], SUM(Salary) [Salary Sum] FROM Employees WHERE Job. Title = 'Design Engineer' Average Salary Max Salary Min Salary Sum 32700. 00 98100. 00 10
MIN() and MAX() Functions You can use MIN() and MAX() for almost any data type (int, datetime, varchar, . . . ) SELECT MIN(Hire. Date) Min. HD, MAX(Hire. Date) Max. HD FROM Employees Min. HD Max. HD 1996 -07 -31 2003 -06 -03 Displaying the first and last employee's name in alphabetical order: SELECT MIN(Last. Name), MAX(Last. Name) FROM Employees 11
The COUNT(…) Function COUNT(*) returns the number of rows in the result record set SELECT COUNT(*) Cnt FROM Employees WHERE Department. ID = 3 Cnt 18 COUNT(expr) returns the number of rows with non-null values for the expr SELECT COUNT(Manager. ID) Mgr. Count, COUNT(*) All. Count FROM Employees WHERE Department. ID = 16 Mgr. Count 1 All. Count 2 12
Group Functions and NULLs Group functions ignore NULL values in the target column SELECT AVG(Manager. ID) Avg, SUM(Manager. ID) / COUNT(*) Avg. All FROM Employees Avg 108 Avg. All 106 If each NULL value in the Manager. ID column were considered as 0 in the calculation, the result would be 106 13
Group Functions in Nested Queries Find the earliest hired employee for each department SELECT e. First. Name, e. Last. Name, e. Hire. Date, d. Name FROM Employees e JOIN Departments d ON e. Department. ID = d. Department. ID WHERE e. Hire. Date = (SELECT MIN(Hire. Date) FROM Employees WHERE Department. ID = d. Department. ID) First. Name Last. Name Hire. Date Name Guy Kevin Roberto Gilbert Brown Tamburello 1998 -07 -31 00: 00 Production 1999 -02 -26 00: 00 Marketing 1999 -12 -12 00: 00 Engineering 14
SQL Language Group Functions and the GROUP BY Statement
Creating Groups of Data Employees Department. ID Salary 12 12 12 2 2 16 16. . . 10300 16800 10300 17800 28800 25000 29800 25000 125500 60100. . . 72000 Depart. SUM ment. ID (Salary) 108600 12 2 16. . . 72000 108600 185600. . . 185600 16
The GROUP BY Statement We can divide rows in a table into smaller groups by using the GROUP BY clause The SELECT + GROUP BY syntax: SELECT [WHERE
The GROUP BY Statement (2) Example of grouping data: SELECT Department. ID, SUM(Salary) as Salaries. Cost FROM Employees GROUP BY Department. ID Salaries. Cost 12 2 16. . . 72000 108600 185600. . . The GROUP BY column is not necessary needed to be in the SELECT list 18 
Grouping by Several Columns Depart. Job. Title ment. ID Salary 11 Network Manager 11 Network 32500 Administrator 11 11 39700 Network 32500 Administrator Database 38500 Administrator 11 Database 38500 Administrator 10 Accountant 26400 10 Finance Manager 43300 . . . 39700 Depart Job. Title ment. ID Salary Network Manager Network 65000 Administrator 11 Database 77000 Administrator 10 77000 11 11 65000 Accountant 52800 10 Finance Manager 43300 . . 39700 . . . 52800 43300 19 
Grouping by Several Columns – Example of grouping data by several columns: SELECT Department. ID, Job. Title, SUM(Salary) as Salaries, COUNT(*) as Count FROM Employees GROUP BY Department. ID, Job. Title Department. ID Job. Title Salaries Count 2 2 7 7 Senior Tool Designer Production Supervisor Production Technician 58600 50000 525000 1926000 2 2 21 157 . . . 20 
Illegal Use of Group Functions This SELECT statement is illegal: SELECT Department. ID, COUNT(Last. Name) FROM Employees Can not combine columns with groups functions unless when using GROUP BY This SELECT statement is also illegal SELECT Department. ID, AVG(Salary) FROM Employees WHERE AVG(Salary) > 30 GROUP BY Department. ID Can not use WHERE for group functions 21 
Restrictions for Grouping When using groups we can select only columns listed in the GROUP BY and grouping functions over the other columns SELECT Department. ID, Job. Title, SUM(Salary) AS Cost, MIN(Hire. Date) as Start. Date FROM Employees GROUP BY Department. ID, Job. Title Can not select columns not listed in the GROUP BY clause It is allowed to apply group functions over the columns in the GROUP BY clause, but has no sense 22 
Using GROUP BY with HAVING Clause HAVING works like WHERE but is used for the grouping functions SELECT Department. ID, COUNT(Employee. ID) as Count, AVG(Salary) Average. Salary FROM Employees GROUP BY Department. ID HAVING COUNT(Employee. ID) BETWEEN 3 AND 5 Department. ID Count Average. Salary 2 12 … 4 5 … 27150 14400 … 23 
Using Grouping Functions and Table Joins Grouping function can be applied on columns from joined tables SELECT COUNT(*) AS Emp. Count, d. Name AS Dept. Name FROM Employees e JOIN Departments d ON e. Department. ID = d. Department. ID WHERE e. Hire. Date BETWEEN '1999 -2 -1' AND '2002 -12 -31' GROUP BY d. Name HAVING COUNT(*) > 5 ORDER BY Emp. Count DESC Emp. Count Dept. Name 95 8 8 Production Finance Information Services 24 
SQL Language SQL Server Functions 
Standard Functions in Microsoft SQL Server Single-row functions String functions Mathematical functions Date functions SQL Conversion functions Multiple-row functions Aggregate functions 26 
COALESCE() Function ISNULL(
String Functions Changing the casing – LOWER, UPPER Manipulating characters – SUBSTRING, LEN, LEFT, RIGHT, LTRIM, REPLACE SELECT Last. Name, LEN(Last. Name) AS Last. Name. Len, UPPER(Last. Name) AS Upper. Last. Name FROM Employees WHERE RIGHT(Last. Name, 3) = 'son' Last. Name. Len Upper. Last. Name Erickson Johnson Munson. . . 8 7 6. . . ERICKSON JOHNSON MUNSON. . . 28 
Other Functions Mathematical Functions – ROUND, FLOOR, POWER, ABS, SQRT, … SELECT FLOOR(3. 14) 3 SELECT ROUND(5. 86, 0) 6. 00 Date Functions – GETDATE, DATEADD, DAY, MONTH, YEAR, … Conversion Functions – CONVERT, CAST SELECT CONVERT(DATETIME, '20051231', 112) 2005 -12 -31 00: 00. 000 -- 112 is the ISO formatting style YYYYMMDD 29 
Combining Functions We can combine functions to achieve more complex behavior SELECT Name AS [Projects Name], COALESCE(CONVERT(nvarchar(50), End. Date), 'Not Finished') AS [Date Finished] FROM Projects Name Date Finished HL Mountain Front Wheel LL Touring Handlebars HL Touring Handlebars LL Road Front Wheel Jun 1 2003 12: 00 AM Not Finished Jun 1 2003 12: 00 AM . . . 30 
SQL Language Data Definition Language (DDL) 
Data Definition Language DDL commands for defining / editing objects CREATE ALTER DROP Data Control Language (DCL) for managing access permissions GRANT REVOKE DENY 32 
Creating Database Objects CREATE command CREATE TABLE 
Creating Objects – More Examples CREATE TABLE Countries ( Country. ID int IDENTITY, Name nvarchar(100) NOT NULL, CONSTRAINT PK_Countries PRIMARY KEY(Country. ID) ) GO CREATE TABLE Cities ( City. ID int IDENTITY, Name nvarchar(100) NOT NULL, Country. ID int NOT NULL, CONSTRAINT PK_Cities PRIMARY KEY(City. ID) ) 34 
Modifying Database Objects ALTER command ALTER TABLE 
Deleting Database Objects DROP command DROP TABLE 
Managing Access Permissions GRANT command GRANT 
Creating Tables in SQL Server Best Practices 
Creating Tables in SQL Server Creating new table: Define the table name Should have good name Define the columns and their types Use proper data type Define the table primary key Use IDENTITY for enabling auto increment of the primary key Define foreign/keys and constraints 39 
Creating Tables in SQL Server – Examples CREATE TABLE Groups ( Group. ID int IDENTITY, Name nvarchar(100) NOT NULL, CONSTRAINT PK_Groups PRIMARY KEY(Group. ID) ) CREATE TABLE Users ( User. ID int IDENTITY, User. Name nvarchar(100) NOT NULL, Group. ID int NOT NULL, CONSTRAINT PK_Users PRIMARY KEY(User. ID), CONSTRAINT FK_Users_Groups FOREIGN KEY(Group. ID) REFERENCES Groups(Group. ID) ) 40 
Transactions Begin / Commit / Rollback Transactions in SQL Server 
What Is Concurrency Control? Pessimistic concurrency (default in SQL Server) Locks table data at each data is modification Concurrent users are blocked until the lock is released Optimistic concurrency (default in My. SQL) No locks are performed when data is being read or changed Concurrent users don’t see the changes until they are committed / rolled-back Supported with SNAPSHOT isolation in SQL Server 42 
Transactions start by executing BEGIN TRANSACTION (or just BEGIN TRAN) Use COMMIT to confirm changes and finish the transaction Use ROLLBACK to cancel changes and abort the transaction Example: BEGIN TRAN DELETE FROM Employees. Projects; DELETE FROM Projects; ROLLBACK TRAN 43 
The Implicit Transactions Option What is implicit transactions mode? Automatically start a new transaction after each commit or rollback Nested transactions are not allowed Transaction must be explicitly completed with COMMIT or ROLLBACK TRANSACTION By default, IMPLICIT_TRANSACITONS setting is switched off SET IMPLICIT_TRANSACTIONS ON 44 
Advanced SQL Questions? 
Exercises 1. Write a SQL query to find the names and salaries of the employees that take the minimal salary in the company. Use a nested SELECT statement. 2. Write a SQL query to find the names and salaries of the employees that have a salary that is up to 10% higher than the minimal salary for the company. 3. Write a SQL query to find the full name, salary and department of the employees that take the minimal salary in their department. Use a nested SELECT statement. 46 
Exercises (2) 4. Write a SQL query to find the average salary in the department #1. 5. Write a SQL query to find the average salary in the "Sales" department. 6. Write a SQL query to find the number of employees in the "Sales" department. 7. Write a SQL query to find the number of all employees that have manager. 8. Write a SQL query to find the number of all employees that have no manager. 9. Write a SQL query to find all departments and the average salary for each of them. 47 
Exercises (3) 10. Write a SQL query to find the count of all employees in each department and for each town. 11. Write a SQL query to find all managers that have exactly 5 employees. Display their first name and last name. 12. Write a SQL query to find all employees along with their managers. For employees that do not have manager display the value "(no manager)". 13. Write a SQL query to find the names of all employees whose last name is exactly 5 characters long. Use the built-in LEN(str) function. 48 
Exercises (4) 14. Write a SQL query to display the current date and time in the following format "day. month. year hour: minutes: seconds: milliseconds". Search in Google to find how to format dates in SQL Server. 15. Write a SQL statement to create a table Users should have username, password, full name and last login time. Choose appropriate data types for the table fields. Define a primary key column with a primary key constraint. Define the primary key column as identity to facilitate inserting records. Define unique constraint to avoid repeating usernames. Define a check constraint to ensure the password is at least 5 characters long. 49 
Exercises (5) 16. Write a SQL statement to create a view that displays the users from the Users table that have been in the system today. Test if the view works correctly. 17. Write a SQL statement to create a table Groups should have unique name (use unique constraint). Define primary key and identity column. 18. Write a SQL statement to add a column Group. ID to the table Users. Fill some data in this new column and as well in the Groups table. Write a SQL statement to add a foreign key constraint between tables Users and Groups tables. 50 
Exercises (6) Write SQL statements to insert several records in the Users and Groups tables. 20. Write SQL statements to update some of the records in the Users and Groups tables. 21. Write SQL statements to delete some of the records from the Users and Groups tables. 22. Write SQL statements to insert in the Users table the names of all employees from the Employees table. Combine the first and last names as a full name. For username use the first letter of the first name + the last name (in lowercase). Use the same for the password, and NULL for last login time. 19. 51 
Exercises (7) Write a SQL statement that changes the password to NULL for all users that have not been in the system since 10. 03. 2010. 24. Write a SQL statement that deletes all users without passwords (NULL password). 23. 25. Write a SQL query to display the average employee salary by department and job title. 26. Write a SQL query to display the minimal employee salary by department and job title along with the name of some of the employees that take it. 27. Write a SQL query to display the town where maximal number of employees work. 52 
Exercises (8) 28. Write a SQL query to display the number of managers from each town. 29. Write a SQL to create table Work. Hours to store work reports for each employee (employee id, date, task, hours, comments). Don't forget to define identity, primary key and appropriate foreign key. Issue few SQL statements to insert, update and delete of some data in the table. Define a table Work. Hours. Logs to track all changes in the Work. Hours table with triggers. For each change keep the old record data, the new record data and the command (insert / update / delete). 53 
Exercises (9) 30. Start a database transaction, delete all employees from the 'Sales' department along with all dependent records from the pother tables. At the end rollback the transaction. 31. Start a database transaction and drop the table Employees. Projects. Now how you could restore back the lost table data? 32. Find how to use temporary tables in SQL Server. Using temporary tables backup all records from Employees. Projects and restore them back after dropping and re-creating the table. 54 
Free Trainings @ Telerik Academy "Web Design with HTML 5, CSS 3 and Java. Script" course @ Telerik Academy html 5 course. telerik. com Telerik Software Academy academy. telerik. com Telerik Academy @ Facebook facebook. com/Telerik. Academy Telerik Software Academy Forums forums. academy. telerik. com