Given Tables are
Instructor
table |
Course
table |
Teaches table |
WRITE THE SQL EXPRESSION FOR THE FOLLOWING QUERIES
a) Display all the records of the instructor table.
SELECT * FROM INSTRUCTOR;
b) Display the Name and department of each instructor.
SELECT name, dept_name FROM INSTRUCTOR;
c) Display Name and Salary of comp. sc. Instructors.
SELECT name, salary FROM INSTRUCTOR WHERE dept_name='Comp.Sci.';
d) Display the name of the instructor who belongs to the Physics department and getting salary of less than 90000.
SELECT name FROM INSTRUCTOR WHERE dept_name='Physics' AND salary<90000;
e) Display the name of the instructors who do not belong to Comp. Sci. Department.
SELECT name FROM INSTRUCTOR WHERE NOT dept_name='Comp.Sci.;
f) Display the names of the different departments distinctly.
SELECT DISTINCT dept_name FROM INSTRUCTOR;
g) Display all course_ids that are taught in the Spring semester of 2009.
SELECT course_id FROM TEACHES WHERE semester=’Spring’ AND year=2009.
h) Display course titles that are taught in Comp. Sci. Department and do not have credit equal to 3.
SELECT title FROM COURSE WHERE dept_name='Comp.Sci’ AND credit !=3;
i) Update the title of the course having the title 'Game Design' to 'Game Theory'.
UPDATE COURSE SET title=’Game Theory' WHERE title=’Game Design’;
j) Delete the instructor records of the History department.
DELETE FROM INSTRUCTOR WHERE dept_name=’History’;
k) Delete the course records of the courses having course_id starting with 'BIO'.
DELETE FROM COURSE WHERE course_id LIKE ’BIO%’;
l) Display all columns of the course table sorted in descending order of department names.
SELECT * FROM COURSE ORDER BY dept_name DESC;
m) Add a date_of_join column to the instructor table.
ALTER TABLE INSTRUCTOR ADD(date_of_join date);
n) Add a last_Name column to the instructor table.
ALTER TABLE INSTRUCTOR ADD(last_Name VARCHAR(20));
o) Change the name of dept_name to the department in all the tables.
ALTER TABLE INSTRUCTOR RENAME COLUMN dept_name TO department;
ALTER TABLE COURSE RENAME COLUMN dept_name TO department;
ALTER TABLE DEPARTMENT RENAME COLUMN dept_name TO department;
p) Truncate table prereq.
TRUNCATE TABLE PREREQ;
q) Change course_id column name to sub_code.
ALTER TABLE COURSE RENAME COLUMN course_id TO sub_code;
r) Change the data type of name to varchar2(50).
ALTER TABLE INSTRUCTOR MODIFY(name VARCHAR2(50));
s) Change the name of the Instructor table to Faculty_Info
RENAME INSTRUCTOR TO FACULTY_INFO;
0 Comments
Please do not Enter any spam link in the comment box