Scribd Logo
Upload

Welcome to Scribd!

  • 61 views

    Sahil SQL Queries

    Uploaded by

    Daljit
    This document contains the details of database management system labs practicals conducted by a student. It includes several SQL queries performed on tables like EMPLOYEE, DEPARTMENT to perform operations like creating tables, inserting data, using functions and joins. It also provides examples of procedural SQL using concepts like loops, conditional statements on a table named AREA to calculate the area of a circle for different radius values.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Sahil SQL Queries

    Uploaded by

    Daljit
    61 views18 pages
    This document contains the details of database management system labs practicals conducted by a student. It includes several SQL queries performed on tables like EMPLOYEE, DEPARTMENT to perform operations like creating tables, inserting data, using functions and joins. It also provides examples of procedural SQL using concepts like loops, conditional statements on a table named AREA to calculate the area of a circle for different radius values.

    Original Description:

    sql queries in oracle sql

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains the details of database management system labs practicals conducted by a student. It includes several SQL queries performed on tables like EMPLOYEE, DEPARTMENT to perform operations like creating tables, inserting data, using functions and joins. It also provides examples of procedural SQL using concepts like loops, conditional statements on a table named AREA to calculate the area of a circle for different radius values.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    61 views18 pages

    Sahil SQL Queries

    Uploaded by

    Daljit
    This document contains the details of database management system labs practicals conducted by a student. It includes several SQL queries performed on tables like EMPLOYEE, DEPARTMENT to perform operations like creating tables, inserting data, using functions and joins. It also provides examples of procedural SQL using concepts like loops, conditional statements on a table named AREA to calculate the area of a circle for different radius values.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 18

    DATABASE MANAGEMENT SYSTEM

    LABS PRACTICALS RECORDS

    INFORMATION TECHNOLOGY

    Dr.B.R AMBEDKAR NATIONAL INSTITUTE OF


    TECHNOLOGY
    JALANDHAR-144011,PUNJAB(INDIA)
    Submitted To:
    Submitted by:
    HARSH VERMA
    Batta
    14124012

    Sahil

    IT,4th
    SEM

    LAB WORK 1

    Sql queries using

    Query1:For creating a table


    CREATE TABLE "EMPLOYEE"
    (
    "EMP_ID" NUMBER(4,0),
    "EMP_NAME" VARCHAR2(20),
    "SALARY" NUMBER(8,3),
    "MANAGER_ID" NUMBER(4,0),
    "D_NO" NUMBER(4,0),
    PRIMARY KEY ("EMP_ID") ,
    FOREIGN KEY ("MANAGER_ID")
    REFERENCES "EMPLOYEE" ("EMP_ID") ,
    FOREIGN KEY ("D_NO")
    REFERENCES "DEPARTMENT" ("DEPT_NO")
    )
    /
    Output table created sucessfully.

    Query2:Describing the structure of table created


    describe employee
    output:

    Query3:
    Describe department//department table looks like this
    Output:

    Note we can insert in the tables created using insert command


    One such illustration has been made for a tuple of the relation employee
    Like this:
    Insert into employee values(1,daljit singh,23098.65,NULL,2)
    Select * from employee

    This how we add data to our tables(relations);


    In this case employee table looks like:

    And the department table looks like:

    Query 1:Select * from employee where emp_name like %ingh


    Result:

    Query 2:select * from employee where manager_id IS NULL

    Query 3: select * from employee where salary between 12221 and 60000

    Query :select salary from employee where d_no=(


    Select d_no from employee where emp_id=5)

    Problem: To display employee id,name and department name of all


    employee
    Query: select emp_id,emp_name,dept_no from employee cross join department where
    employee.d_no=department.dept_no

    Problem: To display details of the manager of the employee


    with employee id=3.
    Query: select * from employee where emp_id =(select manager_id from
    where emp_id=3)

    employee

    Problem:to display the average salary of all employees


    Query:select avg(salary) from employee

    Print the details of the department whose employee has the


    maximum salary
    Query:select * from department where dept_no=(select d_no from employee where
    employee.salary=(select max(salary) from employee))

    LAB WORK 2
    Alter statements(for a table stud)_-these statements are used to alter the
    structure of table.
    Query-alter table stud add marks number(4)
    Query- alter table stud add primary key(rollno)

    ->For creating the creating a table department:


    Query:Create table dept(dno number(5) primary key,dname varchar(20),location
    varchar2(20));

    ->to Inserting the values in the table created:


    Query:insert into dept(dno,dname,location)values(:a,:b,:c);

    ->For creating the table employee as a referencing relation to table


    Department:
    Query:create table employee(emp_id number(5) primary key,e_name
    varchar(20),salary number(6),manager_id number(5) references
    employee(emp_id),dno number(6) references dept(dno))

    ->Inserting the values in the employee table:


    Query:insert into
    employee(emp_id,e_name,salary,manager_id,dno)values(:a,:b,:c,:d,:e)

    ->For finding the length of each employee name:


    Query:select length(e_name) from employee

    ->For concatenating two attributes in a single one:


    Query: select dname||location from dept

    ->Using basic mathematical functions on the default table dual:


    ->Floor functions gives the lower bound of the decimal value:
    Query: select floor(5.5) from dual

    ->mod function gives the remainder of 2 numbers:


    Query: select mod(10,3) from dual

    ->power gives one number raised with power to another number:


    Query: Select power(2,3) from dual

    ->ceil provides the upper bound of the given number:


    Query: select ceil(2.5) from dual

    ->initcap capatalizes the initials of the given entity:


    Query: select Initcap(e_name) from employee where e_name like '%ji'

    Query: select log(10,10) from dual

    ->greatest gives the largest of the given list:


    Query: select greatest(10.3,9.8,5.7) from dual

    ->least gives the smallest of the given list:


    Query: select least(1.4,6.4,8.9,1) from dual

    ->Using Aggregate functions


    Query: select max(salary) from employee

    ->count function provides the no. of entites but by use of distinct


    eliminates the duplicate values:
    Query: select count(distinct emp_id) from employee

    Problem:Give the name of the employee whose salary is maximum:


    Query: select e_name,salary from employee where salary=(select max(salary) from
    employee)

    Problem-to give details of all managers from employee table


    Query-create or replace view A(e) as select distinct manager_id from employee;
    Query- select * from employee ,A where employee.emp_id=A.e

    Problem-select details of employees where salary is minimum


    Query-Select * from employee where salary=(Select min(salary) from employee)

    ->group by keyword provides the ability to group the rows on some


    conditions while applying aggregate functions:
    Problem-Find out the employees in each department
    Query-select dno,count(*) from employee group by dno

    ->having keyword is used for making a condition when group by keyword


    is used or it is used for applying conditions on the result of the aggregate
    functions:
    Problem-Display the deptno having employees greater than 1.
    Query-Select dno,count(*) from employee group by dno having count(*)>1

    Problem-Display the Display the deptno having employees greater than 1


    except dept 1

    Query- select dno,count(*) from employee group by dno having count(*)>1 and dno
    not in(1)

    Problem-Display the details of the employee in order of count


    Query-Select dno,count(*) from employee group by dno order by count(*)

    Problem-Display the details of the employee in order of count


    Query-Select dno,count(*) from employee group by dno order by count(*) desc

    LAB WORK 3
    ->Using union ,intersection and minus keywords
    Problem-Find out details of the employee who works for dept1 or having
    salary more than 5000 using union
    Query- select * from employee where dno in(1) union select * from employee where
    salary>5000

    Problem: Find out details of the employee who works for dept1 or having
    salary more than 5000 using intersection
    Query: select * from employee where dno=1 intersect select * from employee
    where salary>5000;

    Problem-Find out the details of the employees where deptnumber is 1 2 or


    3 using union and minus
    Query- select * from employee minus select * from employee where dno not in(1)
    union select * from employee minus select * from employee where dno not in(2)
    union select * from employee minus select * from employee where dno not in(3)

    ->Applying various joins in tables


    Problem:Apply left outer join on employee to dept
    Query:select * from employee left outer join dept on employee.dno=dept.dno

    Problem:Apply right outer join on employee to dept


    Query: select * from employee right outer join dept on employee.dno=dept.dno

    Problem:Apply inner join on employee to dept


    Query: select * from employee inner join dept on employee.dno=dept.dno

    Problem: Apply natural join on employee to dept


    Query:select * from emp natural join dept

    LAB WORK 4
    PROCEDURAL SQL CONCEPTS

    ->Procedural sql contains declaration block declaration block ,executable


    block which contains the commands to be executed and a block for
    exception handling.
    ->A simple example calculating the area of the circle
    declare
    p constant number(4,2):=3.14;
    radius number(4,2);
    ar number(10,2);
    begin
    radius:=:a;
    ar:=p*power(radius,2);
    insert into area values(radius,ar);
    dbms_output.put_line('area is'||ar);
    end;

    output:For input as 3

    ->Here the table area is initially created that is:


    Create table area(radius number(4,2),ar number(10,2))
    After excecuting the above code the table data is:

    Using if section
    Format:
    If
    Then
    Else if

    Then
    .
    Endif;
    An example: To find the greatest to three numbers
    declare
    a number(2);
    b number(2);
    c number(2);
    begin
    a:=:ad;
    b:=:bd;
    c:=:cd;
    if a>b
    then
    if a>c
    then
    dbms_output.put_line('a is largest');
    else
    dbms_output.put_line('c is largest');
    end if;
    else
    if c>b
    then
    dbms_output.put_line('c is largest');
    else
    dbms_output.put_line('b is largest');
    end if;
    end if;
    end;

    Output:

    (Provided a being given largest value)

    Using while loop


    Problem: print the area of circle continuously with taking radius 1 to 10
    declare
    p constant number(4,2):=3.14;
    radius number(4,2);
    ar number(10,2);
    begin
    radius:=1;
    while radius<=10 loop
    ar:=p*power(radius,2);
    insert into area values(radius,ar);
    dbms_output.put_line('area is'||ar);
    radius:=radius+1;
    end loop;end;

    Output:

    Contents of area table becomes:

    Using for loop


    Problem: print the area of circle continuously with taking radius 1 to 10
    declare
    p constant number(4,2):=3.14;
    radius number(4,2);
    ar number(10,2);
    begin
    radius:=1;
    for radius in 1..10 loop
    ar:=p*power(radius,2);
    dbms_output.put_line('area is'||ar);
    end loop;
    end;

    Output:

    You might also like