SQL Queries Company DB example - Srinivas Narasegouda

Consider the Company database with the following Schema
EMPLOYEE (FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO)
DEPARTMENT(DNAME,DNUMBER,MGRSSN,MSRSTARTDATE)
DEPT_LOCATIONS (DNUMBER, DLOCATION)PROJECT (PNAME, PNUMBER, PLOCATION, DNUM)WORKS_ON(ESSN, PNO, HOURS)
DEPENDENT(ESSN,DEPENDENT_NAME,SEX,BDATE,RELATIONSHIP)

Delete an existing Database

-- Following statement will drop (delete) the database named "Company_DB_Reg_No"
drop database Company_DB_Reg_No;

Create the database “Company_DB_Reg_No”

-- SQL QUERY FOR CREATING THE DATABASE COMPANY_DB
CREATE DATABASE Company_DB_Reg_No;

Create the table “Department”

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

-- SQL QUERY FOR CREATING THE TABLE Department
CREATE TABLE DEPARTMENT
(
    DNUMBER INT PRIMARY KEY,
    DNAME VARCHAR(30) UNIQUE,
    MGRSSN CHAR(9),
    MGRSTARTDATE DATE
);

INSERTING A RECORD INTO THE TABLES: Department

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

INSERT INTO DEPARTMENT VALUES
(1,'HR',NULL,'2020-01-01'),
(2,'Accounts',NULL,'2020-02-01'),
(3,'Research',NULL,'2020-03-01'),
(4,'Sales',NULL,'2020-04-01'),
(5,'IT',NULL,'2020-05-01');

Select * from DEPARTMENT;

Create the table “Employee”

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

-- SQL QUERY FOR CREATING THE TABLE Employee
CREATE TABLE EMPLOYEE
(
    SSN CHAR(9) PRIMARY KEY,
    FNAME VARCHAR(20),
    MINIT CHAR(1),
    LNAME VARCHAR(20),
    BDATE DATE,
    ADDRESS VARCHAR(100),
    SEX CHAR(1),
    SALARY DECIMAL(10,2),
    SUPERSSN CHAR(9),
    DNO INT,

    FOREIGN KEY (DNO)
    REFERENCES DEPARTMENT(DNUMBER)
);

INSERTING A RECORD INTO THE TABLES: Employee

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

INSERT INTO EMPLOYEE VALUES
('ssn001','Rahul','K','Sharma','1992-03-10','Bangalore','M',45000,NULL,5),
('ssn002','Priya','R','Nair','1995-06-20','Kochi','F',38000,'ssn001',2),
('ssn003','Amit','S','Patel','1988-09-15','Ahmedabad','M',55000,'ssn001',3),
('ssn004','Sneha','P','Rao','1993-11-18','Mysore','F',42000,'ssn003',3),
('ssn005','Arjun','M','Reddy','1991-01-25','Hyderabad','M',60000,'ssn001',5),
('ssn006','Kiran','V','Kumar','1987-08-11','Chennai','M',39000,'ssn005',2),
('ssn007','Pooja','A','Joshi','1996-05-17','Pune','F',47000,'ssn003',3),
('ssn008','Deepak','N','Singh','1989-10-08','Delhi','M',52000,'ssn005',5),
('ssn009','Neha','T','Gupta','1994-07-12','Lucknow','F',36000,'ssn004',4),
('ssn010','Rohan','J','Das','1998-12-30','Kolkata','M',43000,'ssn008',5);

select * from EMPLOYEE;

Create the table “Dept_Locations”

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

-- SQL QUERY FOR CREATING THE TABLE DEPT_LOCATIONS
CREATE TABLE DEPT_LOCATIONS
(
    DNUMBER INT,
    DLOCATION VARCHAR(30),

    PRIMARY KEY(DNUMBER,DLOCATION),

    FOREIGN KEY(DNUMBER)
    REFERENCES DEPARTMENT(DNUMBER)
);

INSERTING A RECORD INTO THE TABLES: DEPT_LOCATIONS

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

INSERT INTO DEPT_LOCATIONS VALUES
(1,'Bangalore'),
(2,'Mumbai'),
(3,'Delhi'),
(4,'Hyderabad'),
(5,'Pune');

Select * from DEPT_LOCATIONS;

Creating the table ” Project”

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

-- SQL QUERY FOR CREATING THE TABLE Project
CREATE TABLE PROJECT
(
    PNUMBER INT PRIMARY KEY,
    PNAME VARCHAR(30),
    PLOCATION VARCHAR(30),
    DNUM INT,

    FOREIGN KEY(DNUM)
    REFERENCES DEPARTMENT(DNUMBER)
);

INSERTING A RECORD INTO THE TABLES: PROJECT

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

INSERT INTO PROJECT VALUES
(101,'Payroll','Mumbai',2),
(102,'AI Research','Delhi',3),
(103,'Website','Pune',5),
(104,'Sales App','Hyderabad',4),
(105,'Recruitment','Bangalore',1);

Select * from PROJECT;

Creating the table ” Works_On”

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

-- SQL QUERY FOR CREATING THE TABLE Works_On
CREATE TABLE WORKS_ON
(
    ESSN CHAR(9),
    PNO INT,
    HOURS DECIMAL(5,2),

    PRIMARY KEY(ESSN,PNO),

    FOREIGN KEY(ESSN)
    REFERENCES EMPLOYEE(SSN),

    FOREIGN KEY(PNO)
    REFERENCES PROJECT(PNUMBER)
);

INSERTING A RECORD INTO THE TABLES: Works_On

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

INSERT INTO WORKS_ON VALUES
('ssn001',103,20),
('ssn003',102,35),
('ssn004',102,30),
('ssn007',102,25),
('ssn005',103,40),
('ssn008',103,25),
('ssn010',103,30),
('ssn002',101,40),
('ssn006',101,30),
('ssn009',104,40);

Select * from WORKS_ON;

Creating the table “Dependent”

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

-- SQL QUERY FOR CREATING THE TABLE Dependent
CREATE TABLE DEPENDENT
(
    ESSN CHAR(9),
    DEPENDENT_NAME VARCHAR(30),
    SEX CHAR(1),
    BDATE DATE,
    RELATIONSHIP VARCHAR(20),

    PRIMARY KEY(ESSN,DEPENDENT_NAME),

    FOREIGN KEY(ESSN)
    REFERENCES EMPLOYEE(SSN)
);

INSERTING A RECORD INTO THE TABLES: Dependent

-- Use the following database to execute the queries
USE Company_DB_Reg_No;
GO

INSERT INTO DEPENDENT VALUES
('ssn001','Ananya','F','2018-01-01','Daughter'),
('ssn002','Rajesh','M','1965-04-10','Father'),
('ssn003','Riya','F','2017-08-15','Daughter'),
('ssn005','Lakshmi','F','1994-11-12','Wife'),
('ssn008','Aarav','M','2020-07-01','Son');

Select * from DEPENDENT;