Consider the following relational database.
1. Client_master
Description : Used to store client information
Client_master (Client_no First letter must start with ‘C’, Name Not null, Address, City,
Pincode, State, Bal_due)
2. Product_master
Description : Used to store product information
Product_master(Product_no First letter must start with ‘P’, Description Not null,
Profit_percent, Unit_measure, Qty_on_hand, Record_lvl, Sell_price, Cost_price)
3. Salesman_master
Description : Used to store salesman working for the company
Salesman_master(Salesman_no First letter must start with ‘S’ , Salesman_name Not
null, Address1 Not null, City, Pincode, State, Sal_amt Not null cannot be zero ,
Tgt_to_get Not null cannot be zero, Ttd_sales Not null, Remarks)
4. Sales_order
Description : Used to store client’s orders
Sales_order(Order_no First letter must start with ‘O’, Order_date, Client_no, Dely_addr,
Salesman_no, Dely_type Deliver:part(P)/full(F) Default ‘F’, Billed_yn, Dely_date,
Order_status: Values can be ‘in process’ or ‘fulfilled’ or ‘backorder’or ‘cancelled’.
5. Sales_order_details
Description : Used to store client’s orders with details of each product ordered
Sales_order_details(Order_no, product_no, Qty_ordered, Qty_number, Product_rate)
Queries:
1. Print the information of orders placed in the month of ‘May’ from sales_order table.
2. Find out the product whose selling price is more than 3000 and calculate a new
selling-price as 20% of the original selling-price and display both from the
product_master.
3. Display the items that are ordered from the same address.
4. Find the client names that have placed orders before the month of 01-may-96.
5. Find out if the product ‘1.44 floppies’ has been ordered by any client and print the
client_no, name to whom it was sold.
DESIGNING THE SCHEMA(ER DIAGRAM): CO1
CREATION, INSERTION, ALTERING TABLES AND QUERIES: CO2
Consider the following schema:
Employee (employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct, manager_id, department_id)
Jobs (job_id, job_title, min_salary, max_salary)
Location (location_id, street_address, postal_code, city, state_province, country_id)
Departments (department_id, department_name, manager_id, location_id)
Countries (country_id, country_name, region_id)
Create the tables for the above relations and insert data into these tables so that
all queries may be answered.
Answer the following queries using SQL
1. Write a query to display the last name, job, and salary for all those employees who
haven’t worked as a Programmer or a Shipping Clerk, and not drawing the salary
$4,500, $10,000, or $15,000.
2. Write a query to get the department name and number of employees working in
each department.
3. Write a subquery to find the 3rd maximum salary of all salaries.
4. Write a subquery to select the last 2 records from a table.
5. Write a subquery to find the first_name, last_name and salary, which is equal to the
minimum salary for this post, he/she is working on.
DESIGNING THE SCHEMA(ER DIAGRAM): CO1
CREATION, INSERTION, ALTERING TABLES AND QUERIES: CO2
Consider the following schema:
Employee (employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct, manager_id, department_id)
Jobs (job_id, job_title, min_salary, max_salary)
Location (location_id, street_address, postal_code, city, state_province, country_id)
Departments (department_id, department_name, manager_id, location_id)
Countries (country_id, country_name, region_id)
Create the tables for the above relations and insert data into these tables so that
all queries may be answered.
Answer the following queries using SQL
1. Write a query to find the first_name, last_name and salaries of the employees who
have a higher salary than the employee whose last_name is ‘De Haan’.
2. Write a SQL query to find the name, including first_name and last_name, jobs,
department name and ID, of the employees working in Toronto.
3. Write a query to get the job_id and the ID(s) for those employees who are working
in that post.
4. Write a query to display the name, including first_name and last_name and hire
date for all employees who were hired in 1987.
5. Write a query to display the name, including first_name and last_name, and
department ID who works in the department 30 or 100 and arrange the result in
ascending order according to the department ID.
DESIGNING THE SCHEMA(ER DIAGRAM): CO1
CREATION, INSERTION, ALTERING TABLES AND QUERIES: CO2
Using the following database :-
Enrollment (enrollno, name, gender, DOB, address, phone)
Admission (admno, enrollno, course, year, date, cname)
Colleges (cname, city, address, phone, afdate)
FeeStructure (course, year, fee)
Payment (billno, admno, amount, pdate, purpose)
write SQL statement for the following :-
1) List the students who have not paid full fees in your college.
2) List the students in colleges in your city and also live in your city.
3) List the number of admission in your class every year.
4) Get details of students who took admission in Bhilai colleges.
5) List the students in the year who are not in the colleges in the same city as they
live in.
DESIGNING THE SCHEMA(ER DIAGRAM): CO1
CREATION, INSERTION, ALTERING TABLES AND QUERIES: CO2
Using the following database :-
college ( cname , city, city, phone, afdate)
staffs (sid, sname, scity, contactno)
staffjoins(sid, cname, dept, DOJ, post, salary)
teachings (sid, class, paperid, fsession, tsession) where fsession and tsession shows the
class duration (like 2006 - 2007).
subjects (subid, subjectname)
write SQL statement for the following :-
1. List the name of the teachers teaching computer subjects.
2. List the name and cities of all staff working in your college who earn more
than 15,000 and less than 25,000.
3. find the staff whose name starts with ‘m’ or ‘r’ and ends with ‘a’ and\ or 7
characters long.
4. Find the staff who earn a higher salary that is greater than the average salary
of their college.
5. Find a college whose average salary is more than the average salary of C2
college.
DESIGNING THE SCHEMA(ER DIAGRAM): CO1
CREATION, INSERTION, ALTERING TABLES AND QUERIES: CO2
Book(Bookid, title, publishername)
Book_authors(bookid, authorname)
Book_copies(Bookid,branchid,no_of_copies)
Book_loans(Bookid, branchid, cardno, dateout, duedate)
Library_branch(Branchid, Branchname,address)
Borrower(cardno, name, address, phone)
Write SQL commands for the following queries:
1) Retrieve the names of all borrowers who do not have any books
checked out.
2) For each book that is loaned out from the "******" branch and whose
DueDate is today, retrieve the book title, the borrower's name, and the
borrower's address.
3) Retrieve the names, addresses, and number of books checked out for all
borrowers who have more than five books checked out.
4) For each book authored (or co-authored) by "******", retrieve the title
and the number of copies owned by the library branch whose name is
"******"
5) For each library branch, retrieve the branch name and the total number of
books loaned out from that branch.
DESIGNING THE SCHEMA(ER DIAGRAM): CO1
CREATION, INSERTION, ALTERING TABLES AND QUERIES: CO2
Employee(SSN, Name, address, salary, superSSN, Dno)
Department(Dname, Dno, MgrSSN, Mgrstartdate)
Department_locations(Dno, location)
Project(Pno, Pname, plocation, Dno)
Works_on(ESSN, Pno, hours)
Dependent(ESSN, dep_name, relationship)
1) Retrieve the names of employees in department 5 who work more
than 10 hours per week on the 'ProductX' project.
2) Retrieve the names of employees who do not work on any project.
3) List the last names of department managers who have no
dependents.
4) Find details of those employees whose salary is > the average salary
for all employees. Output salary in descending order.
5) List the last names of department managers who have no
dependents.
DESIGNING THE SCHEMA(ER DIAGRAM): CO1
CREATION, INSERTION, ALTERING TABLES AND QUERIES: CO2
Consider the following schema:
Emp(ID,name,SSN)
EmpDet(ID,gender,DOB,address,phone)
JobDet(ID ,DepID, Salary,HireDate,SSSN)
Dep(ID,name)
DepLoc(ID,Location)
Write SQL commands for the following queries:
1) Display the employees’ name and year of birth who has the maximum
salary among employees born in the same year.
2) Display the name of the department for which the number of locations are
maximum.
3) Display the name of the employee who is referred to by more employees as
a Supervisor.
4) Display the employee details whose supervisor’s location and employee’s
location is the same.
5) Display the names of all male employees , those who are working in the
same department ‘X’ and in the location ‘Y’.
DESIGNING THE SCHEMA(ER DIAGRAM): CO1
CREATION, INSERTION, ALTERING TABLES AND QUERIES: CO2
Create the following relations with the given constraints:
Event (eventid, name, description,city)
Participant (playerid, name, eventid, gender, year)
Prizes (prizeid, prize-money, eventid, rank,year)
Winners (prizeid, playerid)
1) Prize-money is of format [Link] and maximum prize that can be awarded is
1500.00 and minimum prize awarded is 500.00
2) Event names cannot be repeated.
3) Player name, event name, prize-money and dateofevent cannot be blank.
4) Rank can take any of these values only - 1, 2, 3.
Write SQL commands for the following queries:
1) Retrieve the name of the person who has won the highest amount of prize.
2) Retrieve the name of events where all prize winners are females.
3) Retrieve the name of all 2nd prize winners along with the event name.
4) Retrieve the name of the person who has won 1st prize of an event for maximum
number of successive years. Give the event name also along with the name of the
player and period for which he has won it.
5) Retrieve the names of the persons who have won the highest number of 1st, 2nd
and 3rd prizes.
DESIGNING THE SCHEMA(ER DIAGRAM): CO1
CREATION, INSERTION, ALTERING TABLES AND QUERIES: CO2
Implement the following schema by using the following constraints.
1) STUDENT (enrol-no, s-name, phone, gender, DOB)
2) BRANCH (branch-name, branch-code, HOD-name)
3) COURSE (Subject-code, Subject-name, details, Branch-code)
4) RESULT (enrol-no, Subject-code, marks, res, rank)
1. Subject-name, phone and s-name should not be null.
2. Rank can take any of these values only – 1, 2, 3, 0.
3. The res attribute can be any of the following grades: A, B, C, D, P or F.
Write SQL statements for the following queries:
1) Find the students who secured Grade A in all the courses offered by a specific
department.
2) Find the name of the branch in which all students failed.
3) Find the name of the youngest student who failed in more than two subjects.
4) Find the HOD for the branch in which no student has failed.
5) Find the age of all students.
DESIGNING THE SCHEMA(ER DIAGRAM): CO1
CREATION, INSERTION, ALTERING TABLES AND QUERIES: CO2