POS/410 Week 2 – On-Ground

Scripts – Retrieving Data from Tables in SQL

The purpose of this lecture is to provide the student with an introduction to the basics of retrieving information from one or more tables using actual scripts. The script file is taken from the instructor supplied material on rEsource. The database is a simple four table database used to track project assignments. The queries illustrate use of the GROUP BY and HAVING clauses.

You should be able to select the appropriate script text and copy it into the SQL Server Query Analyzer and run it.  Note these scripts currently use the older syntax for joining tables using the where clause. They will be updated at some point in the future to use the newer ANSI syntax with the join clause.

There are two techniques for combining information from multiple tables: joins and sub-queries. These scripts illustrate both.

Database Diagram

Here is a link to a diagram of our projects database for reference in using the SQL scripts below.

    Diagram of the Projects Database

Summary Query (Grouped)

The following is a one table summary query using GROUP BY.

/* for each department retrieve the department
number, the number of employees in the department
and their average salary*/
select dno, count(*), avg(salary)
from employee
group by DNO;

Summary Query (Ungrouped)

The following is a one table summary query, sometimes called an Aggregate Query. There is no grouping.

/* retrieve the number of employees
and the average salary for all employees in the company*/
select count(*), avg(salary)
from employee;

Summary Queries (Grouped)  with Joins

The following is a summary query with a join using GROUP BY.

/* for each project retrieve the project number,
project name and number of employees*/
SELECT pnumber, pname, COUNT(*)
FROM project, works_on
WHERE pnumber = pno
GROUP BY pnumber, pname;

The following is a summary query with a join using GROUP BY and HAVING.

/* for each project on which more than two employees work
retrieve the project number, the project name and
the number of employees who work on the project*/
SELECT pnumber, pname, count(*)
FROM project, works_on
WHERE pnumber=pno
GROUP BY pnumber, pname
HAVING count(*) > 2;

The following is a summary query with a join using GROUP BY. It combines information from three tables.

/*for each project, retrieve the project number,
project name, number of employees from department 1003
who work on the project*/
SELECT pnumber, pname, count(*)
FROM project, works_on, employee
WHERE pnumber = pno AND ssn=essn AND DNO='1003'
GROUP BY pnumber, pname;
 

Sub-queries

The following query uses a sub-query that generates a list for inclusion testing

/*for each department having more than 1 employee,
retrieve the department number
and number of employees making over $40,000*/
SELECT dname, count(*)
FROM department, employee
WHERE dnumber=dno AND salary> 40000 AND
dno IN (SELECT dno
FROM employee
GROUP BY dno
HAVING COUNT(*) > 1)
GROUP BY dname;
 

The following uses a correlated sub-query.

/*select all employees currently assigned
to projects, present in alphabetical order on last name*/
SELECT fname, lname
FROM employee
WHERE EXISTS (SELECT *
    FROM works_on
    where ssn=essn)
ORDER BY lname;

The following uses a correlated sub-query with GROUP BY and HAVING

/*List in last name alphabetical order, all employees currently assigned to
more than one project*/
SELECT fname, lname
FROM employee
WHERE NOT EXISTS (SELECT count(*)
    FROM works_on
    where ssn=essn
    GROUP BY essn
    HAVING count(*) > 1)
ORDER BY lname;

Dan D'Urso
Laguna Niguel, CA
April 2005

Back | Home