Sql commands

Sql commands

• 1.
  SQL COMMANDS CREATE TABLECOMMANDS Case Solved Table create table case_solved ( case_solved_id number primary key, case_id number not null unique references case(case_id), fir_no number not null unique, date_case_closed date not null ); Case Table create table case ( case_id number primary key, fir_no number not null references fir(fir_no), case_incharge_id number not null unique, date_case_open date not null ); Complainant table create table complainant ( fir_no number primary key references fir(fir_no), complaintant_name text(30) not null, fathers_name text(30) not null,
• 2.
  address text(50) notnull, sex text(5) not null, phone_no number not null ); Daily diary table create table daily_diary ( s_no number primary key, _date date not null, emp_id number not null references employee(emp_id), duty text(30) NOT NULL, duty_shift text(30) not null ); Designation Table create table designation ( emp_id number primary key references employee(emp_id), rank text(20) not null, salary number not null, date_joined date not null ); Duty Officer Table create table duty_officer (
• 3.
  fir_no number referencesfir(fir_no), duty_officer_id number references employee(emp_id), primary key(fir_no,duty_officer_id) ); Employee table create table employee ( emp_id number primary key, emp_name text(30) not null, sex text(5) not null, address text(100) not null, phone_no number not null ); Empound Officer Table create table empound_officer ( empound_id number references empound(empound_id), investigation_officer_id number references employee(emp_id), primary key(empound_id,investigation_officer_id) ); Empound Table create table empound ( empound_id number primary key, fir_no number not null references fir(fir_no),
• 4.
  vehicle_no number notnull, date_emopound date not null, date_release date not null, under_section text(30) not null ); FIR Investigation Officer Table create table fir_investigation_officer ( fir_no number references fir(fir_no), investigation_officer_id number references employee(emp_id), primary key(fir_no,investigation_officer_id) ); FIR Table create table fir ( fir_no number primary key, date_of_fir date not null, date_of_crime date not null, under_section text(20) not null, details text(50) );
• 5.
  INSERTION COMMANDS Designation table INSERTINTO designation ( emp_id, rank, salary, date_joined ) VALUES ('1231', 'constabler', '20000', '1-6-1995'); EMPLOYEE TABLE INSERT INTO employee ( emp_id, emp_name, address, sex, phone_no ) VALUES ('1231', 'rajesh kumar', 'model town ludhiana', 'male', '9592477245'); FIR TABLE INSERT INTO fir ( fir_no, date_of_fir, date_of_crime, under_section, details ) VALUES ('1023', '24-4-2011', '21-4-2011', '420', 'robbery'); EMPOUND_OFFICER INSERT INTO empound_officer ( empound_id, investigation_officer_id ) VALUES ('1342', '1231'); FIR_INVESTIGATION_OFFICER INSERT INTO fir_investigation_officer ( fir_no, investigation_officer_id ) VALUES ('1023', '1231'); DUTY_OFFICER INSERT INTO duty_officer ( fir_no, duty_officer_id ) VALUES ('1023', '1231');
• 6.
  DAILY_DIARY INSERT INTO daily_diary( s_no, _date, emp_id, duty, duty_shift ) VALUES ('23', '24-04-2011', '1231', '8', 'morning'); EMPOUND INSERT INTO empound ( empound_id, fir_no, vehicle_no, under_section, date_empound, date_release ) VALUES ('1342', '1023', '3465', '356', '24-4-2011', '21-04-2011'); COMPLAINTANT INSERT INTO complaintant ( fir_no, complaintant_name, fathers_name, address, sex, phone_no ) VALUES ('1023', 'Ravi kumar', 'parkash kumar', 'haibowal ludhiana', 'male', '9834562786'); CASE INSERT INTO case ( case_id, fir_no, case_incharge_id, date_case_open ) VALUES ('234', '1023', '1231', '24-4-2011'); CASE_SOLVED INSERT INTO case_solved ( case_solved_id, case_id, fir_no, date_case_closed ) VALUES ('251', '234', '1023', '27-5-2011');
• 7.
  Data query language SELECTemp_name, address FROM employee; Output SELECT * FROM empound WHERE fir_no<1035; SELECT * FROM designation;
• 8.
  SELECT DISTINCT empound_id FROMempound_officer; Output SELECT * FROM complaintant WHERE complaintant_name='kalpana' And fathers_name='niraj sharma'; SELECT * FROM complaintant WHERE complaintant_name='rajesh kumar' Or fathers_name='niraj sharma'; Output