SQL Assignment
Name: Alan Shibu Thomas
Register Number: [Link].U3BCA25309
Semester: 2
Submitted To: SWATHI MAM
Department: Computer Science
College Name: AMRITA MYSURU
Academic Year: 2025 — 2026
Date of Submission: 15/03/2026
QUESTION : Create three tables in table number one
use
one PRIMARY KEY 7 columns. in second table use one
primary key one FOREIGN KEY of the first table and
eight
columns. in third table use one primary key 8 columns
and
one foreign key of the second table Insert 10 rows in
each
table Add 5 subquery includes all three table values.
-- Create Table 1
CREATE TABLE Department (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50),
location VARCHAR(50),
manager VARCHAR(50),
phone VARCHAR(15),
email VARCHAR(50),
established_year INT
);
-- Create Table 2
CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
dept_id INT,
emp_name VARCHAR(50),
position VARCHAR(50),
salary INT,
hire_date DATE,
phone VARCHAR(15),
email VARCHAR(50),
FOREIGN KEY (dept_id) REFERENCES
Department(dept_id)
);
-- Create Table 3
CREATE TABLE Project (
project_id INT PRIMARY KEY,
emp_id INT,
project_name VARCHAR(50),
budget INT,
start_date DATE,
end_date DATE,
status VARCHAR(20),
client_name VARCHAR(50),
FOREIGN KEY (emp_id) REFERENCES
Employee(emp_id)
);
SQL> INSERT INTO EMPLOYEE VALUES
(102,2,'Priya','Accountant',55000,
TO_DATE('2019-02-15','YYYY-MM-DD'),
'9002222222','priya@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(103,3,'Rahul','Developer',70000,
TO_DATE('2021-03-12','YYYY-MM-DD'),
'9003333333','rahul@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(104,4,'Sara','Sales Exec',50000,
TO_DATE('2018-04-18','YYYY-MM-DD'),
'9004444444','sara@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(105,5,'Neha','Marketing Lead',65000,
TO_DATE('2020-05-11','YYYY-MM-DD'),
'9005555555','neha@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(106,6,'Tom','Support Engineer',48000,
TO_DATE('2019-06-14','YYYY-MM-DD'),
'9006666666','tom@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(107,7,'Raj','Admin Officer',45000,
TO_DATE('2021-07-19','YYYY-MM-DD'),
'9007777777','raj@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(108,8,'Maya','Designer',62000,
TO_DATE('2020-08-20','YYYY-MM-DD'),
'9008888888','maya@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(109,9,'Vikram','Security Head',68000,
TO_DATE('2017-09-25','YYYY-MM-DD'),
'9009999999','vikram@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(110,10,'Chris','Researcher',72000,
TO_DATE('2022-10-30','YYYY-MM-DD'),
'9010101010','chris@[Link]');
1 row [Link] INTO Department VALUES
(1,'HR','New
York','John','1111111111','hr@[Link]',2001),
(2,'Finance','London','David','2222222222','finance@co
[Link]',2003),
(3,'IT','Bangalore','Ravi','3333333333','it@[Link]
',2005),
(4,'Sales','Dubai','Ali','4444444444','sales@[Link]
m',2007),
(5,'Marketing','Paris','Emma','5555555555','marketing@
[Link]',2009),
(6,'Support','Tokyo','Ken','6666666666','support@comp
[Link]',2010),
(7,'Admin','Berlin','Max','7777777777','admin@compan
[Link]',2004),
(8,'Design','Rome','Luca','8888888888','design@compa
[Link]',2006),
(9,'Security','Delhi','Arjun','9999999999','security@com
[Link]',2012),
(10,'Research','Sydney','Chris','1010101010','research
@[Link]',2015);
INSERTING VALUES TO TABLE 2
INSERT INTO EMPLOYEE VALUES
(101,1,'Alan','Manager',60000,
TO_DATE('10-JAN-2020','DD-MON-YYYY'),
'9001111111','alan@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(102,2,'Priya','Accountant',55000,
TO_DATE('2019-02-15','YYYY-MM-DD'),
'9002222222','priya@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(103,3,'Rahul','Developer',70000,
TO_DATE('2021-03-12','YYYY-MM-DD'),
'9003333333','rahul@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(104,4,'Sara','Sales Exec',50000,
TO_DATE('2018-04-18','YYYY-MM-DD'),
'9004444444','sara@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(105,5,'Neha','Marketing Lead',65000,
TO_DATE('2020-05-11','YYYY-MM-DD'),
'9005555555','neha@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(106,6,'Tom','Support Engineer',48000,
TO_DATE('2019-06-14','YYYY-MM-DD'),
'9006666666','tom@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(107,7,'Raj','Admin Officer',45000,
TO_DATE('2021-07-19','YYYY-MM-DD'),
'9007777777','raj@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(108,8,'Maya','Designer',62000,
TO_DATE('2020-08-20','YYYY-MM-DD'),
'9008888888','maya@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(109,9,'Vikram','Security Head',68000,
TO_DATE('2017-09-25','YYYY-MM-DD'),
'9009999999','vikram@[Link]');
1 row created.
SQL> INSERT INTO EMPLOYEE VALUES
(110,10,'Chris','Researcher',72000,
TO_DATE('2022-10-30','YYYY-MM-DD'),
'9010101010','chris@[Link]');
1 row created.
INSERTING VALUES TO TABLE 3
SQL> INSERT INTO PROJECT VALUES
(201,101,'HR System',50000,
TO_DATE('2023-01-01','YYYY-MM-DD'),
TO_DATE('2023-06-01','YYYY-MM-DD'),
'Completed','ABC Corp');
1 row created.
SQL> INSERT INTO PROJECT VALUES
(202,102,'Finance App',60000,
TO_DATE('2023-02-01','YYYY-MM-DD'),
TO_DATE('2023-07-01','YYYY-MM-DD'),
'Running','XYZ Ltd');
1 row created.
SQL> INSERT INTO PROJECT VALUES
(203,103,'Website Dev',80000,
TO_DATE('2023-03-01','YYYY-MM-DD'),
TO_DATE('2023-08-01','YYYY-MM-DD'),
'Running','TechSoft');
1 row created.
SQL> INSERT INTO PROJECT VALUES
(204,104,'Sales Portal',40000,
TO_DATE('2023-04-01','YYYY-MM-DD'),
TO_DATE('2023-09-01','YYYY-MM-DD'),
'Pending','RetailCo');
1 row created.
SQL> INSERT INTO PROJECT VALUES
(205,105,'Marketing Tool',55000,
TO_DATE('2023-05-01','YYYY-MM-DD'),
TO_DATE('2023-10-01','YYYY-MM-DD'),
'Running','AdWorks');
1 row created.
SQL> INSERT INTO PROJECT VALUES
(206,106,'Support System',30000,
TO_DATE('2023-06-01','YYYY-MM-DD'),
TO_DATE('2023-11-01','YYYY-MM-DD'),
'Completed','HelpDesk');
1 row created.
SQL> INSERT INTO PROJECT VALUES
(207,107,'Admin Panel',35000,
TO_DATE('2023-07-01','YYYY-MM-DD'),
TO_DATE('2023-12-01','YYYY-MM-DD'),
'Running','OfficePro');
1 row created.
SQL> INSERT INTO PROJECT VALUES
(208,108,'UI Redesign',45000,
TO_DATE('2023-08-01','YYYY-MM-DD'),
TO_DATE('2024-01-01','YYYY-MM-DD'),
'Pending','DesignHub');
1 row created.
SQL> INSERT INTO PROJECT VALUES
(209,109,'Security Upgrade',90000,
TO_DATE('2023-09-01','YYYY-MM-DD'),
TO_DATE('2024-02-01','YYYY-MM-DD'),
'Running','SecureIT');
1 row created.
SQL> INSERT INTO PROJECT VALUES
(210,110,'AI Research',120000,
TO_DATE('2023-10-01','YYYY-MM-DD'),
TO_DATE('2024-05-01','YYYY-MM-DD'),
'Running','FutureTech');
1 row created.
SHOWING THE THREE TABLES
TABLE 1
SELECT * FROM DEPARTMENT;
Table 2:
Table 3:
SUBQUERY USING ALL TABLES VALUES
1)
2)
3)
4)
5)