Scribd Logo
Upload

Welcome to Scribd!

  • 7 views

    Database

    Uploaded by

    Ghulam Fareed
    The document describes creating tables using primary and foreign keys to store student and course data. It then inserts sample data into the student table. It also creates six tables to store employee, department, job title, salary, benefit, and contact information data, inserts sample data, and creates a view to join the tables and display employee information.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Database

    Uploaded by

    Ghulam Fareed
    7 views6 pages
    The document describes creating tables using primary and foreign keys to store student and course data. It then inserts sample data into the student table. It also creates six tables to store employee, department, job title, salary, benefit, and contact information data, inserts sample data, and creates a view to join the tables and display employee information.

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes creating tables using primary and foreign keys to store student and course data. It then inserts sample data into the student table. It also creates six tables to store employee, department, job title, salary, benefit, and contact information data, inserts sample data, and creates a view to join the tables and display employee information.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    7 views6 pages

    Database

    Uploaded by

    Ghulam Fareed
    The document describes creating tables using primary and foreign keys to store student and course data. It then inserts sample data into the student table. It also creates six tables to store employee, department, job title, salary, benefit, and contact information data, inserts sample data, and creates a view to join the tables and display employee information.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 6

    create Table for student using primary or foreign key:

    -- Create the Students table with a primary key ( this is comments for your understanding
    not part of code.)
    CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    dob DATE
    );

    -- Create the Courses table with a foreign key referencing Students ( this is comments for
    your understanding not part of code.)
    CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    instructor VARCHAR(100),
    student_id INT,
    FOREIGN KEY (student_id) REFERENCES Students(student_id)
    );

    Dynamically insert 3 row in table:

    INSERT INTO student (student_id, name, subject)


    VALUES
    (1, Ali, science),
    (2, Usman, maths),
    (3, Mubeen, physics);
    create view and joins of six tables:
    -- Create the Employees table ( this is comments for your
    understanding not part of code.)
    CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT,
    JobTitleID INT,
    ContactInfoID INT
    );

    -- Create the Departments table ( this is comments for your


    understanding not part of code.)
    CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(100)
    );

    -- Create the JobTitles table ( this is comments for your


    understanding not part of code.)
    CREATE TABLE JobTitles (
    JobTitleID INT PRIMARY KEY,
    JobTitleName VARCHAR(100)
    );

    -- Create the Salaries table ( this is comments for your understanding


    not part of code.)
    CREATE TABLE Salaries (
    SalaryID INT PRIMARY KEY,
    EmployeeID INT,
    SalaryAmount DECIMAL(10, 2)
    );

    -- Create the Benefits table ( this is comments for your understanding


    not part of code.)
    CREATE TABLE Benefits (
    BenefitID INT PRIMARY KEY,
    EmployeeID INT,
    BenefitName VARCHAR(100)
    );

    -- Create the ContactInfo table ( this is comments for your


    understanding not part of code.)
    CREATE TABLE ContactInfo (
    ContactInfoID INT PRIMARY KEY,
    EmployeeID INT,
    Email VARCHAR(100),
    PhoneNumber VARCHAR(20)
    );

    -- Insert data into the Departments table ( this is comments for your
    understanding not part of code.)
    INSERT INTO Departments (DepartmentID, DepartmentName)
    VALUES
    (1, 'HR'),
    (2, 'Engineering'),
    (3, 'Finance');

    -- Insert data into the JobTitles table ( this is comments for your
    understanding not part of code.)
    INSERT INTO JobTitles (JobTitleID, JobTitleName)
    VALUES
    (1, 'Manager'),
    (2, 'Software Engineer'),
    (3, 'Accountant');

    -- Insert data into the Employees table ( this is comments for your
    understanding not part of code.)
    INSERT INTO Employees (EmployeeID, FirstName, LastName,
    DepartmentID, JobTitleID, ContactInfoID)
    VALUES
    (1, 'John', 'Doe', 1, 1, 1),
    (2, 'Jane', 'Smith', 2, 2, 2),
    (3, 'Alice', 'Johnson', 1, 2, 3);

    -- Insert data into the Salaries table ( this is comments for your
    understanding not part of code.)
    INSERT INTO Salaries (SalaryID, EmployeeID, SalaryAmount)
    VALUES
    (1, 1, 75000),
    (2, 2, 80000),
    (3, 3, 70000);

    -- Insert data into the Benefits table ( this is comments for your
    understanding not part of code.)
    INSERT INTO Benefits (BenefitID, EmployeeID, BenefitName)
    VALUES
    (1, 1, 'Health Insurance'),
    (2, 2, '401(k) Matching'),
    (3, 3, 'Paid Time Off');

    -- Insert data into the ContactInfo table ( this is comments for your
    understanding not part of code.)
    INSERT INTO ContactInfo (ContactInfoID, EmployeeID, Email,
    PhoneNumber)
    VALUES
    (1, 1, '[email protected]', '123-456-7890'),
    (2, 2, '[email protected]', '987-654-3210'),
    (3, 3, '[email protected]', '555-123-4567');

    CREATE VIEW Employee_information AS


    SELECT e.EmployeeID, e.FirstName, e.LastName, d.DepartmentName,
    jt.JobTitleName, s.SalaryAmount, b.BenefitName, ci.Email,
    ci.PhoneNumber,
    FROM Employees.e, Departments.d, JobTitles.jt, Salaries.s,
    Benefits.b, ContactInfo.ci
    WHERE
    e.DepartmentID = d.DepartmentID and
    e.JobTitleID = jt.JobTitleID and
    e.EmployeeID = s.EmployeeID and
    e.EmployeeID = b.EmployeeID and
    e.ContactInfoID = ci.ContactInfoID;

    You might also like