SQLtoCreateTable.sql

use 24mca;
CREATE TABLE users (
    id                  NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name                VARCHAR2(50) NOT NULL,
    university_id       VARCHAR2(15) UNIQUE NOT NULL,
    course              VARCHAR2(50) NOT NULL,
    university_email    VARCHAR2(100) NOT NULL,
    address             VARCHAR2(150),
    phone_no            VARCHAR2(15),
    password            VARCHAR2(50) NOT NULL,

    -- MST 1 Marks (maximum 20 per subject)
    mst1_python         NUMBER(2) DEFAULT 0 CHECK (mst1_python BETWEEN 0 AND 20),
    mst1_network        NUMBER(2) DEFAULT 0 CHECK (mst1_network BETWEEN 0 AND 20),
    mst1_database       NUMBER(2) DEFAULT 0 CHECK (mst1_database BETWEEN 0 AND 20),
    mst1_web            NUMBER(2) DEFAULT 0 CHECK (mst1_web BETWEEN 0 AND 20),

    -- MST 2 Marks (maximum 20 per subject)
    mst2_python         NUMBER(2) DEFAULT 0 CHECK (mst2_python BETWEEN 0 AND 20),
    mst2_network        NUMBER(2) DEFAULT 0 CHECK (mst2_network BETWEEN 0 AND 20),
    mst2_database       NUMBER(2) DEFAULT 0 CHECK (mst2_database BETWEEN 0 AND 20),
    mst2_web            NUMBER(2) DEFAULT 0 CHECK (mst2_web BETWEEN 0 AND 20)
);

select * from users;