Hindi Vidya Prachar Samiti s RAMNIRANJAN JHUNJHUNWALA COLLEGE Ghatkopar W Mumbai 400 086 Certificate This is to certify that Mr Ms Deepak Adall Roll No 3089 of SYB Sc I T class has completed the required number of experiments in the subject of DBMS in the Department of Information Technology during the academic year 2022 2023 Professor In Charge Co ordinator of IT Department Prof Bharati Bhole Prof ArchanaBhide College Seal Date Examiner INDEX Creating and Managing Tables Including Constraints Manipulating Data Using INSERT Update Delete statement SQL Statements Single Row Functions SUBSTR TO CHAR SYSDATE CONCAT LOWER UPPER LENGTH ROUND Date and Time Functions SQL Statements Displaying Data from Multiple Tables Joins and Subqueries SQL Statements Aggregate functions and Group By clause Triggers PL SQL Declaring Variables and writing executable statements interacting with the Oracle Server Writing Control Structures if else FOR Loop While PL SQL Writing Explicit Cursors 1 Composite data type Record 2 Cursor 1 2 3 4 5 6 7 8 9 10 Creating Procedures and Functions and exception handling 1 Exception Handling 2 Procedures 3 Functions 24 06 22 29 06 22 6 07 22 13 07 22 16 07 22 08 09 22 27 07 22 04 08 22 18 08 22 24 08 22 Creating other database objects Views amp Sequences 20 07 22 Practical No 1 Creating and Managing Tables Date 24 6 22 a Creating and managing Tables Syntax Create table table name Colum name1 datatype a Creating and Managing Tables create table ICS Client ID number primary key Client name varchar2 20 not null Client emailAd varchar2 30 not null Client Phone number 10 not null Client sallary number not null Client Dapartment varchar2 30 not null PROJECT ID drop table project1 update ics set CLIENT DAPARTMENT select from project1 select from ics insert into ics values 101 Vandana Vandana ics org 1234567890 225000 Rehablitation insert into ics values 102 Bhaviravi Bhairavi ics org 1234562220 25000 survivorship insert into ics values 103 datta datta ics org 1232267890 225000 HR insert into ics values 104 Lyntee lyntee ics org 1234522220 75000 survivorship Rehabilitation insert into ics values 105 Deepak intern ics org 1234533390 7500 Rehablitation insert into ics values 106 Dr Assma DrAssma ics org 12342220 30000 Onocologis b Including Constraints create table project1 Client ID number not null project id varchar2 20 not null project name varchar2 40 not null project handler varchar2 30 not null Actual date DATE not null starting date DATE not null Last date DATE not null project budget number 10 not null constraint Fk1 foreign key Client ID references ICS Client ID insert into Project values 102 P1 Ugam Bhairavi survivorship all Output Theory Syntax of create statement Constraints Unique Not Null Check Foreign key Table Level and column level clients create table clients clients id number 4 primary key clients name varchar2 40 NOT NULL address varchar2 30 email varchar2 30 unique phone number 10 business varchar2 20 not null describe clients Output Department CREATE TABLE DEPARTMENT DEPT NO NUMBER 4 PRIMARY KEY DEPT NAME VARCHAR 15 NOT NULL LOCATION VARCHAR 20 DESCRIBE DEPARTMENT Output Employee create table employee EMP NO NUMBER 4 PRIMARY KEY E NAME VARCHAR 20 NOT NULL JOB VARCHAR 15 SALARY NUMBER 7 CHECK SALARY 0 DEPT NO NUMBER 4 CONSTRAINT fk03 foreign key DEPT NO references DEPARTMENT DEPT NO describe employee Projects create table project project id number 3 primary key description varchar2 40 not null project startdate date planned enddate date actual enddate date budget number 10 check budget 0 clientid number 4 constraint fk 02 foreign key clientid references clients clients id describe project OUTPUT EmpProjectTasks create table EmpProjectTasks p id number 3 E no number 4 Start date date End date date Task varchar2 25 status varchar2 15 constraint pk 01 primary key p id E no constraint fk 03 foreign key p id references projects PROJECT ID constraint fk 04 foreign key E no references employee EMPNO describe EmpProjectTasks Output Date 29 6 22 2 Practical Manipulating Data CREATE TABLE CLIENTS CLIENT IDNUMBER 4 0 CNAME VARCHAR2 40 NOT NULL ENABLE ADDRESS VARCHAR2 30 EMAIL VARCHAR2 30 PHONE NUMBER 10 0 BUSINESS VARCHAR2 20 NOT NULL ENABLE PRIMARY KEY CLIENT ID ENABLE UNIQUE EMAIL ENABLE Output drop table project1 a Using DELETE statement delete from CLIENTS where client ID 1007 b Using UPDATE statement update clients set BUSINESS designer where CLIENT ID 1006 select from project1 select from ics c Using INSERT statement emp insert into empl EMPNO ENAME JOB SALARY DEPTNO values 7001 Akash Designer 25000 10 insert into emp EMPNO ENAME JOB SALARY DEPTNO values 7002 Abhilasha Designer 25000 10 insert into emp EMPNO ENAME JOB SALARY DEPTNO values 7003 Amit Developer 24000 20 insert into emp EMPNO ENAME JOB SALARY DEPTNO values 7004 Amit Tester 24000 30 insert into emp EMPNO ENAME JOB SALARY DEPTNO values 7005 Shruti Tester 22000 30 insert into emp EMPNO ENAME JOB SALARY DEPTNO values 7006 Prachi TechWriter 22000 40 Table dept insert into deptt DEPT NO DEPT NAME DEPT LOC values 10 Design Pune insert into dept DEPT NO DEPT NAME DEPT LOC values 20 Development Pune insert into dept DEPT NO DEPT NAME DEPT LOC values 30 Testing Mumbai insert into dept DEPT NO DEPT NAME DEPT LOC values 40 Document Mumbai Table clients insert into clients CLIENT ID CNAME ADDRESS EMAIL PHONE BUSINESS values 1001 Asian Paints Mumbai contact apaints com 9839232892 supplier insert into clients values 1002 Navneet Mumbai contact navneet com 9983923466 manufacturer insert into clients CLIENT ID ADDRESS CNAME EMAIL PHONE BUSINESS values 1003 Mumbai KrishnaConsult contact krishna com 945344592 consultant insert into clients Client ID Cname Address Email Phone Business values 1004 LTI Delhi abc bcd com 6765643523 Services EMP PROJECT TAST insert into EMP PROJECT TAST PROJECT ID EMPNO START DATE END DATE TASK STATUS values 203 7001 1 APR 21 10 APR 21 coverpg design completed insert into EMP PROJECT TAST PROJECT ID EMPNO START DATE END DATE TASK STATUS values 203 7004 11 APR 21 17 APR 21 verified design completed insert into EMP PROJECT TAST PROJECT ID EMPNO START DATE END DATE TASK STATUS values 203 7006 30 APR 21 15 May 21 Doc Writing in prog insert into EMP PROJECT TAST PROJECT ID EMPNO START DATE END DATE TASK STATUS values 201 7006 15 July 21 25 JULY 21 Doc Writing in prog D Using ALTER statement alter table clients rename to clients data 3 Practical SQL Statements 1 create table project1 Client ID