Scribd
Upload
3 views7 pages

Dbms SQL Tables

The document outlines a practical exercise in SQL, focusing on the creation and manipulation of database tables including Department_Master, Employee_Master, Location_Master, and Job_Master. It details the structure of these tables, including primary and foreign key constraints, and provides SQL commands for inserting data into these tables. The aim is to familiarize the reader with SQL commands related to Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL).

Uploaded by

ash07122006
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views7 pages

Dbms SQL Tables

The document outlines a practical exercise in SQL, focusing on the creation and manipulation of database tables including Department_Master, Employee_Master, Location_Master, and Job_Master. It details the structure of these tables, including primary and foreign key constraints, and provides SQL commands for inserting data into these tables. The aim is to familiarize the reader with SQL commands related to Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL).

Uploaded by

ash07122006
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

24SE02CS123 AARUSH PANCHAL

Practical 3

Aim: Introduction to SQL, DDL, DML, DCL, database and table creation, alteration, defining
constraints, primary key, foreign key, unique, not null, check.

Appendix – 1: Master Tables

Table 1. Department_Master

Foreign Key: MANAGER_ID, LOCATION_ID

CREATE TABLE Department_Master (


DEPARTMENT_ID NUMBER(4,0) PRIMARY KEY,
DEPARTMENT_NAME VARCHAR2(30) NOT NULL,
MANAGER_ID NUMBER(6,0),
LOCATION_ID NUMBER(4,0),
FOREIGN KEY (MANAGER_ID) REFERENCES Employee_Master(EMPLOYEE_ID),
FOREIGN KEY (LOCATION_ID) REFERENCES Location_Master(LOCATION_ID)
);

CREATE TABLE Employee_Master (


EMPLOYEE_ID NUMBER(6,0) PRIMARY KEY,
EMPLOYEE_NAME VARCHAR2(50)
);

CREATE TABLE Location_Master (


LOCATION_ID NUMBER(4,0) PRIMARY KEY,
LOCATION_NAME VARCHAR2(50)
);

INSERT FOR Employee_master:-


INSERT INTO Employee_Master VALUES (200001, 'Amit Patel');
INSERT INTO Employee_Master VALUES (200002, 'Neha Sharma');
INSERT INTO Employee_Master VALUES (200003, 'Rahul Verma');
INSERT INTO Employee_Master VALUES (200004, 'Sneha Desai');
INSERT INTO Employee_Master VALUES (200005, 'Vikas Mehta');
INSERT INTO Employee_Master VALUES (200007, 'Rohit Kumar');
INSERT INTO Employee_Master VALUES (200008, 'Kavita Joshi');
INSERT INTO Employee_Master VALUES (200010, 'Suresh Rana');

DBMS
24SE02CS123 AARUSH PANCHAL

INSERT FOR Location_master:-


INSERT INTO Location_Master VALUES (3001, 'Mumbai');
INSERT INTO Location_Master VALUES (3002, 'Delhi');
INSERT INTO Location_Master VALUES (3003, 'Bangalore');
INSERT INTO Location_Master VALUES (3004, 'Pune');
INSERT INTO Location_Master VALUES (3005, 'Chennai');
INSERT INTO Location_Master VALUES (3006, 'Hyderabad');
INSERT INTO Location_Master VALUES (3008, 'Kolkata');
INSERT INTO Location_Master VALUES (3010, 'Ahmedabad');

INSERT FOR Department_master:-


INSERT INTO Department_Master VALUES (1001, 'Human Resources', 200001, 3001);
INSERT INTO Department_Master VALUES (1002, 'Finance', 200002, 3002);
INSERT INTO Department_Master VALUES (1003, 'IT', 200003, 3003);
INSERT INTO Department_Master VALUES (1004, 'Marketing', 200004, 3004);
INSERT INTO Department_Master VALUES (1005, 'Sales', 200005, 3005);
INSERT INTO Department_Master VALUES (1006, 'Logistics', NULL, 3006);
INSERT INTO Department_Master VALUES (1007, 'Customer Support', 200007, NULL);
INSERT INTO Department_Master VALUES (1008, 'R&D', 200008, 3008);
INSERT INTO Department_Master VALUES (1009, 'Procurement', NULL, NULL);
INSERT INTO Department_Master VALUES (1010, 'Legal', 200010, 3010);

