Scribd Logo
Upload

Welcome to Scribd!

  • 89 views

    2020UCM2333-DBMS Practical File

    Uploaded by

    Aman
    The document contains the database schema for a company including tables for departments, jobs, employees, and queries to retrieve information from the tables. The tables are created and sample data is inserted. Queries are provided to count employees hired in March 2015, find the nth highest salary, calculate department budgets, and more. Triggers and views are also created to automatically update data and restrict invalid entries.

    Copyright:

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

    2020UCM2333-DBMS Practical File

    Uploaded by

    Aman
    89 views23 pages
    The document contains the database schema for a company including tables for departments, jobs, employees, and queries to retrieve information from the tables. The tables are created and sample data is inserted. Queries are provided to count employees hired in March 2015, find the nth highest salary, calculate department budgets, and more. Triggers and views are also created to automatically update data and restrict invalid entries.

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains the database schema for a company including tables for departments, jobs, employees, and queries to retrieve information from the tables. The tables are created and sample data is inserted. Queries are provided to count employees hired in March 2015, find the nth highest salary, calculate department budgets, and more. Triggers and views are also created to automatically update data and restrict invalid entries.

    Copyright:

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

    2020UCM2333-DBMS Practical File

    Uploaded by

    Aman
    The document contains the database schema for a company including tables for departments, jobs, employees, and queries to retrieve information from the tables. The tables are created and sample data is inserted. Queries are provided to count employees hired in March 2015, find the nth highest salary, calculate department budgets, and more. Triggers and views are also created to automatically update data and restrict invalid entries.

    Copyright:

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

    DATABASE MANAGEMENT

    SYSTEM LAB FILE


    NAME:- Shashikant
    ROLL NO:- 2020UCM2333
    SEC:- MAC
    Subject:-DBMS
    Ques 1:-
    Consider the following relational schema
    SAILORS (sid, sname, rating, date_of_birth)
    BOATS (bid, bname, color)
    RESERVES (sid, bid, date, time slot)
    Write the following queries in SQL and relational algebra

    a) Find sailors who’ve reserved at least one boat


    b) Find names of sailors who’ve reserved a red or a green boat in the
    month of March.
    c) Find names of sailors who’ve reserved a red and a green boat
    d) Find sid of sailors who have not reserved a boat after Jan 2018.
    e) Find sailors whose rating is greater than that of all the sailors named
    “John”
    f) Find sailors who’ve reserved all boats
    g) Find name and age of the oldest sailor(s)
    h) Find the age of the youngest sailor for each rating with at least 2
    such sailors

    CREATION OF THE TABLE

    create table sailors


    ( sid int primary key,
    sname varchar(20),
    rating int,
    date_of_birth date);
    create table boats
    (bid int primary key,
    bname varchar(20),
    color varchar(10));

    create table reserves


    ( sid int not null, bid
    int not null,

    dt date not null,


    timeslot int,
    foreign key (sid) references sailors(sid)
    foreign key (bid) references boats (bid));

    INSERTING VALUES

    insert into sailors values (1, 'Sagar', 7, "1999-01-03");


    insert into sailors values (2, 'Ram', 9, "1998-07-12");
    insert into sailors values (3, 'Uttam', 9, "1996-05-22");
    insert into sailors values (4, 'Ravi', 8, "1993-01-23");
    insert into sailors values (5, 'Akash',8,"2001-09-01");

    insert into Boats values (101, 'Interlake', 'blue');


    insert into Boats values (102, 'Interlake', 'red');
    insert into Boats values (103, 'Clipper',
    'green'); insert into Boats values (104, 'Marine',
    'red');

    insert into Reserves values (1, 101, '2017-10-10',1);


    insert into Reserves values (1, 102, '2017-10-10',2);
    insert into Reserves values (1, 103, '2017-10-10',2);
    insert into Reserves values (1, 104, '2017-10-10',2);
    insert into Reserves values (1, 101, '2019-10-10',1);
    insert into Reserves values (2, 102, '2011-03-01',3);
    insert into Reserves values (2, 102, '2019-11-07',3);
    insert into Reserves values (3, 101, '2017-11-07',2);
    insert into Reserves values (3, 102, '2017-08-07',2);
    insert into Reserves values (4, 103, '2017-03-19',1);
    insert into Reserves values (2, 103, '2017-03-19',3);

    QUERIES

    a)
    select sname
    from sailors
    where sid in (select sid from reserves);
    b)
    select sname from
    sailors where sid in ( select
    r.sid
    from boats b, reserves r where r.bid = b.bid AND
    b.color = "red" and (select extract(month from
    r.dt)="03") union
    select r2.sid from boats b2, reserves r2 where
    r2.bid = b2.bid AND b2.color = "green" and (select
    extract(month from r2.dt)="03") );

    c)
    select distinct S1.sname from
    sailors S1, reserves R1, boats B1,
    reserves R2, boats B2
    where S1.sid=R1.sid and R1.bid=B1.bid and
    S1.sid=R2.sid and R2.bid=B2.bid and
    B1.color="red" and B2.color="green";

    d)
    select sid
    from sailors
    where sid not in
    (select sid from reserves
    where dt>="2018-01-01");

    e)
    select sname
    from sailors where rating > all (select rating from
    sailors where sname="sagar");
    f)
    select sname from sailors where not exists
    (select * from boats b where not exists ( select * from
    reserves r where r.sid=s.sid AND

    r.bid=b.bid));
    g)
    select sname,TIMESTAMPDIFF
    (YEAR,date_of_birth,"2020-09-17") as age
    from sailors
    where date_of_birth <=all(select date_of_birth
    from sailors);

    Ques 2:-
    Consider the following relational schema:

    CUSTOMER (cust_num, cust_lname , cust_fname,


    cust_balance);

    PRODUCT (prod_num, prod_name, price)

    INVOICE (inv_num, prod_num, cust_num, inv_date


    ,unit_sold, inv_amount);

    Write SQL queries and relational algebraic expression for


    the following

    a)Find the names of the customer who have purchased no


    item. Set default value of Cust_balance as 0 for such
    customers.
    b) Write the trigger to update the CUST_BALANCE in the
    CUSTOMER table when a new invoice record is entered for
    the customer.
    c) Find the customers who have purchased more than three
    units of a product on a day.
    d) Write a query to illustrate Left Outer, Right Outer and Full
    Outer Join.
    e) Count number of products sold on each date.
    f) As soon as customer balance becomes greater than Rs.
    100,000, copy the customer_num in new table called
    ”GOLD_CUSTOMER”
    g) Add a new attribute CUST_DOB in customer table
    CREATION OF THE TABLE

    create table customer(


    cust_num int, cust_lname
    varchar(50) cust_fname varchar(50)
    not null, cust_balance int default
    0, primary key (cust_num));

    create table product(


    prod_num int, prod_name
    varchar(70) not
    null, price int not null, primary
    key(prod_num));

    create table invoice(


    inv_num int,
    prod_num int not null,
    cust_num not null,
    inv_date date not null,
    unit_sold int not null,
    inv_amount int not null,
    primary key (inv_num),
    foreign key(prod_num)
    references product(prod_num), foreign
    key(cust_num) references customer
    (cust_num),

    INSERTING VALUES

    insert into customer values (1, 'Sagar', ‘tom’, 0); insert


    into customer values (2, 'Ram',’fox’ ,220);
    insert into customer values (3, 'Uttam',’mice’ 1110);

    insert into product values (4,”gold ”,150000);


    insert into product values (1, “Earphones”,1250);
    insert into product values (3,”shoes ”,400); insert
    into product values (2,”Guitar ”,4200);

    insert into invoice values(1,2,1,’2019-01-01’,4,600000),


    (2,1,2,’2019-01-01’,2,2500),(3,4,3,’2019-01-02’,1,4200),
    (4,3,4,’2019-01-03’,3,1200),(5,1,5,’2019-01-01’,2,2500),(6,
    1,5,’2019-01-02’,5,6250),(7,2,2,’2019-01-04’,1,150000),

    QUERIES
    a)
    select concat(cust_fname,” ”,cust_lname) as
    name from customer
    where cust_balance=0;
    b).
    create trigger upd_cust before
    insert on invoice
    for each row up[date customer c set
    c.cust_balance=.c.cust_balance+inv_amount where
    c.cust_num=new.cust_num;

    c).
    select cust_num,concat (cust_fname,” “ ,cust_lname)
    from customer
    where cust_num
    in(select cust_num from invoice group by
    cust_num,inv_date,prod_num having
    sum(unit_sold)>3);

    d).
    select concat(c.cust_fname,c.cust_lname) as
    name, i.inv_amount from customer c left join
    invoice
    on c.cust_numi.cust_num;

    select concat(c.cust_fname,c.cust_lname) as
    name, i.inv_amount from customer c right join
    invoice
    on c.cust_numi.cust_num;
    select concat(c.cust_fname,c.cust_lname)
    as name, i.inv_amount from customer c left
    join invoice on
    c.cust_num.cust_num; union
    select concat(c.cust_fname,c.cust_lname) as
    name, i.inv_amount from customer c right join
    invoice
    on c.cust_numi.cust_num;
    e).
    select inv_date,sum(unit_sold) as
    total_daily_sales from invoice
    group by inv_date;

    f).
    create table GOLD CUSTOMER(
    cust_num int, cust_fname
    varchar(50); cust_lname
    varchar(50); primary
    key(cust_num));
    g).
    alter table customer add
    column cust_cust_dob date;
    Ques 3:-
    Consider the following relational schema:

    DEPARTMENT(Department_ID, Name, Location_ID)


    JOB (Job_ID , Function )
    EMPLOYEE (Employee_ID, name, DOB, Job_ID , Manager_ID,
    Hire_Date, Salary, department_id)

    Answer the following queries using SQL and relational algebra:

    a) Write a query to count number of employees who joined in March


    2015
    b) Display the Nth highest salary drawing employee details.
    c) Find the budget (total salary) of each department.
    d) Find the department with maximum budget.
    e) Create a view to show number of employees working in Delhi and
    update it automatically when the database is modified.
    f) Write a trigger to ensure that no employee of age less than 25 can be
    inserted in the database.

    CREATION OF THE TABLE

    CREATE TABLE DEPARTMENT( Department_ID


    varchar(5) PRIMARY KEY, Name varchar(25)
    NOT NULL,
    Location_ID varchar(5)

    );

    CREATE TABLE JOB(


    Job_ID varchar(5) PRIMARY
    KEY, function varchar(50) );

    CREATE TABLE EMPLOYEE(


    Employee_ID varchar(5) PRIMARY KEY,
    Name varchar(25) NOT NULL,
    dob date,
    Job_ID varchar(5),
    Manager_ID varchar(5)

    Hire_Date date,
    Salary int,
    Department_ID varchar(5),
    FOREIGN KEY (Job_ID)
    REFERENCES JOB(Job_ID),
    FOREIGN KEY (Department_ID)
    REFERENCES DEPARTMENT(Department_ID)
    );

    INSERTING VALUES
    INSERT INTO DEPARTMENT VALUES

    ("S01", "Sales", "DEL01"), ("D01", "Design",


    "DEL02"), ("P01", "Product", "MUM01"),
    ("M01", "Management", "KOL01"), ("A01", "
    Accounts", "MUM02");

    INSERT INTO JOB VALUES


    ("J01", "Sales Employee"),
    ("J02", "Designer"),
    ("J03", "Product Manager"),
    ("J04", "Manages Client Orders"),
    ("J05", "Manages Accounts");

    INSERT INTO EMPLOYEE VALUES


    ("E001", "Sagar", "1995-01-01", "J01",
    "E002", "2015-03-13", 30000, "D01"),
    ("E002", "Ram", "1994-11-21", "J03",
    "E002", "2015-03-22", 25000, "S01"),
    ("E003", "Rahul", "1999-05-16", "J03",
    "E002", "2015-03-25", 45000, "P01"),
    ("E004", "Ravi", "1995-04-06", "J02",
    "E006", "2016-03-01", 45000, "M01"),
    ("E005", "Harsh", "1997-06-24", "J04",
    "E006", "2016-03-01", 30000, "A01"),
    ("E006", "Ishaan", "1995-08-04", "J05",
    "E006", "2016-03-01", 50000, "D01"),
    ("E007", "Akash", "1996-03-14", "J01",
    "E008", "2016-03-01", 60000, "S01"),
    ("E008", "Palak", "1995-12-17", "J02",
    "E008", "2016-04-25", 70000, "P01"),
    ("E009", "Raju", "1995-07-11", "J05",
    "E009", "2016-04-25", 20000, "M01"),
    ("E010", "Sahil", "1991-09-22", "J04",
    "E011", "2016-04-25", 33000, "A01"),
    ("E011", "Vishal", "1992-05-29", "J03",
    "E012", "2017-03-16", 46000, "A01"),
    ("E012", "Zaid", "1990-10-16", "J02",
    "E012", "2017-03-16", 56000, "S01");

    QUERIES

    a)
    SELECT COUNT(Employee_ID)
    FROM EMPLOYEE
    WHERE Hire_Date >= "2015-03-01" AND
    Hire_Date <= "2015-03-31";
    (b)

    SELECT *
    FROM EMPLOYEE
    GROUP BY Salary
    ORDER BY Salary DESC LIMIT 1
    (c)

    SELECT SUM(Salary),
    Department_ID FROM EMPLOYEE
    GROUP BY Department_ID;

    (d)

    SELECT SUM(Salary),
    Department_ID FROM EMPLOYEE
    GROUP BY Department_ID ORDER
    BY SUM(Salary) DESC LIMIT 1;

    (e)
    CREATE VIEW DELHI_EMPLOYEES AS
    SELECT COUNT(Employee_ID)
    FROM EMPLOYEE, DEPARTMENT
    WHERE
    (Location_ID="DEL01" OR Location_ID =
    "DEL02") AND EMPLOYEE.Department_ID =
    DEPARTMENT.Department_ID;

    (f)

    CREATE TRIGGER Check_age


    BEFORE INSERT ON EMPLOYEE
    FOR EACH ROW
    BEGIN
    IF NEW.dob > 1993-01-01 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'ERROR:
    Minimum Age is 25 years';
    END IF;

    You might also like