際際滷

際際滷Share a Scribd company logo

Sql commands

Download as DOCX, PDF
S
shweta-sharma99

The document contains SQL commands to create tables for a police database including tables for cases, employees, FIRs, daily diary entries, designations, and more. It also includes sample INSERT statements to add data to these tables, and some example SELECT queries to retrieve data from the tables.

1 of 8
Downloaded 43 times
SQL COMMANDS CREATE TABLE COMMANDS 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,
address text(50) not null, 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 (
fir_no number references fir(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),
vehicle_no number not null, 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) );
INSERTION COMMANDS Designation table INSERT INTO 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');
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');
Data query language SELECT emp_name, address FROM employee; Output SELECT * FROM empound WHERE fir_no<1035; SELECT * FROM designation;
SELECT DISTINCT empound_id FROM empound_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

More Related Content

Sql commands

  • 1. SQL COMMANDS CREATE TABLE COMMANDS 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) not null, 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 references fir(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 not null, 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 INSERT INTO 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 SELECT emp_name, address FROM employee; Output SELECT * FROM empound WHERE fir_no<1035; SELECT * FROM designation;
  • 8. SELECT DISTINCT empound_id FROM empound_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