SQL Assignment Problem and Solutions

Problem 1:
Create tables for the following, where the first 2 are entity-types and the last one is relationship.
Feel free to add additional fields that you think are appropriate.

Course: courseNum, department, semester, maximumEnrolled
Instructor: ssn, instructorid, firstName, lastName.
Teaches: instructorid, courseNum
Pay special attention to assigning primary key and foreign key in each table and use
appropriate data types.
Print out the SQL commands that you use to create the tables.
Then populate your tables with at least 2 rows each and print out the contents of each
table with SELECT. (Include the content of the print-out in your homework).

 

SQL Assignment Solution: Problem 1

CREATE DATABASE mysqldb;

CREATE TABLE Course(
courseNum int,
department varchar(20),
semester int,
maximumEnrolled int,
primary key(courseNum)
);

CREATE TABLE Instructor(
ssn varchar(9) UNIQUE NOT NULL,
instructorid int,
firstName varchar(20),
lastName varchar(20),
primary key(instructorid)

 

);

INSERT INTO Course(courseNum, department, semester,
maximumEnrolled)
VALUES
(2,'Literature',3,245),(1,'Science',4,563),(3,'History',2,149),(
4,'Mathematics',6,234);

INSERT INTO Instructor(ssn, instructorid, firstName, lastName)
VALUES
(123456789,22,'Eddy','Frenson'),(234567891,33,'Lia','Wayne'),(34
5678912,44,'John','Smith');

CREATE TABLE Teaches(
courseNum int,
instructorid int,
FOREIGN KEY (courseNum) REFERENCES Course(courseNum),
FOREIGN KEY (instructorid) REFERENCES Instructor(instructorid)
);

INSERT INTO Teaches(courseNum,instructorid)
VALUES(2,22),(1,33),(3,44);

-- OUTPUT:

SELECT * FROM Course;

SELECT * FROM Instructor;

SELECT * FROM Teaches;

 

Problem 2:
Get to know the HR database, its tables, and their contents. Show the SQL query and
the output. Use INNER JOIN for multiple table query.
1- Display details of jobs where the minimum salary is greater than 10000.
2- Display the first name and join date of the employees who joined between 2002
and 2005.
3- Display first name and join date of the employees who is either IT Programmer or
Sales Man.
4- Display details of employee with ID 150 or 160.

 

SQL Assignment Solution: Problem 2

CREATE DATABASE mysqldb2;

CREATE TABLE Employee(
id INT,
first_name VARCHAR(20),
join_date DATE,
job_role VARCHAR(20),
salary INT,
PRIMARY KEY(id)
);

INSERT INTO Employee (id, first_name, join_date, job_role,
salary)
VALUES (140, 'Albert', '2003-03-20', 'Sales Man', 15000),
(170, 'David', '2001-10-16', 'IT Programmer', 25000),
(150, 'Christine', '2008-05-17', 'HR Manager', 20000),
(130, 'Daniel', '2005-11-07', 'Graphic Designer', 5000),
(160, 'Ben', '1999-02-26', 'Sales Man', 18000),
(120, 'Robert', '2001-08-12', 'Accountant', 8000);

Page 6 | 6

SELECT * FROM Employee;

-- OBJ-1
SELECT * FROM Employee WHERE Salary > 10000;

-- OBJ-2

SELECT first_name, join_date FROM Employee WHERE YEAR(join_date)
>= 2002 AND YEAR(join_date) <= 2005;
SELECT first_name, join_date FROM Employee WHERE YEAR(join_date)
BETWEEN 2002 AND 2005;

-- OBJ-3

SELECT first_name, join_date FROM Employee WHERE job_role = 'IT
Programmer' OR job_role = 'Sales Man';

-- OBJ-4

SELECT * FROM Employee WHERE id = 150 OR id = 160;