Table 2. Employee_Master

Foreign Key: MANAGER_ID, DEPARTMENT_ID

DBMS
24SE02CS123 AARUSH PANCHAL

CREATE TABLE Employee_Master (


EMPLOYEE_ID NUMBER(6,0) PRIMARY KEY,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25) NOT NULL,
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
JOB_ID VARCHAR2(10) NOT NULL,
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6,0),
DEPARTMENT_ID NUMBER(4,0),
FOREIGN KEY (MANAGER_ID) REFERENCES Employee_Master(EMPLOYEE_ID),
FOREIGN KEY (DEPARTMENT_ID) REFERENCES Department_Master(DEPARTMENT_ID)
);

INSERT:-
INSERT INTO Employee_Master VALUES (200001, 'Amit', 'Patel', 'amit.patel@example.com', '9876543210',
'2020-01-10', 'HR001', 60000.00, NULL, NULL, 1001);
INSERT INTO Employee_Master VALUES (200002, 'Neha', 'Sharma', 'neha.sharma@example.com',
'9876501234', '2019-03-12', 'FN001', 72000.00, NULL, 200001, 1002);
INSERT INTO Employee_Master VALUES (200003, 'Rahul', 'Verma', 'rahul.verma@example.com',
'9988776655', '2018-05-20', 'IT001', 85000.00, 0.05, 200001, 1003);
INSERT INTO Employee_Master VALUES (200004, 'Sneha', 'Desai', 'sneha.desai@example.com',
'9123456780', '2021-08-15', 'MK001', 56000.00, NULL, 200002, 1004);
INSERT INTO Employee_Master VALUES (200005, 'Vikas', 'Mehta', 'vikas.mehta@example.com',
'9012345678', '2017-11-01', 'SL001', 65000.00, 0.02, 200002, 1005);
INSERT INTO Employee_Master VALUES (200006, 'Pooja', 'Nair', 'pooja.nair@example.com', '9801234567',
'2022-04-11', 'IT002', 50000.00, NULL, 200003, 1003);
INSERT INTO Employee_Master VALUES (200007, 'Rohit', 'Kumar', 'rohit.kumar@example.com',
'9823456789', '2016-06-20', 'CS001', 47000.00, NULL, 200004, 1007);
INSERT INTO Employee_Master VALUES (200008, 'Kavita', 'Joshi', 'kavita.joshi@example.com',
'9765432109', '2015-12-30', 'RD001', 90000.00, 0.10, 200001, 1008);
INSERT INTO Employee_Master VALUES (200009, 'Manoj', 'Shah', 'manoj.shah@example.com',
'9988001122', '2020-02-02', 'PR001', 40000.00, NULL, 200005, 1009);
INSERT INTO Employee_Master VALUES (200010, 'Suresh', 'Rana', 'suresh.rana@example.com',
'9900112233', '2023-01-01', 'LG001', 53000.00, NULL, 200004, 1010);

DBMS
24SE02CS123 AARUSH PANCHAL

Table 3. Job_Master

CREATE TABLE Job_Master (

JOB_ID VARCHAR(10) PRIMARY KEY,

JOB_TITLE VARCHAR(35) NOT NULL,

MIN_SALARY NUMERIC(6,0),

MAX_SALARY NUMERIC(6,0)

);

