college

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 PRIMARY KEY,

    name VARCHAR(50),

    location VARCHAR(100)

);







Comments

Post a Comment

Popular posts from this blog

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