Divisions of SQL

Data definition language

Used to create (define) data structures such as tables, indexes, clusters ie,
 CREATE databases, tables
 ALTER databases, tables
 DROP tables

Data Manipulation Language

The data manipulation language is used to access and update data; it is not
important for representing the data. (Of course, the data manipulation language
must be aware of how data is represented, and reflects this in the constructs that
it supports ie
 SELECT - extracts data from databases
 UPDATE- updates data in a database
 DELETE- deletes data from tables
 INSERT INTO - inserts data into tables

MORE PRACTICAL EXAMPLES

Data Definition
 Creating a database:
CREATE DATABASE databasename
 Create a table:
CREATE TABLE tablename
(
column1 datatype,
columnname2 datatype,
columnname3 datatype,
...
)
 Data types: bigint,int,datetime,char,varchar,text,image
 Alter: TABLE tablename
 Deleting a table:
 DROP TABLE tablename

Insert data into table

NSERT INTO table name

VALUES (value1, value2, value3,. . . )
INSERT INTO table name (column1, column2, column3,. . . )
VALUES (value1, value2, value3,. . . )

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

Select data

 SELECT column name(s)

FROM table name
 SELECT FROM table name
 SELECT DISTINCT column name(s)
FROM table name
 Selecting data items based upon some condition
WHERE
SELECT column name(s)
FROM table name
WHERE column name operator value
 Operators:=, <>, <, >, <=, >=, BETWEEN, LIKE
 condtions may be joined using AND/OR
 Sorting on some attribute
SELECT column name(s) FROM table name
WHERE condition(S)
ORDER BY column name(s) ASC|DESC
 Updating some data items
UPDATE table name SET column1=value,
column2=value2,. . . WHERE some column=some value
 What if we omit where clause?
Sorting on some attribute
SELECT column name(s) FROM table name
WHERE condition(S)
ORDER BY column name(s) ASC|DESC
 Updating some data items
UPDATE table name SET column1=value,
column2=value2,. . . WHERE some column=some value
 What if we omit where clause?
 DELETE FROM table name
WHERE some column=some value
 How would you delete all rows?
 Sorting on some attribute
SELECT column name(s) FROM table name
WHERE condition(S)
ORDER BY column name(s) ASC|DESC
 Updating some data items
UPDATE table name SET column1=value,
column2=value2,. . . WHERE some column=some value
 What if we omit where clause?
DELETE FROM table name
WHERE some column=some value
 How would you delete all rows?
DELETE * FROM table name

SQL Functions

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  The Average function
SELECT AVG(column name) FROM table name
 The Count function
SELECT COUNT(column name) FROM table name
 To count distinct values
SELECT COUNT(DISTINCT column name) FROM
table name
 The max function
SELECT MAX(column name) FROM table name
Similarly there is a min function
 The sum function
SELECT SUM(column name) FROM table name
 The now function
SELECT ProductName, UnitPrice, Now( as PerDate FROM
Products)

Creating a Schema

 Creating a Table:
 CREATE TABLE employee (
 EmployeeID VARCHAR(10) NOT NULL,
 Last_Name VARCHAR(20) NOT NULL,
 First_name VARCHAR(18) NOT NULL,
 Soc_Sec VARCHAR(11) NOT NULL,
 Date_of_Birth DATE,
 Salary NUMBER
 ) ;

 CREATE TABLE dependant (
 Last_Name VARCHAR(20) NOT NULL,
 First_name VARCHAR(18) NOT NULL,
 Soc_Sec VARCHAR(11) NOT NULL,
 Date_of_Birth DATE,
 EmployeeID VARCHAR(10) NOT NULL
 );

 Note: When naming tables, columns and other database objects, do not
include spaces in the names. For example, do not call the last name
column: Last Name
If you wish to separate words in a name, use the underscore character.
 Specifying Primary and Foreign keys:
 CREATE TABLE order_header (
 order_number NUMBER(10,0) NOT NULL,
 order_date DATE,
 sales_person VARCHAR(25),
 bill_to VARCHAR(35),
 bill_to_address VARCHAR(45),
 bill_to_city VARCHAR(20),
 bill_to_state VARCHAR(2),
 bill_to_zip VARCHAR(10),
 PRIMARY KEY (order_number)
 );


Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  CREATE TABLE order_items (
 order_number NUMBER(10,0) NOT NULL,
 line_item NUMBER(4,0) NOT NULL,
 part_number VARCHAR(12) NOT NULL,
 quantity NUMBER(4,0),
 PRIMARY KEY (order_number, line_item),
 FORIEGN KEY (order_number)
 REFERENCES order_header (order_number),
 FOREIGN KEY (part_number)
 REFERENCES parts (part_number)
 );

 CREATE INDEX order_index
 ON order_header (order_number) ASC ;

 CREATE INDEX items_index
 ON order_items (order_number, line_item) ASC ;


 CREATE TABLE workers (
 FirstName TEXT,
 LastName TEXT,
 ssn INTEGER
 CONSTRAINT ssnConstraint PRIMARY KEY
 );

 CREATE INDEX worker_index
 ON worker (ssn) ;


More examples using Primary Key and Foreign keys

CREATE TABLE department (

departmentid NUMBER NOT NULL
CONSTRAINT pk_department PRIMARY KEY,
department_name VARCHAR(30) NOT NULL,
department_location VARCHAR(30)
)

CREATE TABLE employee (

EmployeeID VARCHAR(10) NOT NULL
CONSTRAINT pk_Employee PRIMARY KEY,
Last_Name VARCHAR(20) NOT NULL,
First_name VARCHAR(18) NOT NULL,
Soc_Sec VARCHAR(11) NOT NULL,
Date_of_Birth DATE,
Salary NUMBER,
departmentid NUMBER
) ;

ALTER TABLE employee

ADD CONSTRAINT fk_department
FOREIGN KEY (departmentid)
REFERENCES department (departmentid)

Specifying Constraints on Columns and Tables

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  Constraints on attributes:
o NOT NULL - Attribute may not take a NULL value
o DEFAULT - Store a given default value wh no value is specified
o PRIMARY KEY - Indicate which attribute(s) form the primary key
o FOREIGN KEY - Indicate which attribute(s) form a foreign key.
This enforces referential integrity
o UNIQUE - Indicates which attribute(s) must have unique values.
 Specify when constraint should be enforced:
o Immediate
o Deferrable until commit time
 Referential Integrity Constraint: Specify the behavior for child tuples
when a parent tuple is modified.
 Action to take if referential integrity is violated:
o SET NULL - Child tuples foreign key is set to NULL - Orphans.
o SET DEFAULT - Set the value of the foreign key to some default value.
o CASCADE - Child tuples are updated (or deleted) according to the action
take on the parent tuple.
 Examples of ON DELETE and ON UPDATE
 CREATE TABLE order_items (
 order_number NUMBER(10,0) NOT NULL,
 line_item NUMBER(4,0) NOT NULL,
 part_number VARCHAR(12) NOT NULL,
 quantity NUMBER(4,0),
 PRIMARY KEY (order_number, line_item),
 FORIEGN KEY (order_number)
 REFERENCES order_header (order_number)
 ON DELETE SET DEFAULT
 ON UPDATE CASCADE,
 FOREIGN KEY (part_number)
 REFERENCES parts (part_number)
 );
 Constraints can also be given names so that they can later be modified or
dropped easily.
 CREATE TABLE order_header (
 order_number NUMBER(10,0) NOT NULL,
 order_date DATE,
 sales_person VARCHAR(25),
 bill_to VARCHAR(35),
 bill_to_address VARCHAR(45),
 bill_to_city VARCHAR(20),
 bill_to_state VARCHAR(2),
 bill_to_zip VARCHAR(10),
 CONSTRAINT pk_order_header
 PRIMARY KEY (order_number)
 );

 CREATE TABLE order_items (
 order_number NUMBER(10,0) NOT NULL,
 line_item NUMBER(4,0) NOT NULL,
 part_number VARCHAR(12) NOT NULL,
 quantity NUMBER(4,0),

 CONSTRAINT pk_order_items

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  PRIMARY KEY (order_number, line_item),

 CONSTRAINT fk1_order_items
 FORIEGN KEY (order_number)
 REFERENCES order_header (order_number)
 ON DELETE SET DEFAULT
 ON UPDATE CASCADE,

 CONSTRAINT fk2_order_items
 FOREIGN KEY (part_number)
 REFERENCES parts (part_number)
 ON DELETE SET DEFAULT
 ON UPDATE CASCADE
 );
 An even better approach is to create the tables without constraints and then
add them separately with ALTER TABLE statements
 CREATE TABLE order_header (
 order_number NUMBER(10,0) NOT NULL,
 order_date DATE,
 sales_person VARCHAR(25),
 bill_to VARCHAR(35),
 bill_to_address VARCHAR(45),
 bill_to_city VARCHAR(20),
 bill_to_state VARCHAR(2),
 bill_to_zip VARCHAR(10)
 );

 ALTER TABLE order_header
 ADD CONSTRAINT pk_order_header
 PRIMARY KEY (order_number);


 CREATE TABLE order_items (
 order_number NUMBER(10,0) NOT NULL,
 line_item NUMBER(4,0) NOT NULL,
 part_number VARCHAR(12) NOT NULL,
 quantity NUMBER(4,0)
 );

 ALTER TABLE order_items ADD
 CONSTRAINT pk_order_items
 PRIMARY KEY (order_number, line_item) ;

 ALTER TABLE order_items ADD
 CONSTRAINT fk1_order_items
 FORIEGN KEY (order_number)
 REFERENCES order_header (order_number)
 ON DELETE SET DEFAULT
 ON UPDATE CASCADE;

 ALTER TABLE order_items ADD
 CONSTRAINT fk2_order_items
 FOREIGN KEY (part_number)
 REFERENCES parts (part_number)
 ON DELETE SET DEFAULT
 ON UPDATE CASCADE;

Removing Schema Components with DROP

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  DROP SCHEMA schema_name CASCADE
Drop the entire schema including all tables. CASCADE option deletes all
data, all tables, indexes, domains, etc.
 DROP SCHEMA schema_name RESTRICT
Removes the schema only if it is empty.
 DROP TABLE table_name
Remove the table and all of its data.
 DROP TABLE table_name CASCADE
Remove the table and all related tables as specified by FOREIGN KEY
constraints.
 DROP TABLE table_name RESTRICT
Remove the table only if it is not referenced (via a FORIEGN KEY
constraint) by other tables.
 DROP INDEX index_name
Removes an index.
 DROP CONSTRAINT table_name.constraint_name
Removes a constraint from a table.

Changing Schema Components with ALTER

 Changing Attributes:
ALTER TABLE student ALTER last_name VARCHAR(35);
ALTER TABLE student ALTER gpa DROP DEFAULT
ALTER TABLE student ALTER gpa SET DEFAULT 0.00;
 Adding Attributes:
ALTER TABLE student ADD admission DATE;
 Removing Attributes (not widely implemented):
ALTER TABLE student DROP home_phone;

Data Manipulation Language

 is used to create and specify the schema. DML is then used to manipulate
(select, insert, update, delete) data.

Inserting Data into Tables

 General syntax:
 INSERT INTO tablename (column1, column2, ... columnX)
 VALUES (val1, val2, ... valX);
 Examples:

 INSERT INTO stocks (symbol, close_date, close_price)
 VALUES ("IBM", "03-JUN-94", 104.25);

 INSERT INTO student_grades (student_id, test_name, score, grade)
 VALUES (101, "Quiz 1", 88, "B+");

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  Quotes are placed around the data depending on the Data type and on the
specific RDBMS being used:

RDBMS Text Data Type Dates

MS Access TEXT: Either " or ' DATETIME: Either # or '
Oracle VARCHAR: ' DATE: '
IBM DB2 VARCHAR: ' DATE: '
Sybase CHAR and VARCHAR: " DATE: "

Examples for Employee and Department .

INSERT INTO department (departmentid, department_name, department_location)
VALUES (1, "Finance", "NY");

INSERT INTO department (departmentid, department_name, department_location)

VALUES (2, "Marketing", "NY");

INSERT INTO department (departmentid, department_name, department_location)

VALUES (3, "IT", "NJ");

INSERT INTO department (departmentid, department_name, department_location)

VALUES (4, "Accounting", "PA");

INSERT INTO department (departmentid, department_name, department_location)

VALUES (5, "Shipping", "CA");

INSERT INTO employee (employeeid, first_name, last_name, soc_sec,

date_of_birth, salary, departmentid)
VALUES ("E101", "Joe", "Smith", "111-22-3344", '5/3/1972', 43000.00, 1)

INSERT INTO employee (employeeid, first_name, last_name, soc_sec,

date_of_birth, salary, departmentid)
VALUES ("E102", "Mary", "Green", "333-11-2222", '12/4/1968', 41500.00,
2)

INSERT INTO employee (employeeid, first_name, last_name, soc_sec,

date_of_birth, salary, departmentid)
VALUES ("E103", "Ed", "Jones", "777-66-5555", '1/9/1971', 39500.00, 3)

INSERT INTO employee (employeeid, first_name, last_name, soc_sec,

date_of_birth, salary, departmentid)
VALUES ("E104", "Sally", "Smith", "661-61-6161", '8/19/1973', 55000.00,
3)

INSERT INTO employee (employeeid, first_name, last_name, soc_sec,

date_of_birth, salary, departmentid)
VALUES ("E105", "Howard", "Brown", "777-88-9999", '8/12/1976',
45000.00, 1)

INSERT INTO employee (employeeid, first_name, last_name, soc_sec,

date_of_birth, salary, departmentid)

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

 VALUES ("E106", "Jane", "Foster", "222-22-2222", '1/1/1972', 35000.00,
4)

Retrieving Data from Tables with Select

 Main way of getting data out of tables is with the SELECT statement.
 SELECT syntax:
 SELECT column1, column2, ... columnN
 FROM tableA, tableB, ... tableZ
 WHERE condition1, condition2, ...conditionM
 GROUP BY column1, ...
 HAVING condition
 ORDER BY column1, column2, ... columnN

Assume an employee table:

employee(employeeid, first_name, last_name, date_of_birth, salary)
and a "Stocks" table:
stocks(symbol, close_date, close_price)

 Some example queries:

 SELECT employeeid, last_name, first_name
 FROM employee
 WHERE last_name = 'Smith'
 ORDER BY first_name DESC


 SELECT employeeid, last_name, first_name
 FROM employee
 WHERE salary > 41000
 ORDER BY last_name, first_name DESC

 SELECT *
 FROM employee
 ORDER BY 2;
 SELECT last_name, first_name, salary
 FROM employee
 WHERE departmentid = 3
 ORDER BY salary DESC

 SELECT symbol, close_price
 FROM stocks
 WHERE close_date > "01-JAN-95" AND
 symbol = "IBM"
 ORDER BY close_date


 SELECT symbol, close_date, close_price
 FROM stocks
 WHERE close_date >= "01-JAN-95"
 ORDER BY symbol, close_date

SQL Built-in Functions

 SQL has two main types of functions:

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

 1. Aggregate Functions: Operate on a collection of records. For
example, SUM, AVG, MIN, MAX and COUNT are all aggregate
functions.
2. Scalar functions: Operate on every record value individually. For
example math functions like SIN, COS, ROUND and text functions
like UCASE, MID, LEN and FORMAT.
 Examples of Aggregate functions follow:
 Average salary in the company:
 SELECT AVG(salary)
 FROM employee;

 Results:
 Expr1000
 ---------
 43166.667

SELECT AVG(salary) AS AverageSalary

 FROM employee;

 Results:
 AverageSalary
 -------------
 43166.667

Give the name of the employee with the highest salary in the company:
This is an example of a subquery
 SELECT first_name, last_name, salary
 FROM employee
 WHERE salary =
 ( SELECT MAX(salary) FROM employee
 );

Results:
first_name last_name salary
Sally Smith 55000

Show the employees with the highest salaries in each department:

SELECT first_name, last_name, departmentid, salary
FROM employee e1
WHERE salary =(SELECT max(salary)
FROM employee e2 WHERE [Link] = [Link]
)
ORDER BY salary DESC;

Results:

first_name last_name departmentid salary

Sally Smith 3 55000
Howard Brown 1 45000
Mary Green 2 41500
Jane Foster 4 35000

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

 Note the two aliases given to the employee table: e1 and e2. These allow us
to refer to different views of the same table.

Show a COUNT of the number of employees in each department

SELECT departmentid, COUNT(employeeid) AS EmployeeCount
FROM employee
GROUP BY departmentid
Results:
departmentid EmployeeCount
3 2
1 2
2 1
4 1
 SELECT MID(first_name, 1, 1) AS first_init, last_name
 FROM employee

 Result:
 first_init last_name
 J Smith
 M Green
 E Jones
 S Smith
 H Brown
 J Foster
 Show the First initial and last name of each employee but make them all
upper case
 SELECT UCASE(MID(first_name, 1, 1)) AS first_init, UCASE(last_name)
AS last_name
 FROM employee

 Result:
 first_init last_name
 J SMITH
 M GREEN
 E JONES
 S SMITH
 H BROWN
 J FOSTER

 Show the current salary and a proposed new salary for each employee:
 SELECT first_name, salary AS CurrentSalary,
 (salary * 1.04) AS SalaryWithRaise
 FROM employee

 Result:
 first_name CurrentSalary SalaryWithRaise
 Joe 43000 44720
 Mary 41500 43160
 Ed 39500 41080
 Sally 55000 57200
 Howard 45000 46800
 Jane 35000 36400
 Show the Date of Birth and the age of each employee in Department 3. This
uses the Now() function which returns the current date and time.
 SELECT first_name, date_of_birth,

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  (Now() - date_of_birth) AS Age
 FROM employee
 WHERE departmentid = 3

 Result:
 first_name date_of_birth Age
 Ed 1/9/1971 14901.5790625
 Sally 8/19/1973 13948.5790625
 Note that the "Age" is given in number of days. A better output would be to
give this in years. So divide answer by 365.
 SELECT first_name, date_of_birth,
 ((Now() - date_of_birth) / 365) AS Age
 FROM employee
 WHERE departmentid = 3

 Result:
 first_name date_of_birth Age
 Ed 1/9/1971 40.8262484462202
 Sally 8/19/1973 38.2152895421106
 Perhaps we do not want so many decimal places. In this case use FORMAT
function (in MS ACCESS)
 SELECT first_name, date_of_birth,
 FORMAT(((Now() - date_of_birth) / 365), "0.0") AS Age
 FROM employee
 WHERE departmentid = 3

 Result:
 first_name date_of_birth Age
 Ed 1/9/1971 40.8
 Sally 8/19/1973 38.2
 Show all employees who have a birthday in August. this uses the MONTH
function. Given a date, MONTH(date) returns the month as a number.
Similar functions include DAY and YEAR.
 SELECT first_name, last_name
 FROM employee
 WHERE MONTH(date_of_birth) = 8;

 Result:
 first_name last_name
 Sally Smith
 Howard Brown

Selecting from 2 or More Tables

 In the FROM portion, list all tables separated by commas. Called a Join.
 The WHERE part becomes the Join Condition
 List all of the employees working in New York:
 SELECT employee.first_name, employee.last_name
 FROM employee, department
 WHERE [Link] = [Link]
 AND department.department_location = 'NY';

 Results:
 first_name last_name
 Joe Smith
 Howard Brown

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  Mary Green

The Where clause: WHERE [Link] =

[Link] is the Join Condition

 List each employee name and what state (location) they work in. List them
in order of location and name:
 SELECT employee.last_name, department.department_location
 FROM employee, department
 WHERE [Link] = [Link]
 ORDER BY department.department_location, employee.last_name;

 Results:
 last_name department_location
 Jones NJ
 Smith NJ
 Brown NY
 Green NY
 Smith NY
 Foster PA

This is similar to a LEFT JOIN.

 List each department and all employees that work there. Show the
department and location even if no employees work there.
 SELECT [Link], department.department_location,
 employee.last_name
 FROM employee RIGHT JOIN department
 ON [Link] = [Link]


 Results:
 departmentid department_location last_name
 1 NY Smith
 1 NY Brown
 2 NY Green
 3 NJ Jones
 3 NJ Smith
 4 PA Foster
 5 CA NULL

What is the highest paid salary in New York ?

 SELECT MAX([Link])
 FROM employee, department
 WHERE [Link] = [Link]
 AND department.department_location = 'NY';

 Results:
 MAX(SALARY)
 ------------
 45000

Cartesian Product of the two tables:

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  SELECT *
 FROM employee, department;

In which states do our employees work ?

 SELECT DISTINCT department_location
 FROM department;
 From our Bank Accounts example.
List the Customer name and their total account holdings:
 SELECT [Link], Sum(Balance)
 FROM customers, accounts
 WHERE [Link] = [Link]
 GROUP BY [Link]

Results:

LASTNAME SUM(BALANCE)
 --------- ------------
 Axe $15,000.00
 Builder $1,300.00
 Jones $1,000.00
 Smith $6,000.00

We can also use a Column Alias to change the title of the columns
 SELECT [Link], Sum(Balance) AS TotalBalance
 FROM customers, accounts
 WHERE [Link] = [Link]
 GROUP BY [Link]

Results:

LASTNAME TotalBalance
 --------- ------------
 Axe $15,000.00
 Builder $1,300.00
 Jones $1,000.00
 Smith $6,000.00

Here is a combination of a function and a column alias:

 SELECT first_name, last_name, departmentid,
 salary AS CurrentSalary,
 (salary * 1.03) AS ProposedRaise
 FROM employee;

Results:

 first_name last_name departmentid CurrentSalary ProposedRaise

 Joe Smith 1 43000 44290
 Mary Green 2 41500 42745
 Ed Jones 3 39500 40685
 Sally Smith 3 55000 56650
 Howard Brown 1 45000 46350
 Jane Foster 4 35000 36050


Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

 Provide a listing of each student and the name of their tutor:
 SELECT [Link] AS Student, [Link] AS Tutor
 FROM students s1, students tutors
 WHERE s1.student_tutorid = [Link];

WHERE Clause Expressions

 There are a number of expressions one can use in a WHERE clause.

 Typical Logic expressions:
COLUMN = value
Also:

 < > = != <= >=

Also consider BETWEEN

SELECT name, grade, "You Got an A"
FROM students
WHERE grade between 91 and 100

Using BETWEEN with dates: Show the employees who were born in the
first 5 months of 1972.
 For MS Access:
 SELECT first_name, last_name, date_of_birth
 FROM employee
 WHERE date_of_birth BETWEEN #1/1/1972# AND #6/30/1972#

 Result:

 first_name last_name date_of_birth
 Joe Smith 5/3/1972
Jane Foster 1/1/1972

Note for Oracle or DB2, enclose the dates in single quotes like so:
SELECT first_name, last_name, date_of_birth
FROM employee
WHERE date_of_birth BETWEEN '01-JAN-1972' AND '30-JUN-1972'

Subqueries using = (equals): Find the student with the highest grade.
 SELECT name, grade
 FROM students
 WHERE grade =
 ( SELECT MAX(grade) FROM students
 );
 Find th eemployee with the lowest salary:
 SELECT first_name, last_name, salary
 FROM employee
 WHERE salary = (SELECT MIN(salary) FROM employee)


Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  Result:
 first_name last_name salary
 Jane Foster 35000

This assumes the subquery returns only one tuple as a result.

Typically used for aggregate functions.

Subqueries using IN:

 SELECT last_name
 FROM employee
 WHERE departmentid IN (1, 3);


 SELECT last_name, first_name
 FROM employee
 WHERE departmentid IN
 (SELECT departmentid
 FROM department
 WHERE department.department_location = 'NY');

In the above case, the subquery returns a set of tuples. The IN clause returns
true when a tuple matches a member of the set.

Subqueries using EXISTS:

 SELECT first_name, last_name, salary
 FROM employee
 WHERE EXISTS
 (SELECT last_name
 FROM EMPLOYEE e2
 WHERE [Link] > [Link])
 AND EXISTS
 (SELECT last_name
 FROM EMPLOYEE e3
 WHERE [Link] < [Link])

Results:
first_name last_name salary
 Joe Smith 43000
 Mary Green 41500
 Ed Jones 39500
 Howard Brown 45000


The above query shows all employees names and salaries where there is at
least one person who makes more money (the first exists) and at least one
person who makes less money (second exists).

NOT EXISTS:
 SELECT first_name, salary
 FROM employee
 WHERE NOT EXISTS
 (SELECT last_name

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  FROM EMPLOYEE e2
 WHERE [Link] > [Link])

 Results:
 first_name salary
 Sally 55000


Above query shows all employees for whom there does not exist an
employee who is paid less.

INLINE SQL Queries

The result of a query is a table. Normally the results are shown to the user.
However we can use the reuslts of a query as the basis for another query.
That is, the subquery becomes a table we can select FROM. For exmaple:
 SELECT first_name, salary
 FROM (SELECT employee.first_name, [Link],
department.department_location
 FROM employee, department
 WHERE [Link] = [Link]
 AND department.department_location = 'NY'
 )
 WHERE salary > 35000

Recall this example:

SELECT [Link] AS TutorName,
COUNT(tutors.student_tutorid) AS NumberTutored
FROM students s1, students tutors
WHERE [Link] = tutors.student_tutorid
GROUP BY [Link];

We might be interested in the maximum number of people one person is

tutoring:
SELECT TutorName, NumberTutored
FROM (SELECT [Link] AS TutorName,
COUNT(tutors.student_tutorid) AS NumberTutored
FROM students s1, students tutors
WHERE [Link] = tutors.student_tutorid
GROUP BY [Link]
)
WHERE NumberTutored =
(SELECT MAX(NumberTutored)
FROM (SELECT [Link] AS TutorName,
COUNT(tutors.student_tutorid) AS NumberTutored
FROM students s1, students tutors
WHERE [Link] = tutors.student_tutorid
GROUP BY [Link]
)
)

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

 LIKE operator:
Use the LIKE operator to perform a partial string match. Generally, the %
character is used as the wild card although in some DBMS, the * character is
used.

Show all employees whose name starts with 'S'

SELECT first_name, last_name, salary
FROM employee
WHERE last_name LIKE 'S%';

Show all employees whose name contains the letters 'en'

SELECT first_name, salary
FROM employee
WHERE first_name LIKE '%ar%';

Note that chatacters within quotes are case sensitive.

Show all employees whose name contains the letter 'e' and the letter 'n' in
that order:
SELECT first_name, salary
FROM employee
WHERE first_name LIKE '%a%r%';

Show all employees whose name contains the letter 'e' and the letter 'n' in
any order:
SELECT first_name, salary
FROM employee
WHERE first_name LIKE '%a%r%' OR
first_name LIKE '%r%a%';

HAVING Clause Expressions

HAVING is like WHERE except that it works on aggregate functions.

For example, assume we want to get a total of salaries paid in each department but
only want to show those with total salary greater than $50,000
SELECT department.department_name, SUM(salary) AS TotalDeptSalary
FROM employee, department
WHERE [Link] = [Link]
GROUP BY department.department_name
HAVING SUM(salary) > 50000

Result:
department_name TotalDeptSalary
Finance 88000
IT 94500

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

Or we can put it another way. Show total of salaries paid by each department but
only if the total is greater than the average paid by each department
SELECT department.department_name, SUM(salary) AS TotalDeptSalary
FROM employee, department
WHERE [Link] = [Link]
GROUP BY department.department_name
HAVING SUM(salary) > AVG(salary)

Suppose we are interested in the name of the department with the largest total
salary. In this case we need to find the MAX of the SUM
SELECT department.department_name, SUM(salary) AS TotalDeptSalary
FROM employee, department
WHERE [Link] = [Link]
GROUP BY department.department_name
HAVING SUM(salary) =
(SELECT MAX(TotalDeptSalary)
FROM
(SELECT department.department_name,
SUM(salary) AS TotalDeptSalary
FROM employee, department
WHERE [Link] = [Link]
GROUP BY department.department_name
)
)

Deleting Tuples with DELETE

 DELETE is used to remove tuples from a table.

 With no WHERE clause, DELETE will remove all tuples from a table.
 Remove all employees:
 DELETE employee;
 Remove only employees making more than $50,000
 DELETE employee
 WHERE salary > 50000;
 Remove all employees working in New York:
 DELETE employee
 WHERE departmentid IN
 (SELECT departmentid
 FROM department
 WHERE department_location = 'NY');
 DELETE will not be successful if a constraint would be violated.
For example, consider the department attribute in the Employee table as a
Foreign Key.
Removing a department would then be contingent upon no employees
working in that department.
This is what we call enforcing Referential Integrity

Change Values using UPDATE

 The UPDATE command is used to change attribute values in the database.

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500

  UPDATE uses the SET clause to overwrite the value.
 Change the last name of an Employee:
 UPDATE employee
 SET last_name = 'Smith'
 WHERE employeeid = 'E101';
 Give an Employee a raise:
 UPDATE employee
 SET salary = salary * 1.05
 WHERE employeeid = 'E101';

Dr. Mbii Kavindu - Email: Honkavindu@[Link] - Phone: 0722294481/0745415500