Scribd Logo
Upload

Welcome to Scribd!

  • 5 views

    SQL Practice Set

    Uploaded by

    Nikhil Rai
    na

    Copyright:

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

    SQL Practice Set

    Uploaded by

    Nikhil Rai
    5 views2 pages
    na

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    na

    Copyright:

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

    SQL Practice Set

    Uploaded by

    Nikhil Rai
    na

    Copyright:

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

    1.

    Consider the following schema of a relational database:


    Customer (cust-id, cust-name, annual-revenue, cust-type) [cust-id needs to maintain a sequence
    starting from 100 and cust-type must be manufacturer, wholesaler, retailer or distributor]
    Truck (truck-no, driver-name)
    City (city-name, population)
    Shipment (shipment-no, cust-id, weight, truck-no, start-city, destination-city). [start-city &
    destination-city both refers to city-name of City table]

    Create table through appropriate SQL commands. Define all integrity constraints and enter sufficient data.

    Write SQL commands for the following queries.

    a) Give the details for those shipments where the start-city and destination-city are same.
    b) Give the driver names who participated in maximum numbers of shipments
    c) Give the name of the city never appears in destination-city.
    d) Give the name of the cities whose population is more the average of all the cities.
    e) Give the shipment details where the starting character of customer name and the last character
    of driver name is ‘A’;

    2. Consider the following schema of a relational database:


    Employee (empno, emp_name, salary, hiredate, city)
    Department (deptno, dname, city)
    Manager (mgrno, deptno, manger_name, salary, hiredate,city)
    Empmgr (empno, mgrno)

    Create table through appropriate SQL commands. Define all integrity constraints and enter sufficient data.

    Write SQL commands for the following queries.


    a) Give the employee names who earn more than their manager.
    b) Give the name of the managers who have no employee under his supervision from the same
    city as of him.
    c) Give the name of the manager who has maximum number of employees under his
    supervision.
    d) Give the name of the employees who have been hired in the year 2006.
    e) Give the name of the city where no manager is located.

    3. Consider the following schema of a relational database:


    Employee (empno, empname, city)
    Project (pno, pname)
    Part (partno, part name, color)
    Use (pno, partno)
    Works (empno,pno)
    Create table through appropriate SQL commands. Define all integrity constraints and enter sufficient data.

    Write SQL commands for the following queries.


    a) Give the name of the employees who are working on more than 1 project.
    b) Give the name of the city where the maximum employees are located.
    c) Give the part names, which are used in all the projects.
    d) Give the name of the projects in which no employees are working whose name start with ‘S’.
    e) Give the name of the project which use maximum red part
    f) Give the name of the projects, which has used exactly 3 parts

    4. Consider the following schema of a relational database:


    Branch (branch_name, branch_city, assets)
    Customer (customer_name, street, city)
    Loan (branch_name, loan_no, amount)
    Borrower (customer_name, loan_no)
    Account (branch_name, account_no, balance)
    Depositor (customer_name, account_no)

    Create table through appropriate SQL commands. Define all integrity constraints and enter sufficient
    data.

    Write SQL commands for the following queries.

    a) Give the customer name who has account but not any loan
    b) Give the name of the customer who has either (10000-20000) or (30000-40000) in their account.
    c) Which branch has issued maximum amount of loan?
    d) Give the name of the customer who has maximum balance in their account.
    e) Give the name of the customer who has opened an account in a city in which he is not
    located.

    5. Consider the following schema of a relational database.


    Aircraft (aid, type)
    Flights (flno, aid, company)
    Employees (eid, ename, salary)
    Certified (eid, aid)
    Schedule (eid, flno, source_city, destination_city, date)

    Create table through appropriate SQL commands. Define all integrity constraints and enter sufficient
    data.

    a) Give the name of the employee who has certification on more than 1 type of aircrafts.
    b) Give the name of the employee who has no certification.
    c) Give the name of the city where from maximum flight takes off (source_city)?
    d) Give the name of the company, which spend maximum salary for their employees.
    e) Give the name of the company which has maximum types of flights.

    You might also like