Q.1. Consider the following relational database:
STUDENT (name, student#, class, major)
COURSE (course name, course#, credit hours, department)
SECTION (section identifier, course#, semester, year, instructor)
GRADE_REPORT (student#, section identifier, grade)
PREREQUISITE (course#, presequisite#)
Specify the following queries in SQL on the above database schema.
(i) Retrieve the names of all students majoring in ‘CS’ (Computer Science).
(ii) Retrieve the names of all courses taught by Professor King in 1998.
(iii) Delete the record for the student whose name is ‘Smith’ and whose student number is 17.
(iv) Insert a new course <’Knowledge Engineering’, ‘CS4390’, 3, ‘CS’>
Q.2. Given the following relations
TRAIN (TNAME, START, DEST)
TICKET (PNRNO.,TNAME, START, DEST, FARE)
PASSENGER (NAME, ADDRESS, PNRNO.)
Write SQL expressions for the following queries:
Note: Assume NAME of Train is a column of Ticket.
(i) List the names of passengers who are travelling from the start to the destination station of the train.
(ii) List the names of passengers who have a return journey ticket.
(iii) Insert a new Shatabdi train from Delhi to Bangalore.
(iv) Cancel the ticket of Tintin.
(e) Define view with example.
STUDENT (name, student#, class, major)
COURSE (course name, course#, credit hours, department)
SECTION (section identifier, course#, semester, year, instructor)
GRADE_REPORT (student#, section identifier, grade)
PREREQUISITE (course#, presequisite#)
Specify the following queries in SQL on the above database schema.
(i) Retrieve the names of all students majoring in ‘CS’ (Computer Science).
(ii) Retrieve the names of all courses taught by Professor King in 1998.
(iii) Delete the record for the student whose name is ‘Smith’ and whose student number is 17.
(iv) Insert a new course <’Knowledge Engineering’, ‘CS4390’, 3, ‘CS’>
(v)Select all the Students who score ‘O’ Grade.
(vi) Sort all the students in ascending order by course and within course descending order by their name.
(vii) list out the maximum and minimum grade.
(viii) Display the name of student who has top grade.
(ix) Display all the students semV.
(x) Display the students who opt for 'Computer Engg' Course.
(vi) Sort all the students in ascending order by course and within course descending order by their name.
(vii) list out the maximum and minimum grade.
(viii) Display the name of student who has top grade.
(ix) Display all the students semV.
(x) Display the students who opt for 'Computer Engg' Course.
Q.2. Given the following relations
TRAIN (TNAME, START, DEST)
TICKET (PNRNO.,TNAME, START, DEST, FARE)
PASSENGER (NAME, ADDRESS, PNRNO.)
Write SQL expressions for the following queries:
Note: Assume NAME of Train is a column of Ticket.
(i) List the names of passengers who are travelling from the start to the destination station of the train.
(ii) List the names of passengers who have a return journey ticket.
(iii) Insert a new Shatabdi train from Delhi to Bangalore.
(iv) Cancel the ticket of Tintin.
(e) Define view with example.
Q.3. Consider the job database described above (the primary keys are underlined) and write SQL queries for the following:
(1) employee(employeename, street, city)
(2) works(employeename, company_name, salary)
(3) company(companyname, city)
(4) manages(employeename, manager_name)
(1) Find the names of all employees who work for First Bank Corporation.
(2) Find the names of all employees in this database who live in the same city as the company for which they work.
(3) Find the names of all employees who live in the same city and on the same street as their managers.
(4) Find the company with the smallest payroll.
(5) Find the employees who earn more than the average salary of all the employees at their company.
(6) Find the names of all employees who do not have a manager.
No comments:
Post a Comment