SQL commands for Class 12 CBSE Computer Science 083

softwares required. with download link

1. JDK version (1.5.0, 1.6.0 or higher) 
2. NetBeans IDE (6.5, 6.7, 6.8 or higher)
3. MySQL Server (4.0, 5.0 or higher)

CREATE TABLE HOSPITAL
(PNo    Int(4)    Primary key,
Name    Varchar(20)    NOT NULL,
Age    Int(2),
Department    Varchar(18),   
Dateofadm    Date,   
Charges    Double(7,2),   
Sex    Char(1));

INSERT INTO HOSPITAL VALUES(1, 'Arprit', 62, 'Surgery', '2008-01-12', 300, 'M');
INSERT INTO HOSPITAL VALUES(2, 'Zarina', 22, 'ENT', '2007-12-12', 250, 'F');
INSERT INTO HOSPITAL VALUES(3, 'Kareem', 32, 'Orthopaedic', '2008-02-19', 200, 'M');
INSERT INTO HOSPITAL VALUES(4, 'Arun', 12, 'Surgery', '2008-01-11', 300, 'M');
INSERT INTO HOSPITAL VALUES(5, 'Zubin', 30, 'ENT', '2008-01-12', 250, 'M');
INSERT INTO HOSPITAL VALUES(6, 'Ketaki', 16, 'ENT', '2008-02-24', 250, 'F');
INSERT INTO HOSPITAL VALUES(7, 'Ankita', 29, 'Cardiology', '2008-02-20', 800, 'F');
INSERT INTO HOSPITAL VALUES(8, 'Zareen', 45, 'Gynaecology', '2008-02-22', 300, 'F');
INSERT INTO HOSPITAL VALUES(9, 'Kush', 19, 'Cardiology', '2008-01-13', 800, 'M');
INSERT INTO HOSPITAL VALUES(10, 'Shilpa', 23, 'Nuclear Medicine', '2008-01-20', 400, 'F');

CREATE TABLE Loan_Account
(AccNo     INT(3) PRIMARY KEY,
Cust_Name     VARCHAR(20) NOT NULL,
Loan_Amount DOUBLE(10,2),
Instalments INT(3),
Int_Rate FLOAT(5,2),
Start_Date DATE,
Interest DOUBLE(8,2));

INSERT INTO LOAN_ACCOUNT (AccNo, Cust_Name, Loan_Amount, Instalments, Int_Rate, Start_Date)
VALUES(1, 'R.K. Gupta', 300000, 36, 12.00, '2009-07-19');
INSERT INTO LOAN_ACCOUNT (AccNo, Cust_Name, Loan_Amount, Instalments, Int_Rate, Start_Date)
VALUES(2, 'S.P. Sharma', 500000, 48, 10.00, '2008-03-22');
INSERT INTO LOAN_ACCOUNT (AccNo, Cust_Name, Loan_Amount, Instalments, Int_Rate, Start_Date)
VALUES(3, 'K.P. Jain', 300000, 36, NULL, '2007-03-08');
INSERT INTO LOAN_ACCOUNT (AccNo, Cust_Name, Loan_Amount, Instalments, Int_Rate, Start_Date)
VALUES(4, 'M.P. Yadav', 800000, 60, 10.00, '2008-06-12');
INSERT INTO LOAN_ACCOUNT (AccNo, Cust_Name, Loan_Amount, Instalments, Int_Rate, Start_Date)
VALUES(5, 'S.P. Sinha', 200000, 36, 12.50, '2010-01-03');
INSERT INTO LOAN_ACCOUNT (AccNo, Cust_Name, Loan_Amount, Instalments, Int_Rate, Start_Date)
VALUES(6, 'P. Sharma', 700000, 60, 12.50, '2008-06-05');
INSERT INTO LOAN_ACCOUNT (AccNo, Cust_Name, Loan_Amount, Instalments, Int_Rate, Start_Date)
VALUES(7, 'K.S. Dhall', 500000, 48, NULL, '2008-03-05');

CREATE TABLE employee (
  EMPNO int(11) NOT NULL,
  ENAME varchar(20) DEFAULT NULL,
  JOB varchar(9) DEFAULT NULL,
  MGR int(4) DEFAULT NULL,
  HIREDATE date DEFAULT NULL,
  SAL float(7,2) DEFAULT NULL,
  COMM float(7,2) DEFAULT NULL,
  DEPTNO int(2) DEFAULT NULL,
  PRIMARY KEY (`EMPNO`),
  KEY DEPTNO (`DEPTNO`),
  CONSTRAINT employee_ibfk_1 FOREIGN KEY (DEPTNO) REFERENCES deptm (DEPTNO) ON DELETE CASCADE;

CREATE TABLE department (
  dept_no char(4) NOT NULL,
  deptname varchar(15) DEFAULT NULL,
  PRIMARY KEY (dept_no));

CREATE TABLE BANK_AC
    (Acc_no            INT(5) PRIMARY KEY,
    CName        VARCHAR(20) NOT NULL,
    Bname        VARCHAR(30) NOT NULL DEFAULT '' ,
    Amount        DOUBLE(10,2) CHECK (AMOUNT > 5000),
    Dateofopen         DATE,
    T_ID    ENUM('Saving', 'Current'),
    T_Transactions    DOUBLE(7,1));

CREATE TABLE STMARK
(NAME    VARCHAR(25) NOT NULL DEFAULT '' ,
ROLL    INT(3) PRIMARY KEY,
AGE        INT(3) ,
MARKS    DOUBLE(5,2),
Check (Age > 12 and Age < 16));

create table salesperson
(SNum INT(6) PRIMARY KEY,
SFName VARCHAR(25) NOT NULL,
SLName VARCHAR(25) NOT NULL DEFAULT '',
City  SET('Delhi', 'Mumbai', 'Kolkata', 'Chennai'),
Sales DOUBLE(9,2),
Comm DOUBLE(8,2));

CREATE TABLE Shops(Id INTEGER, Name VARCHAR(55),
Quality ENUM('High', 'Average', 'Low'));

INSERT INTO Shops VALUES(1, 'Boneys', 'High');
INSERT INTO Shops VALUES(2, 'AC River', 'Average');
INSERT INTO Shops VALUES(3, 'AT 34', 'Low');


CREATE TABLE EMPNEW
(Emp_ID INT(8) PRIMARY KEY,
First_Name VARCHAR(25) NOT NULL,
Last_Name VARCHAR(25) NOT NULL DEFAULT '',
Date_Join DATE,
Basic_Sal DOUBLE(8, 2) CHECK (BASIC_SAL > 5000),
Dept_ID SET('10', '20', '30', '40', '50', '60'));

CREATE TABLE DEPTM
(DEPTNO    IntEGER NOT NULL PRIMARY KEY,
DNAME     Varchar(14),
LOC        Varchar(13));

Create table customers(CustomerID Integer Primary key, Name VARCHAR(25) not null);
Insert into customers values(1, 'Ravi Nayak');
Insert into customers values(2, 'Sudhir Rana');
Insert into customers values(3, 'Amit Kulkarni');
Insert into customers values(4, 'Seema Rout');


CREATE TABLE Book(Book_Id INTEGER, Title TEXT NOT NULL,
Author VARCHAR(25) NOT NULL, Price Float);