Chapter 8: Speaking SQL
GROUP BY Departments.Department
HAVING COUNT(Employees.Name) >= 2
The results show us that there are two departments that have more than two employees:
Department |
HowManyEmployees |
------------------------------------------------- |
---------------- |
Engineering |
2 |
Marketing |
2 |
(2 row(s) affected) |
|
The SUM, AVG, MIN, and MAX Functions
There are other common aggregate functions you’re likely to need when building more complex applications:
SUM
Unlike the COUNT function, which returns a value that reflects the number of rows returned by a query, the SUM function performs a calculation on the data within those returned rows.
AVG
The AVG function receives a list of numbers as its arguments, and returns the average of these numbers.
MIN, MAX
The MIN and MAX functions enable you to find the smallest and largest values in a group, respectively.
These functions are great for the statistical analysis of records within the database. For example, it wouldn’t be difficult to use them to put together a web-based accounting application that monitored daily sales, and gave us totals, averages, and the minimum and maximum values for certain products sold.
Updating Existing Data
Okay, so SQL is so great for querying existing data! But how are we supposed to add data to the tables in the first place? We can’t exactly ask Dorknozzle employees to add data to our tables using SQL Server Management Studio, can we? We
322
The INSERT Statement
need to learn how to add, update, and delete data inside our database programatically.
The basic SQL statements that handle these actions are INSERT, UPDATE, and DELETE. Let’s put them to work!
The INSERT Statement
Here's a very simple example of INSERT in action:
INSERT INTO Departments (Department)
VALUES ('Cool New Department')
Executing this command adds a new department, named Cool New Department, to our database. When we add a new row to a table, we must supply data for all the columns that don’t accept NULL, don’t have a default value, and aren’t IDENTITY columns that are automatically filled by the database (as in this example).
If, in Chapter 7, you used the database scripts to create database structures and insert data, you probably noticed that the script contained many INSERT commands; these populated the tables with the sample data.
The INSERT statement generally consists of the following components:
INSERT INTO
These keywords indicate that this statement will add a new record to the database. The INTO part is optional, but it can make your commands easier to read.
table name
We provide the name of the table into which we want to insert the values.
column names
We also list the names of the columns for which we’ll be supplying data in this statement. We separate these column names with commas and enclose the list in parentheses.
VALUES
This keyword comes between the list of columns and their values.
values
We provide a list of values that we wish to supply for the columns listed above, respectively.
323
Chapter 8: Speaking SQL
Try the above SQL statement. Then, to read the new list of records, execute the following:
SELECT DepartmentID, Department
FROM Departments
All records in the Departments table will be displayed, along with our Cool New Department and its automatically-generated DepartmentID.
Identity Values
To obtain programatically the identity value that we just generated, we can use the scope_identity function like this:
SELECT scope_identity()
The UPDATE Statement
We use the UPDATE statement to make changes to existing records within our database tables. The UPDATE statement requires certain keywords, and usually a WHERE clause, in order to modify particular records. Consider this code:
UPDATE Employees
SET Name = 'Zak Christian Ruvalcaba'
WHERE EmployeeID = 1
This statement would change the name of the employee whose EmployeeID is 1. Let’s break down the UPDATE statement's syntax:
UPDATE
This clause identifies the statement as one that modifies the named table in the database.
table name
We give the name of the table we’re updating.
SET
The SET clause specifies the columns we want to modify, and gives their new values.
column names and values
We provide a list of column names and values, separated by commas.
324
The DELETE Statement
WHERE condition(s)
This condition specifies which records are being updated.
Updating Records
Be sure always to include a WHERE clause in your UPDATE statement! If you fail to do so, all the records will be updated, which is not usually what you want!
The DELETE Statement
The DELETE statement removes records from the database. You could use it to delete all records from the Departments table like so:
DELETE
FROM Departments
Fortunately, executing this command will throw an error if the foreign key that links the Departments and Employees tables is in place, because removing the departments would leave the employees referencing nonexistent departments, which would make your data inconsistent (note that the reverse isn’t true: you could delete all the employees if you wanted to, but please don’t!).
In case you’re curious, here’s the error message that would be generated by the previous DELETE command:
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Employees_Departments". The conflict occurred in database "Dorknozzle", table "dbo.Employees", column 'DepartmentID'.
The statement has been terminated.
You could just as easily delete that new department you created earlier:
DELETE
FROM Departments
WHERE Department = 'Cool New Department'
Real-world References
Remember that in real-world scenarios, items should be referenced by their IDs, not by name (as is shown in the example above). Also note that if you mistype the name of a department when executing that command, no rows will be affected.
325