Inserting rows I

Lecture 5

Introduction to SQL language

Last updated: December 10, 2014

Throrought this lecture we will use the following database diagram

Inserting rows I

TheINSERT INTOstatement enables inserting new rows into a table. The basic syntax of this statements is as follows:

insert into table_name [(column_list)] values (value_list)

Inserting rows II

Example

Insert some rows to theEmployeetable. First, we set date format to day-month-year

set dateformat dmy

An insert statement without the list of columns to which values are inserted

insert into Employee

values('John','Smith','08.08.1980','M','JSMITH',null,null) insert into Employee

values('Justine','Parker','24.05.1985','F','JPARKER',1,null)

Inserting rows III

An insert statement with the list of columns

(the value for theaddress_idis omitted, SQL Server provides the nullvalue for it)

Employee(first_name,last_name,birth_date,gender,email,manager_id) values('Agnes','Braun','11.09.1982','F','ABRAUN',1)

TheINSERT INTO statement can be based on theSELECT clause:

INSERT INTO table_name SELECT...

Updating rows I

TheUPDATEstatement allows to update existing rows in a table:

UPDATE table_name

SET column_name1=value1, column_name2=value2,...

[WHERE selection_predicates]

If noWHERE clause is dened all rows in the table are updated.

Example

Increase the salary by 10% for the employees earning between 2000 and 3000.

UPDATE Employment SET salary = salary*1.1

WHERE salary between 2000 and 3000 and end_date is null

Updating rows II

Example

Assign the rst department to the employeeJohn Smith.

UPDATE Employee SET address_id = 1

WHERE first_name = 'John' and last_name = 'Smith'

Deleting rows I

TheDELETEstatement is applied to delete existing rows in a table:

DELETE FROM table_name

[WHERE selection_predicates]

If noWHERE clause is dened all rows are removed from the table.

Example

Remove the tasks assigned to the rst employee.

DELETE FROM Task WHERE emp_id = 1

The basic SELECT statement I

TheSELECTstatement retrieves columns and rows of data from one or many tables (views). The basic syntax of this statement involves only two parts:

select list (it species the columns of the nal result set and implements the projection),

from clause (it determines the target table or tables).

SELECT DISTINCT select_list FROM list_of_tables

The optionalDISTINCTkeyword eliminates duplicates from the result set.

The basic SELECT statement II

Example

Retrieve data from all rows and all columns of theEmployeetable.

The sign*denotes all columns.

SELECT * FROM Employee Example

Display the names of all employees and their birthday's.

SELECT emp_id,first_name, last_name, birth_date FROM Employee

The basic SELECT statement III

Example

Retrieve all dierent rst names.

SELECT DISTINCT first_name FROM Employee

Assigning aliases (auxiliary names) to columns and tables I

Aliases are applied to improve the readability of select statements.

They can be assigned both to table and column names.

There are two ways of dening aliases: with or without the AS keyword:

column_name AS column_alias column_name column_alias table_name AS table_alias table_name table_alias

Assigning aliases (auxiliary names) to columns and tables II

Example

SELECT emp_id AS "Employee identification number"

FROM Employee

If an alias involves only one name then putting it in quotation mark is optional.

Example

Assigning aliases to a table (it is very useful for dening queries involving more than one table):

SELECT d.dept_id AS "Department identification number", name AS "Department name"

FROM Department AS d

Restricting query result I

The above mentioned queries return all rows from a given table.

To limit the number of rows in the query result set the WHERE clause (selection operator) is required. It involves selection predicates (Boolean combination consisting of conditions connected by logical connectivesor,and or not).

The most common form of a selection predicate is: expr op value, whereexpr is a column name,opis a binary operator, value denotes a value compatible with the value stored in expr.

The general form of the select statement with the where clause is as follows:

Restricting query result II

SELECT select_list FROM list_of_tables

WHERE selection_predicates Example (SQLQuery51.sql)

Get employees whose rst name equalsJohn.

Example (SQLQuery52.sql)

Get all females born after January rst 1980.

Null value I

Remind that we use NULL value to specify that the value is undened or unknown.

We consider the Employment table. To explore the meaning of the NULL value let us consider Employment table. First let us modify the table to insert some NULL value into the salary column.

Since the salary column has a constrain which does not allow to insert null value we have to delete this restriction. We can do it using Ms SQL Management Studio.

Null value II

Next, we insert null values. We can do it using MS SQL Management Studio or update or insert command:

update Employment set salary=NULL

where e_id=5 or e_id=11 insert into Employment

values(5,2,3,NULL,'2010-12-05',NULL)

Null value III

Next consider the the following queries select *

from Employment

where Employment.salary>8000

We observe, thatnull values are not included. However, the same is true for the following query:

select *

from Employment

where Employment.salary>8000 or Employment.salary<=8000

Aggregate functions I

SQL Server provides a number of predened aggregate functions that allow to make a summary of the data.

The functions act on a set of values and return a single value.

They appear on the select list or in theHAVING clause.

The main aggregate functions are (DISTINCT forces a function to consider only various values of the given set. ALL is the default and does not eliminate duplicates):

COUNT(*) - it returns the number of values in the set including null values and duplicates.

COUNT(DISTINCT | ALL expr) it returns the number of dierent or all values inexpr.

AVG(DISTINCT | ALL expr)  it computes the average of various or all values of expr.

SUM(DISTINCT | ALL expr) it computes the sum of

Aggregate functions II

MIN(DISTINCT | ALL expr) it returns the minimum value among dierent or all values of expr.

MAX(DISTINCT | ALL expr)  it returns the maximum value among dierent or all values of expr.

Example (SQLQuery53.sql)

How many dierent rst names do exist?