INSERT:-
INSERT INTO Job_Master (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('J101', 'Software
Engineer', 40000, 80000);

INSERT INTO Job_Master (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('J102', 'Database
Admin', 45000, 85000);

INSERT INTO Job_Master (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('J103', 'Network
Engineer', 42000, 78000);

INSERT INTO Job_Master (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('J104', 'System
Analyst', 43000, 82000);

INSERT INTO Job_Master (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('J105', 'HR Executive',
35000, 60000);

INSERT INTO Job_Master (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('J106', 'Marketing
Lead', 38000, 70000);

INSERT INTO Job_Master (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('J107', 'UI/UX
Designer', 40000, 75000);

INSERT INTO Job_Master (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('J108', 'Project
Manager', 55000, 95000);

INSERT INTO Job_Master (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('J109', 'QA Tester',
36000, 65000);

DBMS
24SE02CS123 AARUSH PANCHAL

INSERT INTO Job_Master (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY) VALUES ('J110', 'Cloud
Architect', 60000, 100000);

Table 4. Location_Master

CREATE TABLE Location_Master (


LOCATION_ID INTEGER PRIMARY KEY,
STREET_ADDRESS VARCHAR(40),
POSTAL_CODE INTEGER NOT NULL,
CITY VARCHAR(30) NOT NULL,
STATE VARCHAR(35),
COUNTRY VARCHAR(30)
);

INSERT:-
INSERT INTO Location_Master (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE, COUNTRY)
VALUES (1001, '123 Main St', 560001, 'Bangalore', 'Karnataka', 'India');
INSERT INTO Location_Master (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE, COUNTRY)
VALUES (1002, '456 Elm St', 110001, 'New Delhi', 'Delhi', 'India');
INSERT INTO Location_Master (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE, COUNTRY)
VALUES (1003, '789 Oak Rd', 400001, 'Mumbai', 'Maharashtra', 'India');
INSERT INTO Location_Master (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE, COUNTRY)
VALUES (1004, '321 Pine Ln', 600001, 'Chennai', 'Tamil Nadu', 'India');
INSERT INTO Location_Master (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE, COUNTRY)
VALUES (1005, '654 Maple Ave', 700001, 'Kolkata', 'West Bengal', 'India');
INSERT INTO Location_Master (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE, COUNTRY)
VALUES (1006, '987 Cedar St', 500001, 'Hyderabad', 'Telangana', 'India');
INSERT INTO Location_Master (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE, COUNTRY)
DBMS
24SE02CS123 AARUSH PANCHAL

VALUES (1007, '111 Birch Blvd', 380001, 'Ahmedabad', 'Gujarat', 'India');


INSERT INTO Location_Master (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE, COUNTRY)
VALUES (1008, '222 Spruce Ct', 160001, 'Chandigarh', NULL, 'India');
INSERT INTO Location_Master (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE, COUNTRY)
VALUES (1009, '333 Redwood Dr', 682001, 'Kochi', 'Kerala', 'India');
INSERT INTO Location_Master (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE, COUNTRY)
VALUES (1010, '444 Fir Way', 302001, 'Jaipur', 'Rajasthan', 'India');

Appendix – 2: Data Tables

Data Table 1. Department_Master

CREATE TABLE Department_Master (


DEPARTMENT_ID INTEGER PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(30) NOT NULL,
MANAGER_ID INTEGER,
LOCATION_ID INTEGER NOT NULL,
FOREIGN KEY (MANAGER_ID) REFERENCES Employee_Master(EMPLOYEE_ID),
FOREIGN KEY (LOCATION_ID) REFERENCES Location_Master(LOCATION_ID)
);
DBMS
24SE02CS123 AARUSH PANCHAL

INSERT:-
INSERT INTO Department_Master (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (10, 'administration', 200, 1700);
INSERT INTO Department_Master (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (20, 'marketing', 201, 1800);
INSERT INTO Department_Master (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (50, 'shipping', 124, 1500);
INSERT INTO Department_Master (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (60, 'IT', 103, 1400);
INSERT INTO Department_Master (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (80, 'sales', 149, 2500);
INSERT INTO Department_Master (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (90, 'exclusive', 100, 1700);
INSERT INTO Department_Master (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (110, 'accounting', 205, 1700);
INSERT INTO Department_Master (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
VALUES (190, 'contracting', NULL, 1700);

DBMS

You might also like