SQL simple Queries to create Database & Tables and Insert Values

  SQL Queries to create Database & Tables

# Creating ABC_College database and tables

CREATE DATABASE ABC_College;

USE ABC_College;

# Creating course table

CREATE TABLE COURSE(

    coursecode varchar(5) NOT NULL PRIMARY KEY,

    title varchar(20) NOT NULL,

    school varchar(20) NOT NULL

);

# Creating student table

CREATE TABLE student (

    regno varchar(6) NOT NULL PRIMARY KEY,

    name varchar(20) NOT NULL,

    DOB date NOT NULL,

    telno varchar(20) NOT NULL,

    Coursecode varchar(10) NOT NULL,

    FOREIGN KEY(Coursecode) REFERENCES COURSE(Coursecode)

);

# Creating unit table

CREATE TABLE UNIT(

    unitcode varchar(5) NOT NULL PRIMARY KEY,

    title varchar (20) NOT NULL,

    year int NOT NULL

);

# Creating results table

CREATE TABLE RESULTS(

    regno varchar(6) NOT NULL,

    unitcode varchar(5) NOT NULL,

    exammark int NOT NULL,

    cwkmark int NOT NULL,

    PRIMARY KEY(regno, unitcode),

    FOREIGN KEY(regno) REFERENCES STUDENT(regno),

    FOREIGN KEY(unitcode) REFERENCES UNIT(unitcode)

);

Notes:

    *If any table contains a foreign key referencing to another table, you have to create that table before creating this table.

    *If any table's primary key contains more than one column, you have to mention all columns in one line as "PRIMARY KEY(regno, unitcode)".

SQL Queries to create Database & Tables

USE abc_college;

# Inserting single value into course table

INSERT INTO course VALUES

('AB12','Applied Biology','Life Sciences');

# Inserting multiple value into course table

INSERT INTO course VALUES

('CE65','Civil Engineering','Engineering'),

    ('CS30','Computing Science','Computing');

# Inserting values into student table

INSERT INTO student VALUES

('PO123','John','1976-02-09','01322-843311','CE65'),

    ('F4567','Sally','1972-01-01','020-73318844','CS30'),

    ('F8910','Andrew','1977-12-06','01322-865833','AB12'),

    ('P7651','Brian','1974-11-21','020-85466540','CS30');

# Inserting values into unit table

INSERT INTO unit VALUES

('ES32','Expert system',2),

    ('PH90','Physics',1),

    ('MA43','Mathematics',2),

    ('FP54','Food preservation',3),

    ('RD19','Relational Databases',2),

    ('HA34','Human Anatomy',3),

    ('ES22','Engineering Science',2);

 

 # Inserting values into result table

INSERT INTO results VALUES

('F4567','ES32',67,90),

    ('F4567','MA43',32,21),

    ('F4567','RD19',76,100),

    ('F8910','FP54',78,12),

    ('F8910','HA34',55,23),

    ('P7651','ES32',33,66);

Thank You