Example (SQLQuery54.sql)

Calculate the number of current (withend_date null) employees in the second department.

Example (SQLQuery55.sql)

Calculate the average salary of all current employees.

Aggregate functions III

Example (SQLQuery56.sql)

Determine the lowest and the highest date of birth for women.

GROUP BY and HAVING clause I

TheGROUP BYclause in a SELECTstatement allows to divide the rows of a queried table into groups.

TheWHERE operator restricts the returned rows.

SELECT select_list FROM list_of_tables

WHERE search_conditions_for_rows GROUP BY grouping_expressions

GROUP BY and HAVING clause II

Remark

Note thatGROUP BYcannot be applied in retrieving all rows of a table grouped by grouping expression. For instance it cannot be use (at leat directly) for selecting identication number of employees (Employment.emp_id) grouped departments

(Employment.dept_id).

The following query does not work.

select *

from Employment group by dept_id

The only thing we can get using query in such a structure is the list of all departments by usingselect dept_id instead of *.

GROUP BY and HAVING clause III

Aggregate functions usually go hand in hand with the GROUP BY clause. In such a case, an aggregate function is applied to each group of rows and returns a single value for each group.

Without theGROUP BYclause, the aggregate function is used to all the rows in the queried table.

SELECT select_list FROM list_of_tables

WHERE search_conditions_for_rows Example (SQLQuery57.sql)

Select department identication number and sum of current salaries in it.

GROUP BY and HAVING clause IV

TheHAVING operator makes a selection on groups of returned rows. It chooses only the groups for which the specied conditions are true. If there is noHAVINGclause in a query, the

nal query result involves summary rows for each group.

Example (SQLQuery58.sql)

Select department identication number if the average of current salaries in this department is greater then 7000.

GROUP BY and HAVING clause V

SQL Server provides also the ORDER BYclause to sort the rows returned by a query

ORDER BY expression(s) [ASC | DESC] - it sorts the selected rows by expression(s) which usually species a column name. TheASC keyword arranges the rows in ascending order.

DESCarranges the rows in descending order. ASCis the default.

The following SELECTstatement involves WHERE,GROUP BY, HAVING, and ORDER BYclause:

SELECT select_list FROM list_of_tables

WHERE search_conditions_for_rows GROUP BY grouping_expressions

HAVING search_conditions_for_groups ORDER BY order_expression [ ASC | DESC ]

GROUP BY and HAVING clause VI

Example (SQLQuery59.sql)

Determine employee identication number if he/she has more than one row in the Employment table. Arrange the result based on identication numbers in descending order.

Joins I

Joins enable to retrieve data from two or more tables.

Consider the following relation schemas (columns) Student={sID,sName,fName}

Faculty={fName,fAddress}

and the following instances (tables)

Joins II

sID sName GPA fName

123 Mark 4,2 Math

672 Ana 4,5 Physics 132 John 4,0 Management 521 Eva 3,0 History

fName fAddress Biology Darwina History Dlugosza Management Matejki Math Banacha Physics Newtona

Joins III

In the second lecture we dened a join of two instances (tables) I(S)andJ(R) of the schemaS andR (i.e. I(S)andJ(R)are tables with columnsS andR responsibly) as the instanceK(S∪R) i.e the table with columnsR andS) which consists of all rows (tuples) which restrictions to columns S is a row fromI(S) and

restriction to columnsR is a row from J(R).

Joins IV

So, we get, an example of join of Student and Faculty, the table

sID sName GPA fName fAddress

123 Mark 4,2 Math Banacha 672 Ana 4,5 Physics Newtona 132 John 4,0 Management Matejki 521 Eva 3,0 History Dlugosza

The result of a cross join can be obtained by aselect clause (how?).

Joins V

Most queries with joins involve so called join condition which has the form: column1 op column2, where column1, column2 are some column names from the queried tables, op is a binary comparison operator applied to compare the values stored incolumn1 andcolumn2.

The join result involves only these pair of rows for which the column1 op column2 expression evaluates to true.

A typical join condition is based on comparing values stored in a primary key column with the values of the foreign key column which refers to this primary key.

Joins VI

SQL Server provides the following types of joins:

inner join (join) it contains only the rows which satisfy a join condition.

A general syntax of inner join is the following:

select select_list

from table1 [inner] join table2 on condition

Example select *

from Student join Faculty on Student.fName=Faculty.fname Example (Example510.sql)

Select the names of all employees living in Warsaw.

Joins VII

left outer join (left join) it involves both the rows satisfying a join condition and the rows from the left table which have no matching rows in the right table.

Example (Example511.sql)

Select the names of all employees living in Warsaw including those not yet assigned to any address.

right outer join (right join) it involves both the rows satisfying a join condition and the rows from the right table which have no matching rows in the left table.

Joins VIII

Example select *

from Student right outer join Faculty on Student.fName=Faculty.fname

full outer join (full join)it involves left and right outer joins.

cross join it produces the Cartesian products of queried tables.

self join it combines the records from a table with the rows from the same table.

Example (SQLQuery512.sql)

Select the names of all employees and their manager.

Subsqueries I

A subquery is a select statement which is nested in another statement.

select list_of_columns from list_of_tables

where conditionW which involves another query

If an inner (child) query makes no reference to the outer (parent) query then it is called the uncorrelated subquery.

In the case of queries having correlated subqueries an inner query refers to the outer query.

Subsqueries II

In consequence, the inner query is evaluated once for each row returned by the outer query.

Example (SQLQuery513.sql)

Select employees older than Adam Parker Example (SQLQuery514.sql)

Get the employees older than their manager Example (SQLQuery515.sql)

Select the name of employees whose salaries are greater than 1000

Thank you for your attention!