Lab 5 : Exercise on SQL queries.

(a) Consider the relational table given below and answer the following SQL queries.   Think that the table has filled up by employee’s records.

 EMPLOYEE (ID_NO, NAME, DEPARTMENT_ID, SALARY)

·        List all the employees whose name starts with the letter ‘L’
·        Find the maximum salary given to employees in each department
·        Find the number of employees working in ‘accounts’ department
·        Find the second maximum salary from the table

·        Find the employee who is getting the minimum salary


(b) Consider a university database for the scheduling of classrooms for final exams. This database could be modeled as the single entity set exam, with attributes course-name, section-number, room-number, and time.Alternatively, one or more additional entity sets could be defined, along with relationship sets to replace some the attributes of the exam entity set, as
  •         course with attributes name, department and c-number
  •         section  with attributes s-number and enrollment, and department as a weak entity     set on course
  •         room with attributes r-number, capacity, and building
  1.         Show and E-R diagram illustrating the use of all three additional entity sets listed.
  2.         Explain what application characteristics would influence the decision to include or not to include each of the additional entity sets.

 

48 comments:

  1. 1) select * from employee where name like 'L%';
    2) select department_id,MAX(salary) from employee group by department_id;
    3) select count(id_no) from employee where department_id = 'accounts';
    4) select distinct salary from employee e where 2 = (select count(distinct salary)
    from employee where e.salary <= salary);
    // note that in (4) 3 means the value of n.
    5) select name,min(salary) from employee where salary = (select min(salary)
    from employee ) group by name;

    ReplyDelete
  2. a) select * from employee where name like 'L%';
    b) select department_id,MAX(salary) from employee group by department_id;
    c) select count(id_no) from employee where department_id = 'accounts;
    e) select name,min(salary) from employee where salary = (select min(salary)
    from employee ) group by name;

    ReplyDelete
  3. 1. select *from employees where names starts with 'L%';
    2. select department_id,MAX(salary) from employees group by department_id;
    3. select count(id_no) from employee where department_id='accounts';
    4. select MAX(salary) from employee where salary < (select max(salary) from employee);
    5. select name, MIN(salary) from employee where salary=(select MIN(salary) from employee) group by name;

    ReplyDelete
  4. 1.select*from employee where emp_name like'L%';
    2.select dept,max(salary) from employee group by dept;
    3.select dept,count(*) from employee
    where dept='CSE' group by dept;
    4.select id_no,emp_name from employee
    where salary=(select min(salary));
    5.select salary from (select salary,rank()over(order by salary DESC) ranking from employee) where ranking=3;

    ReplyDelete
  5. 1. select *from employees where names starts with 'L%';
    2. select dept_id,max(salary) from employees group by dept_id;
    3. select count(id_no) from employee where dept_id='accounts';
    4. select max(salary) from employee where salary < (select max(salary) from employee);
    5. select name, min(salary) from employee where salary=(select min(salary) from employee) group by name;

    ReplyDelete
  6. The below code can find nth maximum/minimum on run time.

    select salary from (select salary, rank() over (order by salary DESC) ranking from employee) where ranking=&n;

    Here, rank() is an aggregate function. rank() can work with WITHIN or OVER keyword.
    Your may try the below query with Lab-5 Exercise.

    SELECT RANK(7000) WITHIN GROUP
    (ORDER BY salary DESC) FROM employee;

    ReplyDelete
  7. 1.select * from e_employee where name like 'l%' ;
    2.select dept_id ,max(salary) from e_employee group by dept_id;
    3.select count(*) from e_employee where dept_id ='accounts' ;
    4.select max(salary) from e_employee where salary not in(select max(salary)from e_employee);
    5.select min(salary) from e_employee;

    ReplyDelete
  8. 1) select * from employee where name like 'L%';
    2) select department_id,MAX(salary) from employee group by department_id;
    3) select count(id_no) from employee where department_id = 'accounts';
    4) select distinct salary from employee e where 2 = (select count(distinct salary)
    from employee where e.salary <= salary);
    5) select name,min(salary) from employee where salary = (select min(salary)
    from employee ) group by name;

    ReplyDelete
  9. >> select * from e_employee where name like 'L%' ;
    >> select dept_id ,max(salary) from employee group by dept_id;
    >> select count(*) from employee where dept_id ='accounts' ;

    >> select min(salary) from employee;

    ReplyDelete
  10. create table employe
    (
    id_no number(5),
    name varchar2(100 byte),
    dept_id varchar2(100 byte),
    salary number(6,2),
    constraint emp_id_no primary key(id_no)
    );

    insert into employe values
    (
    1,
    'shohan',
    'cse',
    1000
    );

    insert into employe values
    (
    2,
    'arif',
    'bba',
    2000
    );

    insert into employe values
    (
    3,
    'Lisha',
    'cse',
    3000
    );

    insert into employe values
    (
    4,
    'salman',
    'accounts',
    400
    );

    insert into employe values
    (
    5,
    'shuvo',
    'accounts',
    500
    );

    insert into employe values
    (
    6,
    'Tonny',
    'accounts',
    400
    );

    select name from employe where name like 'L%';
    select dept_id,max(salary) from employe group by dept_id;
    select count(*) from employe where dept_id='accounts';
    select max(salary) from employe where salary<(select max(salary) from employe);
    select dept_id,min(salary) from employe where salary=(select min(salary) from employe) group by dept_id;

    ReplyDelete
  11. SQL> create table employee
    2 (
    3 emp_id number(5),
    4 emp_name varchar(30),
    5 dept_id varchar(5),
    6 salary number(6,2)
    7 );

    Table created.

    SQL> insert into employee
    2 values(&a,'&b','&c',&d);
    Enter value for a: 1
    Enter value for b: salman
    Enter value for c: cse
    Enter value for d: 5000
    old 2: values(&a,'&b','&c',&d)
    new 2: values(1,'salman','cse',5000)

    1 row created.

    SQL> /
    Enter value for a: 2
    Enter value for b: shohan
    Enter value for c: bba
    Enter value for d: 6000
    old 2: values(&a,'&b','&c',&d)
    new 2: values(2,'shohan','bba',6000)

    1 row created.

    SQL> /
    Enter value for a: 3
    Enter value for b: shuvo
    Enter value for c: soc
    Enter value for d: 7000
    old 2: values(&a,'&b','&c',&d)
    new 2: values(3,'shuvo','soc',7000)

    1 row created.

    SQL> select * from employee;

    EMP_ID EMP_NAME DEPT_ SALARY
    ---------- ------------------------------ ----- ----------
    1 salman cse 5000
    2 shohan bba 6000
    3 shuvo soc 7000
    SQL> select * from employee where emp_name like 's%';

    EMP_ID EMP_NAME DEPT_ SALARY
    ---------- ------------------------------ ----- ----------
    1 salman cse 5000
    2 shohan bba 6000
    3 shuvo soc 7000
    SQL> select dept_id,max(salary) from employee group by dept_id;

    DEPT_ MAX(SALARY)
    ----- -----------
    bba 6000
    cse 5000
    soc 7000
    SQL> select count(emp_id) from employee where dept_id='cse';

    COUNT(EMP_ID)
    -------------
    1
    SQL> select distinct salary from employee e where 2 = (select count(distinct salary)
    2 from employee where e.salary <= salary);

    SALARY
    ----------
    6000
    SQL> select emp_name,min(salary)from employee where salary=(select min(salary)
    2 from employee) group by emp_name;

    EMP_NAME MIN(SALARY)
    ------------------------------ -----------
    salman 5000

    ReplyDelete
  12. select * from employee where name like'L%';
    select dept_id,dept_name,max(salery) from employee group by dept_id,dept_name;
    select count(id_no)from employee where dept_name='account';
    select salery from employee monir where 2= (select count(salery)from employee where monir.salery <=salery);
    select name,min(salery) from employee where salery=(select min(salery)from employee) group by name;

    ReplyDelete
  13. 1.SELECT * FROM employee WHERE name LIKE 'L%';
    or
    SELECT name FROM employee WHERE name LIKE 'L%' //show only name column
    2.SELECT department_id, MAX(SALARY) FROM employee GROUP BY department_id;
    3.SELECT COUNT(*)"Count_Account" FROM employee
    WHERE department_id='accounts';
    or
    SELECT COUNT(id_no)"Count_Account" FROM employee WHERE department_id = 'accounts';

    4.SELECT MAX(salary)FROM employee
    WHERE salary NOT IN(SELECT MAX(salary)FROM employee);
    or

    SELECT MAX(salary)FROM employee
    WHERE salary <>(SELECT MAX(salary)FROM employee);


    5.SELECT name,department_id, MIN(SALARY) FROM employee
    WHERE SALARY= (SELECT MIN(SALARY) FROM employee ) GROUP BY name,department_id;

    ReplyDelete
  14. select name from employee where name like 'l%';
    select dept_id,max(salary) from employee group by dept_id;
    select count(*) from employee where dept_id='accounts';
    select max(salary)from employee where salary not in(select max(salary)from employee);
    select name,min(salary) from employee where salary=(select min(salary) from employee) group by name;


    Student ID#201220985

    ReplyDelete
  15. Badiuzzaman

    1. select from employee where name like 'L%';
    2. select department_id,MAX(salary) from employee group by department_id;
    3. select count(id_no) from employee where department_id = 'accounts';
    4.select max(salary) from employee where salary not in(select max(salary)from e_employee);
    5.select min(salary) from employee;

    ReplyDelete
  16. a)select * from employe;
    b)select name from employe where name like'L%';
    c)select (dept_id) max (salary) from employ group by(dept_id);
    d)select count(id) from employ where dept_id='account';
    e)select max(salary) from employ where salary<(select max(salary) from employ);
    f)select name, min(salary) from employ where salary=(select min(salary) from employ )group by name;

    student id# 201430964

    ReplyDelete
  17. ID=201430308

    1.select * from personal where name like's%';
    2. select deptid,MAX(salary) from personal group by deptid;
    3. select count(name) from personal where dept_name='account';
    4. select MAX(salary) from personal where salary not in (select MAX(salary) from personal);
    5. select name,MIN(salary) from personal where salary=( select MIN(salary) from personal) group by name;

    ReplyDelete
  18. ID=201430308

    1.select * from personal where name like's%';
    2. select deptid,MAX(salary) from personal group by deptid;
    3. select count(name) from personal where dept_name='account';
    4. select MAX(salary) from personal where salary not in (select MAX(salary) from personal);
    5. select name,MIN(salary) from personal where salary=( select MIN(salary) from personal) group by name;

    ReplyDelete
  19. 1) select * from employee where name like'M%';
    2) select DEPARTMENT_ID,MAX(salary) from employee group by DEPARTMENT_ID;
    3) select count(name) from employee where DEPARTMENT_ID='cse';
    4) select MAX(salary) from employee where salary not in (select MAX(salary) from employee);
    5) select name,MIN(salary) from employee where salary=( select MIN(salary) from employee) group by name;

    ReplyDelete
  20. https://www.dropbox.com/home?preview=lab+5.txt

    ReplyDelete
  21. https://www.dropbox.com/s/d11hyqr73srrzxi/lab%205.txt?dl=0

    ReplyDelete
  22. ID=201430426
    select * from employee where name like'L%';
    select dept_id,dept_name,max(salery) from employee group by dept_id,dept_name;
    select count(id_no)from employee where dept_name='account';
    select salery from employee monir where 2= (select count(salery)from employee where monir.salery <=salery);
    select name,min(salery) from employee where salery=(select min(salery)from employee) group by name;

    ReplyDelete
  23. select * from employee;
    where salery=(select max(salery) from employee);
    select * from employee where name like'L%';
    select dept_id,dept_name,max(salery) from employee group by dept_id,dept_name;
    select count(id_no)from employee where dept_name='account';
    select salery from employee monir where 2= (select count(salery)from employee where monir.salery <=salery);
    select name,min(salery) from employee where salery=(select min(salery)from employee) group by name;

    ReplyDelete
  24. Answer for(A):

    create table employee
    (
    id_no number(5),
    name varchar(20) not null,
    department_id varchar(20) not null,
    salary number(7,2) not null,

    constraint i_varpk primary key(id_no)
    );

    desc employee;

    insert into employee
    values(101,'Liakot Ali','Manager',15000);


    insert into employee
    values(102,'Rafiq Ahmed','Manager',14500);


    insert into employee
    values(103,'Lotifa Rahman','Teacher',20000);


    insert into employee
    values(104,'Chondon Kumar','Teacher',18600);



    insert into employee
    values(105,'Raihan Mia','account',17700);


    insert into employee
    values(106,'Hamidul Haq','account',11800);



    insert into employee
    values(107,'Saiful Islam','account',10500);

    desc employee;
    select * from employee;

    select * from employee
    where name like'L%';

    select department_id,max(salary) from employee group by department_id;

    select count(*) as "Number of worker at Account" from employee
    where department_id='account';

    select name,min(salary) from employee where salary = (select min(salary)
    from employee ) group by name;

    ReplyDelete
  25. Id:201420262

    // Homework (a)

    create table employee
    (
    id_no number(20),
    name varchar(35),
    depertment_id varchar(50),
    salary number(6,2)

    );
    desc employee;

    alter table employee
    add constraint varpk primary key(id_no);


    insert into employee
    values(1,'Karim','account',1000);

    select * from employee;

    insert into employee
    values(2,'Rahim','manager',1000);

    insert into employee
    values(3,'Latif','account',9000);

    insert into employee
    values(4,'Labib','manager',8000);


    insert into employee
    values(5,'Lifat','Gard',7000);


    select * from employee;

    select name from employee where name like 'L%';


    select depertment_id,MAX(salary) from employee group by depertment_id;
    select count(id_no) from employee where depertment_id = 'account';
    select MAX(salary) from employee where salary < (select max(salary) from employee);
    select name,min(salary) from employee where salary = (select min(salary)
    from employee ) group by name;

    ReplyDelete
  26. //201430934
    create table employe
    (
    Id_no number(12)primary key,
    Name varchar(20)not null,
    Department_id varchar(10)not null,
    salary number(10,2)not null
    );
    desc employe;

    insert into employe
    values(2014,'Amena Akter Anni','cse',5000000);

    insert into employe
    values(2015,'Md Abul Hossain','cs',5000000);

    insert into employe
    values(2016,'Mrs Beauty Begum','cse',5000000);

    insert into employe
    values(2017,'lisa Akter','accounts',500000);

    insert into employe
    values(2018,'lima Akter','bba',6000000);

    select * from employe;

    SELECT *FROM employe
    WHERE name LIKE 'l%';

    select department_id,MAX(salary) from employe group by department_id;

    select count(Id_no) from employe where department_id='accounts';

    select MAX(salary) from employe where salary < (select max(salary) from employe);
    select name,min(salary) from employe where salary = (select min(salary)
    from employe ) group by name;

    ReplyDelete
  27. id:201430310
    Q.no:A
    create table employee
    (
    id_no number(5),
    name varchar(20) not null,
    dept_id varchar(20) not null,
    salary number(7,2) not null,

    constraint i_varpk primary key(id_no)
    );

    desc employee;

    insert into employee
    values(101,'Lima Chowdhury','Manager',40000);


    insert into employee
    values(102,'Shamima Begum','A.Manager',30500);


    insert into employee
    values(103,'Afia Anjum','Teacher',25000);


    insert into employee
    values(104,'Toma Roy','Teacher',22000);



    insert into employee
    values(105,'Minnet Ali','account',17000);


    insert into employee
    values(106,'Hamid Rahman','account',18000);



    insert into employee
    values(107,'Saiful Islam','account',25000);

    desc employee;
    select * from employee;

    select * from employee
    where name like'L%';

    select dept_id,max(salary) from employee group by dept_id;

    select count(*) as "Number of worker at Account" from employee
    where dept_id='account';

    select name,min(salary) from employee where salary = (select min(salary)
    from employee ) group by name;

    ReplyDelete
  28. Answer for:b(2)

    https://www.dropbox.com/sh/fw07qx90kr1xkqy/AADtXXSf0DbY4T_MoZqtMztSa?dl=0

    ReplyDelete
  29. Answer:Question(b)
    https://www.dropbox.com/s/aeln8dz5xv2zl11/Ques%20b.docx?dl=0

    ReplyDelete
  30. lab5(a) https://drive.google.com/file/d/0B28XeWic3TdIc3BXS1VZSE9hc0U/view?usp=sharing

    ReplyDelete
  31. select * from employees where name like 'L%';
    select department_id,max(salary) from employees group by department_id;
    select count(id_no) from employees where department_id ='Account';
    select max(salary) from employees where salary <(select max(salary) from employees);
    select name,min(salary) from employees where salary=(select min(salary) from employees) group by name;

    ReplyDelete
  32. a)select * from employees where name like 'L%';
    b)select department_id,max(salary) from employees group by department_id;
    c)select count(id_no) from employees where department_id ='Account';
    d)select max(salary) from employees where salary <(select max(salary) from employees);
    e)select name,min(salary) from employees where salary=(select min(salary) from employees) group by name;

    ReplyDelete
  33. https://www.dropbox.com/s/godwl7zyfm97qxr/lab%205.txt?dl=0

    ReplyDelete
  34. https://www.dropbox.com/s/r11g9wjczkss11d/rakibul%20islam.txt?dl=0

    ReplyDelete
  35. CREATE TABLE employ(
    id_no NUMBER,
    name VARCHAR2(150) NOT NULL,
    dept_id VARCHAR2(20),
    salary NUMBER NOT NULL
    );

    INSERT INTO employ VALUES(1, 'L Emp1', 'DEPT1', 1200);
    INSERT INTO employ VALUES(2, 'Emp2', 'DEPT2', 1250);
    INSERT INTO employ VALUES(3, 'Emp3', 'DEPT1', 1900);
    INSERT INTO employ VALUES(4, 'Emp4', 'DEPT3', 112300);
    INSERT INTO employ VALUES(5, 'Emp5', 'DEPT1', 12000);

    SELECT * FROM employ;

    SELECT * FROM employ WHERE name LIKE'L%';
    SELECT dept_id, max(salary) FROM employ GROUP BY dept_id;

    SELECT COUNT(*) FROM employ;

    SELECT MAX(salary) FROM employ WHERE salary != (SELECT MAX(salary) FROM employ);
    SELECT MIN(salary) FROM employ;

    ReplyDelete
  36. Create Table employ(
    id_no number,
    name varchar2(30),
    department_id varchar2(20),
    salary number not null
    );

    Insert into employ values(1,'L Employee name1','department1',12000);
    Insert into employ values(2,' Employee name2','department2',1300);
    Insert into employ values(3,' Employee name3','department3',1600);
    Insert into employ values(4,' Employee name4','department4',1350);
    Insert into employ values(5,' Employee name5','department5',120);

    Select * from employ;

    Select * from employ name LIKE'L%';
    Select department_id, max(salary)from employee GROUP BY department_id;
    Select COUNT(*) from employee;
    Select MAX(Salary) from employ where Salary !=(Select MAX(Salary)from employ);
    Select MIN(Salary)from employ;

    ReplyDelete
  37. Create Table employ(
    id_no number,
    name varchar2(30),
    department_id varchar2(20),
    salary number not null
    );

    Insert into employ values(1,'L Employee name1','department1',12000);
    Insert into employ values(2,' Employee name2','department2',1300);
    Insert into employ values(3,' Employee name3','department3',1600);
    Insert into employ values(4,' Employee name4','department4',1350);
    Insert into employ values(5,' Employee name5','department5',120);

    Select * from employ;

    Select * from employ name LIKE'L%';
    Select department_id, max(salary)from employee GROUP BY department_id;
    Select COUNT(*) from employee;
    Select MAX(Salary) from employ where Salary !=(Select MAX(Salary)from employ);
    Select MIN(Salary)from employ;

    ReplyDelete
  38. create table employe
    (
    id_no number(5),
    name varchar2(100 byte),
    dept_id varchar2(100 byte),
    salary number(6,2),
    constraint emp_id_no primary key(id_no)
    );

    insert into employe values
    (
    1,
    'sabbir',
    'cse',
    1000
    );

    insert into employe values
    (
    2,
    'sajib',
    'bba',
    2000
    );

    insert into employe values
    (
    3,
    'supti',
    'cse',
    3000
    );

    insert into employe values
    (
    4,
    'rafiqul',
    'accounts',
    400
    );

    insert into employe values
    (
    5,
    'sohan',
    'accounts',
    500
    );

    insert into employe values
    (
    6,
    'rakib',
    'accounts',
    400
    );

    select name from employe where name like 's%';
    select dept_id,max(salary) from employe;
    select count(*) from employe where dept_id='accounts';
    select max(salary) from employe where salary<(select max(salary) from employe);
    select dept_id,min(salary) from employe;

    ReplyDelete
  39. https://www.dropbox.com/s/5stiyx126qr0in4/database%20file.txt?dl=0

    ReplyDelete
  40. https://www.dropbox.com/s/ki1p55quu5yp275/database%20file.txt?dl=0

    ReplyDelete

Popular Posts