company database

 -- 1. Create the 'department' table

CREATE TABLE department (

    Dname VARCHAR(20),

    Dnum INT PRIMARY KEY,

    Mgr_ssn INT,

    MgrStdate DATE

);


-- 2. Create the 'employee' table

CREATE TABLE employee (

    Fname VARCHAR(20),

    Mname CHAR(1),

    Lname VARCHAR(20),

    Ssn INT PRIMARY KEY,

    Bdate DATE,

    address VARCHAR(100),

    Gender CHAR(1),

    salary INT,

    Super_ssn INT,

    Dno INT,

    FOREIGN KEY (Dno) REFERENCES department(Dnum)

);


-- 3. Insert data into 'department' table

INSERT INTO department VALUES ('Research', 5, 333445555, '1988-05-22');

INSERT INTO department VALUES ('Administration', 4, 987654321, '1995-01-01');

INSERT INTO department VALUES ('Headquarters', 1, 888665555, '1981-06-19');


-- 4. Insert data into 'employee' table

INSERT INTO employee VALUES ('John', 'B', 'Smith', 123456789, '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, 333445555, 5);

INSERT INTO employee VALUES ('Franklin', 'T', 'Wong', 333445555, '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5);

INSERT INTO employee VALUES ('Alicia', 'J', 'Zelaya', 999887777, '1968-01-19', '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4);

INSERT INTO employee VALUES ('Jennifer', 'S', 'Wallace', 987654321, '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4);

INSERT INTO employee VALUES ('Ramesh', 'K', 'Narayan', 666884444, '1962-09-15', '975 Fire Oak, Humble, TX', 'M', 38000, 333445555, 5);

INSERT INTO employee VALUES ('Joyce', 'A', 'English', 453453453, '1972-07-31', '5631 Rice, Houston, TX', 'F', 25000, 333445555, 5);

INSERT INTO employee VALUES ('Ahmad', 'V', 'Jabbar', 987987987, '1969-03-29', '980 Dallas, Houston, TX', 'M', 25000, 987654321, 4);

INSERT INTO employee VALUES ('James', 'E', 'Borg', 888665555, '1937-11-10', '450 Stone, Houston, TX', 'M', 55000, NULL, 1);


-- 5. Add foreign key constraint for manager SSN

ALTER TABLE department ADD FOREIGN KEY (Mgr_ssn) REFERENCES employee(Ssn);


-- 6. Add foreign key for supervisor (self-referencing)

ALTER TABLE employee ADD FOREIGN KEY (Super_ssn) REFERENCES employee(Ssn);


-- 7. Create 'dept_location' table

CREATE TABLE dept_location (

    Dnumber INT,

    Dept_location VARCHAR(20),

    FOREIGN KEY (Dnumber) REFERENCES department(Dnum)

);


-- 8. Insert data into 'dept_location'

INSERT INTO dept_location VALUES (1, 'Houston');

INSERT INTO dept_location VALUES (4, 'Stafford');

INSERT INTO dept_location VALUES (5, 'Bellaire');

INSERT INTO dept_location VALUES (5, 'Sugarland');

INSERT INTO dept_location VALUES (5, 'Houston');


-- 9. Create 'project' table

CREATE TABLE project (

    Pname VARCHAR(20),

    Pnumber INT PRIMARY KEY,

    Plocation VARCHAR(20),

    Dnum INT,

    FOREIGN KEY (Dnum) REFERENCES department(Dnum)

);


-- 10. Insert data into 'project'

INSERT INTO project VALUES ('ProductX', 1, 'Bellaire', 5);

INSERT INTO project VALUES ('ProductY', 2, 'Sugarland', 5);

INSERT INTO project VALUES ('ProductZ', 3, 'Houston', 5);

INSERT INTO project VALUES ('Computerization', 10, 'Stafford', 4);

INSERT INTO project VALUES ('Reorganization', 20, 'Houston', 1);

INSERT INTO project VALUES ('Newbenefits', 30, 'Stafford', 4);


-- 11. Create 'dependent' table

CREATE TABLE dependent (

    Essn INT,

    Dependent_name VARCHAR(20),

    Gender CHAR(1),

    Bdate DATE,

    Relationship VARCHAR(20),

    PRIMARY KEY (Essn, Dependent_name),

    FOREIGN KEY (Essn) REFERENCES employee(Ssn)

);


-- 12. Insert data into 'dependent'

-- (Fixed wrong SSN 33345555 -> 333445555)

INSERT INTO dependent VALUES (333445555, 'Alice', 'F', '1986-04-05', 'Daughter');

INSERT INTO dependent VALUES (333445555, 'Theodore', 'M', '1983-10-25', 'Son');

INSERT INTO dependent VALUES (333445555, 'Joy', 'F', '1958-05-03', 'Spouse');

INSERT INTO dependent VALUES (987654321, 'Abner', 'M', '1942-02-28', 'Spouse');

INSERT INTO dependent VALUES (123456789, 'Michael', 'M', '1988-01-04', 'Son');

INSERT INTO dependent VALUES (123456789, 'Alice', 'F', '1988-12-30', 'Daughter');

INSERT INTO dependent VALUES (123456789, 'Elizabeth', 'F', '1967-05-05', 'Spouse');


-- 13. Create 'works_on' table (corrected table name from 'workes_on')

CREATE TABLE works_on (

    Essn INT,

    Pno INT,

    Hours FLOAT,

    FOREIGN KEY (Essn) REFERENCES employee(Ssn),

    FOREIGN KEY (Pno) REFERENCES project(Pnumber)

);


-- 14. Insert data into 'works_on'

INSERT INTO works_on VALUES (123456789, 1, 32.5);

INSERT INTO works_on VALUES (123456789, 2, 7.5);

INSERT INTO works_on VALUES (666884444, 3, 40.0);

INSERT INTO works_on VALUES (453453453, 1, 20.0);

INSERT INTO works_on VALUES (453453453, 2, 20.0);

INSERT INTO works_on VALUES (333445555, 2, 10.0);

INSERT INTO works_on VALUES (333445555, 3, 10.0);

INSERT INTO works_on VALUES (333445555, 10, 10.0);

INSERT INTO works_on VALUES (333445555, 20, 10.0);

INSERT INTO works_on VALUES (999887777, 30, 30.0);

INSERT INTO works_on VALUES (999887777, 10, 10.0);

INSERT INTO works_on VALUES (987987987, 10, 35.0);

INSERT INTO works_on VALUES (987987987, 30, 5.0);

INSERT INTO works_on VALUES (987654321, 30, 20.0);

INSERT INTO works_on VALUES (987654321, 20, 15.0);

-- You can insert NULL in numeric columns (like Hours) if needed:

INSERT INTO works_on VALUES (888665555, 20, NULL);

Comments

Post a Comment

Popular posts from this blog

college

Image
CREATE TABLE Department (     dept_id INT PRIMARY KEY,     name VARCHAR(50) ); CREATE TABLE Students (     reg_no INT PRIMARY KEY,     name VARCHAR(50),     address VARCHAR(100),     bcourse VARCHAR(50),     dept_id INT,     FOREIGN KEY (dept_id) REFERENCES Department(dept_id) ); CREATE TABLE Teachers (     teacher_id INT PRIMARY KEY,     name VARCHAR(50),     email VARCHAR(100),     address VARCHAR(100),     dept_id INT,     FOREIGN KEY (dept_id) REFERENCES Department(dept_id) ); CREATE TABLE Courses (     course_id INT PRIMARY KEY,     name VARCHAR(50),     credits INT,     dept_id INT,     teacher_id INT,     FOREIGN KEY (dept_id) REFERENCES Department(dept_id),     FOREIGN KEY (teacher_id) REFERENCES Teachers(teacher_id) ); CREATE TABLE Library (     library_id INT P...
Read more

company

Image
-- 1. Create DEPARTMENT (basic, no FK yet) CREATE TABLE DEPARTMENT (     Dname VARCHAR(50) NOT NULL,     Dnumber INT PRIMARY KEY,     Mgr_ssn CHAR(9),     Mgr_start_date DATE ); -- 2. Create EMPLOYEE CREATE TABLE EMPLOYEE (     Fname VARCHAR(20),     Minit CHAR(1),     Lname VARCHAR(20),     Ssn CHAR(9) PRIMARY KEY,     Bdate DATE,     Address VARCHAR(100),     Sex CHAR(1),     Salary DECIMAL(10,2),     Super_ssn CHAR(9),     Dno INT,     FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn),     FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber) ); -- 3. Alter DEPARTMENT (add manager FK → EMPLOYEE) ALTER TABLE DEPARTMENT ADD CONSTRAINT fk_mgr FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn); -- 4. Create DEPT_LOCATIONS CREATE TABLE DEPT_LOCATIONS (     Dnumber INT,     Dlocation VARCHAR(50),     PRIMARY KEY ...
Read more