Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 33 views

    Assignment 2

    Uploaded by

    Maak Ali Ansari
    This document contains an assignment on database systems for a BS IT/SE part 3 course. It includes 8 questions asking the student to write SQL queries to: 1) Create tables for teachers, courses, departments, and assigned courses 2) Perform inner joins between teachers, courses, and assigned courses tables 3) Perform left outer joins between teachers and departments 4) Perform left outer joins and full outer joins between teachers and departments 5) Filter to only show courses taught by a specific teacher 6) Filter teachers by their job grade The questions cover skills like creating tables, inserting data, performing various types of joins, and filtering results.

    Copyright:

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

    Assignment 2

    Uploaded by

    Maak Ali Ansari
    33 views14 pages
    This document contains an assignment on database systems for a BS IT/SE part 3 course. It includes 8 questions asking the student to write SQL queries to: 1) Create tables for teachers, courses, departments, and assigned courses 2) Perform inner joins between teachers, courses, and assigned courses tables 3) Perform left outer joins between teachers and departments 4) Perform left outer joins and full outer joins between teachers and departments 5) Filter to only show courses taught by a specific teacher 6) Filter teachers by their job grade The questions cover skills like creating tables, inserting data, performing various types of joins, and filtering results.

    Original Description:

    Original Title

    assignment2

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document contains an assignment on database systems for a BS IT/SE part 3 course. It includes 8 questions asking the student to write SQL queries to: 1) Create tables for teachers, courses, departments, and assigned courses 2) Perform inner joins between teachers, courses, and assigned courses tables 3) Perform left outer joins between teachers and departments 4) Perform left outer joins and full outer joins between teachers and departments 5) Filter to only show courses taught by a specific teacher 6) Filter teachers by their job grade The questions cover skills like creating tables, inserting data, performing various types of joins, and filtering results.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    33 views14 pages

    Assignment 2

    Uploaded by

    Maak Ali Ansari
    This document contains an assignment on database systems for a BS IT/SE part 3 course. It includes 8 questions asking the student to write SQL queries to: 1) Create tables for teachers, courses, departments, and assigned courses 2) Perform inner joins between teachers, courses, and assigned courses tables 3) Perform left outer joins between teachers and departments 4) Perform left outer joins and full outer joins between teachers and departments 5) Filter to only show courses taught by a specific teacher 6) Filter teachers by their job grade The questions cover skills like creating tables, inserting data, performing various types of joins, and filtering results.

    Copyright:

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

    BS – (IT/ SE) Part -III

    Course: Database Systems Assignment No 2


    Teacher: Kamran Dahri Submission Deadline: 28 June 2020

    Topics covered: Creating Tables, Inserting Multiple Rows,Equi Join, Non Equi Join, Outer Join

    Name: Maak Ali


    Father name: Ali Nawaz Ansari
    Class: BS (IT) Part-III
    Roll no: 2K18/IT/56
    Subject: Database
    Submitted by: Maak
    Submitted to: Sir Kamran Dehri
    Q1. Write queries to create above tables. (courses, teachers, assigned_course,
    job_grades, dept)

    a. Write below Teachers Table Query [with all Primary Key and Foreign Key constraints if needed]
    CREATE TABLE teachers

    Teacher_id number (30) ,

    Teacher_name varchar (30),

    Designation varchar(30),

    Dept_id number(10),

    Salary number(20)

    );
    b.Write below Courses Table Query[with all Primary Key and Foreign Key constraints if needed]
    CREATE TABLE courses

    Course_id varchar(10) PRIMARY KEY,

    Course_name varchar(30),

    Credit_hours number(10)

    );
    c. Write below Dept Table Query[with all Primary Key and Foreign Key constraints if needed]
    CREATE TABLE depttable

    Dept_id number(20) PRIMARY KEY,

    Dept_name varchar(30),

    Phone number(20),

    Location varchar(30)

    );
    d. Write below Assigned_Courses Table Query[with all Primary Key and Foreign Key
    constraints if needed]

    create table assignedcourses(ac_id number(20)primary key,teacher_id number(20),course_id


    number(20),class_time varchar(20),room_no varchar(30));

    insert into assignedcourses(ac_id,teacher_id,course_id,class_time,room_no)

    values('1','3','102','10:10am','class 03');

    values('2','3','109','11:10am','class 04');

    values('3','1','104','12:10pm','class 01');

    values('4','1','103','01:00pm','class 02');

    values('5','4','101','09:00am','class 08');
    values('6','4','106','11:00am','class 08');

    values('7','7','108','02:00pm','class 09');

    values('8','5','106','01:00pm','class 01');

    values('9','','105','02:00pm','class 02');

    e.Write below Job_grades Table Query[with all Primary Key and Foreign Key constraints if
    needed]

    create table job_grades(grade_id number(20) primary key,grade_name


    varchar(20),lowest_salary number(20),highest_salary number(20));
    insert into job_grades(grade_id,grade_name,lowest_salary,highest_salary)
    f.Write query to insert multiple records (rows) of teachers.

    INSERT INTO teachers (teacher_id, teacher_name, designation, dept_id,


    salary)
    SELECT 1, 'Dr. Zeeshan’, 'Professor', 2, 5000 FROM dual union all
    SELECT 2, 'Dr. Khalil', 'Professor', 2, 5000 FROM dual union all
    SELECT 3, 'Kamran', 'Associate Professor', 3, 10000 FROM dual union all
    SELECT 4, 'Muhammad Hussain', 'Associate Professor', 1, 5000 FROM dual
    union all
    SELECT 5, 'Nazish bashir', 'Associate Professor', 2, 4000 FROM dual union
    all
    SELECT 6, 'Sobia Mumtaz', 'Lecturer', 3, 24000 FROM dual union all
    SELECT 7, 'Z.A Bhutto', 'Lecturer', 4, 18000 FROM dual
    SELECT 8, 'Dr. Bisharat', 'Lecturer', 10000 FROM dual union all
    SELECT 9, 'Dr. Sheeraz', 'Associate Professor', 27000 FROM dual union all
    SELECT 10, 'Dr. Ahmed khan', 'Lecturer', 2000

    g.Write query to insert multiple records (rows) of courses.

    INSERT INTO courses (course_id, course_name, credit_hours)

    SELECT 105, 'Organizational Behavior', 3 from dual union all


    SELECT 106, 'Introduction to IT', 2 from dual union all
    SELECT 107, 'Compiler Construction', 3 from dual union all
    SELECT 108, 'Computer Graphics', 2 from dual union all
    SELECT 101, 'Programming Fundamentals', 3 from dual union all
    SELECT 102, 'Database Systems', 2 from dual union all
    SELECT 103, 'Java Programming', 3 from dual union all
    SELECT 104, 'Multimedia Systems', 2 from dual union all
    SELECT 109, 'Requirement Engineering', 3 from dual union all
    SELECT 110, 'Calculus', 3 from dual

    Q2. Write query on above given tables (teachers, courses, assigned_courses) to show following output

    [Hint: Use 2 Equi Joins or Inner Joins][Teachers and Courses with Assigned_Courses table]

    select a.ac_id,t.teacher_name,c.course_name
    from assignedcourses a,teachers t,courses c
    where a.teacher_id=t.teacher_id and a.course_id=c.course_id order
    by(ac_id);
    Q3. Write query on above given tables (teacher and dept) to show following ouput.

    SELECT teacher_id,teacher_name,designation,dept_name
    FROM teachers
    LEFT JOIN depttable
    ON teachers.teacher_id = depttable.dept_id;
    Q4. Write query on above given tables (teachers, dept) to show following output

    SELECT t.teacher_id,t.teacher_name,t.designation,d.dept_name FROM


    teachers t, depttable d
    where t.dept_id(+) = d.dept_id order by(teacher_id);
    Q5. Write query on above given tables (teachers, dept) to show following output
    SELECT t.teacher_id,t.teacher_name,t.designation,d.dept_name
    FROM teachers t
    FULL OUTER JOIN
    depttable d
    ON t.dept_id = d.dept_id order by(teacher_id);
    Q6. Write query on above given tables (teachers, job_grades) to show following output

    SELECT t.teacher_id,t.teacher_name,t.designation,t.salary,j.grade_name

    FROM teachers t,job_grades j

    where t.salary BETWEEN j.lowest_salary AND j.highest_salary;


    Q7. Write query on above given tables (teachers, courses, assigned_courses) to show following output

    [Hint: show courses taught by Dr. Zeeshan only]

    SELECT a.ac_id, t.teacher_name, c.course_name, t.salary


    From assignedcourses a, teachers t, courses c
    where a.teacher_id = t.teacher_id
    AND a.course_id = c.course_id
    AND t.teacher_name = ‘Dr.Zeshan’
    Q8. Write query on above given tables (teachers, job_grades) to show following output

    [Hint: show all teachers having job grade E]

    SELECT t.teacher_id,t.teacher_name,t.designation,t.salary,j.grade_name
    from teachers t,job_grades j
    where t.salary between j.lowest_salary and j.highest_salary
    and
    j.grade_name='E'
    ------------------------------------------------------------------------------------------------------------------------------------------

    You might also like