70.109
Practice Queries for SQL I

Sample Database

This commands below create the SQLite “projectdb2.db” database containing the tables projects, projectmgr, certification and earnedcerts. Inspect the schema creation statements to understand the structure of the database.

library(RSQLite)

# create a new in-memory database
dbcon <- dbConnect(RSQLite::SQLite(), ":memory:")

create table projectmgr (
  pmid integer,
  name text not null,
  primary key (pmid)
)

create table certifications (
  cid integer not null,
  cert text not null,
  body text not null,
  primary key (cid)
)

create table earnedcerts (
  pmid integer,
  cert integer,
  primary key (pmid,cert)
  foreign key (pmid) references projectmgr(pmid)
  foreign key (cert) references certifications(cid)
)

create table projects (
  pid integer,
  pname text default "No Name",
  budget double default 0,
  pmgr integer  not null,
  primary key (pid),
  foreign key (pmgr) references projectmgr(pmid)
)

insert into certifications values
  (1,"PMP","Project Management Institute"),
  (2,"APM","Project Management Institute"),
  (4,"CBAP","International Institute of Business Analysis"),
  (5,"CIPM","American Academy of Project Management")

insert into projectmgr values
  (11,"John Wu"),
  (22,"Ann Molloy"),
  (18,"Sandeep Ranghani"),
  (19,"Alicia Knauss"),
  (38,"Roxanna da Silva"),
  (23,"Wagner Ormond"),
  (47,"Margaret O'Shea"),
  (20,"Alden White")

insert into earnedcerts values
  (11,1),
  (11,4),
  (18,1),
  (22,1),
  (47,1),
  (20,1),
  (20,4),
  (38,5)

insert into projects values
  (100,"GOTAM",350000,11),
  (200,"Data Twist",875000,20),
  (300,"Alpha Launch",12500,18),
  (302,"Alpha Arch Design",38000,18),
  (303,"Alpha Dev",125000,18),
  (305,"Alpha Analytics Dashboard",235000,38),
  (811,"Bootstrap Demo",0,47)

Sample Queries

List all information about project managers

select * from projectmgr

Table 1: 8 records

pmid	name
11	John Wu
18	Sandeep Ranghani
19	Alicia Knauss
20	Alden White
22	Ann Molloy
23	Wagner Ormond
38	Roxanna da Silva
47	Margaret O’Shea

List all information about projects

select * from projects

Table 2: 7 records

pid	pname	budget	pmgr
100	GOTAM	350000	11
200	Data Twist	875000	20
300	Alpha Launch	12500	18
302	Alpha Arch Design	38000	18
303	Alpha Dev	125000	18
305	Alpha Analytics Dashboard	235000	38
811	Bootstrap Demo	0	47

List all information about certifications

select * from certifications

Table 3: 4 records

cid	cert	body
1	PMP	Project Management Institute
2	APM	Project Management Institute
4	CBAP	International Institute of Business Analysis
5	CIPM	American Academy of Project Management

select * from earnedcerts

Table 4: 8 records

pmid	cert
11	1
11	4
18	1
22	1
47	1
20	1
20	4
38	5

List the names and budgets of all projects with a budget of more than $200,000.

select p.pname, p.budget 
  from projects p
 where p.budget > 200000;

Table 5: 3 records

pname	budget
GOTAM	350000
Data Twist	875000
Alpha Analytics Dashboard	235000

How many projects managers are there?

select count(*) as 'NumPMs'
  from projectmgr;

Table 6: 1 records

NumPMs
8

List the names of all project managers who manage at least one project, ordered alphabetically in descending order starting with ‘A’.

select distinct m.name
  from projectmgr m join projects p on (m.pmid = p.pmgr)
 order by m.name;

Table 7: 5 records

name
Alden White
John Wu
Margaret O’Shea
Roxanna da Silva
Sandeep Ranghani

List the names of all project managers who do not manage at least one project.

select name
  from projectmgr
 except
select distinct m.name
  from projectmgr m join projects p on (m.pmid = p.pmgr)

Table 8: 3 records

name
Alicia Knauss
Ann Molloy
Wagner Ormond

List all project names and the name of the project manager.

select p.pname, m.name
  from projects p join projectmgr m on (m.pmid = p.pmgr);

Table 9: 7 records

pname	name
GOTAM	John Wu
Data Twist	Alden White
Alpha Launch	Sandeep Ranghani
Alpha Arch Design	Sandeep Ranghani
Alpha Dev	Sandeep Ranghani
Alpha Analytics Dashboard	Roxanna da Silva
Bootstrap Demo	Margaret O’Shea

What is the total budget of all projects combined?

select sum(p.budget)
  from projects p;

Table 10: 1 records

sum(p.budget)
1635500

Which project managers have more than one certification?

select m.name, count(*) as 'NumCerts'
  from earnedcerts e join projectmgr m on (e.pmid = m.pmid)
 group by e.pmid
having NumCerts > 1

Table 11: 2 records

name	NumCerts
John Wu	2
Alden White	2

List the name of each project manager, the number of projects they manage, and the total combined budget of their projects.

select m.name, count(*) as 'NumProjects', sum(p.budget) as 'TotalBudget'
  from projectmgr m join projects p on (m.pmid = p.pmgr)
 group by p.pmgr

Table 12: 5 records

name	NumProjects	TotalBudget
John Wu	1	350000
Sandeep Ranghani	3	175500
Alden White	1	875000
Roxanna da Silva	1	235000
Margaret O’Shea	1	0

List all projects that have a project name that contains the text “Alpha”.

select *
  from projects p
 where p.pname like '%Alpha%';

Table 13: 4 records

pid	pname	budget	pmgr
300	Alpha Launch	12500	18
302	Alpha Arch Design	38000	18
303	Alpha Dev	125000	18
305	Alpha Analytics Dashboard	235000	38

What is the average project budget?

select avg(p.budget) from projects p

Table 14: 1 records

avg(p.budget)
233642.9

Which projects are within 10% of the average budget.

select *
  from projects p
 where p.budget > 0.9*(select avg(p.budget) from projects p)
   and p.budget < 1.1*(select avg(p.budget) from projects p);

Table 15: 1 records

pid	pname	budget	pmgr
305	Alpha Analytics Dashboard	235000	38

Which project managers do not have any certifications? List their names.

select p.name
  from projectmgr p
 where p.pmid not in (select distinct e.pmid
                        from earnedcerts e);

Table 16: 2 records

name
Alicia Knauss
Wagner Ormond

Disconnect from Database

dbDisconnect(dbcon)

Conclusion

This tutorial explained the most common SQL query structures using examples.

---

Files & Resources

All Files for Lesson 70.109

---

References

No references.

Errata

None collected yet. Let us know.