college database



-- 1. Create Tables

CREATE TABLE department (

    deptid INT PRIMARY KEY,

    deptname VARCHAR(50)

);


CREATE TABLE teacher (

    tid INT PRIMARY KEY,

    tname VARCHAR(50),

    Dob DATE,

    gender VARCHAR(10),

    salary DECIMAL(10,2),

    address VARCHAR(100),

    dno INT

);


CREATE TABLE semester (

    Semid INT PRIMARY KEY,

    Semname VARCHAR(20)

);


CREATE TABLE student (

    regno INT PRIMARY KEY,

    sname VARCHAR(50),

    phone VARCHAR(15),

    dnumber INT,

    sem INT

);


CREATE TABLE subject (

    subid INT PRIMARY KEY,

    Subname VARCHAR(50),

    dnum INT,

    semnum INT,

    teacherid INT

);


-- 2. Insert Values

INSERT INTO department VALUES

(1, 'Computer Science'),

(2, 'Electronics'),

(3, 'Mechanical'),

(4, 'Civil'),

(5, 'Electrical');


INSERT INTO teacher VALUES

(101, 'Dr. Kavitha', '1985-06-15', 'Female', 65000, 'Bangalore', 1),

(102, 'Mr. Manjunath', '1980-03-22', 'Male', 70000, 'Mysore', 1),

(103, 'Mrs. Rekha', '1987-11-02', 'Female', 60000, 'Hubli', 2),

(104, 'Mr. Ramesh', '1979-12-05', 'Male', 75000, 'Davangere', 3),

(105, 'Dr. Prakash', '1982-08-30', 'Male', 72000, 'Belagavi', 4);


INSERT INTO semester VALUES

(1, 'I Sem'),

(2, 'II Sem'),

(3, 'III Sem'),

(4, 'IV Sem'),

(5, 'V Sem');


INSERT INTO student VALUES

(201, 'Rakesh', '9876543210', 1, 3),

(202, 'Shivaraj', '7896541230', 1, 3),

(203, 'Rajakumar', '9123456789', 1, 2),

(204, 'Suhas', '9988776655', 2, 4),

(205, 'Manoj', '9012345678', 3, 1);


INSERT INTO subject VALUES

(301, 'Database Systems', 1, 3, 101),

(302, 'Computer Networks', 1, 4, 102),

(303, 'Digital Electronics', 2, 3, 103),

(304, 'Thermodynamics', 3, 2, 104),

(305, 'Structural Analysis', 4, 5, 105);


-- 3. Alter Tables (Add Foreign Keys)

ALTER TABLE teacher

ADD CONSTRAINT fk_teacher_dept

FOREIGN KEY (dno) REFERENCES department(deptid);


ALTER TABLE student

ADD CONSTRAINT fk_student_dept

FOREIGN KEY (dnumber) REFERENCES department(deptid),

ADD CONSTRAINT fk_student_sem

FOREIGN KEY (sem) REFERENCES semester(Semid);


ALTER TABLE subject

ADD CONSTRAINT fk_subject_dept

FOREIGN KEY (dnum) REFERENCES department(deptid),

ADD CONSTRAINT fk_subject_sem

FOREIGN KEY (semnum) REFERENCES semester(Semid),

ADD CONSTRAINT fk_subject_teacher

FOREIGN KEY (teacherid) REFERENCES teacher(tid);



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

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', ...
Read more