Database Concepts and SQL Queries: A Practical Project, Assignments of Information Systems

Download Database Concepts and SQL Queries: A Practical Project and more Assignments Information Systems in PDF only on Docsity!

MIS582 Database Concepts - 10723

Final Course Project

Module 8

Student Name

Date

Professor: Dr Nana Liu

1. ERD diagram

2.b. Create Tables (Employee, EmpSkill, Skill, Region, and Customer) and Insert Data

CREATE TABLE REGION (

RegionID VARCHAR(4) NOT NULL PRIMARY KEY, RegionName VARCHAR(20) NOT NULL); CREATE TABLE EMPLOYEE ( EmpID VARCHAR(4) NOT NULL PRIMARY KEY, EmpLastName VARCHAR(255) NOT NULL, EmpFirstName VARCHAR(255) NOT NULL, EmpInitial CHAR, EmpHireDate DATE NOT NULL, RegionID VARCHAR(4) NOT NULL, FOREIGN KEY(RegionID) REFERENCES REGION(RegionID)); CREATE TABLE SKILL ( SkillID VARCHAR(4) NOT NULL PRIMARY KEY,

('E2', 'Khan', 'Salman', NULL, '2017-09-23', '1001'), ('E3', 'Roshan', 'Hritik', NULL, '2016-12-06', '1002'), ('E4', 'Pandey', 'Poonam', NULL, '2015-09-06', '1005'),

('E5', 'Rathi', 'Rahul', NULL, '2014-08-19', '1004'), ('E6', 'Rathi', 'Renu', NULL, '2020-12-20', '1007'), ('E7', 'Chandak', 'Lalit', NULL, '2009-05-06', '1006'), ('E8', 'Agarawal', 'Gaurav', NULL, '2008-11-01', '1001'), ('E9', 'Gandhi', 'Dhiraj', NULL, '2004-06-04', '1002'), ('E10', 'Sharma', 'Pratiksha', NULL, '2000-08-06', '1003'), ('E11', 'Dixit', 'Madhuri', NULL, '2002-11-07', '1004'), ('E12', 'Gehlot', 'Nidhi', NULL, '2007-10-08', '1005'), ('E13', 'Smith', 'Rebecca', NULL, '2010-11-09', '1006'), ('E14', 'Gomez', 'Pascal', NULL, '2011-08-12', '1007'), ('E15', 'Mims', 'Linda', NULL, '2015-12-13', '1001'), ('E16', 'Hennie', 'Mathew', NULL, '2017-11-18', '1002'), ('E17', 'Pounds', 'Carlos', NULL, '2016-02-23', '1003'), ('E18', 'Shorthouse', 'Jordan', NULL, '2014-01-27', '1004'), ('E19', 'Ruck', 'Minnie', NULL, '2002-11-27', '1005'), ('E20', 'Rathi', 'Sneha', NULL, '2007-05-24', '1006'), ('E21', 'Sharma', 'Apeksha', NULL, '2005-04-23', '1007'), ('E22', 'Rathi', 'Ekta', NULL, '2005-11-19', '1001'), ('E23', 'Balachandran', 'Gayathri', NULL, '2002-03-18', '1002'), ('E24', 'Shah', 'Nikhil', NULL, '2001-02-16', '1003'), ('E25', 'Pandey', 'Pankaj', NULL, '2018-01-06', '1004'); INSERT INTO SKILL (SkillID, SkillDescription, SkillRate) VALUES ('S1', 'Data Entry I', 12.00), ('S2', 'Data Entry II', 22.00), ('S3', 'System Analyst I', 30.00), ('S4', 'System Analyst II', 40.00), ('S5', 'Database Designer I', 40.00), ('S6', 'Database Designer II', 50.00), ('S7', 'Java I', 35.00), ('S8', 'Java II', 45.00), ('S9', 'C++ I', 35.00), ('S10', 'C++ II', 45.00), ('S11', 'Python I', 35.00), ('S12', 'Python II', 45.00); INSERT INTO EMPsKILL (EmpID, SkillID) VALUES

('E7', 'S7'), ('E8', 'S8'),

('E9', 'S9'), ('E10', 'S10'),

('E11', 'S11'), ('E12', 'S12'),

('E13', 'S1'), ('E14', 'S12'),

('E15', 'S11'), ('E16', 'S10'),

('E17', 'S9'), ('E18', 'S8'),

('E19', 'S7'), ('E20', 'S6'),

('E21', 'S5'), ('E22', 'S4'),

('E23', 'S3'), ('E24', 'S2'),

('E25', 'S1');

SELECT * FROM REGION;

SELECT * FROM CUSTOMER;

SELECT * FROM EMPLOYEE;

-- 2 Average, Maximum and Minimum Skill Rate

SELECT

ROUND(AVG(SkillRate), 2) 'Average',

MAX(SkillRate) 'Maximum',

MIN(SkillRate) 'Minimum'

FROM SKILL;

3. Write a query to practice joins.

-- 3 Customers in Northeast Region

SELECT CusName

FROM CUSTOMER NATURAL JOIN REGION

WHERE RegionName = 'Northeast';

4. Write a query to practice subqueries.

-- 4 Employees with SkillRate > 15

SELECT DISTINCT EmpID

FROM EMPLOYEE

WHERE EmpID IN (

SELECT EmpID

FROM EMPSKILL NATURAL JOIN SKILL

WHERE SkillRate > 15)

ORDER BY EmpID;