WGU C170 Database Applications: 20 Practice Labs with Solutions

WGU C170 Database Applications: 20 Practice Labs with Solutions

Setup: sample database
Run this script once. Every lab assumes the data below, so reload it before a lab that changes rows (Labs
3, 4, 18, 19).
CREATE TABLE Department (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(40) NOT NULL UNIQUE,
Location VARCHAR(40)
);

CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
Salary DECIMAL(10,2) CHECK (Salary > 0),
HireDate DATE,
DeptID INT,
ManagerID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID),
FOREIGN KEY (ManagerID) REFERENCES Employee(EmpID)
);

CREATE TABLE Project (
ProjID INT PRIMARY KEY,
ProjName VARCHAR(50) NOT NULL,
Budget DECIMAL(12,2),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

CREATE TABLE Assignment (
EmpID INT,
ProjID INT,
Hours INT DEFAULT 0,
PRIMARY KEY (EmpID, ProjID),
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID),
FOREIGN KEY (ProjID) REFERENCES Project(ProjID)
);

INSERT INTO Department VALUES
(10,'Engineering','Nairobi'),(20,'Sales','Mombasa'),
(30,'HR','Nairobi'),(40,'Finance','Kisumu'),(50,'Legal','Nairobi');

INSERT INTO Employee VALUES
(1,'Amina','Otieno',95000,'2019-03-15',10,NULL),
(2,'Brian','Kamau',72000,'2020-07-01',10,1),
(3,'Daniel','Mwangi',88000,'2018-05-20',20,NULL),
(4,'Cynthia','Wanjiru',68000,'2021-01-10',20,3),
(5,'Esther','Njeri',54000,'2022-09-05',30,NULL),

, (6,'Felix','Odhiambo',61000,'2022-02-14',40,NULL),
(7,'Grace','Achieng',59000,'2023-04-03',30,5),
(8,'Hassan','Ali',77000,'2020-11-23',10,1),
(9,'Irene','Chebet',50000,'2024-01-08',NULL,NULL);

INSERT INTO Project VALUES
(101,'Website Redesign',120000,10),(102,'Sales Portal',90000,20),
(103,'Payroll Upgrade',150000,40),(104,'Onboarding Program',30000,30),
(105,'Data Warehouse',200000,10),(106,'Audit Prep',45000,40);

INSERT INTO Assignment VALUES
(1,101,120),(2,101,200),(8,101,150),(1,105,300),(2,105,180),(8,105,90),
(3,102,160),(4,102,210),(6,103,250),(5,104,80),(7,104,95),(6,106,60);
Irene (9) has no department and no assignments, and Legal (50) has no employees, so the outer-join
labs have something to find.


Labs 1–5: DDL, INSERT, UPDATE, DELETE, SELECT

Lab 1: Create a table with constraints
Task. Create Training to record courses taken by employees. It needs a primary key, a required
employee and course name, a score between 0 and 100, a status that defaults to 'Enrolled', a
foreign key to Employee, and a rule that an employee cannot take the same course twice.
CREATE TABLE Training (
TrainID INT PRIMARY KEY,
EmpID INT NOT NULL,
CourseName VARCHAR(60) NOT NULL,
CompletedOn DATE,
Score INT CHECK (Score BETWEEN 0 AND 100),
Status VARCHAR(10) DEFAULT 'Enrolled',
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID),
UNIQUE (EmpID, CourseName)
);
The composite UNIQUE is a candidate key. CHECK rejects a score of 105, and the foreign key rejects an
EmpID that does not exist.


Lab 2: INSERT statements
Task. Add six training records. Use the default status for the ones not yet completed.
INSERT INTO Training (TrainID, EmpID, CourseName, CompletedOn, Score, Status)
VALUES
(1, 1, 'SQL Basics', '2024-02-10', 92, 'Completed'),
(2, 2, 'SQL Basics', '2024-02-10', 85, 'Completed'),
(5, 8, 'Data Modeling', '2024-05-02', 78, 'Completed');

INSERT INTO Training (TrainID, EmpID, CourseName) VALUES
(3, 3, 'Negotiation'),
(4, 4, 'Negotiation'),
(6, 7, 'Recruiting 101');