Categories
Uncategorized

DBMS Query

CIA 1 and 2

Tables to be used:

student (student_id, sname,deptno,dob,email)

colldept(deptno, dname,hod)

faculty(facultyid,fname, deptno,designation, salary)

employee(empid, name,job hiredate,salary,deptno,mgrid,age)

department(deptno,dname,loc)

1. sql>create table colldept( deptno varchar2(20),dname varchar2(20),hod varchar2(20),primary key(deptno));

sql>desc colldept;

OutPut

Name                       Null?                       Type

Deptno                    Null                         varchar2(20)

Dname                                                     varchar2(20)

HOD                                                         varchar2(20)

sql> insert into colldept(deptno,dname,hod)values (001,”’computer science’,’praful’);

sql>insert into colldept(deptno,dname,hod)values (002,’history’’,’shivaram’);

sql>insert into colldept(deptno,dname,hod)values (001,’engineering’,’pritam’);

sql> select * from colldept;

Output

Deptno                                dname                               HOD

1                                           computer science            Praful

2                                            History                              Shivaram

3                                            engineering                      Pritam

2. sql> create table student( student_id varchar2(20),sname varchar2(20),dob date not null, email varchar2(20), primary key (student_id),foreign key (deptno) references colldept(deptno));

sql>desc student;

Output

Name                                            Null?                             Type

Student_id                                   Not null                        varchar2(20)

sname                                                                                 varchar2(20)

deptno                                                                                varchar2(20)

dob                                                                                      varchar2(20)

email                                                                                   varchar2(20)

sql> insert into student(student_id, sname,deptno,dob,email) values(‘MCA11’ ‘sheikh’, 1,to_date(‘19900909’,’yyyymmdd’),’xyz@gmail.com’);

sql>insert into student(student_id, sname,deptno,dob,email) values(‘HIS11’ ‘junaid’,2,to_date(‘19910909’,’yyyymmdd’),’xyz@gmail.com’);

sql>insert into student(student_id, sname,deptno,dob,email) values(‘eng11’ ‘kailash’,3,to_date(‘19920909’,’yyyymmdd’),’xyz@gmail.com’);

sql>select * from student;

Output

Student_id                  sname                     deptno                             dob                          email

MCA11                        sheikh                      1                                  09-sep-90              xyz@gmail.com

HIS11                          Junaid                       2                                  09-sep-91              xyz@gmail.com

ENG11                        kailash                      3                                  09-sep-92              xyz@gmail.com

3. sql> create table faculty(faculty_id varchar2(20) primary key,fname varchar2(20),deptno varchar2(20),salary real,foreign key (deptno) references colldept(deptno));

sql>desc faculty;

Output

name                                    null?                                       type

faculty_id                             not null                                  varchar2(20)

fname                                                                                  varchar2(20)

salary                                                                                   varchar2(20)

designation                                                                         varchar2(20)

deptno                                                                                 varchar2(20)

sql>insert into faculty(faculty_id, fname,designation,salary,deptno) values(‘21’, ‘nisha’,’coordinator’,50000,1);

sql>insert into faculty(faculty_id, fname,designation,salary,deptno) values(‘22’ ,‘Samiksha’,’coordinator’,50000,2);

sql>insert into faculty(faculty_id, fname,designation,salary,deptno) values(‘23’ ,‘Akriti’,’coordinator’,50000,3);

sql>select * from faculty;

Output

faculty_id                     fname                  designation                   salary                         deptno

21                                 Nisha                    coordinator                   50000                         1

22                                 Samiksha             coordinator                   50000                         2

23                                 akriti                     coordinator                    50000                        3

4. sql> create table department(deptno varchar2(10),dname varchar2(20) not null,loc varchar2(20),primary key (deptno));

sql> desc department;

Output

Name                                              Null?                          Type
—————————————–      ——–                —————————-
DEPTNO                                    NOT NULL               VARCHAR2(10)
DNAME                                     NOT NULL                VARCHAR2(20)
LOC                                                                              VARCHAR2(20)

SQL> insert into department(deptno,dname,loc) values(001,’Research’,’Bangalore’);

SQL> insert into department(deptno,dname,loc) values(002,’Developer’,’Mumbai’);

SQL> insert into department(deptno,dname,loc) values(003,’Designer’,’Delhi’);

sql> select * from department;

Output

DEPTNO     DNAME                LOC
———- ——————– ——————–
1          Research              Bangalore
2          Developer            Mumbai
3          Designer               Delhi


