SQL Coding Test

Written by Data Pilot
August 09, 2019
  • PostgreSQL
Have a Database Problem? Speak with an Expert for Free
Get Started >>

Instruction

In this coding test, we will be providing you with code to construct an SQL schema. Some database operations will have to be performed on the schema and answers to those will be provided in the end.

Schema Setup Code

Run the below code in SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
CREATE SCHEMA SCHOOL;

CREATE TABLE Teachers (
T_id INT PRIMARY KEY,
T_name TEXT NOT NULL,
T_experience INT NOT NULL
);

CREATE TABLE DegreeProgram (
P_id INT PRIMARY KEY,
P_name TEXT NOT NULL,
T_id INT NOT NULL,
FOREIGN KEY f_key(T_id) REFERENCES Teachers(T_id)
);

CREATE TABLE Students (
S_id INT PRIMARY KEY,
S_name TEXT NOT NULL,
S_age INT NOT NULL,
S_address CHAR (50),
S_regsitration_no INT UNIQUE,
P_id INT NOT NULL
);

INSERT INTO Teachers(
T_id,
T_name,
T_experience
)
VALUES
(1, 'DR. EMILIA ROSE', 10),
(2, 'DR. CLARK KANE', 5),
(3, 'DR. GARRY BLACKWOOD', 7),
(4, 'DR. H.K. ROSE', 13),
(6, 'DR. JONATHAN LAKE', 4);

INSERT INTO DegreeProgram(
P_id,
P_name,
T_id
)
VALUES
(1, 'BACHELORS OF ENGINEERING IN ELECTRICAL ENGINEERING', 6),
(2, 'BACHELORS OF ENGINEERING IN SOFTWARE ENGINEERING', 2),
(3, 'BACHELORS OF SCIENCES IN COMPUTER SCIENCE', 1),
(4, 'BACHELORS OF ENGINEERING IN MeCHANICAL ENGINEERING', 3),
(5, 'BACHELORS OF ENGINEERING IN CIVIL ENGINEERING', 4);

INSERT INTO students(
S_id,
S_name,
S_age,
S_address,
S_regsitration_no,
P_id
)
VALUES
(0,'JOHN CONNER',19,'23 BOULEVARD ST.',1,3),
(1,'ADAM SMITH',21,'28 BOULEVARD ST.',2,1),
(2,'BLAKE JONES',23,'22 BOULEVARD ST.',3,5),
(3,'BRIDGETTE JOHNSON',18,'21 BOULEVARD ST.',4,5),
(4,'CMAILIA BLACK',20,'27 BOULEVARD ST.',5,3),
(5,'AMY WINEHOUSE',20,'23 BOULEVARD ST.',6,2),
(6,'CHRIS JONES',23,'01 BOULEVARD ST.',7,4),
(8,'JOHNATHAN CONNER',20,'MAIN BOULEVARD',10,1),
(7,'JOHN SMITH',21,'XYS ST. GATE',9,4);

Questions

Now that you have the tables and data you need let’s get to the questions:

  1. Add a foreign key constraint on table students. Make attribute P_id refer to P_id in DegreePrograms table.

  2. Add student with the following information in students table. S_id = 9 S_name = CAPRICE WINSELET S_age = 20 S_registration_no = 8 P_id = 1 S_address = ABC street 22-A

  3. Try dropping the table teachers. See if it is successfully dropped or not. Explain the reason if it’s not successfully dropped.

  4. Get the number of students in each degree program and label the column as ‘number of students’ in ascending order.

  5. Show which teacher has the maximum experience.

  6. See which program is taught by the teacher with minimum experience and who is the teacher. Show his details too.

  7. Show all teachers who do not teach programs with id 1 and id 4.

  8. Show how many degree programs are students enrolled in. Show their details.

  9. See which student is taught by which teacher.

  10. Delete all students who are greater than 22 years old.

Answers

Answer 1

To add a foreign key to an existing table, we use the ALTER TABLE command. The query to be used is:

1
2
ALTER TABLE students
ADD FOREIGN KEY f_key(P_id) REFERENCES degreeprogram(P_id);

Answer 2

To add a student, we will use the insert into statement supported by the SQL database.

1
2
INSERT INTO students
VALUES (9,'CAPRICE WINSELET',20,'ABC street 22-A',8,1);

Answer 3

The drop table statement is used to drop the table:

1
DROP TABLE teachers;

The table fails to drop because of the foreign key constraint on DegreePrograms table. So we find out that we can’t delete a parent table which is referred somewhere in another table.

Answer 4

Group by statement and join will be used to do what is asked in the question.

1
2
3
4
SELECT P_name, COUNT (*) AS 'Number of students'
FROM degreeprogram JOIN students
USING (P_id)
GROUP BY (P_id);

Answer 5

This answer to this question will use a nested query since we need the value from one query into another query.

1
2
3
4
5
6
SELECT T_name, T_experience
FROM teachers
WHERE T_experience = (
SELECT MAX(T_experience)
FROM teachers
);

Answer 6

This query will do the required operation:

1
2
3
4
5
6
SELECT T_name, T_experience, T_id, P_name
FROM teachers JOIN degreeprogram
USING (T_id)
WHERE T_experience = (
    SELECT MIN(T_experience)
    FROM teachers);

Answer 7

In this query, we will be using the OR operator in SQL along with the NOT IN operator.

1
2
3
4
5
6
7
SELECT *
FROM TEACHERS
WHERE T_id NOT IN (
SELECT T_id
FROM degreeprogram
WHERE P_id =1 OR P_id = 4
);

Answer 8

The DISTINCT clause that we studied in SQL is useful for this operation since the degree programs are repeated, we only need one instance of them.

1
2
3
4
5
6
SELECT *
FROM DegreeProgram
WHERE P_id IN (
    SELECT DISTINCT(P_id)
    FROM students
);

Answer 9

This task can be done with 3 nested queries or 3 tables join. we will do it using 3 table joins.

1
2
SELECT S_name, T_name
FROM (students JOIN degreeprogram USING (P_id)) JOIN teachers USING(T_ID);

Answer 10

This operation requires the use of the SQL DELETE clause.

1
2
DELETE FROM students
WHERE S_age > 22;

Thanks for taking our SQL coding test. We hope you were able to level up your PostgreSQL skills!

Rate 1 Star2 Stars3 Stars4 Stars5 Stars

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started
Related Topics:
Additional Information:

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.