Scribd Logo
Upload

Welcome to Scribd!

  • 43 views

    DBMS - IT C08 Practical List: Create Database and Write SQL Queries For The Following

    Uploaded by

    mridul
    The document provides examples of SQL queries and problems to solve related to creating and querying databases representing flights, books, and supplier/parts catalogs. It includes 8 problems related to a flights database involving pilots, aircraft, employees, and flights. It also includes 9 problems related to a books database involving authors, books, topics, and library branches. Finally, it provides 7 SQL queries to write related to a suppliers, parts, and catalog database including adding constraints, aggregating data, updating costs, authorizing users, deleting data, and creating a view.

    Copyright:

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

    DBMS - IT C08 Practical List: Create Database and Write SQL Queries For The Following

    Uploaded by

    mridul
    43 views2 pages
    The document provides examples of SQL queries and problems to solve related to creating and querying databases representing flights, books, and supplier/parts catalogs. It includes 8 problems related to a flights database involving pilots, aircraft, employees, and flights. It also includes 9 problems related to a books database involving authors, books, topics, and library branches. Finally, it provides 7 SQL queries to write related to a suppliers, parts, and catalog database including adding constraints, aggregating data, updating costs, authorizing users, deleting data, and creating a view.

    Original Title

    DBMS COIT 08 practical list

    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 provides examples of SQL queries and problems to solve related to creating and querying databases representing flights, books, and supplier/parts catalogs. It includes 8 problems related to a flights database involving pilots, aircraft, employees, and flights. It also includes 9 problems related to a books database involving authors, books, topics, and library branches. Finally, it provides 7 SQL queries to write related to a suppliers, parts, and catalog database including adding constraints, aggregating data, updating costs, authorizing users, deleting data, and creating a view.

    Copyright:

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

    DBMS - IT C08 Practical List: Create Database and Write SQL Queries For The Following

    Uploaded by

    mridul
    The document provides examples of SQL queries and problems to solve related to creating and querying databases representing flights, books, and supplier/parts catalogs. It includes 8 problems related to a flights database involving pilots, aircraft, employees, and flights. It also includes 9 problems related to a books database involving authors, books, topics, and library branches. Finally, it provides 7 SQL queries to write related to a suppliers, parts, and catalog database including adding constraints, aggregating data, updating costs, authorizing users, deleting data, and creating a view.

    Copyright:

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

    DBMS – IT C08 Practical List

    Create database and write SQL queries for the following:

    Q1. Flights(flno, from, to, distance, departs)


    Aircraft(aid, aname, range)
    Certified(eid, aid)
    Employees(eid, ename, salary)

    By definition, pilots are those employees who are certified on at least one aircraft. An aircraft
    can be used for any flight provided it has sufficient range. Pilots can pilot any flight provided
    they are certified on an aircraft with sufficient range.

    Problems
    1. Find eid’s of pilots who are certified on some Boeing.
    2. Find names of pilots who are certified on some Boeing.
    3. Find aid’s of aircraft that can fly non-stop from LA to NY. Assume you don’t already know
    the distance.
    4. Find flno of flights that can be piloted by every pilot whose salary is over $100,000.
    5. Find names of pilots who can operate planes with a range greater than 3,000 miles, but are
    not certified on any Boeing.
    6. Find eid of employee(s) with the highest salary.
    7. Find eid of employee(s) with the second highest salary.
    8. Find eid’s of employees certified on exactly three aircraft.

    Q2. Author ( authorname, citizenship, birthyear)


    Book(isbn, title, authorname)
    Topic(isbn, subject)
    Branch(libname, city)
    Instock(isbn, libname, quantity)

    1. Give all authors born after 1940.


    2. Give the names of libraries in Sydney.
    3. Give the cities where each book is held.
    4. Give the title of each book on the topic of either alcohol or drugs.
    5 Give the title and author of each book of which at least two copies are held in a branch
    located in Melbourne.
    6. Give the name of each Italian author who wrote an autobiography.
    7. Give the total number of books in stock in the branch called Fisher.
    8. This could mean the number of different titles, or the number of physical copies.
    9. Give the total number of books in stock in the branches located in Sydney.
    Q3. Consider a database:
    Suppliers(sid: integer,sname:varchar(10),address: varchar(10),)
    Parts(pid: integer, pname: varchar(10), color: varchar(10))
    Catalog(sid: integer, pid: integer, cost: real)
    *

    Write SQL queries:


    1. After creating the table I realized that I forgot to mention that only green, red, blue and
    black color parts are allowed in parts relation. Write a query to add this constraint in parts
    relation.
    2. For every supplier that only supplies green parts, print the name of the supplier and the
    total number of parts that she supplies.
    3. Increase the cost of each product by 10% which are green in color and are supplied by
    supplier = “John”.
    4. Authorize a user “Mary” to update color in “Parts” table. She can also pass the
    authorization to other users.
    5. Delete all the parts supplied by supplier “King”.
    6. Drop the attribute color from parts relation.
    7. Create a view for customers so that they can see only the supplier names, the name of
    parts supplied by that supplier and their cost.

    You might also like