5. SQL> create table employee( empid varchar2(10),name varchar2(20) Not NULL,job varchar2(20),hiredate date,salary varchar2(20),deptno varchar(20),mgrid varchar2(20),age varchar2(10), primary key (empid), foreign key (deptno) references department(deptno));

Sql>desc employee;

Output

Name                                          Null?                         Type
——————————–            ——–           —————————-
EMPID                                     NOT NULL           VARCHAR2(10)
NAME                                      NOT NULL           VARCHAR2(20)
JOB                                                                        VARCHAR2(20)
HIREDATE                                                            DATE
SALARY                                                                 VARCHAR2(20)
DEPTNO                                                               VARCHAR2(20)
MGRID                                                                 VARCHAR2(20)
AGE                                                                      VARCHAR2(10)

SQL>insert into employee (empid,name,job,hiredate,salary,deptno,mgrid,age) values (11,’nisha’,’programmer’,to_date(‘19900503′,’yyyymmdd’),15000,2,21,27);

SQL>insert into employee (empid,name,job,hiredate,salary,deptno,mgrid,age) values (12,’sangita’,’Manager’,to_date(‘19900803′,’yyyymmdd’),15000,1,22,25);

SQL>insert into employee (empid,name,job,hiredate,salary,deptno,mgrid,age) values (13,’Rajesh’,’frontend designer’,to_date(‘19900904′,’yyyymmdd’),18000,3,23,29);

Output

empid           Name          Job                             Hiredate                  Salary             deptno               mgrid               age

11                  nisha         programmer                 03-may-90              15000             2                           21                     27

12                  Sangita      Manager                      03-aug-90               15000             1                           22                     25     

13                  Rajesh      front-end designer        04-sep-90               18000              3                           23                     29

Queries

1. create a queries to display all the data from the table, separate each column by a comma, name the column the output.

SQL> select deptno || ’,’ || dname || ’,’ Hod  “The output” from colldept;

Output

The output

————————————-

1,computer science,praful

2,History,shivaram

3,engineering,pritam

2. create a query to display name and age of students whose age is more than 20 years.

SQL> select sname,(sysdate-dob)/365 as age from student where ((sysdate-dob)/365)>20;

Output

SNAME               AGE

——————–    ———-

Sheikh               25.5835166

junaid                24.5835166

Kailash              23.5807768

3. Display the employee numbers,name,salary and salary increased by 15%.

SQL>select empid,name,salary,(salary+(salary*0.15)) as increased_salary from employee;

Output

EMPID       NAME              SALARY         INCREASED_SALARY

———- ——————– ——————–     —————-

11               nisha                15000              17250

12               sangita             15000              17250

13               Rajesh              18000              20700

4. create a query that display the name and indicates the amount of their annual salaries with astris, sort the date in descending order of salary

sql>select fname,rpad((salary*12),8,’*’)as annual_salary from faculty order by salary desc;

Output

FNAME              ANNUAL_SALARY

——————– ——————————–

nisha                  600000**

Akriti                   600000**

samiksha            600000**

5. Inner joins

A. Inner join, Retrieve only the information about department to which at least one employee is assigned.

SQL>select employee.deptno,department.dname from department inner join employee on employee.deptno=department.deptno;

Output

DEPTNO           DNAME

——————– ——————–

2                       Developer

1                       Research

3                       Designer

B. Retrieve only the information about the employee who are assigned to a department.

SQL>select employee.deptno, name from employee,department where department.deptno=employee.deptno;

Output

DEPTNO               NAME
——————– ——————–
2                         nisha
1                         sangita
3                         Rajesh

C. Left outer join, Retrieve the information of all the employees along with their department name if they are assigned to any department.

SQL>select empid, name, department.deptno from employee left outer join department on employee.deptno=department.deptno;

Output

EMPID      NAME                  DEPTNO
———-   ——————–       ———-
11            nisha                      2
12            sangita                   1
13            Rajesh                    3

D. Retrieve the info of all the employees along with the detail of employees name belonging to each department if any is available.

SQL>select dname,department.deptno,name from employee right outer join department on employee.deptno=department.deptno;

Output


DNAME                DEPTNO       NAME
——————–     ———-        ——————–
Developer            2                  nisha
Research              1                 sangita
Designer              3                 Rajesh

E. Self join, Retrieve the id of the employee and the id’s of their respective manager from the employee table.

Please help me in this…..

4 replies on “DBMS Query”

Comments are closed.